Oracle Apps DBA R12 Interview Questions

Oracle Apps DBA R12 Interview Questions

Question.  What do we have in FND_NODES?

Answer:  FND_NODES table contains information about node_names and services enabled on a node.  In multinode instance if you want to know which node is running what services, You can query the fnd_nodes and get that information.

Question.  What is multi node system ?

Answer:  We will install Apache, Forms, and Concurrent on different nodes.

Question.  List out Profile Options in Useful for Oracle Apps DBA?

Answer:  Applications Help Web Agent
Applications Servlet Agent
Applications Web Agent
Concurrent: Active Request Limit
Concurrent: Hold Requests
Concurrent: Multiple Time Zones
Concurrent: Report Access Level
Concurrent: Report Copies
Concurrent: Request priority
Database Instance
Enable Security Group
FND: Debug Log Filename
FND: Debug Log Level
Forms Runtime Parameters
Gateway User ID
ICX: Discoverer Launcher
ICX: Forms Launcher
ICX: Report Launcher
ICX: Limit Connect
ICX: Limit time
ICX: Session Timeout
MO Operating Unit
Node Trust Level
RRA: Delete Temporary Files
RRA: Enabled
RRA: Service Prefix
RRA: Maximum Transfer Size
Self Service Personal Home Page Mode
Sign-On: Audit Level
Signon Password Failure Limit
Signon Password Hard to Guess
Signon Password Length
Signon Password No Reuse
Site Name
Socket Listener Port
TCF: Host
TCF: Port
TWO TASK
Viewer: Text

Question.  Can you enable diagnostics for EBS?

Answer:  Yes. Enable profile option FND:Diagonistic.

Question.  How To find latest patchset level for modules installed in Oracle Apps R12?

Answer:  select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME.

Question.  What is adsplice utility ?

Answer:  We can use adsplice to add a new product.

Question.  How to find out Patch level of mini Pack in Oracle Apps R12?

Answer:  Select product_version,patch_level from FND_PROUDCT_INSTALLATIONS where patch_level like ‘%&shortname%’;
Replace short name by name of Oracle Apps Minipack for which you want to find out Patch level . ex.
AD – for Applications DBA
GL – for General Ledger
PO – Purchase Order

Question.  When do we run FND_CONC_CLONE.SETUP_CLEAN ?

Answer:  FND_NODES table contains node information, If you have cloned test instance from production still the node information of production will be present after clone in the test instance.
we use FND_CONC_CLONE.SETUP_CLEAN to cleanup FND_NODES table in the target to clear source node information as part of cloning.
Below syntax to execute:

SQL> sho user
USER is “APPS”
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

This will delete all the entries in the fnd_nodes table, to populate it with target system node information, Run autoconfig on DB node and Applications node.

Question.  What are various options available with adpatch ?

Answer:  Its depending on your AD version, which includes noautoconfig, check_exclusive, checkfile, nocompiledb, nocompilejsp, nocopyportion, nodatabaseprtion, nogenerateportion, hotpatch, integrity, maintainmrc, parallel, noprereq, validate

Question.  How To find the latest Oracle application R12 version ?

Answer:  select ARU_RELEASE_NAME||’.’||MINOR_VERSION||’.’||TAPE_VERSION version, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS “how it is done”,BASE_RELEASE_FLAG “Base version” FROM AD_RELEASES where END_DATE_ACTIVE IS NULL.

Question.  Describe how many different types of patches are there in Oracle Apps R12.

Answer:  One-off, mini, diagnostics, cu, rup, language, help, platform specific,,Interoperability,family pack, maintenance pack.

Question.  How to know, what all has been done during application of PATCH

Answer:

Select J.PATCh_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top,D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B,AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G,
AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J where A.BUG_ID = B.BUG_ID and
B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID
and D.FILE_ID = F.FILE_ID and G.APPL_TOP_ID = H.APPL_TOP_ID and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and
I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and B.PATCH_RUN_ID = G.PATCH_RUN_ID and C.EXECUTED_FLAG = ‘Y’ and
G.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = ‘merged’))
GROUP BY J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME, D.SUBDIR,
D.FILENAME, E.ACTION_CODE;

Question.  Does rapid clone takes care of Updating Global oraInventory or you have to register manually in Global OraInventory after clone ?

Answer:  Rapid Clone will automatically Update Global oraInventory during configuration phase.

Question.  How to find out if any patch except localisation patch is applied or not, if applied, then what all drivers it contained and time of it’s application?

Answer:  select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.PATCH_DRVIER_ID, B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATIONG_DATE, B.FILE_SIZE, B.MERGED_DRIVER_FLAG, B.MERGE_DATE from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID and A.PATCH_NAME = ” ;

Question.  What is MRC ? What you do as Apps DBA for MRC ?

Answer:  MRC also called as Multiple Reporting Currency in oracle Apps. Default you have currency in US Dollars, but if your organization operating books are in other currency then Apps DBA needs to enable MRC in Apps.

Question.  List out few Apps related tables ?

Answer:
CONCURRENT REQUEST/PROGRAM/MANAGERS
———————————–
FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE

FND/AOL Tables
————–
FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_APPLICATION
FND_PROFILE_OPTION_VALUES

AD/Patches
————-
AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS

Question.  Where is Jserv configuration files stored ?

Answer:  Jserv configuration files are stored in $IAS_ORACLE_HOME/Apache/Jserv/etc

Question.  How To find what is being done by the patch?

Answer:  select A.BUG_NUMBER “Patch Number”, B. PATCh_RUN_BUG_ID “Run Id”,D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and A.BUG_NUMBER = ” and B.PATCH_RUN_BUG_ID = ‘ < > ‘ and C.EXECUTED_FLAG = ‘Y’ GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID, D. APP_SHORT_NAME, D>SUBDIR, D.FILENAME, E.ACTION_CODE;

Question.  Where is Concurrent Manager log file location in Oracle Apps R12 ?

Answer:

a. $APPLCSF/$APPLLOG
b. $FND_TOP/log

Question.  How To find all available application version?

Answer:  select ARU_RELEASE_NAME||’.’||MINOR_VERSION||’.’||TAPE_VERSION version, START_DATE_ACTIVE when updated, END_DATE_ACTIVE “when lasted”, CASE WHEN BASE_RELEASE_FLAG = ‘Y’ Then ‘BASE VERSION’ ELSE ‘Upgrade’ END “BASE/UPGRADE”, ROW_SOURCE_COMMENTS “how it is done” from AD_RELEASES;

Question.  Adident utility is used for what in Oracle Apps R12?

Answer:  To find version of any file. AD Identification. for ex. “adident Header FileName”

Question.  How verify the sysadmin password from command line?

Answer:  This utility can be used to verify the GUEST/ORACLE password

SQL> select fnd_web_sec.validate_login(‘SYSADMIN’,'<sysadmin_password>’)from dual;

If it returns Y then sysadmin password is correct

If it returns N then sysadmin password that we are using
Eg:
SQL> select fnd_web_sec.validate_login(‘SYSADMIN’,’WELCOME123′) from dual;
FND_WEB_SEC.VALIDATE_LOGIN(‘SYSADMIN’,’SYSADMIN123′)
——————————————————————————–
N
SQL> select fnd_web_sec.validate_login(‘SYSADMIN’,’SYSADMIN’) from dual;
FND_WEB_SEC.VALIDATE_LOGIN(‘SYSADMIN’,’SYSADMIN’)
——————————————————————————–
Y

Question.  How To get file version of any application file which is changed through patch application?

Answer:  select A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME, max(B.VERSION) from AD_FILES A, AD_FILE_VERSIONS B where A.FILE_ID = B.FILE_ID and B.FILE_ID = 86291 group by A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME;

Question.  List out the modules related to oracle Apps DBA ?

Answer:

FND = Application Object Library
AU = Application Utilities
AD = Application DBA

Question.  How to know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id … patch run id ?

Answer:  select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.INSTANCE_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRVIERS C, AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = ”)) ORDER BY 3;

Question.  What is .dbc file , where its stored , whats use of .dbc file ?

Answer:  dbc as name says is database connect descriptor file which stores database connection information used by application tier to connect to database. This file is in directory $INST_TOP/admin/fnd/12.0.0/FND/secure also called as FND_SECURE.

Question.  Howto find the base application version

Answer:  select ARU_RELEASE_NAME||’.’||MINOR_VERSION||’.’||TAPE_VERSION version, START_DATE_ACTIVE when updated, ROW_SOURCE_COMMENTS “how it is done” from AD_RELEASES where BASE_RELEASE_FLAG = ‘Y’

Question.  Can you apply patch without putting Applications 11i in Maintenance mode ?

Answer:  Yes, use options=hotpatch as mentioned above with adpatch.

Question.  How To get information related to how many time driver file is applied for bugs ?

Answer:  select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = ”

Question.  Whats main concurrent Manager types.

Answer:

ICM = Internal Concurrent Manager which manage concurrent Managers
Standard Managers = Manage processing of requests
CRM = Conflict Resolution Managers , resolve conflicts in case of incompatibility

Question.  How To find Merged patch Information from database in Oracle Applications

Answer:  select bug_number from ad_bugs where bug_id in ( select bug_id from ad_comprising_patches where patch_driver_id =(select patch_driver_id from ad_patch_drivers where applied_patch_id =&n) );

Question.  I am applying a patch , can I open another session and run adadmin ?

Answer:  Yes, unless you are running a process where workers are involved

Question.  I am applying a patch , can I open another session in another node and run adpatch?

Answer:  No

Question.  List some profile options with their function?

Answer:

ICX: Session timeout
ICX: Forms Launcher
ICX: Limit Connect

Nagulu Polagani

"We are all apprentices in a craft where no one ever becomes a master."

Latest posts by Nagulu Polagani (see all)