ashusatapathy
- Feb 16, 2021
- 7 min read

Step By Step Dataguard Set up in oracle 12c RAC (RAC to RAC)

Updated: Apr 30, 2022

#dataguard #12c #Oracle #RAC

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

---------------------------------------

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

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

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

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 :)

Step By Step Dataguard Set up in oracle 12c RAC (RAC to RAC)

Recent Posts