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;
}
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
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
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
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
Variable Size 169694432 bytes
Database Buffers 452984832 bytes
Redo Buffers 4325376 bytes
allocated channel: ch1
channel ch1: sid=249 devtype=DISK
channel ch1: sid=249 devtype=DISK
allocated channel: ch2
channel ch2: sid=504 devtype=DISK
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-12
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-12
Starting backup at 25-MAY-12
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-12
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-12
channel ch2: finished piece 1 at 25-MAY-12
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-12
channel ch1: finished piece 1 at 25-MAY-12
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-12
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-12
channel ch1: finished piece 1 at 25-MAY-12
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-12
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-12
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-12
channel ch2: finished piece 1 at 25-MAY-12
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-12
channel ch1: finished piece 1 at 25-MAY-12
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-12
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-12
channel ch1: finished piece 1 at 25-MAY-12
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-12
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
———-
3803722301
3. Now perform task on target machine.
First set ORACLE_SID,
$export ORACLE_SID=DEVTST
Copy the spfile from source database
$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 2012
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
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
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-12
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-12
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
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
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.
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
– 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;
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-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=499 devtype=DISK
Finished implicit crosscheck backup at 26-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=499 devtype=DISK
Finished implicit crosscheck backup at 26-MAY-12
Starting implicit crosscheck copy at 26-MAY-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-MAY-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-MAY-12
searching for all files in the recovery area
cataloging files…
no files cataloged
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
=====================================
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
=======================
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-12
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
——- —- — ———- ———– ———— —————
1 Full 6.77M DISK 00:00:00 25-MAY-12
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-12
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
——- —- — ———- ———– ———— —————
2 Full 255.91M DISK 00:00:00 25-MAY-12
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-12
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
——- —- — ———- ———– ———— —————
3 Full 29.78M DISK 00:00:00 25-MAY-12
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-12
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-12
——- —- — ———- ———– ———— —————
4 Full 6.77M DISK 00:00:00 25-MAY-12
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-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
5 Full 80.00K DISK 00:00:00 25-MAY-12
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-12
——- —- — ———- ———– ———— —————
5 Full 80.00K DISK 00:00:00 25-MAY-12
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-12
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
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-12
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-12
No comments:
Post a Comment