Wednesday, September 28, 2011

Master DBA and Apps DBA interview questions

Below are some of the Master DBA and Apps DBA:

1)Explain the installation and configuration of Oracle SOA?
What steps you followed for building Oracle SOA setup?
What were the major issues faced by you during Oracle SOA installation and configuration?
(Oracle Fusion DBA interview question)

Ans: Check the below link for the installation and configuration process:

When installing and configuring SOA.The Repository creation utility(RCU) and SOA version should be same.
Eg: If we are using Oracle RCU version than we have to SOA version also
I Installed the Oracle RCU version and SOA version so was facing the issue like SOA infra was not coming up so I upgraded the Oracle SOA version to by using upgrade option,while using Oracle SOA version than after upgradation,I started SOA managed server it worked.

For Starting the SOA(Service-Oriented Architecture) managed server:

Set the SERVER_NAME and ADMIN_URL environment variables, then run the script with no arguments.

[oracle@nodefmw bin]$ pwd
Usage: startManagedWebLogic [SERVER_NAME] [ADMIN_URL]

for example:

startManagedWebLogic managedserver1 http://localhost:7001
./startManagedWegLogic http://
#./ soa_server1

For Starting the BAM(Business Authentication Module) managed server:

#./ bam_server1

2)Have you done DMZ setup?What are the steps followed in doing DMZ setup?
(Oracle Apps DBA interview question)

Ans: Thanks Navdeep for explaing the things beautifully and clearly.For detail steps please
check the Navdeep's link.

For implementing DMS check the MY ORACLE SUPPORT note:
Oracle E-Business Suite R12 Configuration in a DMZ [ID 380490.1]

A DMZ (De-Militarized Zone) is a separate part of an organization’s network which is a shielded and 'cut off' from the main corporate network and its systems.The DMZ contains technical equipment to prevent access from external parties(say on the Internet) from gaining access to your main systems.

In the IT industry,a demilitarized zone is a single or multi-segment perimeter network that demarks the portion of the corporate network that lies between the intranet and outside networks.Corporate DMZ borders are enforced by firewalls and other dedicated networking devices.

When configuring Oracle E-Business Suite in a DMZ configuration,
firewalls are deployed at various levels to ensure that only the traffic that the architecture expects is allowed to cross the firewall boundaries.
The firewalls ensure that if intrusion attempts against machines in the DMZ are successful,the intrusion is contained within the DMZ and the machines in the intranet are not affected.

The steps followed in doing DMZ setup are as follows:

To make Oracle E-Business Suite modules as secure as possible, the following tasks may need to be performed.

* Use of separate web node for external usage
* Setting of server level profile values
* Associate trust levels to application middle tier nodes
* Mark a subset of responsibilities as available on an external web node
* Deploy a Reverse proxy in front of the external web node
* Configuring a URL firewall and mod security in the reverse proxy
* Run only the required Oracle E-Business Suite Application services on the external web tier

3)What are the background processes in Oracle ASM explain?(oracle DBA interview question)
Ans: The following background processes are an integral part of Automatic Storage Management:

1)ARBn(Actual Rebalance)
* ARBn(Actual Rebalance) performs the actual rebalance data extent movements in an Automatic Storage Management instance.
* There can be many of these processes running at a time, named ARB0, ARB1, and so on.

2)ASMB(ASM Balance)
* ASMB(ASM Balance) runs in a database instance that is using an ASM disk group.
* ASMB communicates with the ASM instance, managing storage and providing statistics.
* ASMB can also run in the ASM instance. ASMB runs in ASM instances when the ASMCMD cp command runs or
* when the database instance first starts if the SPFILE is stored in ASM.

3)GMON(Global monitor)
* GMON(Global monitor) maintains disk membership in ASM disk groups.

* MARK marks ASM allocation units as stale following a missed write to an offline disk.
* This essentially tracks which extents require resync for offline disks and

* RBAL runs in both database and ASM instances.
* In the database instance,it does a global open of ASM disks.
* In an ASM instance, it also coordinates rebalance activity for disk groups.

4)What are the parameters specific to Oracle ASM explain?(oracle DBA interview question)
Ans: The following parameters for specific to ASM instance They are:

Ofcourse,there are other parameters also like:

Let us briefly discuss them,

The ASM_DISKGROUPS initialization parameter specifies a list of the names of disk groups that an ASM instance mounts at startup. Oracle ignores the value that you set for ASM_DISKGROUPS when you specify the NOMOUNT option at startup or when you issue the ALTER DISKGROUP ALL MOUNT statement. The default value of the ASM_DISKGROUPS parameter is a NULL string. If the parameter value is NULL or is not specified, then ASM does not mount any disk groups.

The ASM_DISKGROUPS parameter is dynamic. If you are using a server parameter file (SPFILE), then you should not need to manually alter the value of ASM_DISKGROUPS. ASM automatically adds a disk group to this parameter when the disk group is successfully created or mounted. ASM also automatically removes a disk group from this parameter when the disk group is dropped or dismounted. The following is an example of setting the ASM_DISKGROUPS parameter dynamically:



The ASM_DISKSTRING initialization parameter specifies a comma-delimited list of strings that limits the set of disks that an ASM instance discovers. The discovery strings can include wildcard characters. Only disks that match one of the strings are discovered. The same disk cannot be discovered twice.

The discovery string format depends on the ASM library and the operating system that are in use. Pattern matching is supported; refer to your operating system-specific installation guide for information about the default pattern matching. For example, on a Linux server that does not use ASMLIB, to limit the discovery process to only include disks that are in the /dev/rdsk/ directory, set ASM_DISKSTRING to:


The asterisk is required. To limit the discovery process to only include disks that have a name that ends in disk3 or disk4, set ASM_DISKSTRING to:

/dev/rdsk/*disk3, /dev/rdsk/*disk4


The ASM_POWER_LIMIT initialization parameter specifies the default power for disk rebalancing.
The default value is 1 and the range of allowable values is 0 to 11 inclusive. A value of 0 disables rebalancing.
Higher numeric values enable the rebalancing operation to complete more quickly, but might result in higher I/O overhead.


The ASM_PREFERRED_READ_FAILURE_GROUPS initialization parameter value is a comma-delimited list of strings that specifies the failure groups that should be preferentially read by the given instance. This parameter is generally used only for clustered ASM instances and its value can be different on different nodes. For example:

diskgroup_name1.failure_group_name1, ...

The ASM_PREFERRED_READ_FAILURE_GROUPS parameter setting is instance specific.
This parameter is only valid for clustered ASM instances and the default value is NULL.
The ASM_PREFERRED_READ_FAILURE_GROUPS parameter is valid only in Oracle RAC environments.


The INSTANCE_TYPE initialization parameter must be set to ASM for an ASM instance.
This is a required parameter and cannot be modified. The following is an example of the INSTANCE_TYPE parameter
in the initialization file:



You do not need to set a value for the DB_CACHE_SIZE initialization parameter if you use automatic memory management.

The setting for the DB_CACHE_SIZE parameter determines the size of the buffer cache.
This buffer cache is used to store metadata blocks. The default value for this parameter is suitable for most environments.


The DIAGNOSTIC_DEST initialization parameter specifies the directory where diagnostics for an instance are located.
The value for an ASM instance is of the form:


For an ASM instance, db_name defaults to +asm.


You do not need to set a value for the LARGE_POOL_SIZE initialization parameter if you use automatic memory management.

The setting for the LARGE_POOL_SIZE parameter is used for large allocations.
The default value for this parameter is suitable for most environments.

5)What all the possible ways to convert/copy/transfer non-asm filesystem to asm or viceversa?
What all the conversions possible in asm?(oracle DBA interview question)
Ans: In 10g the possible ways to migrate - DBMS_FILE_TRANSFER, rman (copy vs.
backup), or XMLDB FTP
In 11g, we introduced the asmcmd copy command. Key point here is that copy files
out is great for:
1. archive logs
2. Controlfiles
3. Datafiles for debugging
4. Dumpsets (can be done across platforms)
Copying files in:
Copy in only supported files.
ASMCMD> cp expdp_5_5.dat sys@rac1.orcl1:+DATA/dumpsets/ex
source +fra/dumpsets/expdp_5_5.dat
target +DATA/dumpsets/expdp_5_5.dat
copying file(s)...
file, +DATA/dumpsets/expdp_5_5.dat,
copy committed.

6)What is ACFS?what are the advantages of ACFS?(oracle DBA interview question)
Ans: The new feature added in Oracle11g release2 for ASM is ACFS(ASM cluster file system)
ACFS is available in 11gR2 for LINUX and few unix platforms and Windows operating system
also have a similar solution is available.

ACFS will work with Single Instance Installations as well as Cluster Databases.
It is strongly integrated with the Oracle Clusterware 11g release2.
One more point to note is - Supported protocols for NAS are NFS(Network file system and CIFS(Common Internet File System).

This new feature is also named Unified Storage Management and allows for storing the following data in ASM:

1)Application File Data
2)Oracle Cluster Registry (OCR)
3)Voting Disk and
4)Oracle Binaries

Major advantages of ACFS are:

1)We will have all the trace files, alert.log,reports in ASM which makes ASM a complete storage management system .Because of this reason both database and non-database files are stored ins ASM and it completely eliminates the need for any third party cluster file systems like veritas and sun clusters.

2) ACFS uses a journaling mechanism and uses endian independent metadata to support large files and file systems to an extent up to exabytes.
A special metadata log structure is maintained for integrity checks and fast recovery through a metadata transaction engine and

3)File systems in ASM can dynamically be expended and contracted with no downtime.
High availability is fully supported through mirroring and striping capabilities for all files now with ASM.

7)what is interconnect(Cache fusion) in Oracle rac?(oracle DBA interview question)
Ans: Starting with Oracle 9i, RAC can transfer blocks from one instance to another this can be called as interconnect (cache fusion).

8)Have you done workflow configuration?(oracle Apps DBA interview question)
Ans: Best link with explaination I found:

9)Explain the importance of autoconfig file?(oracle Apps DBA interview question)
AutoConfig uses a file called context file, which is used to configure changes.Context file is a XML file having all the parameters of application system.
These parameters are part of some configuration file for some services or it may be part of some environment file.When we create an application system (using Rapid install),context file gets created automatically will all the parameters
and there values. Most of the parameters take default value,where as some parameters takes the value that is supplied
at the time of installation.

All the information required to configure Oracle Apps 11i/R12 are stored in files called as Context file.So there are two context file, one for Apps Tier(Middle Tier) and one for DB Tier.

Oracle apps 11i Context file location is as follows:

Apps Tier:$APPL_TOP/admin/$TWO_TASK.xml
DB Tier:$ORACLE_HOME/appsutil/$TWO_TASK.xml

Oracle apps R12 Context file location is as follows:

Apps Tier:$INST_TOP/appl/admin/$TWO_TASK_.xml
DB Tier:$ORACLE_HOME/appsutil/$TWO_TASK_.xml

Where: TWO_TASK is the SID set for EBS Database.

More details about autoconfig can be extracted from the below mentioned link with nice explaination:

Context file is repository for configuration stored in xml format.
Its xml file and file name format is _.xml in Oracle Apps 11i

10)What all the techniques to improve oracle application patching mechanism?
(oracle Apps DBA interview question)

To improve the oracle application patching mechanism in terms of performance and other criteria we can apply
patches as given below:

1)Merge Patches using admrgpch

admrgpch utility is used to merge two or more patches in oracle applications.
The advantage of merging patches is that it reduces downtime and thus very much useful.

Note 1: The syntax for using admrgpch utility is as given below:

admrgpch -s -d -merge_name

For eg:

admrgpch -s -d -merge_name

We have to make sure the the merge path log file "admrgpch.log" does not contain any error,once the two patches
are merged.

Note 2:'admrgpch' utility will not merge patches of different Oracle applications releases and operating systems platforms and with different parallel modes and we cannot use 'admrgpch' utility to merge AD and Non-AD patches.

2)To improve the performance while applying patch using 'adpatch'(auto patch utility) has lot of options like:
options=nocopyportion noautoconfig nogenerateportion hotpatch ... We can use as per our convenience.

$adpatch options=noautoconfig

3)If you cannot afford downtime than you can apply patch using options=hotpatch,very much useful for production environment here it is not required to keep the application in maintainance mode.

$adpatch options=hotpatch

11)How we register new products in Oracle applications?(oracle Apps DBA interview question)
Ans: 'adsplice' is an ad utility which is used to add off-cycle/new product to oracle apps.
i.e. products released between maintenance packs.

Download the product patch which contains information about the new product,'adsplice' control file and other associated files. Go through the readme and apply pre-reqs if any.Now for using 'adsplice' there is a control file newprods.txt. A typical newprods.txt looks like as given below:


If we are using OFA tablespace model then we need to change the last 4 parameters else for OATM(Oracle Application tablespace Model) it is optional.

We have to copy the new-product in $APPL_TOP/admin location and run 'adsplice' utility from 'applmgr' OS user and follow the prompts.Then we can verify that product is correctly spliced or not using adutconf.sql (AD Configuration report).
We need to logout and login again so that the new environment is set.
We can then verify that new _TOP environment variable is set and need to apply the patches for newly added product.

12)What is SHARED APPL_TOP?What are the major advantages and disadvantages of SHARED APPL_TOP?
(oracle Apps DBA interview question)

Ans: Basically Shared APPL_TOP's means sharing APPL_TOP and
COMMON_TOP across the Application Servers.


1)Administration tasks only need to be performed on any one node.
2)Application patches only need to be applied to any one node in the system.
3)Allows the use of "Distributed AD" to reduce downtime (check MetaLink 236469.1).
4)Reduces overall disk space requirements.
5)Makes it easier to add additional nodes to our Applications system.
6)Create single node clones from a shared APPL_TOP multi node system and
7)Merge APPL_TOP(Merge TOPs) from different application tiers into a single application tier APPL_TOP.


1)Single point of failure.
2)Performance might be an issue,if shared APPL_TOP's filesystem will be a 'nfs'(network file system) mount.

13)Explain the architecture of Oracle Golden Gate?(Oracle DBA interview question)
Ans: The Oracle GoldenGate software architecture is comprised of three primary components:
2)Trail Files and
As shown in the figure,
This modular approach allows each component to perform its tasks independently of the others,
accelerating data replication and ensuring data integrity.

Oracle GoldenGate’s Capture module resides on the source database and looks for new transactional activity.The Capture module reads the result of insert, update, and delete operations by directly accessing the database transaction (redo) logs, and then immediately captures new and changed data for distribution.
The Capture module only moves committed transactions—filtering out intermediate activities and rolled-back operations—which not only reduces infrastructure load but also eliminates potential data inconsistencies.Further optimization is achieved through transaction grouping and optional compression features.

2)Trail Files:
Oracle GoldenGate’s Trail Files contain the database operations for the changed data in a transportable,platform-independent data format.
Trail Files are a critical component within Oracle GoldenGate’s optimized queuing mechanism.
They reside on the source and/or target server but exist outside of the database to ensure heterogeneity,improved reliability, and minimal data loss.
This architecture minimizes impact to the source system because no additional tables or queries to the database are required to support the data capture process. The Capture module reads once, and then immediately moves the captured data to the external Trail File for delivery to the target(s).

In the event of an outage at the source and/or target, the Trail Files contain the most-recent
changed data up to the point of the outage, and the data is applied once the systems are back online again.

Oracle GoldenGate’s Delivery module takes the changed data from the latest Trail File and applies it to the target database using native SQL for the appropriate relational database management system.Delivery can be made to any open database connectivity–compliant database.
The Delivery module applies each transaction in the same order as it was committed and within the same transactional context as at the source, enabling consistency and referential integrity at the target.
To enhance IT flexibility, captured data can also be delivered to a Java Message Service destination or as a flat file using Oracle GoldenGate Application Adapters.

14)What is difference between Socket & Servlet Mode in Apps Forms?(oracle Apps DBA interview question)
Ans: When forms run SOCKET Mode these are dedicated connection between Client Machine & Form Server (Started by 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 we won't start form via

15)How to compile Forms in 11i and R12?(oracle Apps DBA interview question)

Ans: We can Compile Forms in 11i using 'f60gen' utility as follows:

$f60gen module=.fmb userid=apps/ output_file=$XX_TOP/forms/US/.fmx

Where as,In R12 we can Compile Forms using 'frmcmp_batch' utility as follows:

$frmcmp_batch .fmb userid=apps/ output_file=$XX_TOP/12.0.0/form/US/.fmx module_type=form compile_all=special

16)If a patch fail how to proceed in Oracle Application?
Ans: I like the below link for this:

17)Describe the steps involved in the upgradation from Oracle 11i to R12 Appplications?
(oracle Apps DBA interview question)

Ans: Atul again described these upgradation steps beautifully in the below mentioned link:

We usually prepare the below documents for doing upgrade of Oracle Applications:
1)xls document for preparing a plan and to record the steps of upgrade.
2)Word document for the actual implemented steps in details the steps.

Best regards,



  1. There are many more benefits of pursuing ITIL Intermediate PPO in USA It draws out the hidden skills and increase your market value that can be much beneficial for the future.

  2. I have read your blog its very attractive and impressive. I like it your blog.

    Oracle SOA Online Training