Configure Oracle 12c RAC to RAC Data Guard (DR) on Linux

We will discuss how to configure RAC to RAC Data Guard on Linux. If you follow all below steps you will able to configure the two node (or multi node) DR.

Overview:

Data Guard configuration consists of one production database and one or more standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided they can communicate with each other. For example, you can have a standby database on the same system as the production database, along with two standby databases on other systems at remote locations.

1. Overview of Primary and Standby DB Settings:

PRIMARYSTANDBY
Site LocationChicagoAtlanta
Cluster Nodeocmnode1, ocmnode2ocmdrnode1, ocmdrnode2
SCANocmnode-scanocmdrnode-scan
VIPSocmnode1-vip
ocmnode2-vip
ocmdrnode1-vip
ocmdrnode2-vip
DB_UNIQUE_NAMEORCLPRODDRORCL
DB_NAMEORCLORCL
DB StorageASMASM
File ManagementOMFOMF
ASM DiskgroupOCR, DATA, FRAOCR, DATA, FRA
OS Linux 6.5Linux 6.5
DB & GRID Version12.1.0.212.1.0.2

2. Prerequisites:

Note:::Assumed you have already Installed Grid Infrastructure and Oracle Database Binary/Software on STANDBY Servers.

How to install GI and Oracle DB software/binary, Check this link: RAC Configuration

Note:::From above link, don’t create database using DBCA on Standby site.

3. Perform Prerequisites on Primary Database:

  • Enable Force Logging.
  • Modify Data Guard related Parameters on spfile or pfile.
  • Enable Archivelog Mode.
  • Create the Standby Redo Logs.
  • Backup the Database for Standby.
  • Create pfile for standby database.
  • Update the tnsnames.ora file.

3.1. Enable Force Logging:

SQL> SELECT NAME, FORCE_LOGGING FROM V$DATABASE;

NAME      FORCE_LOGGING
--------- ---------------------------------------
ORCL      NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> SELECT NAME, FORCE_LOGGING FROM V$DATABASE;

NAME      FORCE_LOGGING
--------- ---------------------------------------
ORCL      YES

3.2 Modify Data Guard related Parameters on spfile or pfile:

Note::: Primary DB UNIQUE NAME is ORCL and Standby DB UNIQUE NAME is ORCLDR. So Change the value accordingly for your environment.

SQL> SHOW PARAMETER DB_NAME

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      ORCL
SQL> SHOW PARAMETER DB_UNIQUE_NAME

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      ORCL
DB_NAME=ORAL
DB_UNIQUE_NAME=ORCL
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCLDR)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL'
LOG_ARCHIVE_DEST_2='SERVICE=INDIA ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDR' 
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=4
FAL_SERVER=ORCLDR 
DB_FILE_NAME_CONVERT='ORCLDR','ORCL'
LOG_FILE_NAME_CONVERT= 'ORCLDR','ORCL'
STANDBY_FILE_MANAGEMENT=AUTO

Before changing values, create a PFile from SPfile for backup.

Below parameters can be changed without rebooting database.

SQL> CREATE PFILE='/u01/app/oracle/product/12.1.0/dbhome_2/dbs/pfile_orcl.ora' from SPFILE;

File created.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,DRORCL)' SCOPE=BOTH SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL' SCOPE=BOTH SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DRORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DRORCL' SCOPE=BOTH SID='*';
ALTER SYSTEM SET FAL_SERVER=DRORCL SCOPE=BOTH SID='*';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4 SCOPE=BOTH SID='*';

Database reboot is required to be impacted of below parameters changes.

ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DRORCL','ORCL' SCOPE=SPFILE SID='*';
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DRORCL','ORCL' SCOPE=SPFILE SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE SID='*';

Validate parameter values after changed using below query:

set linesize 1000 pages 0
col value for a80
col name for a40
 
SQL> select name, value from v$parameter where name in ('db_name', 'db_unique_name', 'log_archive_config',  'log_archive_dest_1', 'log_archive_dest_2', 'log_archive_dest_state_1',  'log_archive_dest_state_2',   'remote_login_passwordfile', 'log_archive_format', 'log_archive_max_processes', 'fal_server', 'db_file_name_convert',  'log_file_name_convert', 'standby_file_management');

db_file_name_convert                     DRORCL, ORCL
log_file_name_convert                    DRORCL, ORCL
log_archive_dest_1                       LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL
log_archive_dest_2                       SERVICE=DRORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DRORCL
log_archive_dest_state_1                 enable
log_archive_dest_state_2                 enable
fal_server                               DRORCL
log_archive_config                       DG_CONFIG=(ORCL,DRORCL)
log_archive_format                       %t_%s_%r.arc
log_archive_max_processes                4
standby_file_management                  AUTO
remote_login_passwordfile                EXCLUSIVE
db_name                                  ORCL
db_unique_name                           ORCL

14 rows selected.

3.3. Enable Archivelog Mode On Primary DB:

Check this link to change Archive Log Mode : Change Archivelog Mode

3.4 Create the Standby Redo Logs on Primary DB

Standby Redo Log File: The recommended number of standby redo logs is: (maximum # of logfiles +1) * maximum # of threads.
This example uses two Online Redo Logfiles for each thread. Thus, the number of standby redo logs should be (2 + 1) * 2 = 6. That is, one more standby redo log file for each thread.

Make sure standby logfile size is same with REDO Logfile size. Otherwise it will generate error.

SQL> SELECT MAX(BYTES/1024/1024) SIZE_MB, COUNT(*) FROM V$LOG;

   SIZE_MB   COUNT(*)
---------- ----------
        50          6
alter database add standby logfile thread 1 group 7 '+DATA/ORCL/STANDBYLOG/standby_group_07.log' size 50M;
alter database add standby logfile thread 1 group 8 '+DATA/ORCL/STANDBYLOG/standby_group_08.log' size 50M;
alter database add standby logfile thread 1 group 9 '+DATA/ORCL/STANDBYLOG/standby_group_09.log' size 50M;
alter database add standby logfile thread 1 group 10 '+DATA/ORCL/STANDBYLOG/standby_group_10.log' size 50M;
alter database add standby logfile thread 2 group 11 '+DATA/ORCL/STANDBYLOG/standby_group_11.log' size 50M;
alter database add standby logfile thread 2 group 12 '+DATA/ORCL/STANDBYLOG/standby_group_12.log' size 50M;

3.5. Backup the Database for Standby:

Take full backup of PROD database using RMAN:

[oracle@ocmnode1 backup]$ rman target /
connected to target database: ORCL (DBID=1575875723)

RMAN>run
{
allocate channel ch1 type disk format '/u01/software/backup/Primary_bkp_for_stndby_%U';
configure controlfile autobackup format for device type disk to '/u01/software/backup/controlfile_stndby_%F';
backup tag='LVL0_FULLDB_BACKUP' as backupset database include current controlfile plus archivelog;
backup current controlfile for standby; 
}

3.6. Create pfile for standby database:

[oracle@ocmnode1 admin]$ sqlplus / as sysdba

SQL> create pfile='/u01/software/backup/pfile_standby.ora' from spfile;

File created.

Password File:

[oracle@ocmnode1 admin]$ asmcmd pwget --dbuniquename ORCL
+DATA/ORCL/PASSWORD/pwdorcl.256.1048094823
OR,
[oracle@ocmnode1 admin]$ srvctl config database -d ORCL
Database unique name: ORCL
Database name: ORCL
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_2
Oracle user: oracle
Spfile: +DATA/ORCL/PARAMETERFILE/spfile.269.1048095383
Password file: +DATA/ORCL/PASSWORD/pwdorcl.256.1048094823
Domain:
Start options: open

OR,
[oracle@ocmnode1 admin]$ crsctl stat res ora.orcl.db -f | grep ORCL
DB_UNIQUE_NAME=ORCL
GEN_AUDIT_FILE_DEST=/u01/app/oracle/admin/ORCL/adump
GEN_USR_ORA_INST_NAME@SERVERNAME(ocmnode1)=ORCL1
GEN_USR_ORA_INST_NAME@SERVERNAME(ocmnode2)=ORCL2
PWFILE=+DATA/ORCL/PASSWORD/pwdorcl.256.1048094823
SERVER_POOLS=ora.ORCL
SPFILE=+DATA/ORCL/PARAMETERFILE/spfile.269.1048095383
USR_ORA_DB_NAME=ORCL
USR_ORA_INST_NAME@SERVERNAME(ocmnode1)=ORCL1
USR_ORA_INST_NAME@SERVERNAME(ocmnode2)=ORCL2

Cope password file from ASM to Filesystem:

[oracle@ocmnode1 admin]$ asmcmd
ASMCMD> cd +DATA/ORCL/PASSWORD/
ASMCMD> ls
pwdorcl.256.1048094823
ASMCMD> pwcopy pwdorcl.256.1048094823 /tmp/pwdorcl.256.1048094823
copying +DATA/ORCL/PASSWORD/pwdorcl.256.1048094823 -> /tmp/pwdorcl.256.1048094823

[oracle@ocmnode1 admin]$ ls -l /tmp/pwdorcl.256.1048094823
-rw-r----- 1 grid oinstall 7680 Aug 16 18:25 /tmp/pwdorcl.256.1048094823
[oracle@ocmnode1 admin]$ cp /tmp/pwdorcl.256.1048094823 /u01/software/backup/

3.7. Update the tnsnames.ora file:

[oracle@ocmnode1 admin]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_2/network/admin
[oracle@ocmnode1 admin]$ cat tnsnames.ora
# tnsnames.ora.ocmnode1 Network Configuration File: /u01/app/12.1.0/grid/network/admin/tnsnames.ora.ocmnode1
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode-scan)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

ORCL1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode1-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

ORCL2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode2-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

4. Perform Prerequisites on Standby Database:

  • Create required directories for Standby Instance
  • Create directories on ASM Diskgroup.
  • Copy pfile (created from PROD DB on previous stage) to Standby Database Server
  • Copy the password file.
  • Copy RMAN backup.
  • Copy the tnsnames.ora.
  • Modify the pfile.

4.1. Create required directories on Standby Servers (all nodes):

# On Node1:
$mkdir –p /u01/app/oracle/admin/DRORCL/adump
$mkdir –p /u01/app/oracle/diag/rdbms/DRORCL/ORCL1
$cd /u01/app/oracle/diag/rdbms/DRORCL/ORCL1
$mkdir trace cdump

# On Node2:
$mkdir –p /u01/app/oracle/admin/DRORCL/adump
$mkdir –p /u01/app/oracle/diag/rdbms/DRORCL/ORCL2
$cd /u01/app/oracle/diag/rdbms/DRORCL/ORCL2
$mkdir trace cdump

4.2. Create directories on ASM Diskgroup:

[oracle@ocmdrnode1 ~]$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      8188     8089                0            8089              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576      8188     8089                0            8089              0             N  FRA/
MOUNTED  EXTERN  N         512   4096  1048576      8189     3717                0            3717              0             Y  OCR/
ASMCMD> cd DATA
ASMCMD> mkdir DRORCL
ASMCMD> mkdir DATAFILE CONTROLFILE TEMPFILE ONLINELOG STANDBYLOG PASSWORD  PARAMETERFILE
ASMCMD> cd ..
ASMCMD> cd ..
ASMCMD> cd FRA
ASMCMD> mkdir DRORCL
ASMCMD> cd DRORCL
ASMCMD> mkdir ARCHIVELOG CONTROLFILE ONLINELOG STANDBYLOG PARAMETERFILE

4.3. Copy pfile (created from PROD DB on previous stage) to Standby Database Server:

[oracle@ocmnode1 backup]$ scp pfile_standby.ora oracle@ocmdrnode1:/u01/software/backup/
oracle@ocmdrnode1's password:
pfile_standby.ora                                                                             100% 1910     1.9KB/s   00:00

4.4. Copy Password File:

[oracle@ocmnode1 backup]$ scp pwdorcl.256.1048094823 oracle@ocmdrnode1:/u01/software/backup/
oracle@ocmdrnode1's password:
pwdorcl.256.1048094823                                                                        100% 7680     7.5KB/s   00:00

4.5. Copy RMAN Backup:

[oracle@ocmnode1 backup]$ scp Primary_bkp* oracle@ocmdrnode1:/u01/software/backup/
oracle@ocmdrnode1's password:
Primary_bkp_for_stndby_01v8149b_1_1                                                           100%  830MB  59.3MB/s   00:14
Primary_bkp_for_stndby_02v8149s_1_1                                                           100% 1467MB  61.1MB/s   00:24
Primary_bkp_for_stndby_03v814al_1_1                                                           100%   18MB  18.3MB/s   00:00
Primary_bkp_for_stndby_04v814at_1_1                                                           100%   56KB  56.0KB/s   00:00
Primary_bkp_for_stndby_05v814au_1_1                                                           100%   18MB  18.2MB/s   00:00

4.6. Modify pfile for Standby Database:

Parameter values on PROD database:

[oracle@ocmnode1 backup]$ cat pfile_standby.ora
*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
*.control_files='+DATA/ORCL/CONTROLFILE/current.261.1048094987','+FRA/ORCL/CONTROLFILE/current.256.1048094987'
*.db_file_name_convert='DRORCL','ORCL'
*.db_unique_name='ORCL'
*.fal_server='DRORCL'
*.log_archive_config='DG_CONFIG=(ORCL,DRORCL)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL'
*.log_archive_dest_2='SERVICE=DRORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DRORCL'
*.log_file_name_convert='DRORCL','ORCL'

Above mentioned parameters need to change for Standby database. After Changed:

[oracle@ocmdrnode1 backup]$ cat pfile_standby.ora
*.audit_file_dest='/u01/app/oracle/admin/DRORCL/adump'
*.control_files='+DATA/DRORCL/CONTROLFILE/control01.ctl','+FRA/DRORCL/CONTROLFILE/control02.ctl'
*.db_file_name_convert='ORCL','DRORCL'
*.db_unique_name='DRORCL'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(ORCL,DRORCL)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DRORCL'
*.log_archive_dest_2='SERVICE=DRORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
*.log_file_name_convert='ORCL','DRORCL'
*.remote_listener='ocmdrnode-scan:1521'

Standby database parameter file will be like below: (Final Version)

[oracle@ocmdrnode1 backup]$ cat pfile_standby.ora
ORCL1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORCL2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/app/oracle/admin/DRORCL/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.1.0.2.0'
*.control_files='+DATA/DRORCL/CONTROLFILE/control01.ctl','+FRA/DRORCL/CONTROLFILE/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='ORCL','DRORCL'
*.db_name='ORCL'
*.db_unique_name='DRORCL'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4785m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.fal_server='ORCL'
ORCL1.instance_number=1
ORCL2.instance_number=2
*.log_archive_config='DG_CONFIG=(ORCL,DRORCL)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DRORCL'
*.log_archive_dest_2='SERVICE=DRORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.log_file_name_convert='ORCL','DRORCL'
*.memory_target=800m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
ORCL2.thread=2
ORCL1.thread=1
ORCL2.undo_tablespace='UNDOTBS2'
ORCL1.undo_tablespace='UNDOTBS1'

3.7. Copy tnsnames.ora File:

Cope primary database’s tnsnames.ora file to Standby database server:

From Primary node to Standby node:

[oracle@ocmnode1 backup]$ scp tnsnames.ora oracle@ocmdrnode1:/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/
oracle@ocmdrnode1's password:
tnsnames.ora                                                                                  100%  691     0.7KB/s   00:00

Add LOCAL_LISTENER parameters in the tnsnames.ora on standby nodes to reflect the standby specific vip host names:

LISTENER_ORCL1=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode1-vip)(PORT = 1521)))
LISTENER_ORCL2=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode2-vip)(PORT = 1521)))

LISTENER_DRORCL1_OCMDRNODE1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmdrnode1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.112)(PORT=1521)(IP=FIRST))))
LISTENER_DRORCL2_OCMDRNODE2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmdrnode2-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.113)(PORT=1521)(IP=FIRST))))

TNSNAMES.ora files on Standby:

[oracle@ocmdrnode1 admin]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin
[oracle@ocmdrnode1 admin]$ cat tnsnames.ora
# tnsnames.ora.ocmnode1 Network Configuration File: /u01/app/12.1.0/grid/network/admin/tnsnames.ora.ocmnode1
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode-scan)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

ORCL1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode1-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

ORCL2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode2-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )


LISTENER_ORCL1=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode1-vip)(PORT = 1521)))
LISTENER_ORCL2=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode2-vip)(PORT = 1521)))


LISTENER_DRORCL1_OCMDRNODE1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmdrnode1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.112)(PORT=1521)(IP=FIRST))))
LISTENER_DRORCL2_OCMDRNODE2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmdrnode2-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.113)(PORT=1521)(IP=FIRST))))

5. Create Physical Standby Data Guard with RAC:

  • Start the Instance in NOMOUNT using Created pfile for standby.
  • Restore the Standby Database using RMAN DUPLICATE command.
  • Create the Online Redo logs and Standby redo logs.
  • Add Standby Database’s TNS on TNSNAMES.ORA on all Standby and Primary Nodes.
  • Create SPFILE from PFile and add to OCR.
  • Add Standby database to OCR.
  • Start Managed Recovery Process on Standby.
  • Validate Replication between Primary and Standby.
  • Start the Active Data Guard.

5.1. Start the Instance in NOMOUNT using Created pfile for standby: (Standby Node1)

[oracle@ocmdrnode1 dbs]$ export ORACLE_SID=ORCL1
[oracle@ocmdrnode1 dbs]$ export ORACLE_BASE=/u01/app/oracle
[oracle@ocmdrnode1 dbs]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2
[oracle@ocmdrnode1 dbs]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@ocmdrnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 17 12:44:06 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/product/12.1.0/dbhome_2/dbs/pfile_standby.ora';
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             654314224 bytes
Database Buffers          176160768 bytes
Redo Buffers                5455872 bytes
SQL> create spfile='+DATA/DRORCL/PARAMETERFILE/spfileorcl.ora' from  pfile='/u01/app/oracle/product/12.1.0/dbhome_2/dbs/pfile_standby.ora';

File created.

5.2. Restore Standby Database using RMAN:

[oracle@ocmdrnode1 dbs]$ rman AUXILIARY /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Aug 17 14:03:06 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: ORCL (not mounted)

RMAN> run{
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
DUPLICATE DATABASE FOR STANDBY BACKUP LOCATION '/u01/software/backup/' nofilenamecheck;
release channel c1;
release channel c2;
}
Restoration script log:
[oracle@ocmdrnode1 dbs]$ rman AUXILIARY /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Aug 17 14:16:58 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: ORCL (not mounted)

RMAN> run{
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
DUPLICATE DATABASE FOR STANDBY BACKUP LOCATION '/u01/software/backup/' nofilenamecheck;
release channel c1;
release channel c2;
}2> 3> 4> 5> 6> 7>

allocated channel: c1
channel c1: SID=42 instance=ORCL1 device type=DISK

allocated channel: c2
channel c2: SID=43 instance=ORCL1 device type=DISK

Starting Duplicate Db at 17-AUG-20

contents of Memory Script:
{
   restore clone standby controlfile from  '/u01/software/backup/Primary_bkp_for_stndby_05v814au_1_1';
}
executing Memory Script

Starting restore at 17-AUG-20

channel c2: skipped, AUTOBACKUP already found
channel c1: restoring control file
channel c1: restore complete, elapsed time: 00:00:08
output file name=+DATA/DRORCL/CONTROLFILE/control01.ctl
output file name=+FRA/DRORCL/CONTROLFILE/control02.ctl
Finished restore at 17-AUG-20

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for tempfile  1 to
 "+DATA";
   switch clone tempfile all;
   set newname for datafile  1 to
 "+DATA";
   set newname for datafile  2 to
 "+DATA";
   set newname for datafile  3 to
 "+DATA";
   set newname for datafile  4 to
 "+DATA";
   set newname for datafile  5 to
 "+DATA";
   set newname for datafile  6 to
 "+DATA";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 17-AUG-20

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to +DATA
channel c1: restoring datafile 00002 to +DATA
channel c1: restoring datafile 00003 to +DATA
channel c1: restoring datafile 00004 to +DATA
channel c1: restoring datafile 00005 to +DATA
channel c1: restoring datafile 00006 to +DATA
channel c1: reading from backup piece /u01/software/backup/Primary_bkp_for_stndby_02v8149s_1_1
channel c1: piece handle=/u01/software/backup/Primary_bkp_for_stndby_02v8149s_1_1 tag=LVL0_FULLDB_BACKUP
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:35
Finished restore at 17-AUG-20

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=1048688284 file name=+DATA/DRORCL/DATAFILE/system.258.1048688249
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=1048688284 file name=+DATA/DRORCL/DATAFILE/undotbs2.261.1048688249
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=1048688284 file name=+DATA/DRORCL/DATAFILE/sysaux.257.1048688249
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=1048688284 file name=+DATA/DRORCL/DATAFILE/undotbs1.259.1048688249
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=1048688284 file name=+DATA/DRORCL/DATAFILE/example.260.1048688249
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=1048688284 file name=+DATA/DRORCL/DATAFILE/users.264.1048688249
Finished Duplicate Db at 17-AUG-20

released channel: c1

released channel: c2

5.3. Add Standby Database’s TNS on TNSNAMES.ORA on all Standby and Primary Nodes:

DRORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode-scan)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DRORCL)
    )
  )

DRORCL1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode1-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DRORCL)
      (SID = ORCL1)
    )
  )

DRORCL2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode2-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DRORCL)
      (SID = ORCL2)
    )
  )

Make sure that primary and standby databases TNS are added in tnsnames.ora file on primary and standby database.

5.4. TNS file on Primary and Standby:

TNSNAMES.ORA File will look like below on Primary Nodes:

TNSNAMES.ORA file on Primary Database: (All Nodes)
[oracle@ocmnode1 admin]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_2/network/admin
[oracle@ocmnode1 admin]$ cat tnsnames.ora
# tnsnames.ora.ocmnode1 Network Configuration File: /u01/app/12.1.0/grid/network/admin/tnsnames.ora.ocmnode1
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode-scan)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

ORCL1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode1-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

ORCL2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode2-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

DRORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode-scan)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DRORCL)
    )
  )

DRORCL1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode1-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DRORCL)
      (SID = ORCL1)
    )
  )

DRORCL2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode2-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DRORCL)
      (SID = ORCL2)
    )
  )

TNSNAMES.ORA File will look like below on Standby Nodes:

TNSNAMES.ORA file on Standby Database: (All Nodes)
[oracle@ocmdrnode1 admin]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_2/network/admin
[oracle@ocmdrnode1 admin]$ cat tnsnames.ora
# tnsnames.ora.ocmnode1 Network Configuration File: /u01/app/12.1.0/grid/network/admin/tnsnames.ora.ocmnode1
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode-scan)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

ORCL1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode1-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

ORCL2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode2-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )


LISTENER_ORCL1=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode1-vip)(PORT = 1521)))
LISTENER_ORCL2=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode2-vip)(PORT = 1521)))


LISTENER_DRORCL1_OCMDRNODE1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmdrnode1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.112)(PORT=1521)(IP=FIRST))))
LISTENER_DRORCL2_OCMDRNODE2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmdrnode2-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.113)(PORT=1521)(IP=FIRST))))

DRORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode-scan)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DRORCL)
    )
  )

DRORCL1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode1-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DRORCL)
      (SID = ORCL1)
    )
  )

DRORCL2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode2-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DRORCL)
      (SID = ORCL2)
    )
  )
# From Primary Database Node, check tnsping of standby database and VS. 
PRIMARY NODE $ tnsping drorcl
STANDBY NODE $ tnsping orcl 

5.5. Create SPFILE from PFile:

SQL> create spfile='+DATA/DRORCL/PARAMETERFILE/spfileorcl.ora' from  pfile='/u01/app/oracle/product/12.1.0/dbhome_2/dbs/pfile_standby.ora';

$ srvctl modify database –d ORCL –p +DATA/DRORCL/PARAMETERFILE/spfileorcl.ora

5.6. Add Standby database to OCR:

[oracle@ocmdrnode1 dbs]$ srvctl add database -d ORCL -o /u01/app/oracle/product/12.1.0/dbhome_2  -p +DATA/DRORCL/PARAMETERFILE/spfileorcl.ora -r physical_standby -s MOUNT -t immediate -c RAC -a OCR,DATA,FRA

[oracle@ocmdrnode1 dbs]$ srvctl add instance -d ORCL -i ORCL1 -n ocmdrnode1
[oracle@ocmdrnode1 dbs]$ srvctl add instance -d ORCL -i ORCL2 -n ocmdrnode2

[oracle@ocmdrnode1 dbs]$ srvctl config database -d orcl
Database unique name: ORCL
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_2
Oracle user: oracle
Spfile: +DATA/DRORCL/PARAMETERFILE/spfileorcl.ora
Password file:
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: OCR,DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: ORCL1,ORCL2
Configured nodes: ocmdrnode1,ocmdrnode2
Database is administrator managed
[oracle@ocmdrnode1 admin]$ srvctl start database -d orcl
[oracle@ocmdrnode2 ~]$ srvctl status database -d orcl
Instance ORCL1 is running on node ocmdrnode1
Instance ORCL2 is running on node ocmdrnode2

SQL> SELECT DB_UNIQUE_NAME, NAME, OPEN_MODE, PROTECTION_MODE, DATABASE_ROLE FROM V$DATABASE;

DB_UNIQUE_NAME                 NAME      OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
------------------------------ --------- -------------------- -------------------- ----------------
DRORCL                         ORCL      MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY

5.7. Start Managed Recovery Process on Standby:

# Start MRP process 
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

# Cancel / Stop MRP Process
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Validate Replication between Primary and Standby:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> SELECT sequence#, first_time, next_time, APPLIED  from V$ARCHIVED_LOG order by sequence#;

 SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED
---------- -------------------- -------------------- ---------
        25 16-AUG-2020 17:25:58 16-AUG-2020 17:26:49 YES
        26 16-AUG-2020 17:26:49 17-AUG-2020 08:30:32 YES
        27 17-AUG-2020 08:30:32 17-AUG-2020 15:31:01 YES
        28 17-AUG-2020 15:31:01 17-AUG-2020 18:59:41 YES
        29 17-AUG-2020 18:59:41 17-AUG-2020 20:10:54 YES
        30 17-AUG-2020 20:10:54 17-AUG-2020 20:53:20 IN-MEMORY
        50 16-AUG-2020 17:25:57 16-AUG-2020 17:26:49 YES
        51 16-AUG-2020 17:26:49 17-AUG-2020 08:30:32 YES
        52 17-AUG-2020 08:30:32 17-AUG-2020 08:30:35 YES
        53 17-AUG-2020 08:30:35 17-AUG-2020 15:23:19 YES
        54 17-AUG-2020 15:23:19 17-AUG-2020 16:26:33 YES

 SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED
---------- -------------------- -------------------- ---------
        55 17-AUG-2020 16:26:33 17-AUG-2020 17:00:51 YES
        56 17-AUG-2020 17:00:51 17-AUG-2020 18:59:41 YES
        57 17-AUG-2020 18:59:41 17-AUG-2020 18:59:46 YES
        58 17-AUG-2020 18:59:46 17-AUG-2020 20:10:52 YES
        59 17-AUG-2020 20:10:52 17-AUG-2020 20:46:52 YES
        60 17-AUG-2020 20:46:52 17-AUG-2020 20:50:58 YES
        61 17-AUG-2020 20:50:58 17-AUG-2020 20:53:16 YES

18 rows selected.

SQL> SELECT   a.thread#,  b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq   ARC_DIFF FROM (SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,           (SELECT  thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

   THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
---------- ---------- ----------- -------------------- ----------
         1         61          61 17-AUG-2020 20:53:16          0
         2         30          29 17-AUG-2020 20:10:54          1
SQL> set linesize 1000
SQL> SELECT timestamp, gvi.thread#, message FROM gv$dataguard_status gvds, gv$instance gvi WHERE gvds.inst_id = gvi.inst_id AND severity in ('Error','Fatal') ORDER BY timestamp, thread#;

TIMESTAMP               THREAD# MESSAGE
-------------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
17-AUG-2020 20:11:18          1 Error 1017 received logging on to the standby
17-AUG-2020 20:11:18          1 FAL[client, ARC0]: Error 16191 connecting to ORCL for fetching gap sequence
17-AUG-2020 20:30:52          1 Error 1017 received logging on to the standby
17-AUG-2020 20:30:52          1 FAL[client, MRP0]: Error 16191 connecting to ORCL for fetching gap sequence
17-AUG-2020 20:31:02          1 Error 1017 received logging on to the standby
17-AUG-2020 20:31:02          1 FAL[client, MRP0]: Error 16191 connecting to ORCL for fetching gap sequence
17-AUG-2020 20:31:16          1 Error 1017 received logging on to the standby
17-AUG-2020 20:31:16          1 FAL[client, MRP0]: Error 16191 connecting to ORCL for fetching gap sequence
17-AUG-2020 20:31:30          1 Error 1017 received logging on to the standby
17-AUG-2020 20:31:30          1 FAL[client, MRP0]: Error 16191 connecting to ORCL for fetching gap sequence
17-AUG-2020 20:31:44          1 Error 1017 received logging on to the standby

TIMESTAMP               THREAD# MESSAGE
-------------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
17-AUG-2020 20:31:44          1 FAL[client, MRP0]: Error 16191 connecting to ORCL for fetching gap sequence
17-AUG-2020 20:31:59          1 Error 1017 received logging on to the standby
17-AUG-2020 20:31:59          1 FAL[client, MRP0]: Error 16191 connecting to ORCL for fetching gap sequence

14 rows selected.

Start the Active Data Guard:

Issue 1:

ORA-01017: invalid username/password; logon denied
Error 1017 received logging on to the standby

Check that the primary and standby are using a password file and remote_login_passwordfile is set to SHARED or EXCLUSIVE, and that the SYS password is same in the password files. returning error ORA-16191

FAL[client, MRP0]: Error 16191 connecting to ORCL for fetching gap sequence
Mon Aug 17 15:19:39 2020
Errors in file /u01/app/oracle/diag/rdbms/drorcl/ORCL1/trace/ORCL1_mrp0_7054.trc: ORA-16191: Primary log shipping client not logged on standby Mon
Aug 17 15:19:51 2020
Errors in file /u01/app/oracle/diag/rdbms/drorcl/ORCL1/trace/ORCL1_mrp0_7054.trc: ORA-01017: invalid username/password; logon denied

Solution: Follow the link: Create password file on Standby ASM

Leave a Reply