Last week, I had to refresh a training database with data from production. In order to do this, I used Active Database Duplication from ASM to disk. This feature, introduced in 11g, allows you to duplicate a database from a live database instead of from the RMAN backup.

The datafiles for this database are on a filesystem. We are in the process of migrating the databases to new servers and moving them to ASM. I wanted to reduce the RMAN impact on production by using one of the test databases that are running on ASM as the source for the duplication. This meant moving the datafiles of the test database from ASM to disk.

Here is the RMAN script to duplicate the database from ASM to the filesystem. Note that I have removed references to the actual database name, removed the application specific datafiles and gave generic database and filesystem names.

connect target sys@prod
connect auxiliary sys@DEV

run {
set newname for datafile 1 to '/dev_01/oradata/DEV/system01.dbf';
set newname for datafile 2 to '/dev_02/oradata/DEV/sysaux01.dbf';
set newname for datafile 3 to '/dev_03/oradata/DEV/undotbs01.dbf';
set newname for datafile 4 to '/dev_04/oradata/DEV/users01.dbf';
set newname for datafile 57 to '/dev_05/oradata/DEV/undotbs02.dbf';
set newname for datafile 81 to '/dev_03/oradata/DEV/undotbs031.dbf';
set newname for datafile 82 to '/dev_05/oradata/DEV/undotbs04.dbf';
set newname for datafile 83 to '/dev_03/oradata/DEV/undotbs05.dbf';
set newname for datafile 84 to '/dev_05/oradata/DEV/undotbs06.dbf';
set newname for datafile 85 to '/dev_03/oradata/DEV/undotbs07.dbf';
set newname for datafile 86 to '/dev_05/oradata/DEV/undotbs08.dbf';
set newname for datafile 87 to '/dev_03/oradata/DEV/undotbs09.dbf';
set newname for datafile 88 to '/dev_05/oradata/DEV/undotbs10.dbf';
set newname for datafile 116 to '/dev_05/oradata/DEV/system02.dbf';
set newname for tempfile 1 to '/dev_05/oradata/DEV/temp01.dbf';
set newname for tempfile 2 to '/dev_01/oradata/DEV/temp02.dbf';
set newname for tempfile 3 to '/dev_03/oradata/DEV/temp03.dbf';
set newname for tempfile 4 to '/dev_02/oradata/DEV/temp04.dbf';
set newname for tempfile 5 to '/dev_01/oradata/DEV/temp05.dbf';
duplicate target database to DEV
from active database
spfile parameter_value_convert 'PROD','DEV'
 set sga_max_size '10G'
 set sga_target '10G'
 set control_files '/dev_01/oradata/DEV/control01.ctl', '/dev_02/oradata/DEV/control02.ctl', '/dev_03/oradata/DEV/control03.ctl'
 set diagnostic_dest '/dev_00/orasw/app/oracle'
 set db_recovery_file_dest '/dev_00/orasw/app/oracle/fra
 set audit_file_dest '/dev_00/orasw/app/oracle/admin/DEV/adump'
 set log_archive_dest_1 'location=/dev_06/oraarch/DEV'
LOGFILE
 GROUP 1 ('/dev_01/oradata/DEV/redo01_m1.log',
          '/dev_02/oradata/DEV/redo01_m2.log') SIZE 1024m REUSE,
 GROUP 2 ('/dev_02/oradata/DEV/redo02_m1.log',
          '/dev_03/oradata/DEV/redo02_m2.log') SIZE 1024m REUSE,
 GROUP 3 ('/dev_03/oradata/DEV/redo03_m1.log',
          '/dev_04/oradata/DEV/redo03_m2.log') SIZE 1024m REUSE,
 GROUP 4 ('/dev_01/oradata/DEV/redo04_m1.log',
          '/dev_02/oradata/DEV/redo04_m2.log') SIZE 1024m REUSE
nofilenamecheck;
}

The duplication will bring a copy of the spfile from the source. The set commands allow us to change any parameters for the new database as it is being copied.

After we have the script in place, you can follow the standard duplication process to refresh the database.