1Z0-233 : Oracle Apps DBA Certification Questions

Its time to crack 11i certification (check our previous post here) and rush towards 12i Certification.
       I believe basic knowledge (which script does what, which table is useful for what purpose and How to use the ad* utilities….) is enough with some hands on to clear Oracle Apps DBA Certification Track.

so let’s start with some basic things

Patching:
X. Utility used to apply application patch is ??
“adpatch”

X.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

X. For database patch utility used to apply patch is ??
“opatch”

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

X. What are .lgi files for ?

X. 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)

X.By default where adadmin log file will go??
$APPL_TOP/admin/$SID/log/abc.log

-

Workers:
X. How to check what adpatch is doing ??
“adctrl” is the utility to do check status of ad worker, including adpatch.

X. What are various stages of adpatch worker
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

X. What is Default number of workers:
2 times the number of CPU on the database server.

-
Deferred jobs

Have you ever seen worker status as deferred, while using adctrl ??
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 thefnd_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.

- 

X. 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…

here comes AD_DEFERRED_JOBS table which will be created at the time ofFND_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.

X. Why APPS schema present in the 11i application ??
Well 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

File System
X. What all directories will be there under any “product” top.  (To know more about various top’s in 11i click here)

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)

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

Note* .fmb and .fmx are different

X. When you run f60gen to compile the forms : .fmx will be put to $PRODUCT_TOP/forms/US/  of respective product of form.

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

X. What is stored in “bin” directory under each product top
“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

X. How to compile apps schema and when to compile??
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.

X. How many database connections are allowed during fresh installation of oracle application.
100.

-
ADADMIN :- day to day use 

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)

Hence question on maintenance snapshot can be like “What all maintain snapshot can do”
a. You can create copy of existing snapshot.
b. You can create a list of current view and named snapshot stored in your system.
c. You can update current view snapshot with any changes to the snapshot since the last update
d. You can export an existing snapshot to a file for storage or to another system.
e. You can delete the current view snapshot

 And answer is  We can export/import snapshot:Delete named snapshots (You can’t delete current view snapshot):Update current view snapshot:Create named snapshot and list all the present snapshots too .