close

Oracle Apps DBA Set 6

Interview Questions for Oracle Apps DBA’s (11i/R12) – Part 9
Few more Interview questions for Apps DBA’s

Q. I am applying database patch using opatch. I don’t have oraInventory, can I apply database patch ?
No, you can’t apply a database patch using opatch if Inventory is missing.
Register ORACLE_HOME with global Inventory (in case inventory is missing) using steps mentioned here “3.6.2 Creating the Central Inventory”

Q. How many concurrent mangers can be used ? what is the limit ?
There is no such documented limit on number of concurrent managers, as long as you have enough operating system resource (memory).

Q. Suppose my concurrent manager status is showing Inactive ? what is the reason ?
This could be because of multiple reasons, to name few

a) Processes value for Concurrent Manager is 0 (System Administrator > Concurrent > Manager > Define > search for Manager > Click on the Work Shifts button and check value of Processes)

b) Concurrent Manager was shut down by some one (Check using System Administrator > Concurrent > Manager > Administer)

c) There is no workshift defined against Concurrent Manager (or workshift removed from Manager)

.

Q. What is transaction manager ? what is the significance ?
Normal/Standard concurrent manager execute long running, data-intensive application program asynchronously, where as transaction manager support “synchronous” processing of particular requests.

For more info and significance of transaction manager Check Oracle Applications System Administrator’s Guide – Configuration (zipped) Here (Page 176)

Q. My cloning is successfully completed but my apps listener is not getting up ? What is the problem ?
First check log file of apps listener to find root cause at
1. $OAD_TOP/admin/log/$CONTEXT_NAME/adalnctl.txt
2. $ORACLE_HOME/network/admin/apps_$SID.log (on Middle Tier)

To name few
1. Failure to create proper apps listener file via adgentns.sh during Rapid Clone process
2. FNDSM (Service Manager issues)

Q. How frequently do you preform patching ? (For work ex < 1.5 yrs)
Patching is done mainly to fix existing problem (bug) or for new features (enhancements). Frequency of patching vary from company to company and implementation stage.

Q. How frequently you perform cloning ? (For work ex < 1.5 yrs)
This again vary from company to company. Frequency ranging from every few days or every few weeks to every few months.

Q. How much time it will take to complete cloning ?
Cloning time depends on
– Network speed to copy various tops (APPL_TOP, COMMON_TOP.., DATA_TOP)
– Customizations (printer setting and any post clone issue)
– Topology (Number of middle tier, is system integrated with OID & SSO, Database is RAC or not, DMZ setup)

Time for Rapid clone for single node
Approx. 30 Minutes for pre-clone
Approx. 1-2 hours for post clone on database tier
Approx. 1-2 hours for Middle tier
+
Time to copy files from source instance to target Instance

Q. What is the purpose of the appsutil folder in ORACLE_HOME in database tier ?
appsutil directory in database ORACLE_HOME is place holder for Autoconfig and Rapid Clone related files on database Node.

Q. After cloning my oraInventory got corrupted ? can I apply my Opatch?
No, you can’t apply database patch using opatch (Though you can apply apps patch using adpatch). Recreate inventory as mentioned above.

Q. what command is used to kill the process which is using largest memory ?
Vary as per operating system if you wish to kill o.s. process (kill -9 <pid>)

Q. I am applying patch can I open another session and run adadmin ? (Thanks to Amit Pancholia for pointing this out)
Yes & No (depends on stage of patching), you can run adadmin if FND_INSTALL_PROCESSES is NOT locked (in use) by adpatch session. If this table is in use by adpatch session then you can’t run adadmin at same time.

Q. I am applying patch can I open another session in another node and run adpatch ?
Well, question is tricky. If you are using shared appl_top (application tier) and using distributed patching (a.k.a. Distributed AD) feature then yes you can start patch from one node giving total number of workers and start X number of workers from node1 , then start another adpatch session from second node mentioning remaining workers to start from second node. (More on distributed patching in shared application tier environment coming soon …).
Q. I am not able to start concurrent manager ? What is the problem ?
This could again be for various reasons, to check root cause of problem check log file at
$APPLCSF/$APPLLOG/$SID_MMDD.mgr (where MM is month & DD is Date on which Concurrent Manager was started)

 

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

1. What is a one-off patch?
Ans : An one-off patch is a small patch (of without any pre-requisites) to fix a bug.

2. What is a mini pack?
Ans : A mini pack is one which will upgrade any product patchset level to next level (like AD.H to AD.I).

3. What is Family pack?
Ans : A Family pack is one which will upgrade the patchset level of all the products in that family to particular patchset level.

4. What is Maintenance pack?
Ans : A maintenance pack will upgrade applications from one version to another (like 11.5.8 to 11.5.9).

5. What is a Rollup patch?
Ans : A rollup patch is one which will deliver bug fixes identified after the release of any major application versions (like 11.5.10.2/12.1.0).

6. What is consolidated patch?
Ans: Consolidated patches will come into picture after upgrades from one version of applications to another, all post upgrade patches will a consolidated and given as consolidated patch.

7. How you apply a patch?
Ans : adpatch

8. How to find latest patchset level for module installed?
Ans : select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME;

9. How you will find whether a patch is applied/not?
Ans : Query ad_bugs.

10. What is the other table where you can query what are the patches applied?
Ans : ad_applied_patches.

11. How to find out which patch driver is applied (like c,d,g or u)?
Ans: Query ad_patch_drivers.

12. How to find out whether a language patch is applied for a particular patch?
Ans : Query ad_patch_driver_langs.

13. What is the difference between ad_bugs and ad_applied_patches?
Ans: A patch can deliver solution for more than one bug, so ad_applied_patches may not give the perfect information as in case of ad_bugs.

14. What inputs you need to apply a patch other than driver name and etc?
Ans : apps and system passwords

15. What are the tables adpatch will create and when?
Ans : Adpatch will create FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS table when it will apply d,g and u drivers.

16. What is a patch and name some different types of patches?
Ans : Patch is a program which fixes the bug.

There are 4 different types of patches

• One-off patch:
o This is the simplest type of patch. It is created to resolve a particular problem.

• Mini pack Patch:
o It is a collection of one-off patches related to a particular module or product.
o Mini Pack version of module is denoted by Alphabetic characters.

• Family pack patch:
o This is a collection of mini pack patches in one family.
o Alphabetic characters denote the family pack version.

• Maintenance pack patch:
o This is a collection of family pack patches.
o Oracle Applications Release 11.5.10 is an example of Maintenance pack.
rollup patches,
consolidated patches.

17. What are the contents of patch and drivers present in patching?
Ans : Contents of patch:
• Readme files
• Driver files
• Metadata files
• Replacement files

Drivers present in patching:
• C (copy) driver – It copies all the files and links the executables.
• D (database) driver – It is responsible for running SQL scripts which updates the database.
• G (generate) driver – It is responsible for generating forms, reports and message files.
• U (unified) driver – It is a united driver containing all copy and database actions.

18. What do you do if patch fails?
Ans :
• Evaluate log files to determine the cause of the error.
• Repair the cause of error.
• Now restart adpatch.
• Answer ‘Yes’ when adpatch asks if you want to continue the previous session.
• Adpatch starts from where it left off by skipping all completed steps.

19. How can I determine the effects a patch will have on my application system?
Ans :This can be done by patch wizard in the Oracle Applications manager.
• Checking total number of files in the patch and which are installed.
• Products that contain updated files.
• Total number of files introduced by the patch.
• Files on the target system changed by the patch.
• Files which depend on patched files.

20. How can you reduce the downtime when you have to apply multiple patches?
Ans :
• You can reduce the downtime by merging all the patches into one single patch using admergepatch (admrgpch) tool.
• You can also apply multiple patches one by one by choosing options nocompiledb, nocompilejsp, and nolink.
• Compilation of invalid objects, jsp’s and relinking can be skipped till the last patch is applied.
• In the last patch you can compile all of them and then relink.
• You can also choose the max number of workers which your CPU permits.

================================================================================================

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. When 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. When 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 it 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.
=======================================================================

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

1. What is cloning and why is it required?
• Cloning is the process of creating an identical copy of the Oracle application system.
It is required due to following reasons
• Creating a test copy of your production system before upgrading.
• Moving an existing system to a different machine.
• To test some patches
• Creating a development copy of your environment to be used by the developers.

2. What is rapid clone?
Ans : Rapid Clone is the new cloning utility introduced in Release 11.5.8. Rapid Clone leverages the new installation and configuration technology utilized by Rapid Install

3. How do I determine if my system is rapid clone enabled?
Ans : First, verify system is AutoConfig enabled. Then, verify that you have applied the latest Rapid Clone patch.

4. Explain the cloning process?
Ans :
1. Run adpreclone as applmgr and oracle user on source Perl adpreclone.pl dbTier as oracle user Perl adpreclone.pl appsTier as applmgr user
2. Take the cold/hotbackup of source database
3. Copy the five directories appl,comn,ora , db,data to target
4. Rename the directories, and change the permisssion
5. Set the inventory in oraInst.loc
6. Run perl adcfgclone.pl dbTier as oracle user,if the backup type is cold
7. If the backup type is hotbackup then Perl adcfgclone.pl dbTechStack. Create the control file on target from the control script trace file from source Recover the database Alter database open resetlogs
8. Run autoconfig with the ports changed as per requirement in xml.
9. Run perl adcfgclone.pl appsTier as applmgr
10. Run autoconfig with the ports changed as per requirement in xml.

5. What are the steps to clone from a single node to a multi-node?
• You must login as the owner of file system once the database cloning is done.
• Run the adcfgclone.pl from the common_top/clone bin.
• Accept for target system having more than one application tier server node.
• Collect the details for processing node, admin node, forms node, and web node.
• Now you get a prompt for the various mount point details and it creates the context file for you.
• Follow the same steps from all the nodes.

6. What are the files you need to copy from APPL_TOP for creating a clone application system?
• APPL_TOP
• OA_HTML
• OA_JAVA
• OA_JRE_TOP
• COMMON_TOP>/util
• COMMON_TOP>/clone
• 806 ORACLE_HOME
• iAS ORACLE_HOME

7. Does clone preserve the patch history?
• Yes, Rapid clone preserves the patch history in following locations
• RDBMS ORACLE_HOME: preserves the OUI oraInventory.
• iAS ORACLE_HOME: preserves the OUI oraInventory
• 806 ORACLE_HOME: preserves the patch level and Oracle inventory
• APPL_TOP and Database: preserves the patch level and history tables.

8. What are the scripts do you use while Apps cloning?
• A type of script that’s made a comeback in hot scripts is clone script.
• adpreclone.pl prepares the source system and adcfgclone.pl configures the target system.
• Both the scripts are located in $COMMON_TOP/admin/scripts/contextname directory.
• Adpreclone.pl collects information about the database.
• It also creates generic templates of files containing source specified hardcore values.

9. What are the pre-upgrade steps that need to be taken for the up gradation of non_11i instance to 11.5.10?
• First, you need to take a complete backup of the application system.
• Run the TUMS utility
• Review the TUMS report
• Maintain the multilingual tables
• Rename the custom database objects
• Check attachment file upload directory
• You need to save the custom.pll

10. How often do you clone?
Ans: Cloning happens biweekly or monthly depending on the organization requirement.

11. 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.

12. 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.

13. What is the location of adpreclone.pl for oracle user?
Ans : $RDBMS_ORACLE_HOME/appsutil/scripts/

14. What is the location of adcfgclone.pl for oracle user?
Ans : $RDBMS_ORACLE_HOME/appsutil/clone/bin

15. What is the location of adpreclone.pl for applmgr user?
Ans : $COMMON_TOP/admin/scripts/

16. What is the location of adcfgclone.pl for applmgr user?
Ans : $COMMON_TOP/clone/bin

17. How do we find adpreclone is run in source or not ?
Ans : If clone directory exists under RDBMS_ORACLE_HOME/appsutil for oracle user and $COMMON_TOP for applmgr user.

18. How many clonings you have done?

19. What are the clone errors, you have encountered?

=====================================================================================
Oracle Apps DBA EBS (E-Business Suite)
AD utilities Interview Questions/FAQs Part1

1. What is adctrl?
Ans: Adctrl is one of the adutilities, which is used to check the status of workers and to manage the workers.

2. What is adrelink?
Ans : adrelink will relink the executables with the libraries. Generally we will go for adrelink when some patch delivers some library files, or when executables were corrupted.

3. What is autoconfig?
Ans : Autoconfig is an adutility which is used to main application environment and configuration files.

4. What is adodfcmp utility?
Ans : This utility is used to recreate/repair corrupted database objects from odf (object definition files) files.

5. What is adsplicer?
Ans : Adsplicer is a uitility used to register off cycle products.

6. What is adjkey? What files it will create?
Ans : adjkey is an adutility which will create digital signature, which will be used to sign all t” admin? mailto:adsign.txt@APPL_TOP>adsign.txt@APPL_TOP/adminappltop.cer@APPL_TOP/adminidentitydb.obj@applmgr home

7. What are the important AD tables and define their importance?
• AD_APPL_TOPS: It contains information about all APPL_TOP’s using the instance.
• AD_BUGS: It stores information about all different bug fixes applied.
• AD_PATCH_DRIVER_MINIPACKS: Mini_packs information will be stored here.
• AD_PATCH_RUN_BUGS: It stores information about all applied and updated bug fixes.
• AD_FILES: It stores information about files.

8. Difference between ad_bugs table and ad_applied_patches?
• When you apply the patch from adpatch utility ad_applied_patches table will get updated.
• ad_applied_patch doesn’t store the information which was not applied using adpatch.
• Such type of information will be stored in ad_bugs table.
• Also, if all the patches are applied using the adpatch utility, that information is also stored in the ad_bugs_table.

9. What is context file?
Ans : Context file is a central repositary, which stores all application configuration information. The name is like _ .xml

10. What are the menu options in adctrl?
Ans: Check the status of workers, tell manager that worker has quitted, restart a failed worker etc….

11. How to skip a worker and why?
Ans: We can skip a worker using option 8 in adctrl which is hidden. We will go for skipping a worker when we have executed the job which the worker is supposed to do.

12. What are the worker log file names and its location?
Ans : adwork01,adwork02…… and location is $APPL_TOP/admin/SID/log

13. What is the configuration file for adutilities (like adadmin, adconfig etc)?
Ans: adconfig.txt @APPL_TOP/admin

14. How to find the version of a file?
Ans : 1. adident Header
2. strings -a filename grep Header

15. What are the parameter autoconfig will ask for?
Ans : Context file name and apps password

16. How you will find autoconfig is enabled/not for u r applications?
Ans: 1. Open any env / configuration files, the first few lines will tell u that this files are maintained by autoconfig.2. If contextname.xml file is there in $APPL_TOP/admin

17. How autoconfig will create env and configuration files?
Ans: Autoconfig will go to each and every top template directory take the templates from there and fill the values from xml file and create the required files.

18. In how many phases autoconfig will run?
Ans : Autoconfig will run in 3 phases.
1.INIT – Instantiate the drivers and templates
2.SETUP – Fill the templated with values from xml and create files
3.PROFILE – Update the profile values in database.

19. What is the location of adconfig log file?
Ans : APPL_TOP/admin//log/

20. Is it possible to restore a autoconfig run?
Ans :
Partially. Adconfig will create a restore.sh script at $APPL_TOP/admin//out/. This restore.sh will copy the backed up files before autoconfig run to its original locations. But the profile values updated in the database can’t be restored back.

AD utilities Interview Questions/FAQs Part2

21. How to run autoconfig in test mode?
Ans :
adchkcfg.sh script at AD_TOP/bin. This script will run autoconfig in test mode and create the difference file which tells us what is going to change, when u actually run autoconfig.

22. How to find autoconfig is enabled or not for database?
Ans:
If we have appsutil directory under RDBMS_ORACLE_HOME

23. What is for “validating apps schema” option in adadmin?
Ans: It will check for the corrupted objects in apps schema

24. What is “compile apps schema” option in adadmin?
Ans : It will compile the invalid database objects.

25. How to find MRC is enabled or not?
Ans: In adadmin if covert to MRC options is there, then MRC is not enabled, if maintain MRC options is there, then MRC is enabled.

26. How to find Multi-Org is enabled or not?
Ans : In adadmin if covert to Multi org option is there, then Multi-org is not enabled. If maintain multi-org options is there, then Multi-org is enabled.

27. What is the configuration file for adutilities (like adadmin,adconfig etc)?
Ans: adconfig.txt @APPL_TOP/admin

28. What is the other script by which u can start apache other than adapcctl.sh?
Ans : apachectl @IAS_ORACLE_HOME/Apache/bin

29. How to find adconfig is enabled for oracle operating system user/database?
Ans : If appsutil directory is there in RDBMS_ORACLE_HOME

30. What is licence manager?
Ans : Licence manager(adlicmgr) utility is used to licence/unlicence , enable new languages,enbale country specific functionality.

31. What is the other script by which u can start apache other than adapcctl.sh?
Ans : apachectl @IAS_ORACLE_HOME/Apache/bin

32. What are the post installation task?
Ans : Running adjkey –initialize and then runnning adadmin to regenerate jar files.

33. What are the real time problems you have encountered and how you troubleshooted that?
Ans: 1. Concurrent Program is erroring out with snapshot too old error. To resolve this we have added space to temp tablespace.2. Concurrent Program is erroing out with unable to extent a perticular tablespace by so and so extents. To resolve this we have added on more data file to that tablespace.3. When we are trying to start apache with adapcctl.sh script after a autoconfig run, its saying that “node id is not matching with the application server id”. To resolve this we have updated the server id column in fnd_nodes table with the server id value in dbc file.

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

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

=============================================================
Oracle Apps DBA EBS (E-Business Suite)
RDBMS Interview Questions/FAQs Part1

1. How to apply an rdbms patch?
Ans : Using opatch

2. Have you applied rdbms patches and for what?
Ans : We got ORA-7445 error, for which oracle recommended to apply a rdbms patch.

3. What is the pre-req for applying a rdbms patch?
Ans : Inventory should be set in file oraInst.loc @/var/opt/oracle or /etc

4. What is Inventroy?
Ans: The oraInventory is the location for the OUI (Oracle Universal Installer)’s bookkeeping. The inventory stores information about all Oracle software products installed in all ORACLE_HOMES on a machine, other non-Oracle products, such as the Java Runtime Environment (JRE). In a 11i Application system the RDBMS and iAS ORACLE_HOMEs are registered in the oraInventory. The 806 ORACLE_HOME, which is not managed through OUI, is not registered in oraInventory.

5. What are different types of inventories?
Ans:
The Global inventory (or Central inventory) The Local inventory (or Home inventory).

6. Is it necessary to shutdown database while applying a database patch?
Ans : Yes.

7. What is Global inventory?
Ans : The Global Inventory is the part of the XML inventory that contains the high level list of all oracle products installed on a machine. There should therefore be only one per machine. Its location is defined by the content of oraInst.loc. The Global Inventory records the physical location of Oracle products installed on the machine, such as ORACLE_HOMES (RDBMS and IAS) or JRE. It does not have any information about the detail of patches applied to each ORACLE_HOMEs. The Global Inventory gets updated every time you install or de-install an ORACLE_HOME on the machine, be it through OUI Installer, Rapid Install, or Rapid Clone. If you need to delete an ORACLE_HOME, you should always do it through the OUI de-installer in order to keep the Global Inventory synchronized.

8. What is local inventory?
Ans : There is one Local Inventory per ORACLE_HOME. It is physically located inside the ORACLE_HOME at $ORACLE_HOME/inventory and contains the detail of the patch level for that ORACLE_HOME. The Local Inventory gets updated whenever a patch is applied to the ORACLE_HOME, using OUI.

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

10. What is the size of your database?
Ans :

11. How to find operating system version?
Ans : uname –a

12. How to kill a database session?
Ans : alter system kill session ‘&sid,&serial#’;

13. How to find invalid objects in database?
Ans : select count(*) from dba_objects where status=’INVALID’;

14. What is tnsping?
Ans : tnsping is command used to check the connectivity to the database server node from other nodes. TNS entry should be there in tnsnames.ora for the database we are trying to work this command.

15. How to find the locks and what is the resolution?
Ans : we can find general locks with the following query:
select * from sys.dba_dml_locks order by session_id.
We can find the dead locks with the following query:
select * from v$lock where lmode > 0 and id1 in (select distinct id1 from v$lock where request > 0)
If it’s a dead lock, we need to kill that session.

16. Which files tell you the database health?
Ans : alert log file @RDBMS_ORACLE_HOME/admin//bdump

17. What is statspack?
Ans : Statspack is a database utility to gather database and session level performance information.

18. How to install statspack?
Ans : Run the script spcreate.sql @RDBMS_ORACLE_HOME/rdbms/admin.

19. How to enable trace at database level?
Ans : set init.ora parameter sql_trace

20. How to enable trace for a session?
Ans: alter system set sql_trace=true;
Execute the sql query
alter system set sql_trace=false;
This will create a trace file at $RDBMS_ORACLE_HOME/admin/contextname/udump with the spid of the current sql session.

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

21. How to enable trace for other session?
Ans : exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,true/false)
To enable trace for sql session
SQL> exec sys.dbms_system.set_sql_trace_in_session(66,2321,true);

To disable trace
SQL> exec sys.dbms_system.set_sql_trace_in_session(66,2321,false);

22.What is the location of init.ora ?
Ans : $RDBMS_ORACLE_HOME/dbs

23. What is that trace files contains and the utility used to read them?
Ans : Trace file contains the detail diagnostics of a sql statement like explain plan, physical reads, logical reads, buffer gets etc. TKPROF utility is used to convert trace file into readable format.

24. What is the syntax for tkprof?
Ans: tkprof explain=apps/ sys=no

25. What is a view and how do you recreate a view which becomes invalid when you recreate a patch?
• View is used for accessing a particular portion of a table.
• A view is also called as virtual table.
• For recreating the view you can search for the view name in the SQL directory.
• We can recreate the views running scripts located in the Product top/patch/115/sql directory.
• Most views are recreated using sql script.

=================================================

blog

The author blog