23 November 2012

Step by Step to create Duplicate database from active database10g using Rman

Step by Step create Rman Duplicate from active database10g


RMAN 'Duplicate From Active Database' Feature in 11G [ID 452868.1]

Creating a Duplicate Database on a New Host. [ID 388431.1]

1. Instance Oracle 10G database

2, Enable archive log mode

3, Prepare targetinstance

4, modify pfile db_file_name_convert,log_file_name_convert

# Set the below for the from and to location for all data files / redo
# logs to be cloned.
#db_file_name_convert =("source", "target")
db_file_name_convert =("/oradata/PROD/", "/oradata/AUX/") 

#log_file_name_convert =("source", "target")
log_file_name_convert =("/oradata/PROD/", "/oradata/AUX/")
#Set the below to the same as the production target

[oradev@muthu dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 30 13:44:43 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area  150667264 bytes
Fixed Size                  1342736 bytes
Variable Size              96469744 bytes
Database Buffers           46137344 bytes
Redo Buffers                6717440 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oradev@muthu dbs]$ rman target sys/***@oracle3 catalog tst/tst@rman auxiliary /
Recovery Manager: Release 11.2.0.2.0 - Production on Tue Oct 30 13:45:05 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORC3DB (DBID=3030113310)
connected to recovery catalog database
connected to auxiliary database: ORADEV (not mounted)
RMAN> run {
  allocate auxiliary channel C1 device type disk
  duplicate target database to oradev
}
allocated channel: C1
channel C1: SID=63 device type=DISK
Starting Duplicate Db at 30-OCT-12
contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     150667264 bytes
Fixed Size                     1342736 bytes
Variable Size                 96469744 bytes
Database Buffers              46137344 bytes
Redo Buffers                   6717440 bytes
allocated channel: C1
channel C1: SID=63 device type=DISK
contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORC3DB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''ORADEV'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script
sql statement: alter system set  db_name =  ''ORC3DB'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set  db_unique_name =  ''ORADEV'' comment= ''Modified by RMAN duplicate'' scope=spfile
0-20121029-01.ctl
channel C1: piece handle=/ORACLE2/oradata/test/rmanbkp1/c-3030113310-20121029-01.ctl tag=TAG20121029T100438
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:00:01
output file name=/u02/app/oradata/devdir/control.ctl
Finished restore at 30-OCT-12
database mounted
contents of Memory Script:
{
   set until scn  345421;
   set newname for datafile  1 to
 "/u02/app/oradata/devdir/system.dbf";
   set newname for datafile  2 to
 "/u02/app/oradata/devdir/sysaux.dbf";
   set newname for datafile  3 to
 "/u02/app/oradata/devdir/undotbs.dbf";
   set newname for datafile  4 to
Oracle instance shut down
Oracle instance started
Total System Global Area     150667264 bytes
Fixed Size                     1342736 bytes
Variable Size                 96469744 bytes
Database Buffers              46137344 bytes
Redo Buffers                   6717440 bytes
allocated channel: C1
channel C1: SID=63 device type=DISK
Starting restore at 30-OCT-12
channel C1: starting datafile backup set restore
channel C1: restoring control file
channel C1: reading from backup piece /ORACLE2/oradata/test/rmanbkp1/c-303011331
 "/u02/app/oradata/devdir/userdata.dbf";
   set newname for datafile  5 to
 "/u02/app/oradata/devdir/ts1.dbf";
   set newname for datafile  6 to
 "/u02/app/oradata/devdir/ts2.dbf";
   restore
   clone database
   ;
}
executing 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
Starting restore at 30-OCT-12
channel C1: starting datafile backup set restore
channel C1: specifying datafile(s) to restore from backup set
channel C1: restoring datafile 00001 to /u02/app/oradata/devdir/system.dbf
channel C1: restoring datafile 00004 to /u02/app/oradata/devdir/userdata.dbf
channel C1: restoring datafile 00006 to /u02/app/oradata/devdir/ts2.dbf
channel C1: reading from backup piece /ORACLE2/oradata/test/rmanbkp1/9dnov6fi_1_1.dbf
channel C1: piece handle=/ORACLE2/oradata/test/rmanbkp1/9dnov6fi_1_1.dbf tag=TAG20121029T100329
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:00:45
channel C1: starting datafile backup set restore
channel C1: specifying datafile(s) to restore from backup set
channel C1: restoring datafile 00002 to /u02/app/oradata/devdir/sysaux.dbf
channel C1: restoring datafile 00003 to /u02/app/oradata/devdir/undotbs.dbf
channel C1: restoring datafile 00005 to /u02/app/oradata/devdir/ts1.dbf
channel C1: reading from backup piece /ORACLE2/oradata/test/rmanbkp1/9enov6fj_1_1.dbf
channel C1: piece handle=/ORACLE2/oradata/test/rmanbkp1/9enov6fj_1_1.dbf tag=TAG20121029T100329
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:00:36
Finished restore at 30-OCT-12
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=798040042 file name=/u02/app/oradata/devdir/system.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=798040042 file name=/u02/app/oradata/devdir/sysaux.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=798040042 file name=/u02/app/oradata/devdir/undotbs.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=798040042 file name=/u02/app/oradata/devdir/userdata.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=798040043 file name=/u02/app/oradata/devdir/ts1.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=798040043 file name=/u02/app/oradata/devdir/ts2.dbf
contents of Memory Script:
{
   set until scn  345421;
   recover
 clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 30-OCT-12
starting media recovery
archived log for thread 1 with sequence 70 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_70_796934633.dbf
archived log for thread 1 with sequence 71 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_71_796934633.dbf
archived log for thread 1 with sequence 72 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_72_796934633.dbf
archived log for thread 1 with sequence 73 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_73_796934633.dbf
archived log for thread 1 with sequence 74 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_74_796934633.dbf
archived log for thread 1 with sequence 75 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_75_796934633.dbf
archived log for thread 1 with sequence 76 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_76_796934633.dbf
archived log for thread 1 with sequence 77 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_77_796934633.dbf
archived log for thread 1 with sequence 78 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_78_796934633.dbf
archived log for thread 1 with sequence 79 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_79_796934633.dbf
archived log for thread 1 with sequence 80 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_80_796934633.dbf
archived log for thread 1 with sequence 81 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_81_796934633.dbf
archived log for thread 1 with sequence 82 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_82_796934633.dbf
archived log for thread 1 with sequence 83 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_83_796934633.dbf
archived log for thread 1 with sequence 84 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_84_796934633.dbf
archived log for thread 1 with sequence 85 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_85_796934633.dbf
archived log for thread 1 with sequence 86 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_86_796934633.dbf
archived log for thread 1 with sequence 87 is already on disk as file /ORACLE2/oradata1/orc3dir/arch/1_87_796934633.dbf
archived log file name=/ORACLE2/oradata1/orc3dir/arch/1_70_796934633.dbf thread=1 sequence=70
archived log file name=/ORACLE2/oradata1/orc3dir/arch/1_71_796934633.dbf thread=1 sequence=71
archived log file name=/ORACLE2/oradata1/orc3dir/arch/1_72_796934633.dbf thread=1 sequence=72
archived log file name=/ORACLE2/oradata1/orc3dir/arch/1_73_796934633.dbf thread=1 sequence=73
archived log file name=/ORACLE2/oradata1/orc3dir/arch/1_74_796934633.dbf thread=1 sequence=74
archived log file name=/ORACLE2/oradata1/orc3dir/arch/1_75_796934633.dbf thread=1 sequence=75
archived log file name=/ORACLE2/oradata1/orc3dir/arch/1_86_796934633.dbf thread=1 sequence=86
archived log file name=/ORACLE2/oradata1/orc3dir/arch/1_87_796934633.dbf thread=1 sequence=87
media recovery complete, elapsed time: 00:00:24
Finished recover at 30-OCT-12
Oracle instance started
Total System Global Area     150667264 bytes
Fixed Size                     1342736 bytes
Variable Size                 96469744 bytes
Database Buffers              46137344 bytes
Redo Buffers                   6717440 bytes
contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORADEV'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
sql statement: alter system set  db_name =  ''ORADEV'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     150667264 bytes
Fixed Size                     1342736 bytes
Variable Size                 96469744 bytes
Database Buffers              46137344 bytes
Redo Buffers                   6717440 bytes
allocated channel: C1
channel C1: SID=63 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORADEV" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      2
  MAXDATAFILES       30
  MAXINSTANCES     1
MAXLOGHISTORY     1103
 LOGFILE
  GROUP  1 ( '/u02/app/oradata/devdir/redolog1.log' ) SIZE 4 M  REUSE,
  GROUP  2 ( '/u02/app/oradata/devdir/redolog2.log' ) SIZE 4 M  REUSE
 DATAFILE
  '/u02/app/oradata/devdir/system.dbf'
 CHARACTER SET US7ASCII

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u02/app/oradata/devdir/temp.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u02/app/oradata/devdir/sysaux.dbf", "/u02/app/oradata/devdir/undotbs.dbf",
 "/u02/app/oradata/devdir/userdata.dbf",
 "/u02/app/oradata/devdir/ts1.dbf",
 "/u02/app/oradata/devdir/ts2.dbf";
   switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u02/app/oradata/devdir/temp.dbf in control file
cataloged datafile copy
datafile copy file name=/u02/app/oradata/devdir/sysaux.dbf RECID=1 STAMP=798040098
cataloged datafile copy
datafile copy file name=/u02/app/oradata/devdir/undotbs.dbf RECID=2 STAMP=798040098
cataloged datafile copy
datafile copy file name=/u02/app/oradata/devdir/userdata.dbf RECID=3 STAMP=798040098
cataloged datafile copy
datafile copy file name=/u02/app/oradata/devdir/ts1.dbf RECID=4 STAMP=798040098
cataloged datafile copy
datafile copy file name=/u02/app/oradata/devdir/ts2.dbf RECID=5 STAMP=798040098
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=798040098 file name=/u02/app/oradata/devdir/sysaux.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=798040098 file name=/u02/app/oradata/devdir/undotbs.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=798040098 file name=/u02/app/oradata/devdir/userdata.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=798040098 file name=/u02/app/oradata/devdir/ts1.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=798040098 file name=/u02/app/oradata/devdir/ts2.dbf
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 30-OCT-12
released channel: C1
RMAN> exit

Recovery Manager complete.
[oradev@muthu dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 30 13:49:47 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>select instance_name,status from v$instance;
INSTANCE_NAME    STATUS
---------------- ------------
oradev           OPEN
SQL>