Duplicating an ASM Database at Remote Host

You can use the create RMAN DUPLICATE command to duplicate database from target database backups while still retaining the original target database. The duplicate database can be either identical to the original database or contain only a subset of the original tablespaces. A duplicate database is a copy of the target database that you can run independently for a variety of purposes.

Pre-Requisite:

• To prepare for database duplication, you must first create an auxiliary instance (new database). For the duplication to work, you must connect RMAN to both the target (primary) database and an auxiliary instance started in NOMOUNT mode.
• You must have at least one auxiliary channel allocated on the auxiliary instance. The principal work of the duplication is performed by the auxiliary channel, which starts a server session on the duplicate host. This channel then restores the necessary backups of the primary database, uses them to create the duplicate database, and initiates recovery.
• All backups, copies of datafiles, and archived logs used for creating and recovering the duplicate database must be accessible by the server session on the duplicate host. If the duplicate host is different from the primary host, then you must make backups and copies on the primary host disks available to the remote node with the same full path name as in the primary database.

Step 1: Create an Oracle Password File for the Auxiliary Instance
Step 2: Ensure Oracle Net Connectivity to the Auxiliary Instance
Step 3: Create an Initialization Parameter File for the Auxiliary Instance
Step 4: Start the Auxiliary Instance
Step 5: Mount or Open the Target Database
Step 6: Make Sure You Have the Necessary Backups and Archived Redo Logs
Step 7: Allocate Auxiliary Channels if Automatic Channels Are Not Configured

Step 1: Create an Oracle Password File for the Auxiliary Instance

[oracle@]$ orapwd
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n>

where
file – name of password file (mand),
password – password for SYS (mand),
entries – maximum number of distinct DBA and force – whether to overwrite existing file (opt),OPERs (opt),
There are no spaces around the equal-to (=) character.
[oracle@]$ orapwd file=orapwtest password=test entries=10

Step 2: Ensure Oracle Net Connectivity to the Auxiliary Instance
Step 3: Create an Initialization Parameter File for the Auxiliary Instance

• Copy the Target init file on auxilliary instance. Replace the database name with new name , amend other parameters.
• Create bdump,cdump,udump,cdump and other directories at auxilliary instance.

Step 4: Start the Auxiliary Instance

[oracle@]$ echo $ORACLE_SID
test
[oracle@]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Dec 24 09:50:35 2009

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

SQL> conn sys/oracle@auxiliary as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.

Step 5: Mount or Open the Target Database

— connect to target database
CONNECT SYS/oracle@target
— mount or open target database
STARTUP

Step 6: Make Sure You Have the Necessary Backups and Archived Redo Logs

[oracle@target dbs]$ rman TARGET SYS/oracle@target

RMAN> list backup summary; — See the backup sets are available there

Step 7: Allocate Auxiliary Channels if Automatic Channels Are Not Configured

% rman TARGET sys/oracle@target AUXILIARY SYS/oracle@auxiliary

RUN
{
# to manually allocate a channel of type sbt issue:
ALLOCATE AUXILIARY CHANNEL ch1 DEVICE TYPE sbt;

# to manually allocate three auxiliary channels for disk issue (specifying whatever
# channel id that you want):
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 atest NOFILENAMECHECK;
}

I have tried this script in past and worked on RAC environment. Unfortunately this time it returned errors like

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/24/2008 11:45:49
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found – aborting restore
RMAN-06023: no backup or copy of datafile 10 found to restore

Reason for Error : Under RAC setup, the ASM instances at both nodes have access to same shared disks , here +BACKUP volume. The error indicates that it’s not able to locate backup sets so I decided to move asm backup files from Target to auxiliary ASM partitions.

• Setting up ASM ftp/http access and copying files from Target to Auxiliary database +BACKUP asm . Once backup sets are there, it’s easy to restore.

I picked up ftp/http access path but soon ran into the problems. I could copy files from Target ASM by installing ftp ASM access. But could not push them at Auxiliary because there were no running(open) oracle instance to compile @catxdbdbca. This means I can’t get ASM http/ftp access working at auxiliary database. I don’t see a way to get it working just for ASM instance.

• I am out of options now. I can see only one way forward, a) Copy ASM backup into disk volume, b) Move this backup set to remote node using ftp/scp and c) rman restore of backup , and finally d) recover the backup

a) Copy ASM backup into disk volume

run
{ allocate channel non_asm_disk device type disk format ‘/oraback/backup_%U;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG TAG = ‘Full_backup_2412_0318pm’; }

Starting backup at 24-DEC-09
channel non_asm_disk: starting compressed full datafile backupset
channel non_asm_disk: specifying datafile(s) in backupset
input datafile fno=00006 name=+DATA/Dev/datafile/devdata.276.665236665
input datafile fno=00002 name=+DATA/Dev/datafile/undotbs1.281.665236667
input datafile fno=00008 name=+DATA/Dev/datafile/devblob.278.665236667
input datafile fno=00007 name=+DATA/Dev/datafile/devindex.277.665236665
nput datafile fno=00001 name=+DATA/Dev/datafile/system.280.665236667
input datafile fno=00009 name=+DATA/Dev/datafile/devdoc.279.665236667
input datafile fno=00010 name=+DATA/Dev/datafile/devscratch.284.665236709
input datafile fno=00003 name=+DATA/Dev/datafile/sysaux.282.665236669
input datafile fno=00004 name=+DATA/Dev/datafile/users.264.682390947
channel non_asm_disk: starting piece 1 at 24-DEC-09
channel non_asm_disk: finished piece 1 at 24-DEC-09
piece handle=/oraback/backup_2412_0318_1nl1nmij_1_1 tag=TAG20091224T171355 comment=NONE
channel non_asm_disk: backup set complete, elapsed time: 00:52:28
Finished backup at 24-DEC-09

rman>backup as copy current controlfile format ‘/oraback/backup_controlfile’;

Starting backup at 29-DEC-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=/oraback/backup_controlfile tag=TAG20091229T125621 recid=189 stamp=706884988
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Once the full backup is done, I will also backup all archives needed for recovery

run
{
allocate channel non_asm_disk device type disk format ‘/oraback/archive_3012_%U’;
BACKUP ARCHIVELOG All; }

Using target database control file instead of recovery catalog
allocated channel: non_asm_disk
channel non_asm_disk: sid=159 devtype=DISK

Starting backup at 30-DEC-09
current log archived
channel non_asm_disk: starting archive log backupset
channel non_asm_disk: specifying archive log(s) in backup set
input archive log thread=1 sequence=5067 recid=32804 stamp=706908611
input archive log thread=1 sequence=5068 recid=32805 stamp=706908690
input archive log thread=1 sequence=5069 recid=32806 stamp=706911787
input archive log thread=1 sequence=5070 recid=32807 stamp=706918822
input archive log thread=1 sequence=5071 recid=32808 stamp=706928604
input archive log thread=1 sequence=5072 recid=32809 stamp=706955842
input archive log thread=1 sequence=5073 recid=32810 stamp=706974505
channel non_asm_disk: starting piece 1 at 30-DEC-09
channel non_asm_disk: finished piece 1 at 30-DEC-09
piece handle=/oraback/archive_3012_2ol274pb_1_1 tag=TAG20091230T134826 comment=NONE
channel non_asm_disk: backup set complete, elapsed time: 00:00:17
Finished backup at 30-DEC-09

• b) The backup sets were created at NFS share accessible to both machines.

• c) Rman restore of backup

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 599785472 bytes

Fixed Size 2022632 bytes
Variable Size 176161560 bytes
Database Buffers 419430400 bytes
Redo Buffers 2170880 bytes

RMAN> alter database mount;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/29/2009 16:26:30
ORA-00205: error in identifying control file, check alert log for more info

RMAN> restore controlfile from ‘/oraback/backup_controlfile’;

Starting restore at 29-DEC-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA/Dev/controlfile/current.257.706897629
output filename=+BACKUP/Dev/controlfile/current.257.706897629
Finished restore at 29-DEC-09

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 29-DEC-09
Starting implicit crosscheck backup at 29-DEC-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 21 objects
Finished implicit crosscheck backup at 29-DEC-09

Starting implicit crosscheck copy at 29-DEC-09
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 29-DEC-09

searching for all files in the recovery area
cataloging files…
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/Dev/datafile/system.280.665236667
restoring datafile 00002 to +DATA/Dev/datafile/undotbs1.281.665236667
restoring datafile 00003 to +DATA/Dev/datafile/sysaux.282.665236669
restoring datafile 00004 to +DATA/Dev/datafile/users.264.682390947
restoring datafile 00006 to +DATA/Dev/datafile/devdata.276.665236665
restoring datafile 00007 to +DATA/Dev/datafile/devindex.277.665236665
restoring datafile 00008 to +DATA/Dev/datafile/devblob.278.665236667
restoring datafile 00009 to +DATA/Dev/datafile/devdoc.279.665236667
restoring datafile 00010 to +DATA/Dev/datafile/devscratch.284.665236709
channel ORA_DISK_1: reading from backup piece /oraback/backup_2412_0318_2fl24a2h_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/exports/sagar/backup_2412_0318_2fl24a2h_1_1 tag=TAG20091229T120016

d) recover the backup

RMAN> recover database;

Starting recover at 30-DEC-09
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5066
channel ORA_DISK_1: reading from backup piece /oraback/backup_2412_0318_2gl24d59_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/backup_2412_0318_2gl24d59_1_1 tag=FULL_BACKUP_2912_100AM
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
archive log filename=+BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5066.258.706968135 thread=1 sequence=5066
channel default: deleting archive log(s)
archive log filename=+BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5066.258.706968135 recid=32804 stamp=706968136
unable to find archive log
archive log thread=1 sequence=5067
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/30/2009 12:02:20
RMAN-06054: media recovery requesting unknown log: thread 1 seq 5067 lowscn 334193729

[oracle@wyg-asp ~]$
[oracle@wyg-asp ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Wed Dec 30 13:20:35 2009

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

connected to target database: Dev (DBID=1155472299, not open)

I need to restore archivelogs but before I do so, I need to Catalog backup piece using

RMAN> CATALOG BACKUPPIECE ‘/oraback/archive_3012_2ol274pb_1_1’;

Using target database control file instead of recovery catalog
cataloged backuppiece
backup piece handle=/oraback/archive_3012_2ol274pb_1_1 recid=3103 stamp=706972902

RMAN> run {
allocate channel t1 type DISK;
restore archivelog from logseq 5067 until logseq 5073;
release channel t1;
}

allocated channel: t1
channel t1: sid=160 devtype=DISK

Starting restore at 30-DEC-09

channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=5067
channel t1: restoring archive log
archive log thread=1 sequence=5068
channel t1: restoring archive log
archive log thread=1 sequence=5069
channel t1: restoring archive log
archive log thread=1 sequence=5070
channel t1: restoring archive log
archive log thread=1 sequence=5071
channel t1: restoring archive log
archive log thread=1 sequence=5072
channel t1: restoring archive log
archive log thread=1 sequence=5073
channel t1: reading from backup piece /oraback/exports/sagar/archive_3012_2ol274pb_1_1
channel t1: restored backup piece 1
piece handle=/oraback/archive_3012_2ol274pb_1_1 tag=TAG20091230T134826
channel t1: restore complete, elapsed time: 00:00:15
Finished restore at 30-DEC-09

released channel: t1

RMAN> recover database;

Starting recover at 30-DEC-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

starting media recovery

archive log thread 1 sequence 5067 is already on disk as file +BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5067.262.706972921
archive log thread 1 sequence 5068 is already on disk as file +BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5068.265.706972921
archive log thread 1 sequence 5069 is already on disk as file +BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5069.264.706972921
archive log thread 1 sequence 5070 is already on disk as file +BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5070.258.706972921
archive log thread 1 sequence 5071 is already on disk as file +BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5071.257.706972921
archive log thread 1 sequence 5072 is already on disk as file +BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5072.261.706972921
archive log thread 1 sequence 5073 is already on disk as file +BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5073.263.706972921
archive log filename=+BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5067.262.706972921 thread=1 sequence=5067
archive log filename=+BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5068.265.706972921 thread=1 sequence=5068
archive log filename=+BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5069.264.706972921 thread=1 sequence=5069
archive log filename=+BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5070.258.706972921 thread=1 sequence=5070
archive log filename=+BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5071.257.706972921 thread=1 sequence=5071
archive log filename=+BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5072.261.706972921 thread=1 sequence=5072
archive log filename=+BACKUP/Dev/archivelog/2009_12_30/thread_1_seq_5073.263.706972921 thread=1 sequence=5073
unable to find archive log
archive log thread=1 sequence=5074
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/30/2009 13:23:10
RMAN-06054: media recovery requesting unknown log: thread 1 seq 5074 lowscn 334380095

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/30/2009 13:23:19
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;
database opened