Day: April 7, 2025

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:

1. Copy the password file from source database for the target database, or create a password file with the same password as source db.
2. Add “(UR = A)” into source and target tnsnames.
3. 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.
4. 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”