April 2, 2013

Oracle Apps DBA RDBMS Interview Questions/FAQs Part1

Oracle Apps DBA EBS (E-Business Suite)
RDBMS Interview Questions/FAQs Part1

1. How to apply an rdbms patch?
Ans : Using opatch

2. Have you applied rdbms patches and for what?
Ans : We got ORA-7445 error, for which oracle recommended to apply a rdbms patch.

3. What is the pre-req for applying a rdbms patch?
Ans : Inventory should be set in file oraInst.loc @/var/opt/oracle or /etc

4. What is Inventroy?
Ans: The oraInventory is the location for the OUI (Oracle Universal Installer)'s bookkeeping. The inventory stores information about all Oracle software products installed in all ORACLE_HOMES on a machine, other non-Oracle products, such as the Java Runtime Environment (JRE). In a 11i Application system the RDBMS and iAS ORACLE_HOMEs are registered in the oraInventory. The 806 ORACLE_HOME, which is not managed through OUI, is not registered in oraInventory.

5. What are different types of inventories?
The Global inventory (or Central inventory) The Local inventory (or Home inventory).

6. Is that necessary to shutdown database while applying a database patch?
Ans : Yes.

7. What is Global inventory?
Ans : The Global Inventory is the part of the XML inventory that contains the high level list of all oracle products installed on a machine. There should therefore be only one per machine. Its location is defined by the content of oraInst.loc. The Global Inventory records the physical location of Oracle products installed on the machine, such as ORACLE_HOMES (RDBMS and IAS) or JRE. It does not have any information about the detail of patches applied to each ORACLE_HOMEs. The Global Inventory gets updated every time you install or de-install an ORACLE_HOME on the machine, be it through OUI Installer, Rapid Install, or Rapid Clone. If you need to delete an ORACLE_HOME, you should always do it through the OUI de-installer in order to keep the Global Inventory synchronized.

8. What is local inventory?
Ans : There is one Local Inventory per ORACLE_HOME. It is physically located inside the ORACLE_HOME at $ORACLE_HOME/inventory and contains the detail of the patch level for that ORACLE_HOME. The Local Inventory gets updated whenever a patch is applied to the ORACLE_HOME, using OUI.

9. How to find opatch is enabled or not for u r database?
Ans : If Opatch directory exists under RDBMS_ORACLE_HOME.

10. What is the size of your database?
Ans :

11. How to find operating system version?
Ans : uname –a

12. How to kill a database session?
Ans : alter system kill session '&sid,&serial#';

13. How to find invalid objects in database?
Ans : select count(*) from dba_objects where status=’INVALID’;

14. What is tnsping?
Ans : tnsping is command used to check the connectivity to the database server node from other nodes. TNS entry should be there in tnsnames.ora for the database we are trying to work this command.

15. How to find the locks and what is the resolution?
Ans : we can find general locks with the following query:
select * from sys.dba_dml_locks order by session_id.
We can find the dead locks with the following query:
select * from v$lock where lmode > 0 and id1 in (select distinct id1 from v$lock where request > 0)
If it’s a dead lock, we need to kill that session.

16. Which files tell you the database health?
Ans : alert log file @RDBMS_ORACLE_HOME/admin//bdump

17. What is statspack?
Ans : Statspack is a database utility to gather database and session level performance information.

18. How to install statspack?
Ans : Run the script spcreate.sql @RDBMS_ORACLE_HOME/rdbms/admin.

19. How to enable trace at database level?
Ans : set init.ora parameter sql_trace

20. How to enable trace for a session?
Ans: alter system set sql_trace=true;
Execute the sql query
alter system set sql_trace=false;
This will create a trace file at $RDBMS_ORACLE_HOME/admin/contextname/udump with the spid of the current sql session.

Oracle AppsDBA (E-Business Suite) RDBMS Interview Questions/FAQs Part2

Related Articles:  Oracle DBA Interview Questions/FAQs  Oracle RAC Interview Questions  Oracle Exadata Interview Questions