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

Updated: Apr 30

#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

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

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



3,279 views0 comments

Recent Posts

See All

#oracle #ASM #controlfile #multiplexing #oracle19c It is a best practice for any database to have controlfile as well as redolog file to be multiplexed . This helps during any disk failure to keep one

In the previous post we saw how to upgrade OMS from 13.2 to 13.4 version .(Click Here) Once OMS is upgraded we have to upgrade the agents running in the servers which are monitored by OEM . This whole