Dataguard Set up in oracle 12c RAC (RAC to RAC) ~ Oracle DBA Secrets

Above are the environment details for two Node RAC Dataguard setups.

Prerequisite:

PRIMARY DB :(Node1,Node3)

Archivelog mode

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 214

Next log sequence to archive 215

Current log sequence 215

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 the archive log is not enabled. Follow the below steps to change the archive log mode .

[oracle@Node1 ~]srvctl status database -d ITMS

[oracle@Node1 ~]srvctl stop database -d ITMS

[oracle@Node1 ~]srvctl start database -d ITMS -o mount

[oracle@Node1 ~]$ . oraenv

ORACLE_SID = [ITMS1] ?

[oracle@Node1 ~]sqlplus / as sysdba

SQL> alter database archivelog;

[oracle@Node1 ~]srvctl stop database -d ITMS

[oracle@Node1 ~]srvctl start database -d ITMS

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

Configure Standby Redo Log (SRL) files for both the Instance

Min No of SRL= no of threads *(Group in threads +1)

Use the command to check the Number of Redo Groups & threads on the Primary Database.

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 SRLs will be

SRL= 2*(4+1)=10

Note: 1-We must consider having a single member in each group so to avoid waits with a commit for each transaction in each member.

2-No need to create standby redo log files on standby and Oracle take care of it during RMAN duplicate.

Add standby redo log 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 ;

Use the command to check the Number of Standby Redo Groups & threads that are added to the Primary Database.

SQL> select group#,thread#,bytes from v$standby_log;

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

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=’*’;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ITMS,ITMSDR)' sid='*';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ITMS' scope= both sid='*';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ITMSDR async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ITMSDR' scope=both sid='*';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE sid='*';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='ITMS_%t_%s_%r.arc' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET FAL_SERVER='ITMSDR' SCOPE=BOTH sid='*';

System altered.

SQL> ALTER SYSTEM SET FAL_CLIENT='ITMS' SCOPE=BOTH sid='*';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=10 sid='*';

System altered.

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.

Restart the database to make the Changes Effective

[oracle@Node1 ~]$ srvctl stop database -d ITMS

[oracle@Node1 ~]$ srvctl start database -d ITMS

Now, verify all the required values have the appropriate values.

set lines 999 pages 999

col value for a110

col name for a50

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','fal_client','standby_file_management');

NAME VALUE

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

log_archive_dest_1 LOCATION=+FRA

log_archive_dest_2 SERVICE=ITMSDR NOAFFIRM ASYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ITMSDR

log_archive_dest_state_1 enable

log_archive_dest_state_2 ENABLE

fal_client ITMS

fal_server ITMSDR

log_archive_config DG_CONFIG=(ITMS,ITMSDR)

log_archive_format ITMS_%t_%s_%r.arc

log_archive_max_processes 10

standby_file_management AUTO

remote_login_passwordfile EXCLUSIVE

db_name ITMS

db_unique_name ITMS

Create an Initialization Parameter file for the Standby database

SQL> create pfile='/tmp/inititmsdr.ora' from spfile ;

Copy the Password File from Primary to Standby

To get the details of the Password file we will check the database configuration.

[oracle@Node1 ~]$ srvctl config database -d ITMS

Database unique name: ITMS

Database name: ITMS

Oracle home: /u01/app/oracle/dbhome

Oracle user: oracle

Spfile: +DATA/ITMS/PARAMETERFILE/spfile.283.1013156439

Password file: +DATA/ITMS/PASSWORD/pwditms.270.1013155873

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA,REDOLOG,REDOLOG02,FRA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: oinstall

Database instances: ITMS1,ITMS2

Configured nodes: Node1,Node3

Database is administrator managed

get the password file location and copy it to the target

[oracle@Node1 ~] su - grid

[grid@Node1 ~]$ . oraenv

ORACLE_SID = [+ASM1] ?

[grid@Node1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwget --dbuniquename ITMS

+DATA/ITMS/PASSWORD/pwditms.270.1013155873

ASMCMD> pwcopy +DATA/ITMS/PASSWORD/pwditms.270.1013155873 /home/grid/orapwditmsdr

copying +DATA/ITMS/PASSWORD/pwditms.270.1013155873 -> /home/grid/orapwditmsdr

Copy the Parameter File & Password File to Standby Node2

[oracle@Node1 ~]$ scp /tmp/inititmsdr.ora root@Node2:/home/oracle/backup

root@Node2's password:

inititmsdr.ora 100% 2277 772.5KB/s 00:00

[oracle@Node1~]$ scp /home/grid/orapwditmsdr root@Node2:/home/oracle/backup

root@Node2's password:

orapwditmsdr 100% 7680 3.0MB/s 00:00

Add TNS entries for Standby Database in Primary & Vice Verse

Add the tns entries of both the Primary and Standby Database in the tnsnames.ora file of both the Primary and Standby Database.

============

Primary DB TNS :

==========

ITMS =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.174)(PORT = 1524))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.175)(PORT = 1524))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ITMS)

)

ITMS1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.174)(PORT = 1524))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ITMS)

(INSTANCE_NAME= ITMS1)

)

ITMS2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.175)(PORT = 1524))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ITMS)

(INSTANCE_NAME= ITMS2)

)

=============

Standby DB TNS :

============

ITMSDR =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.80.17)(PORT = 1524))

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.80.18)(PORT = 1524))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ITMSDR)

)

ITMSDR1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.17)(PORT = 1524))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ITMSDR)(UR=A)

(INSTANCE_NAME=ITMSDR1)

)

ITMSDR2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.18)(PORT = 1524))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ITMSDR)(UR=A)

(INSTANCE_NAME=ITMSDR2)

)

We have done the Modification of Parameters in the Primary Database.

Now the Changes will be done in the Standby Database.

STANDBY DB :(Node2,Node4)

Static Entry of Listener

Add Static Entry of Listener at both nodes in Standby Database (Node2,Node4)

LISTENER_Node2:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ITMSDR)

(ORACLE_HOME = /u01/app/oracle/dbhome)

(SID_NAME = ITMSDR1)

)

LISTENER_Node4:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ITMSDR)

(ORACLE_HOME = /u01/app/oracle/dbhome)

(SID_NAME = ITMSDR2)

)

Create Required Directories(PATH) for the Standby Instances

Nodes2

[oracle@Node2 ]$ cd $ORACLE_BASE/diag/rdbms/

[oracle@Node2 rdbms]$ mkdir -p primdr/ITMSDR1/trace

[oracle@Node2 rdbms]$ mkdir -p primdr/ITMSDR1/cdump

[oracle@Node2 diag]$ cd ../admin/

[oracle@Node2 admin]$ mkdir -p ITMSDR/adump

Node4

[oracle@Node4~]$ cd $ORACLE_BASE/diag/rdbms/

[oracle@Node4 rdbms]$ mkdir -p primdr/ITMSDR2/trace

[oracle@Node4 rdbms]$ mkdir -p primdr/ITMSDR2/cdump

[oracle@Node2 rdbms]$ cd ../../admin/

[oracle@Node2 admin]$ mkdir -p ITMSDR/adump

Create the ASM Directories

ASMCMD> mkdir data/itmsdr

ASMCMD> cd data/itmsdr

ASMCMD> mkdir PARAMETERFILE DATAFILE CONTROLFILE TEMPFILE ONLINELOG

Modify the Pfile that we had copied from Primary Database

vi /home/oracle/inititmsdr.ora

*.audit_file_dest='/u01/app/oracle/admin/ITMSDR/adump'
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='12.1.0.2.0'
*.control_files='+DATA/ITMSDR/control01.ctl','+DATA/ITMSDR/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ITMS'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=161061273600
*.db_unique_name='ITMSDR'
*.diagnostic_dest='/apps/oracle/121'
*.fal_client='ITMSDR'
*.fal_server='ITMS'
ITMSDR1.instance_number=1
ITMSDR2.instance_number=2
*.log_archive_config='DG_CONFIG=(ITMSDR,ITMS)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ITMSDR'
*.log_archive_dest_2='SERVICE=ITMS VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ITMS'
*.log_archive_format='ITMS_%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'
ITMSDR2.thread=2
ITMSDR1.thread=1
ITMSDR1.undo_tablespace='UNDOTBS1'
ITMSDR2.undo_tablespace='UNDOTBS2'

Passwordfile

Copy the Passwordfile that we had transferred from the Primary Database to the $ORACLE_HOME/dbs of both the Standby Nodes and Rename it on Both Nodes.

[oracle@Node2 ~]$ cp orapwdITMS /u01/app/oracle/dbhome/dbs/orapwITMSDR1

[oracle@Node2 ~]$ scp orapwdITMS oracle@Node4:/u01/app/oracle/dbhome/dbs/orapwITMSDR2

Restore the Database Backup on Standby Node

export ORACLE_SID=ITMSDR1

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/inititmsdr.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@ITMS auxiliary sys/passwd@ITMSDR1

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jun 15 03:32:22 2020

connected to target database: ITMS (DBID=xxxxxxxxxx)

connected to auxiliary database: ITMS (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

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

ITMS 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 215

This completes the copying of data from the primary database to the standby database. However, since we know that the standby database is created from a single instance, the settings file and the password file are stored in the local file system. So in the next step, we will copy the settings file and the password file to the ASM shared file system. And then will bring both the instances 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 215

SQL> show parameter pfile

NAME TYPE VALUE

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

spfile string

SQL> create spfile='+DATA' from pfile='/home/oracle/inititmsdr.ora';

File created.

ASMCMD> cd ITMSDR/

ASMCMD> ls

CONTROLFILE/

DATAFILE/

PARAMETERFILE/

ASMCMD> cd PARAMETERFILE/

ASMCMD> ls

spfile.277.123456789

ASMCMD> pwd

+DATA/ITMSDR/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/initITMSDR1.ora

spfile='+DATA/ITMSDR/PARAMETERFILE/spfile.277.1230456789

[oracle@Node4~]$ vi /u01/app/oracle/dbhome/dbs/initITMSDR2.ora

spfile='+DATA/ITMSDR/PARAMETERFILE/spfile.277.1230456789

ASMCMD> pwcopy /u01/app/oracle/dbhome/dbs/orapwITMSDR1

+DATA/ITMSDR/orapwitmsdr

copying /u01/app/oracle/dbhome/dbs/orapwITMSDR1 -> +DATA/ITMSDR/orapwitmsdr

Add the Standby DB 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 ITMSDR -n ITMS -o /u01/app/oracle/dbhome -r PHYSICAL_STANDBY -startoption MOUNT -diskgroup DATA,FRA -pwfile +DATA/ITMSDR/orapwitmsdr

[oracle@Node2 ~]$ srvctl add instance -d ITMSDR -i ITMSDR1 -n Node2

[oracle@Node2 ~]$ srvctl add instance -d ITMSDR -i ITMSDR2 -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 ITMSDR

[oracle@Node2 ~]$ srvctl status database -d ITMSDR

Instance ITMSDR1 is running on node Node2

Instance ITMSDR2 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

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

ITMS MOUNTED PHYSICAL STANDBY ITMSDR1 MOUNTED

ITMS MOUNTED PHYSICAL STANDBY ITMSDR2 MOUNTED

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 346

2 245

At Standby :

SQL> select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;

THREAD# MAX(SEQUENCE#)

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

1 346

2 245

So we have successfully configured the Dataguard for our ORACLE RAC 12c.

Reference Doc Id: MAA - 10g Creating a RAC Physical Standby for a RAC Primary (Doc ID 380449.1)

Creating a 10gr2 Data Guard Physical Standby database with Real-Time apply (Doc ID 343424.1)

Oracle DBA Secrets

Sunday, March 31, 2024