11 | 12 | 2017
Latest Articles
Popular Articles

RMAN

Clone database using db_file_name_convert & log_file_name_convert

User Rating:  / 1
PoorBest 

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/secret@PLAT701M.world

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.