Oracle Application DBA Interview Questions - III
Q)What is your day to day activity as an Apps DBA?
Ans:
As an Apps DBA we monitor the system for different alerts (Entreprise Manager or third party tools used for configuring the Alerts) Tablespace Issues, CPU consuption
Database blocking sessions..etc
Regular maintanance activies like cloning,patching,custom code migrations (provided by developers)
Working with user isses.
Q)How often Do you patch?
Ans: Usually for non-production the patching request comes aroung weekly 4-6 and the same patches will be applied to Production in the outage or maintanance window.
Production has weekly maintance window (Eg Sat 6PM to 9PM) where all the changes (patches) will applied on production.
Q)How often Do you clone?
Ans: Cloning happens biweekly or monthly depending on the organization requierement.
Q)What change control/management or CCB?
Ans: Every organization has change control process, Change control process is no change goes into production witout proir testing on non-production instance.
Eg: If a user encouters an issue in production instance and the fix for the issue is known, Still the fix should not be applied directly on production, as it is not tested.
The same fix need to be first applied on a non-prod instance where the similar issue is happening and test the issue and instance stability.
Once user is happy with the results the change or fix will be implemented to production with the approval from Change control Board, CCB is a managemant team who reviews all the changes being deployed to production,
Depending on the need and criticality and testing results they approve the change movement to production instance.
Q)How much time does it take to upgrade, clone ?
Ans: Clone usually takes around 48hrs to copy and configure and upgrade depends on the database size and module involved.
upgrade from 11.5.9 to 11.5.10.2 will take around 3-4 days and 11i to R12 upgrade will take around 4-5 days.
Q)What is the meaning QA,CRP,SIT,DEV,UAT,PRE-PROD,PROD Instance?
QA - Tesing Instance
CRP - Conference Room Pilot
SIT - System Integration Testing
DEV - Developement
UAT- User Acceptence Testing
STAGE - Pre-production Instance
Prod - Production/actuall instance where the business is running
Q)What do we have in FND_NODES?Ans:
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.
Q)when do we run FND_CONC_CLONE.SETUP_CLEAN ?
Ans:
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.
Q)How verify the sysadmin password from command line?
Ans:
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','SYSADMIN123') 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
Q)List out few Apps related tables ?
Ans:
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
Q) How To find the latest application version
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
Q) How to find out if any patch except localisation patch is applied or not, if applied, that what all drivers it contain and time of it's application
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 = ''
Q) 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 */
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;
Q) Howto find the base application version
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'
Q) How To find all available application version
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
Q) How To get file version of any application file which is changed through patch application
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
Q) How To get information related to how many time driver file is applied for bugs
select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = ''
Q) How To find latest patchset level for module installed
select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME
Q) How To find what is being done by the patch
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
Q) How To find Merged patch Information from database in Oracle Applications
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) );
Q) How toto know, what all has been done during application of PATCH
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
Q) How to find out Patch level of mini Pack
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
Q)List out Profile Options Useful for Oracle Apps DBA?
Here is the list of few profile options which Apps DBA use frequently.
**It is not necessary that you as Apps DBA must know all profile options**
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
