In this post we will look at how to create a standby database using RMAN duplicate command.
I've already written about the standby database creation in this post a few years back using Oracle 9i database. The major difference to the old post is that we are now going to perform standby build using active database. Let's look into how to perform the same. The old post is still valid for building standby using the same steps explained even for 19c database.
We still have to satisfy all the prerequisites before building a standby database. Prerequisites basically comprises of enabling archive log mode and force logging, creating and copying the password file to auxiliary instance, etc. You can go through and perform all the activities defined in this post to complete all prerequisites. We can skip performing the database backup for this activity as we are now going to use active database to perform the standby build.
This is going to be very simple compared to the post which I created for Oracle 9i.
Few checks:
# Copy the password file from primary and store in standby server and rename to match standby naming
From Primary:
scp $ORACLE_HOME/dbs/orapwABC standbyserver:/oracle/ABC/19.0.0/dbs/
# Prepare Primary for standby setup
# Add below parameters in primary
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ABC,ABC_STANDBY)' scope=both;alter system set LOG_ARCHIVE_DEST_1='LOCATION=+ARCH/ABC/oraarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ABC' scope=both;alter system set LOG_ARCHIVE_DEST_2='SERVICE=ABC_STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ABC_STANDBY' scope=both;alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;alter system set FAL_SERVER=ABC_STANDBY scope=both;alter system set FAL_CLIENT=ABC scope=both;
# Try tnsping in both target server and auxiliary server and both should work
From Primary: tnsping abc_standbyFrom standby: tnsping abc
### This is from Primary server -sh-4.2$ tnsping abc_standby TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 19-AUG-2020 01:25:46 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: /usr/sap/ABC/SYS/profile/oracle/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = SAP.WORLD) (PROTOCOL = TCP) (HOST = xxxxx) (PORT = 1527))) (CONNECT_DATA = (SID = ABC) (GLOBAL_NAME = ABC.WORLD))) OK (70 msec) -sh-4.2$ ### This is from Standby server -sh-4.2$ tnsping abc TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 19-AUG-2020 01:26:46 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: /oracle/ABC/19.0.0/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = SAP.WORLD) (PROTOCOL = TCP) (HOST = abcdb) (PORT = 1527))) (CONNECT_DATA = (SID = ABC) (GLOBAL_NAME = ABC.WORLD))) OK (0 msec) -sh-4.2$
Assuming we have already completed all the prerequisites, I'm going to start the auxiliary instance with just the below parameters.
# Create a simple pfile (initABC.ora) under $ORACLE_HOME/dbs with the below entries
*.db_name=ABC*.db_unique_name=ABC_STANDBY*.db_block_size=16384
-- For db_block_size choose which is optimal for your environment. General and most common is 8kb
# Create all necessary directories for trace files etc. This can be similar set up to your primary database server as names of both the database will be same.
# Start standby database in nomount
-sh-4.2$ . oraenv
ORACLE_SID = [ABC] ?
The Oracle base has been changed from /oracle to /oracle/ABC
-sh-4.2$ sqlplus / as sysdbaSQL> startup nomount pfile=/oracle/ABC/19.0.0/dbs/initABC.ora
# Check connectivity from both ends
From Primary: sqlplus sys/<passwd<@ABC AS SYSDBA
From Standby: sqlplus sys/<passwd>@ABC_STANDBY AS SYSDBA
Create standby script
$ more create_abcstby.ksh
#!/bin/ksh
dt=`date '+%Y%m%d'`
mylog=create_abcstby_${dt}.log
touch $mylog
rman target sys/*****@ABC auxiliary sys/*****@ABC_STANDBY cmdfile create_abcstby.rcv msglog $mylog
$
Contents of create_abcstby.ksh is as follows
$ more create_abcstby.rcv
run {
ALLOCATE CHANNEL t1 DEVICE TYPE disk;
ALLOCATE CHANNEL t2 DEVICE TYPE disk;
ALLOCATE CHANNEL t3 DEVICE TYPE disk;
ALLOCATE CHANNEL t4 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL a1 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL a2 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL a3 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL a4 DEVICE TYPE disk;
duplicate target database
for standby
from active database
dorecover
nofilenamecheck
spfile
set db_name='ABC'
set db_unique_name='ABC_STANDBY'
set audit_file_dest='/oracle/ABC/saptrace/audit'
set diagnostic_dest='/oracle/ABC/saptrace'
set control_files='+DATA','+LOG1','+LOG2'
set db_create_file_dest='+DATA'
set db_create_online_log_dest_1='+LOG1'
set db_create_online_log_dest_2='+LOG2'
set db_recovery_file_dest='+RECO'
set db_recovery_file_dest_size='10240G'
set log_archive_max_processes='8'
set fal_client='ABC_STANDBY'
set fal_server='ABC'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(ABC,ABC_STANDBY)'
set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=ABC_STANDBY'
set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxx)(PORT=1527))'
set log_archive_dest_2='service=ABC db_unique_name=ABC valid_for=(online_logfile, primary_role)'
set sga_max_size='1280G'
;
}
Script explained: - We started the auxiliary instance with just the 3 parameters required
- Once we start the script RMAN will be connecting to both Target (Primary) and Auxiliary (Standby) database to perform the duplicate.
- RMAN will use the contents of the spfile used in the Primary database to start the auxiliary database.
- The set commands specified in the RMAN duplicate script will introduce new parameters to the auxiliary instance if not present in the primary instance and override any parameters with the new values if the parameters already exists in the primary instance.
- For example, db_unique_name will be ABC in the primary instance and now will be override by ABC_STANDBY in the auxiliary instance. Like wise if we don't have log_archive_max_processes process defined in primary instance, this process will be introduced to the auxiliary (standby) instance.
Note that I have allocated equal number of channels for both target channels and auxiliary channels. This is an RMAN active duplicate feature enhancement introduced in Oracle version 12c and above to off load processing of duplicate operation to the auxiliary site.
When the number of auxiliary channels are allocated equal to the number of target channels, the backup request is initiated by auxiliary channels and processing load is done in the auxiliary instance. This is called as pull-based method of active duplication which differs from the push-based method of duplication which uses image copies of the target DB to be copied to auxiliary using target channels and is done by target instance. To know in detail about push bases and pull based method, please check here
We can start the activity by calling the script in nohup mode as we are not sure how long the script will run and we don't any disconnects to disrupt our activity.
# Run the below in auxiliary (standby) server
$ nohup create_bwpstby.ksh &
Once the script has completed execution the database will be in mount state and recovered until the logs are available.
The last step is to enable MRP process which will then keep the standby database in sync with primary database by applying the logs as and when it receives the logs.
-- Start MRP process SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; Media recovery complete. SQL>Starting 12.1.0.2, the above command will start real time apply if we have Standby Redo Logs (SRL) available in the standby database. Since we already created the SRLs as part of our prerequisites, we should now have real time changes applied on the standby database. This also deprecates use of "USING CURRENT LOGFILE" clause from the real time apply command and throws a warning message in alert log file if used.
What if we don't have SRLs created? No worries, dataguard will still work but will apply archived logs as and when available in the standby site as opposed to the online redo changes apply. This will have a delay in standby data sync with respect to primary and we can see the below warning in the alert log file as well.
WARNING: There are no standby redo logs.Standby redo logs should be configured for real time apply. Real time apply will be ignored.
Now once all the set up is completed, we can check the status of the dataguard using the below command.
# Verify DG works
select process, sequence#, status from v$managed_standby;
Some tips...
Since I'm using OMF, we don't have to include log_file_name_convert and db_file_name_convert when duplicating the database
We have an enhanced parameter parameter_value_convert starting Oracle version 11g which can update all string values, not just those containing path names. The values are case-sensitive.
References:
Duplicating a Database - I have used 12.1 document to show the new features
Oracle Support note: Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1)
Oracle Support note: RMAN ACTIVE DUPLICATE USING BACKUPSET IN 12C(NEW FEATURE) (Doc ID 1987193.1)
Oracle Support note: Rman Active Duplicate Runs Into RMAN-06217 -- PUSH & PULL method Explanation (Doc ID 2369137.1)
Happy standby building!!! :)
