RMAN
Clone database using db_file_name_convert & log_file_name_convert
User Rating: / 1
- Details
-
Parent Category: Articles
-
Created on Wednesday, 31 August 2011 15:45
-
Last Updated on Tuesday, 10 December 2019 15:00
-
Published on Wednesday, 31 August 2011 15:45
-
Hits: 20909
Clone database using db_file_name_convert & log_file_name_convert
See also Clone database using set name and log file name clause
1. You need to duplicate a database on the same host but forcefully to another location than the target database, either
2. You need to duplicate a database on another host with another filesystem layout
3. You would like to use the RMAN duplicate technology since it is told to be easy.
There we go
1. Copy password file and spfile from the target database towards the new auxiliary Oracle Home and make your change ie
2. Adjust the instance initilization parameters which uses OS paths like background_dump_dest, user_dump_dest, core_dump_dest, control_files
3. Create a valid TNS entry in the tnsnames.ora which points to the target database.
SQL> show parameter db_file_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /u01/oradata/PLAT701M, /u01/oradata/PLAT802M
SQL> show parameter log_file_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_file_name_convert string /u01/oradata/PLAT701M, /u01/oradata/PLAT802M
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2089400 bytes
Variable Size 700452424 bytes
Database Buffers 364904448 bytes
Redo Buffers 6295552 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@host803 ~]$ rman
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Aug 31 11:47:08 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> connect target rman/[email protected]
connected to target database: PLAT701M (DBID=708305797)
RMAN> connect catalog rman/secret@catalogdb
connected to recovery catalog database
RMAN> connect auxiliary /
connected to auxiliary database: PLAT802M (not mounted)
RMAN> run {
2> set until logseq 1920;
3> duplicate target database to PLAT802M;
4> }
executing command: SET until clause
Starting Duplicate Db at 31-AUG-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
contents of Memory Script:
{
set until scn 576922745;
set newname for datafile 1 to
"/u01/oradata/PLAT802M/system01.dbf";
set newname for datafile 2 to
"/u01/oradata/PLAT802M/undotbs01.dbf";
set newname for datafile 3 to
"/u01/oradata/PLAT802M/sysaux01.dbf";
set newname for datafile 4 to
"/u01/oradata/PLAT802M/users01.dbf";
set newname for datafile 5 to
"/u01/oradata/PLAT802M/twadmin01.dbf";
set newname for datafile 6 to
"/u01/oradata/PLAT802M/reporting01.dbf";
restore
check readonly
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 31-AUG-11
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/oradata/PLAT802M/users01.dbf
restoring datafile 00005 to /u01/oradata/PLAT802M/twadmin01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/PLAT701M/DB_PLAT701M_ekmlbf6a_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/rman/PLAT701M/DB_PLAT701M_ekmlbf6a_1_1 tag=TAG20110831T040009
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/PLAT802M/system01.dbf
restoring datafile 00003 to /u01/oradata/PLAT802M/sysaux01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/PLAT701M/DB_PLAT701M_emmlbf6a_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/rman/PLAT701M/DB_PLAT701M_emmlbf6a_1_1 tag=TAG20110831T040009
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/oradata/PLAT802M/undotbs01.dbf
restoring datafile 00006 to /u01/oradata/PLAT802M/reporting01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/PLAT701M/DB_PLAT701M_elmlbf6a_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/rman/PLAT701M/DB_PLAT701M_elmlbf6a_1_1 tag=TAG20110831T040009
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 31-AUG-11
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PLAT802M" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 ( '/u01/oradata/PLAT802M/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/oradata/PLAT802M/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/oradata/PLAT802M/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u01/oradata/PLAT802M/system01.dbf'
CHARACTER SET UTF8
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=760621900 filename=/u01/oradata/PLAT802M/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=760621900 filename=/u01/oradata/PLAT802M/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=760621900 filename=/u01/oradata/PLAT802M/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=760621900 filename=/u01/oradata/PLAT802M/twadmin01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=760621900 filename=/u01/oradata/PLAT802M/reporting01.dbf
contents of Memory Script:
{
set until scn 576922745;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 31-AUG-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=158 devtype=DISK
starting media recovery
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=1917
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=1918
channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/PLAT701M/AL_PLAT701M_ermlbf7l
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/rman/PLAT701M/AL_PLAT701M_ermlbf7l tag=TAG20110831T040052
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archive log filename=/u01/oradata/PLAT802M/archive/PLAT802M_1_0000001917_735307336.arc thread=1 sequence=1917
channel clone_default: deleting archive log(s)
archive log filename=/u01/oradata/PLAT802M/archive/PLAT802M_1_0000001917_735307336.arc recid=2 stamp=760621904
archive log filename=/u01/oradata/PLAT802M/archive/PLAT802M_1_0000001918_735307336.arc thread=1 sequence=1918
channel clone_default: deleting archive log(s)
archive log filename=/u01/oradata/PLAT802M/archive/PLAT802M_1_0000001918_735307336.arc recid=1 stamp=760621903
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=1919
channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/PLAT701M/AL_PLAT701M_eumlc8lg
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/rman/PLAT701M/AL_PLAT701M_eumlc8lg tag=TAG20110831T111455
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
archive log filename=/u01/oradata/PLAT802M/archive/PLAT802M_1_0000001919_735307336.arc thread=1 sequence=1919
channel clone_default: deleting archive log(s)
archive log filename=/u01/oradata/PLAT802M/archive/PLAT802M_1_0000001919_735307336.arc recid=3 stamp=760621913
media recovery complete, elapsed time: 00:00:05
Finished recover at 31-AUG-11
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 2089400 bytes
Variable Size 700452424 bytes
Database Buffers 364904448 bytes
Redo Buffers 6295552 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PLAT802M" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 ( '/u01/oradata/PLAT802M/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/oradata/PLAT802M/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/oradata/PLAT802M/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u01/oradata/PLAT802M/system01.dbf'
CHARACTER SET UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/oradata/PLAT802M/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u01/oradata/PLAT802M/undotbs01.dbf";
catalog clone datafilecopy "/u01/oradata/PLAT802M/sysaux01.dbf";
catalog clone datafilecopy "/u01/oradata/PLAT802M/users01.dbf";
catalog clone datafilecopy "/u01/oradata/PLAT802M/twadmin01.dbf";
catalog clone datafilecopy "/u01/oradata/PLAT802M/reporting01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /u01/oradata/PLAT802M/temp01.dbf in control file
cataloged datafile copy
datafile copy filename=/u01/oradata/PLAT802M/undotbs01.dbf recid=1 stamp=760621929
cataloged datafile copy
datafile copy filename=/u01/oradata/PLAT802M/sysaux01.dbf recid=2 stamp=760621930
cataloged datafile copy
datafile copy filename=/u01/oradata/PLAT802M/users01.dbf recid=3 stamp=760621930
cataloged datafile copy
datafile copy filename=/u01/oradata/PLAT802M/twadmin01.dbf recid=4 stamp=760621930
cataloged datafile copy
datafile copy filename=/u01/oradata/PLAT802M/reporting01.dbf recid=5 stamp=760621930
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=760621929 filename=/u01/oradata/PLAT802M/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=760621930 filename=/u01/oradata/PLAT802M/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=760621930 filename=/u01/oradata/PLAT802M/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=760621930 filename=/u01/oradata/PLAT802M/twadmin01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=760621930 filename=/u01/oradata/PLAT802M/reporting01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 31-AUG-11.