RAC

ORACLE DATA GUARD FOR RAC 12C USING SEPARATE REPLICATION NETWORK

Posted on by debasis maity
ORACLE DATA GUARD FOR RAC 12C USING SEPERATE REPLICATION NETWORK

Table of Contents

Revision history

Rev. Resp Date Note.
PA1   07.10.2015 Initial Version
.

1    Environment

1.1   Hardware

In below table you can find information about hardware on which Oracle components will be installed:

  • Hardware details

Parameter Value Value Value Value
Hostname Kolkata1 Kolkata2 Delhi1 Delhi2
Server type Physical Physical Physical Physical
Role primary primary standby standby
Physical ip 10.51.11.7 10.51.11.8 10.51.11.36 10.51.11.37
Dataguard physical ip 10.51.20.69 10.51.20.70 10.75.20.101 10.75.20.102
Instance name ORCLPD11 ORCLPD12 ORCLDR11 ORCLDR12
 

1.2                      Software

In below table you can find information about software  installed:
  • Hardware details
Parametr Value Value Value  
Hostname Kolkata1 Kolkata2 Delhi1 Delhi2
Operating system RHEL 7.2 RHEL 7.2 RHEL 7.2 RHEL 7.2
Oracle 12.1.0.2.0 12.1.0.2.0 12.1.0.2.0 12.1.0.2.0

1.3        Networking

(This step is only required if you have separate replication network for DR traffic.It is not necessary if there is no separate DR  replication network)

1.3.1          Primary Site

Network in Primaryfor DR(primary database)
su – root

. oraenv

+ASM1

oifcfg iflist -p -n

10.51.20.64 is the subnet IP which we got from above command.

10.51.20.71 and 10.51.20.72 are VIP address.

srvctl add network -k 2 -S 10.51.20.64/255.255.255.240/bond3.161 -w static -v

srvctl add vip -n Kolkata1 -A 10.51.20.71/255.255.255.240/bond3.161 -k 2
srvctl add vip -n Kolkata2 -A 10.51.20.72/255.255.255.240/bond3.161 -k 2

crsctl status res -t

srvctl start vip -n Kolkata1
srvctl start vip -n Kolkata2

 

 

1.3.2       Disaster recovery Site

Network in Secondary for DR(standby database)
su – root

. oraenv

+ASM1

oifcfg iflist -p -n

  10.51.20.96 is the subnet IP which we got from above command.

10.51.20.103 and 10.51.20.104 are VIP address.

srvctl add network -k 2 -S 10.75.20.96/255.255.255.240/bond3.161 -w static -v

srvctl add vip -n Delhi1 -A 10.75.20.103/255.255.255.240/bond3.161 -k 2
srvctl add vip -n Delhi2 -A 10.75.20.104/255.255.255.240/bond3.161 -k 2

crsctl status res -t

srvctl start vip -n Delhi1
srvctl start vip -n Delhi2

 
1.4    LISTENER_DG both for primary and standby
Please use netca to create listener LISTENER_DG dedicated to handle DR traffic

1.4.1      Listener Configuration Kolkata1

Oracle Network Configuration Kolkata1
SID_LIST_LISTENER_DG =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLPD1.tdeprdcl.internal)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ORCLPD11)
    )
  )
 [grid@Kolkata1 admin]$ lsnrctl start LISTENER_DG
 LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 28-MAR-2017 09:13:21
 Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 Starting /u01/app/product/12.1.0/grid/bin/tnslsnr: please wait…
 TNSLSNR for Linux: Version 12.1.0.2.0 – Production
System parameter file is /u01/app/product/12.1.0/grid/network/admin/listener.ora
Log messages written to /u01/app/grid/diag/tnslsnr/Kolkata1/listener_dg/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_DG)))
 connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DG)))
STATUS of the LISTENER
————————
Alias                     LISTENER_DG
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date                28-MAR-2017 09:13:21
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/Kolkata1/listener_dg/alert/log.xml
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_DG)))
Services Summary…
Service “ORCLPD1.tdeprdcl.internal” has 1 instance(s).
  Instance “ORCLPD11”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

1.4.2                   Listener Configuration Kolkata2

Oracle Network Configuration Kolkata2
SID_LIST_LISTENER_DG =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLPD1.tdeprdcl.internal)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ORCLPD12)
    )
  )
 
[grid@Kolkata2 ~]$ lsnrctl start LISTENER_DG
 
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 28-MAR-2017 09:15:24
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
Starting /u01/app/product/12.1.0/grid/bin/tnslsnr: please wait…
 
TNSLSNR for Linux: Version 12.1.0.2.0 – Production
System parameter file is /u01/app/product/12.1.0/grid/network/admin/listener.ora
Log messages written to /u01/app/grid/diag/tnslsnr/Kolkata2/listener_dg/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_DG)))
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DG)))
STATUS of the LISTENER
————————
Alias                     LISTENER_DG
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date                28-MAR-2017 09:15:24
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/Kolkata2/listener_dg/alert/log.xml
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_DG)))
Services Summary…
Service “ORCLPD1.tdeprdcl.internal” has 1 instance(s).
  Instance “ORCLPD12”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

1.4.3    Listener Configuration Delhi1

Oracle Network Configuration Delhi1
 
SID_LIST_LISTENER_DG =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLDR1.tdeprdcl.internal)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ORCLDR11)
    )
  )
 
[grid@Delhi1 admin]$ lsnrctl start LISTENER_DG
 
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 28-MAR-2017 09:20:34
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
Starting /u01/app/product/12.1.0/grid/bin/tnslsnr: please wait…
 
TNSLSNR for Linux: Version 12.1.0.2.0 – Production
System parameter file is /u01/app/product/12.1.0/grid/network/admin/listener.ora
Log messages written to /u01/app/grid/diag/tnslsnr/Delhi1/listener_dg/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_DG)))
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DG)))
STATUS of the LISTENER
————————
Alias                     LISTENER_DG
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date                28-MAR-2017 09:20:34
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/Delhi1/listener_dg/alert/log.xml
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_DG)))
Services Summary…
Service “ORCLDR1.tdeprdcl.internal” has 1 instance(s).
  Instance “ORCLDR11”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

1.4.4     Listener Configuration Delhi2

Oracle Network Configuration Delhi1
SID_LIST_LISTENER_DG =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLDR1.tdeprdcl.internal)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ORCLDR12)
    )
  )
 
[grid@Delhi2 ~]$ lsnrctl start LISTENER_DG
 
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 28-MAR-2017 09:23:40
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
Starting /u01/app/product/12.1.0/grid/bin/tnslsnr: please wait…
 
TNSLSNR for Linux: Version 12.1.0.2.0 – Production
System parameter file is /u01/app/product/12.1.0/grid/network/admin/listener.ora
Log messages written to /u01/app/grid/diag/tnslsnr/Delhi2/listener_dg/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_DG)))
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DG)))
STATUS of the LISTENER
————————
Alias                     LISTENER_DG
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date                28-MAR-2017 09:23:40
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/Delhi2/listener_dg/alert/log.xml
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_DG)))
Services Summary…
Service “ORCLDR1.tdeprdcl.internal” has 1 instance(s).
  Instance “ORCLDR12”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

1.5    TNS Configuration

1.5.1    TNS Configuration for kolkata1 and kolkata2
Configure /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora as below
Oracle TNS Configuration For Kolkata1 and Kolkata2
 

ORCLDR1 =
          (description=
           (load_balance=on)
            (address=(protocol=tcp)(host=10.75.20.103)(port=1592))
            (address=(protocol=tcp)(host=10.75.20.104)(port=1592))
           (connect_data=
     (service_name=ORCLDR1.tdeprdcl.internal)))
 
ORCLPD1 =
          (description=
           (load_balance=on)
            (address=(protocol=tcp)(host=10.51.20.71)(port=1592))
            (address=(protocol=tcp)(host=10.51.20.72)(port=1592))
           (connect_data=
     (service_name=ORCLPD1.tdeprdcl.internal)))

  

1.5.2   TNS Configuration for delhi1 and delhi2

Oracle TNS Configuration For For delhi1 and delhi2
ORCLPD1 =
          (description=
           (load_balance=on)
            (address=(protocol=tcp)(host=10.51.20.71)(port=1592))
            (address=(protocol=tcp)(host=10.51.20.72)(port=1592))
           (connect_data=
     (service_name=ORCLPD1.tdeprdcl.internal)))
 
ORCLDR1 =
          (description=
           (load_balance=on)
            (address=(protocol=tcp)(host=10.75.20.103)(port=1592))
            (address=(protocol=tcp)(host=10.75.20.104)(port=1592))
           (connect_data=
     (service_name=ORCLDR1.tdeprdcl.internal)))

 

 

2 Dataguard Setup Detail

2.1  Architecture

On the orclprod primary database, Data Guard uses the following processes:
  • Log writer (LGWR) – it collects transaction redo information and updates the online redo In synchronous mode, it ships redo information directly to the remote file server (RFS) process on the physical standby database and waits for a confirmation before proceeding. In asynchronous mode, it ships the redo information directly but doesn’t wait before proceeding. In asynchronous mode, LGWR submits the network I/O request to the Log-write Network Server (LNSn) process for that destination
  • Archiver (ARCHn) – ARCH1 creates copy of the online redo logs locally for use in a primary database The ARCH2 process also ships the redo stream to the RFS process while simultaneously archiving the online log. ARCH2 is also responsible for proactively detecting and resolving gaps on the physical standby database.
  • Fetch archive log (FAL) – exists only on the physical standby database only: FAL provides a client/server mechanism for resolving gaps detected in the range of archived redo logs that are generated at the primary database and received at the standby This process is started only when needed and shuts down as soon as it is finished. It is very likely you will not see this process running.
Note: You can configure a primary database to ship redo information to a single standby database by using either LGWR or ARCn, but not both.
On the standby database bscsproddr, Data Guard uses the following processes:
  • Remote file server (RFS) – RFS receives redo information from the primary RFS can write the redo into standby redo logs or directly to archived redo logs. Each LNSn and ARCHn process from the primary database has its own RFS process.
  • Archiver (ARCHn) – ARCH1 process archives the standby redo
  • Managed recovery process (MRP) – exists only for physical standby database only, MRP applies archived redo log information to the physical standby If you start the managed recovery with the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT sql statement, this foreground session performs the recovery. If you use the optional DISCONNECT FROM SESSION clause, the MRP background process starts. If you use Data Guard broker to manage your standby databases, the broker always starts the MRP background process for a physical standby database.

2.2     Enable forced logging

In order to implement Standby Database I enable ‘Forced Logging’. This option ensures that even in the event that a ‘nologging’ operation is done, force logging takes precedence and all operations are logged into the redo logs.
SQL> alter database force logging;
Database altered.

2.3    Configure a standby redo log in Primary

A Standby Redo log is added to enable Data Guard Maximum Availability and Maximum Protection modes. It is important to configure the Standby Redo Logs (SRL) with the same size as the online redo logs.
Standby redolog should be 1 greater than no of primary redo log groups per thread.If You have 2 node RAC and thread has 4 redo log groups,then number of standby redo log will be 5 per thread.Standby redo log should never be multiplexed.
alter system set standby_file_management=manual scope=both sid=’*’;
alter database add standby logfile thread 1 group 9 ‘+FRA’ size 512m;
alter database add standby logfile thread 1 group 10 ‘+FRA’ size 512m;
alter database add standby logfile thread 1 group 11 ‘+FRA’ size 512m;
alter database add standby logfile thread 1 group 12 ‘+FRA’ size 512m;
alter database add standby logfile thread 1 group 13 ‘+FRA’ size 512m;
alter database add standby logfile thread 2 group 14 ‘+FRA’ size 512m;
alter database add standby logfile thread 2 group 15 ‘+FRA’ size 512m;
alter database add standby logfile thread 2 group 16 ‘+FRA’ size 512m;
alter database add standby logfile thread 2 group 17 ‘+FRA’ size 512m;
alter database add standby logfile thread 2 group 18 ‘+FRA’ size 512m;
alter system set standby_file_management=auto scope=both sid=’*’;
 

2.4 Enable archive mode in Primay

Check whether archive mode is enabled in database if it is not enabled:
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5
If archive mode was not enabled please execute below commands keeping one node of RAC down:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             419430920 bytes
Database Buffers         1174405120 bytes
Redo Buffers               14680064 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL>alter database open;

2.5   Create directories adump in Standby

mkdir -p /u01/app/oracle/admin/ORCLDR1/adump

2.6 Copy password file using ASM command from primary to standby.

In Primary
ASMCMD> pwcopy +DATA_BILLP/ORCLPD1/PASSWORD/pwdORCLpd1.256.926689449 /tmp/pwdORCLpd1
In Standby
pwcopy /tmp/pwdORCLpd1 +DATA_BILLP/pwdORCLdr1
copying +DATA_ORCLP/ORCLPD1/PASSWORD/pwdORCLpd1.256.926689449 -> /tmp/pwdORCLpd1

2.7  Add database and instance in CRS using srvctl in standby

srvctl add database -d ORCLDR1 -o /u01/app/oracle/product/12.1.0/db_1 -r PHYSICAL_STANDBY -s mount
srvctl add instance -d ORCLDR1 -i ORCLDR11 -n Delhi1
srvctl add instance -d ORCLDR1 -i ORCLDR12 -n Delhi2
In Standby,to add password file
srvctl add database -d ORCLDR1 -o /u01/app/oracle/product/12.1.0/db_1 -r PHYSICAL_STANDBY -s mount -pwfile +DATA_ORCLP/pwdORCLdr1
To modify password file
srvctl modify database -d ORCLDR1 -pwfile +DATA_ORCLP/pwdORCLdr1

2.8 Copy pfile and start instance in each node of standby with pfile

copy pfile from /tmp to $ORACLE_HOME/dbs and rename the parameter file to init$ORACLE_SID.ora in each node and startup nomount mode for each node of DR.
Please change db_unique_name and SID name in pfile from DR side.
[oracle@Delhi1 ~]$ sqlplus / as sysdba
$SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 3 09:34:01 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=’$ORACLE_HOME/dbs/init$ORACLE_SID.ora’;
If you face permission denied issue in ASM,please include oracle under asmadmin
 /usr/sbin/usermod -G oinstall,asmdba,dba,oper,asmadmin,asmoper oracle

2.9   Please check If you connect with SYS user to RMAN in each node individually

2.10   Now We need to take RMAN backup either in ASM diskgroup or any other mount point.

run
{
sql “alter system switch logfile”;
allocate channel ch1 type disk format ‘+DATA/ORCLPD1/BKP/Primary_bkp_for_stndby_%U’;
allocate channel ch2 type disk format ‘+DATA/ORCLPD1/BKP/Primary_bkp_for_stndby_%U’;
BACKUP AS COMPRESSED BACKUPSET DATABASE;
backup current controlfile for standby;
sql “alter system archive log current”;
}
Non-ASM Backup
run
{
sql “alter system switch logfile”;
allocate channel ch1 type disk format ‘/U01/ORCLPD1/BKP/Primary_bkp_for_stndby_%U’;
allocate channel ch2 type disk format ‘/U01/ORCLPD1/BKP/Primary_bkp_for_stndby_%U’;
BACKUP AS COMPRESSED BACKUPSET DATABASE;
backup current controlfile for standby;
sql “alter system archive log current”;
}

2.11   Manual backup and duplicate for standby(This is not active duplicate)

If you use NFS as share mount point for backup,please use it.No need to copy ASM files as above.
Login to DR dataase
export ORACLE_SID=ORCLDR11
sqlplus / as sysdba
startup nomount pfile=$ORACLE_HOME/dbs/initTEST.ora
rman target sys/****@ORCLPD1 auxiliary /
DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

2.12  Automated backup and restoration to DR (This is active duplicate)

rman target sys/test#123@ORCLPD11 auxiliary sys/test#123@ORCLDR11
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel prim type disk;
allocate auxiliary channel aux type disk;
duplicate target database for standby from active database spfile
parameter_value_convert ‘ORCLPD1′,’ORCLDR1’
set db_file_name_convert=’ORCLPD1′,’ORCLDR1′
set log_file_name_convert=’ORCLPD1′,’ORCLDR1′
set log_archive_max_processes=’10’
set db_unique_name=’ORCLDR1′
set standby_file_management=’AUTO’
set log_archive_config=’dg_config=(ORCLPD1,ORCLDR1)’
set log_archive_dest_1=’location=+ARCH_BILLP valid_for=(all_logfiles,all_roles) db_unique_name=ORCLDR1′
set log_Archive_dest_2=’service=ORCLPD1 async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=ORCLPD1′
set log_archive_config=’dg_config=(ORCLPD1,ORCLDR1)’;
sql channel aux “alter database add standby logfile thread 1 group 9 ‘+FRA’ size 512m”;
sql channel aux “alter database add standby logfile thread 1 group 10 ‘+FRA’ size 512m”;
sql channel aux “alter database add standby logfile thread 1 group 11 ‘+FRA’ size 512m”;
sql channel aux “alter database add standby logfile thread 1 group 12 ‘+FRA’ size 512m”;
sql channel aux “alter database add standby logfile thread 1 group 13 ‘+FRA’ size 512m”;
sql channel aux “alter database add standby logfile thread 2 group 14 ‘+FRA’ size 512m”;
sql channel aux “alter database add standby logfile thread 2 group 15 ‘+FRA’ size 512m”;
sql channel aux “alter database add standby logfile thread 2 group 16 ‘+FRA’ size 512m”;
sql channel aux “alter database add standby logfile thread 2 group 17 ‘+FRA’ size 512m”;
sql channel aux “alter database add standby logfile thread 2 group 18 ‘+FRA’ size 512m”;
sql channel prim “alter system archive log current”;
sql channel aux “alter database recover managed standby database disconnect”;
}
Log file:-
Note:- If you have ASM diskgroup name different from primary,then you need to add below parameters in above script
SET CONTROL_FILES =’+FRA’,’+DATA_ORCLD’
set db_create_file_dest=’+ DATA_ORCLD ‘
SET log_archive_dest_1=’LOCATION=+FRA’

2.13  Re-create standby logfiles

sqlplus / as sysdba
alter database drop standby logfile group 9;
alter database drop standby logfile group 10;
alter database drop standby logfile group 11;
alter database drop standby logfile group 12;
alter database drop standby logfile group 13;
alter database drop standby logfile group 14;
alter database drop standby logfile group 15;
alter database drop standby logfile group 16;
alter database drop standby logfile group 17;
alter database drop standby logfile group 18;
alter database add standby logfile thread 1 group 9 ‘+fra’ size 512M;
alter database add standby logfile thread 1 group 10 ‘+fra’ size 512M;
alter database add standby logfile thread 1 group 11 ‘+fra’ size 512M;
alter database add standby logfile thread 1 group 12 ‘+fra’ size 512M;
alter database add standby logfile thread 1 group 13 ‘+fra’ size 512M;
alter database add standby logfile thread 2 group 14 ‘+fra’ size 512M;
alter database add standby logfile thread 2 group 15 ‘+fra’ size 512M;
alter database add standby logfile thread 2 group 16 ‘+fra’ size 512M;
alter database add standby logfile thread 2 group 17 ‘+fra’ size 512M;
alter database add standby logfile thread 2 group 18 ‘+fra’ size 512M;
 

2.14    Please create spfile add instance for RAC

[oracle@Delhi1 ~]$ sqlplus as sysdba
SQL> show parameter spfile;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string
SQL> create spfile=’+DATA_BSCS/spfileORCLDR1.ora’ from pfile;
File created.
srvctl modify database -d ORCLDR1 -spfile +DATA_ORCLP/spfileORCLDR1.ora
[oracle@Delhi1 ~]$ srvctl start instance -d ORCLDR1 -i ORCLDR11
PRCR-1013 : Failed to start resource ora.orcldr1.db
PRCR-1064 : Failed to start resource ora.orcldr1.db on node Delhi1
CRS-5017: The resource action “ora.orcldr1.db start” encountered the following error:
ORA-00205: error in identifying control file, check alert log for more info
. For details refer to “(:CLSN00107:)” in “/u01/app/grid/diag/crs/Delhi1/crs/trace/crsd_oraagent_oracle.trc”.
CRS-2674: Start of ‘ora.orcldr1.db’ on ‘Delhi1’ failed
Please check the restore log file to identify where control file has been created.
Now you need to change control_files parameter after startup nomount.
alter system set control_files=’+REDO_ORCLP/ORCLDR1/CONTROLFILE/current.316.941562301,+FRA/ORCLDR1/CONTROLFILE/current.330.941562301′ scope=spfile;

2.15 DGMGRL configuration

In Primary:-
alter system set dg_broker_start=false sid=’*’;
alter system set dg_broker_config_file1=’+FRA/dr1ORCLPD1.dat’ sid=’*’;
alter system set dg_broker_config_file2=’+FRA/dr2ORCLPD1.dat’ sid=’*’;
alter system set dg_broker_start=true sid=’*’;
In DR:-
alter system set dg_broker_start=false sid=’*’;
alter system set dg_broker_config_file1=’+FRA/dr1ORCLDR1.dat’ sid=’*’;
alter system set dg_broker_config_file2=’+FRA/dr2ORCLDR1.dat’ sid=’*’;
alter system set dg_broker_start=true sid=’*’;
dgmgrl connect /
CREATE CONFIGURATION dg_config_orcl AS PRIMARY DATABASE IS ORCLPD1 connect identifier is ORCLPD1;
ADD DATABASE ORCLDR1 AS CONNECT IDENTIFIER IS ORCLDR1;
SHOW CONFIGURATION;
ENABLE CONFIGURATION;
SHOW CONFIGURATION;
validate database ORCLPD1;
validate database ORCLDR1;

2.16 Change spfile if required to reflect DR related change in DR site (Delhi) .(Not required if you used DGMGRL)

In Primary:-
alter system set log_archive_config=’dg_config=(ORCLPD1,ORCLDR1)’;
alter system set standby_file_management=AUTO;
alter system set log_archive_dest_2=’service=”ORCLPD1″ LGWR ASYNC NOAFFIRM delay=0 optional max_failure=0 max_connections=1 reopen=300 db_unique_name=”ORCLPD1″ net_timeout=30 valid_for=(all_logfiles,primary_role)’
alter system set fal_server=’ORCLDR1′
In Standby:-
alter system set log_archive_config=’dg_config=(ORCLPD1,ORCLDR1)’;
alter system set standby_file_management=AUTO;
alter system set log_archive_dest_2=’service=”ORCLDR1″ LGWR ASYNC NOAFFIRM delay=0 optional max_failure=0 max_connections=1 reopen=300 db_unique_name=”ORCLDR1″ net_timeout=30 valid_for=(all_logfiles,primary_role)’
alter system set fal_server=’ORCLPD1′

2.17   Start the managed recovery process  if not already started.

sqlplus / as sysdba
alter database recover managed standby database using current logfile disconnect;
If you want to setup active dataguard,Active dataguard require seperate license cost.
1.Mount the database.
2.Open the database.
3.Start log apply using following command:-
alter database recover managed standby database using current logfile disconnect;

2.18  Check the status of redo log apply in standby database. This should be executed in primary

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

2.19   Check the alert log files in both standby and primary.

2.20   The process to switchover and failover dataguard

During actual  DR failover when prod primary will not be available permanently then   DR standby will become primary and one standby database to be created on DR side node2.
  • Switchover:
 Before performing switchover, kindly verify the state of data guard on both the instances by following SQL queries:
SQL> ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
SQL> SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#;
…
…
59 08-OCT-14 08-OCT-14 YES
60 08-OCT-14 08-OCT-14 YES
61 08-OCT-14 08-OCT-14 YES
SQL> select dest_name,status,error from v$archive_dest where dest_name=’LOG_ARCHIVE_DEST_2′;
DEST_NAME
——————————————————————————–
STATUS ERROR
——— —————————————————————–
LOG_ARCHIVE_DEST_2
VALID
SQL> select message from v$dataguard_status;
Note: This command will give you appropriate message about the data guard current status.
After getting confirmation on data guard smooth activity, we can instantiate switchover activity by issuing following sort of commands:
On Primary database:
Step-1>>
Connect to Primary database and convert primary database to standby.
[oracle@pr ~]$ sqlplus / as sysdba
SQL> alter database commit to switchover to standby;
Database altered.
Step-2>>
Shutdown primary database:
SQL> shutdown immediate;
Step-3>>
Startup nomount old primary database as new standby database:
SQL> startup nomount
ORACLE instance started.
Total System Global Area 972898304 bytes
Fixed Size 2219272 bytes
Variable Size 805307128 bytes
Database Buffers 159383552 bytes
Redo Buffers 5988352 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Verify database role on old primary database:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
RTS MOUNTED PHYSICAL STANDBY
On Standby database:
Step-4>>
On original standby database, Convert old standby database to primary database:
[oracle@dr ~]$ sqlplus / as sysdba
Step-5>>
Convert old standby database as primary and shutdown database:
SQL> alter database commit to switchover to primary;
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Step-6>>
Startup old standby database as primary database:
SQL> startup
ORACLE instance started.
Total System Global Area 972898304 bytes
Fixed Size 2219272 bytes
Variable Size 717226744 bytes
Database Buffers 247463936 bytes
Redo Buffers 5988352 bytes
Database mounted.
Database opened.
Verify database role on old standby database:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
RTS READ WRITE PRIMARY
Switch over activity have completed successfully, our old primary database has become standby and old standby database has become primary database.
Note
To test above switchover activity, generate multiple archive logs on primary database and verify those archive logs being transferred on standby database.
Switchback:
To switchback, you have to follow same above mentioned 6 steps.

  • FAILOVER:

In short, the failover is the deformation of the production (primary) database and activating standby database as the primary. It is not reversible. When enabled, re-create the standby database . What to do in case of failover:
(Important note: Kolkata is the primary server and Delhi is the standby server)
  1. [Kolkata] If the primary database is accessible and running, then it must provide to send redo buffer to the standby database.
SQL> alter system flush redo to standby_db_name;
 SQL>alter system archive log current;
If you don’t receive an error, you can continue with step 5th. In this case, the system can be opened by zero data loss. If you receive an error, We continue with step 2 to open the system at least data loss.
  1. [Delhi] We must run the following query to learn last applied archive log sequence number.
SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
  1. [Delhi] If you can access archive logs which are not copied to standby then copy archives to standby. After copy archive log files we must register them to standby database. This operation must be done for every thread.
SQL> alter database register physical logfile ‘/oracle/ora11g/dbs/arch/ TALIP_991834413_1_102.arc ‘;
  1. [Delhi] Check the standby database for redo gap. If there is a gap then we must copy archive log files and register.
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP; 
SQL> alter database register physical logfile ‘/oracle/ora11g/dbs/arch/ TALIP_991834413_1_101.arc ‘;
As a result of the above query until it returns to zero.
  1. [Delhi] Stop the redo apply process in standby database.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
  1. [Delhi] Finish to apply archive logs copied from primary.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
If you get an error, it means there are redo logs not applied. Consider 2th and 4th steps. You can also continue with following command;
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
In this situation you can open database in 8th step.  If you get no error, continue with 7th step.
  1. [Delhi] Switch standby database to primary database.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
  1. [Delhi] Open database.
SQL> ALTER DATABASE OPEN;
3                          References