How to Duplicate a Standalone Database: ASM to ASM

Posted at Thursday, September 18, 2008
This example covers how to duplicate a 10.2.0.4 database using ASM from one host to another, which is also using ASM. This example uses RMAN without a catalog to disk, but could be quite easily retrofit to support tape channels or the use of a recovery catalog. The RMAN backup is written to a file system available to both hosts.

In my example, I am duplicating my Grid Control Database from one host to another (EMREP, duplicating to EMTST).

The term 'TARGET' is used to refer to the Primary Host/Database (The Database you are copying). The term 'AUXILIARY' is used to refer to the Duplicate Host/Database (The New Cloned Database you are creating).

Backup the TARGET Database (EMREP)

backup_emrep.rman

run {
configure device type disk parallelism 4 backup type to compressed backupset;
configure default device type to disk;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/u02/oracle/ora
1/emrep/%F';
configure maxsetsize to 5G;
configure snapshot controlfile name to '/u02/oracle/ora1/emrep/snapf_emrep.ora';
backup full database format '/u02/oracle/ora1/emrep/%d_%U.rman' tag rman_nocat_e
mrep plus archivelog format '/u02/oracle/ora1/emrep/%d_%U.rman' delete input tag
rman_nocat_emrep ;
}

run_backup.bsh

#!/bin/bash

export $ORACLE_SID=emrep

. oraenv

rman target=/ @backup_emrep.rman

Run the Backup

$ ./run_backup.bsh > rman_emrep.log

The output should look something like this:

$ more rman_emrep.log

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Sep 18 12:13:21 2008

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

connected to target database: EMREP (DBID=3888623110)

RMAN> run {
2> configure device type disk parallelism 4 backup type to compressed backupset;
3> configure default device type to disk;
4> configure controlfile autobackup on;
5> configure controlfile autobackup format for device type disk to '/u02/oracle/ora1/emrep/%F';
6> configure maxsetsize to 5G;
7> configure snapshot controlfile name to '/u02/oracle/ora1/emrep/snapf_emrep.ora';
8> backup full database format '/u02/oracle/ora1/emrep/%d_%U.rman' tag rman_nocat_emrep plus archivelog format '/u02/orac
le/ora1/emrep/%d_%U.rman' delete input tag rman_nocat_emrep ;
9> }
10>
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/oracle/ora1/emrep/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/oracle/ora1/emrep/%F';
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 5 G;
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 5 G;
new RMAN configuration parameters are successfully stored

snapshot control file name set to: /u02/oracle/ora1/emrep/snapf_emrep.ora
new RMAN configuration parameters are successfully stored

Starting backup at 18-SEP-08
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=135 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=144 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=125 devtype=DISK
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=68 recid=53 stamp=665756004
channel ORA_DISK_1: starting piece 1 at 18-SEP-08
channel ORA_DISK_1: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_24jqt8b5_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=+DATA2/emrep/archivelog/2008_09_18/thread_1_seq_68.263.665756005 recid=53 stamp=665756004
Finished backup at 18-SEP-08

Starting backup at 18-SEP-08
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=+DATA2/emrep/datafile/mgmt_tablespace.256.665690321
channel ORA_DISK_1: starting piece 1 at 18-SEP-08
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00001 name=+DATA2/emrep/datafile/system.257.665690973
input datafile fno=00006 name=+DATA2/emrep/datafile/mgmt_ecm_depot_ts.260.665691169
channel ORA_DISK_2: starting piece 1 at 18-SEP-08
channel ORA_DISK_3: starting compressed full datafile backupset
channel ORA_DISK_3: specifying datafile(s) in backupset
input datafile fno=00002 name=+DATA2/emrep/datafile/undotbs1.258.665691079
input datafile fno=00003 name=+DATA2/emrep/datafile/sysaux.259.665691133
channel ORA_DISK_3: starting piece 1 at 18-SEP-08
channel ORA_DISK_4: starting compressed full datafile backupset
channel ORA_DISK_4: specifying datafile(s) in backupset
input datafile fno=00004 name=+DATA2/emrep/datafile/users.261.665691205
channel ORA_DISK_4: starting piece 1 at 18-SEP-08
channel ORA_DISK_4: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_28jqt8b9_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:18
channel ORA_DISK_3: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_27jqt8b9_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:01:23
channel ORA_DISK_2: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_26jqt8b8_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:31
channel ORA_DISK_1: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_25jqt8b8_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:56
Finished backup at 18-SEP-08

Starting backup at 18-SEP-08
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=69 recid=54 stamp=665756192
channel ORA_DISK_1: starting piece 1 at 18-SEP-08
channel ORA_DISK_1: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_29jqt8h1_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=+DATA2/emrep/archivelog/2008_09_18/thread_1_seq_69.263.665756189 recid=54 stamp=665756192
Finished backup at 18-SEP-08

Starting Control File and SPFILE Autobackup at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/c-3888623110-20080918-01 comment=NONE
Finished Control File and SPFILE Autobackup at 18-SEP-08

Recovery Manager complete.

The filesystem should now house a series of files, similar to:

$ ls -lart
total 160138
drwxr-xr-x 9 oracle dba 4096 Sep 18 11:36 ../
-rw-r----- 1 oracle dba 376320 Sep 18 12:13 EMREP_24jqt8b5_1_1.rman
-rw-r----- 1 oracle dba 98304 Sep 18 12:13 EMREP_28jqt8b9_1_1.rman
-rw-r----- 1 oracle dba 76972032 Sep 18 12:14 EMREP_27jqt8b9_1_1.rman
-rw-r----- 1 oracle dba 54304768 Sep 18 12:14 EMREP_26jqt8b8_1_1.rman
-rw-r----- 1 oracle dba 22044672 Sep 18 12:16 EMREP_25jqt8b8_1_1.rman
-rw-r----- 1 oracle dba 100352 Sep 18 12:16 EMREP_29jqt8h1_1_1.rman
drwxr-xr-x 2 oracle dba 4096 Sep 18 12:16 ./
-rw-r----- 1 oracle dba 5079040 Sep 18 12:16 c-3888623110-20080918-01
-rw-r----- 1 oracle dba 4997120 Sep 18 12:16 snapf_emrep.ora

Prepare the Auxiliary Host for the Duplicate Database (EMTST)

Update /etc/oratab

echo "emtst:/u01/app/oracle/product/10.2:N" >> /etc/oratab

Create a password file for the Database (EMTST)

$ cd $ORACLE_HOME/dbs
$ orapwd file=orapwemtst password=shine123

Create any Required Directories for the Database (EMTST)

$ mkdir -p /u01/app/oracle/admin/emtst/bdump
$ mkdir -p /u01/app/oracle/admin/emtst/udump
$ mkdir -p /u01/app/oracle/admin/emtst/cdump
$ mkdir -p /u01/app/oracle/admin/emtst/adump

Update the Oracle Networking Files as appropriate (EMTST)

$ cd $TNS_ADMIN
$ vi tnsnames.ora

EMREP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.colestock.test)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = emrep.colestock.test)
(INSTANCE_NAME = emrep)
)
)

EMTST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2.colestock.test)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = emtst.colestock.test)
(INSTANCE_NAME = emtst)
)
)

Test connectivity to all services involved in the DUPLICATE operation, for example:

$ tnsping emrep

TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 18-SEP-2008 13:27:46

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.colestock.test)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = emrep.colestock.test) (INSTANCE_NAME = emrep)))
OK (0 msec)

Create/Update the Listener with a Static Entry (EMTST)

$ vi listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2.colestock.test)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = emtst.colestock.test)
(ORACLE_HOME = /u01/app/oracle/product/10.2)
(SID_NAME = emtst)
)
)

If creating a new LISTENER, start it:

$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 18-SEP-2008 13:26:13

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Starting /u01/app/oracle/product/11.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /u01/app/oracle/product/11.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.colestock.test)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 18-SEP-2008 13:26:15
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.colestock.test)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "emtst.colestock.test" has 1 instance(s).
Instance "emtst", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Create Necessary ASM Directories on the Host of the New Database (EMTST)

$ export ORACLE_SID=+ASM2
$ . oraenv
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1 is /u01/app/oracle
$ asmcmd
ASMCMD> cd DATA
ASMCMD> mkdir EMTST
ASMCMD> cd EMTST
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir ONLINELOG

Copy the Parameter File from the TARGET to the AUXILIARY(EMREP to EMTST)

$ export ORACLE_SID=emrep
$ . oraenv
$ sqlplus "/ as sysdba"
SQL> create pfile='/tmp/initemtst.ora' from spfile;
scp /tmp/initemtst.ora oracle@rac2:/u01/app/oracle/product/10.2/dbs/.
oracle@rac2's password:
initemtst.ora 100% 1409 1.4KB/s 00:00
cd $ORACLE_HOME/dbs

Edit the Parameter File, Changing Necessary Values on Auxiliary Host (EMTST)

In this example, the Diskgroup is switched from DATA to DATA2. I will create the online redo logs as part of the DUPLICATE clause.

vi initemtst.ora

*.background_dump_dest='/u01/app/oracle/admin/emtst/bdump'
*.core_dump_dest='/u01/app/oracle/admin/emtst/cdump'
*.db_file_name_convert=('+DATA2','+DATA')
*.control_files='+DATA/EMTST/CONTROLFILE/control01.ctl','+DATA/EMTST/CONTROLFILE/control02.ctl','+DATA/EMTST/CONTROLFILE/control03.ctl'
*.db_name='emtst'
*.log_archive_dest_1='LOCATION=+DATA'
*.log_archive_format='emtst_%t_%s_%r.arch'
*.user_dump_dest='/u01/app/oracle/admin/emtst/udump'

Create an SPFILE and startup nomount the Auxiliary Instance

SQL> create spfile from pfile;

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1268484 bytes
Variable Size 503317756 bytes
Database Buffers 25165824 bytes
Redo Buffers 7118848 bytes

Create a Connection File that Obfuscates Passwords for RMAN

connect.rman

connect auxiliary / ;
connect target sys/password@emrep ;

Change the file's ownership:

chmod 711 connect.rman

Perform the DUPLICATE operation on the Auxiliary Host

duplicate_emrep_to_emtst.rman

@connect.rman
run {
allocate auxiliary channel aux1 device type disk;
allocate auxiliary channel aux2 device type disk;
allocate auxiliary channel aux3 device type disk;
duplicate target database to emtst logfile
group 1 ('+DATA/EMTST/ONLINELOG/redo_0101.log','+DATA/EMTST/ONLINELOG/redo_0102.log') SIZE 100M REUSE,
group 2 ('+DATA/EMTST/ONLINELOG/redo_0201.log','+DATA/EMTST/ONLINELOG/redo_0202.log') SIZE 100M REUSE,
group 3 ('+DATA/EMTST/ONLINELOG/redo_0301.log','+DATA/EMTST/ONLINELOG/redo_0302.log') SIZE 100M REUSE,
group 4 ('+DATA/EMTST/ONLINELOG/redo_0401.log','+DATA/EMTST/ONLINELOG/redo_0402.log') SIZE 100M REUSE;
}

Run the aforementioned script

rman @duplicate_emrep_to_emtst.rman > duplicate_emrep_to_emtst.log &

A successful DUPLICATE should yield output similar to this:

$ more duplicate_emrep_to_emtst.log

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Sep 18 16:18:02 2008

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

RMAN> @connect.rman
2> connect auxiliary *;
3> connect target *;
4> **end-of-file**
5> run {
6> allocate auxiliary channel aux1 device type disk;
7> allocate auxiliary channel aux2 device type disk;
8> allocate auxiliary channel aux3 device type disk;
9> duplicate target database to emtst logfile
10> group 1 ('+DATA/EMTST/ONLINELOG/redo_0101.log','+DATA/EMTST/ONLINELOG/redo_0102.log') SIZE 100M REUSE,
11> group 2 ('+DATA/EMTST/ONLINELOG/redo_0201.log','+DATA/EMTST/ONLINELOG/redo_0202.log') SIZE 100M REUSE,
12> group 3 ('+DATA/EMTST/ONLINELOG/redo_0301.log','+DATA/EMTST/ONLINELOG/redo_0302.log') SIZE 100M REUSE,
13> group 4 ('+DATA/EMTST/ONLINELOG/redo_0401.log','+DATA/EMTST/ONLINELOG/redo_0402.log') SIZE 100M REUSE;
14> }
15>
connected to auxiliary database: EMTST (not mounted)

connected to target database: EMREP (DBID=3888623110)

using target database control file instead of recovery catalog
allocated channel: aux1
channel aux1: sid=156 devtype=DISK

allocated channel: aux2
channel aux2: sid=155 devtype=DISK

allocated channel: aux3
channel aux3: sid=154 devtype=DISK

Starting Duplicate Db at 18-SEP-08
WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to diskgroup only.

contents of Memory Script:
{
set until scn 5512981;
set newname for datafile 1 to
"+data";
set newname for datafile 2 to
"+data";
set newname for datafile 3 to
"+data";
set newname for datafile 4 to
"+data";
set newname for datafile 5 to
"+data";
set newname for datafile 6 to
"+data";
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 18-SEP-08

channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +DATA
channel aux1: reading from backup piece /u02/oracle/ora1/emrep/EMREP_28jqt8b9_1_1.rman
channel aux2: starting datafile backupset restore
channel aux2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DATA
restoring datafile 00003 to +DATA
channel aux2: reading from backup piece /u02/oracle/ora1/emrep/EMREP_27jqt8b9_1_1.rman
channel aux3: starting datafile backupset restore
channel aux3: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA
restoring datafile 00006 to +DATA
channel aux3: reading from backup piece /u02/oracle/ora1/emrep/EMREP_26jqt8b8_1_1.rman
channel aux1: restored backup piece 1
piece handle=/u02/oracle/ora1/emrep/EMREP_28jqt8b9_1_1.rman tag=RMAN_NOCAT_EMREP
channel aux1: restore complete, elapsed time: 00:00:15
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +DATA
channel aux1: reading from backup piece /u02/oracle/ora1/emrep/EMREP_25jqt8b8_1_1.rman
channel aux2: restored backup piece 1
piece handle=/u02/oracle/ora1/emrep/EMREP_27jqt8b9_1_1.rman tag=RMAN_NOCAT_EMREP
channel aux2: restore complete, elapsed time: 00:01:20
channel aux3: restored backup piece 1
piece handle=/u02/oracle/ora1/emrep/EMREP_26jqt8b8_1_1.rman tag=RMAN_NOCAT_EMREP
channel aux3: restore complete, elapsed time: 00:01:20
channel aux1: restored backup piece 1
piece handle=/u02/oracle/ora1/emrep/EMREP_25jqt8b8_1_1.rman tag=RMAN_NOCAT_EMREP
channel aux1: restore complete, elapsed time: 00:02:30
Finished restore at 18-SEP-08
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "EMTST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ( '+DATA/EMTST/ONLINELOG/redo_0101.log', '+DATA/EMTST/ONLINELOG/redo_0102.log' ) SIZE 100 M REUSE,
GROUP 2 ( '+DATA/EMTST/ONLINELOG/redo_0201.log', '+DATA/EMTST/ONLINELOG/redo_0202.log' ) SIZE 100 M REUSE,
GROUP 3 ( '+DATA/EMTST/ONLINELOG/redo_0301.log', '+DATA/EMTST/ONLINELOG/redo_0302.log' ) SIZE 100 M REUSE,
GROUP 4 ( '+DATA/EMTST/ONLINELOG/redo_0401.log', '+DATA/EMTST/ONLINELOG/redo_0402.log' ) SIZE 100 M REUSE
DATAFILE
'+DATA/emtst/datafile/system.282.665770693'
CHARACTER SET AL32UTF8


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

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=665770855 filename=+DATA/emtst/datafile/undotbs1.283.665770693
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=665770855 filename=+DATA/emtst/datafile/sysaux.292.665770695
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=665770855 filename=+DATA/emtst/datafile/users.284.665770693
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=665770855 filename=+DATA/emtst/datafile/mgmt_tablespace.291.665770703
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=665770855 filename=+DATA/emtst/datafile/mgmt_ecm_depot_ts.293.665770695

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

executing command: SET until clause

Starting recover at 18-SEP-08

starting media recovery

archive log thread 1 sequence 70 is already on disk as file +DATA2/emrep/archivelog/2008_09_18/thread_1_seq_70.
280.665766251
channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=69
channel aux1: reading from backup piece /u02/oracle/ora1/emrep/EMREP_29jqt8h1_1_1.rman
channel aux1: restored backup piece 1
piece handle=/u02/oracle/ora1/emrep/EMREP_29jqt8h1_1_1.rman tag=RMAN_NOCAT_EMREP
channel aux1: restore complete, elapsed time: 00:00:02
archive log filename=+DATA/emtst/archivelog/2008_09_18/thread_1_seq_69.287.665770857 thread=1 sequence=69
channel clone_default: deleting archive log(s)
archive log filename=+DATA/emtst/archivelog/2008_09_18/thread_1_seq_69.287.665770857 recid=1 stamp=665770856
archive log filename=+DATA2/emrep/archivelog/2008_09_18/thread_1_seq_70.280.665766251 thread=1 sequence=70
media recovery complete, elapsed time: 00:00:32
Finished recover at 18-SEP-08

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 536870912 bytes

Fixed Size 1268484 bytes
Variable Size 503317756 bytes
Database Buffers 25165824 bytes
Redo Buffers 7118848 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "EMTST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ( '+DATA/EMTST/ONLINELOG/redo_0101.log', '+DATA/EMTST/ONLINELOG/redo_0102.log' ) SIZE 100 M REUSE,
GROUP 2 ( '+DATA/EMTST/ONLINELOG/redo_0201.log', '+DATA/EMTST/ONLINELOG/redo_0202.log' ) SIZE 100 M REUSE,
GROUP 3 ( '+DATA/EMTST/ONLINELOG/redo_0301.log', '+DATA/EMTST/ONLINELOG/redo_0302.log' ) SIZE 100 M REUSE,
GROUP 4 ( '+DATA/EMTST/ONLINELOG/redo_0401.log', '+DATA/EMTST/ONLINELOG/redo_0402.log' ) SIZE 100 M REUSE
DATAFILE
'+DATA/emtst/datafile/system.282.665770693'
CHARACTER SET AL32UTF8


contents of Memory Script:
{
set newname for tempfile 2 to
"+data";
switch clone tempfile all;
catalog clone datafilecopy "+DATA/emtst/datafile/undotbs1.283.665770693";
catalog clone datafilecopy "+DATA/emtst/datafile/sysaux.292.665770695";
catalog clone datafilecopy "+DATA/emtst/datafile/users.284.665770693";
catalog clone datafilecopy "+DATA/emtst/datafile/mgmt_tablespace.291.665770703";
catalog clone datafilecopy "+DATA/emtst/datafile/mgmt_ecm_depot_ts.293.665770695";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 2 to +data in control file

cataloged datafile copy
datafile copy filename=+DATA/emtst/datafile/undotbs1.283.665770693 recid=1 stamp=665770907

cataloged datafile copy
datafile copy filename=+DATA/emtst/datafile/sysaux.292.665770695 recid=2 stamp=665770907

cataloged datafile copy
datafile copy filename=+DATA/emtst/datafile/users.284.665770693 recid=3 stamp=665770907

cataloged datafile copy
datafile copy filename=+DATA/emtst/datafile/mgmt_tablespace.291.665770703 recid=4 stamp=665770907

cataloged datafile copy
datafile copy filename=+DATA/emtst/datafile/mgmt_ecm_depot_ts.293.665770695 recid=5 stamp=665770907

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=665770907 filename=+DATA/emtst/datafile/undotbs1.283.665770693
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=665770907 filename=+DATA/emtst/datafile/sysaux.292.665770695
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=665770907 filename=+DATA/emtst/datafile/users.284.665770693
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=665770907 filename=+DATA/emtst/datafile/mgmt_tablespace.291.665770703
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=665770907 filename=+DATA/emtst/datafile/mgmt_ecm_depot_ts.293.665770695

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

database opened
Finished Duplicate Db at 18-SEP-08
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-12005: error during channel cleanup
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Recovery Manager complete.

Logon to the the Auxiliary Database (EMTST) and Verify

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/emtst/datafile/system.282.665770693
+DATA/emtst/datafile/undotbs1.283.665770693
+DATA/emtst/datafile/sysaux.292.665770695
+DATA/emtst/datafile/users.284.665770693
+DATA/emtst/datafile/mgmt_tablespace.291.665770703
+DATA/emtst/datafile/mgmt_ecm_depot_ts.293.665770695

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/emtst/controlfile/control01.ctl
+DATA/emtst/controlfile/control02.ctl
+DATA/emtst/controlfile/control03.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/emtst/onlinelog/redo_0401.log
+DATA/emtst/onlinelog/redo_0402.log
+DATA/emtst/onlinelog/redo_0301.log
+DATA/emtst/onlinelog/redo_0302.log
+DATA/emtst/onlinelog/redo_0201.log
+DATA/emtst/onlinelog/redo_0202.log
+DATA/emtst/onlinelog/redo_0101.log
+DATA/emtst/onlinelog/redo_0102.log

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/emtst/tempfile/temp.295.665770955




Here is how to adjust the aforementioned example to use an RMAN Recovery Catalog

Create the RMAN Recovery Catalog

$ export ORACLE_SID=jlc2
$ . oraenv
$ sqlplus "/ as sysdba"

SQL> create user rman identified by rman
2 temporary tablespace temp
3 default tablespace users quota unlimited on users;

User created.

SQL> grant recovery_catalog_owner to rman;

Grant succeeded.

Create the Recovery Catalog and Register the TARGET Database (EMREP)

$ export ORACLE_SID=emrep
$ . oraenv
$ rman catalog=rman/rman@jlcc target=/

RMAN> create catalog;

recovery catalog created

RMAN> register database;

Create a Connection File on the TARGET to obfuscate the RMAN password

rman.connect

connect catalog rman/rman@jlc;

Modify the file's permissions

$ chmod 711 rman.connect

Add the following line to the previously created backup script, backup_emrep.rman

@rman.connect

Create a New Backup of the TARGET (EMREP)

./run_backup.bsh > rman_emrep.log

Update the Connect File on the AUXILIARY (EMTST)

rman.connect

Add the following line:

connect catalog rman/rman@jlc;

Drop the Previously Created Database (EMTST)

$ export ORACLE_SID=emtst
$ . oraenv

SQL> shutdown immediate;
SQL> startup restrict mount;
SQL> drop database;
SQL> exit;

$ sqlplus "/ as sysdba"

SQL> startup nomount;
SQL> exit

Duplicate the Database using the Recovery Catalog

rman @duplicate_emrep_to_emtst.rman > duplicate_emrep_to_emtst.log &




You may have noticed the following error earlier on in this example:
"WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to diskgroup only"

Using OMF (Oracle Managed Files) and ASM with this parameter is a little tricky. I prefer to discontinue the use of OMF by relocating the files and renaming them; for example:

RMAN> COPY DATAFILE '+DATA2/emrep/datafile/undotbs1.258.665691079' TO '+DATA2/emrep/datafile/undotbs01.dbf';


SQL> alter database rename file '+DATA2/emrep/datafile/undotbs1.258.665691079' TO '+DATA2/emrep/datafile/undotbs01.dbf';

Once all the files have been explicitly named, file name conversion becomes much more straightforward.

If you are using explicitly named files on ASM, then you can alter db_file_name_convert; for example:

*.db_file_name_convert=('+DATA2/emrep','+DATA/emtst')

Provided that the necessary directories are there, this should work. You will, however, see the aforementioned error message. The intended mapping should - in essence - have the intended effects.

Labels: ,