Creating physical standby database using rman duplicate - denis-kol4ev/OraDBA GitHub Wiki

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1)

Purpose:

Creating physical standby using RMAN duplicate

Based on:

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1)

Tested on:

11.2.0.4

12.2.0.1

19.3

Note:

Database Name : prd

primary db_unique_name : angel_prd

standby db_unique_name : devil_prd

1. Enable Forced Logging (primary)

SQL> 
select force_logging from v$database;
alter database force logging;

2. Enable Archiving (primary)

SQL> 
select log_mode from v$database;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

3.Enable broker (primary)

SQL>
alter system set dg_broker_start=true scope=both; 

4. Enable automatic standby file management (primary)

SQL>
alter system set standby_file_management=auto scope=both;

5. Configure the Primary Database to Receive Redo Data if switchover occur (primary)

Determine the size of each log file and the number of log groups in the redo log

SQL> 
select group#, thread#, bytes/1024/1024 as mb from v$log;

Create a standby redo log groups for each thread, standby redo must have at least one more redo log group than the redo log at the redo source database, for example, if thread 1 have 3 redo groups, then standby redo for thread 1 mast have at least 4 standby redo groups

SQL> 
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 ('/appdata/redo/redo11a.stb','/appdata/redo/redo11b.stb') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('/appdata/redo/redo12a.stb','/appdata/redo/redo12b.stb') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('/appdata/redo/redo13a.stb','/appdata/redo/redo13b.stb') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 ('/appdata/redo/redo14a.stb','/appdata/redo/redo14b.stb') SIZE 1024M;
SQL> 
select group#, thread#, bytes/1024/1024 as mb from v$standby_log;

If the db_create_online_log_dest_n parameter is specified, then

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 SIZE 4096m;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 SIZE 4096m;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 SIZE 4096m;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 SIZE 4096m;
SQL> 
select group#, thread#, bytes/1024/1024 as mb from v$standby_log;

6. Creating the password file for standby (primary)

Copy a password file from primary to standby server

cd $ORACLE_HOME/dbs
scp orapwprd oracle@devil:/opt/oracle/ora12c/12.1.0.2/dbs/

7. Ensure that the sql*net connectivity is working fine

7.1 tnsnames.ora for the Primary and Standby should have both entries

#Primary
angel =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = angel)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = angel_prd_DGMGRL))
  )

#Standby 
devil =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = devil)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = devil_prd_DGMGRL))
  )

7.2 Insert a static entrys for angel_prd_DGMGRL and devil_prd_DGMGRL services in the listener.ora file , start listener on standby, reload listener on primary

Primary:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = angel_prd_DGMGRL)
      (ORACLE_HOME = /opt/oracle/ora12c/12.1.0.2)
      (SID_NAME = prd)
    )
  ) 

Standby:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = devil_prd_DGMGRL)
      (ORACLE_HOME = /opt/oracle/ora12c/12.1.0.2)
      (SID_NAME = prd)
    )
  )

7.3 Check with the SQL*Net configuration using the following commands on the Primary and Standby

tnsping angel
tnsping devil

8. Create standby database initialization parameter file

vi $ORACLE_HOME/dbs/initprd.ora
	
db_name=prd
db_unique_name=devil_prd
db_block_size=8192

sqlplus "/ as sysdba"
startup nomount pfile=$ORACLE_HOME/dbs/initprd.ora

*Alternative solution is create spfile with necessary parameters and start instance in nomount with it. In that case we must not use spfile clause in rman run block.

9. Verify if the connection 'AS SYSDBA' is working

sqlplus /nolog
connect sys@angel AS SYSDBA
select host_name from v$instance;
connect sys@devil AS SYSDBA
select host_name from v$instance;

Alternative

(echo "set heading off"; echo "connect sys/pass@angel AS SYSDBA"; echo "select 'Primary: ' || host_name from v\$instance;"; echo "connect sys/pass@devil AS SYSDBA"; echo "select 'Standby: ' || host_name from v\$instance;";) | sqlplus -s / as sysdba

10. Disable archived logs backup scheduled task before run rman duplicate command

If archived redo logs will be moved to tape during duplicate, duplicate will not be success.

11. Connect the target & auxiliary database using RMAN from standby server

rman target sys@angel AUXILIARY sys@devil

12. Duplicate target database using RMAN

run
{
ALLOCATE CHANNEL p1 DEVICE TYPE disk;
ALLOCATE CHANNEL p2 DEVICE TYPE disk;
ALLOCATE CHANNEL p3 DEVICE TYPE disk;
ALLOCATE CHANNEL p4 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL s1 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL s2 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL s3 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL s4 DEVICE TYPE disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER
USING COMPRESSED BACKUPSET
SECTION SIZE 8G
spfile
parameter_value_convert 'angel','devil'
set db_unique_name='devil_prd'
set db_file_name_convert='/appdata/data/prd/','+DATA/devil_prd/'
set log_file_name_convert='/appdata/redo/prd/','+REDO/devil_prd/'
set control_files='+DATA/devil_prd/control01.ctl','+REDO/devil_prd/control02.ctl'
set db_recovery_file_dest='+FRA/'
set db_recovery_file_dest_size='200G'
set audit_file_dest='/opt/oracle/admin/prd/adump'
reset local_listener
reset use_large_pages;
}

*If OMF files are used on the primary database and the db_create_file_dest and db_create_online_log_dest_n parameters are set, then the db_file_name_convert, log_file_name_convert, control_files parameters do not need to be specified in the DUPLICATE command, all files on the standby will be created using OMF.

**For 11G remove USING COMPRESSED BACKUPSET, SECTION SIZE 8G

RMAN ACTIVE DUPLICATE USING BACKUPSET IN 12C(NEW FEATURE) (Doc ID 1987193.1)

• When you specify USING BACKUPSET, RMAN uses the "pull" method.

• The SECTION SIZE clause divides data files into subsections that are restored in parallel across multiple channels on the auxiliary database. For an effective use of parallelization, allocate more AUXILIARY channels.

• With the USING COMPRESSED BACKUPSET clause, the files are transferred as compressed backup sets. RMAN uses unused block compression while creating backups, thus reducing the size of backups that are transported over the network.

Alternative method for primary and standby hosts with the same directory structure configuration

run
{
ALLOCATE CHANNEL p1 DEVICE TYPE disk;
ALLOCATE CHANNEL p2 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL s1 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL s2 DEVICE TYPE disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK DORECOVER
USING COMPRESSED BACKUPSET
SECTION SIZE 1G
spfile
parameter_value_convert 'angel','devil'
set db_unique_name='devil_prd'
set db_recovery_file_dest='+FRA/'
set db_recovery_file_dest_size='200G'
set log_file_name_convert='/appdata/redo/prd/','/appdata/redo/prd/';
}

NOFILENAMECHECK

Prevents RMAN from checking whether the data files and online redo logs files of the source database are in use when the source database files share the same names as the duplicate database files. You are responsible for determining that the duplicate operation does not overwrite useful data. This option is necessary when you are creating a duplicate database in a different host that has the same disk configuration, directory structure, and file names as the host of the source database.

LOG_FILE_NAME_CONVERT

If LOG_FILE_NAME_CONVERT not specified and DB_RECOVERY_FILE_DEST is specified, then online logs will be created in DB_RECOVERY_FILE_DEST with Oracle managed online redo log file names

13. Open standby database, start redo apply

SQL> 
ALTER DATABASE OPEN READ ONLY;
RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

14. Create dataguard broker configuration (primary)

DGMGRL> 
connect sys/***
CREATE CONFIGURATION 'DG_CONF' AS PRIMARY DATABASE IS 'angel_prd' CONNECT IDENTIFIER IS angel;
ADD DATABASE 'devil_prd' AS CONNECT IDENTIFIER IS devil;
ENABLE CONFIGURATION;
SHOW CONFIGURATION;

edit database angel_prd set property 'DbFileNameConvert'='+DATA/angel_prd/, /appdata/data/prd/';
edit database angel_prd set property 'LogFileNameConvert'='+REDO/angel_prd/, /appdata/redo/prd/';

Bug 28651637 - Broker: "edit database set property dbfilenameconvert" fails with ORA-32017 ORA-1678 (Doc ID 28651637.8) 19c DGMGRL Command "SHOW DATABASE VERBOSE" Does Not Display Property Values (Doc ID 2732540.1)

alter system set db_file_name_convert='+DATA/angel_prd/','/appdata/data/prd/' scope=spfile;
alter system set log_file_name_convert='+REDO/angel_prd/','/appdata/redo/prd/' scope=spfile;

*DbFileNameConvert and LogFileNameConvert ignored in 19.3, configuring this parameters in dgmgrl for this version are not required.

15. Configure archivelog deletion policy

rman target /
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY BACKED UP 1 TIMES TO DEVICE TYPE SBT;

16. Enable archived logs backup scheduled task that was disabled before rman duplicate

17. Check absence of Transport Lag and Apply Lag for standby database

dgmgrl /
show database devil_prd;

18. Add standby database to Oracle Restart (if ASM used)

echo 'prd:/opt/oracle/ora12c/12.1.0.2:N' >> /etc/oratab
srvctl add database -d devil_prd -n prd -i prd -o /opt/oracle/ora12c/12.1.0.2 -p /opt/oracle/ora12c/12.1.0.2/dbs/spfileprd.ora -r PHYSICAL_STANDBY -s "READ ONLY" -t IMMEDIATE -y AUTOMATIC -a "DATA,REDO,FRA"
(echo "shutdown immediate;"; echo "startup;";) | sqlplus -s / as sysdba