Data Guard Physical Standby Setup Using Active Duplicate
VM Machine Details:
node1.oracle.com (147.43.0.15)
node2.oracle.com (147.43.0.16)
Note:
1. Both VM machines should be ping each, Network adapter setting choose "Host-Only".
2. Configure or add both machine ip addresses in /etc/hosts file.
3. Check or test the connectivity using ping or ssh commands.
Database Details:
Database Name :- PRODUAT
Primary db_unique_name :- PRODUAT
standby db_unique_name :- PRODSIT
Steps:
1. Ensure that the database is in archivelog mode.
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
2. Enable force logging.
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
-- Make sure at least one logfile is present.
ALTER SYSTEM SWITCH LOGFILE;
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
3. Create standby redologs.
Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:
(maximum number of logfiles for each thread + 1) * maximum number of threads
SQL> select bytes from v$standby_log;
no rows selected
SQL> SELECT * FROM V$LOGFILE;
SQL> select group#,thread#,bytes from v$log;
GROUP# THREAD# BYTES
---------- ---------- ----------
1 1 52428800
2 1 52428800
3 1 52428800
alter database add standby logfile '/u01/app/oracle/fast_recovery_area/PRODUAT/stbyredo03.log' size 50M;
alter database add standby logfile '/u01/app/oracle/fast_recovery_area/PRODUAT/stbyredo04.log' size 50M;
alter database add standby logfile '/u01/app/oracle/fast_recovery_area/PRODUAT/stbyredo05.log' size 50M;
SQL> select bytes from v$standby_log;
BYTES
----------
52428800
52428800
52428800
52428800
Note: We no need to create standby redo log files on standby and Oracle take cares of it during RMAN duplicate.
4. Modify the primary initialization parameter for dataguard on primary.
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRODUAT,PRODSIT)';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/fast_recovery_area/PRODUAT/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODUAT';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=PRODSIT LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSIT';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.
SQL> alter system set standby_file_management=auto scope=both;
System altered.
SQL> alter system set FAL_SERVER=PRODSIT;
System altered.
SQL> alter system set FAL_CLIENT=PRODUAT;
System altered.
SQL> alter system set DB_FILE_NAME_CONVERT='PRODUAT','PRODSIT' scope=spfile; System altered.
SQL> alter system set LOG_FILE_NAME_CONVERT='PRODUAT','PRODSIT' scope=spfile;
System altered.
5. Create the necessary directories on the standby server.
mkdir -p /u01/app/oracle/oradata/PRODSIT
chown -R oracle:oinstall /u01/app/oracle/oradata/PRODSIT
6. Configure Oracle net service/TNS names for standby system using NETCA or NETMGR
Make sure tnsnames.ora file contains both entries in Primary and Standby servers
Primary:
[oracle@node1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/MTEST/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PRODUAT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = PRODUAT)
)
)
PRODSIT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2.oracle.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = PRODSIT)
)
)
Standby:
[oracle@node2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PRODSIT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2.oracle.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = PRODSIT)
)
)
PRODUAT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = PRODUAT)
)
)
7. Check with the SQL*Net configuration using the following commands on the Primary and Standby
tnsping MQMPROD
tnsping MQMDR
8. Create the standby database
-Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.
-Create a initialization parameter with only one parameter DB_NAME.
DB_NAME=PRODUAT
DB_UNIQUE_NAME=PRODSIT
compatible='12.1.0.2.0'
log_file_name_convert='PRODUAT','PRODSIT'
9. Create the necessary directories in the standby location to place database files and trace files ($ADR_HOME)
mkdir -p /u01/app/oracle/admin/MQMDR/adump
10. Set the environment variable ORACLE_SID to the standby service and start the standby-instance.
export ORACLE_SID=MQMDR
sqlplus "/ as sysdba"
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initPRODSIT.ora
11. Verify if the connection 'AS SYSDBA' is working
sqlplus /nolog
SQL> connect sys/Welcome1@MQMDR AS SYSDBA
Connected.
SQL> connect sys/Welcome1@MQMPROD AS SYSDBA
Connected.
12. Connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances ( Issue on Standby )
Connect RMAN using target and auxiliary should connected as “not mounted” only.
[oracle@node2 ~]$ rman target sys/Welcome1@PRODUAT auxiliary sys/Welcome1@PRODSIT
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 28 05:23:39 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRODUAT (DBID=1381890412)
connected to auxiliary database: PRODUAT (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;
An explanation of the above RMAN command.
FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.
DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
NOFILENAMECHECK: Destination file locations are not checked.
Once the command is complete, we can start the apply process.
Output:
Starting Duplicate Db at 28-AUG-20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK
current log archived
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/MTEST/dbs/orapwPRODUAT' auxiliary format
'/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwPRODSIT' ;
}
executing Memory Script
Starting backup at 28-AUG-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 device type=DISK
Finished backup at 28-AUG-20
contents of Memory Script:
{
restore clone from service 'PRODUAT' standby controlfile;
}
executing Memory Script
Starting restore at 28-AUG-20
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRODUAT
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/cntrlPRODSIT.dbf
Finished restore at 28-AUG-20
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_temp_hnkbdtkj_.tmp";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_system_hnkb8mqx_.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_sysaux_hnkb68cx_.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_undotbs1_hnkbcn7x_.dbf";
set newname for datafile 6 to
"/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_users_hnkbcm4m_.dbf";
restore
from service 'PRODUAT' clone database
; sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_temp_hnkbdtkj_.tmp in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 28-AUG-20
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRODUAT
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_system_hnkb8mqx_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRODUAT
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_sysaux_hnkb68cx_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRODUAT
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_undotbs1_hnkbcn7x_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRODUAT
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_users_hnkbcm4m_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 28-AUG-20
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service 'PRODUAT'
archivelog from scn 1618367;
switch clone datafile all;
}
executing Memory Script
Starting restore at 28-AUG-20
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service PRODUAT
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=14
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service PRODUAT
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=15
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 28-AUG-20
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1049648103 file name=/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_system_hnkb8mqx_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1049648103 file name=/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_sysaux_hnkb68cx_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1049648103 file name=/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_undotbs1_hnkbcn7x_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=1049648103 file name=/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_users_hnkbcm4m_.dbf
contents of Memory Script:
{
set until scn 1618687;
recover
standby
clone database
delete archivelog
;}
executing Memory Script
executing command: SET until clause
Starting recover at 28-AUG-20
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_14_1049631726.dbf
archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_15_1049631726.dbf
archived log file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_14_1049631726.dbf thread=1 sequence=14
archived log file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_15_1049631726.dbf thread=1 sequence=15
media recovery complete, elapsed time: 00:00:00
Finished recover at 28-AUG-20
Finished Duplicate Db at 28-AUG-20
12. Start managed recovery
Connect to standby using SQL*Plus and start the MRP (Managed Recovery Process). Compare the primary last sequence and MRP (Managed Recovery Process) applying sequence.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE from v$database;
NAME CONTROL OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- ------- -------------------- ---------------- --------------------
PRODUAT STANDBY MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL> host
[oracle@node2 ~]$ ps -ef|grep mrp
oracle 10077 1 0 17:17 ? 00:00:02 ora_mrp0_PRODSIT
oracle 10158 10129 0 17:23 pts/1 00:00:00 grep mrp
The Managed Recovery Process (MRP) applies information from the archived redo logs to the standby database. When performing managed recovery operations, log apply services automatically apply archived redo logs to maintain transactional synchronization with the primary database.
Alertlog:
Fri Aug 28 17:17:52 2020
alter database recover managed standby database disconnect from session
Fri Aug 28 17:17:52 2020
Attempt to start background Managed Standby Recovery process (PRODSIT)
Starting background process MRP0
Fri Aug 28 17:17:52 2020
MRP0 started with pid=21, OS id=10077
Fri Aug 28 17:17:52 2020
MRP0: Background Managed Standby Recovery process started (PRODSIT)
Fri Aug 28 17:17:57 2020
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Fri Aug 28 17:17:57 2020
Waiting for all non-current ORLs to be archived...
Fri Aug 28 17:17:57 2020
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 16
Completed: alter database recover managed standby database disconnect from session
Note:
If you face any SYNC issue, try to set the standby related parameters.