Oracle DBA Tips and Techniques

By Arjun Raja

11g RELEASE 2 – RMAN DUPLICATION OF DATABASE

Posted by Arjun Raja on December 1, 2009

11g release 2 has a very good feature for RMAN duplication which will allow you to duplicate a database even if the target database is not available.

In case a disk backup is available, there is no need to even connect to a catalog, otherwise a conenction to the catalog is necessary to get info from tape.

11g release 1 –

We will try the duplicate command without connecting to the target database – this will fail as can be seen below.

connect catalog rman10d/d10rman@rcatd
connect auxiliary /

run {
allocate auxiliary channel ch1 device type sbt_tape;

SET UNTIL TIME “TO_DATE (’23-11-09 10:57:00′, ‘DD-MM-YY HH24:MI:SS’)”; duplicate target database to test
nofilenamecheck;
}
connected to recovery catalog database

connected to auxiliary database: TEST (not mounted)

RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of set command at 11/30/2009 13:32:01
RMAN-06171: not connected to target database

Recovery Manager complete.
~
~
11g RELEASE 2 –

export ORACLE_SID=TESTDB

TAKE FULL BACKUP TO DISK –

RMAN> run {
configure channel device type disk format
‘/u02/oradata/backup_TESTDB/TESTDB_%U’;
backup database plus archivelog;
}

piece handle=/u02/oradata/backup_TESTDB/TESTDB_08kvld2f_1_1
tag=TAG20091130T134655 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 30-NOV-09

RMAN>

NOW CREATE DUPLICATE DATABASE – WITHOUT CONNECTING TO TARGET OR CATALOG –

The backup must be accessible on disk in this case.

Ascertain entry for TESTDB1 – duplicate database exisit in tnsnames.ora and listener.ora

Now create an init.ora file for the new duplicate database –

cd $ORACLE_HOME/dbs

export ORACLE_SID=TESTDB1

copy the init.ora of the target database as initTESTDB1.ora – and make changes to reflect TESTDB1

Add foll in new init.ora –

*.db_file_name_convert=’/u02/oradata/TESTDB’,’/u02/oradata/TESTDB1′ *.log_file_name_convert=’/u02/oradata/TESTDB’,’/u02/oradata/TESTDB1′

create spfile from pfile;

startup nomount duplicate database – TESTDB1.

export ORACLE_SID=TESTDB1

sql > startup nomount;

exit;

Now begin duplication by only connecting to auxiliary – no connection to target or even catalog –

export ORACLE_SID=TESTDB1

[oracle@redhat346 ~]$ rman auxiliary /

Recovery Manager: Release 11.2.0.1.0 – Production on Mon Nov 30 14:06:32 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to auxiliary database: TESTDB1 (not mounted)

RMAN> RUN {
DUPLICATE DATABASE TO TESTDB1
BACKUP LOCATION ‘/u02/oradata/backup_TESTDB’; – specify backup location on disk.
}
2> 3> 4>
Starting Duplicate Db at 30-NOV-09

contents of Memory Script:
{
sql clone “alter system set db_name =
”TESTDB” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”TESTDB1” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from
‘/u02/oradata/backup_TESTDB/TESTDB_07kvld2d_1_1’;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ”TESTDB” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”TESTDB1” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 626327552 bytes

Fixed Size 2215944 bytes
Variable Size 390074360 bytes
Database Buffers 226492416 bytes
Redo Buffers 7544832 bytes

Starting restore at 30-NOV-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u02/oradata/TESTDB1/control01.ctl
output file name=/u02/oradata/flash_recovery_area/TESTDB1/control02.ctl Finished restore at 30-NOV-09

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

contents of Memory Script:
{
set until scn 1000239;
set newname for datafile 1 to
“/u02/oradata/TESTDB1/system01.dbf”;
set newname for datafile 2 to
“/u02/oradata/TESTDB1/sysaux01.dbf”;
set newname for datafile 3 to
“/u02/oradata/TESTDB1/undotbs01.dbf”;
set newname for datafile 4 to
“/u02/oradata/TESTDB1/users01.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

Starting restore at 30-NOV-09
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to
/u02/oradata/TESTDB1/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to
/u02/oradata/TESTDB1/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to
/u02/oradata/TESTDB1/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to
/u02/oradata/TESTDB1/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece
/u02/oradata/backup_TESTDB/TESTDB_06kvld1u_1_1
channel ORA_AUX_DISK_1: piece
handle=/u02/oradata/backup_TESTDB/TESTDB_06kvld1u_1_1
tag=TAG20091130T134638
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25 Finished restore at 30-NOV-09

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=704297238 file
name=/u02/oradata/TESTDB1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=704297238 file
name=/u02/oradata/TESTDB1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=704297238 file
name=/u02/oradata/TESTDB1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=704297238 file
name=/u02/oradata/TESTDB1/users01.dbf

contents of Memory Script:
{
set until scn 1000239;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 30-NOV-09
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_AUX_DISK_1: reading from backup piece
/u02/oradata/backup_TESTDB/TESTDB_08kvld2f_1_1
channel ORA_AUX_DISK_1: piece
handle=/u02/oradata/backup_TESTDB/TESTDB_08kvld2f_1_1
tag=TAG20091130T134655
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 archived log file
name=/u02/oradata/flash_recovery_area/TESTDB1/archivelog/2009_11_30/o1_mf_1_8_5k6r8qnm_.arc thread=1 sequence=8
channel clone_default: deleting archived log(s)
archived log file
name=/u02/oradata/flash_recovery_area/TESTDB1/archivelog/2009_11_30/o1_mf_1_8_5k6r8qnm_.arc RECID=1 STAMP=704297239
media recovery complete, elapsed time: 00:00:01
Finished recover at 30-NOV-09

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone “alter system set db_name =
”TESTDB1” 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

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 626327552 bytes

Fixed Size 2215944 bytes
Variable Size 390074360 bytes
Database Buffers 226492416 bytes
Redo Buffers 7544832 bytes

sql statement: alter system set db_name = ”TESTDB1” 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 626327552 bytes

Fixed Size 2215944 bytes
Variable Size 390074360 bytes
Database Buffers 226492416 bytes
Redo Buffers 7544832 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “TESTDB1” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/u02/oradata/TESTDB1/redo01.log’ ) SIZE 50 M REUSE, GROUP 2 ( ‘/u02/oradata/TESTDB1/redo02.log’ ) SIZE 50 M REUSE, GROUP 3 ( ‘/u02/oradata/TESTDB1/redo03.log’ ) SIZE 50 M REUSE DATAFILE
‘/u02/oradata/TESTDB1/system01.dbf’
CHARACTER SET AL32UTF8

contents of Memory Script:
{
set newname for tempfile 1 to
“/u02/oradata/TESTDB1/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy “/u02/oradata/TESTDB1/sysaux01.dbf”, “/u02/oradata/TESTDB1/undotbs01.dbf”,
“/u02/oradata/TESTDB1/users01.dbf”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/oradata/TESTDB1/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u02/oradata/TESTDB1/sysaux01.dbf RECID=1 STAMP=704297257
cataloged datafile copy
datafile copy file name=/u02/oradata/TESTDB1/undotbs01.dbf RECID=2 STAMP=704297257
cataloged datafile copy
datafile copy file name=/u02/oradata/TESTDB1/users01.dbf RECID=3 STAMP=704297257

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=704297257 file
name=/u02/oradata/TESTDB1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=704297257 file
name=/u02/oradata/TESTDB1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=704297257 file
name=/u02/oradata/TESTDB1/users01.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 30-NOV-09

RMAN> exit

Recovery Manager complete.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: