Duplicating Database without connecting to Target – 11gR2

From 11gR2, you can duplicate the database without connecting to the target database or recovery catalog which is termed as “Backup-Based Duplication Without a Target and a Recovery Catalog Connection”.

http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmdupdb.htm#BRADV435

OS – Windows
Duplicate Database – ORCL
Target Database – MATRIX

1. Backup the target database

D:\scripts>rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 23 14:58:15 2011

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

connected to target database: MATRIX (DBID=2302797847)

RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> backup as compressed backupset format 'D:\oracle\backup\matrix\%d_%u' database plus archivelog
5> include current controlfile;
6> release channel c1;
7> release channel c2;
8> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=198 device type=DISK

allocated channel: c2
channel c2: SID=11 device type=DISK


Starting backup at 23-SEP-11
current log archived
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 09/23/2011 14:58:42
RMAN-06029: the control file may be included only in a datafile backup set

RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> backup as compressed backupset format 'D:\oracle\backup\matrix\%d_%u' database
5> include current controlfile;
6> backup archivelog all;
7> release channel c1;
8> release channel c2;
9> }

allocated channel: c1
channel c1: SID=198 device type=DISK

allocated channel: c2
channel c2: SID=11 device type=DISK

Starting backup at 23-SEP-11
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\ORACLE\ORADATA\MATRIX\SYSTEM01.DBF
input datafile file number=00004 name=D:\ORACLE\ORADATA\MATRIX\USERS01.DBF
input datafile file number=00005 name=D:\ORACLE\ORADATA\MATRIX\EXAMPLE01.DBF
channel c1: starting piece 1 at 23-SEP-11
channel c2: starting compressed full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=D:\ORACLE\ORADATA\MATRIX\SYSAUX01.DBF
input datafile file number=00003 name=D:\ORACLE\ORADATA\MATRIX\UNDOTBS01.DBF
input datafile file number=00006 name=D:\ORACLE\ORADATA\MATRIX\TEST_01.DBF
channel c2: starting piece 1 at 23-SEP-11
channel c2: finished piece 1 at 23-SEP-11
piece handle=D:\ORACLE\BACKUP\MATRIX\MATRIX_02MN9ADQ tag=TAG20110923T145905 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:35
channel c2: starting compressed full datafile backup set
channel c2: specifying datafile(s) in backup set
including current control file in backup set
channel c2: starting piece 1 at 23-SEP-11
channel c2: finished piece 1 at 23-SEP-11
piece handle=D:\ORACLE\BACKUP\MATRIX\MATRIX_03MN9AEU tag=TAG20110923T145905 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:03
channel c1: finished piece 1 at 23-SEP-11
piece handle=D:\ORACLE\BACKUP\MATRIX\MATRIX_01MN9ADQ tag=TAG20110923T145905 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:55
Finished backup at 23-SEP-11

Starting backup at 23-SEP-11
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=762487259
input archived log thread=1 sequence=6 RECID=2 STAMP=762521155
input archived log thread=1 sequence=7 RECID=3 STAMP=762523780
input archived log thread=1 sequence=8 RECID=4 STAMP=762523836
input archived log thread=1 sequence=9 RECID=5 STAMP=762562840
channel c1: starting piece 1 at 23-SEP-11
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=6 STAMP=762600646
input archived log thread=1 sequence=11 RECID=7 STAMP=762609637
input archived log thread=1 sequence=12 RECID=8 STAMP=762620322
input archived log thread=1 sequence=13 RECID=9 STAMP=762620401
channel c2: starting piece 1 at 23-SEP-11
channel c1: finished piece 1 at 23-SEP-11
piece handle=D:\ORACLE\PRODUCT\11.2.0.2\DBHOME_1\DATABASE4MN9AFI_1_1 tag=TAG20110923T150002 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:15
channel c2: finished piece 1 at 23-SEP-11
piece handle=D:\ORACLE\PRODUCT\11.2.0.2\DBHOME_1\DATABASE5MN9AFI_1_1 tag=TAG20110923T150002 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:15
Finished backup at 23-SEP-11

Starting Control File and SPFILE Autobackup at 23-SEP-11
piece handle=D:\ORACLE\PRODUCT\11.2.0.2\DBHOME_1\DATABASE\C-2302797847-20110923-00 comment=NONE
Finished Control File and SPFILE Autobackup at 23-SEP-11

released channel: c1

released channel: c2

RMAN>

The backup location is D:\ORACLE\BACKUP\MATRIX\ . Copy the backupset to the auxiliary/duplicate database server. I copied all the backupsets created as part of backup to D:\oracle\backup\orcl

2. Crerate the initORCL file for duplicate database –

db_name=ORCL
db_unique_name=ORCL
control_files='D:\oracle\oradata\orcl\control01.ctl','D:\oracle\oradata\orcl\control02.ctl'
audit_file_dest='D:\ORACLE\PRODUCT\ADMIN\ORCL\ADUMP'
compatible='11.2.0.0.0'
diagnostic_dest='D:\ORACLE\PRODUCT'
db_file_name_convert='D:\oracle\oradata\matrix','D:\oracle\oradata\orcl'
log_file_name_convert='D:\oracle\oradata\matrix','D:\oracle\oradata\orcl'

3. Create a new instance using ORADIM utility

D:\scripts>oradim -new -sid ORCL -pfile D:\oracle\product\11.2.0.2\dbhome_1\database\INITorcl.ORA
Instance created.

4. Create the password file

D:\scripts>orapwd file=D:\oracle\product\11.2.0.2\dbhome_1\database\PWDORCL.ora entries=2 password=sys123

5. Startup nomount the ORCL instance

D:\scripts>set oracle_sid=ORCL
D:\scripts>
D:\scripts>sqlplus sys/sys123 as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sat Sep 24 00:06:33 2011

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                  1382112 bytes
Variable Size              92277024 bytes
Database Buffers           50331648 bytes
Redo Buffers                6676480 bytes
SQL>

6. Connect to the auxiliary ORCL database and run the duplicate command

D:\scripts>set oracle_sid=ORCL
D:\scripts>rman auxiliary /

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Sep 24 00:31:00 2011

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

connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate database to ORCL  backup location 'D:\oracle\backup\orcl';

memory script content –

Starting Duplicate Db at 24-SEP-11

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                     1382112 bytes
Variable Size                 92277024 bytes
Database Buffers              50331648 bytes
Redo Buffers                   6676480 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''MATRIX'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''ORCL'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  'D:\ORACLE\BACKUP\orcl\C-2302797847-20110923-00';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''MATRIX'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     150667264 bytes

Fixed Size                     1382112 bytes
Variable Size                 92277024 bytes
Database Buffers              50331648 bytes
Redo Buffers                   6676480 bytes

Starting restore at 24-SEP-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=130 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=D:\ORACLE\ORADATA\ORCL\CONTROL01.CTL
output file name=D:\ORACLE\ORADATA\ORCL\CONTROL02.CTL
Finished restore at 24-SEP-11

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

contents of Memory Script:
{
   set until scn  937053;
   set newname for datafile  1 to
 "D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF";
   set newname for datafile  2 to
 "D:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF";
   set newname for datafile  3 to
 "D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF";
   set newname for datafile  4 to
 "D:\ORACLE\ORADATA\ORCL\USERS01.DBF";
   set newname for datafile  5 to
 "D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF";
   set newname for datafile  6 to
 "D:\ORACLE\ORADATA\ORCL\TEST_01.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 24-SEP-11
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 D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00004 to D:\ORACLE\ORADATA\ORCL\USERS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00005 to D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
channel ORA_AUX_DISK_1: reading from backup piece D:\ORACLE\BACKUP\ORCL\MATRIX_01MN9ADQ
channel ORA_AUX_DISK_1: piece handle=D:\ORACLE\BACKUP\ORCL\MATRIX_01MN9ADQ tag=TAG20110923T145905
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
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 00002 to D:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00006 to D:\ORACLE\ORADATA\ORCL\TEST_01.DBF
channel ORA_AUX_DISK_1: reading from backup piece D:\ORACLE\BACKUP\ORCL\MATRIX_02MN9ADQ
channel ORA_AUX_DISK_1: piece handle=D:\ORACLE\BACKUP\ORCL\MATRIX_02MN9ADQ tag=TAG20110923T145905
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 24-SEP-11

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

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=762655138 file name=D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=762655138 file name=D:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=762655138 file name=D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=762655138 file name=D:\ORACLE\ORADATA\ORCL\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=762655138 file name=D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=762655138 file name=D:\ORACLE\ORADATA\ORCL\TEST_01.DBF

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

executing command: SET until clause

Starting recover at 24-SEP-11
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=13
channel ORA_AUX_DISK_1: reading from backup piece D:\ORACLE\BACKUP\ORCL5MN9AFI_1_1
channel ORA_AUX_DISK_1: piece handle=D:\ORACLE\BACKUP\ORCL5MN9AFI_1_1 tag=TAG20110923T150002
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=D:\ORACLE\PRODUCT\11.2.0.2\DBHOME_1\RDBMS\ARC0000000013_0762473114.0001 thread=1 sequence=13
channel clone_default: deleting archived log(s)
archived log file name=D:\ORACLE\PRODUCT\11.2.0.2\DBHOME_1\RDBMS\ARC0000000013_0762473114.0001 RECID=1 STAMP=762655142
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-SEP-11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/24/2011 00:39:15
RMAN-06403: could not obtain a fully authorized session
RMAN-04006: error from auxiliary database: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
RMAN-04014: startup failed: ORA-00600: internal error code, arguments: [17099], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [KSMFPG5], [0xAEC0000], [], [], [], [], [], [], [], [], [], []

As per metalink –

ORA-00600: internal error code, arguments: [KSMFPG5], [0xAEC0000], [], [], [], [], [], [], [], [], [], []
Bug 10024309 –This issue is not seen in 11.2.0.1 and seen only in 11.2.0.2 and only on Windows platforms.

Lucky me 😉

Lets try and start the ORCL database –


D:\scripts>sqlplus sys/sys123 as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sat Sep 24 00:44:49 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  150667264 bytes
Fixed Size                  1382112 bytes
Variable Size              92277024 bytes
Database Buffers           50331648 bytes
Redo Buffers                6676480 bytes
Database mounted.
ORA-19838: Cannot use this control file to open database

How to resolve it?

SQL> alter database backup controlfile to trace as 'D:\oracle\backup\orcl\orcl_ctl.lst';

Database altered.

The controlfile trace showed

CREATE CONTROLFILE REUSE DATABASE "MATRIX" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ORACLE\ORADATA\MATRIX\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'D:\ORACLE\ORADATA\MATRIX\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'D:\ORACLE\ORADATA\MATRIX\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  'D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
  'D:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF',
  'D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
  'D:\ORACLE\ORADATA\ORCL\USERS01.DBF',
  'D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
  'D:\ORACLE\ORADATA\ORCL\TEST_01.DBF'
CHARACTER SET AL32UTF8
;

The controlfile still has the db_name as matrix and the redo logfile location as D:\ORACLE\ORADATA\MATRIX\ which is for the target.


SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      MATRIX
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      ORCL
SQL> alter system set db_name=ORCL scope=spfile;

System altered.

recreate the controlfile with new db name and correct redo logfile location –

SQL> startup nomount
ORACLE instance started.

Total System Global Area  150667264 bytes
Fixed Size                  1382112 bytes
Variable Size              92277024 bytes
Database Buffers           50331648 bytes
Redo Buffers                6676480 bytes
SQL>
SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      ORCL
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      ORCL
SQL>
SQL>
SQL> CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'D:\ORACLE\ORADATA\ORCL\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\ORACLE\ORADATA\ORCL\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\ORACLE\ORADATA\ORCL\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    'D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
 14    'D:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF',
 15    'D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
 16    'D:\ORACLE\ORADATA\ORCL\USERS01.DBF',
 17    'D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
 18    'D:\ORACLE\ORADATA\ORCL\TEST_01.DBF'
 19  CHARACTER SET AL32UTF8
 20  ;

Control file created.

Once the controlfile is re-created, simply open the database with resetlogs option


SQL> alter database open resetlogs;

Database altered.

7 thoughts on “Duplicating Database without connecting to Target – 11gR2

  1. Hi Anand,

    Nice demo..for upcoming dba’s.
    Only doubt and I couldn’t recollect how the rman is getting notified the scn 937053. It’s from headers of the file or backupset. I have forgotten the underneath working of this information. I hope if you re-collect.

    1. Hi Pavan,

      As far as i remember the scn is the CONTROLFILE_CHANGE# number in the controfile when the backup was taken. So the database is recovered till that point.

      Anand

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