(转)Oracle Apps DBA R12 Interview Questions

By  Nagulu Polagani 

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