Oracle DBA Tips Corner
Return to the Oracle DBA Tips Corner.
|
Oracle DBA Tips Corner
|
|
Duplicating a Database using RMAN - (UNIX / Linux)
by Jeff Hunter, Sr. Database Administrator
Contents
Overview
Please keep in mind
that this article should not be considered
a substitution for completing reading and understanding the official
documentation and release notes from Oracle. The following links can be used to
download the Recovery Manager User's Guide for Oracle9i (9.2.0):
It is a common request to create a duplicated database from a
production database. This
can be easily achieved using the RMAN DUPLICATE command. Using the
RMAN DUPLICATED command, the DBA can create a duplicate database
from a target database's backup while still retaining the original
target database. While it is possible to create a duplicate database
that contains only a subset of the original database (by tablespace), this article
will demonstrate how to create a completely identical database.
Using this method allows you to test backup and recovery procedures
as well as exporting data such as a table that was inadvertently dropped from the production
database, and then importing it back into the production database.
Pre-requisites
Steps Required
Copy the initialization parameter from the target database and make the
necessary changes for the duplicated database.
Create all needed directories for the duplicate database:
Modify both the listener.ora and tnsnames.ora file to be able
to connect to the auxiliary database. After making changes to the networking files,
test the connection keeping in mind that you must be able to connect to the
auxiliary instance with SYSDBA privileges, so a valid password file must exist.
As mentioned in the pre-requisites section of this article, the target
database should be either opened or mounted.
As mentioned in the pre-requisites section of this article, ensure that you have
a current backup that you wish to use to create the duplicate database. Login
to query the RMAN catalog:
The following RUN block can be used to fully duplicate the target database
from the latest full backup.
duplicate target database to TESTDB until time 'SYSDATE-1';.
In almost all cases, you will need to create the tempfiles for your temporary
tablespace:
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter
and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express,
prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate,
but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from
relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
This article provides a quick introduction on how to duplicate
a database using the RMAN DUPLICATE DATABASE command on
the UNIX / Linux operating environment. For this demonstration, we will
create a duplicate target database using RMAN's DUPLICATE DATABASE
command to the same host (localhost) of the target database.
Here is a short introduction to some of the configuration parameters that will
be used for installing the Oracle RDBMS Software:
Oracle9i Database Release 2 Documentation
Oracle9i Recovery Manager User's Guide - (A96566-01)
Operating Environment
Red Hat Linux - Fedora Core 2
Oracle Release / Version
9.2.0.5.0 Enterprise Edition
ORACLE_BASE
/u01/app/oracle
ORACLE_HOME
/u01/app/oracle/product/9.2.0
Target Database SID
ORA920
Duplicate Database SID
TESTDB
RMAN Catalog Database
No recovery catalog. Using control file.
Archive Log Mode
Enabled
log_archive_dest_1
location=/u06/app/oradata/ORA920/archive mandatory
log_archive_dest_state_1
enable
log_archive_format
arch_t%t_s%s.dbf
log_archive_start
true
RMAN Default Configuration Settings
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/orabackup/ORA920/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/orabackup/ORA920/backup_DB_%d_S_%s_P_%s_T_%t';
RMAN configuration has no stored or default parameters
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/9.2.0/dbs/snapcf_ORA920.f'; # default
A valid full database backup of the target database
Target database must be mounted or open
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
14 B A A DISK 03-NOV-04 1 1 TAG20041103T163334
15 B F A DISK 03-NOV-04 1 1 TAG20041103T163336
16 B A A DISK 03-NOV-04 1 1 TAG20041103T163651
17 B F A DISK 03-NOV-04 1 1
$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.5.0 - Production on Wed Nov 3 16:42:34 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup open
ORACLE instance started.
Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> exit
$ orapwd file=/u01/app/oracle/product/9.2.0/dbs/orapwTESTDB password=change_on_install
$ export ORACLE_SID=ORA920
$ sqlplus "/ as sysdba"
SQL> create pfile='/u01/app/oracle/product/9.2.0/dbs/initTESTDB.ora' from spfile;
File created.
After creating the initialization parameter for the duplicate database, change
at least the following parameters:
db_file_name_convert = ('/u06/app/oradata/ORA920', '/u06/app/oradata/TESTDB')
log_file_name_convert = ('/u03/app/oradata/ORA920', '/u03/app/oradata/TESTDB',
'/u04/app/oradata/ORA920', '/u04/app/oradata/TESTDB',
'/u05/app/oradata/ORA920', '/u05/app/oradata/TESTDB')
control_files = '/u03/app/oradata/TESTDB/control01.ctl'
, '/u04/app/oradata/TESTDB/control02.ctl'
, '/u05/app/oradata/TESTDB/control03.ctl'
db_name = 'TESTDB'
instance_name = 'TESTDB'
audit_file_dest = '/u01/app/oracle/admin/TESTDB/adump'
background_dump_dest = '/u01/app/oracle/admin/TESTDB/bdump'
core_dump_dest = '/u01/app/oracle/admin/TESTDB/cdump'
user_dump_dest = '/u01/app/oracle/admin/TESTDB/udump'
service_names = 'TESTDB.IDEVELOPMENT.INFO'
dispatchers = '(PROTOCOL=TCP) (SERVICE=TESTDBXDB)'
log_archive_dest_1 = 'location=/u06/app/oradata/TESTDB/archive mandatory'
$ mkdir /u01/app/oracle/admin/TESTDB
$ mkdir /u01/app/oracle/admin/TESTDB/adump
$ mkdir /u01/app/oracle/admin/TESTDB/bdump
$ mkdir /u01/app/oracle/admin/TESTDB/cdump
$ mkdir /u01/app/oracle/admin/TESTDB/create
$ mkdir /u01/app/oracle/admin/TESTDB/pfile
$ mkdir /u01/app/oracle/admin/TESTDB/scripts
$ mkdir /u01/app/oracle/admin/TESTDB/udump
$ mkdir /u03/app/oradata/TESTDB
$ mkdir /u04/app/oradata/TESTDB
$ mkdir /u05/app/oradata/TESTDB
$ mkdir /u06/app/oradata/TESTDB
$ mkdir /u06/app/oradata/TESTDB/archive
$ export ORACLE_SID=TESTDB
$ sqlplus "/ as sysdba"
SQL> startup nomount
$ sqlplus "sys/change_on_install@TESTDB as sysdba"
SQL*Plus: Release 9.2.0.5.0 - Production on Wed Nov 3 17:43:22 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL>
$ export ORACLE_SID=ORA920
$ sqlplus "/ as sysdba"
SQL> startup open
$ rman target sys/change_on_install@ORA920
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
14 B A A DISK 03-NOV-04 1 1 TAG20041103T163334
15 B F A DISK 03-NOV-04 1 1 TAG20041103T163336
16 B A A DISK 03-NOV-04 1 1 TAG20041103T163651
17 B F A DISK 03-NOV-04 1 1
$ rman target sys/change_on_install@ORA920 auxiliary sys/change_on_install@TESTDB
Recovery Manager: Release 9.2.0.5.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORA920 (DBID=2542332757)
connected to auxiliary database: TESTDB (not mounted)
RMAN>
Note that you can duplicate the database to a specific date/time using
the UNTIL TIME '<DATE>' clause. For example, to
duplicate the new database to yesterdays date/time, use the following:
run {
# Allocate the channel for the duplicate work
allocate auxiliary channel ch1 type disk;
# Duplicate the database to TESTDB
duplicate target database to TESTDB;
}
using target database controlfile instead of recovery catalog
allocated channel: ch1
channel ch1: sid=13 devtype=DISK
Starting Duplicate Db at 03-NOV-04
printing stored script: Memory Script
{
set until scn 14757706;
set newname for datafile 1 to "/u06/app/oradata/TESTDB/system01.dbf";
set newname for datafile 2 to "/u06/app/oradata/TESTDB/undotbs1_01.dbf";
set newname for datafile 3 to "/u06/app/oradata/TESTDB/cwmlite01.dbf";
set newname for datafile 4 to "/u06/app/oradata/TESTDB/drsys01.dbf";
set newname for datafile 5 to "/u06/app/oradata/TESTDB/indx01.dbf";
set newname for datafile 6 to "/u06/app/oradata/TESTDB/odm01.dbf";
set newname for datafile 7 to "/u06/app/oradata/TESTDB/tools01.dbf";
set newname for datafile 8 to "/u06/app/oradata/TESTDB/users01.dbf";
set newname for datafile 9 to "/u06/app/oradata/TESTDB/xdb01.dbf";
set newname for datafile 10 to "/u06/app/oradata/TESTDB/example01.dbf";
set newname for datafile 11 to "/u06/app/oradata/TESTDB/perfstat01.dbf";
set newname for datafile 12 to "/u06/app/oradata/TESTDB/users02.dbf";
restore
check readonly
clone database
;
}
executing script: Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 03-NOV-04
channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u06/app/oradata/TESTDB/system01.dbf
restoring datafile 00002 to /u06/app/oradata/TESTDB/undotbs1_01.dbf
restoring datafile 00003 to /u06/app/oradata/TESTDB/cwmlite01.dbf
restoring datafile 00004 to /u06/app/oradata/TESTDB/drsys01.dbf
restoring datafile 00005 to /u06/app/oradata/TESTDB/indx01.dbf
restoring datafile 00006 to /u06/app/oradata/TESTDB/odm01.dbf
restoring datafile 00007 to /u06/app/oradata/TESTDB/tools01.dbf
restoring datafile 00008 to /u06/app/oradata/TESTDB/users01.dbf
restoring datafile 00009 to /u06/app/oradata/TESTDB/xdb01.dbf
restoring datafile 00010 to /u06/app/oradata/TESTDB/example01.dbf
restoring datafile 00011 to /u06/app/oradata/TESTDB/perfstat01.dbf
restoring datafile 00012 to /u06/app/oradata/TESTDB/users02.dbf
channel ch1: restored backup piece 1
piece handle=/orabackup/ORA920/backup_DB_ORA920_S_16_P_16_T_541269216 tag=TAG20041103T163336 params=NULL
channel ch1: restore complete
Finished restore at 03-NOV-04
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 5
MAXDATAFILES 600
MAXINSTANCES 10
MAXLOGHISTORY 1134
LOGFILE
GROUP 1 ( '/u03/app/oradata/TESTDB/redo_g01a.log', '/u04/app/oradata/TESTDB/redo_g01b.log', '/u05/app/oradata/TESTDB/redo_g01c.log' ) SIZE 104857600
REUSE,
GROUP 2 ( '/u03/app/oradata/TESTDB/redo_g02a.log', '/u04/app/oradata/TESTDB/redo_g02b.log', '/u05/app/oradata/TESTDB/redo_g02c.log' ) SIZE 104857600
REUSE,
GROUP 3 ( '/u03/app/oradata/TESTDB/redo_g03a.log', '/u04/app/oradata/TESTDB/redo_g03b.log', '/u05/app/oradata/TESTDB/redo_g03c.log' ) SIZE 104857600
REUSE
DATAFILE
'/u06/app/oradata/TESTDB/system01.dbf'
CHARACTER SET WE8ISO8859P1
printing stored script: Memory Script
{
switch clone datafile all;
}
executing script: Memory Script
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=541274660 filename=/u06/app/oradata/TESTDB/undotbs1_01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=541274660 filename=/u06/app/oradata/TESTDB/cwmlite01.dbf
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=541274660 filename=/u06/app/oradata/TESTDB/drsys01.dbf
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=541274660 filename=/u06/app/oradata/TESTDB/indx01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=5 stamp=541274660 filename=/u06/app/oradata/TESTDB/odm01.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=6 stamp=541274660 filename=/u06/app/oradata/TESTDB/tools01.dbf
datafile 8 switched to datafile copy
input datafilecopy recid=7 stamp=541274660 filename=/u06/app/oradata/TESTDB/users01.dbf
datafile 9 switched to datafile copy
input datafilecopy recid=8 stamp=541274660 filename=/u06/app/oradata/TESTDB/xdb01.dbf
datafile 10 switched to datafile copy
input datafilecopy recid=9 stamp=541274660 filename=/u06/app/oradata/TESTDB/example01.dbf
datafile 11 switched to datafile copy
input datafilecopy recid=10 stamp=541274660 filename=/u06/app/oradata/TESTDB/perfstat01.dbf
datafile 12 switched to datafile copy
input datafilecopy recid=11 stamp=541274660 filename=/u06/app/oradata/TESTDB/users02.dbf
printing stored script: Memory Script
{
set until scn 14757706;
recover
clone database
delete archivelog
;
}
executing script: Memory Script
executing command: SET until clause
Starting recover at 03-NOV-04
starting media recovery
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=151
channel ch1: restored backup piece 1
piece handle=/orabackup/ORA920/backup_DB_ORA920_S_17_P_17_T_541269412 tag=TAG20041103T163651 params=NULL
channel ch1: restore complete
archive log filename=/u06/app/oradata/TESTDB/archive/arch_t1_s151.dbf thread=1 sequence=151
channel clone_default: deleting archive log(s)
archive log filename=/u06/app/oradata/TESTDB/archive/arch_t1_s151.dbf recid=1 stamp=541274663
media recovery complete
Finished recover at 03-NOV-04
printing stored script: Memory Script
{
shutdown clone;
startup clone nomount ;
}
executing script: Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 5
MAXDATAFILES 600
MAXINSTANCES 10
MAXLOGHISTORY 1134
LOGFILE
GROUP 1 ( '/u03/app/oradata/TESTDB/redo_g01a.log', '/u04/app/oradata/TESTDB/redo_g01b.log', '/u05/app/oradata/TESTDB/redo_g01c.log' ) SIZE 104857600
REUSE,
GROUP 2 ( '/u03/app/oradata/TESTDB/redo_g02a.log', '/u04/app/oradata/TESTDB/redo_g02b.log', '/u05/app/oradata/TESTDB/redo_g02c.log' ) SIZE 104857600
REUSE,
GROUP 3 ( '/u03/app/oradata/TESTDB/redo_g03a.log', '/u04/app/oradata/TESTDB/redo_g03b.log', '/u05/app/oradata/TESTDB/redo_g03c.log' ) SIZE 104857600
REUSE
DATAFILE
'/u06/app/oradata/TESTDB/system01.dbf'
CHARACTER SET WE8ISO8859P1
printing stored script: Memory Script
{
catalog clone datafilecopy "/u06/app/oradata/TESTDB/undotbs1_01.dbf";
catalog clone datafilecopy "/u06/app/oradata/TESTDB/cwmlite01.dbf";
catalog clone datafilecopy "/u06/app/oradata/TESTDB/drsys01.dbf";
catalog clone datafilecopy "/u06/app/oradata/TESTDB/indx01.dbf";
catalog clone datafilecopy "/u06/app/oradata/TESTDB/odm01.dbf";
catalog clone datafilecopy "/u06/app/oradata/TESTDB/tools01.dbf";
catalog clone datafilecopy "/u06/app/oradata/TESTDB/users01.dbf";
catalog clone datafilecopy "/u06/app/oradata/TESTDB/xdb01.dbf";
catalog clone datafilecopy "/u06/app/oradata/TESTDB/example01.dbf";
catalog clone datafilecopy "/u06/app/oradata/TESTDB/perfstat01.dbf";
catalog clone datafilecopy "/u06/app/oradata/TESTDB/users02.dbf";
switch clone datafile all;
}
executing script: Memory Script
cataloged datafile copy
datafile copy filename=/u06/app/oradata/TESTDB/undotbs1_01.dbf recid=1 stamp=541274721
cataloged datafile copy
datafile copy filename=/u06/app/oradata/TESTDB/cwmlite01.dbf recid=2 stamp=541274721
cataloged datafile copy
datafile copy filename=/u06/app/oradata/TESTDB/drsys01.dbf recid=3 stamp=541274721
cataloged datafile copy
datafile copy filename=/u06/app/oradata/TESTDB/indx01.dbf recid=4 stamp=541274721
cataloged datafile copy
datafile copy filename=/u06/app/oradata/TESTDB/odm01.dbf recid=5 stamp=541274721
cataloged datafile copy
datafile copy filename=/u06/app/oradata/TESTDB/tools01.dbf recid=6 stamp=541274721
cataloged datafile copy
datafile copy filename=/u06/app/oradata/TESTDB/users01.dbf recid=7 stamp=541274721
cataloged datafile copy
datafile copy filename=/u06/app/oradata/TESTDB/xdb01.dbf recid=8 stamp=541274721
cataloged datafile copy
datafile copy filename=/u06/app/oradata/TESTDB/example01.dbf recid=9 stamp=541274721
cataloged datafile copy
datafile copy filename=/u06/app/oradata/TESTDB/perfstat01.dbf recid=10 stamp=541274721
cataloged datafile copy
datafile copy filename=/u06/app/oradata/TESTDB/users02.dbf recid=11 stamp=541274721
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=541274721 filename=/u06/app/oradata/TESTDB/undotbs1_01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=541274721 filename=/u06/app/oradata/TESTDB/cwmlite01.dbf
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=541274721 filename=/u06/app/oradata/TESTDB/drsys01.dbf
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=541274721 filename=/u06/app/oradata/TESTDB/indx01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=5 stamp=541274721 filename=/u06/app/oradata/TESTDB/odm01.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=6 stamp=541274721 filename=/u06/app/oradata/TESTDB/tools01.dbf
datafile 8 switched to datafile copy
input datafilecopy recid=7 stamp=541274721 filename=/u06/app/oradata/TESTDB/users01.dbf
datafile 9 switched to datafile copy
input datafilecopy recid=8 stamp=541274721 filename=/u06/app/oradata/TESTDB/xdb01.dbf
datafile 10 switched to datafile copy
input datafilecopy recid=9 stamp=541274721 filename=/u06/app/oradata/TESTDB/example01.dbf
datafile 11 switched to datafile copy
input datafilecopy recid=10 stamp=541274721 filename=/u06/app/oradata/TESTDB/perfstat01.dbf
datafile 12 switched to datafile copy
input datafilecopy recid=11 stamp=541274721 filename=/u06/app/oradata/TESTDB/users02.dbf
printing stored script: Memory Script
{
Alter clone database open resetlogs;
}
executing script: Memory Script
database opened
Finished Duplicate Db at 03-NOV-04
RMAN> exit
$ export ORACLE_SID=TESTDB
$ sqlplus "/ as sysdba"
SQL> alter tablespace temp add tempfile '/u06/app/oradata/TESTDB/temp01.dbf'
2 size 524288000 reuse autoextend on next 524288000 maxsize 1500M;
Tablespace altered.
Thursday, 31-Aug-2006 13:09:02 EDT
Page Count: 58