Oracle Apps DBA Interview Questions and Answers

Oracle Apps DBA Interview Questions

 This is the Interview season, with lots of openings World Wide. I have listed some of the very common Apps DBA interview questions faced by me. The answers are listed after each question. All the Best.....!

File System

1. What all directories will be there under any “product” top

Ans àcd $AP_TOP (Example of product top AP) (Below are various directory under each product top – ad, ap, gl..)
admin  bin  forms  help  html  lib  log  mds  media  mesg  out  patch  reports  sql  xml

$ Here directory “forms” contains all .fmx (compiled version of forms)

2. Where are .fmb (Forms) stored
Ans àThey will be under $AU_TOP/forms/US

NOTE* .fmb and .fmx are different

3. When you run f60gen to compile the forms : .fmx will be put to where ..?

Ans à$PRODUCT_TOP/forms/US/  of respective product of form.

4. What is stored in “mesg” directory under each product top ??
Ans à“mesg” contains language specific message files and error message for the product.

5. What is stored in “bin” directory under each product top
Ans à“bin” directory contains executable files like in $AP_TOP/bin you will see APPBCF  APTZGF  apxamex.ctl  apxboav.ctl  apxdiner.ctl  apxgecmc.ctl  apxusbv.ctl  APXXTR

6. How to compile apps schema and when to compile??
Ans àYou can use “adadmin” utility to compile apps schema (other methods like utlrp.sql exists). Usually you compile apps  after application of the patches, maintanance patch, upgrade, runtime error due to AD_DDL packages or scenarios where there are invalid objects in apps schema.

7. How many database connections are allowed during fresh installation of oracle application.
Ans à100. (Number has increased to 250 for Pluggable Databases in 12c)

Patching:

1. Utility used to apply application patch is ??
Ans à “adpatch”

Note -- Various options available with adpatch are
option=nocheckfile, noautoconfig, nocompiledb, nocompilejsp, nocopyportion, nodatabaseportion, nogenerateportion, hotpatch, integrity, prereq, noprereq, nomaintainmrc, validate, nojcopy, noforcecopy, forcecopy, nolink, nogenform, nogenrep

2. For database patch utility used to apply patch is ??
Ans à“opatch”

Note -- .log and .lgi files for adpatch will be under $APPL_TOP/admin/SID/log directory.

3. What are .lgi files for ?

Ans à While applying patch put the system in maintenance mode by running
$adadmin  and then select option 5 (change maintenance mode) after that select options enable/disable maintenance mode. (You can also change system to maintenence mode using $AD_TOP/patch/115/sql/adsetmmd.sql)

4.By default where adadmin log file will go??
Ans à$APPL_TOP/admin/$SID/log/

5. How to check what adpatch is doing ??
Ans à“adctrl” is the utility to do check status of ad worker, including adpatch.

6. What are various stages of adpatch worker
Ans àFirst of all workers will be in “waiting” stage after that jobs will be “assigned” then “running“, If error doesn’t come it will move to “complete” (waiting -> assigned -> running -> complete)

If error comes it will go like waiting -> assigned -> running -> failed -> fixed -> restarted -> completed

7. What is Default number of workers:
Ans à2 times the number of CPU on the database server.

8. Have you ever seen worker status as deferred, while using adctrl ??
Ans àOnce you start applying patch , manager assigns jobs and unique ID to each worker. Manager will also insert one row into FND_INSTALL_PROCESSES table for each worker  with information about who is doing what ???

1) CONTROL_CODE 2) STATUS, these two columns are important for us from the fnd_install_processes. Manager will monitor the workers by these two columns about status of the assigned job.Once worker is done with first job, manager will assign it the second job to do with the status update as <assigned>.

Once all the worker’s status is completed for all the jobs, manager will tell the workers to shutdown and will drop fnd_install_process table.

- 

9. What happens if worker is working on some particular job say updating some table but that table is locked ??? so what the worker will do in such situation…

Ans àThat’s the AD_DEFERRED_JOBS table which will be created at the time of FND_INSTALL_PROCESSES and ad_deferred_jobs too will be dropped with FND_INSTALL_PROCESSES. First time when worker checks and find that table is locked, that job fails and manager will automatically defers the job, and it will assigns a new job to the worker.
    The job which was waiting in ad_deferred_jobs table will be assigned again till time it will not become failed or completed.

In such situation above, worker will wait till either the lock is released or timed out is reached and We (DBA) need not to take any action till the job status becomes fails…. once it shows its failed we can fix it and restart it.

10. Why APPS schema present in the 11i application ??
Ans à It reduces traffic because all the product schemas will grant full privileges to Apps schema. so it will have full access to the complete Oracle application.

All the products like AP,AR,FIN has the code for respective products and APPS will have access to all these code objectives too. Apps will have all the synonyms for base tables and sequences as well.

 OBJECT_NAME
——————————————————————————–
OBJECT_TYPE        STATUS  OWNER
—————— ——- ——————————
BEN_PL_PCP_S
SYNONYM            VALID   APPS

BEN_PL_PCP_TYP_S
SYNONYM            VALID   APPS

BOM_CTO_ORDER_DEMAND_S1
SYNONYM            VALID   APPS

Describe the AD Administration Main Menu  

  1.    Generate Applications Files menu

   2.    Maintain Applications Files menu  —Snapshot details

   3.    Compile/Reload Applications Database Entities menu ————-COMPILE APPS schema (invalid objects or runtime error)

   4.    Maintain Applications Database Entities menu  

   5.    Change Maintenance Mode —–While patching

   6.    Exit AD Administration

 Maintain Snapshot Information

   1.    List snapshots

   2.    Update current view snapshot

   3.    Create named snapshot

   4.    Export snapshot to file

   5.    Import snapshot from file

6.       Delete named snapshot(s)

Author – Akash Pramanik

Database Administrator in IBM

akash007.pramanik@gmail.com


---------------------------------------------------------------------------------------------------------------------

More Question Answers (more on RAC)

1. How to find the database/sqlplus version(R12 and R11) ?

 Ans : select banner from v$version;

2. Do you know some Cluster Vendor other than Oracle?
HP Tru64 Unix, Veritas, Microsoft

3. What is CRS?
Oracle RAC 10g Release 1 introduced Oracle Cluster Ready Services (CRS), a platform-independent set of system services for cluster environments. In Release 2, Oracle has renamed this product to Oracle Clusterware.

4. What is a raw partition?
A raw partition is a portion of a physical disk that is accessed at the lowest possible level. A raw partition is created when an extended partition is created and logical partitions are assigned to it without any formatting. Once formatting is complete, it is called cooked partition.

5. When to use CFS over raw?
A CFS offers:
- Simpler management
- Use of Oracle Managed Files with RAC
- Single Oracle Software installation
- Autoextend enabled on Oracle datafiles
- Uniform accessibility to archive logs in case of physical node failure
- With Oracle_Home on CFS, when you apply Oracle patches CFS guarantees that the updated Oracle_Home is visible to all nodes in the cluster.

Note: This option is very dependent on the availability of a CFS on your platform.

6. When to use raw over CFS?
- Always when CFS is not available or not supported by Oracle.
- The performance is very, very important: Raw devices offer best performance without any intermediate layer between Oracle and the disk.
Note: Autoextend fails on raw devices if the space is exhausted. However the space could be added online if needed.

7. What is VIP IP used for?
It returns a dead connection IMMIDIATELY, when its primary node fails. Without using VIP IP, the clients have to wait around 10 minutes to receive ORA-3113: “end of file on communications channel”. However, using Transparent Application Failover (TAF) could avoid ORA-3113.

8. How is possible to install a RAC if we don’t have a CFS?
This is possible by using a raw device.

9. What is a raw device?
A raw device is a disk drive that does not yet have a file system set up. Raw devices are used for Real Application Clusters since they enable the sharing of disks.

10. Where are the Clusterware files stored on a RAC environment?
The Clusterware is installed on each node (on an Oracle Home) and on the shared disks (the voting disks and the CSR file)

11. Where are the database software files stored on a RAC environment?

The base software is installed on each node of the cluster and the
database storage on the shared disks.

12. What kind of storage we can use for the shared Clusterware files?
- OCFS (Release 1 or 2)
- raw devices
- third party cluster file system such as GPFS or Veritas

13. What kind of storage we can use for the RAC database storage?
- OCFS (Release 1 or 2)
- ASM
- raw devices
- third party cluster file system such as GPFS or Veritas

14. What is a CFS?
A cluster File System (CFS) is a file system that may be accessed (read and write) by all members in a cluster at the same time. This implies that all members of a cluster have the same view.

15. What is an OCFS2?
The OCFS2 is the Oracle (version 2) Cluster File System which can be used for the Oracle Real Application Cluster.

16. Which files can be placed on an Oracle Cluster File System?
- Oracle Software installation (Windows only)
- Oracle files (controlfiles, datafiles, redologs, files described by the bfile datatype)
- Shared configuration files (spfile)
- OCR and voting disk
- Files created by Oracle during runtime

Note: There are some platform specific limitations.

17. How is possible to install a RAC if we don’t have a CFS?
This is possible by using a raw device.

18. What is a raw device?
A raw device is a disk drive that does not yet have a file system set up. Raw devices are used for Real Application Clusters since they enable the sharing of disks.

19. What is the Cluster Verification Utility (cluvfy)?

The Cluster Verification Utility (CVU) is a validation tool that you can use to check all the important components that need to be verified at different stages of deployment in a RAC environment.

20. What are Oracle Clusterware Components

Voting Disk — Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures. The voting disk must reside on shared disk.

Oracle Cluster Registry (OCR) — Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR must reside on shared disk that is accessible by all of the nodes in your cluster

 

Author – Akash Pramanik

Database Administrator in IBM

akash007.pramanik@gmail.com