50 Oracle Apps DBA Interview Questions and Answers

Describe the architecture of Oracle Applications?

Oracle Applications follow a 3 Tier architecture:

- Database Tier
- Middle Tier
- Desktop Tier

- Database tier contains RDBMS database called as oracle database server.

- Database tier stores all the data needed by oracle applications.

- Middle tier or Application Tier containing various servers configured with listeners like web server, forms server, admin server, reports server.

- Database server communicates with middle tier, doesn’t directly communicate with desktop tier.

- Desktop tier contains client desktop where users access oracle applications through web browsers.

How do you delete a responsibility from oracle applications?

- Responsibility is a level of authority given to Oracle Applications users.

- It is a collection of requests, forms and menus attached to user.

- Records are used for monitoring and security purpose so we can’t disable responsibilities.

- However, responsibilities can be disabled by assigning an end date to the effective period.

- We can delete a responsibility from both backend and frontend.

What are the components of Oracle Applications technology layer?

Oracle Applications technology layer comprises the following products:

- Oracle Applications DBA (AD)
- Oracle Applications object library (FND)
- Oracle Common Modules (AK)
- Oracle Applications Utilities (AU)
- Oracle Alert (ALR)
- Oracle Workflow (WF)
- Oracle Applications Framework (FWK)
- Oracle XML Publisher (XML)

How many Oracle Homes are there in Oracle Applications and what is the importance of each one of them?

Oracle Applications have three Oracle Homes:

- Oracle Home is an environment where oracle products run.

- Database Oracle Home in the database tier that acts as the Oracle Home for the Oracle database.

- Oracle Home in the application tier called as 8.0.6 Oracle Home. It is called the technology stack Oracle Home and used by forms, reports and discoverer.

- IAS Oracle Home, used by the Oracle HTTP Server (Web Listener).

- We can run multiple releases of same products simultaneously by using multiple oracle homes.

Can I enable real application clusters in the database tier along with oracle applications?

- Yes, real application clusters can also be configured with Oracle Applications

- RAC allows multiple instances to mount and open a single database.

- RAC requires shared disk storage and a dedicated network interconnect.

- When any one of the node fails, database services can be still available on remaining nodes.

- In that case, more than one instance of Oracle runs and the data file are stored at a central location accessible from the entire instance.

Where are Apache log files and configuration files stored and list each of them?

- Apache log files are stored in $IAS_ORACLE_HOME/Apache/Apache/logs.

- The log files are error_log, error_log_pls, access_log and access_log_pls.

- Apache configuration files are stored in $IAS_ORACLE_HOME/Apche/Apache/conf directory.

- The main configuration files are httpd.conf, apps.conf, httpd_pls.conf,oprocmgr.conf, and oracle_apache.conf.

How to recreate a DBC file and where is it located?

- Running Autoconfig will recreate the DBC file.

- DBC is a database connect descriptor file which stores database connection information used by application tier (web browsers) to connect to database.

- The file is located in FND_SECURE directory.

- you can also create the DBC file by running the script adgendbc.sh

- We can find the above script at $COMMON_TOP/admin/install directory.

List out the important configuration files in APPL_TOP?

Important configuration files available in the APPL_TOP are:

- APPLSYS.env/APPSORA.env
- Adovars.env
- SID.xml
- Adconfig.txt
- Adjareas.txt
- Topfile.txt
- Appsweb.cfg
- Hostname_SID.dbc
- Adpltfrm.txt
- Adjborg.txt
- Adjborg2.txt

Which utility is used for changing the password of the application users and how it runs?

- The password of the application users can be changed using the FNDCPASS utility.

- You must have system and applications password for running FNDCPASS.

FNDCPASS will run in the following manner:

- FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME.
- FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1
- FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME.

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.

What is a patch and name some different types of patches?

- Patch is a program which fixes the bug.

There are 4 different types of patches:

- One-off patch:

- This is the simplest type of patch. It is created to resolve a particular problem.
- Mini pack Patch:
- It is a collection of one-off patches related to a particular module or product.
- Mini Pack version of module is denoted by Alphabetic characters.

- Family pack patch:

- This is a collection of mini pack patches in one family.
- Alphabetic characters denote the family pack version.

- Maintenance pack patch:

- This is a collection of family pack patches.
- Oracle Applications Release 11.5.10 is an example of Maintenance pack.

List out APPS 11i post installation steps?

- Shutdown all Oracle Application Processes.

- Apply Inter-Operability Patch(3830807).

- Apply Discoverer Patch(3170128).

- Re link all Oracle Applications executables.

- Finally, Restart Oracle Application Processes.

What are the contents of patch and drivers present in patching?

Contents of patch:

- Readme files
- Driver files
- Metadata files
- Replacement files

Drivers present in patching:

C driver - It copies all the files and links the executables.

D driver – It is responsible for running SQL scripts which updates the database.

G driver - It is responsible for generating forms, reports and message files.

U driver – It is a united driver containing all copy and database actions.

What do you do if patch fails?

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

How can I determine the effects a patch will have on my application system?

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 no of files introduced by the patch.

- Files on the target system changed by the patch.

- Files which depend on patched files.

How can you reduce the downtime when you have to apply multiple patches?

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

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 about the 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.

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.

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.

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.

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.

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.

What are the 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 and zone properties
- Plsql.conf
- Wdbsvr.app

What are GSM and FNDSM?

- GSM stands for Generic Service Management Framework.

- Oracle E-Business suite contains many services used to start at their own earlier, but managing these services are distributed across network.

- So, GSM is an extension of concurrent processing which manages all your services.

- With GSM all services are centrally managed.

- FNDSM is executable and core component in GSM.

What are cursor attributes?

- Cursor attributes are used to get information about the current status of your cursor.

Both explicit and implicit cursors have four attributes as shown:

FOUND - It Returns TRUE if record was fetched successfully, FALSE otherwise.

NOTFOUND - Returns TRUE if record was not fetched successfully, FALSE otherwise.

ROWCOUNT - Returns number of records fetched from cursor at that point in time.

ISOPEN - Returns TRUE if cursor is open, FALSE otherwise.

What is the purpose of cluster?

- Cluster is used storing table data.
- It contains group of tables that share the same data blocks.
- Suppose if you cluster EMP and DEPT table, both share same DEPTNO column.
- Oracle stores all rows of each department physically from both the EMP and DEPT tables in the same data blocks.
- Clusters can’t be used for tables that are frequently fetched.

How to confirm if Apps Listener is up and running?

Execute the following commands:
If the listener is up, you will observe the following output:
- Services summary
- FNDFS has 1 service handler(s)
- FNDSM has 1 service handler(s)

What is Adadmin and why it is used?

- Adadmin (Ad administration) is used to perform a number of administrative tasks to maintain the Oracle Applications.
- For Oracle Applications to run smoothly Adadmin is used.
- Adadmin performs work at both database level and file system level.
- Application users are requested to provide all inputs at Adadmin prompt which displays various options from Adadmin menu.
- You can run non_interactive tasks and this is really useful for scheduling routine tasks that require no user intervention.

What are the adadmin activities that can be run in a parallel way?

- Recreate grants and synonyms.
- Compile APPS schema
- Maintain multiple reporting currencies schema.
- Convert to Multi-org.
- Generate message files.
- Generate form files.
- Generate report files.

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.

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.

What are generate and unified driver and what does they 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.
- 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.

Explain about Forms server?

- Forms server is a component of Oracle Applications server which is used to deploy Forms applications across the web.
- It is previously called as Web Forms.
- It consists of Forms Listener, Runtime engine, Forms CGI.
- Forms listener manages connections and Runtime engine.
- Runtime engine contains Application logic, Data management and pl/sql engine.
- It is an optional entry point and used for Load balancing.

How to check whether forms server is running or not and how to change the port of the forms server?

- Forms server communicates with forms client and Database server.
- Check for the process f60ctl, if it is running it means the forms are up and running.
- You can find the Forms Server listener's log file at $COMMON_TOP/admin/install/f60srvm.txt.
- You can change the port numbers at f60ctl start by specifying port parameters.
- Modify the file appsweb.cgf which is available at $OA_HTML/bin.

Name some of the files related to forms?

Forms files include Script files, Configuration files and text files:

Adfmsctl.sh – It is used for starting and stopping the forms metric client.
Appsweb.cfg – It is used for defining parameter values used by forms CGI located in $OA_HTML/html/bin.
Appsbase.html – This is the default HTML file for starting an applet.
D21c60.txt – This is forms metric client log file.
D21s60.txt – This is forms metric server log file.

What is the difference between socket and servlet mode forms and list some advantages & disadvantages of each?

- In socket mode, we can find a dedicated connection between client desktop and the forms server.
- In servlet mode, Jserv processes the forms requested.
- Socket mode uses up to 40% less bandwidth than forms servlet mode.
- Socked mode is not supported on windows-based platforms.
- Servlet connections can be reestablished if network connections drop unexpectedly whereas it is not possible with socket mode.
- Servlet mode HTTP & HTTPS traffic can be easily recognized by routers, whereas socket mode communication is treated as suspect.

How do I change from socket to servlet mode and vice versa?

- First, you must login to Oracle Applications Manager
- Then you must navigate to sitemap, then find the Autoconfig.
- In Autoconfig you can able to change from socket mode to servlet mode and vice versa
- Run the autoconfig from backend and restart the forms server.
- Due to many advantages Forms servlet mode is preferred and is always recommended for web deployment.

What is JInitiator?

- It is a java virtual machine provided for running web based oracle forms applications inside a client web browser.
- It is implemented as a plug-in or ActiveX Object.
- It allows you to specify the use of Oracle-certified JVM instead of relying on default JVM provided by the browser.
- It is automatically downloaded to a client machine from the application server.
- Its installation and update is performed by standard plug-in mechanism provided by the browser.

What is the function of Oracle HTTP (Apache) server?

- Web listener accepts the Http requests coming from the client browsers and web server services the request.
- Apache server forwards advanced processing requests to the servlet engine.
- Servlet engine forwards this requests to database.
- If the Http request needs parsing a JSP file then web listener checks the nature of the request.
- It then contacts the Jserv to run a JSP.
- The JSP contacts the database for the information and returns a HTML page displayed in the web browser.

What should I do for debugging when I login to the oracle applications from PHP based applications to avoid hanging?

- First check the Apache log files.
- Then in the Apache log directory, check for the access_log and error_log.
- If you are unable find any information from above steps then check for Jserv logs and JVM.
- Analyze all the errors that you are getting from log files and fix them
- Still you are not able to debug, then perform AOL diagnostics test.

What kind of maintenance activities can be performed from admin server?

Following maintenance activities are performed from admin server:

- Applying the patches
- Maintaining Oracle Applications
- Applying the ad utilities
- Upgrading Oracle Applications

What is the significance of appsweb.cgf file and where is it located?

- It defines the parameter values used by forms WEB CGI.
- It is the main configuration file used by forms server.
- It contains forms server Name, ServerPort, DomainName
- It stores Database Connection Parameters and Jinitiatorversion.
- It is located at $OA_HTML/bin.

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.

What are the steps to clone from a single node to a multi-node?

Steps:

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

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

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.

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

What is ad splice and what does it do?

- Oracle often releases new products known as off cycle products.
- These products are up gradation to the base releases of Oracle applications.
- Ad splice is the utility which takes care of bringing an off cycle product in to oracle applications.
- Ad splice registers off cycle products as active in the system.
- Therefore, ad utilities recognize the off cycle products as valid product for a particular product release.

What are the important configuration files used by Apache run along with Jserv?

- $APACHE_TOP/Apache/conf/httpd.conf
- $APACHE_TOP/Jserv/etc/Jserv.conf
- $APACHE_TOP/Jserv/etc/Jserv.properties
- $APACHE_TOP/Jserv/etc/zone.properties
- $APACHE_TOP/Apache/conf/oracle_apache.conf
- $APACHE_TOP/Ojsp/conf/ojsp.conf

How do I check the version of Apache?

Version of Apache can be checked using the following command:Output is given as below:From the above output we can know the version of the apache.