Duplicate database (Current incarnation)

To proceed with this tutorial you need to have target database. If you want to duplicate database to completely new target you have to create empty database first. To do this complete first 3 sections from Create a new database page (1 – Set the SID, 2 – Create new password file and 3 – Create new parameter files).

Check space requirements

On source system connect to SQL and query data and temp files for used space. Example of SQL command (including temporary tablespace):

SELECT SUM(bytes/1024/1024) "MB" FROM (SELECT bytes FROM dba_data_files UNION ALL SELECT bytes FROM dba_temp_files);

Example of SQL command (excluding temporary tablespace, after duplication you can add new tempfiles with desired size):

SELECT SUM(bytes/1024/1024) "MB" FROM dba_data_files;wiki oracle duplicate database current incarnation

On target system check on operating system how much space is available on target path. Example of UNIX command:

bdf /oracle/DATAwiki oracle duplicate database current incarnation

Duplicate the database

Modify the pfile/spfile (OPTIONAL)

You can insert the parameters in the target database’s pfile/spfile that will make the database to convert its files name/path during the duplication. For datafiles conversion is used parameter “db_file_name_convert” and for logfiles conversion is used parameter “log_file_name_convert”. The syntax for both is like =(,,,,…). Example from pfile:

...
*.db_file_name_convert=('/or0700021/a05a/WOST/','/or0700275/a05a/ZTEST/','/or0700021/a06a/WOST/','/or0700275/a06a/ZTEST/')
*.log_file_name_convert=('/or0700021/a03a/WOST/','/or0700275/a03a/ZTEST/','/or0700021/a03b/WOST/','/or0700275/a03b/ZTEST/')
...

This step is optional and you need it only in case you want to change the path or name of the files (source and target are on same host, naming conventions, … etc). The same results can be accomplished with the duplicate command “SET NEWNAME” (see section Run the duplication).

Start the target database

You have to startup the target database in “nomount” state. Example of SQL command:

startup force nomount;

Connect to source with RMAN

On target system you need to have entry for source in tnsnames.ora file. Example of UNIX command to test connection to source database:

tnsping <SOURCE SID>

Example of connection test to source database:

tnsping WOSTwiki oracle duplicate database current incarnation

If the tnsping is not successful you have to adjust your tnsnames.ora file. Check link Create a new database: Create new entry in tnsnames.ora to see how to do this. If connection is successful then connect to source database with RMAN. Example of UNIX command:

rman target <USERID>/<PASSWORD>@<SOURCE SID>wiki oracle duplicate database current incarnation

Connect auxiliary to target

To create auxiliary connection to target database execute following RMAN command within the same RMAN session:

connect auxiliary /;wiki oracle duplicate database current incarnation

Run the duplication

Execute the duplicate target database command. Example of RMAN command:

run
{
allocate auxiliary channel t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/oracle/product/10.2.0/admin/backup/tdpo_WOST.opt)';
set until time '2012-08-05:03:00:00';
duplicate target database to ZTEST;
release channel t1;
}

In above example I have first allocated the auxiliary channel of tape type (I have to restore from tapes) with Tivoli parameters file (we are using Tivoli Storage Management tool for backups).

As second I have set the until time parameter because I needed a copy the database from exact time. This time has to be higher then the time of the end of relevant backup (My backup ended few minutes after 2am so I choose 3am) otherwise your recovery won’t succeed.

If you skipped the optional step and did not modified the pfile/spfile parameters to convert the database file paths, you can add the commands in run code to set new path/filenames for restored datafiles. The run command then might look like this:

run
{
set newname for datafile 1 to '/or0700275/a06a/ZTEST/system01.dbf';
... -|other datafiles|- ...
set newname for logfile '/or0700021/a03a/WOST/redo01a.log' to '/or0700275/a03a/ZTEST/redo01a.log';
... -|other logfiles|- ...
allocate auxiliary channel t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/oracle/product/10.2.0/admin/backup/tdpo_WOST.opt)';
set until time '2012-08-05:03:00:00';
duplicate target database to ZTEST;
release channel t1;
}

Recover the database

If for whatever reason your database was not recovered after duplication you have to recover it manually before opening it. Before running the database recovery you have to restore offline redologs. Example of RMAN command:

run
{
set archivelog destination to '/or0700275/a02a/ZTEST/arch';
allocate auxiliary channel t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/oracle/product/10.2.0/admin/backup/tdpo_WOST.opt)';
restore (archivelog low logseq 58041 high logseq 58052 thread 1 all);
release channel t1;
}

In above example I have first set the destination where the logs will be restored, then again allocated the auxiliary channel and executed the restore command to restore 12 offline redolog files from number 58041 until number 58052.

When all necessary redo log files are restored you can execute the database recovery. Example of SQL command:

recover database using backup controlfile until time '2012-08-05:03:00:00';

After successful recovery you have to opend the database with “resetlogs” option. Example of SQL command:

alter database open resetlogs;

Post-duplication actions

Re-create temp file

Tempfiles of temporary tablespaces are not being backed up so you have to re-create them after duplication. Example of SQL command:

alter tablespace TEMP add tempfile '/or0700275/a04a/ZTEST/temp01.dbf' size 7000M;

Leave a Reply