I was sick and tired of taking backups of our E-Business Database for cloning purposes. This was all over until I found the nice new feature in RMAN " DUPLICATE FROM ACTIVE DATABASE ", which allows you to clone your database without the need of taking a backup, avoiding the need of copying the backup pieces across the network and using temporary space on the auxiliary database host.This process is also useful to create your standby database without having to take a backup. This is what I describe in this post, Enjoy!!!
Here is what you need to do:
Source Database
host: srchost1
RAC: EBIZ 1, EBIZ 2
db_unique_name: EBIZ_PRIM Target Database
host: tgthost1
RAC: EBIZ1, EBIZ 2
db_unique_name: EBIZ_STBY
1) On SOURCE startup the auxiliary instance (stby) - Use a pfile ready for standby
SQL> startup nomount pfile=EBIZ2.pfile;
2) Connect to RMAN from the SOURCE database (prim). If RAC: start only first instance . Define an entry for EBIZ1_stby in the $TNS_ADMIN/<>_ifile.ora, pointing to the standby instance .
$ rman auxiliary sys@EBIZ1_stby target sys@EBIZ_prim
Recovery Manager: Release 11.1.0.7.0 - Production on Tue May 18 09:35:11 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:
connected to target database: EBIZ (DBID=1345541966)
auxiliary database Password:
connected to auxiliary database: EBIZ (not mounted)
RMAN> run {
allocate channel prim1 device type disk;
allocate channel prim2 device type disk;
allocate channel prim3 device type disk;
allocate channel prim4 device type disk;
allocate auxiliary channel stby1 device type disk;
allocate auxiliary channel stby2 device type disk;
allocate auxiliary channel stby3 device type disk;
allocate auxiliary channel stby4 device type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
SPFILE
parameter_value_convert = '+DATA/EBIZ','+DATA/EBIZ_STBY', '+DATA/EBIZ_PRIM', '+DATA/EBIZ_STBY'
set db_unique_name = 'EBIZ_STBY'
set instance_number='1'
SET DB_FILE_NAME_CONVERT = '+DATA/EBIZ','+DATA/EBIZ_STBY', '+DATA/EBIZ_PRIM', '+DATA/EBIZ_STBY'
SET log_file_name_convert = '+DATA/EBIZ','+DATA/EBIZ_STBY', '+DATA', '+DATA/EBIZ_STBY', '+DATA/EBIZ_PRIM', '+DATA/EBIZ_STBY'
set diagnostic_dest = '/u04/oracle/EBIZ/db/tech_st/11.1.0_RAC/admin/EBIZ1_tgthost1';
}
3) You can not overwrite instance specific parameters like EBIZ2.disgnostic_dest. To workaround diagnostic_det you have to manually create the directory same as the source.
mkdir -p /u04/oracle/EBIZ/db/tech_st/11.1.0_RAC/admin/EBIZ2_tgthost2
4) You have to connect with a TNS alias. That is a restriction.
5) Make sure you run RMAN from the PRIM instance always, if not you may encounter ORA- errors while running duplicate.
Starting backup at 18-MAY-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7791 instance=EBIZ1 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/18/2010 09:34:03
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/18/2010 09:34:02
ORA-17628: Oracle error 19505 returned by remote Oracle server
6) Make sure PRIM has only the first instance started. There are restrictions if both instances are started and will prevent duplicate from running.
7) Complete RMAN output
[ora@srchost1 ~]$ rman auxiliary sys@EBIZ1_stby target sys@EBIZ_prim
Recovery Manager: Release 11.1.0.7.0 - Production on Tue May 18 09:35:11 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:
connected to target database: EBIZ (DBID=1345541966)
auxiliary database Password:
connected to auxiliary database: EBIZ (not mounted)
RMAN> run {
allocate channel prim1 device type disk;
allocate channel prim2 device type disk;
allocate channel prim3 device type disk;
allocate channel prim4 device type disk;
allocate auxiliary channel stby1 device type disk;
allocate auxiliary channel stby2 device type disk;
allocate auxiliary channel stby3 device type disk;
allocate auxiliary channel stby4 device type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
SPFILE
parameter_value_convert = '+DATA/EBIZ','+DATA/EBIZ_STBY', '+DATA/EBIZ_PRIM', '+DATA/EBIZ_STBY'
set db_unique_name = 'EBIZ_STBY'
set instance_number='1'
SET DB_FILE_NAME_CONVERT = '+DATA/EBIZ','+DATA/EBIZ_STBY', '+DATA/EBIZ_PRIM', '+DATA/EBIZ_STBY'
SET log_file_name_convert = '+DATA/EBIZ','+DATA/EBIZ_STBY', '+DATA', '+DATA/EBIZ_STBY', '+DATA/EBIZ_PRIM', '+DATA/EBIZ_STBY'
set diagnostic_dest = '/u04/oracle/EBIZ/db/tech_st/11.1.0_RAC/admin/EBIZ1_tgthost1';
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22>
allocated channel: prim1
channel prim1: SID=7789 instance=EBIZ1 device type=DISK
allocated channel: prim2
channel prim2: SID=7790 instance=EBIZ1 device type=DISK
allocated channel: prim3
channel prim3: SID=7845 instance=EBIZ1 device type=DISK
allocated channel: prim4
channel prim4: SID=7856 instance=EBIZ1 device type=DISK
allocated channel: stby1
channel stby1: SID=1072 instance=EBIZ1 device type=DISK
allocated channel: stby2
channel stby2: SID=1069 instance=EBIZ1 device type=DISK
allocated channel: stby3
channel stby3: SID=1070 instance=EBIZ1 device type=DISK
allocated channel: stby4
channel stby4: SID=1071 instance=EBIZ1 device type=DISK
Starting Duplicate Db at 18-MAY-10
contents of Memory Script:
{
backup as copy reuse
file '/u04/oracle/EBIZ/db/tech_st/11.1.0_RAC/dbs/orapwEBIZ1' auxiliary format
'/u04/oracle/EBIZ/db/tech_st/11.1.0_RAC/dbs/orapwEBIZ1' file
'+DATA/EBIZ/spfileEBIZ.ora' auxiliary format
'+DATA/EBIZ_stby/spfileEBIZ.ora' ;
sql clone "alter system set spfile= ''+DATA/EBIZ_stby/spfileEBIZ.ora''";
}
executing Memory Script
Starting backup at 18-MAY-10
Finished backup at 18-MAY-10
sql statement: alter system set spfile= ''+DATA/EBIZ_stby/spfileEBIZ.ora''
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/EBIZ_STBY/cntrl01.dbf'', ''+DATA/EBIZ_STBY/cntrl02.dbf'', ''+DATA/EBIZ_STBY/cntrl03.dbf'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''EBIZ_STBY'' comment=
'''' scope=spfile";
sql clone "alter system set instance_number =
1 comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''+DATA/EBIZ'', ''+DATA/EBIZ_STBY'', ''+DATA/EBIZ_PRIM'', ''+DATA/EBIZ_STBY'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''+DATA/EBIZ'', ''+DATA/EBIZ_STBY'', ''+DATA'', ''+DATA/EBIZ_STBY'', ''+DATA/EBIZ_PRIM'', ''+DATA/EBIZ_STBY'' comment=
'''' scope=spfile";
sql clone "alter system set diagnostic_dest =
''/u04/oracle/EBIZ/db/tech_st/11.1.0_RAC/admin/EBIZ1_tgthost1'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script
sql statement: alter system set control_files = ''+DATA/EBIZ_STBY/cntrl01.dbf'', ''+DATA/EBIZ_STBY/cntrl02.dbf'', ''+DATA/EBIZ_STBY/cntrl03.dbf'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''EBIZ_STBY'' comment= '''' scope=spfile
sql statement: alter system set instance_number = 1 comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''+DATA/EBIZ'', ''+DATA/EBIZ_STBY'', ''+DATA/EBIZ_PRIM'', ''+DATA/EBIZ_STBY'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''+DATA/EBIZ'', ''+DATA/EBIZ_STBY'', ''+DATA'', ''+DATA/EBIZ_STBY'', ''+DATA/EBIZ_PRIM'', ''+DATA/EBIZ_STBY'' comment= '''' scope=spfile
sql statement: alter system set diagnostic_dest = ''/u04/oracle/EBIZ/db/tech_st/11.1.0_RAC/admin/EBIZ1_tgthost1'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 12827369472 bytes
Fixed Size 2163968 bytes
Variable Size 8992590592 bytes
Database Buffers 3758096384 bytes
Redo Buffers 74518528 bytes
allocated channel: stby1
channel stby1: SID=7968 instance=EBIZ1 device type=DISK
allocated channel: stby2
channel stby2: SID=7967 instance=EBIZ1 device type=DISK
allocated channel: stby3
channel stby3: SID=7966 instance=EBIZ1 device type=DISK
allocated channel: stby4
channel stby4: SID=7965 instance=EBIZ1 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DATA/EBIZ_stby/cntrl01.dbf';
restore clone controlfile to '+DATA/EBIZ_stby/cntrl02.dbf' from
'+DATA/EBIZ_stby/cntrl01.dbf';
restore clone controlfile to '+DATA/EBIZ_stby/cntrl03.dbf' from
'+DATA/EBIZ_stby/cntrl01.dbf';
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting backup at 18-MAY-10
channel prim1: starting datafile copy
copying standby control file
output file name=/u04/oracle/EBIZ/db/tech_st/11.1.0_RAC/dbs/snapcf_EBIZ1.f tag=TAG20100518T093617 RECID=56 STAMP=719314579
channel prim1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-MAY-10
Starting restore at 18-MAY-10
channel stby2: skipped, AUTOBACKUP already found
channel stby3: skipped, AUTOBACKUP already found
channel stby4: skipped, AUTOBACKUP already found
channel stby1: copied control file copy
Finished restore at 18-MAY-10
Starting restore at 18-MAY-10
channel stby2: skipped, AUTOBACKUP already found
channel stby3: skipped, AUTOBACKUP already found
channel stby4: skipped, AUTOBACKUP already found
channel stby1: copied control file copy
Finished restore at 18-MAY-10
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
"+data";
…
…
…
set newname for datafile 407 to
"+data";
backup as copy reuse
datafile 1 auxiliary format
"+data" datafile
…
…
…
407 auxiliary format
"+data" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +data in control file
renamed tempfile 2 to +data in control file
executing command: SET NEWNAME
…
…
…
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 18-MAY-10
channel prim1: starting datafile copy
input datafile file number=00400 name=+DATA/EBIZ/datafile/apps_ts_media.319.716999635
…
…
…
channel prim1: datafile copy complete, elapsed time: 00:00:01
output file name=+DATA/EBIZ_stby/datafile/quest_tools.362.719317261 tag=TAG20100518T093658 RECID=0 STAMP=0
channel prim2: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-MAY-10
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=56 STAMP=719317266 file name=+DATA/EBIZ_stby/datafile/system.448.719316989
…
…
…
input datafile copy RECID=109 STAMP=719317270 file name=+DATA/EBIZ_stby/datafile/apps_ts_seed.380.719316907
Finished Duplicate Db at 18-MAY-10
released channel: prim1
released channel: prim2
released channel: prim3
released channel: prim4
released channel: stby1
released channel: stby2
released channel: stby3
released channel: stby4
RMAN> exit
Recovery Manager complete.
References:
How to create physical standby database with 11g RMAN DUPLICATE FROM ACTIVE DATABASE [ID 747250.1]
http://www.oracle.com/technology/obe/11gr1_db/ha/dataguard/physstby/physstdby.htm
No comments:
Post a Comment