How to use RMAN DUPLICATE FROM ACTIVE DATABASE to clone an entire CDB and all PDBs to a remote host

In this post I will show how to clone an entire cdb with all PDBs using the DUPLICATE FROM ACTIVE DATABASE command, which has been around for many years. 

You can still reuse your existing scripts for these operations, with some minor tweaks, even after you have moved to the multitenant architechture.

My target server is called prodserver

My auxiliary server is called testserver

On prodserver, the container database is called "cdb" and for the time being, there is only one pluggable database running there, called "pdbprod", as shown below:

orasoft@prodserver:[pdbprod] sqlplus / as sysdba
SYS@CDB$ROOT SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 pdbprod                        READ WRITE NO

On my auxiliary server testserver, I also have a container database called "cdb", and a PDB called "pdbtest":

orasoft@testserver:[pdbtest] sqlplus / as sysdba
SYS@CDB$ROOT SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 pdbtest                        READ WRITE NO

First, I recommend to configure a wallet, to avoid exposing your passwords in scripts or in on the Linux shell prompt.

Add a global user that exists in both container database and make sure it has SYSDBA privileges.
In this example, I add a user called c##cloneadmin and use the alias "cdbprod" and "cdbtest" for both:

mkstore -wrl $TNS_ADMIN/wallet -createCredential cdbprod c##cloneadmin
mkstore -wrl $TNS_ADMIN/wallet -createCredential cdbaux c##cloneadmin

Add the connect descriptions to your tnsnames.ora file, on both source and target.
Make sure the passwordless connections work before you attempt cloning. Test like this:

rman 
connect target /@cdbprod
connect auxiliary /@cdbaux
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
}
exit

Do not proceed until you connect successfully to both.

Create a minimal initcdb.ora file, containing only these two parameters:

*.db_name='cdb'
*.enable_pluggable_database=TRUE

Startup your auxiliary container database in nomount mode using the initcdb.ora file above:

sqlplus / as sysdba
startup nomount pfile=initcdb.ora

Create an RMAN script for your duplication, in my example I put it in a file called run_duplication.cmd:

connect target /@cdbprod
connect auxiliary /@cdbaux
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
configure device type disk parallelism 2;
debug io;
DUPLICATE DATABASE TO cdb
FROM ACTIVE DATABASE
USING BACKUPSET
SPFILE
 set db_file_name_convert='pdbprod','pdbtest'
 set audit_file_dest='/oraadmin/oracle/admin/cdb/adump'
 set core_dump_dest='/oraadmin/oracle/diag/rdbms/cdb/cdb/cdump'
 set control_files='/dbfiles01/oradata/CDB/control01.ctl','/dbfiles02/fra/CDB/control02.ctl'
 set sga_target='33621540864'
 set sga_max_size='33621540864'
 set pga_aggregate_target='12773752832'
 set shared_pool_size='2751463424'
 set streams_pool_size='268435456'
 set service_names='cdb,pdbtest'
 set db_recovery_file_dest_size='3221225472000'
NOFILENAMECHECK 
;
debug off;
}
exit

Remember that the NOFILENAMECHECK is required when using the same data file names but on different hosts. Without it, RMAN throws the error below and aborts your script:

RMAN-05001: auxiliary file name /dbfiles02/oradata/CDB/undotbs01.dbf conflicts with a file used by the target database

Create a shell script that calls the RMAN script, for example run_duplication.sh:

rman cmdfile='run_duplication.cmd' debug trace='duplicate.trc' log='duplicate.log'

Make the script executable, and execute it. If the source database is very large, and you expect the duplication to take more than 2-3 hours, you may want execute it in the background:

chmod 755 run_duplication.sh
nohup ./run_duplication.sh &

When the duplication was finished, the cdb + pdbprod was opened on the remote server. The last lines of the logfile states:

RMAN-06400: database opened
RMAN-06162: sql statement: alter pluggable database all open
RMAN-03091: Finished Duplicate Db at 29-APR-23

Sources:
Duplicating a CDB from the Oracle 19c documentation.

How to use active database duplication for creating a physical standby

When you use active database duplication for creating a physical standby database, make sure of the following:

1. keep your pfile used to start an auxiliary instance to a minimum. Only the following lines are needed:

db_block_size=8192
db_name='proddb01'

Of course, the db_name must be identical to the db_name of the primary database.

2. In your duplication script, make sure you the spfile contains a correct value for db_unique name:

run{
        allocate channel c1 type disk;
        allocate channel c2 type disk;
        allocate channel c3 type disk;
        allocate channel c4 type disk;
        allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
SET db_unique_name='stby01'
SET db_domain='mydomain.no'
SET FAL_CLIENT='stby01'
SET FAL_SERVER='proddb01'
SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stby01'
SET log_archive_dest_2=''
SET control_files='/data1/oradata/stby01/control01.ctl','/data2/fra/stby01/control02.ctl'
SET STANDBY_FILE_MANAGEMENT='MANUAL'
SET LOG_ARCHIVE_MAX_PROCESSES='2'
SET local_listener='stby01.skead.no'
SET dg_broker_config_file1='/sw/oracle/product/12201/dbs/dr1stby01.dat'
SET dg_broker_config_file2='/sw/oracle/product/12201/dbs/dr2stby01.dat'
SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(stby01,proddb01)'
SET diagnostic_dest='/sw/oracle'
SET db_recovery_file_dest='/data2/fra'
SET db_file_name_convert='/data1/oradata/proddb01','/data1/oradata/stby01'
SET log_file_name_convert='/data3/oradata/proddb01','/data3/oradata/stby01'
NOFILENAMECHECK;
}

Note that the directive "spfile" used right before the individual SET commands does not mean "create spfile". It means "fetch spfile from primary". The SET commands, however, will be written to your local spfile which will then be used to start the auxiliary instance once more, before the actual cloning starts.

When the duplicaton has finished, the value of "db_name" parameter will be identical to the primary database, since we are fetching the spfile from your primary database server over the network.

How to use rman to create a backup-based clone on a remote server

It has been a while since I needed to use this technique, since we rely on storage snapshot clones these days. Sometimes though, a good old-fashioned rman clone based on backups is the only way to solve a problem.
Since I always avoid working on the production server during cloning, most steps are done logged onto the auxiliary server.

The method I used is based on "Backup-Based Duplication Without a Target Database and Recovery Catalog Connection"
Here is what I did:

1. On the production server, backup of the source database using RMAN:

rman target / nocatalog backup database plus archivelog;

From this point and onwards, everything is done while logged onto the destination server.

2. Get the files from the source server:

ssh testserver cd /u05 mkdir bup cd bup scp -r prodserver:/u05/flash_recovery_area/PRODDB01/2018_11_16/* .

3. Prepare the auxiliar pfile. Note that the parameters I use exceeds the actual required parameters.
But since the auxiliary instance will replace an already existing database instance, which is already tuned and has the correct memory parameters, I choose to include them. Notice also the db_file_name_convert and log_file_name_convert parameters. They control where rman will place the files during the restore process.

cd $ORACLE_HOME/dbs vi inittest1.ora Add the following: *.db_name='test1' *.db_unique_name='test1' *.audit_file_dest='/u01/oracle/admin/test1/adump' *.audit_trail='DB' *.compatible='12.2.0' *.control_files='/u02/oradata/test1/control01.ctl','/u04/fra/test1/control02.ctl' *.db_block_size=8192 *.db_file_name_convert='proddb01','test1' *.log_file_name_convert='proddb01','test1' *.db_recovery_file_dest='/u05/flash_recovery_area/test1' *.db_recovery_file_dest_size=1000G *.diagnostic_dest='/u01/oracle' *.nls_language='NORWEGIAN' *.nls_territory='NORWAY' *.open_cursors=300 *.optimizer_adaptive_plans=FALSE *.optimizer_dynamic_sampling=0 *.optimizer_mode='ALL_ROWS' *.pga_aggregate_target=7222M *.processes=1500 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=21696M *.shared_pool_size=2624M *.streams_pool_size=256M *.undo_tablespace='UNDOTBS1' *.diagnostic_dest=/u01/oracle

4. I prefer using an spfile over a pfile. Therefore I take the time here to create an spfile:

sqlplus / as sysdba startup nomount pfile=inittest1.ora create spfile from pfile;

5. Startup the auxiliary instance in nomount-mode using the spfile:

shutdown abort startup nomount

6. Start the duplication process:

rman auxiliary / duplicate database to test1 backup location '/u05/fra/bup';

RMAN went through the normal restore, name switching and recovery phases. Finally, the database was opened with the resetlog option.

Step by step: how to create a physical standby database with Oracle 10g

I recently had to create an Oracle 10g standby database to support a simultaneous move and upgrade of the database.

Below are the steps I followed to set it up.

Prerequisites for these steps are
* you have a working tape backup in place, and that you can perform redirected restores.
* you have the same software installed on both servers, in my case it was Oracle EE version 10.2.0.4

By "redirected restores" I mean that you are able to restore database backups through rman to a host different from the one where the backup was taken.
I will not explain the details around details such as standby redo log files, log transportation methods etc; there are plenty of good sources for this to be found on the internet.


1. prepare the primary database parameters:

LOG_ARCHIVE_CONFIG='DG_CONFIG=(proddb01,stby01)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/oracle/admin/proddb01/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=proddb01'
LOG_ARCHIVE_DEST_2='SERVICE=stby01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby01'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=DEFER
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
STANDBY_FILE_MANAGEMENT=AUTO

# The following parameters will only be applicable when the primary is turned into a standby
# We put them there to maker life easier in case of such event
FAL_SERVER=stby01
FAL_CLIENT=proddb01
DB_FILE_NAME_CONVERT='stby01','proddb01'
LOG_FILE_NAME_CONVERT='/u04/oradata/stby01/archive/','/u01/oracle/admin/proddb01/archive/'

2. create standby redo log files on primary database:

First, check the number of redo log groups currently in use:

SQL> select group# ,members, status from v$log;

    GROUP#    MEMBERS STATUS

---------- ---------- ----------------
         4          1 ACTIVE
         5          1 ACTIVE
         6          1 CURRENT

Oracle recommends to add standby redo log file groups with the same size as the largest member in any group. They also recommend that the number of standby redo log groups is the same as the number of redo log groups + 1.
In my case, this means that I will add 4 standby redo log groups of 512M size each:

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u02/oradata/proddb01/stb_redo7.log') size 512M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/u02/oradata/proddb01/stb_redo8.log') size 512M;
SQL> select group# ,members, status from v$log;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/u02/oradata/proddb01/stb_redo9.log') size 512M;

Database altered.

SQL>  ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/u02/oradata/proddb01/stb_redo10.log') size 512M;

Database altered.
Verify that they were created:
SQL> select group#,thread#,sequence#,archived,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
         7          0          0 YES UNASSIGNED
         8          0          0 YES UNASSIGNED
         9          0          0 YES UNASSIGNED
        10          0          0 YES UNASSIGNED
3. enable force logging:
ALTER DATABASE FORCE LOGGING;
4. restart primary database
STARTUP MOUNT
Assuming that you have a valid tape backup solution in place, backup your current control file "for standby" using rman:
rman target / catalog uid/pwd@rmancat
run {
allocate channel c1 type 'sbt_tape' maxpiecesize 4000M;
BACKUP CURRENT CONTROLFILE FOR STANDBY;
release channel c1;
5. Open the database, and switch the current logfile 

This must be done so that the last log file is indeed older than your backup controfile that you created in the previous step:
SQL> ALTER DATABASE OPEN;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';  # so backup is consistent and recoverable
6. Finally, backup the latest archived redo log to tape:
rman target / catalog uid/pwd@rmancat
run {
allocate channel c1 type 'sbt_tape' maxpiecesize 4000M;
BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;
release channel c1;

7. Set up SQL*net connection
...by adding an entry in the primary database's tnsnames.ora:
stby01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = psu0erdb01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stby01)
    )
  )
8. Prepare the standby database parameters. 

# The db_name must from now on match your primary database db_name
db_name='proddb01'
# The db_unique_name is the name to which you will typically refer to the standby database
db_unique_name='stby01'
log_archive_config='DG_CONFIG=(proddb01,stby01)'
log_archive_format=log%t_%s_%r.arc
log_archive_dest_1='LOCATION=/u04/oradata/ stby01/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= stby01'
log_archive_dest_2='SERVICE=proddb01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddb01'
log_archive_dest_state_1=enable
log_archive_dest_state_2=defer
remote_login_passwordfile='EXCLUSIVE'
standby_file_management=auto
fal_server=proddb01
fal_client=stby01
db_file_name_convert='proddb01','stby01'
log_file_name_convert='proddb01','stby01'

 
9. shutdown the to-become standby database, open it in nomount-mode:
shutdown immediate
startup nomount pfile='your_pfile.ora'
Your standby database is now ready to be cloned from your primary. 

10. Create an rman script:

in file duplicate_for_standby.sh
export ORACLE_SID=stby01
export ORACLE_HOME=/u01/oracle/product/10204
export NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
export NLS_LANG=AMERICAN_NORWAY.WE8ISO8859P15
rman target sys/password@proddb01 catalog uid/pwd@rmancat auxiliary / cmdfile='duplicate_for_standby.cmd' log='duplicate_for_standby.log' trace='duplicate_for_standby.trc'
exit
In file duplicate_for_standby.cmd:
run {
ALLOCATE AUXILIARY CHANNEL c1 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c2 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c3 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c4 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c5 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c6 TYPE 'SBT_TAPE';
duplicate target database for standby
dorecover;
}
exit

chmod 755 duplicate_for_standby.sh
11. Start the cloning script in the background:
nohup ./duplicate_for_standby.sh &
Follow the progress with 
tail -f duplicate_for_standby.log
As the directive "dorecover" indicates, rman will create a clone from the primary database, recover it, and leave the recovered database in mounted state.

12. Enable log transport services on the primary database:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
As a recap, this will instruct Oracle to ship logs to LOG_ARCHIVE_DEST_2, which is defined as a service that you have made known to your primary server through an entry in tnsnames.ora:
LOG_ARCHIVE_DEST_2='SERVICE=stby01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby01'

13. Check that your standby is applying logs. 
You can use the script found here

Pay particularly attention that the following section looks like this:
Is the MRP process running?
PROCESS   STATUS
--------- ------------

MRP0      WAIT_FOR_LOG
Crosscheck that with a look at the current archived log status:
set lines 200
col name format a60
alter session set nls_language='american';
alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
SELECT SEQUENCE#,NAME,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,COMPLETION_TIME
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE# ASC
/
The primary and standby should give similar views of the archived log situation:
Standby:
SEQUENCE# NAME                                                      STA ARC APP DEL S COMPLETION_TIME
---------- -------------------------------------------------------- --- --- --- --- - -------------------

75072 /u04/oradata/stby01/archive/log1_75072_681750063.arc          NO  YES YES NO  A 22.10.2014 12:26:07
75073 /u04/oradata/stby01/archive/log1_75073_681750063.arc          NO  YES YES NO  A 22.10.2014 12:26:37
Primary. Notice how the archived logs are sent to two destinations at every log switch:
SEQUENCE# NAME                                                      STA ARC APP DEL S COMPLETION_TIME
---------- -------------------------------------------------------- --- --- --- --- - -------------------

75072 stby01                                                        YES YES YES NO  A 22.10.2014 12:26:06
75072 /u01/oracle/admin/proddb01/archive/1_75072_681750063.arc      NO  YES NO  NO  A 22.10.2014 12:26:08
75073 stby01                                                        YES YES YES NO  A 22.10.2014 12:26:37
75073 /u01/oracle/admin/proddb01/archive/1_75073_681750063.arc      NO  YES NO  NO  A 22.10.2014 12:26:37
From the standby database's alert log, it's clear that logs are being applied:
Wed Oct 22 13:26:13 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75074_681750063.arc
Wed Oct 22 13:26:33 2014
Media Recovery Waiting for thread 1 sequence 75075 (in transit)
Wed Oct 22 13:26:39 2014
Primary database is in MAXIMUM PERFORMANCE mode
RFS[30]: Successfully opened standby log 7: '/u02/oradata/stby01/stb_redo7.log'
Wed Oct 22 13:26:43 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75075_681750063.arc
Media Recovery Waiting for thread 1 sequence 75076 (in transit)
Documentation used 

"Creating a Physical Standby Database"
"Creating a Standby Database with Recovery Manager"

Workaround for RMAN-11003 and ORA-32010 during cloning

At the end of the duplication procedure, my script threw the following error:

RMAN-06162: sql statement: alter system reset  db_unique_name scope=spfile
 RMAN-08031: released channel: c1
 RMAN-08031: released channel: c2
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of Duplicate Db command at 06/12/2014 17:40:14
 RMAN-05501: aborting duplication of target database
 RMAN-03015: error occurred in stored script Memory Script
 RMAN-03009: failure of sql command on clone_default channel at 06/12/2014 17:40:14
 RMAN-11003: failure during parse/execution of SQL statement: alter system reset  db_unique_name scope=spfile
 ORA-32010: ORA-32010: cannot find entry to delete in SPFILE 

 Recovery Manager complete.

After a brief search I found that my init.ora was the culprit: I had accidently left out single quotes in the db_file_name_convert parameter:

*.db_file_name_convert=('/u02/oradata/proddb01/datafile','/u02/oradata/testdb01','/u02/oradata/proddb01/tempfile',/u02/oradata/testdb01')

Oracle Support Dok ID 1384262.1 "ORA-32010: cannot find entry to delete in SPFILE" points this out.

The spfile was not in $OH/dbs physically, although Oracle believes it does:

SQL> show parameter spfile

NAME        TYPE        VALUE
 ---------- ----------- ---------------------------------------------
 spfile     string      /u01/oracle/product/11204/dbs/spfiletestdb01.ora

To prove it, I attempted to create an spfile:

SQL> create spfile from pfile;
 create spfile from pfile
 *
 ERROR at line 1:
 ORA-32002: cannot create SPFILE already being used by the instance 

When attempting to mount:

SQL> alter database mount;
 alter database mount
 *
 ERROR at line 1:
 ORA-01103: database name PRODDB01 in control file is not TESTDB01

So the control files are incorrect, written to during the duplication, and RMAN was unfortunately interrupted before the duplication procedure completed.

Solution:
Recreate the controlfile from the Source database, and open the auxiliary database with this new controlfile instead of the one that was generated during duplication.


1. Log onto the source server on which the PRODDB01 database is hosted, and execute

SQL> alter database backup controlfile to trace as '/tmp/proddb01_cntrfile.sql';

2. Transfer the file /tmp/proddb01_cntrfile.sql to the testserver
3. Shutdown the auxiliary database
4. Edit the script:
* Go to the section that says "Set #2. RESETLOGS case". You can strip away anything above this section, as you won't be needing it.
* Use the physical paths valid on the auxiliary server
* Exchange "REUSE DATABASE" with "SET DATABASE"
From Source database:

STARTUP NOMOUNT
 CREATE CONTROLFILE REUSE DATABASE "PRODDB01" RESETLOGS  ARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 200
     MAXINSTANCES 8
     MAXLOGHISTORY 1168
 LOGFILE
   GROUP 1 '/u02/oradata/proddb01/redo1.log'  SIZE 1000M BLOCKSIZE 512,
   GROUP 2 '/u02/oradata/proddb01/redo2.log'  SIZE 1000M BLOCKSIZE 512,
   GROUP 3 '/u02/oradata/proddb01/redo3.log'  SIZE 1000M BLOCKSIZE 512,
   GROUP 4 '/u02/oradata/proddb01/redo4.log'  SIZE 1000M BLOCKSIZE 512
 -- STANDBY LOGFILE
 DATAFILE
   '/u02/oradata/proddb01/datafile/system.428.688578763',
   '/u02/oradata/proddb01/datafile/undotbs1.489.688574787',
   '/u02/oradata/proddb01/datafile/sysaux.436.688579377'
.
.
.
CHARACTER SET AL32UTF8
;

The new edited version:

STARTUP NOMOUNT
 CREATE CONTROLFILE REUSE
 SET DATABASE "TESTDB01" RESETLOGS  ARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 200
     MAXINSTANCES 8
     MAXLOGHISTORY 1168
 LOGFILE
   GROUP 1 '/u02/oradata/testdb01/redo1.log'  SIZE 1024M BLOCKSIZE 512,
   GROUP 2 '/u02/oradata/testdb01/redo2.log'  SIZE 1024M BLOCKSIZE 512,
   GROUP 3 '/u02/oradata/testdb01/redo3.log'  SIZE 1024M BLOCKSIZE 512,
   GROUP 4 '/u02/oradata/testdb01/redo4.log'  SIZE 1024M BLOCKSIZE 512
 -- STANDBY LOGFILE
 DATAFILE
  '/u02/oradata/testdb01/system.428.688578763',
   '/u02/oradata/testdb01/undotbs1.489.688574787',
   '/u02/oradata/testdb01/sysaux.436.688579377',
   '/u02/oradata/testdb01/users.476.688577593',
   '/u02/oradata/testdb01/undotbs1.510.688564845',
   '/u02/oradata/testdb01/sl_part_data_2006.473.688577939',
.
.
.
CHARACTER SET AL32UTF8
;

5. Run the script on the newly cloned database:

SQL> sqlplus / as sysdba @proddb01_cntrfile.sql
Oracle instance started

 Total System Global Area    5344731136 bytes

 Fixed Size                     2255784 bytes
 Variable Size               1459618904 bytes
 Database Buffers            3875536896 bytes
 Redo Buffers                   7319552 bytes

Control file created.

6. Mount the database

SQL> alter database mount;

Database mounted.

7. Open the database

SQL> alter database open resetlogs;

Database opened.

Finally I had to handle TEMP tablespaces, as the alert log would tell you:

Dictionary check beginning
 Tablespace 'TEMP' #3 found in data dictionary,
 but not in the controlfile. Adding to controlfile.
 *********************************************************************
 WARNING: The following temporary tablespaces contain no files.
          This condition can occur when a backup controlfile has
          been restored.  It may be necessary to add files to these
          tablespaces.  That can be done using the SQL statement:

          ALTER TABLESPACE  ADD TEMPFILE

          Alternatively, if these temporary tablespaces are no longer
          needed, then they can be dropped.
            Empty temporary tablespace: TEMP

Add a file to the temporary tablespace using the REUSE clause:

SQL> alter tablespace temp add tempfile '/u02/oradata/testdb01/temp01.dbf' size 1024M reuse autoextend on next 32M maxsize unlimited;

Examples of RMAN duplicate database scripts

I prefer to set the parameters db_file_name_convert and log_file_name_convert whenever possible, to simplify the redirection of restored files.

Here is an example when parameter log_file_name_convert is not set. We are also specifying the auxiliary channels with vendor specific tape settings:

run { 
allocate auxiliary channel t1 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64; 
allocate auxiliary channel t2 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64;
send 'NB_ORA_POLICY=mypolicy,NB_ORA_CLIENT=myclient-bkp.mydomain,NB_ORA_SCHED=myschedule'; 
set until scn 5886541624244; 
duplicate target database to AUXDB1 
logfile group 1 ('/data/oracle/u01/AUXDB1/redo01.log') size 128M, 
        group 2 ('/data/oracle/u02/AUXDB1/redo02.log') size 128M, 
        group 3 ('/data/oracle/u03/AUXDB1/redo03.log') size 128M;
} 

Another example, when both db_file_name_convert and log_file_name_convert are set.
In smaller environments, you may not need to bother specifying different backup polices etc, you simply specify 'SBT_TAPE' and the

run {
allocate auxiliary channel c1 type 'SBT_TAPE';
allocate auxiliary channel c2 type 'SBT_TAPE';
set until time "to_date('01.05.2014 18:00:00','DD.MM.YYYY HH24:MI:SS')";
duplicate target database to AUXDB1; 
}

How to set the db_file_name_convert and log_file_name_convert parameters

In this example, I want to redirect the files to a slightly different path during a clone from prod to test.

In prod, files are to be found under:

* /u02/oradata/proddb01/datafile

In test, I want to be placed under:

* /u02/oradata/testdb01

Furthermore, some tempfiles are placed differently than regular datafiles in prod. In test I do not need or want multiple destinations; all files should be placed under /u02/oradata/testdb01. Therefore, my db_file_name_convert parameter must have multiple pairs of source and target locations.

For the log files, they could all be placed under similar locations, so the redirection string can simply contain the only thing that will differ: the ORACLE_SID.

When using a pfile:

db_file_name_convert=('/u02/oradata/proddb01/datafile','/u02/oradata/testdb01',
                      '/u02/oradata/proddb01/tempfile',/u02/oradata/testdb01')

log_file_name_convert=('proddb01','testdb01')

When using an spfile:

alter system set db_file_name_convert='/u02/oradata/proddb01/datafile','/u02/oradata/testdb01',
                                      '/u02/oradata/proddb01/tempfile',/u02/oradata/testdb01' scope=spfile;

alter system set log_file_name_convert='proddb01','testdb01' scope=spfile;

It is also supported to use the log_file_name_convert multiple times in the parameter file, like this:

log_file_name_convert=('/u01/app/oracle/oradata/proddb01/onlinelog/','/u04/oradata/testdb01/onlinelog/')
log_file_name_convert=('/u01/app/oracle/flash_recovery_area/proddb01/onlinelog/','/u04/oradata/testdb01/onlinelog/')

Oracle Docs:
db_file_name_convert
log_file_name_convert

How to perform a clone from active database

With Oracle 11g, a new method to clone databases was introduced: "Clone from Active Database".
This method allows for cloning of a database using RMAN's DUPLICATE command, but without being dependent on any previous backups being available.

The following are my notes on how to use this feature to produce a clone from production database PRODDB01 to test database TESTDB01.

In file run_duplication.cmd:

connect catalog cataloguser/password@RMANCAT
connect target sys/clonedb@PRODDB01
connect auxiliary sys/clonedb@TESTDB01;
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
configure device type disk parallelism 2;
run {
debug io;
DUPLICATE TARGET DATABASE TO TESTDB01
FROM ACTIVE DATABASE
SKIP TABLESPACE TOOLS;
debug off;
}
exit

to execute:

rman cmdfile='run_duplication.cmd' debug trace='run_duplication.trc' log='run_duplication.log'


For this to work smoothly I followed the principles below:

• Script executed from the target server
• Auxiliary database must be in NOMOUNT mode
• Sys passwords must be identical on both source and target.
• Use the notation uid/pwd@<ORACLE_SID> when you Connect to both target and auxiliary. Do not use operating system authenticaion ( connect / ) - it will throw an error
• The db_block_size parameter must be identical on both source and target
• TNS connection must exist on both sides e.g. communication must be able to go from and from the source and the target. If not you will see the error

RMAN-03002: failure of Duplicate Db command at 07/21/2015 10:06:15
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on c1 channel at 07/21/2015 10:06:15
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server

• I set the db_file_name_convert and the log_file_name_convert parameter in the auxiliary database. Make sure you check the location of the tempfiles, too, and include this in the db_file_name_convert value.
• I had no success using the TABLESPACE clause (which means an implicit EXCLUDE of all other tablespaces except those named explicitly)
• Using SKIP TABLESPACE ,, etc works, if the tablespaces are self-contained, meaning there are no referential constraints from object in one tablespace to another (even if both are skipped!)

Remember to reregister the duplicated database if it's backed up using a catalog database:

RMAN> connect target /
RMAN> list incarnation of database;
RMAN> register database;
RMAN> list incarnation of database;


The prerequisites for ACTIVE DATABASE cloning as laid out in the Oracle documentation are:

• At least one normal target channel and at least one AUXILIARY channel are required.
• When you connect RMAN to the source database as TARGET, you must specify a password, even if RMAN uses operating system authentication.
• The source database must be mounted or open. If the source database is open, then archiving must be enabled.
• If the source database is not open, then it must have been shut down consistently.
• When you connect RMAN to the auxiliary instance, you must provide a net service name. This requirement applies even if the auxiliary instance is on the local host.
• The source database and auxiliary instances must use the same SYSDBA password, which means that both instances must have password files.
• You cannot use the UNTIL clause when performing active database duplication. RMAN chooses a time based on when the online data files have been completely copied, so that the data files can be recovered to a consistent point in time.

How to check that an Oracle database parameter is set in a unix shell script

Probably many ways to do the same thing, but I tend to spool database information to disk, then assign the values found to shell script variables, as in the following example, where I need to determined whether the parameters db_file_name_convert and log_file_name_convert is set in the database:

###############################################################
# Check that the db_convert_file_name and log_file_name_convert
# are both set. If not, abort script.
###############################################################
TRG_DB=${ORACLE_SID}
cat << EoF > check_${TRG_DB}_convert_params.sql
set termout off
set lines 200
set trimspool on
set heading off
set pages 0
set feedback off
set echo off
set verify off
col name format a30
col value format a40
spool check_${TRG_DB}_convert_params.lst
select name,value
from v\$parameter
where name in ('db_file_name_convert','log_file_name_convert');
exit
EoF
sqlplus -s / as sysdba @check_${TRG_DB}_convert_params.sql
CONV_DATA_FILE_SET=`cat check_${TRG_DB}_convert_params.lst | grep db_file | awk '{print $2}'`
CONV_LOG_FILE_SET=`cat check_${TRG_DB}_convert_params.lst | grep log_file | awk '{print $2}'`

case ${CONV_DATA_FILE_SET} in
'') echo db_file_name_convert is not set. Correct and retry operation.;exit;;
*) echo db_file_name_convert is set. Continuing...;;
esac

case ${CONV_LOG_FILE_SET} in
'') echo log_file_name_convert is not set. Correct and retry operation.;exit;;
*) echo log_file_name_convert is set. Continuing...;;
esac
exit

How to use the DUPLICATE ... FOR STANDBY command to create a physical standby database

run {
allocate auxiliary channel t1 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64;
allocate auxiliary channel t2 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64;
send 'NB_ORA_POLICY=mypolicy,NB_ORA_CLIENT=myclient-bkp.mydomain,NB_ORA_SCHED=my_schedule';
allocate auxiliary channel aux1 device type disk;
set until logseq=l2345 thread=1;
duplicate target database for standby dorecover;
release channel t1;
release channel t2;
}

Note that the parameters db_file_name_convert and log_file_name_convert must be set in order to use this simplified syntax.
If not, you must specify file location for all the involved files and redo log members.