Sunday, August 06, 2017

What is Edition Based Redefinition (EBR) in Oracle?

Edition Based Redefinition (EBR) is a new feature introduced from Oracle Software Version 11g R2.Edition-based redefinition allows multiple versions of PL/SQL objects, views and synonyms in a single schema.This feature facilitates Online patching of database objects in R12.2 online Patching.

Editionable and Non-editionable Objects

Below schema object types are editionable in the database:

SQL translation profile

All PL/SQL object types:


If a schema object type is editionable in the database, then it can be editionable in schemas.All other schema object types are noneditionable in the database and in every schema, and objects of that type are always noneditioned. Tables are always noneditioned objects.

To view the editions related information in the database, use the below datadictionary tables.


DBA_OBJECTS_AE: DBA_OBJECTS_AE view shows all objects, of all editions.

What is the use of MMON background process in Oracle Database?

MMON (Manageability Monitor)

MMON (Manageability Monitor) is a background process introduced in Oracle 10g. It gathers memory statistics (snapshots) and stores this information in the AWR (automatic workload repository). MMON is also responsible for issuing alerts for metrics that exceed their thresholds.

How to Compile Forms in Oracle Applications Release 11i and Release 12?

Steps to compile forms  in  Oracle Applications Release 12.1.x and 12.2.x

Login to Application Tier node as applmgr user

Change to $AU_TOP/forms/US

Execute the Below command to generate form files.

frmcmp_batch userid=apps/<password> module=formname.fmb
output_file=$PROD_TOP/forms/US/<name>.fmx module_type=form batch=no compile_all=special

After execution of the above command, verify there are no compilation errors in output displayed and the fmx is created succesfully.

Steps to compile forms  in  Oracle Applications Release 11i

Login to Application Tier node as applmgr user

Change to $AU_TOP/forms/US

Execute the Below command to generate form files.

f60gen module=$AU_TOP/forms/US/<custom_form>.fmb  userid=apps/<passwd> output_file=$PRODUCT_TOP/forms/<customformname>.fmx module_type=form compile_all=yes


Use command "f60gen help=y" or " help=y" to find the optional parameters.

Using Compile_all= yes changes cached version in the source file in addition to compiling pl/sql in the utput fmx file.

Tuesday, July 25, 2017

Oracle Applications DBA Utilities :FNDLOAD

What are the objects loaded using FNDLOAD in Oracle Applications?

FNDLOAD (Generic Loader) is a concurrent Program which is used to move metadata between database and text file representations.The loader reads a configuration file to determine what data to access.

FNDLOAD can download data from an application entity into a portable, editable text file. This file can then be uploaded into any other database to copy the data. Conversion between database store and file format is specified by a configuration file that is read by the loader. FNDLOAD downloads data from a database according to a configuration (.lct) file, and converts the data into a data file (.ldt file). FNDLOAD can then upload this data to another database using a configuration file.
The loader operates in one of two modes: download or upload. In the download mode, data is downloaded from the database to a text file; in the upload mode, data is uploaded from a text file to the database.

Syntax to upload/download concurrent Programs using FNDLOAD:

To Download:


To Upload:

FNDLOAD apps/$apps_password 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct PROGNAME.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Below are the application entities which can be uploaded/downloaded using FNDLOAD.

FND Messages
EBS Front End Users
Concurrent Programs
Profile Options
Request Groups 

Oracle Recovery Manager Features: BACKUP AS COPY

What is the advantage backup as copy feature in RMAN?

RMAN(Recovery Manager) utility creates backup in the form of backupsets or backup pieces by default.RMAN utility also allows other method which is similar to user managed hot backup. It can be acheived using 'BACKUP AS COPY' command.Using "Backup as Copy" RMAN creates backup in the form of image copies. However, we do not need to put the database in begin backup/end backup mode as in Conventional Hot Backup.

Syntax for Datafile Backup using Backup as copy:

RMAN> backup as copy datafile 1 format '/backup/data/datafile01.dbf' ;

Another advantage of this feature is that it allows to create image copy of control file.

 RMAN> backup as copy current controlfile format 'F:\CntrlFile_Bkp.ctl';  

How do we identify backups taken  as Image copies using RMAN?

Use "LIST COPY" Command. "LIST BACKUP" only gives a list of backup sets which were created using default RMAN backup Methos.



Saturday, July 08, 2017

ADOP Options: cleanup_mode

ADOP cleanup_mode

Run with adop cleanup phase.

Provides control over the extent of cleanup operations.
If no value is specified, cleanup is performed in standard mode, which does the same as quick mode but also drops obsolete code objects.

Values: quick/full


cleanup_mode=quick performs the minimum necessary cleanup to complete the online patching cycle. Use this mode if you want to start a new online patching cycle as soon as possible.

cleanup_mode=full performs all possible cleanup tasks for maximum recovery of space from unused database editions, objects and columns. Full cleanup is required after aborting an online patching cycle.


adop phase=cleanup cleanup_mode=full
adop phase=cleanup cleanup_mode=quick

ADOP Options: actualize_all

Whenever adop prepare phase is initiated, a new patch edition is created in the database.During Online patching (ADOP) : An additional column ZD_EDITION_NAME is populated in the seed tables.As we do more online patching cycles, the number of entries for database editions will increase. This affects system performance and also increases cleanup time.When the number of old database editions reaches 25 or more, you should consider dropping all old database editions by running the adop actualize_all phase and then performing a full cleanup.

To drop older database editions, follow the below steps:

$ adop phase=prepare
$ adop phase=actualize_all
$ adop phase=finalize finalize_mode=full
$ adop phase=cutover
$ adop phase=cleanup cleanup_mode=full

How to Restore context file in oracle applications?

Scenario: Your Applications context file is either accidentally deleted or Corrupted. You do not have any latest backup of context file available.

Below are the steps to recreate context file 

The Applications context file can be retrieved by running the script.

perl /clone/bin/ retrieve

Above script prompts for Database connection Info, apps username/password , Available Context files and the location to which context files can be restored.When prompted for the context file to be retrieved, select the option of retrieving the Applications tier context file that has been lost and retrieve it to the default location as in $CONTEXT_FILE parameter.

The above command can be used only when INST_TOP the is still intact. Incase if INST_TOP is also lost, the Applications tier context file may be retrieved as follows:

Execute the following command on the Database tier:
perl /appsutil/clone/bin/ retrieve

When prompted for the context file to be retrieved, select the option of retrieving the Applications tier context file that has been lost.Choose the location to any writeable location and copy the restored context file to the value specified in $CONTEXT_FILE parameter on Application Tier.

To retrieve the Database tier context file,

Execute the following command on the Database tier:
perl /appsutil/clone/bin/ retrieve

When prompted for the context file to be retrieved, select the Database tier context file and retrieve it to the default location ($CONTEXT_FILE on DB node).

Oracle Applications R12.2.5 New Features: dualfs in cloning

From Oracle Applications Release 12.2.5 Version, we have dualfs option for cloning applications tier.We can use this feature in earlier 12.2.x versions also, if AD-TXK Delta 7 patch is applied.Before AD-TXK Delta 7, it was required to run adcfgclone twice on run and patch filesystems.Using dualfs option,we can create both run and patch fs at one time.

-bash$ perl appsTier dualfs

                     Copyright (c) 2002, 2015 Oracle Corporation
                        Redwood Shores, California, USA

                        Oracle E-Business Suite Rapid Clone

                                 Version 12.2

                      adcfgclone Version 120.63.12020000.56

Enter the APPS password :

Enter the Weblogic AdminServer password :

Do you want to add a node (yes/no) [no] :

Running: Context clone...

Log file located at /u01/VISN/fs1/EBSapps/comn/clone/bin/CloneContext_1023194859.log

Provide the values required for creation of the new APPL_TOP Context file.

Target System Hostname (virtual or normal) [appsmt] :

Target System Database SID : VISN

Target System Database Server Node [r12erpt] : appsdb

Target System Database Domain Name [] :

Target System Base Directory : /u01/VISN

Target System Base Directory set to /u01/VISN

Target System Current File System Base set to /u01/VISN/fs1

Target System Other File System Base set to /u01/VISN/fs2

Target System Fusion Middleware Home set to /u01/VISN/fs1/FMW_Home
Target System Other File System Fusion Middleware Home set to /u01/VISN/fs2/FMW_Home

Target System Web Oracle Home set to /u01/VISN/fs1/FMW_Home/webtier
Target System Other File System Web Oracle Home set to /u01/VISN/fs2/FMW_Home/webtier

Target System Appl TOP set to /u01/VISN/fs1/EBSapps/appl
Target System Other File System Appl TOP set to /u01/VISN/fs2/EBSapps/appl

Target System COMMON TOP set to /u01/VISN/fs1/EBSapps/comn
Target System Other File System COMMON TOP set to /u01/VISN/fs2/EBSapps/comn

Target System Instance Home Directory [V] : /u01/VISN

Target System Current File System Instance Top set to /u01/VISN/fs1/inst/apps/VISN_appsmt

Do you want to preserve the Display [appsmt:0.0] (y/n)  : n

Target System Display [r12erpt:0.0] : appsmt:3.0

Target System Root Service [enabled] :

Target System Web Entry Point Services [enabled] :

Target System Web Application Services [enabled] :

Target System Batch Processing Services [enabled] :

Target System Other Services [disabled] :

Do you want the target system to have the same port values as the source system (y/n) [y] ? : n

Target System Port Pool [0-99] : 88

Checking the port pool 88
done: Port Pool 88 is free
Report file located at /u01/VISN/fs1/inst/apps/VISN_appsmt/admin/out/portpool.lst

UTL_FILE_DIR on database tier consists of the following directories.

1. /usr/tmp
2. /usr/tmp
4. /usr/tmp
Choose a value which will be set as APPLPTMP value on the target node [1] : 3
The new APPL_TOP context file has been created :
Check Clone Context logfile /u01/VISN/fs1/EBSapps/comn/clone/bin/CloneContext_1023194859.log for details.

Creating Patch file system context file.....

Log file located at /u01/VISN/fs1/EBSapps/comn/clone/bin/CloneContextPatch_1023194973.log

Target System Other File System Instance Top set to /u01/VISN/fs2/inst/apps/VISN_appsmt

Target System Port Pool [0-99] : 89

Checking the port pool 89
done: Port Pool 89 is free
Report file located at /u01/VISN/fs2/inst/apps/VISN_appsmt/admin/out/portpool.lst
The new APPL_TOP context file has been created :
Check Clone Context logfile /u01/VISN/fs1/EBSapps/comn/clone/bin/CloneContextPatch_********.log for details.

FMW Pre-requisite check log file location : /u01/VISN/fs1/EBSapps/comn/clone/FMW/logs/prereqcheck.log
Running: FMW pre-req check...

Configuring: Run file system....
LogFile located at /u01/VISN/fs1/inst/apps/VISN_appsmt/admin/log/clone/run/RCloneApplyAppstier_*******.log

What are the differences between Oracle Applications R12.1.x and R12.2.x

From DBA perspective, below are the changes in R12.2.x version of Oracle Applications, compared to R12.1.3.

1. Dual FS: Oracle Application Release 12.2 has dual filesystems(RUN FS and PATCH FS) to facilitate Online Patching.This Requires double the mountpoint space at MiddleTier Level.

2. Weblogic Server: Oracle HTTP Server in R12.1 is replaced by Oracle Weblogic Server.
10.1.3 Home is replaced by FMW_HOME.Weblogic Server Manages all oacore, forms and oafm servers in R12.2.

3. Online Patching : Dual Filesystem and EBR feature in 11gR2 Database facilitate online patching. Online Patching minimizes the downtime required for patching.adpatch utility in R12.1.x is replaced by adop utility. adop cycle runs in 5 Phases : prepare,apply,finalize,cutover and cleanup. adop internally calls adpatch utility to run the driver files associated with adop phases.

4. APPS Password Change: After Changing Apps Password in R12.2, we need to update the new password in EBS Datasource used by Weblogic Domain.To know more about apps password change in Oracle applications R12.2, check the below link.

5. Cloning : While Cloning of R12.2.x adcfgclone instance needs to be run twice,  on RUN fs and PATCH fs.From 12.2.5 Version (OR R12.AD-TXK Delta 7), we have the dualfs option which creates both run fs and patch fs during clone process.

Thursday, July 06, 2017

How to Configure RMAN Backups into Multiple Locations?

If we need  RMAN to distribute backup sets among multiple disks on different mountpoints. We can use the below syntax to configure backup in multiple locations.

Using Allocate Channel:


Using Configure Channel:


Tuesday, June 27, 2017

Oracle RMAN (Recovery Manager) Concepts

What is the difference between obsolete and expired Backup in RMAN?

Obsolete Backup: Backup pieces or backup sets which are not needed for recovery.
We can use the REPORT OBSOLETE command to list the obsolete backups and DELETE OBSOLETE command to delete the obsolete backup.

Expired Backup:When the CROSSCHECK command is used to determine whether backups recorded in the repository still exist on disk or tape, if RMAN cannot locate the backups, then it updates their records in the RMAN repository to EXPIRED status.We can then use the DELETE EXPIRED command to remove records of expired backups from the RMAN repository.

Oracle DBA Concepts: Background Processes in Oracle ASM (Automatic storage Management)

The following background processes are an integral part of Automatic Storage Management:

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

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

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

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.

Sunday, June 25, 2017

How to change Rebalance Power in ASM Instances?

Whenever a new diskgroup is added or dropped, ASM automatically performs REBALANCING operation.Power used by rebalacing defaults to value specified for asm_power_limit initialization parameter.We can check the status of rebalancing from v$asm_operation view.

Increasing the value of ASM_POWER_LIMIT, reduces the estimated time for completion of Rebalance Operation.

ASM power limit can be increased using the below command

alter system set asm_power_limit =11;

To know more about values for this parameter, check this link:

But this does not increase the rebalancing power of current operation. 

To increase the rebalancing power of ongoing operation, use the below command

 alter diskgroup dg1 rebalance power 11;

We can also specify rebalance power at the time of adding or dropping disks.

alter diskgroup dg1 add disk d01 rebalance power 11;

When to use asm_power_limit 0?

Whenever there is a need to add/drop several disks (like migrations), setting the power limit to 0 is highly beneficial.We can avoid the waiting time for rebalance operation to complete,before the next disk is rebalanced.In such cases, set the asm_power_limit to 0, add/drop all the disks and set the value of asm_power_limit to a non zero value.
Rebalance operation will be parallelized and completes faster.

Oracle Database ASM Features: REBALANCING

ASM has the ability to rebalance data across the disks whenever a disk is added,dropped or replaced.
The performance of rebalance operation is controlled by initialization parameter ASM_POWER_LIMIT.


Default value 1

Range of Values 0 to 11 (Prior to 11gR2)

Range of Values 0 to 1024(From 11gR2)

For 10g Databases:

SQL> alter diskgroup DG1 rebalance power 12;

alter diskgroup DG1 rebalance power 12
ERROR at line 1:
ORA-15102: invalid POWER expression

SQL> alter diskgroup DG1 rebalance power 11;

Diskgroup altered.

For 11g AND 12c Databases

SQL> alter diskgroup DG1 rebalance power 1025;
alter diskgroup DG1 rebalance power 1025
ERROR at line 1:
ORA-15102: invalid POWER expression

SQL> alter diskgroup DG1 rebalance power 1024;

Diskgroup altered.

The higher the value of rebalancing power, faster would be rebalancing operation.

ADOP Useful Options :skipsyncerror

There are Various options available with Oracle Applications Online Patching Utility (ADOP). Here , we will see the benefits of skipsyncerror option.


Your Previous  adop session failed while applying patches and you did not find any solution to fix the errors, Oracle Support Provided a new patch to fix the issue. You will get errors while synchronization and need to ignore those errors.

Usage: adop phase=prepare skipsyncerror=yes

skipsyncerror Details

This option is used along with prepare phase.


This feature enables the user to specify that any synchronization errors in the prepare phase are expected to be fixed automatically in the synchronization that takes place with subsequent patches.

Values: yes/no

Default value is 'no'. Set the value to 'yes' in order to work around synchronization failures that may occur when patches that failed to apply correctly in a previous patching cycle are synchronized during the prepare phase.

Tuesday, June 06, 2017

Initialization Parameters Required for Oracle ASM (Automatic Storage Management)

The below parameters are required for ASM instance.


INSTANCE_TYPE specifies whether the instance is a database instance or an Automatic Storage Management instance.



The instance is a database instance.


The instance is an Automatic Storage Management instance.


DB_UNIQUE_NAME specifies a globally unique name for the database

Default value
Database instances: the value of DB_NAME
Automatic Storage Management instances: +ASM


ASM_POWER_LIMIT specifies the maximum power on an Automatic Storage Management instance for disk rebalancing. The higher the limit, the faster rebalancing will complete. Lower values will take longer, but consume fewer processing and I/O resources.

Default value 1

Range of Values 0 to 11 (Prior to 11gR2)

Range of Values 0 to 1024(From 11gR2)


ASM_DISKGROUPS specifies a list of names of disk groups to be mounted by an Automatic Storage Management instance at instance startup or when an ALTER DISKGROUP ALL MOUNT statement is issued.

Automatic Storage Management (ASM) automatically adds a disk group to this parameter when the disk group is successfully created or mounted, and automatically removes a disk group from this parameter when the disk group is dropped or dismounted.

Issuing the ALTER DISKGROUP...ALL MOUNT or ALTER DISKGROUP...ALL DISMOUNT command does not affect the value of this parameter.


ASM_DISKSTRING specifies an operating system-dependent value used by Automatic Storage Management to limit the set of disks considered for discovery. When a new disk is added to a disk group, each Automatic Storage Management instance that has the disk group mounted must be able to discover the new disk using the value of ASM_DISKSTRING.


ASM_PREFERRED_READ_FAILURE_GROUPS specifies the failure groups that contain preferred read disks. Preferred disks are instance specific. This parameter is only valid in ASM instances.

What are Nodeapps Services in Oracle RAC

Nodeapps are standard set of oracle application services which are started automatically for RAC.

Node apps Include:

1) VIP.
2) Oracle Net listener.
3) Global Service Daemon.
4) Oracle Notification Service.

Nodeapp Services run on each node of the cluster and will switched over to other nodes through VIP during the failover.

Useful commands to maintain nodeapps services:

srvctl stop nodeapps -n NODE1 
srvctl stop nodeapps -n NODE2            

srvctl start nodeapps -n NODE1              
srvctl start nodeapps -n NODE2             

srvctl status nodeapps                      
[Check the status of services on all nodes]

Tuesday, May 23, 2017

SQL Profile and SQL Plan Baseline

What is SQL Profile?

A SQL profile contains corrections for poor optimizer estimates discovered by the SQL Tuning advisor.A  SQL profile is to a SQL statement what statistics are to a table or index.
Implementing sql profiles allows us to replace bad sql execution plan with good execution plan.

What is SQL Plan Baseline?

A SQL plan baseline for a SQL statement consists of a set of accepted plans. When the statement is parsed, the optimizer will only select the best plan from among this set. If a different plan is found using the normal cost-based selection process, the optimizer will add it to the plan history but this plan will not be used until it is verified to perform better than the existing accepted plan and is evolved.

Friday, May 05, 2017

Oracle Database Administrator Interview Questions on RAC

Oracle RAC (Voting Disk ) FAQ's

1.What is Voting Disk?

Voting Disk is a file that maintains node membership details.
All members in the Cluster read and write Heartbeat Information in Voting Disk.

2. What Information is stored in Voting Disk?

Voting disks contain static and dynamic data.
Static data : Info about nodes in the cluster
Dynamic data : Disk heartbeat logging
It maintains and consists of important details about the cluster nodes membership, such as
- which node is part of the cluster,
- which node is joining the cluster, and
- which node is leaving the cluster.

3. Why do we need Voting Disk?

CSSD processes (Cluster Services Synchronization Daemon) monitor the health of  RAC nodes employing two distinct heart 

beats: Network heart beat and Disk heart beat. Healthy nodes will have continuous network and disk heartbeats exchanged 

between the  nodes. Break in heart beat indicates a possible error scenario. There are few different scenarios possible 

with missing heart beats:
1. Network heart beat is successful, but disk heart beat is missed.
2. Disk heart beat is successful, but network heart beat is missed.
3. Both heart beats failed.
In addition, with numerous nodes, there are other possible scenarios too. Few possible scenarios:
1. Nodes have split in to N sets of nodes, communicating within the set, but not with members in other set.
2. Just one node is unhealthy.
Nodes with quorum will maintain active membership of the cluster and other node(s) will be fenced/rebooted.

4.Why do we have odd number of voting disks?

Odd number of Voting disks are required to prevent Split Brain Syndrome.
A node must be able to access more than half of the voting disks in order to decide which node can be evicted incase of 


5. How to backup Voting Disk?

Prior to Oracle 11g R2 version, Voting Disk is backed up using dd command

From 11g R2, no need to manually backup voting disk.
It is automatically backup along with OCR  whenever there is a configuration change.

In 11g R2, Restoring Voting disk from a manually copied backupfile may prevent cluster services from starting up.

Oracle Database Administrator Interview Questions on RMAN


1.What is the difference between recovery catalog and Nocatalog backup in RMAN?

NoCatalog Backup:

ByDefault RMAN connects to the target database in Nocatalog Mode.
In Nocatalog Mode, Backup Information and Metadata related to RMAN is stored in target database controlfile.

Catalog Backup:

A recovery catalog is a schema created in a separate database that contains metadata obtained from the target control file.
In recovery catalog we can store rman scripts.

We can store metadata about multiple incarnations of a single target database in the catalog.
Recovery catalog is central and can have information of many databases.
If the control file is lost and must be restored from backup, the backup configuration information is available when the database is not mounted.

2.What are the differences between crosscheck and validate commands?

Validate command is to examine a backup set and report whether it can be restored. RMAN scans all of the backup pieces in the specified backup sets and looks at the checksum to verify that the contents are intact so that backup can be successfully restored if necessary.

Crosscheck command is to verify the status of backups and copies recorded in the RMAN repository against media such as disk or tape. The crosscheck command only processes files created on the same device type as the channel running crosscheck.

3.What is obsolete backup & expired backup?

A status of “expired” means that the backup piece or backup set is not found in the backup destination.

A status of “obsolete” means the backup piece is still available, but it is no longer needed. The backup piece is no longer needed since RMAN has been configured to no longer need this piece after so many days have elapsed, or so many backups have been performed.

4.What is the difference between hot backup & RMAN backup?

We need to put the database in Backup mode for Hot  Backup. RMAN Backup does not need the database to be in Backup mode.

5. Which Tables are have information required for RMAN list & report commands ?
 V$BACKUP_FILES and recovery catalog views e.g.,  RC_DATAFILE_COPY or RC_ARCHIVED_LOG.

Below tables contain RMAN Catalog information: