Sunday, 4 March 2018

Oracle Apps Interviews Technical Questions and Answers by MNC Compnaies

Hi DBA-mates,
We are back again with some important Questions which may useful and helpful for any interviews.
I got these Questions from a friend, so I thought to share and it might useful here for all.
Actually, these questions are not for Oracle Apps DBA. These questions are looks like for Oracle DBA (Core).

1. Introduced yourself and your role.

2. How to disable archive log:
Ans. The method for disabling archive log mode described above is the only way to disable archive log mode.

Shutdown edit pfile / spfile (spfile when instance is up of course) to say log_archive_start = false.

startup mount

alter database noarchivelog;

alter database open;


Remember that once you disable archive log mode you need to take a fresh backup once archive log mode is turned back on.

3. RMAN Configuration:

rman>show all ;                                                 ## to check all the configuration.

RMAN> configure controlfile autobackup on;    # for on and off
RMAN> configure backup optimization off;

4. How to check database alert log path?
Ans:
If database if UP and Running:
SQL> show parameters dump

Or SELECT * FROM v$daig_info;

If database is down:
Or you can check as from ps –ef|grep tns then follow the paths…
i.e go till oracle_home (oracle version)
then $ORACEL_HOME/admin/SID_Hostname/diag/rdbms/sid/SID/trace

ls –l alert*
alert_SID.log


5. How to check Table log? (i.e. Logging and Nologging concepts) # not sure

6. How to check blocking session?
Ans:
$ cat block.sql
select sid "Session id ", decode(Block,1, 'Blocking Session','Waiting Session') Details, ctime  "Blocking Time /Waiting time",id2 from v$lock where (block <> 0 or request <>0 ) and ctime > 200 order by id2,Details;

SQL> desc v$lock
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 KADDR                                              RAW(8)
 SID                                                NUMBER
 TYPE                                               VARCHAR2(2)
 ID1                                                NUMBER
 ID2                                                NUMBER
 LMODE                                              NUMBER
 REQUEST                                            NUMBER
 CTIME                                              NUMBER
 BLOCK                                              NUMBER

SQL>

7. How to do expdp and impdp details and concepts.

Ans:


8. How to Apply Database Patches.

Ans:
9. How to Apply Apps patches apply.

Ans:


Some more Questions and Answers by Top MNC Companies coming soon...

Regards,
Ora-data Team

Sunday, 14 January 2018

How to Create a Database User and Grant Permissions in Oracle



Hi DBA-mates,
As we all know, creating a Schema or DB User is very easy and we all do it in DBA’s life.
However, there is some important points, which we need to take care.
There is some points such as how to create User’s and Grant them the Privileges.

How to create User and grant the user:
1. Create DB User or Schema:
Note: Here username is ora-data.
$sqlplus '/as sysdba'
Before creating user, we should check either user is existing or not.
SQL>select USERNAME from DBA_USERS where USERNAME like '%ORA-DATA%';

Query:
CREATE USER user_name IDENTIFIED BY  'passsword' DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE tablespace_name PROFILE DEFAULT ACCOUNT UNLOCK;

Example:
SQL>CREATE USER ora-data IDENTIFIED BY ora-data DEFAULT TABLESPACE user TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;
Cross check:
SELECT USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED from dba_users where USERNAME='ora-data';

Providing GRANTS:
SQL>GRANT CONNECT, RESOURCE TO ora-data;
SQL>GRANT CREATE SESSION TO ora-data;

SQL>GRANT CONNECT TO ora-data;
SQL>GRANT SELECT ANY DICTIONARY to ora-data;
SQL>GRANT SELECT ANY TABLE to ora-data;
SQL>GRANT UNLIMITED TABLESPACE to ora-data;
SQL>ALTER USER ora-data ENABLE EDITIONS; (In R12.2 specially)

If you want to Grant only on any particular Table (tb1) then find the below details:
SQL>GRANT SELECT ON tb1 to ora-data;

Suppose, if you want to GRANT for any particular OWNER for particular TABLE then follow below steps:

Example: OWNER= xxabc, TABLE= tb1, USERNAME=ora-data

SQL>conn xxabc/password
SQL>show user
xxabc
SQL> GRANT SELECT ON tb1 TO ora-data;

How to Check the GRANT details:
1. DBA_SYS_PRIVS: To show information about user's privileges related to System (DBA).
2. DBA_TAB_PRIVS: To show information about user's privileges related to Tables.
3. DBA_ROLE_PRIVS: To show information about user's privileges related to Roles.

To know details:
SQL>desc DBA_SYS_PRIVS;

Some Column Name and details:
GRANTEE: It is the name, role, or user that was assigned the privilege.

PRIVILEGE: It is the privilege that is assigned.

ADMIN_OPTION: It indicates if the granted privilege also includes the ADMIN option.

SQL>desc DBA_TAB_PRIVS;

GRANTEE: It is the name of the user with granted access.

TABLE_NAME: It is the name of the object (table, index, sequence, etc).

PRIVILEGE: It is the privilege assigned to the GRANTEE for the associated object.

Hope this may useful and helpful. If there is any suggestions or  corrections, please let us know either in comment box or contact us @https://ora-data.blogspot.in/


Some more useful links:






Thanks & Regards,
ora-data Team