close

Oracle Apps DBA Set 6

no thumb

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.

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

read more

Oracle Apps DBA Set 5

no thumb

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

read more

Oracle Apps DBA Set 4

no thumb

 

APPS DBA QUESTIONS

1). Let me know the flow of application when user login?
2). What is the purpose of authentication to connect todatabase?
3). Why u need GUEST/ORACLE to connect to database?
4). What is the purpose of JSERV?
5). How to increase the performance of Apache?

6). Tell me some issues with Apache?
7). Why you need httpd.pid? Why you need to clear that?
8). How do you increase the jvm size?
9). What is JVM?
10). If One of our dba can launch the forms and another unable to launch the forms? What will be the issue?

11). How to set trace at forms level?
12). If u have Concurrent request with pending–standby how do you troubleshoot?
13). If you have long pending requests and system become slow what you do?
14). How autoconfig works?
15). What Concurrent Tables that will cleanup when u run cmclean.sql?

16). What is PCP configuration? What is the use behind it? How to you configured?
17). What is diff between distributed AD and Shared APPL_TOP? What happens when u apply patch and distributed AD and Shared APPL_TOP?
18). What happens if you Miss Pre-Req patch and apply main patch and how to do continue?
19). How to do you skip a worker?

 

oracle apps dba interview questions for 3+ years of experience

1: What is wdbsvr.app file used for? What’s full path of this file? What’s significance of this file ?

I’ll again suggest you don’t just remember answer & try to solve mystery behind this file. First where this file exists ? You can find this file under $IAS_ORACLE_HOME/Apache/modplsql/cfg

Based on file location I am sure you can say this is related to Apache, & looking into modplsql/cfg , I am sure you will say its related to mod_pls (mod plsql component of Apache/Oracle 11i WebServer) configuration file. This file is used by mod_plsql component of Apache to connect to database. So when you type url http://hostname:port/pls/SID , whenever Apache(11i Web Server) finds that request is for /pls/ then Apache delegates this request to mod_pls component which in turn pick this file & check if there is any DAD with name SID (in our example its VISION11I) &

Sample entry in wdbsvr.app (here vision11i—sid)

[DAD_VISION11I]

connect_string = VISION11I

password = apps

username = APPS

default_page = fnd_web.ping

On typing http://hostname:port/pls/VISION11I , it will connect to database using apps schema & will return you page fnd_web.ping ( where fnd_web is package & ping is procedure or vise versa).

So story about this file doesn’t stop here , this url which I mentioned about is quite useful in troubleshooting so you can check if database connection is working fine or not. Another thing you want to check about this file is since it stores APPS password you need to change here whenever you change apps password .

2: Explain Architecture of Oracle Apps 11i.

Apps 11i is Mutli Tier architecture with Desktop(Client Tier), Middle Tier (Application Tier) and Database Tier.

3: What are various components in Application/Middle Tier.

In Application Tier various components are Web Server, Forms Server , Reports Server, Concurrent Manager, Admin Server & Discoverer Server.

4: What is APPL_TOP, COMN_TOP, ORA_TOP…..

XXX_TOP is top level directory in Oracle Application 11i for respective Component.

5: What are issues you encountered during Oracle Applications 11i?

6: What’s difference between TWO user & single User Apps 11i Install ? What are advantages/disadvantages of two ?

7: Whats US directory in $AD_TOP or under various product TOP’s .

US directory is defauly language directory in Oracle Applications. If you have multiple languages Installed in your Applications then you will see other languages directories besides US, that directory will contain reports, fmx and other code in that respective directory like FR for France, AR for arabic, simplifies chinese or spanish.

8: Whats main concurrent Manager types.

ICM – Internal Concurrent Manager which manages concurrent Managers

Standard Managers – Which Manage processesing of requests.CRM – Conflict Resolution Managers , resolve conflicts in case of incompatibility.

9: Where is Concurrent Manager log file location.

By default standard location is $APPLCSF/$APPLLOG , in some cases it can go to $FND_TOP/log as well.

10: Where would i find .rf9 file, and what execatly it dose ?

These files are used during restart of patch in case of patch failure because of some reason.

11: Where is appsweb.cfg or appsweb_$CONTEXT.cfg stored & why its used ?

This file is defined by environment variable FORMS60_WEB_CONFIG_FILE This is usually in directory $OA_HTML/bin on forms tier.

This file is used by any forms client session. When a user try to access forms , f60webmx picks up this file and based on this configuration file creates a forms session to user/client.

12: What is multi node system ?

Multi Node System in Oracle Applications 11i means you have Applications 11i Component on more than one system. Typical example is Database, Concurrent Manager on one machine and forms, Web Server on second machine is example of Two Node System.

13: Can you clone from multi node system to single node system & vice versa ?

Yes , this is now supported via Rapid Clone, Check if your system has all prereq. patches for Rapid Clone and you are on latest rapid clone patch

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

Rapid Clone will automatically Update Global oraInventory during configuration phase. You don’t have to do any thing manually for Global oraInventory.

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

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 $FND_TOP/secure also called as FND_SECURE

16: Whats things you do to reduce patch timing ? You can take advantage of following –

Merging patches via admrgpch

Use various adpatch options like nocompiledb or nocompilejsp

Use defaults file

Staged APPL_TOP during upgrades

Increase batch size (Might result into negative )

17: How you put Applications 11i in Maintenance mode ?

Use adadmin to change Maintenance mode is Oracle Apps. With AD.I you need to enable maintenance mode in order to apply apps patch via adpatch utility. If you don’t want to put apps in maintenance mode you can use adpatch options=hotpatch feature.

18: Can you apply patch without putting Applications 11i in Maintenance mode ?

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

19: What are various options available with adpatch ?

Various options available with adpatch depending on your AD version are

autoconfig, check_exclusive, checkfile, compiledb, compilejsp, copyportion, databaseprtion, generateportion, hotpatch, integrity, maintainmrc, parallel, prereq, validate

20: adident utility is used for what ?

adident utility in oracle apps is used to find version of any file . AD Identification.

for ex. “adident Header <filename>

21: What is adsplice utility ?

adsplice in oracle apps is utility to add a new product.

22: How can you licence a product after installation ?

You can use ad utility adlicmgr to licence product in Oracle Apps.

23: What is MRC ? What you do as Apps DBA for MRC ?

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 you as apps dba need to enable MRC in Apps.

24: Whats is JVM(Java Virtual Machine) and which component uses JVM ?

JVM stands for Java Virtual Machine,

25: What is access_log in apache , what entries are recored in access_log ? Where is default location of thsi file ?

access_log in Oracle Application Server records all users accessing oracle applications 11i. This file location is defined in httpd.conf with default location at $IAS_ORACLE_HOME/Apache/Apache/logs. Entries in this file is defined by directive LogFormat in httpd.conf Typical entry in access_log is

198.0.0.1 – – [10/Sep/2006:18:37:17 +0100] “POST /OA_HTML/OA.jsp?…. HTTP/1.1” 200 28035

where 200 is HTTP status code & last digits 28035 is bytes dowloaded as this page(Size of page)

26: Where is Jserv configuration files stored ?

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

27: What is session time out parameter & where all you define these values ?

To know about session time out parameter & location where its defined

28: Where is applications start/stop scripts stored ?

applications start/stop scripts are in directory $COMMON_TOP/admin/scripts/$CONTEXT_NAME

29: What are main configuration files in Web Server (Apache) ?

Main configuration files in Oracle Apps Web Server are

httpd.conf, apps.conf, oracle_apache.conf, httpd_pls.conf

jserv.conf, ssp_init.txt, jserv.properties, zone.properties

plsql.conf, wdbsvr.app, plsql.conf

30: What is profile options, What are various type of profile options ?

31: What is APPS listener ? Why its used ?

32: How do you start/stop apps listener ?

33: If users complaining Oracle Applications 11i system is running slow , what all things you will check at broad level ?

34: What is context file ?

35: Why appsutil directory under Database ORACLE_HOME used for ?

36: How to create User in Oracle Applications 11i ? Can you delete a User ?

37: What is Single Sign On ? ( If you are using portal 3.0.9 or 10G )?

38: How to configure portal with 11i ? ( If you are using portal 3.0.9 or 10G )?

39: How to check if Apps 11i System is Autoconfig enabled ?

Under $AD_TOP/bin check for file adcfginfo.sh & if this exists use

adcfginfo.sh contextfile=<CONTEXT> show=enabled

If this file is not there , look for any configuration file under APPL_TOP if system is Autoconfig enabled then you will see entry like

# AutoConfig automatically generates this file. It will be read and …….

40: How to check if Oracle Apps 11i System is Rapid Clone enabled ?

For syetem to be Rapid Clone enabled , it should be Autoconfig enabled (Check above How to confirm if Apps 11i is Autoconfig enabled). You should have Rapid Clone Patches applied , Rapid Clone is part of Rapid Install Product whose Family Pack Name is ADX. By default all Apps 11i Instances 11.5.9 and above are Autoconfig & Rapid Clone enabled

41: Whats is difference between two env files in <CONTEXT>.env and APPS<CONTEXT>.env under $APPL_TOP ?

APPS<CONTEXT>.env is main environment file which inturn calls other environment files like< CONTEXT>.env under $APPL_TOP, <CONTEXT>.env under 806 ORACLE_HOME and custom<CONTEXT>.env for any Customized environment files.

42: What is access_log in Apache ?

access_log file keeps record of users accessing Oracle Apps 11i Webserver.

Typical entry in access_log is like

198.0.0.1 – – [25/Aug/2006 :03:15:13 +0100] “GET /OA_JAVA /oracle /forms /registry/Registry.dat HTTP/1.1” 200 4117

Which means client with IP 198.0.0.1 requested for file mentioned above on 25 Aug 2006 at 03:15 AM , 200 is status code returned by Apache which means page returned successfully (Status Code 302 means page redirected , 404 page not found, 500+ Internal Server error)

last digit 4117 in above entry of access_log means file size which is 4117 bytes. This file is quite useful in monitoring your Web Server.

Please note above format might defer on your system as this is dependent log_format in Apache configuration file ( httpd.conf)

43: Whats is location of access_log file ?

access_log file by default is located in $IAS_ORACLE_HOME/ Apache/Apache/logs. Location of this file is defined in httpd.conf by patameter CustomLog or TransferLog

44: What is your Oracle Apps 11i Webserver Version and how to find it ?

From 11.5.8 to 11.5.10 Webserver version is iAS 1.0.2.2.2, In order to find version under $IAS_ORACLE_HOME/Apache/Apache/bin execute ./httpd -version

./httpd -version

Server version: Oracle HTTP Server Powered by Apache/1.3.19

Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)

45: What is Location of Jserv configuration files ?

Jserv configuration files are located in $IAS_ORACLE_HOME /Apache/Jserv/etc .

46: What is plssql/database cache ? In order to improve performance mod_pls (Apache component) caches some database content to file. This database/plssql cache is usually of type session & plsql cache

session cache is used to store session information.

plsql cache is used to store plsql cache i.e. used by mod_pls

47: Where is database/plssql cache stored ?

plssql& session cache are stored under $IAS_ORACLE_HOME/ Apache/modplsql/cache directory.

48: How to determine Oracle Apps 11i Version ?

select RELEASE_NAME from fnd_product_groups;

You should see output like

RELEASE_NAME

———————–

11.5.9 or 11.5.10.2

49: What is *.dbc file & whats is location of dbc file ?

dbc as name stands for is database connect descriptor file used to connect to database. This file by default located in $FND_TOP/secure directory also called as $FND_SECURE directory.

50: What is content of dbc file & why its important ?

DBC file is quite important as whenever Java or any other program like forms want to connect to database it uses dbc file. Typical entry in dbc file is

GUEST_USER_PWD

APPS_JDBC_URL

DB_HOST

51: There are lot of dbc file under $FND_SECURE, How its determined that which dbc file to use from $FND_SECURE ?

This value is determined from profile option “Applications Database ID”

52: What is RRA/FNDFS ?

Report Review Agent(RRA) also referred by executable FNDFS is default text viewer in Oracle Applications 11i for viewing output files & log files. As most of apps dba’s are not clear about Report Server & RRA, I’ll discuss one on my blog and update link here .

53: What is PCP is Oracle Applications 11i ?

PCP is acronym for Parallel Concurrurent processing. Usually you have one Concurrent Manager executing your requests but if you can configure Concurrent Manager running on two machines (Yes you need to do some additional steps in order to configure Parallel Concurrent Processing) . So for some of your requests primary CM Node is on machine1 and secondary CM node on machine2 and for some requests primary CM is on machine2 & secondary CM on machine1.

54: Why I need two Concurrent Processing Nodes or in what scenarios PCP is Used ?

55: Why I need two Concurrent Processing Nodes or in what scenarios PCP is Used ?

Well If you are running GL Month end reports or taxation reports annually these reposrts might take couple of days. Some of these requests are very resource intensive so you can have one node running long running , resource intensive requests while other processing your day to day short running requets.

another scenario is when your requests are very critical and you want high resilience for your Concurrent Processing Node , you can configure PCP. So if node1 goes down you still have CM node available processing your requests.

56: Output & Logfiles for requests executed on source Instance not working on cloned Instance

Here is exact problem description – You cloned an Oracle Apps Instance from PRODBOX to another box with Instance name say CLONEBOX on 1st of August. You can any CM logs/output files after 1st of August only becuase these all are generated on CLONEBOX itself, But unable to view the logs/output files which are prior to 1st August. What will you do & where to check ?

Log , Output file path & location is stored in table FND_CONCURRENT_REQUESTS. Check

select logfile_name, logfile_node_name, outfile_name, outfile_node_name from fnd_concurrent_requests where request_id=&requestid ;

where requestid is id of request for which you are not able to see log or out files. You should see output like

/u01/PRODBOX/log/l123456.req, host1,/u01/PRODBOX/out/o123456.out, host1

Update it according to your cloned Instance Variables.

57: How to confirm if Report Server is Up & Running ?

Report Server is started by executable rwmts60 on concurrent manager Node & this file is under $ORACLE_HOME/bin .execute command on your server like

ps -ef | grep rwmts60

You should get output like

applmgr ……. rwmts60 name=REP60_VISION

where VISION is your Instance name.

Else you can submit a request like “Active Users” with display set to PDF, check output & log file to see if report server can display PDF files.

58: What is difference between ICM, Standard Managers & CRM in Concurrent Manager ?

ICM stand for Internal Concurrent Manager, which controls other managers. If it finds other managers down , it checks & try to restart them. You can say it as administrator to other concurrent managers. It has other tasks as well.

Standard Manager These are normal managers which control/action on the requests & does batch or single request processing.

CRM acronym for Conflict Resolution Manager is used to resolve conflicts between managers & request. If a request is submitted whose execution is clashing or it is defined not to run while a particular type of request is running then such requests are actioned/assigned to CRM for Incompatibilities & Conflict resoluti59: What is use of Apps listener ?

Apps Listener usually running on All Oracle Applications 11i Nodes with listener alias as APPS_$SID is mainly used for listening requests for services like FNDFS & FNDSM.

60: How to start Apps listener ?

In Oracle 11i, you have script adalnctl.sh which will start your apps listener. You can also start it by command

lsnrctl start APPS_$SID (Replace sid by your Instance SID Name)

61: How to confirm if Apps Listener is Up & Running ?

execute below command

lsnrctl status APPS_$SID (replcae SID with your Instance Name)

so If your SID is VISION then use lsnrctl status APPS_VISION out put should be like

Services Summary…

FNDFS has 1 service handler(s)

FNDSM has 1 service handler(s)

The command completed successfully

62: What is Web Listener ?

Web Listener is Web Server listener which is listening for web Services(HTTP) request. This listener is started by adapcctl.sh & defined by directive (Listen, Port) in httpd.conf for Web Server. When you initially type request like http://becomeappsdba.blogspot.com:80 to access application here port number 80 is Web Listener port.

63: How will you find Invalid Objects in database ?

using query

SQLPLUS> select count(*) from dba_objects where status like ‘INVALID’;

64: How to compile Invalid Objects in database ?

You can use adadmin utility to compile or you can use utlrp.sql script shipped with Oracle Database to compile Invalid Database Objects.

65: How to compile JSP in Oracle Apps ?

You can use ojspCompile.pl perl script shipped with Oracle apps to compile JSP files. This script is under $JTF_TOP/admin/scripts. Sample compilation method is

perl ojspCompile.pl –compile –quiet

66: What is difference between adpatch & opatch ?

adpatch is utility to apply oracle apps Patches whereas

opatch is utility to apply database patches

67: Can you use both adpatch & opatch in Apps ?

Yes you have to use both in apps , for apps patches you will use adpatch utility and for applying database patch in apps you will opatch utility.

68: Where will you find forms configuration details apart from xml file ?

Forms configuration at time of startup is in script adfrmctl.sh and appsweb_$CONTEXT_NAME.cfg (defined by environment variable FORMS60_WEB_CONFIG_FILE) for forms client connection used each time a user initiates forms connection.

69: What is forms server executable Name ?

f60srvm

70: What are different modes of forms in which you can start Forms Server and which one is default ?

You can start forms server in SOCKET or SERVLET by defualt Forms are configured to start in socket mode.

71: How you will start Discoverer in Oracle Apps 11i ?

In order to start dicoverer you can use script addisctl.sh under $OAD_TOP/admin/scripts/$CONTEXT_NAME or startall.sh under $ORACLE_HOME/discwb4/util (under Middle/Application Tier)

72: How many ORACLE HOME are Oracle Apps and whats significance of each ?

There are three $ORACLE_HOME in Oracle Apps, Two for Application Tier (Middle Tier) and One in Database Tier.

ORACLE_HOME 1 : On Application Tier used to store 8.0.6 techstack software. This is used by forms, reports & discoverer. ORACLE_HOME should point to this ORACLE_HOME which applying Apps Patch.

ORACLE_HOME 2: On Application Tier used by iAS (Web Server) techstack software. This is used by Web Listener & contains Apache.

ORACLE_HOME 3: On Database Tier used by Database Software usually 8i,9i or 10g database.

73: Where is HTML Cache stored in Oracle Apps Server ?

Oracle HTML Cache is available at $COMMON_TOP/_pages for some previous versions you might find it in $OA_HTML/_pages

74: Where is plssql cache stored in Oracle Apps ?

Usually two type of cache session & plssql stored under $IAS_ORACLE_HOME/Apache/modplsql/cache

75: What happens if you don’t give cache size while defining Concurrent Manager ?

Lets first understand what is cache size in Concurrent Manager. When Manager picks request from FND CONCURRENT REQUESTS Queues, it will pick up number of requests defined by cache size in one shot & will work on them before going to sleep. So in my views if you don’t define cache size while defining CM then it will take default value 1, i.e. picking up one request per cycle.

Thanks to vish for pointing out that answer to above Q’s copied from pervious Q’s. If you find any disperancy do mail me.

76: How many ORACLE HOME are Oracle Apps and whats significance of each ?

There are three $ORACLE_HOME in Oracle Apps, Two for Application Tier (Middle Tier) and One in Database Tier.

ORACLE_HOME 1 : On Application Tier used to store 8.0.6 techstack software. This is used by forms, reports & discoverer. ORACLE_HOME should point to this ORACLE_HOME which applying Apps Patch.

ORACLE_HOME 2: On Application Tier used by iAS (Web Server) techstack software. This is used by Web Listener & contains Apache.

ORACLE_HOME 3: On Database Tier used by Database Software usually 8i,9i or 10g database.

77: Where is HTML Cache stored in Oracle Apps Server ?

Oracle HTML Cache is available at $COMMON_TOP/_pages for some previous versions you might find it in $OA_HTML/_pages

78: Where is plssql cache stored in Oracle Apps ?

Usually two type of cache session & plssql stored under $IAS_ORACLE_HOME/Apache/modplsql/cache

79: What are few profile options which you update after cloning ?

Rapid clone updates profile options specific to site level . If you have any profile option set at other levels like server, responsibility, user….level then reset them.

80: How to retrieve SYSADMIN password ?

If forgot password link is enabled and sysadmin account is configured with mail id user forget password link else you can reset sysadmin password via FNDCPASS.

81: If you have done two node Installation, First machine : Database and concurrent processing server. 2nd machine: form,web Which machine have admin server/node?

Admin Server will be on First machine with concurrent processing server

82: Whats is TWO_TASK in Oracle Database ?

TWO_TASK mocks your tns alias which you are going to use to connect to database. Lets assume you have database client with tns alias defined as PROD to connect to Database PROD on machine teachmeoracle.com listening on port 1521. Then usual way to connect is sqlplus username/passwd@PROD ; now if you don’t want to use @PROD then you set TWO_TASK=PROD and then can simply use sqlplus username/passwd then sql will check that it has to connect to tnsalias define by value PROD i.e. TWO_TASK

83: What is GWYUID ?

GWYUID , stands for Gateway User ID and password. Usually like APPLSYSPUB/PUB

84: Where GWYUID defined & what is its used in Oracle Applications ?

GWYUID is defined in dbc i.e. Database Connect Descriptor file . It is used to connect to database by think clients.

85: What is difference between GUEST_USER_PWD (GUEST/ORACLE) & GWYUID ?

GUEST_USER_PWD(Guest/Oracle) is used by JDBC Thin Client where as GWYUID is used by Thick Clients like via Forms Connections.

86: How to check number of forms users at any time ?

Forms Connections initiate f60webmx connections so you can use

ps -ef | grep f60webmx | wc -l

87: What is 0 & Y in FNDCPASS, FNDLOAD or WFLOAD ?

0& Y are flags for FND Executable like FNDCPASS & FNDLOAD where

0 is request id (request ID 0 is assigned to request ID’s which are not submitted via Submit Concurrent Request Form.

‘Y’ indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.

88: In a Multi Node Installation, How will you find which node is running what SErvices ?

You can query for table FND_NODES and check for column , SUPPORT_CP ( for Concurrent Manager) SUPPORT_FORMS ( for forms server) , SUPPPORT_WEB (Web Server), SUPPORT_ADMIN( Admin Server), and SUPPORT_DB for database tier.

You can also check same from CONTEXT File (xml file under APPL_TOP/admin)

89: If your system has more than one Jinitiator, how will the system know, which one to pick. ?

When client makes a forms connection in Oracle Applications, forms client session uses configuration file defined by environment variable FORMS60_WEB_CONFIG_FILE also called as appsweb config file. These

days this file is of format appsweb_$CONTEXT.cfg The initiator version number defined by parameter jinit_ver_name in this file will be used .

90: While applying Apps patch using adpatch, if you want to hide the apps password, how will that be possible ?

Use adpatch flags=hidepw while applying patches in apps to hide apps or system password being displayed on Users Screen.

91: What is importance of IMAP Server in Java Notification Mailer ?

IMAP stands for Internet Message Access Protocol and Java Notification mailer require IMAP server for Inbound Processing of Notification Mails.

92: What is difference between Socket & Servlet Mode in Apps Forms ?

When forms run SOCKET Mode these are dedicated connection between Client Machine& Form Server (Started by adfrmctl.sh). When Forms run in servlet mode the forms requests are fulfilled by Jserv in Apache . There will be additional JVM for Forms Request in that case and you won’t start form via adfrmctl.sh.

93: How to find OUI version ?

OUI stands for Oracle Universal Installer. In order to find how to check OUI version Click Here on my “How to find Version in Oracle” Page.

94: What is make program in Unix ?

make is utility in Unix/Linux to maintain , update & generate an file mainly executable.

95: If by mistake you/someone deleted FNDLIBR can this executable be restored if Yes, How & if no, what will you do ?

Yes, you can restore FNDLIBR executables

run adadmin on concurrent manager node

select option 2. Maintain Applications Files menu

then select 1. Relink Applications programs

when prompts for

Enter list of products to link (‘all’ for all products) [all]

select FND

when prompt for

Generate specific executables for each selected product [No] ? YES

select YES

& from list of executables select FNDLIBR

This will create new FNDLIBR executables.

96: What is .pls files which you see with apps ?

.pls file stands for plsql files. In apps patch these files contain code to create package spec or package body or both.

97: What are .ldt & .lct files which you see in apps patch or with FNDLOAD ?

.ldt& .lct stands for Loader datafile & Loader configuration files, used frequently in migrating customization, profile options, configuration data, etc.. across Instances.

98: What are .odf file in apps patch ?

odf stands for Object Description Files used to create tables & other database objects.

99: What to find Form Server log files in forms ?

Form Server Start up log file default location is $OAD_TOP/admin/log/$CONTEXT_NAME/f60svrm.txt

Forms Run Time Diagnostics default location is $ORACLE_HOME/forms60/log/$CONTEXT_NAME

100: How to convert pll to pld file or pld file to pll ?

Pll->Pld f60gen module=MSCOSCW3.pll module_type=library userid=apps/<passwd> module_access=file output_file=MSCOSCW1.pld script=yes

Pld -> pll f60gen module=MSCOSCW3.pld userid=apps/<passwd> module_type=library module_access=file output_file=MSCOSCW1.pll parse=y batch=yes compile_all=special

101: Is APPS_MRC Schema exists for MRC in 11.5.10 and higher ?

No , apps_mrc schema is dropped with 11.5.10 Upgrade & 11.5.10 new Install. This is replaced by more Integrated Architecture.

102: If APPS_MRC schema is not used in 11.5.10 and higher then How MRC is working ?

For products like Payable, Recievables which uses MRC and if MRC is enabled then each transaction table in base schema related to currency now has an assoicated MRC Subtables.

103: When you apply C driver patch does it require database to be Up & Why ?

Yes , database & db listener should be Up when you apply any driver patch in apps. even if driver is not updating any database object connection is required to validate apps & other schema and to upload patch history information in database tables.

104: Can C driver in apps patch create Invalid Object in database ?

No , C driver only copies files in File System. Database Object might be invalidated during D driver when these objects are created/dropped/modify

105: What is dev60cgi & f60cgi ?

cgi stands for Common Gateway Interface and these are Script Alias in Oracle Apps used to access forms server . Usually Form Server access directly via http://hostname:port/dev60cgi/f60cgi

106: Why does a worker fails in Oracle Apps Patch and few scenarios in which it failed for you ?

This question sounds stupid but this is asked quite often in Apps DBA Interview. Apps Patch worker can fail in case it doesn’t find expected data, object, files or any thing which driver is trying to update/edit/modify. Possible symptoms may be underlying tables/objects are invalid, a prereq patch is missing , login information is incorrect, inconsistency in seeded data…

107: What is difference between mod_osso & mod_ose in Oracle HTTP Server ?

mod_osso is Oracle Single Sign-On Module where as mod_ose is module for Oracle Servlet Engine.

mod_osso is module in Oracle’s HTTP Server serves as Conduit between Oracle Apache Server & Singl Sign-On Server where as mod_ose is also another module in Oracle’s HTTP Server serves as conduit between Oracle Apache & Oracle Servlet Engine.

108: What is difference between COMPILE_ALL=SPECIAL and COMPILE=ALL while compiling Forms ?

Both the options will compile all the PL/SQL in the resultant .FMX, .PLX, or .MMX file but COMPILE_ALL=YES also changes the cached version in the source .FMB, .PLL, or .MMB file. This confuses version control and build tools (CVS, Subversion, make, scons); they believe you’ve made significant changes to the source. COMPILE_ALL=SPECIAL does not do this.

109: What is ps -ef or ps command in Unix ? for work ex < 1 yr

ps is unix/linux utility or executable to find status of process. Used mainly to find if services/process is running or not.

110: What is GSM in Oracle application E-Business Suite ?

GSM stands for Generic Service Management Framework. Oracle E-Business Suite consist of various compoennts like Forms, Reports, Web Server, Workflow, Concurrent Manager ..

Earlier each service used to start at their own but managing these services (given that) they can be on various machines distributed across network. So Generic Service Management is extension of Concurrent Processing which manages all your services , provide fault tolerance (If some service is down ICM through FNDSM& other processes will try to start it even on remote server) With GSM all services are centrally managed via this Framework.

111: What is FNDSM ?

FNDSM is executable & core component in GSM ( Generic Service Management Framework discussed above). You start FNDSM services via APPS listener on all Nodes in Application Tier in E-Business Suite.

112: What is iAS Patch ?

iAS Patch are patches released to fix bugs associated with IAS_ORACLE_HOME (Web Server Component) Usually these are shiiped as Shell scripts & you apply iAS patches by executing Shell script. Note that by default ORACLE_HOME is pointing to 8.0.6 ORACLE_HOME and if you are applying iAS patch export ORACLE_HOME to iAS . You can do same by executing environment file under $IAS_ORACLE_HOME

113: If we run autoconfig which files will get effected ?

In order to check list of files changes during Autoconfig , you can run adchkcfg utility which will generate HTML report. This report will list all files & profile options going to change when you run AutoConfig.

114: What is difference between .xml file & AutoConfig ?

Autoconfig is Utility to configure your Oracle Application environment. .xml file is repository of all configuration from which AutoConfig picks configuration and polulates related files.

115: What is .lgi files ?

lgi files are created with patching along with .log files . .lgi files are informative log files containing information related to patch. You can check .lgi files to see what activities patch has done. Usually informative logs.

116: How will you skip worker during patch ?

If in your adctrl there are six option shown then seventh is hidden option.(If there are seven options visible then 8th option is to Skip worker depending on ad version).

117: Which two tables created at start of Apps Patch & drops at end of Patch ?

xFND_INSTALLED_PROCESSES& AD_DEFFERED_JOBS are the tables that get updated while applying a patch mainly d or unified driver.

118: How to compile an Oracle Reports file ?

Utility adrepgen is used to compile Reports. Synatx is given below

adrepgen userid=apps\<psswd> source = $PRODUCT_TOP\srw\filename.rdf dest=$PRODUCT_TOP\srw\filename.rdf stype=rdffile dtype=rdffile logfile=x.log overwrite=yes batch=yes dunit=character

119: What is difference between AD_BUGS & AD_APPLID_PATCHES ?

AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.

AD_APPLIED_PATCHES holds information about the “distinct” Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. “merged” patches), then they are considered distinct and this table will therefore hold 2 records.

120: What exactly happens when you put an Oracle Apps instance in maintenance mode ?

Maintenance mode provides a clear separation between normal runtime operation of Oracle Applications and system downtime for maintenance. Enabling the maintenance mode feature

a) shuts down the Workflow Business Events System and

b) sets up function security so that no Oracle Applications functions are available to users.

Used only during AutoPatch sessions, maintenance mode ensures optimal performance and reduces downtime when applying a patch.

1: What is wdbsvr.app file used for? What’s full path of this file? What’s significance of this file ?

I’ll again suggest you don’t just remember answer & try to solve mystery behind this file. First where this file exists ? You can find this file under $IAS_ORACLE_HOME/Apache/modplsql/cfg

Based on file location I am sure you can say this is related to Apache, & looking into modplsql/cfg , I am sure you will say its related to mod_pls (mod plsql component of Apache/Oracle 11i WebServer) configuration file. This file is used by mod_plsql component of Apache to connect to database. So when you type url http://hostname:port/pls/SID , whenever Apache(11i Web Server) finds that request is for /pls/ then Apache delegates this request to mod_pls component which in turn pick this file & check if there is any DAD with name SID (in our example its VISION11I) &

Sample entry in wdbsvr.app (here vision11i—sid)

[DAD_VISION11I]

connect_string = VISION11I

password = apps

username = APPS

default_page = fnd_web.ping

On typing http://hostname:port/pls/VISION11I , it will connect to database using apps schema & will return you page fnd_web.ping ( where fnd_web is package & ping is procedure or vise versa).

So story about this file doesn’t stop here , this url which I mentioned about is quite useful in troubleshooting so you can check if database connection is working fine or not. Another thing you want to check about this file is since it stores APPS password you need to change here whenever you change apps password .

2: Explain Architecture of Oracle Apps 11i.

Apps 11i is Mutli Tier architecture with Desktop(Client Tier), Middle Tier (Application Tier) and Database Tier.

3: What are various components in Application/Middle Tier.

In Application Tier various components are Web Server, Forms Server , Reports Server, Concurrent Manager, Admin Server & Discoverer Server.

4: What is APPL_TOP, COMN_TOP, ORA_TOP…..

XXX_TOP is top level directory in Oracle Application 11i for respective Component.

5: What are issues you encountered during Oracle Applications 11i?

6: What’s difference between TWO user & single User Apps 11i Install ? What are advantages/disadvantages of two ?

7: Whats US directory in $AD_TOP or under various product TOP’s .

US directory is defauly language directory in Oracle Applications. If you have multiple languages Installed in your Applications then you will see other languages directories besides US, that directory will contain reports, fmx and other code in that respective directory like FR for France, AR for arabic, simplifies chinese or spanish.

8: Whats main concurrent Manager types.

ICM – Internal Concurrent Manager which manages concurrent Managers

Standard Managers – Which Manage processesing of requests.CRM – Conflict Resolution Managers , resolve conflicts in case of incompatibility.

9: Where is Concurrent Manager log file location.

By default standard location is $APPLCSF/$APPLLOG , in some cases it can go to $FND_TOP/log as well.

10: Where would i find .rf9 file, and what execatly it dose ?

These files are used during restart of patch in case of patch failure because of some reason.

11: Where is appsweb.cfg or appsweb_$CONTEXT.cfg stored & why its used ?

This file is defined by environment variable FORMS60_WEB_CONFIG_FILE This is usually in directory $OA_HTML/bin on forms tier.

This file is used by any forms client session. When a user try to access forms , f60webmx picks up this file and based on this configuration file creates a forms session to user/client.

12: What is multi node system ?

Multi Node System in Oracle Applications 11i means you have Applications 11i Component on more than one system. Typical example is Database, Concurrent Manager on one machine and forms, Web Server on second machine is example of Two Node System.

13: Can you clone from multi node system to single node system & vice versa ?

Yes , this is now supported via Rapid Clone, Check if your system has all prereq. patches for Rapid Clone and you are on latest rapid clone patch

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

Rapid Clone will automatically Update Global oraInventory during configuration phase. You don’t have to do any thing manually for Global oraInventory.

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

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 $FND_TOP/secure also called as FND_SECURE

16: Whats things you do to reduce patch timing ? You can take advantage of following –

Merging patches via admrgpch

Use various adpatch options like nocompiledb or nocompilejsp

Use defaults file

Staged APPL_TOP during upgrades

Increase batch size (Might result into negative )

17: How you put Applications 11i in Maintenance mode ?

Use adadmin to change Maintenance mode is Oracle Apps. With AD.I you need to enable maintenance mode in order to apply apps patch via adpatch utility. If you don’t want to put apps in maintenance mode you can use adpatch options=hotpatch feature.

18: Can you apply patch without putting Applications 11i in Maintenance mode ?

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

19: What are various options available with adpatch ?

Various options available with adpatch depending on your AD version are

autoconfig, check_exclusive, checkfile, compiledb, compilejsp, copyportion, databaseprtion, generateportion, hotpatch, integrity, maintainmrc, parallel, prereq, validate

20: adident utility is used for what ?

adident utility in oracle apps is used to find version of any file . AD Identification.

for ex. “adident Header <filename>

21: What is adsplice utility ?

adsplice in oracle apps is utility to add a new product.

22: How can you licence a product after installation ?

You can use ad utility adlicmgr to licence product in Oracle Apps.

23: What is MRC ? What you do as Apps DBA for MRC ?

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 you as apps dba need to enable MRC in Apps.

24: Whats is JVM(Java Virtual Machine) and which component uses JVM ?

JVM stands for Java Virtual Machine,

25: What is access_log in apache , what entries are recored in access_log ? Where is default location of thsi file ?

access_log in Oracle Application Server records all users accessing oracle applications 11i. This file location is defined in httpd.conf with default location at $IAS_ORACLE_HOME/Apache/Apache/logs. Entries in this file is defined by directive LogFormat in httpd.conf Typical entry in access_log is

198.0.0.1 – – [10/Sep/2006:18:37:17 +0100] “POST /OA_HTML/OA.jsp?…. HTTP/1.1” 200 28035

where 200 is HTTP status code & last digits 28035 is bytes dowloaded as this page(Size of page)

26: Where is Jserv configuration files stored ?

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

27: What is session time out parameter & where all you define these values ?

To know about session time out parameter & location where its defined

28: Where is applications start/stop scripts stored ?

applications start/stop scripts are in directory $COMMON_TOP/admin/scripts/$CONTEXT_NAME

29: What are main configuration files in Web Server (Apache) ?

Main configuration files in Oracle Apps Web Server are

httpd.conf, apps.conf, oracle_apache.conf, httpd_pls.conf

jserv.conf, ssp_init.txt, jserv.properties, zone.properties

plsql.conf, wdbsvr.app, plsql.conf

30: What is profile options, What are various type of profile options ?

31: What is APPS listener ? Why its used ?

32: How do you start/stop apps listener ?

33: If users complaining Oracle Applications 11i system is running slow , what all things you will check at broad level ?

34: What is context file ?

35: Why appsutil directory under Database ORACLE_HOME used for ?

36: How to create User in Oracle Applications 11i ? Can you delete a User ?

37: What is Single Sign On ? ( If you are using portal 3.0.9 or 10G )?

38: How to configure portal with 11i ? ( If you are using portal 3.0.9 or 10G )?

39: How to check if Apps 11i System is Autoconfig enabled ?

Under $AD_TOP/bin check for file adcfginfo.sh & if this exists use

adcfginfo.sh contextfile=<CONTEXT> show=enabled

If this file is not there , look for any configuration file under APPL_TOP if system is Autoconfig enabled then you will see entry like

# AutoConfig automatically generates this file. It will be read and …….

40: How to check if Oracle Apps 11i System is Rapid Clone enabled ?

For syetem to be Rapid Clone enabled , it should be Autoconfig enabled (Check above How to confirm if Apps 11i is Autoconfig enabled). You should have Rapid Clone Patches applied , Rapid Clone is part of Rapid Install Product whose Family Pack Name is ADX. By default all Apps 11i Instances 11.5.9 and above are Autoconfig & Rapid Clone enabled

41: Whats is difference between two env files in <CONTEXT>.env and APPS<CONTEXT>.env under $APPL_TOP ?

APPS<CONTEXT>.env is main environment file which inturn calls other environment files like< CONTEXT>.env under $APPL_TOP, <CONTEXT>.env under 806 ORACLE_HOME and custom<CONTEXT>.env for any Customized environment files.

42: What is access_log in Apache ?

access_log file keeps record of users accessing Oracle Apps 11i Webserver.

Typical entry in access_log is like

198.0.0.1 – – [25/Aug/2006 :03:15:13 +0100] “GET /OA_JAVA /oracle /forms /registry/Registry.dat HTTP/1.1” 200 4117

Which means client with IP 198.0.0.1 requested for file mentioned above on 25 Aug 2006 at 03:15 AM , 200 is status code returned by Apache which means page returned successfully (Status Code 302 means page redirected , 404 page not found, 500+ Internal Server error)

last digit 4117 in above entry of access_log means file size which is 4117 bytes. This file is quite useful in monitoring your Web Server.

Please note above format might defer on your system as this is dependent log_format in Apache configuration file ( httpd.conf)

43: Whats is location of access_log file ?

access_log file by default is located in $IAS_ORACLE_HOME/ Apache/Apache/logs. Location of this file is defined in httpd.conf by patameter CustomLog or TransferLog

44: What is your Oracle Apps 11i Webserver Version and how to find it ?

From 11.5.8 to 11.5.10 Webserver version is iAS 1.0.2.2.2, In order to find version under $IAS_ORACLE_HOME/Apache/Apache/bin execute ./httpd -version

./httpd -version

Server version: Oracle HTTP Server Powered by Apache/1.3.19

Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)

45: What is Location of Jserv configuration files ?

Jserv configuration files are located in $IAS_ORACLE_HOME /Apache/Jserv/etc .

46: What is plssql/database cache ? In order to improve performance mod_pls (Apache component) caches some database content to file. This database/plssql cache is usually of type session & plsql cache

session cache is used to store session information.

plsql cache is used to store plsql cache i.e. used by mod_pls

47: Where is database/plssql cache stored ?

plssql & session cache are stored under $IAS_ORACLE_HOME/ Apache/modplsql/cache directory.

48: How to determine Oracle Apps 11i Version ?

select RELEASE_NAME from fnd_product_groups;

You should see output like

RELEASE_NAME

———————–

11.5.9 or 11.5.10.2

49: What is *.dbc file & whats is location of dbc file ?

dbc as name stands for is database connect descriptor file used to connect to database. This file by default located in $FND_TOP/secure directory also called as $FND_SECURE directory.

50: What is content of dbc file & why its important ?

DBC file is quite important as whenever Java or any other program like forms want to connect to database it uses dbc file. Typical entry in dbc file is

GUEST_USER_PWD

APPS_JDBC_URL

DB_HOST

51: There are lot of dbc file under $FND_SECURE, How its determined that which dbc file to use from $FND_SECURE ?

This value is determined from profile option “Applications Database ID”

52: What is RRA/FNDFS ?

Report Review Agent(RRA) also referred by executable FNDFS is default text viewer in Oracle Applications 11i for viewing output files & log files. As most of apps dba’s are not clear about Report Server & RRA, I’ll discuss one on my blog and update link here .

53: What is PCP is Oracle Applications 11i ?

PCP is acronym for Parallel Concurrurent processing. Usually you have one Concurrent Manager executing your requests but if you can configure Concurrent Manager running on two machines (Yes you need to do some additional steps in order to configure Parallel Concurrent Processing) . So for some of your requests primary CM Node is on machine1 and secondary CM node on machine2 and for some requests primary CM is on machine2 & secondary CM on machine1.

54: Why I need two Concurrent Processing Nodes or in what scenarios PCP is Used ?

55: Why I need two Concurrent Processing Nodes or in what scenarios PCP is Used ?

Well If you are running GL Month end reports or taxation reports annually these reposrts might take couple of days. Some of these requests are very resource intensive so you can have one node running long running , resource intensive requests while other processing your day to day short running requets.

another scenario is when your requests are very critical and you want high resilience for your Concurrent Processing Node , you can configure PCP. So if node1 goes down you still have CM node available processing your requests.

56: Output& Logfiles for requests executed on source Instance not working on cloned Instance

Here is exact problem description – You cloned an Oracle Apps Instance from PRODBOX to another box with Instance name say CLONEBOX on 1st of August. You can any CM logs/output files after 1st of August only becuase these all are generated on CLONEBOX itself, But unable to view the logs/output files which are prior to 1st August. What will you do & where to check ?

Log , Output file path & location is stored in table FND_CONCURRENT_REQUESTS. Check

select logfile_name, logfile_node_name, outfile_name, outfile_node_name from fnd_concurrent_requests where request_id=&requestid ;

where requestid is id of request for which you are not able to see log or out files. You should see output like

/u01/PRODBOX/log/l123456.req, host1,/u01/PRODBOX/out/o123456.out, host1

Update it according to your cloned Instance Variables.

57: How to confirm if Report Server is Up & Running ?

Report Server is started by executable rwmts60 on concurrent manager Node & this file is under $ORACLE_HOME/bin .execute command on your server like

ps -ef | grep rwmts60

You should get output like

applmgr ……. rwmts60 name=REP60_VISION

where VISION is your Instance name.

Else you can submit a request like “Active Users” with display set to PDF, check output & log file to see if report server can display PDF files.

58: What is difference between ICM, Standard Managers & CRM in Concurrent Manager ?

ICM stand for Internal Concurrent Manager, which controls other managers. If it finds other managers down , it checks & try to restart them. You can say it as administrator to other concurrent managers. It has other tasks as well.

Standard Manager These are normal managers which control/action on the requests & does batch or single request processing.

CRM acronym for Conflict Resolution Manager is used to resolve conflicts between managers& request. If a request is submitted whose execution is clashing or it is defined not to run while a particular type of request is running then such requests are actioned/assigned to CRM for Incompatibilities & Conflict resoluti59: What is use of Apps listener ?

Apps Listener usually running on All Oracle Applications 11i Nodes with listener alias as APPS_$SID is mainly used for listening requests for services like FNDFS & FNDSM.

60: How to start Apps listener ?

In Oracle 11i, you have script adalnctl.sh which will start your apps listener. You can also start it by command

lsnrctl start APPS_$SID (Replace sid by your Instance SID Name)

61: How to confirm if Apps Listener is Up & Running ?

execute below command

lsnrctl status APPS_$SID (replcae SID with your Instance Name)

so If your SID is VISION then use lsnrctl status APPS_VISION out put should be like

Services Summary…

FNDFS has 1 service handler(s)

FNDSM has 1 service handler(s)

The command completed successfully

62: What is Web Listener ?

Web Listener is Web Server listener which is listening for web Services(HTTP) request. This listener is started by adapcctl.sh & defined by directive (Listen, Port) in httpd.conf for Web Server. When you initially type request like http://becomeappsdba.blogspot.com:80 to access application here port number 80 is Web Listener port.

63: How will you find Invalid Objects in database ?

using query

SQLPLUS> select count(*) from dba_objects where status like ‘INVALID’;

64: How to compile Invalid Objects in database ?

You can use adadmin utility to compile or you can use utlrp.sql script shipped with Oracle Database to compile Invalid Database Objects.

65: How to compile JSP in Oracle Apps ?

You can use ojspCompile.pl perl script shipped with Oracle apps to compile JSP files. This script is under $JTF_TOP/admin/scripts. Sample compilation method is

perl ojspCompile.pl –compile –quiet

66: What is difference between adpatch & opatch ?

adpatch is utility to apply oracle apps Patches whereas

opatch is utility to apply database patches

67: Can you use both adpatch & opatch in Apps ?

Yes you have to use both in apps , for apps patches you will use adpatch utility and for applying database patch in apps you will opatch utility.

68: Where will you find forms configuration details apart from xml file ?

Forms configuration at time of startup is in script adfrmctl.sh and appsweb_$CONTEXT_NAME.cfg (defined by environment variable FORMS60_WEB_CONFIG_FILE) for forms client connection used each time a user initiates forms connection.

69: What is forms server executable Name ?

f60srvm

70: What are different modes of forms in which you can start Forms Server and which one is default ?

You can start forms server in SOCKET or SERVLET by defualt Forms are configured to start in socket mode.

71: How you will start Discoverer in Oracle Apps 11i ?

In order to start dicoverer you can use script addisctl.sh under $OAD_TOP/admin/scripts/$CONTEXT_NAME or startall.sh under $ORACLE_HOME/discwb4/util (under Middle/Application Tier)

72: How many ORACLE HOME are Oracle Apps and whats significance of each ?

There are three $ORACLE_HOME in Oracle Apps, Two for Application Tier (Middle Tier) and One in Database Tier.

ORACLE_HOME 1 : On Application Tier used to store 8.0.6 techstack software. This is used by forms, reports & discoverer. ORACLE_HOME should point to this ORACLE_HOME which applying Apps Patch.

ORACLE_HOME 2: On Application Tier used by iAS (Web Server) techstack software. This is used by Web Listener & contains Apache.

ORACLE_HOME 3: On Database Tier used by Database Software usually 8i,9i or 10g database.

73: Where is HTML Cache stored in Oracle Apps Server ?

Oracle HTML Cache is available at $COMMON_TOP/_pages for some previous versions you might find it in $OA_HTML/_pages

74: Where is plssql cache stored in Oracle Apps ?

Usually two type of cache session & plssql stored under $IAS_ORACLE_HOME/Apache/modplsql/cache

75: What happens if you don’t give cache size while defining Concurrent Manager ?

Lets first understand what is cache size in Concurrent Manager. When Manager picks request from FND CONCURRENT REQUESTS Queues, it will pick up number of requests defined by cache size in one shot & will work on them before going to sleep. So in my views if you don’t define cache size while defining CM then it will take default value 1, i.e. picking up one request per cycle.

Thanks to vish for pointing out that answer to above Q’s copied from pervious Q’s. If you find any disperancy do mail me.

76: How many ORACLE HOME are Oracle Apps and whats significance of each ?

There are three $ORACLE_HOME in Oracle Apps, Two for Application Tier (Middle Tier) and One in Database Tier.

ORACLE_HOME 1 : On Application Tier used to store 8.0.6 techstack software. This is used by forms, reports & discoverer. ORACLE_HOME should point to this ORACLE_HOME which applying Apps Patch.

ORACLE_HOME 2: On Application Tier used by iAS (Web Server) techstack software. This is used by Web Listener & contains Apache.

ORACLE_HOME 3: On Database Tier used by Database Software usually 8i,9i or 10g database.

77: Where is HTML Cache stored in Oracle Apps Server ?

Oracle HTML Cache is available at $COMMON_TOP/_pages for some previous versions you might find it in $OA_HTML/_pages

78: Where is plssql cache stored in Oracle Apps ?

Usually two type of cache session & plssql stored under $IAS_ORACLE_HOME/Apache/modplsql/cache

79: What are few profile options which you update after cloning ?

Rapid clone updates profile options specific to site level . If you have any profile option set at other levels like server, responsibility, user….level then reset them.

80: How to retrieve SYSADMIN password ?

If forgot password link is enabled and sysadmin account is configured with mail id user forget password link else you can reset sysadmin password via FNDCPASS.

81: If you have done two node Installation, First machine : Database and concurrent processing server. 2nd machine: form,web Which machine have admin server/node?

Admin Server will be on First machine with concurrent processing server

82: Whats is TWO_TASK in Oracle Database ?

TWO_TASK mocks your tns alias which you are going to use to connect to database. Lets assume you have database client with tns alias defined as PROD to connect to Database PROD on machine teachmeoracle.com listening on port 1521. Then usual way to connect is sqlplus username/passwd@PROD ; now if you don’t want to use @PROD then you set TWO_TASK=PROD and then can simply use sqlplus username/passwd then sql will check that it has to connect to tnsalias define by value PROD i.e. TWO_TASK

83: What is GWYUID ?

GWYUID , stands for Gateway User ID and password. Usually like APPLSYSPUB/PUB

84: Where GWYUID defined & what is its used in Oracle Applications ?

GWYUID is defined in dbc i.e. Database Connect Descriptor file . It is used to connect to database by think clients.

85: What is difference between GUEST_USER_PWD (GUEST/ORACLE) & GWYUID ?

GUEST_USER_PWD(Guest/Oracle) is used by JDBC Thin Client where as GWYUID is used by Thick Clients like via Forms Connections.

86: How to check number of forms users at any time ?

Forms Connections initiate f60webmx connections so you can use

ps -ef | grep f60webmx | wc -l

87: What is 0 & Y in FNDCPASS, FNDLOAD or WFLOAD ?

0 & Y are flags for FND Executable like FNDCPASS & FNDLOAD where

0 is request id (request ID 0 is assigned to request ID’s which are not submitted via Submit Concurrent Request Form.

‘Y’ indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.

88: In a Multi Node Installation, How will you find which node is running what SErvices ?

You can query for table FND_NODES and check for column , SUPPORT_CP ( for Concurrent Manager) SUPPORT_FORMS ( for forms server) , SUPPPORT_WEB (Web Server), SUPPORT_ADMIN( Admin Server), and SUPPORT_DB for database tier.

You can also check same from CONTEXT File (xml file under APPL_TOP/admin)

89: If your system has more than one Jinitiator, how will the system know, which one to pick. ?

When client makes a forms connection in Oracle Applications, forms client session uses configuration file defined by environment variable FORMS60_WEB_CONFIG_FILE also called as appsweb config file. These

days this file is of format appsweb_$CONTEXT.cfg The initiator version number defined by parameter jinit_ver_name in this file will be used .

90: While applying Apps patch using adpatch, if you want to hide the apps password, how will that be possible ?

Use adpatch flags=hidepw while applying patches in apps to hide apps or system password being displayed on Users Screen.

91: What is importance of IMAP Server in Java Notification Mailer ?

IMAP stands for Internet Message Access Protocol and Java Notification mailer require IMAP server for Inbound Processing of Notification Mails.

92: What is difference between Socket & Servlet Mode in Apps Forms ?

When forms run SOCKET Mode these are dedicated connection between Client Machine & Form Server (Started by adfrmctl.sh). When Forms run in servlet mode the forms requests are fulfilled by Jserv in Apache . There will be additional JVM for Forms Request in that case and you won’t start form via adfrmctl.sh.

93: How to find OUI version ?

OUI stands for Oracle Universal Installer. In order to find how to check OUI version Click Here on my “How to find Version in Oracle” Page.

94: What is make program in Unix ?

make is utility in Unix/Linux to maintain , update & generate an file mainly executable.

95: If by mistake you/someone deleted FNDLIBR can this executable be restored if Yes, How& if no, what will you do ?

Yes, you can restore FNDLIBR executables

run adadmin on concurrent manager node

select option 2. Maintain Applications Files menu

then select 1. Relink Applications programs

when prompts for

Enter list of products to link (‘all’ for all products) [all]

select FND

when prompt for

Generate specific executables for each selected product [No] ? YES

select YES

& from list of executables select FNDLIBR

This will create new FNDLIBR executables.

96: What is .pls files which you see with apps ?

.pls file stands for plsql files. In apps patch these files contain code to create package spec or package body or both.

97: What are .ldt & .lct files which you see in apps patch or with FNDLOAD ?

.ldt & .lct stands for Loader datafile & Loader configuration files, used frequently in migrating customization, profile options, configuration data, etc.. across Instances.

98: What are .odf file in apps patch ?

odf stands for Object Description Files used to create tables & other database objects.

99: What to find Form Server log files in forms ?

Form Server Start up log file default location is $OAD_TOP/admin/log/$CONTEXT_NAME/f60svrm.txt

Forms Run Time Diagnostics default location is $ORACLE_HOME/forms60/log/$CONTEXT_NAME

100: How to convert pll to pld file or pld file to pll ?

Pll->Pld f60gen module=MSCOSCW3.pll module_type=library userid=apps/<passwd> module_access=file output_file=MSCOSCW1.pld script=yes

Pld -> pll f60gen module=MSCOSCW3.pld userid=apps/<passwd> module_type=library module_access=file output_file=MSCOSCW1.pll parse=y batch=yes compile_all=special

101: Is APPS_MRC Schema exists for MRC in 11.5.10 and higher ?

No , apps_mrc schema is dropped with 11.5.10 Upgrade & 11.5.10 new Install. This is replaced by more Integrated Architecture.

102: If APPS_MRC schema is not used in 11.5.10 and higher then How MRC is working ?

For products like Payable, Recievables which uses MRC and if MRC is enabled then each transaction table in base schema related to currency now has an assoicated MRC Subtables.

103: When you apply C driver patch does it require database to be Up & Why ?

Yes , database & db listener should be Up when you apply any driver patch in apps. even if driver is not updating any database object connection is required to validate apps & other schema and to upload patch history information in database tables.

104: Can C driver in apps patch create Invalid Object in database ?

No , C driver only copies files in File System. Database Object might be invalidated during D driver when these objects are created/dropped/modify

105: What is dev60cgi & f60cgi ?

cgi stands for Common Gateway Interface and these are Script Alias in Oracle Apps used to access forms server . Usually Form Server access directly via http://hostname:port/dev60cgi/f60cgi

106: Why does a worker fails in Oracle Apps Patch and few scenarios in which it failed for you ?

This question sounds stupid but this is asked quite often in Apps DBA Interview. Apps Patch worker can fail in case it doesn’t find expected data, object, files or any thing which driver is trying to update/edit/modify. Possible symptoms may be underlying tables/objects are invalid, a prereq patch is missing , login information is incorrect, inconsistency in seeded data…

107: What is difference between mod_osso & mod_ose in Oracle HTTP Server ?

mod_osso is Oracle Single Sign-On Module where as mod_ose is module for Oracle Servlet Engine.

mod_osso is module in Oracle’s HTTP Server serves as Conduit between Oracle Apache Server& Singl Sign-On Server where as mod_ose is also another module in Oracle’s HTTP Server serves as conduit between Oracle Apache & Oracle Servlet Engine.

108: What is difference between COMPILE_ALL=SPECIAL and COMPILE=ALL while compiling Forms ?

Both the options will compile all the PL/SQL in the resultant .FMX, .PLX, or .MMX file but COMPILE_ALL=YES also changes the cached version in the source .FMB, .PLL, or .MMB file. This confuses version control and build tools (CVS, Subversion, make, scons); they believe you’ve made significant changes to the source. COMPILE_ALL=SPECIAL does not do this.

109: What is ps -ef or ps command in Unix ? for work ex < 1 yr

ps is unix/linux utility or executable to find status of process. Used mainly to find if services/process is running or not.

110: What is GSM in Oracle application E-Business Suite ?

GSM stands for Generic Service Management Framework. Oracle E-Business Suite consist of various compoennts like Forms, Reports, Web Server, Workflow, Concurrent Manager ..

Earlier each service used to start at their own but managing these services (given that) they can be on various machines distributed across network. So Generic Service Management is extension of Concurrent Processing which manages all your services , provide fault tolerance (If some service is down ICM through FNDSM& other processes will try to start it even on remote server) With GSM all services are centrally managed via this Framework.

111: What is FNDSM ?

FNDSM is executable & core component in GSM ( Generic Service Management Framework discussed above). You start FNDSM services via APPS listener on all Nodes in Application Tier in E-Business Suite.

112: What is iAS Patch ?

iAS Patch are patches released to fix bugs associated with IAS_ORACLE_HOME (Web Server Component) Usually these are shiiped as Shell scripts & you apply iAS patches by executing Shell script. Note that by default ORACLE_HOME is pointing to 8.0.6 ORACLE_HOME and if you are applying iAS patch export ORACLE_HOME to iAS . You can do same by executing environment file under $IAS_ORACLE_HOME

113: If we run autoconfig which files will get effected ?

In order to check list of files changes during Autoconfig , you can run adchkcfg utility which will generate HTML report. This report will list all files & profile options going to change when you run AutoConfig.

114: What is difference between .xml file & AutoConfig ?

Autoconfig is Utility to configure your Oracle Application environment. .xml file is repository of all configuration from which AutoConfig picks configuration and polulates related files.

115: What is .lgi files ?

lgi files are created with patching along with .log files . .lgi files are informative log files containing information related to patch. You can check .lgi files to see what activities patch has done. Usually informative logs.

116: How will you skip worker during patch ?

If in your adctrl there are six option shown then seventh is hidden option.(If there are seven options visible then 8th option is to Skip worker depending on ad version).

117: Which two tables created at start of Apps Patch & drops at end of Patch ?

xFND_INSTALLED_PROCESSES& AD_DEFFERED_JOBS are the tables that get updated while applying a patch mainly d or unified driver.

118: How to compile an Oracle Reports file ?

Utility adrepgen is used to compile Reports. Synatx is given below

adrepgen userid=apps\<psswd> source = $PRODUCT_TOP\srw\filename.rdf dest=$PRODUCT_TOP\srw\filename.rdf stype=rdffile dtype=rdffile logfile=x.log overwrite=yes batch=yes dunit=character

119: What is difference between AD_BUGS & AD_APPLID_PATCHES ?

AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.

AD_APPLIED_PATCHES holds information about the “distinct” Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. “merged” patches), then they are considered distinct and this table will therefore hold 2 records.

120: What exactly happens when you put an Oracle Apps instance in maintenance mode ?

Maintenance mode provides a clear separation between normal runtime operation of Oracle Applications and system downtime for maintenance. Enabling the maintenance mode feature

a) shuts down the Workflow Business Events System and

b) sets up function security so that no Oracle Applications functions are available to users.

Used only during AutoPatch sessions, maintenance mode ensures optimal performance and reduces downtime when applying a patch.

read more

Oracle Apps DBA Set 3

no thumb

Oracle Apps DBA Interview Questions and Answers

Oracle Apps DBA Interview Questions
This is the Interview season, with lots of openings World Wide. I have listed some of the very common Apps DBA interview questions faced by me. The answers are listed after each question. All the Best…..!
File System

1. What all directories will be there under any “product” top
Ans àcd $AP_TOP (Example of product top AP) (Below are various directory under each product top – ad, ap, gl..)
admin bin forms help html lib log mds media mesg out patch reports sql xml
$ Here directory “forms” contains all .fmx (compiled version of forms)
2. Where are .fmb (Forms) stored
Ans àThey will be under $AU_TOP/forms/US
NOTE* .fmb and .fmx are different
3. When you run f60gen to compile the forms : .fmx will be put to where ..?
Ans à$PRODUCT_TOP/forms/US/ of respective product of form.
4. What is stored in “mesg” directory under each product top ??
Ans à“mesg” contains language specific message files and error message for the product.
5. What is stored in “bin” directory under each product top
Ans à“bin” directory contains executable files like in $AP_TOP/bin you will see APPBCF APTZGF apxamex.ctl apxboav.ctl apxdiner.ctl apxgecmc.ctl apxusbv.ctl APXXTR
6. How to compile apps schema and when to compile??
Ans àYou can use “adadmin” utility to compile apps schema (other methods like utlrp.sql exists). Usually you compile apps after application of the patches, maintanance patch, upgrade, runtime error due to AD_DDL packages or scenarios where there are invalid objects in apps schema.
7. How many database connections are allowed during fresh installation of oracle application.
Ans à100. (Number has increased to 250 for Pluggable Databases in 12c)

Patching:

1. Utility used to apply application patch is ??
Ans à “adpatch”
Note — Various options available with adpatch are
option=nocheckfile, noautoconfig, nocompiledb, nocompilejsp, nocopyportion, nodatabaseportion, nogenerateportion, hotpatch, integrity, prereq, noprereq, nomaintainmrc, validate, nojcopy, noforcecopy, forcecopy, nolink, nogenform, nogenrep
2. For database patch utility used to apply patch is ??
Ans à“opatch”
Note — .log and .lgi files for adpatch will be under $APPL_TOP/admin/SID/log directory.
3. What are .lgi files for ?
Ans à While applying patch put the system in maintenance mode by running
$adadmin and then select option 5 (change maintenance mode) after that select options enable/disable maintenance mode. (You can also change system to maintenence mode using $AD_TOP/patch/115/sql/adsetmmd.sql)
4.By default where adadmin log file will go??
Ans à$APPL_TOP/admin/$SID/log/
5. How to check what adpatch is doing ??
Ans à“adctrl” is the utility to do check status of ad worker, including adpatch.
6. What are various stages of adpatch worker
Ans àFirst of all workers will be in “waiting” stage after that jobs will be “assigned” then “running“, If error doesn’t come it will move to “complete” (waiting -> assigned -> running -> complete)
If error comes it will go like waiting -> assigned -> running -> failed -> fixed -> restarted -> completed
7. What is Default number of workers:
Ans à2 times the number of CPU on the database server.
8. Have you ever seen worker status as deferred, while using adctrl ??
Ans àOnce you start applying patch , manager assigns jobs and unique ID to each worker. Manager will also insert one row into FND_INSTALL_PROCESSES table for each worker with information about who is doing what ???
1) CONTROL_CODE 2) STATUS, these two columns are important for us from the fnd_install_processes. Manager will monitor the workers by these two columns about status of the assigned job.Once worker is done with first job, manager will assign it the second job to do with the status update as <assigned>.
Once all the worker’s status is completed for all the jobs, manager will tell the workers to shutdown and will drop fnd_install_process table.

9. What happens if worker is working on some particular job say updating some table but that table is locked ??? so what the worker will do in such situation…
Ans àThat’s the AD_DEFERRED_JOBS table which will be created at the time of FND_INSTALL_PROCESSES and ad_deferred_jobs too will be dropped with FND_INSTALL_PROCESSES. First time when worker checks and find that table is locked, that job fails and manager will automatically defers the job, and it will assigns a new job to the worker.
The job which was waiting in ad_deferred_jobs table will be assigned again till time it will not become failed or completed.
In such situation above, worker will wait till either the lock is released or timed out is reached and We (DBA) need not to take any action till the job status becomes fails…. once it shows its failed we can fix it and restart it.
10. Why APPS schema present in the 11i application ??
Ans à It reduces traffic because all the product schemas will grant full privileges to Apps schema. so it will have full access to the complete Oracle application.
All the products like AP,AR,FIN has the code for respective products and APPS will have access to all these code objectives too. Apps will have all the synonyms for base tables and sequences as well.
OBJECT_NAME
——————————————————————————–
OBJECT_TYPE STATUS OWNER
—————— ——- ——————————
BEN_PL_PCP_S
SYNONYM VALID APPS
BEN_PL_PCP_TYP_S
SYNONYM VALID APPS
BOM_CTO_ORDER_DEMAND_S1
SYNONYM VALID APPS

Describe the AD Administration Main Menu
1. Generate Applications Files menu
2. Maintain Applications Files menu —Snapshot details
3. Compile/Reload Applications Database Entities menu ————-COMPILE APPS schema (invalid objects or runtime error)
4. Maintain Applications Database Entities menu
5. Change Maintenance Mode —–While patching
6. Exit AD Administration
Maintain Snapshot Information
1. List snapshots
2. Update current view snapshot
3. Create named snapshot
4. Export snapshot to file
5. Import snapshot from file
6. Delete named snapshot(s)

Author – Akash Pramanik
Database Administrator in IBM
akash007.pramanik@gmail.com

———————————————————————————————————————
More Question Answers (more on RAC)

1. How to find the database/sqlplus version(R12 and R11) ?
Ans : select banner from v$version;

2. Do you know some Cluster Vendor other than Oracle?
HP Tru64 Unix, Veritas, Microsoft

3. What is CRS?
Oracle RAC 10g Release 1 introduced Oracle Cluster Ready Services (CRS), a platform-independent set of system services for cluster environments. In Release 2, Oracle has renamed this product to Oracle Clusterware.

4. What is a raw partition?
A raw partition is a portion of a physical disk that is accessed at the lowest possible level. A raw partition is created when an extended partition is created and logical partitions are assigned to it without any formatting. Once formatting is complete, it is called cooked partition.

5. When to use CFS over raw?
A CFS offers:
– Simpler management
– Use of Oracle Managed Files with RAC
– Single Oracle Software installation
– Autoextend enabled on Oracle datafiles
– Uniform accessibility to archive logs in case of physical node failure
– With Oracle_Home on CFS, when you apply Oracle patches CFS guarantees that the updated Oracle_Home is visible to all nodes in the cluster.
Note: This option is very dependent on the availability of a CFS on your platform.

6. When to use raw over CFS?
– Always when CFS is not available or not supported by Oracle.
– The performance is very, very important: Raw devices offer best performance without any intermediate layer between Oracle and the disk.
Note: Autoextend fails on raw devices if the space is exhausted. However the space could be added online if needed.

7. What is VIP IP used for?
It returns a dead connection IMMIDIATELY, when its primary node fails. Without using VIP IP, the clients have to wait around 10 minutes to receive ORA-3113: “end of file on communications channel”. However, using Transparent Application Failover (TAF) could avoid ORA-3113.

8. How is possible to install a RAC if we don’t have a CFS?
This is possible by using a raw device.

9. What is a raw device?
A raw device is a disk drive that does not yet have a file system set up. Raw devices are used for Real Application Clusters since they enable the sharing of disks.

10. Where are the Clusterware files stored on a RAC environment?
The Clusterware is installed on each node (on an Oracle Home) and on the shared disks (the voting disks and the CSR file)

11. Where are the database software files stored on a RAC environment?
The base software is installed on each node of the cluster and the
database storage on the shared disks.

12. What kind of storage we can use for the shared Clusterware files?
– OCFS (Release 1 or 2)
– raw devices
– third party cluster file system such as GPFS or Veritas

13. What kind of storage we can use for the RAC database storage?
– OCFS (Release 1 or 2)
– ASM
– raw devices
– third party cluster file system such as GPFS or Veritas

14. What is a CFS?
A cluster File System (CFS) is a file system that may be accessed (read and write) by all members in a cluster at the same time. This implies that all members of a cluster have the same view.

15. What is an OCFS2?
The OCFS2 is the Oracle (version 2) Cluster File System which can be used for the Oracle Real Application Cluster.

16. Which files can be placed on an Oracle Cluster File System?
– Oracle Software installation (Windows only)
– Oracle files (controlfiles, datafiles, redologs, files described by the bfile datatype)
– Shared configuration files (spfile)
– OCR and voting disk
– Files created by Oracle during runtime
Note: There are some platform specific limitations.

17. How is possible to install a RAC if we don’t have a CFS?
This is possible by using a raw device.

18. What is a raw device?
A raw device is a disk drive that does not yet have a file system set up. Raw devices are used for Real Application Clusters since they enable the sharing of disks.

19. What is the Cluster Verification Utility (cluvfy)?
The Cluster Verification Utility (CVU) is a validation tool that you can use to check all the important components that need to be verified at different stages of deployment in a RAC environment.

20. What are Oracle Clusterware Components
Voting Disk — Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures. The voting disk must reside on shared disk.
Oracle Cluster Registry (OCR) — Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR must reside on shared disk that is accessible by all of the nodes in your cluster

read more
1 2 3 9
Page 1 of 9