Generally we do active duplicate from primary database, but I think it should be fine to see what happen if we do active duplicate a database from a standby database. So, I do some tests and I write the results in this blog
Below the actual configuration of my Data Guard environment
DGMGRL> show configuration Configuration - db21 Protection Mode: MaxPerformance Members: DB21_SITE1 - Primary database DB21_SITE2 - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 35 seconds ago) DGMGRL>
The tests are done with Oracle 21c but I guess it should work for 19c
Standby in Read Only Mode
The first tests I do is with the standby opened in read only mode
If you do not have Active Data Duard license stop the apply process before opening the database in Read Only mode
[email protected]:/home/oracle/ [DB21 (CDB$ROOT)] DB21 ********* dbi services Ltd. ********* STATUS : OPEN DB_UNIQUE_NAME : DB21_SITE2 OPEN_MODE : READ ONLY LOG_MODE : ARCHIVELOG DATABASE_ROLE : PHYSICAL STANDBY FLASHBACK_ON : YES FORCE_LOGGING : YES VERSION : 21.0.0.0.0 CDB Enabled : YES List PDB(s) MOUNTED : PDB1, PDB2, PDB3 List PDB(s) READ ONLY : PDB$SEED ************************************* [email protected]:/home/oracle/ [DB21 (CDB$ROOT)]
The goal is to duplicate the standby database DB21_SITE2 to DB21CLNE to a remote server. I assume that all network files are already configured. Password file, spfile are also already configured.
From the source server
[email protected]:/home/oracle/ [DB21 (CDB$ROOT)] tnsping db21_site2 TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 06-APR-2022 13:29:59 Copyright (c) 1997, 2020, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraadserver2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DB21_SITE2_DGMGRL))) OK (0 msec) [email protected]:/home/oracle/ [DB21 (CDB$ROOT)] tnsping db21clne TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 06-APR-2022 13:30:05 Copyright (c) 1997, 2020, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraadserver4)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DB21CLNE))) OK (0 msec) [email protected]:/home/oracle/ [DB21 (CDB$ROOT)]
From the target server
[email protected]:/home/oracle/ [DB21CLNE (CDB$ROOT)] tnsping db21_site2 TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 06-APR-2022 13:33:12 Copyright (c) 1997, 2020, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraadserver2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DB21_SITE2_DGMGRL))) OK (0 msec) [email protected]:/home/oracle/ [DB21CLNE (CDB$ROOT)] tnsping db21clne TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 06-APR-2022 13:33:16 Copyright (c) 1997, 2020, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraadserver4)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DB21CLNE))) OK (10 msec) [email protected]:/home/oracle/ [DB21CLNE (CDB$ROOT)]
I then create a simple duplicate rman script
[email protected]: [DB21CLNE (CDB$ROOT)] cat duplicate_from_standby.rcv connect target sys/******@db21_site2 connect auxiliary sys/****@db21clne run { duplicate target database to DB21CLNE from active database nofilenamecheck; }
After starting the target database DB21CLNE in NOMOUNT state, I run the script to create DB21CLNE
[email protected]: [DB21CLNE (CDB$ROOT)] nohup rman cmdfile=duplicate_from_standby.rcv log=duplicate_standby_`date +"%Y-%m-%d_%H%M%S"`.log &
A few minutes later the duplicate went fine (output truncated)
Recovery Manager: Release 21.0.0.0.0 - Production on Wed Apr 6 14:51:56 2022 Version 21.1.0.0.0 Copyright (c) 1982, 2020, Oracle and/or its affiliates. All rights reserved. RMAN> connect target * 2> connect auxiliary * 3> run { 4> duplicate target database to DB21CLNE from active database nofilenamecheck; 5> } 6> connected to target database: DB21 (DBID=1137202071) connected to auxiliary database: DB21CLNE (not mounted) Starting Duplicate Db at 06-APR-2022 14:51:57 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=25 device type=DISK current log archived at primary database current log archived at primary database duplicating Online logs to Oracle Managed File (OMF) location duplicating Datafiles to Oracle Managed File (OMF) location contents of Memory Script: { sql clone "alter system set control_files = ''/u01/app/oracle/oradata/DB21CLNE/controlfile/o1_mf_k4v3hqr1_.ctl'', ''/u01/app/oracle/fast_recovery_area/DB21CLNE/controlfile/o1_mf_k4v3hqr7_.ctl'' comment= ''Set by RMAN'' scope=spfile"; sql clone "alter system set db_name = ''DB21'' comment= ''Modified by RMAN duplicate'' scope=spfile"; sql clone "alter system set db_unique_name = ''DB21CLNE'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount restore clone from service 'db21_site2' primary controlfile; alter clone database mount; } executing Memory Script sql statement: alter system set control_files = ''/u01/app/oracle/oradata/DB21CLNE/controlfile/o1_mf_k4v3hqr1_.ctl'', ''/u01/app/oracle/fast_recovery_area/DB21CLNE/controlfile/o1_mf_k4v3hqr7_.ctl'' comment= ''Set by RMAN'' scope=spfile sql statement: alter system set db_name = ''DB21'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set db_unique_name = ''DB21CLNE'' comment= ''Modified by RMAN duplicate'' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area 1577057624 bytes Fixed Size 9686360 bytes Variable Size 385875968 bytes Database Buffers 889192448 bytes Redo Buffers 292302848 bytes Starting restore at 06-APR-2022 14:56:46 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=333 device type=DISK channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service db21_site2 channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/DB21CLNE/controlfile/o1_mf_k4v3hqr1_.ctl output file name=/u01/app/oracle/fast_recovery_area/DB21CLNE/controlfile/o1_mf_k4v3hqr7_.ctl Finished restore at 06-APR-2022 14:56:49 database mounted … … Executing: alter database force logging contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script database opened Executing: alter database flashback on contents of Memory Script: { sql clone "alter pluggable database all open"; } executing Memory Script sql statement: alter pluggable database all open Finished Duplicate Db at 06-APR-2022 15:02:41 Recovery Manager complete.
And DB21CLNE is created and opened in READ WRITE and ready to be used
[email protected]:/u01/app/oracle/admin/DB21CLNE/create/ [DB21CLNE (CDB$ROOT)] DB21CLNE ********* dbi services Ltd. ********* STATUS : OPEN DB_UNIQUE_NAME : DB21CLNE OPEN_MODE : READ WRITE LOG_MODE : ARCHIVELOG DATABASE_ROLE : PRIMARY FLASHBACK_ON : YES FORCE_LOGGING : YES VERSION : 21.0.0.0.0 CDB Enabled : YES List PDB(s) READ ONLY : PDB$SEED List PDB(s) READ WRITE : PDB1, PDB2, PDB3 *************************************
Standby in MOUNT Mode
I decide to do the same tests , but with the source standby database in MOUNT state
[email protected]:/home/oracle/ [DB21 (CDB$ROOT)] DB21 ********* dbi services Ltd. ********* STATUS : MOUNTED DB_UNIQUE_NAME : DB21_SITE2 OPEN_MODE : MOUNTED LOG_MODE : ARCHIVELOG DATABASE_ROLE : PHYSICAL STANDBY FLASHBACK_ON : YES FORCE_LOGGING : YES CDB Enabled : YES List PDB(s) MOUNTED : PDB$SEED, PDB1, PDB2, PDB3 ************************************* [email protected]:/home/oracle/ [DB21 (CDB$ROOT)]
In this case the starts fine, but the recovery process was asking for a archived and was not able to finish. There was no error returned, but the duplicate is asking, asking the missing archived
RMAN> connect target * 2> connect auxiliary * 3> run { 4> duplicate target database to DB21CLNE from active database nofilenamecheck; 5> } 6> connected to target database: DB21 (DBID=1137202071, not open) connected to auxiliary database: DB21CLNE (not mounted) Starting Duplicate Db at 06-APR-2022 14:12:28 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 at primary database current log archived at primary database duplicating Online logs to Oracle Managed File (OMF) location duplicating Datafiles to Oracle Managed File (OMF) location contents of Memory Script: { sql clone "alter system set control_files = ''/u01/app/oracle/oradata/DB21CLNE/controlfile/o1_mf_k4v15kz7_.ctl'', ''/u01/app/oracle/fast_recovery_area/DB21CLNE/controlfile/o1_mf_k4v15l04_.ctl'' comment= ''Set by RMAN'' scope=spfile"; sql clone "alter system set db_name = ''DB21'' comment= ''Modified by RMAN duplicate'' scope=spfile"; sql clone "alter system set db_unique_name = ''DB21CLNE'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount restore clone from service 'db21_site2' primary controlfile; alter clone database mount; } executing Memory Script … … starting media recovery archived log file name=/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence=35 archived log file name=/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence=35 archived log file name=/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence=35 ... ... archived log file name=/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence=35 archived log file name=/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence=35 archived log file name=/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence=35 archived log file name=/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence=35 ….
As a workaround from the standby server I manually copy the requested archive to the remote source server
[email protected] ] scp o1_mf_1_35_k4v156k8_.arc oraadserver4:/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06
And the duplicate process went fine
… … contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script database opened Executing: alter database flashback on contents of Memory Script: { sql clone "alter pluggable database all open"; } executing Memory Script sql statement: alter pluggable database all open Finished Duplicate Db at 06-APR-2022 14:34:39 Recovery Manager complete.
So we can see that duplicate can be done from a standby database like a primary one. Just when the standby is mounted, you may have to manually copy some archived.
Note that I repeat the duplicate a second time with the database mount and I had the same behavior, I had to manually copy a missing archived log.