RMAN Backup and restore different Host

This posting is about restore and recover a database to different host. This procedure requires DBID of the original database. This is very usefully to copy a database to different datacenter. I mean to transporting using Disk/Tape/USB etc..

First step is to take a COLD backup of the source database. COLD BACKUP does not require online log file for recovery, In fact it does not need a recovery, because it is consistant. Same method can be used for HOT back up as well, but that need a recovery at the target side.

1. In source machine(Source)

$cat rman_cold_backup.rman
run {
shutdown immediate;
startup mount;
allocate channel ch1 device type disk format ‘/opt/app/oracle/backup/backup/DEVtst_backup_%U’;
allocate channel ch2 device type disk format ‘/opt/app/oracle/backup/backup/DEVtst_backup_%U’;
backup current controlfile TAG=DEVtst_backup_ctl;
backup database;
release channel ch1;
release channel ch2;
alter database open;
}

$ rman target / nocatalog cmdfile=rman_cold_backup.rman

Recovery Manager: Release 10.2.0.4.0 – Production on Wed May 25 18:20:41 2011

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

connected to target database: DEVTST (DBID=3803722301)
using target database control file instead of recovery catalog

RMAN> run {
2> shutdown immediate;
3> startup mount;
4> allocate channel ch1 device type disk format ‘/opt/app/oracle/backup/backup/DEVtst_backup_%U’;
5> allocate channel ch2 device type disk format ‘/opt/app/oracle/backup/backup/DEVtst_backup_%U’;
6> backup current controlfile TAG=DEVtst_backup_ctl;
7> backup database;
8> release channel ch1;
9> release channel ch2;
10> alter database open;
11> }
12>
database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 629145600 bytes

Fixed Size 2140960 bytes
Variable Size 169694432 bytes
Database Buffers 452984832 bytes
Redo Buffers 4325376 bytes

allocated channel: ch1
channel ch1: sid=249 devtype=DISK

allocated channel: ch2
channel ch2: sid=504 devtype=DISK

Starting backup at 25-MAY-11
channel ch1: starting full datafile backupset
channel ch1: specifying datafile(s) in backupset
including current control file in backupset
channel ch1: starting piece 1 at 25-MAY-11
channel ch1: finished piece 1 at 25-MAY-11
piece handle=/opt/app/oracle/backup/backup/DEVtst_backup_01md80gm_1_1 tag=DEVTST_BACKUP_CTL comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:02
Finished backup at 25-MAY-11

Starting backup at 25-MAY-11
channel ch1: starting full datafile backupset
channel ch1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oradata/DEVtst/system01.dbf
input datafile fno=00002 name=/u03/oradata/DEVtst/undotbs01.dbf
channel ch1: starting piece 1 at 25-MAY-11
channel ch2: starting full datafile backupset
channel ch2: specifying datafile(s) in backupset
input datafile fno=00003 name=/u01/oradata/DEVtst/sysaux01.dbf
input datafile fno=00004 name=/u01/oradata/DEVtst/users01.dbf
channel ch2: starting piece 1 at 25-MAY-11
channel ch2: finished piece 1 at 25-MAY-11
piece handle=/opt/app/oracle/backup/backup/DEVtst_backup_03md80gp_1_1 tag=TAG20110525T182112 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:03
channel ch2: starting full datafile backupset
channel ch2: specifying datafile(s) in backupset
including current control file in backupset
channel ch2: starting piece 1 at 25-MAY-11
channel ch1: finished piece 1 at 25-MAY-11
piece handle=/opt/app/oracle/backup/backup/DEVtst_backup_02md80gp_1_1 tag=TAG20110525T182112 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:04
channel ch1: starting full datafile backupset
channel ch1: specifying datafile(s) in backupset
channel ch2: finished piece 1 at 25-MAY-11
piece handle=/opt/app/oracle/backup/backup/DEVtst_backup_04md80gt_1_1 tag=TAG20110525T182112 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:01
including current SPFILE in backupset
channel ch1: starting piece 1 at 25-MAY-11
channel ch1: finished piece 1 at 25-MAY-11
piece handle=/opt/app/oracle/backup/backup/DEVtst_backup_05md80gu_1_1 tag=TAG20110525T182112 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-MAY-11

released channel: ch1

released channel: ch2

database opened

Recovery Manager complete.

2. Determine the DBID of source machine (Source )

SQL> select dbid from v$database;

DBID
———-
3803722301

3. Now perform task on target machine.

First set ORACLE_SID,
$export ORACLE_SID=DEVTST
Copy the spfile from source database

Then connect to rman at the target machine and set the DBID collected from source database. After setting DBID, startup the database in nomount mode then restore the controlfile from backup. Location of the backup controlfile has to be specified in the recover command as RMAN does not know anything about database backup without the target controlfile. Target recovery catalog is stored in the controlfile. If recovery catalog is used and it is accessible from the target machine we do not need give any backup location as long as the backup is there in the same as the original back on the target machine.

$ rman target /

Recovery Manager: Release 10.2.0.4.0 – Production on Wed May 25 20:55:59 2011

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

connected to target database (not started)

RMAN> set dbid 3803722301

executing command: SET DBID

RMAN> startup nomount;

Oracle instance started

Total System Global Area 629145600 bytes

Fixed Size 2042656 bytes
Variable Size 167777504 bytes
Database Buffers 452984832 bytes
Redo Buffers 6340608 bytes

RMAN> restore controlfile from ‘/u08/oradata/backup/DEVtst_backup_01md80gm_1_1’;

Starting restore at 25-MAY-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=498 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/oradata/DEVtst/control01.ctl
output filename=/u01/oradata/DEVtst/control02.ctl
output filename=/u01/oradata/DEVtst/control03.ctl
Finished restore at 25-MAY-11

controlfile will be restored to the location specified in the pfile

5. Start the instance with pfile

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

5. From SQL*Plus determine the data file and redo log file name.

This can be done on source database or target database. Following query will give list files.

select file#, name from v$datafile
union
select group#,member from v$logfile

After getting the filenames, you may want create a set newname or alter statement to rename files at the target machine.
It may not be possible to use same path/location for datafile and logfile at target database.

6. Catalog your backuppiece

During restore, RMAN will expect the backup sets to be located in the same directory as written to during the backup. For disk backups, the DBA can accomplish this in many ways:

– setup an NFS directory, mounted on both host A and host B
– create the same directory structure on host A and host B
– use of symbolic links on host B
– catalog the backup pieces

Here I’m going to catalog the backup pieces.
You can catalog one by one as follows
catalog backuppiece ‘/u08/oradata/backup’/DEVtst_backup_02md80gp_1_1’
Or catalog all the file in a specified directory
Catalog start with ‘/tmp/backups’ noprompt;

RMAN> catalog start with ‘/u08/oradata/backup’ noprompt;

Starting implicit crosscheck backup at 26-MAY-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=499 devtype=DISK
Finished implicit crosscheck backup at 26-MAY-11

Starting implicit crosscheck copy at 26-MAY-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-MAY-11

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

searching for all files that match the pattern /u08/oradata/backup

List of Files Unknown to the Database
=====================================
File Name: /u08/oradata/backup/DEVtst_backup_01md80gm_1_1
File Name: /u08/oradata/backup/DEVtst_backup_02md80gp_1_1
File Name: /u08/oradata/backup/DEVtst_backup_03md80gp_1_1
File Name: /u08/oradata/backup/DEVtst_backup_04md80gt_1_1
File Name: /u08/oradata/backup/DEVtst_backup_05md80gu_1_1
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /u08/oradata/backup/DEVtst_backup_01md80gm_1_1
File Name: /u08/oradata/backup/DEVtst_backup_02md80gp_1_1
File Name: /u08/oradata/backup/DEVtst_backup_03md80gp_1_1
File Name: /u08/oradata/backup/DEVtst_backup_04md80gt_1_1
File Name: /u08/oradata/backup/DEVtst_backup_05md80gu_1_1

RMAN> list backup;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
1 Full 6.77M DISK 00:00:00 25-MAY-11
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: DEVTST_BACKUP_CTL
Piece Name: /u08/oradata/backup/DEVtst_backup_01md80gm_1_1
Control File Included: Ckp SCN: 180894 Ckp time: 25-MAY-11

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
2 Full 255.91M DISK 00:00:00 25-MAY-11
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20110525T182112
Piece Name: /u08/oradata/backup/DEVtst_backup_02md80gp_1_1
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 Full 180894 25-MAY-11 /u01/oradata/DEVtst/system01.dbf
2 Full 180894 25-MAY-11 /u03/oradata/DEVtst/undotbs01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
3 Full 29.78M DISK 00:00:00 25-MAY-11
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20110525T182112
Piece Name: /u08/oradata/backup/DEVtst_backup_03md80gp_1_1
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
3 Full 180894 25-MAY-11 /u01/oradata/DEVtst/sysaux01.dbf
4 Full 180894 25-MAY-11 /u01/oradata/DEVtst/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
4 Full 6.77M DISK 00:00:00 25-MAY-11
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20110525T182112
Piece Name: /u08/oradata/backup/DEVtst_backup_04md80gt_1_1
Control File Included: Ckp SCN: 180894 Ckp time: 25-MAY-11

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
5 Full 80.00K DISK 00:00:00 25-MAY-11
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20110525T182112
Piece Name: /u08/oradata/backup/DEVtst_backup_05md80gu_1_1
SPFILE Included: Modification time: 25-MAY-11

RMAN>

RMAN> @rman_restore.rman

RMAN> run {
2> allocate channel ch1 device type disk;
3> set newname for datafile 1 to ‘/u01/oradata/DEVtst/system01.dbf’;
4> set newname for datafile 2 to ‘/u02/oradata/DEVtst/undotbs01.dbf’;
5> set newname for datafile 3 to ‘/u01/oradata/DEVtst/sysaux01.dbf’;
6> set newname for datafile 4 to ‘/u01/oradata/DEVtst/users01.dbf’;
7> restore database;
8> switch datafile all;
9> release channel ch1;
10> }
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: sid=501 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 25-MAY-11

channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/DEVtst/system01.dbf
restoring datafile 00002 to /u02/oradata/DEVtst/undotbs01.dbf
channel ch1: reading from backup piece /u08/oradata/backup/DEVtst_backup_02md80gp_1_1
channel ch1: restored backup piece 1
piece handle=/u08/oradata/backup/DEVtst_backup_02md80gp_1_1 tag=TAG20110525T182112
channel ch1: restore complete, elapsed time: 00:00:16
channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u01/oradata/DEVtst/sysaux01.dbf
restoring datafile 00004 to /u01/oradata/DEVtst/users01.dbf
channel ch1: reading from backup piece /u08/oradata/backup/DEVtst_backup_03md80gp_1_1
channel ch1: restored backup piece 1
piece handle=/u08/oradata/backup/DEVtst_backup_03md80gp_1_1 tag=TAG20110525T182112
channel ch1: restore complete, elapsed time: 00:00:15
Finished restore at 25-MAY-11

datafile 2 switched to datafile copy
input datafile copy recid=5 stamp=752105177 filename=/u02/oradata/DEVtst/undotbs01.dbf

released channel: ch1

RMAN> **end-of-file**

7. Logfile renaming before recovery

If the destination log file are using different location. It has to be changed from SQL prompt.

Conn using SQLplus

sqplus “/ as sysdba”

SQL>alter database rename file ‘/u03/oradata/DEVtst/redo01a.log’ to ‘/u02/oradata/DEVtst/redo01a.log’;
SQL>alter database rename file ‘/u03/oradata/DEVtst/redo01b.log’ to ‘/u02/oradata/DEVtst/redo01b.log’;
SQL>alter database rename file ‘/u03/oradata/DEVtst/redo02a.log’ to ‘/u02/oradata/DEVtst/redo02a.log’;
SQL>alter database rename file ‘/u03/oradata/DEVtst/redo02b.log’ to ‘/u02/oradata/DEVtst/redo02b.log’;
SQL>alter database rename file ‘/u03/oradata/DEVtst/redo03a.log’ to ‘/u02/oradata/DEVtst/redo03a.log’;
SQL>alter database rename file ‘/u03/oradata/DEVtst/redo03b.log’ to ‘/u02/oradata/DEVtst/redo03b.log’;
SQL>alter database rename file ‘/u03/oradata/DEVtst/redo04a.log’ to ‘/u02/oradata/DEVtst/redo04a.log’;
SQL>alter database rename file ‘/u03/oradata/DEVtst/redo04b.log’ to ‘/u02/oradata/DEVtst/redo04b.log’;

8. Open the Database resetlogs option.

Since the backup was a COLD BACUP and we have not re-created the controlfile, We can just open the database. This will create online log file in the appropriate location.

RMAN> alter database open resetlogs;

database opened

Verify the alert log file for any errors.

Leave a Reply

Your email address will not be published. Required fields are marked *