dbi Blog

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.

Active Duplicate from standby database

Mouhamadou Diaw

Related blog articles

Mouhamadou Diaw

Related blog articles

Contained Availability Groups error

Get SQL Server database size with PowerShell

Reminder about SSMS

Will [email protected] change or kill your DBA job?