Interview Questions and Answers on Oracle Apps DBA
1. What is Edition Based Redefinition (EBR) in Oracle?
Edition Based Redefinition (EBR) is a new feature introduced from Oracle Software Version 11g R2.Edition-based redefinition allows multiple versions of PL/SQL objects, views and synonyms in a single schema. This feature facilitates Online patching of database objects in R12.2 online Patching.
Editionable and Non-editionable Objects
Below schema object types are editionable in the database:
SQL translation profile
All PL/SQL object types:
PACKAGE and PACKAGE BODY
TYPE and TYPE BODY
If a schema object type is editionable in the database, then it can be editionable in schemas. All other schema object types are noneditionable in the database and in every schema, and objects of that type are always noneditioned. Tables are always noneditioned objects.
To view the editions related information in the database, use the below datadictionary tables.
DBA_EDITIONS: SELECT * FROM dba_editions;
DBA_OBJECTS_AE: DBA_OBJECTS_AE view shows all objects, of all editions
2. What is the difference between ad_bugs and ad_applied_patches?
Ans: If a patch is applying multiple bug fixes, the details of all bugs fixed by the patch can be found from ad_bugs, ad_applied_patches has information only about patches applied using adpatch.
Two tables to check if the patch is applied or not:
This table includes the defined bugs on the system:
SELECT bug_number FROM apps.ad_bugs WHERE bug_number LIKE '%'&patchnum'%';
This table includes patches applied on the system:
SELECT patch_name FROM apps.ad_applied_patches WHERE patch_name LIKE '%'&patchnum'%';
3. What is the significance of FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS table?
FND_INSTALL_PROCESSES table will store the worker information like what job is assigned to which worker and its status. AD_DEFERRED_JOBS will come into picture when some worker is failed, it will be moved to AD_DEFERRED_JOBS table, from where again adpatch will take that job and try to resign, after doing this 3 times if still that worker is failing, then adpatch will stop patching and throw the error that perticular worker has failed. We need to trouble shoot and restrart the worker.
4. While applying a patch if that patch is failing because of a pre-req then how you will apply that pre-req patch and resume with the current patch?
We need to take the backup of FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables and restart directory at APPL_TOP/amdin/SID and then use adctrl to quit all the workers. Then apply the pre-req patch , after that rename u r restart directory to its original name and create FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables from the bcakup tables. Start adpatch session and take the options want to continue previous session.
5. What are the differences between Oracle Applications R12.1.x and R12.2.x
From DBA perspective, below are the changes in R12.2.x version of Oracle Applications, compared to R12.1.3.
1. Dual FS: Oracle Application Release 12.2 has dual filesystems(RUN FS and PATCH FS) to facilitate Online Patching.This Requires double the mountpoint space at MiddleTier Level.
2. Weblogic Server: Oracle HTTP Server in R12.1 is replaced by Oracle Weblogic Server.
10.1.3 Home is replaced by FMW_HOME.Weblogic Server Manages all oacore, forms and oafm servers in R12.2.
3. Online Patching : Dual Filesystem and EBR feature in 11gR2 Database facilitate online patching. Online Patching minimizes the downtime required for patching.adpatch utility in R12.1.x is replaced by adop utility. adop cycle runs in 5 Phases : prepare,apply,finalize,cutover and cleanup. adop internally calls adpatch utility to run the driver files associated with adop phases.
4. APPS Password Change: After Changing Apps Password in R12.2, we need to update the new password in EBS Datasource used by Weblogic Domain.To know more about apps password change in Oracle applications R12.2, check the below link.
5. Cloning : While Cloning of R12.2.x adcfgclone instance needs to be run twice, on RUN fs and PATCH fs.From 12.2.5 Version (OR R12.AD-TXK Delta 7), we have the dualfs option which creates both run fs and patch fs during clone process.
6. What is the difference between dbtechstack,dbtier and dbconfig modes in Cloning?
While Cloning Oracle Applications, adcgclone can be executed in 3 Modes on Database node.
- perl adcfgclone.pl dbTier
It configures the ORACLE_HOME on the target database node and recreate the controlfiles.
This is used in case of standby database/hot backups.
- perl adcfgclone.pl dbTechStack
It configures the ORACLE_HOME on the target database tier node only. Relink the oracle home.
The below steps has to be performed manually
1. Create the Target Database control files.
2. Start the Target System Database in open mode
3. Run the library update script against the Database
cd $RDBMS_ORACLE_HOME/appsutil/install/[CONTEXT NAME]
sqlplus "/ as sysdba" @adupdlib.sql [libext]
Where [libext] should be set to 'sl' for HP-UX, 'so' for any other UNIX platform,
or 'dll' for Windows.
- perl adcfgclone.pl dbconfig
It configures the database with context file. Database must be open while running adcfgclone in dbconfig mode.
perl adcfgclone.pl dbconfig <Context_file>
Where Target Context File is: