This post demonstrates how to duplicate an Oracle database from a standby database instead of from a primary database normally.
The environment list:
| Primaey DB | Standby DB | Dulicate DB |
| TESTDB | STBYDB | DUDB |
Assume that all tnsnames are already configured. Password file, spfile are also already configured as the following:
- Copy the password file from source database for the target database, or create a password file with the same password as source db.
- Add “(UR = A)” into source and target tnsnames.
- Add an entry into listener.ora for auxiliary instance static registration. You can create a temporary listener with a spare port without changing anything of the current listener.
- CLUSTER_DATABASe=false in spfile for auxiliary instance.
Please note when you create the password file, you have to add the database into cluster OCR first, otherwise the following errors will occur:
ASMCMD> pwcreate --dbuniquename DUPDB '+DATA2/DUPDB/password/pwddupdb'
Enter password: ***********
OPW-00021: Failed to retrieve DB password file location from the CRS resource
ASMCMD-9454: could not create new password file
$ srvctl add database -d DUPDB -oraclehome /u01/app/oracle/product/19.0.0/dbhome_1 -dbtype RAC -domain world -spfile +DATA2/DUPDB/PARAMETERFILE/spfileDUPDB.ora -role PRIMARY -startoption OPEN -stopoption IMMEDIATE
ASMCMD> pwcreate --dbuniquename DUPDB '+DATA2/DUPDB/password/pwddupdb'
Enter password: ***********
$ srvctl modify database -db DUPDB -pwfile '+DATA2/DUPDB/password/pwddupdb'
-- The following error will occur if the database is not added into CRS before creating the password file:
ASMCMD> pwcreate --dbuniquename DUPDB '+DATA2/DUPDB/password/pwddupdb'
Enter password: ***********
OPW-00021: Failed to retrieve DB password file location from the CRS resource
ASMCMD-9454: could not create new password file
Start Up Duplicate Database in Nomount
SQL> startup nomount pfile='/tmp/initDUPDB.ora' ;
SQL> create spfile='+DATA2/DUPDB/PARAMETERFILE/spfileDUPDB.ora' from pfile='/tmp/initDUPDB1.ora' ;
$ cat $ORACLE_HOME/dbs/initDUPDB1.ora
spfile='+DATA2/DUPDB/PARAMETERFILE/spfileDUPDB.ora'
SQL> startup database nomount force;
Duplicate database when standby in Read Only Mode
$ srvctl stop database -db STBYDB
$ srvctl start database -db STBYDB -startoption "READ ONLY"
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
$ rman target sys@stbydb auxiliary sys@dupdb
target database Password:
connected to target database: STDBYDB (DBID=123456789)
auxiliary database Password:
connected to auxiliary database: DUPDB (not mounted)
RMAN> duplicate target database to DUPDB from active database ;
...
...
.
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 03-APR-25
RMAN>
Duplicate database when standby in Recovery( Mounted and Applying ) Mode
DGMGRL> show configuration;
Configuration - dg_testdb
Protection Mode: MaxPerformance
Members:
testdb - Primary database
stbydb - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 56 seconds ago)
DGMGRL> show database verbose "stbydb";
Database - stbydb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 3.40 MByte/s
Active Apply Rate: 12.61 MByte/s
Maximum Apply Rate: 12.89 MByte/s
Real Time Query: OFF
Instance(s):
STBYDB1 (apply instance)
STBYDB2
...
Database Status:
SUCCESS
DGMGRL>
$ rman target sys@stbydb auxiliary sys@dupdb
target database Password:
connected to target database: STBYDB (DBID=123456789)
auxiliary database Password:
connected to auxiliary database: DUPDB (not mounted)
RMAN> duplicate target database to DUPDB from active database ;
...
...
.
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 03-APR-25
RMAN>
Duplicate database when standby in Non-Recovery ( Mounted and Non-applying ) Mode
DGMGRL> show database verbose "stbydb"
Database - stbydb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 12 hours 39 minutes 52 seconds (computed 0 seconds ago)
Average Apply Rate: 46.00 KByte/s
Active Apply Rate: 1.17 MByte/s
Maximum Apply Rate: 12.89 MByte/s
Real Time Query: OFF
Instance(s):
STBYDB1 (apply instance)
STBYDB2
Database Error(s):
ORA-16766: Redo Apply is stopped
Database Warning(s):
ORA-16853: apply lag has exceeded specified threshold
...
..
.
Database Status:
ERROR
DGMGRL>
$ rman target sys@stbydb auxiliary sys@dupdb
target database Password:
connected to target database: STDBYDB (DBID=123456789)
auxiliary database Password:
connected to auxiliary database: DUPDB (not mounted)
RMAN> duplicate target database to DUPDB from active database ;
...
...
.
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 03-APR-25
RMAN>
From the above tests, we can see the duplicate process will
1) switch archivelogs on PRIMARY
2) copy archived logs onto new duplicated database
3) apply the archived logs onto new database finally before OPEN new database.
so, no matter standby database is either
1) OPEN READ ONLY MODE
2) MOUNTED and RECOVERY MODE
3) MOUNTED and NON RECOVERY MODE
DUPLICATE from standby database all works well.
The common errors and solutions
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
This error is generated because STATIC REGISTRATION on listener was not done. To fix this, we need to add an entry of SID_LIST_LISTENER into listener.ora.
RMAN-04006: error from auxiliary database: ORA-12537: TNS:connection closed
This error was generated by missing the (UR = A) from tnsnames.
RMAN-04006: error froRMAN-06136: Oracle error from auxiliary database: ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database serverm auxiliary database: ORA-01017: invalid username/password; logon denied
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/03/2025 15:29:36
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: Oracle error from auxiliary database: ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/03/2025 16:28:42
RMAN-05501: aborting duplication of target database
RMAN-05614: Passwords for target and auxiliary connections must be the same when using active duplicate
This error is because password file was not copied to duplicate database, or the password is different.
ORA-12720: operation requires database is in EXCLUSIVE mode
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/03/2025 16:50:17
RMAN-05501: aborting duplication of target database
RMAN-06136: Oracle error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode
set parameter “cluster_database=FALSE”
Make DBA Life Easy 