step by step cascaded dataguard implementation with E-Business suite

In the following steps we are going to implement Cascaded Dataguard on Database 11g R2 and E-Business Suite R12.1.3 with the following configurations:

1- Database Name is DBA
2- Primary Server Name: DBA
3- Secondary Server Name: clndb
4- Cascaded Server Name: uat
5- Primary Database Uniuque Name: DBA
6- Secondary Database Uniuque Name: DBAS
7- Cascaded Database Uniuque Name: DBASC

Here are the steps:

1- (On Primary Database) ALTER DATABASE FORCE LOGGING;
2- (On Primary Database) create the file <TNS_ADMIN>/<CONTEXT_NAME>_ifile.ora file
e.g. :/u02/oracle/product/11.2.0/network/admin/DBA_dbadb/DBA_dbadb_ifile.ora
with the following entries

##################################################################
#
# Created to define net services to support a Oracle Data Guard physical
# standby environment.
#
##################################################################

#
# The Oracle Data Guard physical standby of primary runs on clndb.
#Oracle Data Guard uses the tcp protocol only.
#
#
 # # MUST BE CHANGED on switchover:
DBAS=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=clndb.kpc.com.kw)(PORT=1521))
            (CONNECT_DATA=
                (SERVICE_NAME=DBA)
            )
        )
DBASC=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=uat.kpc.com.kw)(PORT=1521))
            (CONNECT_DATA=
                (SERVICE_NAME=DBA)
            )
        )


# The Oracle Data Guard physical standby of primary runs on clndb.
#Oracle Data Guard uses the tcp protocol only.

#
#
# Fetch Archive Log (FAL) service definition.
# This entry can be set up for use when THIS server hosts a
# standby database (thus will not need to be changed on switchover),
# and should point to what would be the PRIMARY AT THAT TIME -
# i.e. this is the fal_server alias used to communicate from the standby to primary.

DBA=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=dbadb.kpc.com.kw)(PORT=1521))
            (CONNECT_DATA=
                (SERVICE_NAME=DBA)
            )
        )

3- (On Primary Database) Standby Listener to be used when the server is running as standby
Create the file LISTENER_STDBY.ora in /u02/oracle/product/11.2.0/network/admin/DBA_dbadb
#Standby LISTENER.ORA file when server is running as standby
DBAS =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = clndb.kpc.com.kw)(PORT = 1521))
    )
  )

SID_LIST_DBAS =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DBA)
      (ORACLE_HOME =/u02/oracle/product/11.2.0)
      (SID_NAME = DBA)
    )
  )
 
STARTUP_WAIT_TIME_DBAS = 0
CONNECT_TIMEOUT_DBAS = 10
TRACE_LEVEL_DBAS = OFF

LOG_DIRECTORY_DBAS = /u02/oracle/product/11.2.0/network/admin
LOG_FILE_DBAS = STDBY
TRACE_DIRECTORY_DBAS = /u02/oracle/product/11.2.0/network/admin
TRACE_FILE_DBAS = STDBY
ADMIN RESTRICTIONS_DBAS = OFF

4- (On Primary Database) recreate the password file
orapwd file=orapwDBA password=sys entries=5 ignorecase=y
5- (On Primary Database) add the following parameters in /u02/oracle/product/11.2.0/dbs/DBA_dbadb_ifile.ora then bounce the database

##################################################################
#
# Created to define parameters to support a Oracle Data Guard physical
# standby environment.
#
##################################################################

#
# The Oracle Data Guard physical standby of primary runs on clndb.
#

db_unique_name = DBA
log_archive_dest_1 = 'LOCATION=/u02/oracle/dbadata/archive'
log_archive_dest_2 = 'SERVICE=DBAS AFFIRM LGWR SYNC VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=DBAS NET_TIMEOUT=120'
log_archive_config='dg_config=(DBA,DBAS)'
# log_archive_dest_state_2 = defer
log_archive_dest_state_2 = enable

log_archive_min_succeed_dest = 1

standby_file_management = AUTO

Remote_login_passwordfile = exclusive


# db_file_name_convert: do not need; same directory structure
# log_file_name_convert: do not need; same directory structure
fal_server = DBA, DBAS
fal_client= DBA
log_file_name_convert = xx,xx # Specify dummy values to trigger log clearing

6- (On Primary Database) Add standby redo logs
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oracle/dbadata/stdby_log04a.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oracle/dbadata/stdby_log04b.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oracle/dbadata/stdby_log03b.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oracle/dbadata/stdby_log03a.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oracle/dbadata/stdby_log02a.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oracle/dbadata/stdby_log02b.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oracle/dbadata/stdby_log01b.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oracle/dbadata/stdby_log01a.log') SIZE 100M;

7- (On Primary Database) make this step for clndb,uat
1.From a client, connect to OAM to register the standby database server as a node. Navigate as follows:
Site Map > Administration > System Configuration > Hosts > Register (button under Other Hosts)
2.Next, use OAM to add this host to the list of hosts that need access to the database:
Applications Dashboard > Security > Manage Security Options > Enable Restricted Access > Run Wizard
3.Select the host you just added, and click 'Continue'.
4.If the displayed list is correct and includes your new host, click 'Submit'.
5.Run AutoConfig on Database Tier.
    6.Bounce the TNS Listener.
Note: this steps reflecting in sqlnet.ora and any other node than clndb,uat will not be able to connect, you have to comment tcp.validnode_checking = yes
8- (On Primary Database) Run the application tier and database tier pre-clone scripts
9- (On Primary Database) Copy the following to the Secondary Node:
1- APPL_TOP
2- COMMON_TOP
3- OracleAS Tools ORACLE_HOME
4- OracleAS Web IAS_ORACLE_HOME
rsync -avz /u02/oracle/apps applcln@clndb:/u02/apps
10- (On Primary Database) Copy ORACLE_HOME to the secondary Database and the cascaded database
scp -r /u02/oracle/product/11.2.0 oracln@clndb:/u02/oracle/product/11.2.0
11- (On Secondary Database) modify environment varibales to point the copied oracle home and set TNS_ADMIN
ORACLE_BASE=/u02/oracle/product
ORACLE_HOME=/u02/oracle/product/11.2.0
ORACLE_SID=DBA
PATH=$PATH:$ORACLE_HOME/bin
LD_LIBRARY_PATH=$ORACLE_HOME/lib
TNS_ADMIN=/u02/oracle/product/11.2.0/network/admin/DBA_dbadb

export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH TNS_ADMIN

12- (On Secondary Database) change the following in the init.ora:
db_unique_name = DBAS
log_archive_dest_1 = 'LOCATION=/u02/oracle/dbadata/archive'
log_archive_dest_2 = 'SERVICE=DBA  AFFIRM LGWR SYNC VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBA  NET_TIMEOUT=120'
log_archive_dest_3 = 'SERVICE=DBASC LGWR ASYNC=20480 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=DBASC OPTIONAL REOPEN=15 NET_TIMEOUT=30'
log_archive_config='dg_config=(DBA,DBAS,DBASC)'

13- (On Secondary Database)startup nomount pfile= <pfile created in above step >
14- (On Secondary Database) mv listener.or listener_primary.ora and use listener_stdby.ora instead (rename it listener.ora)
15- (On Secondary Database)rman target sys/sys@dba auxiliary sys/sys@dbas
RMAN >DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
DORECOVER
SPFILE SET "db_unique_name"="DBAs"
SET LOG_ARCHIVE_DEST_2="SERVICE=DBAS AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBAS"
SET FAL_SERVER="DBA" COMMENT "Is primary"
SET DIAGNOSTIC_DEST="/u02/oracle/product/11.2.0/admin/DBA_clndb" COMMENT "DIAGNOSTIC Destination on standby server"
SET UTL_FILE_DIR= "/usr/tmp"SET DIAGNOSTIC_DEST="/u02/oracle/product/11.2.0/admin/DBA_clndb" COMMENT "DIAGNOSTIC Destination on standby server"
NOFILENAMECHECK;
16- (On Secondary Database) shutdown the database and the listener
17- (On Secondary Database)
1- set the following variables
export ORACLE_BASE=/u02/oracle/product
export ORACLE_HOME=/u02/oracle/product/11.2.0
export ORACLE_SID=DBA
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/ctx/lib
export PATH=$PATH:$ORACLE_HOME/bin:/usr/ccs/bin:/usr/sbinexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/ctx/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib/export PATH=$PATH:$ORACLE_HOME/bin:/usr/ccs/bin:/usr/sbinexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/ctx/lib

2- execute perl adcfgclone.pl dbTechStack from $ORACLE_HOME/appsutil/clone/bin
18- (On Secondary Database) source environment variables then stop the database and the listener
19- (On Primary Database)
scp /u02/oracle/product/11.2.0/network/admin/DBA_dbadb/DBA_dbadb_ifile.ora oracln@clndb:/u02/oracle/product/11.2.0/network/admin/DBA_clndb/DBA_clndb_ifile.ora
20- (On Secondary Database)
1- mv DBA_dbadb_ifile.ora DBA_clndb_ifile.ora
2- start the listener
3- startup nomount;
4- alter database mount standby database;
5- alter database recover managed standby database disconnect from session;
21- (On Secondary Application)
1- From /u02/clnapp/apps/apps_st/comn/clone/bin execute perl adcfgclone.pl atTechStack
2- From /u02/clnapp/apps/apps_st/appl/ad/12.0.0/bin execute  perl adconfig.pl contextfile=/u02/clnapp/inst/apps/DBA_clndb/appl/admin/DBA_clndb.xml run=INSTE8


22- (On Primary Database) Copy ORACLE_HOME to the Cascaded Database
scp -r /u02/oracle/product/11.2.0 orauat@uat:/u02/oracle/product/11.2.0
23- (On Cascaded Database) modify environment varibales to point the copied oracle home and set TNS_ADMIN
ORACLE_BASE=/u02/oracle/product
ORACLE_HOME=/u02/oracle/product/11.2.0
ORACLE_SID=DBA
PATH=$PATH:$ORACLE_HOME/bin
LD_LIBRARY_PATH=$ORACLE_HOME/lib
TNS_ADMIN=/u02/oracle/product/11.2.0/network/admin/DBA_dbadb

export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH TNS_ADMIN
24- (On Cascaded Database)
mv $ORACLE_HOME/rdbms/lib/config.o $ORACLE_HOME/rdbms/lib/config.o.orig
cd /u02/app/oracle/app/product/11.2.0.1.0/db/rdbms/lib
correct the os group in the file config.s
/usr/ccs/bin/make  -f ins_rdbms.mk ioracle

24- (On Cascaded Database) change the following in the init.ora (DBA_dbadb_ifile.ora):
db_unique_name = DBASC
log_archive_dest_1 = 'LOCATION=/u02/oracle/dbadata/archive'
log_archive_dest_2 = 'SERVICE=DBAS LGWR ASYNC=20480 VALID_FOR=(ALL_LOGFILES, ALL_ROLES) db_unique_name=DBAS OPTIONAL REOPEN=15 NET_TIMEOUT=30'
log_archive_config='dg_config=(DBASC,DBAS)'

25- (On Cascaded Database)startup nomount pfile= <pfile created in above step >
26- (On Cascaded Database) mv listener.ora listener_primary.ora and use listener_cascaded.ora instead (rename it listener.ora) then start the listener.ora
27  (On Cascaded Database) mkdir -p /u02/oracle/product/11.2.0/admin/DBA_uat
28- (On Cascaded Database)rman target sys/sys@dba auxiliary sys/sys@dbasc
RMAN >DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
DORECOVER
SPFILE SET "db_unique_name"="DBASC"
SET LOG_ARCHIVE_DEST_2="SERVICE=DBASC AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBASC"
SET FAL_SERVER="DBA" COMMENT "Is primary"
SET DIAGNOSTIC_DEST="/u02/oracle/product/11.2.0/admin/DBA_uat" COMMENT "DIAGNOSTIC Destination on standby server"
SET UTL_FILE_DIR= "/usr/tmp"SET DIAGNOSTIC_DEST="/u02/oracle/product/11.2.0/admin/DBA_uat" COMMENT "DIAGNOSTIC Destination on standby server"
NOFILENAMECHECK;
29- (On Cascaded Database) shutdown the database and the listener
30- (On Cascaded Database)
1- set the following variables
export ORACLE_BASE=/u02/oracle/product
export ORACLE_HOME=/u02/oracle/product/11.2.0
export ORACLE_SID=DBA
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/ctx/lib
export PATH=$PATH:$ORACLE_HOME/bin:/usr/ccs/bin:/usr/sbinexport
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib/export


2- execute perl adcfgclone.pl dbTechStack from $ORACLE_HOME/appsutil/clone/bin
31- (On Cascaded Database) source environment variables then stop the listener
32- (On Primary Database)
scp /u02/oracle/product/11.2.0/network/admin/DBA_dbadb/DBA_dbadb_ifile.ora orauat@uat:/u02/oracle/product/11.2.0/network/admin/DBA_uat/DBA_uat_ifile.ora
33- (On Cascaded Database)
1- mv DBA_dbadb_ifile.ora DBA_uat_ifile.ora
1- start the listener
2- startup nomount;
3- alter database mount standby database;
4- alter database recover managed standby database disconnect from session;
34- (On Primary Database) Copy the following to the Secondary Node:
1- APPL_TOP
2- COMMON_TOP
3- OracleAS Tools ORACLE_HOME
4- OracleAS Web IAS_ORACLE_HOME
/opt/sfw/bin/rsync -avz --rsync-path=/opt/sfw/bin/rsync /u02/oracle/apps appluat@uat:/u02/oracle/apps
/opt/sfw/bin/rsync -avz /u02/oracle/apps appluat@uat:/u02/clnapp
scp -r /u02/oracle/apps applcln@clndb:/u02/clnapp
35- (On Cascaded Application)
1- From /u02/clnapp/apps/apps_st/comn/clone/bin execute perl adcfgclone.pl atTechStack
2- From /u02/clnapp/apps/apps_st/appl/ad/12.0.0/bin execute  perl adconfig.pl contextfile=/u02/clnapp/inst/apps/DBA_clndb/appl/admin/DBA_clndb.xml run=INSTE8

Data Guard 11g Protection modes

Maximum Protection
This mode offers the highest level of data protection. Data is synchronously transmitted to the standby database from the primary database, and transactions are not committed on the primary database unless the redo data is available on at least one standby database configured in this mode. If the last standby database configured in this mode becomes unavailable, processing stops on the primary database. This mode guarantees no data loss.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;

•Maximum Availability
This mode is similar to the maximum protection mode, including no data loss. However, if a standby database becomes unavailable (for example, due to network connectivity problems), processing continues on the primary database. When the fault is corrected, the standby database is resynchronized with the primary database. If there is a need to fail over before the standby database is resynchronized, some data may be lost.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

•Maximum Performance
This mode offers slightly less data protection on the primary database, but higher performance than maximum availability mode. In this mode, as the primary database processes transactions, redo data is asynchronously shipped to the standby database. The commit operation on the primary database does not wait for the standby database to acknowledge receipt of redo data before completing write operations on the primary database. If any standby destination becomes unavailable, processing continues on the primary database, and there is little effect on primary database performance.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;