Step By Step Dataguard Set up in oracle 12c RAC (RAC to RAC)
Updated: Apr 30, 2022
Primary Standby
Hostname: Node1,Node3 Node2,Node4
DB Name : Prim Prim
DB Unique name: Prim Primdr
Instance Name: Prim1,Prim2 Primdr1,Primdr2
Storage : ASM ASM
OS : RHEL 7.2 RHEL7.2
DB Version : 12.1.0.2 12.1.0.2
Prerequisite:
PRIMARY DB :(Node1,Node3)
1-Enable archivelog mode:
Please check if the db is in archive log is enabled :
SQL> archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 294
Next log sequence to archive 295
Current log sequence 295
SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 100G
If archive log is not enabled .Follow the below steps to change the archive log mode .
[oracle@Node1 ~]srvctl status database -d PRIM
[oracle@Node1 ~]srvctl stop database -d PRIM
[oracle@Node1 ~]srvctl start database -d PRIM -o mount
[oracle@Node1 ~] oraenv : PRIM1
[oracle@Node1 ~]sqlplus “/as sysdba”
SQL> alter database archivelog;
[oracle@Node1 ~]srvctl stop database -d PRIM
[oracle@Node1 ~]srvctl start database -d PRIM 2-Enable force logging mode
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
3- Configure Standby Redo Log (SRL) files for both the instance :
Min No of SRL= no of threads *(Group in threads +1)SQL> set lines 200 pages 200
SQL> col MEMBER for a55
SQL> select group#,thread#,members,status,bytes/1024/1024 from v$log;
In our environment we have 4 groups with 2 threads .So here number of SRL will be
SRL= 2*(4+1)=10
Note: 1-We must consider having single member in each group so to avoid waits with commit for each transaction in each member.
2-No need to create standby redo log files on standby and Oracle take cares of it during RMAN duplicate.Add standby redolog file :
alter database add standby logfile thread 1
group 9 ('+REDOLOG','+REDOLOG02') size 2048M,
group 10 ('+REDOLOG','+REDOLOG02') size 2048M,
group 11 ('+REDOLOG','+REDOLOG02') size 2048M,
group 12 ('+REDOLOG','+REDOLOG02') size 2048M,
group 13 ('+REDOLOG','+REDOLOG02') size 2048M;
alter database add standby logfile thread 2
group 14 ('+REDOLOG','+REDOLOG02') size 2048M,
group 15 ('+REDOLOG','+REDOLOG02') size 2048M,
group 16 ('+REDOLOG','+REDOLOG02') size 2048M,
group 17 ('+REDOLOG','+REDOLOG02') size 2048M,
group 18 ('+REDOLOG','+REDOLOG02') size 2048M ;
SQL> select group#,thread#,bytes from v$standby_log;
4-Configure “standby_file_management” parameter
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL> alter system set standby_file_management = 'AUTO' scope=spfile sid=’*’;
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
5- Configure “remote_login_passwordfile” parameter
SQL> show parameter remote_login_passwordfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
If not set to exclusive :
SQL> alter system set remote_login_passwordfile = 'EXCLUSIVE' scope = spfile sid=’*’;6- Now set the below parameters for log transport :
SQL> alter system set log_archive_config='DG_CONFIG=(PRIM,PRIMDR)' scope=both sid='*';
SQL> alter system set log_archive_dest_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIM' scope= both sid='*';
SQL> alter system set log_archive_dest_2='SERVICE=PRIMDR async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMDR' scope=both sid='*';
SQL> alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile sid='*';
SQL> alter system set fal_server ='PRIMDR' scope=both sid='*';
SQL> alter system set fal_client ='PRIM' scope=both sid='*';
Note:Set the FAL_SERVER parameter with the “NET ALIAS NAME” of the standby database. This parameter will be used only when a switchover occurs and the primary starts behaving in the standby role.SQL>alter system set LOG_ARCHIVE_MAX_PROCESSES=8 scope=both sid='*';
SQL>alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE ;
7- Restart the database to make the changes effective :
[oracle@Node1 ~]$ srvctl stop database -d PRIM
[oracle@Node1 ~]$ srvctl start database -d PRIM
8- Create initialization parameter file for Standby database
SQL> create pfile='/tmp/initprimdr.ora' from spfile ;
9- Copy the password file from Primary to Standby
To get the details of password file we will check the database configuration .
[oracle@Node1 ~]$ srvctl config database -d PRIM
Database unique name: PRIM
Database name: PRIM
Oracle home: /u01/app/oracle/dbhome
Oracle user: oracle
Spfile: +DATA/PRIM/PARAMETERFILE/spfile.283.1013156439
Password file: +DATA/PRIM/PASSWORD/pwdprim.270.1013155873
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,REDOLOG,REDOLOG02
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oinstall
Database instances: PRIM1,PRIM2
Configured nodes: Node1,Node2
Database is administrator managed
get the password file location and copy it to target
[oracle@Node1 ~] su - grid [grid@Node1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ?
[grid@Node1 ~]$ asmcmd
ASMCMD> ASMCMD> pwget --dbuniquename PRIM
+DATA/PRIM/PASSWORD/pwdprim.270.1013155873
ASMCMD> pwcopy +DATA/PRIM/PASSWORD/pwdprim.270.1013155873 /home/grid/orapwdPRIM
copying +DATA/PRIM/PASSWORD/pwdprim.270.1013155873 -> /home/grid/orapwdPRIM
10-copy the parameter file (from Step:8) & password file(from Step:9) to standby Node2
[oracle@Node1 ~]$ scp /tmp/initprimdr.ora root@Node2:/home/oracle/backup
root@Node2's password:
initprimdr.ora 100% 2277 772.5KB/s 00:00
[oracle@Node1~]$ scp /home/grid/orapwdPRIM root@Node2:/home/oracle/backup
root@Node2's password:
orapwdPRIM 100% 7680 3.0MB/s 00:00
11- Add standby node database TNS entries in primary & vice verse
Add the tns entries of both primary and standby node in the tnsnames.ora file of both the primary and standby database .
=============
Standby DB TNS :
============
PRIMDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =Node2_vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =Node4_vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIMDR)
)
)
PRIMDR1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Node2_vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIMDR)(UR=A)
(INSTANCE_NAME=PRIMDR1)
)
)
PRIMDR2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Node4_vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIMDR)(UR=A)
(INSTANCE_NAME=PRIMDR2)
)
)
============
Primary DB TNS :
==========
PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Node1_vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = Node3_vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM)
)
)
PRIM1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Node1_vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM)
(INSTANCE_NAME= PRIM1)
)
)
PRIM2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Node3_vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM)
(INSTANCE_NAME= PRIM2)
)
This Completes the Modification of parameters at the primary database .
Now the changes will be done in the standby database .
STANDBY DB :
1- Add static entry of listener at both nodes in standby (Node2,Node4)
Node2:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PRIMDR)
(ORACLE_HOME = /u01/app/oracle/dbHome)
(SID_NAME = PRIMDR1)
)
)
Node4:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PRIMDR)
(ORACLE_HOME = /u01/app/oracle/dbHome)
(SID_NAME = PRIMDR2)
)
)
2-Create required directories for the Standby Instances
Nodes2
[oracle@Node2 ]$ cd $ORACLE_BASE/diag/rdbms/
[oracle@Node2 rdbms]$ mkdir -p primdr/PRIMDR1/trace
[oracle@Node2 rdbms]$ mkdir -p primdr/PRIMDR1/cdump
[oracle@Node2 diag]$ cd ../admin/
[oracle@Node2 admin]$ mkdir -p PRIMDR/adump
Node4
[oracle@Node4~]$ cd $ORACLE_BASE/diag/rdbms/
[oracle@Node4 rdbms]$ mkdir -p primdr/PRIMDR2/trace
[oracle@Node4 rdbms]$ mkdir -p primdr/PRIMDR2/cdump
[oracle@Node2 rdbms]$ cd ../../admin/
[oracle@Node2 admin]$ mkdir -p PRIMDR/adump
3- Create the ASM directories
ASMCMD> mkdir data/primdr
ASMCMD> cd data/primdr
ASMCMD> mkdir PARAMETERFILE DATAFILE CONTROLFILE TEMPFILE ONLINELOG
4- Modify the pfile that we had copied from primary
vi /home/oracle/initprimdr.ora
*.audit_file_dest='/u01/app/oracle/admin/PRIMDR/adump'
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='12.1.0.2.0'
*.control_files='+DATA/PRIMDR/control01.ctl','+DATA/PRIMDR/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PRIM'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=161061273600
*.db_unique_name='PRIMDR'
*.diagnostic_dest='/apps/oracle/121'
*.fal_client='PRIMDR'
*.fal_server='PRIM'
PRIMDR1.instance_number=1
PRIMDR2.instance_number=2
*.log_archive_config='DG_CONFIG=(PRIMDR,PRIM)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMDR'
*.log_archive_dest_2='SERVICE=PRIM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIM'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=0
*.open_cursors=1000
*.pga_aggregate_target=64424509440
*.processes=512
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=256
*.sga_max_size=161061273600
*.sga_target=161061273600
*.shared_servers=0
*.standby_file_management='AUTO'
PRIMDR2.thread=2
PRIMDR1.thread=1
PRIMDR1.undo_tablespace='UNDOTBS1'
PRIMDR2.undo_tablespace='UNDOTBS2'5-copy the passwordfile from that we had transferred from primary [Step 10]to the $ORACLE_HOME/dbs of both the standbynode and rename it :
[oracle@Node2 ~]$ cp orapwdPRIM /u01/app/oracle/dbHome/dbs/orapwPRIMDR1
[oracle@Node2 ~]$ scp orapwdPRIM oracle@Node4:/u01/app/oracle/dbHome/dbs/orapwPRIMDR2
6- Now set the environment restore the backup
export ORACLE_SID=PRIMDR1
export ORACLE_HOME=/u01/app/oracle/dbHome
export ORACLE_BASE=/u01/app/oracle
Now we will create the physical standby database from the primary database using RMAN DUPLICATE Database :
[oracle@Node2 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 15 03:21:33 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=' /home/oracle/initprimdr.ora' ;
ORACLE instance started.
Total System Global Area 1.0737E+11 bytes
Fixed Size 5298856 bytes
Variable Size 3.2481E+10 bytes
Database Buffers 7.4625E+10 bytes
Redo Buffers 263135232 bytes
[oracle@Node2 ~]$ rman target sys/passwd@PRIM auxiliary sys/passwd@PRIMDR1
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jun 15 03:32:22 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIM (DBID=xxxxxxxxxx)
connected to auxiliary database: PRIM (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;
..........................................................................
Finished Duplicate Db at 15-JUN-20
RMAN> alter system register;
Statement processed
RMAN> exit
Recovery Manager complete.
[oracle@Node2 ~]$ sqlplus "/as sysdba"
SQL> select open_mode, database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY
SQL> select name,open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
PRIM MOUNTED PHYSICAL STANDBY
SQL> select group#,thread#,bytes from v$standby_log;
GROUP# THREAD# BYTES
---------- ---------- ----------
9 1 2147483648
10 1 2147483648
11 1 2147483648
12 1 2147483648
13 1 2147483648
14 2 2147483648
15 2 2147483648
16 2 2147483648
17 2 2147483648
18 2 2147483648
10 rows selected.
SQL> select THREAD#, max(SEQUENCE#) from v$log_history group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 304
2 227
7-
This completes the data copy from primary to standby database .But as we know the standby database was created from only one instance , the parameter file & password file was stored at the local file system .
So in next step we will copy the parameter file and password file to the shared file system i.e to ASM .And then will bring both the instance of standby database up and running .
===============
Parameter file copy
===============
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select THREAD#, max(SEQUENCE#) from v$log_history group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 304
2 227
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> create spfile='+DATA' from pfile='/home/oracle/initprimdr.ora';
File created.
ASMCMD> cd PRIMDR/
ASMCMD> ls
CONTROLFILE/
DATAFILE/
PARAMETERFILE/
ASMCMD> cd PARAMETERFILE/
ASMCMD> ls
spfile.277.123456789
ASMCMD> pwd
+DATA/PRIMDR/PARAMETERFILE
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> shu abort ;
ORACLE instance shut down.
Now go to the DBS location of both the nodes and add the spfile location :
[oracle@Node2 ~]$ vi /u01/app/oracle/dbHome/dbs/initPRIMDR1.ora
spfile='+DATA/PRIMDR/PARAMETERFILE/spfile.277.1230456789
[oracle@Node4~]$ vi /u01/app/oracle/dbHome/dbs/initPRIMDR2.ora
spfile='+DATA/PRIMDR/PARAMETERFILE/spfile.277.1230456789
=================
Passwdfile copy
=================
ASMCMD> pwcopy /u01/app/oracle/dbHome/dbs/orapwPRIMDR1
+DATA/PRIMDR/orapwprimdr
copying /u01/app/oracle/dbHome/dbs/orapwPRIMDR1 -> +DATA/PRIMDR/orapwprimdr
8- Add the standby db and instance to grid service
As the standby database is in RAC ,we will register/add the database and instance services to the Cluster .
[oracle@Node2 ~]$ srvctl add database -d PRIMDR -n PRIM -o /u01/app/oracle/dbHome -r PHYSICAL_STANDBY -startoption MOUNT -diskgroup DATA,FRA -pwfile +DATA/PRIMDR/orapwprimdr
[oracle@Node2 ~]$ srvctl add instance -d PRIMDR -i PRIMDR1 -n Node2
[oracle@Node2 ~]$ srvctl add instance -d PRIMDR -i PRIMDR2 -n Node4
[oracle@Node2 dbs]$ sqlplus "/as sysdba"
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options
SQL> shu abort ;
ORACLE instance shut down.
[oracle@Node2 ~]$ srvctl start database -d PRIMDR
[oracle@Node2 ~]$ srvctl status database -d PRIMDR
Instance PRIMDR1 is running on node Node2
Instance PRIMDR2 is running on node Node4
[oracle@Node2 dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL> select name,open_mode,database_role ,instance_name,status from gv$database,gv$instance ;
NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME STATUS
--------- -------------------- ---------------- ---------------- ------------
PRIM MOUNTED PHYSICAL STANDBY PRIMDR1 MOUNTED
PRIM MOUNTED PHYSICAL STANDBY PRIMDR2 MOUNTED
9- Now will verify if log switch is working or not .
Primary:
SQL> alter system switch logfile ;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select THREAD#, max(SEQUENCE#) from v$log_history group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 316
2 212
At standby :
SQL> select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 316
2 211
So we have successfully configured dataguard for our ORACLE RAC 12c.
Happy learning :)