How to configure the 2 Node RAC Dataguard in Oracle 11gR2(11.2.0.4)
Posted by Mir Sayeed Hassan on May 7th, 2020
How to configure the 2 Node RAC Dataguard in Oracle 11gR2(11.2.0.4)
I’m going for step by step configuration for the 2 Node RAC Dataguard/Standby in Oracle Linux.
Let us take the below environment in this example:
Note:
Prior to this configuration, need to installed the Grid & Oracle Software Only on Standby DB.
Assign the Public, Private, Virtual and Scan IP Address on /etc/hosts on Primary DB (Node 1 & 2)
[oracle@pritestn1 ~]$ cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 #PUBLIC 192.168.116.56 pritestn1 192.168.116.57 pritestn2 #VIP 192.168.116.65 pritestn1-vip 192.168.116.64 pritestn2-vip #PRIVATE 10.20.30.56 pritestn1-prv 10.20.30.57 pritestn2-prv #SCAN 192.168.116.67 pritest-cluster-scan 192.168.116.68 pritest-cluster-scan 192.168.116.71 pritest-cluster-scan
Assign the Public, Private, Virtual and Scan IP Address on /etc/hosts on Standby DB (Node 1 & 2)
[oracle@stbytestn1 ~]$ cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 #PUBLIC 192.168.116.58 stbytestn1 192.168.116.59 stbytestn2 #VIP 192.168.116.75 stbytestn1-vip 192.168.116.76 stbytestn2-vip #PRIVATE 20.30.40.58 stbytestn1-prv 20.30.40.59 stbytestn2-prv #SCAN 192.168.116.72 stbytest-cluster-scan 192.168.116.73 stbytest-cluster-scan 192.168.116.74 stbytest-cluster-scan
Primary RAC Configuration for DG
-
Check DB Unique Name. If not set, Configure it
-
Enable Force Logging.
-
Check the DB in Archive log mode, If not enable, then enable it.
-
Modify mandatory init Parameters.
-
Create the Standby Redo Logs for thread 1&2
-
Copy the pfile of Primary & orapwd file to standby database
-
Update the tnsnames.ora on Primary DB (Node 1&2), Standby DB(Node 1&2)
-
Check the tnsping connectivity from Primary & Standby DB
-
Check the SYS Login Connectivity from the Standby to Primary DB
-
Take a backup of Primary DB using RMAN
Check the DB Unique Name
SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- -------- db_unique_name string testdb
Verify the archive log in DB
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 9 Next log sequence to archive 10 Current log sequence 10
Note: if the archive log mode is not enabled, then enable it by the DB in mount state
SQL> shut immediate; SQL> Startup mount; SQL> alter database archivelog; SQL> alter database open;
Enable the Force Login.
SQL>alter database force logging; Database altered.
Modify mandatory init Parameters.
SQL> alter system set log_archive_config='dg_config=(testdb,testdbstd)' sid='*'; System altered. SQL> alter system set log_archive_dest_2='service=testdbstd async valid_for=(online_logfiles,primary_role) db_unique_name=testdbstd' sid='*'; System altered. SQL> alter system set log_archive_dest_state_2=enable sid='*'; System altered. SQL&> alter system set log_archive_format='testdb_%t_%s_%r.arc' scope=spfile; System altered. SQL> alter system set log_archive_max_processes=2 sid='*'; System altered. SQL> alter system set remote_login_passwordfile=exclusive scope=spfile; System altered. SQL> alter system set fal_server='testdbstd1' sid='*'; System altered. SQL> alter system set fal_client='testdb1' sid='*'; System altered. SQL> alter system set standby_file_management=auto scope=both sid='*'; System altered.
Create the Standby Redo Logs for thread 1&2
Thread 1
SQL> alter database add standby logfile thread 1 group 10 '+DATA' size 50M; Database altered. SQL> alter database add standby logfile thread 1 group 11 '+DATA' size 50M; Database altered. SQL> alter database add standby logfile thread 1 group 12 '+DATA' size 50M; Database altered. SQL> alter database add standby logfile thread 1 group 13 '+DATA' size 50M; Database altered. SQL> alter database add standby logfile thread 1 group 14 '+DATA' size 50M; Database altered.
Thread 2
SQL> alter database add standby logfile thread 2 group 15 '+DATA' size 50M; Database altered. SQL> alter database add standby logfile thread 2 group 16 '+DATA' size 50M; Database altered. SQL> alter database add standby logfile thread 2 group 17 '+DATA' size 50M; Database altered. SQL> alter database add standby logfile thread 2 group 18 '+DATA' size 50M; Database altered. SQL> alter database add standby logfile thread 2 group 19 '+DATA' size 50M; Database altered.
Verify the Standby logfile created.
SQL> select * from v$standby_log; GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVE STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME ------ ---------------------------------------- ---------- ---------- ---------- ---------- ---------- ------- ---------- ------------- --------- ------------ --------- ------------ --------- 10 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED 11 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED 12 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED 13 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED 14 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED 15 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED 16 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED 17 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED 18 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED 19 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED 10 rows selected.
Copy the pfile of Primary & orapwd file to standby database
[oracle@pritestn1 dbs]$ scp inittestdb1.ora oracle@192.168.116.58:/u01/app/oracle/product/11.2.0/db_2/dbs/ oracle@192.168.116.59's password: inittestdb1.ora [oracle@pritestn1 dbs]$ scp orapwtestdb1 oracle@192.168.116.58:/u01/app/oracle/product/11.2.0/db_2/dbs/ oracle@192.168.116.58's password: orapwtestdb1 100% 1536 1.5KB/s 00:00 [oracle@pritestn1 dbs]$ scp orapwtestdb1 oracle@192.168.116.59:/u01/app/oracle/product/11.2.0/db_2/dbs/ oracle@192.168.116.59's password: orapwtestdb1 100% 1536 1.5KB/s 00:00
Note: Make sure rename the Pfile & orapwd file on standby database.
Assign the new entries tnsnames.ora file
[oracle@pritestn1 ~]$ cd /u01/app/oracle/product/11.2.0/db_2/network/admin/ [oracle@pritestn1 admin]$ ls samples shrept.lst tnsnames.ora
[oracle@pritestn1 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_2/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ##PRIMARY NODE 1 TESTDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.116.56)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb) ) ) ##PRIMARY NODE 2 TESTDB2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.116.57)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb) ) ) ##STANDBY NODE 1 TESTDBSTD1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.116.58)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdbstd) ) ) ##STANDBY NODE 2 TESTDBSTD2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.116.59)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdbstd) ) )
Note: All this entries into the Primary DB Node 1&2 , Standby DB Node 1&2.
Check the tnsping connecting between the Primary & Standby DB
Primary DB
[oracle@pritestn1 admin]$ tnsping testdb1 TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-MAY-2020 11:47:21 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.116.56)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb))) OK (10 msec) [oracle@pritestn1 admin]$ tnsping testdb2 TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-MAY-2020 11:47:23 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.116.57)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb))) OK (0 msec) [oracle@pritestn1 admin]$ tnsping testdbstd1 TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-MAY-2020 11:47:26 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.116.58)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb))) OK (0 msec) [oracle@pritestn1 admin]$ tnsping testdbstd2 TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-MAY-2020 11:47:28 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.116.59)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb))) OK (0 msec)
Standby DB
[oracle@stbytestn1 admin]$ tnsping testdb1 TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-MAY-2020 11:42:40 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.116.56)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb))) OK (0 msec) [oracle@stbytestn1 admin]$ tnsping testdb2 TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-MAY-2020 11:42:42 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.116.57)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb))) OK (0 msec) [oracle@stbytestn1 admin]$ tnsping testdbstd1 TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-MAY-2020 11:42:45 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.116.58)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb))) OK (0 msec) [oracle@stbytestn1 admin]$ tnsping testdbstd2 TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-MAY-2020 11:42:46 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.116.59)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb))) OK (0 msec)
Also check the sys login connectivity from standby to Primary DB
[oracle@stbytestn1 ~]$ sqlplus sys/testdb@testdb1 as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu May 7 12:06:43 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> exit [oracle@stbytestn1 ~]$ sqlplus sys/testdb@testdb2 as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu May 7 12:06:39 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> exit
Backup Database using RMAN
[root@pritestn1 /]# mkdir backup [root@pritestn1 /]# chown oracle:oinstall backup [root@pritestn1 /]# chmod 775 backup/ [root@pritestn1 /]# su - oracle
[oracle@pritestn1 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 7 11:29:14 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (DBID=2823018451)
RMAN> run {
backup as compressed backupset database format '/backup/full_%d_%T_%s.bkp';
backup archivelog all format '/backup/arch_%d_%T_%s.arc';
backup current controlfile for standby format '/backup/CTRL4STDBY_%d_%T_%s.bkp';
}
Starting backup at 07-MAY-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 instance=testdb1 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/testdb/datafile/system.256.1039690267
input datafile file number=00002 name=+DATA/testdb/datafile/sysaux.257.1039690267
input datafile file number=00005 name=+DATA/testdb/datafile/example.267.1039690327
input datafile file number=00003 name=+DATA/testdb/datafile/undotbs1.258.1039690269
input datafile file number=00006 name=+DATA/testdb/datafile/undotbs2.268.1039690421
input datafile file number=00004 name=+DATA/testdb/datafile/users.259.1039690269
channel ORA_DISK_1: starting piece 1 at 07-MAY-20
piece handle=/backup/full_TESTDB_20200507_2.bkp tag=TAG20200507T112933 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-MAY-20
channel ORA_DISK_1: finished piece 1 at 07-MAY-20
piece handle=/backup/full_TESTDB_20200507_3.bkp tag=TAG20200507T112933 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-MAY-20
Starting backup at 07-MAY-20
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=2 STAMP=1039690754
input archived log thread=2 sequence=1 RECID=1 STAMP=1039690488
input archived log thread=2 sequence=2 RECID=3 STAMP=1039691680
input archived log thread=1 sequence=6 RECID=6 STAMP=1039718418
input archived log thread=2 sequence=3 RECID=4 STAMP=1039704308
input archived log thread=2 sequence=4 RECID=5 STAMP=1039718417
input archived log thread=2 sequence=5 RECID=7 STAMP=1039730339
input archived log thread=1 sequence=7 RECID=8 STAMP=1039730514
input archived log thread=2 sequence=6 RECID=10 STAMP=1039744577
input archived log thread=1 sequence=8 RECID=9 STAMP=1039735818
input archived log thread=1 sequence=9 RECID=13 STAMP=1039771855
input archived log thread=2 sequence=7 RECID=11 STAMP=1039757894
input archived log thread=2 sequence=8 RECID=12 STAMP=1039771853
input archived log thread=2 sequence=9 RECID=15 STAMP=1039777013
input archived log thread=1 sequence=10 RECID=14 STAMP=1039777011
input archived log thread=1 sequence=11 RECID=16 STAMP=1039777017
input archived log thread=2 sequence=10 RECID=17 STAMP=1039777022
input archived log thread=1 sequence=12 RECID=19 STAMP=1039777038
input archived log thread=2 sequence=11 RECID=18 STAMP=1039777038
input archived log thread=2 sequence=12 RECID=21 STAMP=1039778720
input archived log thread=1 sequence=13 RECID=20 STAMP=1039778719
input archived log thread=1 sequence=14 RECID=22 STAMP=1039778721
input archived log thread=2 sequence=13 RECID=23 STAMP=1039778724
input archived log thread=1 sequence=15 RECID=24 STAMP=1039778726
input archived log thread=2 sequence=14 RECID=26 STAMP=1039779012
input archived log thread=1 sequence=16 RECID=25 STAMP=1039779011
channel ORA_DISK_1: starting piece 1 at 07-MAY-20
channel ORA_DISK_1: finished piece 1 at 07-MAY-20
piece handle=/backup/arch_TESTDB_20200507_4.arc tag=TAG20200507T113016 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 07-MAY-20
Starting backup at 07-MAY-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 07-MAY-20
channel ORA_DISK_1: finished piece 1 at 07-MAY-20
piece handle=/backup/CTRL4STDBY_TESTDB_20200507_5.bkp tag=TAG20200507T113020 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-MAY-20
Verify the backup
[oracle@pritestn1 backup]$ ls arch_TESTDB_20200507_4.arc CTRL4STDBY_TESTDB_20200507_5.bkp full_TESTDB_20200507_2.bkp
Transfer the standby Controlfile backup to the standby database
[oracle@pritestn1 backup]$ scp * oracle@192.168.116.58:/backup oracle@192.168.116.58's password:
On STANDBY CONFIGURATION
-
Create the require directories
-
Modify the pfile for standby configuration
[oracle@stbytestn1 ~]$ mkdir -p /u01/app/oracle/admin/testdb/adump [oracle@stbytestn2 ~]$ mkdir -p /u01/app/oracle/admin/testdb/adump
Modify the pfile on Standby DB
[oracle@stbytestn1 ~]$ cd $ORACLE_HOME/dbs
[oracle@stbytestn1 dbs]$ vi inittestdb1.ora testdb1.__db_cache_size=671088640 testdb2.__db_cache_size=687865856 testdb2.__java_pool_size=16777216 testdb1.__java_pool_size=16777216 testdb2.__large_pool_size=33554432 testdb1.__large_pool_size=33554432 testdb2.__pga_aggregate_target=335544320 testdb1.__pga_aggregate_target=352321536 testdb2.__sga_target=1174405120 testdb1.__sga_target=1157627904 testdb2.__shared_io_pool_size=0 testdb1.__shared_io_pool_size=0 testdb1.__shared_pool_size=419430400 testdb2.__shared_pool_size=419430400 testdb2.__streams_pool_size=0 testdb1.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/testdb/adump' *.audit_trail='db' *.cluster_database=true *.compatible='11.2.0.4.0' *.control_files='+DATA/testdb/controlfile/current.256.1039781509','+DATA/testdb/controlfile/current.257.1039781509' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_name='testdb' *.db_unique_name='testdbstd' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=4621074432 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)' *.fal_client='TESTDBSTD1' *.fal_server='TESTDB1' testdb1.instance_number=1 testdb2.instance_number=2 *.log_archive_config='DG_CONFIG=(TESTDB,TESTDBSTD)' *.log_archive_dest_2='SERVICE=TESTDB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTDB' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='ORCL_%t_%s_%r.arc' *.log_archive_max_processes=2 *.memory_target=1509949440 *.open_cursors=300 *.processes=1500 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=1655 *.standby_file_management='AUTO' testdb2.thread=2 testdb1.thread=1 testdb1.undo_tablespace='UNDOTBS1' testdb2.undo_tablespace='UNDOTBS2' :wq
Now start creating the physical standby database
-
Start the DB in NOMOUNT Mode with pfile
-
Restore the Standby Control file from backup
-
Restore the Standby DB using the RMAN Duplicate.
-
Start the Managed recovery process
-
Create the spfile from pfile & start the DB with SPFILE.
Set the ENV on Standy DB
export ORACLE_SID=testdb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_2
Startup DB NOMOUNT Mode
[oracle@stbytestn1 ~]$ !sq sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu May 7 15:53:09 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 1503199232 bytes Fixed Size 2253424 bytes Variable Size 1342180752 bytes Database Buffers 150994944 bytes Redo Buffers 7770112 bytes
Restore the Standby Controlfile
[oracle@stbytestn1 backup]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 7 12:11:30 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: TESTDB (not mounted) RMAN> restore controlfile from '/backup/CTRL4STDBY_TESTDB_20200507_5.bkp'; Starting restore at 07-MAY-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=33 instance=testdb1 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 output file name=+DATA/testdb/controlfile/current.256.1039781509 output file name=+DATA/testdb/controlfile/current.257.1039781509 Finished restore at 07-MAY-20
Restore DB using the RMAN DUPLICATE.
[oracle@stbytestn1 backup]$ rman target sys/testdb@testdb1 AUXILIARY /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 7 12:12:03 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (DBID=2823018451)
connected to auxiliary database: TESTDB (not mounted)
RMAN> duplicate target database for standby nofilenamecheck;
Starting Duplicate Db at 07-MAY-20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=29 instance=testdb1 device type=DISK
contents of Memory Script:
{
restore clone standby controlfile;
}
executing Memory Script
Starting restore at 07-MAY-20
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /backup/CTRL4STDBY_TESTDB_20200507_5.bkp
channel ORA_AUX_DISK_1: piece handle=/backup/CTRL4STDBY_TESTDB_20200507_5.bkp tag=TAG20200507T113020
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/testdb/controlfile/current.256.1039781509
output file name=+DATA/testdb/controlfile/current.257.1039781509
Finished restore at 07-MAY-20
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter databasemount standby database
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
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 07-MAY-20
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
hannel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /backup/full_TESTDB_20200507_2.bkp
channel ORA_AUX_DISK_1: piece handle=/backup/full_TESTDB_20200507_2.bkp tag=TAG20200507T112933
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 07-MAY-20
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=1039781588 file name=+DATA/testdb/datafile/system.258.1039781543
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=1039781588 file name=+DATA/testdb/datafile/sysaux.259.1039781543
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=1039781588 file name=+DATA/testdb/datafile/undotbs1.261.1039781543
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=1039781588 file name=+DATA/testdb/datafile/users.263.1039781543
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=1039781588 file name=+DATA/testdb/datafile/example.260.1039781543
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=1039781588 file name=+DATA/testdb/datafile/undotbs2.262.1039781543
Finished Duplicate Db at 07-MAY-20
So far the Standby dataset configuration is completed.
Verify the Standby DB
SQL> select status from V$instance; STATUS ------- MOUNTED
SQL> select name from v$datafile;
NAME
——————————————————————————–
+DATA/testdb/datafile/system.258.1039781543
+DATA/testdb/datafile/sysaux.259.1039781543
+DATA/testdb/datafile/undotbs1.261.1039781543
+DATA/testdb/datafile/users.263.1039781543
+DATA/testdb/datafile/example.260.1039781543
+DATA/testdb/datafile/undotbs2.262.1039781543
Start the recovery process
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS -------- ------------ ---------- ---------- ---------- ---------- ARCH CLOSING 1 34 67584 1506 ARCH CONNECTED 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 2 23 488 1 RFS IDLE 0 0 0 0 RFS IDLE 1 35 1441 1 MRP0 APPLYING_LOG 1 35 1440 102400 7 rows selected.
Stop the temporarily listener
[oracle@stbytestn1 dbs]$ lsnrctl stop listener LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-MAY-2020 16:00:56 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.116.58)(PORT=1521))) The command completed successfully
Create pfile from spfile
SQL> create pfile from spfile; File created.
Shutdown the DB
SQL> shu immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down.
Modify the cluster parameters on pfile
[oracle@stbytestn1 dbs]$ vi inittestdb1.ora testdb1.instance_name='testdb1' testdb2.instance_name='testdb2' testdb1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.116.58)(PORT=1521))' testdb2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.116.59)(PORT=1521))' *.cluster_database_instances=2 *.cluster_database=true :wq
Transfer the New PFILE to Standby Node 2
[oracle@stbytestn1 dbs]$ scp inittestdb1.ora oracle@192.168.116.59:/u01/app/oracle/product/11.2.0/db_2/dbs/
Setup ENV on Node 1 & 2 & Start the DB on Mount Mode with Updated PFILE
Node 1
[oracle@stbytestn1 ~]$ export ORACLE_SID=testdb1 [oracle@stbytestn1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_2 [oracle@stbytestn1 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwtestdb1 password=testdb force=y
[oracle@stbytestn1 ~]$ !sq SQL> startup mount pfile=$ORACLE_HOME/dbs/inittestdb1.ora; ORACLE instance started. Total System Global Area 1503199232 bytes Fixed Size 2253424 bytes Variable Size 1342180752 bytes Database Buffers 150994944 bytes Redo Buffers 7770112 bytes Database mounted.
Node 2
[oracle@stbytestn2 ~]$ export ORACLE_SID=testdb2 [oracle@stbytestn2 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_2 [oracle@stbytestn2 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwtestdb2 password=testdb force=y [oracle@stbytestn2 ~]$ !sq SQL> startup mount pfile=$ORACLE_HOME/dbs/inittestdb2.ora; ORACLE instance started. Total System Global Area 1503199232 bytes Fixed Size 2253424 bytes Variable Size 1342180752 bytes Database Buffers 150994944 bytes Redo Buffers 7770112 bytes Database mounted.
Register the Database instance with CRS on Node 1
[oracle@stbytestn1 ~]$ srvctl add database -d testdbstd -o /u01/app/oracle/product/11.2.0/db_2/ [oracle@stbytestn1 ~]$ srvctl add instance -d testdbstd -i testdb1 -n stbytestn1 [oracle@stbytestn1 ~]$ srvctl add instance -d testdbstd -i testdb2 -n stbytestn2
Note:
testdbstd is Unique DB Name
testdb1 is Instance Name
stbytestn1 Hostname
Therefore the Instances are registered with the CRS & Stutdown both the instances in Standby & start with SRVCTL.
Shutdown DB of Node 1 & Node 2
Standby Node 1
[oracle@stbytestn1 ~]$ sqlplus / as sysdba SQL> shu immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down.
Standby Node 2
[oracle@stbytestn2 ~]$ sqlplus / as sysdba SQL> shu immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down.
Start the CRS using SRVCTL
[oracle@stbytestn1 ~]$ srvctl start database -d testdbstd -o mount
Check the status of both the instances
[oracle@stbytestn1 ~]$ srvctl status database -d testdbstd -v Instance testdb1 is running on node stbytestn1. Instance status: Mounted (Closed). Instance testdb2 is running on node stbytestn2. Instance status: Mounted (Closed).
Start the recovery process
SQL> alter database recover managed standby database disconnect from session; Database altered.
Create the SPFILE in ASM & Point it using the PFILE in $ORACLE_HOME/dbs location on both the Standby Node 1&2
Create SPFILE
SQL> create spfile='+DATA/TESTDBSTD/PARAMETERFILE/spfiletestdb.ora' from pfile; File created.
Check the DB Instance as converted into RAC DB
SQL> select * from v$active_instances; INST_NUMBER INST_NAME ------------------------------------ 1 stbytestn1:testdb1 2 stbytestn2:testdb2
Take the backup of PFILE from standby Node 1 & 2 for safer side
[oracle@stbytestn1 dbs]$ cp inittestdb1.ora inittestdb1.ora_final_working [oracle@stbytestn2 dbs]$ cp inittestdb2.ora inittestdb2.ora_final_working
Now edit the PFILE & give the SPFILE path from ASM
Standby Node 1
[oracle@stbytestn1 dbs]$ cat inittestdb1.ora spfile='+DATA/TESTDBSTD/PARAMETERFILE/spfiletestdb.ora'
Standby Node 2
[oracle@stbytestn2 dbs]$ cat inittestdb2.ora spfile='+DATA/TESTDBSTD/PARAMETERFILE/spfiletestdb.ora'
Shutdown the both the instances & startup the DB using SPFILE from ASM Location
Stop & Start the DB using SRVCTL
[oracle@stbytestn1 dbs]$ srvctl stop database -d testdbstd [oracle@stbytestn1 dbs]$ srvctl start database -d testdbstd -o mount
Check the status of database
[oracle@stbytestn1 dbs]$ srvctl status database -d testdbstd -v Instance testdb1 is running on node stbytestn1. Instance status: Mounted (Closed). Instance testdb2 is running on node stbytestn2. Instance status: Mounted (Closed).
Check the current SPFILE used in instance
SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ---------------------------------------------------------------------- spfile string +DATA/testdbstd/parameterfile/spfiletestdb.ora
Finally start the recovery manager process on Standby
SQL> alter database recover managed standby database disconnect from session; Database altered.
Issue few switch logfile on Primary DB & Verify
SQL> alter system switch all logfile; System altered. / /
Verify on Primary DB
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_ ARC_DIFF ---------- ---------- ----------- --------- ---------- 1 46 45 07-MAY-20 1 2 29 29 07-MAY-20 0
Verify on Standby DB
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- ARCH CLOSING 1 34 67584 1506 ARCH CONNECTED 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 2 23 488 1 RFS IDLE 0 0 0 0 RFS IDLE 1 35 1441 1 MRP0 APPLYING_LOG 1 35 1440 102400