Oracle Apps DBA Interview Questions

1) KeepAlive option should be used judiciously along with MaxClients directive. KeepAlive option would tie a worker thread to an established connection until it times out or the number of requests reaches the limit specified by MaxKeepAliveRequests. This means that the connections or users in the ListenBacklog queue would be starving for a worker until the worker is relinquished by the keep-alive user. The starvation for resources happens on the KeepAlive user load with user population consistently higher than that specified in the MaxClients.

2) Increasing MaxClients may impact performance in the following ways:

A high number of MaxClients can overload the system resources and may lead to poor performance.

For a high user population with fewer requests, consider increasing the MaxClients to support KeepAlive connections to avoid starvation. Note that this can impact overall performance if the user concurrency increases. System performance is impacted by increased concurrency and can possibly cause the system to fail.To avoid potential performance issues, values for any parameters should be set only after considering the nature of the workload and the system capacity.

Review the HTTP log file. The error is very clear about the missing soft link. It would show this error:
…
<physical Path>/10.1.3/Apache/Apache/bin/apachectl startssl: execing httpd
<physical Path>/10.1.3/Apache/Apache/bin/httpd: error while loading shared
libraries: libdb.so.2: cannot open shared object file: No such file or directory
…

Soft link libdb.so.2 is missing.

Apps is a schema which does not contain any tables of itself. We can say APPS is the shared runtime schema for all E-Business Suite products. It contains all the synonyms of all the table in Oracle apps. But it also contains packages, functions, procedures. The default password is apps.
Hence, apps schema has universal access to Oracle Applications.

Applsys

schema contains all the tables required for administarative purpose. The default password is apps. All the technical products’ database objects are consolidated into a single schema called Applsys
It is a schema that stores the data objects for the Applications technology layer products (FND, AD, and so on).

Applsyspub

schema is responsible for password checking.The default password is pub.Applsyspub is used for authentication by having read only views.
It is a public schema which is used only during the signon process and has no data objects, only synonyms to APPS.
This user account has very restricted privileges and has access to below objects (primarily for authentication purposes):-
FND_APPLICATION

FND_UNSUCCESSFUL_LOGINS
FND_SESSIONS
FND_PRODUCT_INSTALLATIONS
FND_PRODUCT_GROUPS
FND_MESSAGES
FND_LANGUAGES_TL
FND_APPLICATION_TL
FND_APPLICATION_VL
FND_LANGUAGES_VL
FND_SIGNON
FND_PUB_MESSAGE
FND_WEBFILEPUB
FND_DISCONNECTED
FND_MESSAGE
FND_SECURITY_PKG
FND_LOOKUPS

Q 6 What is GSM and FNDSM ?

Service processes (application tier processes such as Forms listeners, HTTP servers, and concurrent managers etc) must be kept running on an application tier for the proper functioning of their associated products. Management of the services is complicated by the fact that they may be distributed across multiple host machines. The Generic Service Management (GSM) feature simplifies management of these generic service processes, by providing a fault-tolerant framework with a central management console built into Oracle Applications Manager.

FNDSM is executable and core component in GSM (Generic Service Management Framework). GSM and Concurrent Processing are closely integrated.
You start FNDSM services via application listener on all Nodes in Application Tier in E-Business Suite.

The Service Manager (FNDSM) PID can be used to locate all concurrent manager and service processes on the node, since the Service Manager (FNDSM) is the parent process

Script to start FNDSM: gsmstart.sh

Q 7 What will happen to pending request if ICM is down?

Keep in mind that the ICM really does NOT have any concurrent request scheduling responsibilities. It has NOTHING to do with scheduling requests, or deciding which manager will run a particular request.
Its function is only to run ‘queue control’ requests, which are requests to startup or shutdown other managers. It is responsible for startup and shutdown of the whole concurrent processing facility, and it also monitors the other managers periodically, and restarts them if they should go down. It can also take over the Conflict Resolution manager’s job, and resolve incompatibilities.

So pending request will run as usual.

 Q 8 What happen if “alter user apps identified by password” is fired for apps user?

We cannot change apps password through alter user statement because Oracle Application use APPS PASSWORD to encrypt end user’s password in FND_USER and oracle user’s password in FND_ORACLE_USERID. So using FNDCPASS to change password of APPS, changes the column encrypted_oracle_password in these two tables, but alter don’t do this actions. FNDCPASS update DBA_USERS table as well.

While when you run “alter user apps identified by password” it will update only DBA_USERS.

If you have mistakenly did used “alter user”, you may see below error:

APP-FND-01496: Cannot access application ORACLE password
Cause: Application Object Library was unable access your ORACLE password.

It is very difficult to recover the application at this stage.

Q 9 How will you plan to do multi-node to single node cloning in 11i and R12?

In 11i, RapidInstall copies from the Staging Area only reports on CM nodes and Forms on the Web nodes. To do multi-node to single node clone in 11i, it is called merging appltops.

$perl adpreclone.pl appsTier merge

and then copy /clone/appl from every source appl server

Depending on which tier you chose as the primary node, certain files may be missing. Run adadmin to verify files required at runtime. If any files are listed as missing files, you must manually copy them to the merged APPL_TOP from other nodes’ APPL_TOP

In R12 it is easy to do multi node to single node cloning.
In R12, it employs a new concept called Unified Appltop, which means that no matter what you choose while installing, R12 will install a complete apps file system on all nodes.
All you need to do is choose a node for running adpreclone.pl appsTier, copy that node to target system, and run adcfgclone.pl

 Q 10 Which all tables FNDCPASS touches when changing user’s password?

 Below is how FNDCPASS works.

(1) applsys validation. (make sure APPLSYS name is correct)
(2) re-encrypt all password in FND_USER
(3) re-encrypt all password in FND_ORACLE_USERID
(4) update applsys’s password in FND_ORACLE_USERID table.
(5) Update apps password in FND_ORACLE_USERID table.

Also changes are made in DBA_USERS table.

Q 11  ADPATCH creates two tables during patch application. What are the contents of those tables.

when we are applying patches 2 tables will be created i.e
1)fnd_install_processes
2)ad_deferred_jobs

1)fnd_install_processes – This table is used to store the information about the job given to the worker. It will insert a row for each worker when it assigned a job. This table serves as a staging area for the job information, and as a way for the manager and the worker to communicate. Once all jobs are complete, the manager tells the workers to shut down, and then drops the FND_INSTALL_PROCESSES table

2)ad_deferred_jobs – this table is used to store the information about the deferred jobs (jobs failed to run).

 Q 12 What are custom schema creation steps?

Step-1 : Create a Tablespace in the backened Database for custom schema.
Step-2 : Create Schema
Step-3 : Grants Connect and Resource to schema
Step-4 : Make the directory structure for your custom application files.
Step-5 : Create Custom Environment file in APPL_TOP directory
Step-6 : Register your Oracle Schema.
Login to Applications with System Administrator responsibility
Navigate to Application–>Register
Step-7 : Register Oracle User
Naviate to Security–>Oracle–>Register
Step-8 : Add Application to a Data Group
Navigate to Security–>Oracle–>DataGroup
Step-9 : Create custom request group, custom menu etc

Q 13 How to do new product licensing?

select Site Map -> License Manager -> License Applications Product
or
$AD_TOP/sql/adlicmgr.sql

Select the product(s) to be licensed.

Click on submit button.

Q 14 What is the purpose of FNDFS?

FNDFS or the Report Review Agent (RRA) is the default text viewer within Oracle Applications, which allows users to view report output and log files. Report Review Agent is also referred to by the executable FNDFS.

When the operation of trying to view reports takes place, the Report Review Agent needs a valid connection string definition to successfully make the network connection to the application server, and this is done through the FNSFS entries presented in the tnsnames.ora file.
1) The user selects ‘Request Output’, ‘Request Log’, or ‘Manager Log’
2) The file name and nodename are selected from the database.
SELECT outfile_name, outfile_node_name FROM fnd_concurrent_requests
WHERE request_id = :id;
3) The client takes the nodename that was returned and adds FNDFS_ to the
beginning of it.
4) a connection is made to the given host. The listener on
this host receives the connection request, and resolves the SID using its
listener.ora file. If it finds a PROGRAM parameter listed for this SID, it will
launch this program. (which should be $FND_TOP/bin/FNDFS)

 Q 15 What are the basic steps for Printer Installation in EBS 11i/R12?

For most printing needs, the Pasta Utility offers quick setup and easy maintenance. For additional flexibility, Oracle E-Business Suite allows you to define your own printer drivers and print styles.

1. Setup the printer at the OS level
2. Add a valid entry in the hosts file (Printer Name and the IP Address)
3. Login to System Administrator responsibility
4. Navigate to Install > Printer > Register
5. Define a new printer by entering the Printer Name you have set in the hosts file
6. Save
7. Bounce the Concurrent Manager

Q 16 What are basic steps for Workflow Mailer configuration?

> use Oracle Application Manager (OAM) to configure Workflow Notification Mailer.
> For Outbound Notification, CM (Concurrent Manager) node should be able to connect to SMTP (Simple Mail Transfer Protocol) server/relay.
> For Inbound Notification (Optional), CM node should be able to connect to IMAP (Internet Message Access Protocol) Server.
> Log file for Workflow Mailer Notification are at $APPLCSF/$APPLLOG/FNDC*.txt
> Workflow Notification Mailer in background run as Concurrent Manager (Workflow Mailer Service, Workflow Agent Listener Service)
> If you wish to configure Inbound Notification as well then ensure IMAP Server should be configured with a valid user (create Inbox, Processed & Discard folder for this User)
> Schedule “Workflow Background Process” Concurrent Request:

1. Frequency : Every 5 Minutes – Parameter : Deferred:Yes, Timeout:No, Stuck:No
2. Frequency : Every 60 Minutes – Parameter : Deferred:No, Timeout:Yes, Stuck:No
4. Frequency : Daily – Parameter : Deferred:No, Timeout:No, Stuck:Yes
5. Frequency : Every 10 Minutes – Parameter : Deferred:Yes, Timeout:No, Stuck:
6. Frequency : Every 6 Hours – Parameter : Deferred:Yes, Timeout:Yes, Stuck:Yes
The main component of the Oracle Workflow Notification Mailer is the executable WFMAIL. This is a server side program that queries the database for any pending
notifications. It then dispatches these notifications by calling sendmail for UNIX and the MAPI APIs for Microsoft Windows NT. The notification mailer also queries the local inbox for incoming messages. These messages are validated and then passed to the database for response processing. To configure Workflow Notification Mailer we have to do OS level setup (Sendmail) and Application level setup.

Q 17 Is it possible to upgrade to R12.1.1 from 11.5.9?

Yes
Applications R11.5.6 and below requires to first upgrade to R11.5.10.2/10gR2
Applications R11.5.7 and up can be directly upgraded to R12

But 11.5.9 can not be upgraded to R12.2 directly.

 Q 18 What are the high level steps for upgrading 11i to R12.1.3?

– Upgrade database 9.2.0.6 to 11.2.0.4 in 11i
* Upgrade database from 9.2.0.6 to 9.2.0.8 and then to 11.2.0.4.
* Apply all interoperability patches for EBS 11i to work with 11.2.0.4 database.
* Upgrade JDK/JRE to supported version.

– If you want to use R12 on 64 Bit, 11i Split configuration- Move 11g Database to Linux 6 64Bit.

* Install 64 Bit Database 11.2.0.4 software on the new 64 Bit Linux machine.
* Move 11i database from old Linux 32-bit to new Linux-64 bit machine and attach 11i application to new 64 Bit database.

– Upgrade 11i TO R12.1.1

* Prepare 11i database for upgrade (configuration, applying patches etc).
* Install R12.1.1 upgrade filesystem on the same 64 Bit Linus machine that we configured earlier and where our database currently run.
* Configure R12.1.1 application with existing 11.2.0.4 64 Bit database.
– Upgrade R12.1.1 To R12.1.3

* Upgrade all ORACLE_HOMEs (database, forms 10.1.2, web 10.1.3)
* Upgrade R12.1.1 application to R12.1.3 on Linux 64 Bit machine.

Q 19 How does one process more concurrent requests concurrently?

The Concurrent Manager parameters, (Query the concurrent manager by Login as Sysadmin, navigate -> Concurrent -> Manager -> Define and Query for the relevant concurrent manager), should be modified to handle more concurrent requests concurrently, this can be done in two steps:

(i) Increase the Number of Target processes for the manager
(ii) Change the cache size of the concurrent manager as this determines how many requests will be evaluated by a manager at a time and should match the target (process) value as set above.

Q 20 How to restart a patch? How will you take backup of tables – FND_INSTALL_PROCESS, AD_DEFERED_JOBS?

Sometimes  you need to apply a second patchin the middle of a running patch.

1. Use adctrl (option 3) to tell all the existing workers to QUIT

2. Use adctrl (option 5) to tell managers that all workers have QUIT. (Adpatch session ends!)

3. Backup tables applsys.ad_deferred_jobs and applsys.fnd_install_processes.
Login as APPLSYS user and execute:
ALTER “TABLE” applsys.ad_deferred_jobs “RENAME” TO ad_deferred_jobs_old;
ALTER “TABLE” applsys.fnd_install_processes “RENAME” TO fnd_install_processes_old;
ALTER “INDEX” applsys.ad_deferred_jobs_u1 “RENAME” TO ad_deferred_jobs_u1_old;
ALTER “INDEX” applsys.fnd_install_processes_u1 “RENAME” TO fnd_install_processes_u1_old;
4. Go to $APPL_TOP/admin/SID/ for example $APPL_TOP/admin/TEST/ and rename the existing directory “restart”

$mv restart restart.old

5. Use adpatch to apply the second/other patch.

6. Login as APPLSYS and revert back to the original tables, ad_deferred_jobs and fnd_install_processes
ALTER “TABLE” applsys.ad_deferred_jobs_old “RENAME” TO ad_deferred_jobs;
ALTER “TABLE” applsys.fnd_install_processes_old “RENAME” TO fnd_install_processes;
ALTER “INDEX” applsys.ad_deferred_jobs_u1_old “RENAME” TO ad_deferred_jobs_u1;
ALTER “INDEX” applsys.fnd_install_processes_u1_old “RENAME” TO fnd_install_processes_u1;
CREATE SYNONYM FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;
CREATE SYNONYM AD_DEFERRED_JOBS FOR APPLSYS.AD_DEFERRED_JOBS;
7. Replace the original restart directory:

cd $APPL_TOP/admin/SID/ for example $APPL_TOP/admin/TEST/
mv restart restart_new
mv restart.old restart

8. Run adpatch to continue the first patch (with continue session?Yes)

9. Use adctrl (option 2, will change status to “Fixed/Restart”) to restart the failed workers for first patch

 Q 21 How will you reduce down time in 11i to R12 upgrade?

APPLICATION LEVEL
—————–
– Take advantage of patch merge & hot patching of help/nls portions
– Use Shared APPL_TOP, shared application tier with Distributed AD (use of multiple application nodes to run ADPATCH in parallel)
– Prepare a complete list of pre and post patches and recommended code levels including CUP – Critical Upgrade Patches
– Apply latest RUPs for ATG/AD/OAM prior to upgrade
– Purge Data aggressively
– Re-gather Statistics close to start of actual upgrade downtime
– Batch size – 10K is suitable for most installs, you can test other values from 1K up to 100K
– Number of Workers – Starting rule-of-thumb is between 1 and 1.5 x #CPUs
– Order NLS Sync patchsets in Oracle instead of download and apply it for each patch.
– use staged application system ( not good for r12.2)
DATABASE LEVEL
————–
– Maximize SGA and PGA sizing – Adjust with help from AWR pool advisories
– Set job_queue_processes = # of CPUS
– Set parallel_max_servers = 2 X CPUs
– Double java_pool_size
– Shutdown other RAC instances on same server
– Re-create your redo-logs – no mirrors, 2GB+
– Make sure you’ve applied the latest PSU for your DB version (especially if you’re doing an 11i to R12 upgrade with DB upgrade as well).
– Turn off archive logging – use interim snapshot backups for rollback points
– Gather statistics with higher estimate.
HARDWARE LEVEL
—————
– H/W and OS planned changes – put as much CPU as possible on database node and RAM.
– CPU Server utilization in testing cycle (scale down if at 100%)
– Implement “huge pages” to offload your CPUs.
– Check Server Memory utilization in testing cycle (no swapping/ excessive paging)

Q 22 What are high level important steps in R12.2 cloning process? 

PART 1 : PREREQUISITE TASKS
—————————-
– check for in-progress online patching cycle

PART 2 : PREPARE SOURCE SYSTEM
——————————
perl adpreclone.pl dbTier
perl adpreclone.pl appsTier
**require Apps User password and Weblogic AdminServer password
In 12.2, the adpreclone.pl process on the application tier creates a complete compressed archive of the Oracle Fusion Middleware and its components

PART 3 : COPY APPLICATION TIER NODE
———————————–
Copy Application Tier node from the Source “Run Edition File System” to the Target “Run Edition File System”.
so if source run edition is fs2, then target run edition will also be fs2
Only copy “EBSapps” directory from under $RUN_BASE. DO NOT COPY “inst” or “FMW_Home”

PART 4 : COPY DATABASE TIER NODE
——————————–
do normal RMAN cloning
PART 5 : CONFIGURE TARGET DATABASE SYSTEM
—————————————–
SQL> exec fnd_conc_clone.setup_clean;

$ perl adconfig.pl dbTier
PART 6 : CONFIGURE TARGET APPLICATION SYSTEM
——————————————–
$ perl adcfgclone.pl appsTier

RUN ADPRECLONE ON RUN EDITION <<< NEW STEP IN R12.2
COPY TARGET RUN EDITION OVER TARGET PATCH EDITION <<< NEW STEP IN R12.2
RUN ADCFGCLONE ON TARGET APPLICATION NODE PATCH EDITION <<< NEW STEP IN R12.2

PART 7 : FINISHING TASKS
————————-
Update profiles, workflow settings, SESSION_COOKIE_DOMAIN, SSL and SSO configuration as required
Changing SYS/SYSTEM/APPS/SYSADMIN/OTHER ORACLE passwords as per business requirements

Q 23 AD_BUGS VS AD_APPLIED_PATCHES. When to use which table?

– AD_BUGS: holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.
So this table holds information about all bug fixes that have been applied. Even if this patch have been included in other patch.

– 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_
So it contains information on patches you installed directly (running adpatch driver=u<patch>.drv).

 Q 24 What is forms server? Explain briefly, how the connection of the forms server works?

The forms server is that server which the forms are hosted. It’s a component of middle tier. The forms server can be hosted from more than one node and the load balancing can be implemented with the forms. The forms user interface is used in the desktop clients for working in Oracle Applications.

Below is what happens:
– Browser receives Java Applet (JAR files) and begins to run them in its JVM
– The Java thin client connects to the forms listener via a TCP/IP socket or an HTTP port. The forms listener is already started, and listens for these requests.
– Forms Listener allocates a forms runtime engine
– Java Applet connection is passed from Forms Listener to forms runtime engine. Forms runtime engine loads module(s) needed to run the requested form. As per form name received, Forms runtime engine loads the form and any libraries and/or menus required by that form.
– Forms runtime engine opens a connection to the database.

Q 25 Describe Purge programs in EBS and their significance?

Below are two important purge programs:

1) Purge Concurrent Requests and/or Manager Data

– delete information about completed concurrent requests from below set of tables

FND_CONCURRENT_REQUESTS,
FND_RUN_REQUESTS,
FND_CONC_REQUEST_ARGUMENTS,
FND_CONC_STAT_LIST,
FND_CONCURRENT_PROCESSES,
FND_CONC_STAT_SUMMARY,
FND_CONC_PP_ACTIONS,
FND_RUN_REQ_PP_ACTIONS
and FND_DUAL tables.

– also deletes the log and output files for those concurrent requests from your UNIX file system
– For parameter ‘Mode], choose Age so it could delete files older than the number of days specified in Mode Value.

2) Purge Obsolete Workflow Runtime Data

Workflow records data about each step of a running workflow in the

WF_ITEMS,
WF_ITEM_ACTIVITY_STATUSES,
WF_ITEM_ACTIVITY_STATUSES_H,
WF_ACTIVITY_ATTR_VALUES,
WF_NOTIFICATIONS, and
WF_NOTIFICATION_ATTRIBUTES

Q 26 In the cloning process, when do you utilize dbTechStack vs just dbTier?
perl adcfgclone.pl dbTechStack vs perl adcfgclone.pl dbtier

There are different components with RapidClone that are used when cloning an Oracle Applications instance. These are:

dbTechStack (RDBMS ORACLE_HOME)
database (database only, including control file creation)
dbconfig (database only, with no control file creation)
dbTier (both dbTechStack and database)

atTechStack (Tools and Web ORACLE_HOMEs)
appltop (APPL_TOP only)
appsTier (both atTechStack and appltop)

perl adcfgclone.pl dbtier
This will configure the ORACLE_HOME on the target database tier node + recreate the control files. This is used for cold backup.
perl adcfgclone.pl dbTechStack
This will configure the ORACLE_HOME on the target database tier node only. When running this command you will have to recreate the control files manually.
This is used to clone the Database separately, for example using RMAN hot backup.

When you use “dbTier” option, the perl script will configure both the tech stack and data stack(ORACLE_HOME and Oracle Database) whereas “dbTechStack” option will only configures ORACLE_HOME and it WILL NOT create database and this is generally used while doing hot clone where db creation is a manual step.

 Q 27 How To Verify Application Of Pre-install Patches As They Are Not Recorded In Tables AD_APPLIED_PATCHES Or AD_BUGS?

The preinstall mode of adpatch will not update the ad_bugs neither the ad_applied_patches tables. The preinstall mode is used when Oracle may require
the install of a patch before running AutoInstall to install or upgrade Oracle Applications. Otherwise, the patch should be applied in normal mode.

Please check the read me of the patch you are applying in pre install mode. It would typically show the files and file versions installed by the patch.
By checking the file versions in the read me is the easy way to verify if the patch is installed.

AutoPatch stores patch information in the database automatically each time it successfully applies a patch. However, if the patch is not applied successfully, or when you run AutoPatch in pre-install mode, patch history is not written directly to the database, but instead is written to these patch information files:

javaupdates.txt, which contains information about changes to Java files

adpsv.txt, which contains information about changes to all files except Java files

Both files are located in the /admin/ directory. Each time you run AutoPatch, it checks this directory for the existence of the patch information files. If it finds them, it automatically uploads the information they contain to the patch history database. If the upload is successful, AutoPatch then deletes the files from the directory. The AutoPatch log file records whether the upload was successful or unsuccessful.

Q 28 Does Last Update Date Column In The AD_APPLIED_PATCHES Table Get Updated For Every Patch Run?

There is no information captured in the AD_APPLIED_PATCHES Table for every re-application of a patch.

Users should refer to tables like ad_patch_runs,ad_patch_run_bugs and ad_patch_run_bug_actions to see change in last updated date as these tables hold information of various runs of a patch and the bugs that may or may not have got applied in a particular invocation.

Whereas the tables like ad_applied_patches and ad_bugs do not get updated their rows with various invocations of adpatchand hence last updated date also does not change.