March 28, 2015

Oracle AppsDBA Patching Interview Questions/FAQs Part2

Oracle Apps DBA EBS (E-Business Suite)
Patching Interview Questions/FAQs Part2

21. List out the Log Files created on running patch?
Running the patch creates the following log files
• Adpatch.log: Information about the patch run will be stored here.
• Adpatch.lgi: It contains information which has been discarded by adpatch.log. For example, the files which has not been copied by the adpatch.
• Adrelink.log: Relinking information performed by patch will be stored here.
• Adworkxx.log: Workers log details will be stored here.

22. Is it possible to revert the patch application? Explain in detail how?
• Yes, it’s possible to revert the patching but not 100% sure.
• In test environment it’s always recommended to take the full backup of the database, apply the patch and revert from backup.
• Adpatch is responsible for copying the files from the patch.
• From the patch log, drop the database objects created by the patch.
• Similarly forms in the G driver should be replaced from the backup taken by adpatch and should be manually generated.

23. What things you do to reduce patch timing?
• Admrgpch utility is used for merging all the patches.
• Various adpatch options like nocompiledb or nocompilejsp are used for reducing patch timing.
• By Using defaults file.
• By using, staged APPL_TOP for upgradation.
• By increasing the batch size, this might result into negative results.

24. What is copy driver (C driver) and what does it do?
• All the files in the patch are copied to APPL_TOP by C driver.
• It extracts all the appropriate files from c library.
• Oracle applications products are relinked by C driver.
• Regenerates the JAR files and compiles the Java server pages (JSP) files.
• Compares the files in the patch with the files in the $APPL_TOP.
• If all the files in the patch are of higher version, adpatch copies all the files from patch to $APPL_TOP.

25. What is database driver (D driver) and what does it do?
• It contains all the commands to change the database object.
• All the scripts copied by c driver are applied to the database.
• It makes a list of all the invalid objects that are there in the database.
• Runs SQL scripts which make changes to the database objects.
• Compiles all the invalid objects that are there in the database.

26. What is generate driver (G driver) and what does it do?
• Generate driver is named as g<patch number>.drv
• It regenerates all forms, reports and pl/sql libraries that have been affected by the patch.

27. What is unified driver (U driver) and what does it do?
• Unified driver is a combination of C,D, & G drivers.
• U driver is also named as u<patch_number>.drv
• It requires only a single execution of AutoPatch.

28. I am applying a patch, can I open another session and run adadmin?
Ans:
Yes, unless you are running a process where workers are involved.

29. I am applying a patch, can I open another session in another node and run adpatch?
Ans:
No

30. How often do you patch?
Ans: Usually for non-production the patching request comes around weekly 4-6 and the same patches will be applied to Production in the outage or maintenance window.
Production has weekly maintenance window (E.g. Sat 6PM to 9PM) where all the changes (patches) will applied on production.

31. How to find out if any patch except localization 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 = ''

32. 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; 

33. How to get information related to how many times 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 = '';

34. 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;

35. 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) );

36. How to 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;

37. How to find out patch level of minipack?
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

38. What is the significance of FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS table?
Ans: 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 particular worker has failed. We need to troubleshoot and restart the worker.

39. If it is a multinode installation which driver we need to apply on which node?
Ans: c,d,g on concurrent node and c, g on web node. If it is u-driver we need to apply on all nodes.

40. While applying an application patch is that necessary that database and listener should be up?
Ans: Yes. Because adpatch will connect to database and update so many tables etc…

41. 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?
Ans: We need to take the backup of FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables and restart directory at APPL_TOP/admin/SID and then use adctrl to quit all the workers. Then apply the pre-req patch, after that rename restart directory to its original name and create FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables from the backup tables. Start adpatch session and take the options want to continue previous session.

42. How adpatch knows what are the pre-req’s for the patch which it is applying?
Ans: With every patch a file called b.ldt file will be delivered which contain the pre-req information. adpatch load this into database using FNDLOAD and check, whether those pre-req patches were applied or not.

43. What c-driver will do?
Ans:
C-drive copies the files from patch unzipped directory to required location in application file system. Before copying it will check the file version of the existing file at the file system with the file version of the file in the patch. If the patch file version is higher than what it is at file system level then only c-driver will copy that files.

44. How adpatch will know the file versions of the patch delivered files?
Ans:
With each patch a file with name f.ldt is delivered, which contain the file versions of the files delivered with the patch. adpatch will use this file to compare the file versions of files its delivering with the file on file system.

45. What is the adpatch log file location?
Ans : $APPL_TOP/admin/SID/log

46. How you will know what are the files the patch is going to change just my unzipping the patch?
Ans:
When we unzip a patch it will keep all the files related to a particular product under that directory inside u r patch directory for example if the patch delivering files related to FND product then it will create a sub directory under the patch directory with the name FND in which it will put all related files to that product

47. What is the significance of backup directory under patch directory?
Ans:
When we apply a patch it will keep the copy of the files which it’s going to change in file system.

48. What are the different modes you can run your adpatch?
Ans :
1.Interactive – default mode
2.Non interactive – Use defaults files to store prompt values (adpatch defaultsfile= interactive=no)
3. Test – Without actually applying a patch just to check what doing. (adpatch apply=no)
4. Pre-install – (adpatch preinstall=y)
This mode will be useful to decrease upgrade downtime as its applies bus fixes without running SQL, EXEC and generate portion of patch.

49. When a patch delivers java files what extra file you will get when you unzip the patch, other than driver and readme files?
Ans : j.zip52.

50. What is apps.zip/appsbrog2.zip file?
Ans : apps.zip/appsbrog2.zip is the patchable archive of all java class files required for oracle application. Apps.zip was used to old application version, but from 11.5.8 onwards its appsbrog2.zip

50. What is the location of apps.zip/appsbrog2.zip?
Ans : AU_TOP/java and JAVA_TOP

51. How to skip copy portion while applying a patch?
Ans : Adpatch options=nocopyportion

52. How to merge patches and what type of patches can be merged?
Ans : admrgpch. We can merge any kind of application patches, if any of the patch contain a u-driver then merged patch will contain u_merged.drv otherwise c_merged.drv, d_merged.drv and g_merged.drv

53. How to find opatch is enabled or not for you r database?
Ans : If Opatch directory exists under RDBMS_ORACLE_HOME.

54. How to find out what are the rdbms patches applied to an oracle home?
Ans :
1. opatch lsinventory
2. $RDBMS_ORACLE_HOME/.patch_storage directory contains the directories with the rdbms patch numbers, which are applied to this oracle home.

55. Is that necessary to enable maintenance mode while applying a patch?
Ans : We can even apply a patch without enabling maintenance mode with the following option
adpatch options=hotpatch

56. While applying a rdbms patch using opatch you are getting the error, unable to read inventory/inventory is corrupted/ORACLE_HOME is not registered, what you will do, and how you will apply the patch?
Ans: We will check the inventory directory permission, try to apply the patch after giving 777 permissions to that inventory directory. If still it won’t work we will apply patch with the following command:
opatch apply no_inventory

57. What are the patch errors, you have encountered?
Ans :
1) Patch fails with the error, unable to generate particular form, do u want to continue. We continue patching by saying “yes”, and then we manually regenerate the form using f60gen utility.
2) Unable to generate jar files under JAVA_TOP AutoPatch error: Failed to generate the product JAR files Solution: Run adjkey -initialize -----------to creat identitydb.obj file which will be used by adjava to sign jar files.

Related Articles:  Oracle DBA Interview Questions/FAQs  Oracle RAC Interview Questions  Oracle Exadata Interview Questions