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:
A 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.
- Overview of Primary and Standby DB Settings.
- Prerequisites.
- Perform Prerequisites on Primary Database.
- Perform Prerequisites on Standby Database.
- Create Physical Standby Data Guard with RAC.
- Configure Data Guard Broker
- Perform Switchover/Failover using DG Broker and SQL Command
1. Overview of Primary and Standby DB Settings:
| PRIMARY | STANDBY | |
|---|---|---|
| Site Location | Chicago | Atlanta |
| Cluster Node | ocmnode1, ocmnode2 | ocmdrnode1, ocmdrnode2 |
| SCAN | ocmnode-scan | ocmdrnode-scan |
| VIPS | ocmnode1-vip ocmnode2-vip | ocmdrnode1-vip ocmdrnode2-vip |
| DB_UNIQUE_NAME | ORCLPROD | DRORCL |
| DB_NAME | ORCL | ORCL |
| DB Storage | ASM | ASM |
| File Management | OMF | OMF |
| ASM Diskgroup | OCR, DATA, FRA | OCR, DATA, FRA |
| OS | Linux 6.5 | Linux 6.5 |
| DB & GRID Version | 12.1.0.2 | 12.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
