This document will detail the steps to restore a database on a new host
using RMAN. For this demonstration we will be using Oracle Database 11gR2 on solaris
and disk based backup.
One of the first things we want to do
is get the Database ID (DBID) from the database in which we will
be restoring its backup. You can find the DBID by connecting to
the database with RMAN as shown below.
|
[oracle@ora1 ~]$ rman
Recovery Manager: Release
11.2.0.1.0 - Production on Tue Apr 27 09:40:34 2014
Copyright (c) 1982, 2009,
Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database:
ORCL (DBID=1201234856)
RMAN>
|
If you are unable to connect to the
database you can also find the DBID by inspecting the
file name of the control file autobackup. For example, c-1201234856-20140427-00, is a control file
autobackup. The first group of numbers appearing after the c-, 123915029, is the DBID.
When we restore the database we are
not going to have the active redo logs from the original database so we want to
stop the recovery at the last SCN in the archive
logs. We can find the last SCN by querying V$ARCHIVED_LOG.
|
SQL> select max(next_change#)
2 from v$archived_log
3 where archived = 'YES'
4 group by thread#;
MAX(NEXT_CHANGE#)
-----------------
1275229
|
Here we see that the archive logs
contain log information up to SCN 1275229. That is
our stopping SCN. We can also find the last SCN in our archive logs
through RMAN using LIST BACKUP OF ARCHIVELOG ALL.
|
RMAN> list backup of
archivelog all;
List of Backup Sets
===================
BS Key Size
Device Type Elapsed Time Completion Time
------- ---------- -----------
------------ ---------------
54
2.09M
DISK
00:00:01 27-APR-14
BP
Key: 54 Status: AVAILABLE Compressed: NO Tag: TAG20140427T094350
Piece
Name: /u03/app/oracle/oradata/orcl/backup/1vlc5nqn_1_1
List of Archived
Logs in backup set 54
Thrd
Seq Low SCN Low Time Next
SCN Next Time
---- -------
---------- --------- ---------- ---------
1
12 1259005 26-APR-14 1259528
26-APR-14
1
13 1259528 26-APR-14 1259609
26-APR-14
1
14 1259609 26-APR-14 1262018
26-APR-14
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- -----------
------------ ---------------
55
13.82M DISK
00:00:02 27-APR-14
BP
Key: 55 Status: AVAILABLE Compressed: NO Tag: TAG20140427T094350
Piece
Name: /u03/app/oracle/oradata/orcl/backup/20lc5nqr_1_1
List of Archived
Logs in backup set 55
Thrd
Seq Low SCN Low Time Next
SCN Next Time
---- -------
---------- --------- ---------- ---------
1
1 1262019 26-APR-14 1274991
27-APR-14
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- -----------
------------ ---------------
60
136.00K DISK
00:00:00 27-APR-14
BP
Key: 60 Status: AVAILABLE Compressed: NO Tag: TAG20140427T094652
Piece
Name: /u03/app/oracle/oradata/orcl/backup/24lc5o0c_1_1
List of Archived
Logs in backup set 60
Thrd
Seq Low SCN Low Time Next
SCN Next Time
---- -------
---------- --------- ---------- ---------
1
2 1274991 27-APR-14 1275229
27-APR-14
RMAN>
|
Looking through the output above we see that highest Next SCN is 1275229
which is part of backup set 60.
Next we need to make the backup available to the new host.
Set the ORACLE_SID to the SID of the database
that made the backup and start up RMAN and connect to the target.
|
[oracle@ora2 backup]$ export
ORACLE_SID=orcl
[oracle@ora2 backup]$ rman
Recovery Manager: Release
11.2.0.1.0 - Production on Tue Apr 27 11:27:23 2014
Copyright (c) 1982, 2009,
Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database
(not started)
RMAN>
|
We now need to set the DBID and bring the
database in mount mode. When we bring the database up in mount mode we will
receive errors because the parameter file is not found. This is to be expected
as we are restoring to a new host. Oracle will use a “dummy” parameter file for
the time being.
|
RMAN> set dbid 1201234856
executing command: SET DBID
RMAN> startup nomount
startup failed: ORA-01078:
failure in processing system parameters
LRM-00109: could not open
parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'
starting Oracle instance
without parameter file for retrieval of spfile
Oracle instance started
Total System Global
Area 159019008 bytes
Fixed
Size
1335192 bytes
Variable
Size
75497576 bytes
Database
Buffers
79691776 bytes
Redo
Buffers
2494464 bytes
RMAN>
|
Now with the database in mount mode
we need to restore the SPFILE. We are going to need to make some changes to the
SPFILE so we will restore SPFILE to a PFILE. The SPFILE is stored in the
control file autobackup. All of the RMAN configuration parameters values are at
their defaults so we will need to set the location for the control file
autobackup.
|
RMAN> show controlfile
autobackup format;
RMAN configuration parameters
for database with db_unique_name DUMMY are:
CONFIGURE CONTROLFILE
AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
RMAN>
|
Below we will set the location for
the control file autobackup and restore the SPFILE to a PFILE and then we
shutdown the database.
|
RMAN> set controlfile
autobackup format for device type disk to
'/u01/app/oracle/oradata/orcl/backup/%F';
executing command: SET CONTROLFILE
AUTOBACKUP FORMAT
RMAN> restore spfile
2> to
pfile '/u01/app/oracle/oradata/orcl/initorcl.ora'
3> from
autobackup;
Starting restore at 27-APR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19
device type=DISK
channel ORA_DISK_1: looking for
AUTOBACKUP on day: 20140427
channel ORA_DISK_1: AUTOBACKUP
found: /u01/app/oracle/oradata/orcl/backup/c-1201234856-20140427-00
channel ORA_DISK_1: restoring
spfile from AUTOBACKUP /u01/app/oracle/oradata/orcl/backup/c-1201234856-20140427-00
channel ORA_DISK_1: SPFILE
restore from AUTOBACKUP complete
Finished restore at 27-APR-14
RMAN> shutdown abort;
Oracle instance shut down
RMAN>
|
Now that we have the SPFILE we need to edit the
some of the parameter to reflect the new host’s file system were appropriate.
Below is a list of the some of the parameter you might need to change in your
environment. These directories must exist on the new host.
|
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.control_files='/u02/app/oracle/oradata/orcl/ctl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl','/u03/app/oracle/oradata/orcl/ctl/control03.ctl'#Restore
Controlfile
*.db_recovery_file_dest_size=4039114752
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest_1='LOCATION=/u02/app/oracle/oradata/orcl/arch'
*.local_listener='LISTENER_ORCL'
|
After making the necessary changes to
the PFILE we now will bring
the database back up in NOMOUNT mode so we can
restore the control files. When bringing the database up in NOMOUNT mode we will use
the PFILE we edited earlier.
|
RMAN> startup nomount
pfile='/u01/app/oracle/oradata/orcl/initorcl.ora';
connected to target database
(not started)
Oracle instance started
Total System Global
Area 849530880 bytes
Fixed
Size
1339824 bytes
Variable
Size
528485968 bytes
Database
Buffers
314572800 bytes
Redo Buffers
5132288 bytes
RMAN>
|
Just like the SPFILE we will use the
autobackup to restore the control file after setting the control file
autobackup format. After restoring the control files we mount the database.
|
RMAN> set controlfile
autobackup format for device type disk to
'/u01/app/oracle/oradata/orcl/backup/%F';
executing command: SET
CONTROLFILE AUTOBACKUP FORMAT
RMAN> restore controlfile
from autobackup;
Starting restore at 27-APR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19
device type=DISK
recovery area destination:
/u01/app/oracle/flash_recovery_area
database name (or database
unique name) used for search: ORCL
channel ORA_DISK_1: no
AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for
AUTOBACKUP on day: 20140427
channel ORA_DISK_1: AUTOBACKUP
found: /u01/app/oracle/oradata/orcl/backup/c-1201234856-20140427-00
channel ORA_DISK_1: restoring
control file from AUTOBACKUP /u01/app/oracle/oradata/orcl/backup/c-1201234856-20140427-00
channel ORA_DISK_1: control
file restore from AUTOBACKUP complete
output file
name=/u02/oradata/orcl/ctl/control01.ctl
output file
name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
output file
name=/u03/app/oracle/oradata/orcl/ctl/control03.ctl
Finished restore at 27-APR-14
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN>
|
Now that the control files have been restored and mounted, all of the
RMAN configuration parameters have been set. You should verify the paths to
make sure they are appropriate for this host.
|
RMAN> show all;
RMAN configuration parameters
for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO
REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION
OFF; # default
CONFIGURE DEFAULT DEVICE TYPE
TO DISK; # default
CONFIGURE CONTROLFILE
AUTOBACKUP ON;
CONFIGURE CONTROLFILE
AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/app/oracle/oradata/orcl/backup/%F';
CONFIGURE DEVICE TYPE DISK
PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP
COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP
COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE
DISK FORMAT '/u03/app/oracle/oradata/orcl/backup/%U' MAXPIECESIZE
2G;
CONFIGURE MAXSETSIZE TO
UNLIMITED; # default
CONFIGURE ENCRYPTION FOR
DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM
'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM
'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION
POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
CONFIGURE SNAPSHOT CONTROLFILE
NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; #
default
RMAN>
|
Given the current settings RMAN is
going to look in /u03/app/oracle/oradata/orcl/backup for both the
control file autobackups and database/archive log backups. The backup we copied
over is located in/u01/app/oracle/oradata/orcl/backup. There are two
ways to resolve this issue. Either move the backup to the location were RMAN is
configured or change the configuration. It may be that moving the backups may
not be an option so we document what needs to be done next.
RMAN has record of a backup in /u03/app/oracle/oradata/orcl/backup but has no
record of the backup we have in/u01/app/oracle/oradata/orcl/backup/. We need remove
the record of the old location and the backup of the new location.
In order to let RMAN know about the
backup in /u01/app/oracle/oradata/orcl/backup we use
the CATALOG command.
|
RMAN> catalog start with
'/u01/app/oracle/oradata/orcl/backup';
Starting implicit crosscheck
backup at 27-APR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19
device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=20
device type=DISK
Crosschecked 32 objects
Finished implicit crosscheck
backup at 27-APR-14
Starting implicit crosscheck
copy at 27-APR-14
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck
copy at 27-APR-14
searching for all files in the
recovery area
cataloging files...
no files cataloged
searching for all files that
match the pattern /u01/app/oracle/oradata/orcl/backup
List of Files Unknown to the
Database
=====================================
File Name:
/u01/app/oracle/oradata/orcl/backup/20lc5nqr_1_1
File Name:
/u01/app/oracle/oradata/orcl/backup/23lc5ns4_1_1
File Name:
/u01/app/oracle/oradata/orcl/backup/c-1201234856-20140427-00
File Name:
/u01/app/oracle/oradata/orcl/backup/c-1201234856-20140426-00
File Name:
/u01/app/oracle/oradata/orcl/backup/21lc5nqv_1_1
File Name:
/u01/app/oracle/oradata/orcl/backup/1vlc5nqn_1_1
File Name:
/u01/app/oracle/oradata/orcl/backup/24lc5o0c_1_1
File Name:
/u01/app/oracle/oradata/orcl/backup/22lc5nqv_1_1
Do you really want to catalog
the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name:
/u01/app/oracle/oradata/orcl/backup/20lc5nqr_1_1
File Name:
/u01/app/oracle/oradata/orcl/backup/23lc5ns4_1_1
File Name:
/u01/app/oracle/oradata/orcl/backup/c-1201234856-20140427-00
File Name: /u01/app/oracle/oradata/orcl/backup/c-1201234856-20140426-00
File Name:
/u01/app/oracle/oradata/orcl/backup/21lc5nqv_1_1
File Name:
/u01/app/oracle/oradata/orcl/backup/1vlc5nqn_1_1
File Name:
/u01/app/oracle/oradata/orcl/backup/24lc5o0c_1_1
File Name:
/u01/app/oracle/oradata/orcl/backup/22lc5nqv_1_1
RMAN>
|
As a result of the CATALOG command RMAN
performed a CROSSCHECK. The backup
registered at/u03/app/oracle/oradata/orcl/backup/ was marked
expired as a result of the CROSSCHECK because they were
not present.
We are almost ready to restore the
database the last thing to be done is to build the restore script. During the
restore we can change the location of the data files by using SET
NEWNAME FOR DATAFILE. The data files in the backup are on various locations but we would like
to restore them to only one.
Also we would like to change the
location of the redo logs as well. In order to change the redo log locations
the ALTER DATABASE RENAME FILE command has
to be issued. Below are examples of both.
|
SET NEWNAME FOR DATAFILE 1 TO
'/u01/app/oracle/oradata/orcl/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO
'/u01/app/oracle/oradata/orcl/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 TO
'/u01/app/oracle/oradata/orcl/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 TO
'/u01/app/oracle/oradata/orcl/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO
'/u01/app/oracle/oradata/orcl/example01.dbf';
SET NEWNAME FOR DATAFILE 6 TO
'/u01/app/oracle/oradata/orcl/test.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/u01/app/oracle/oradata/orcl/dbfs01.dbf';
SQL "ALTER DATABASE RENAME
FILE ''/u02/app/oracle/oradata/orcl/redo/redo01.log''
TO
''/u01/app/oracle/oradata/orcl/redo/redo01.log'' ";
SQL "ALTER DATABASE RENAME
FILE ''/u03/app/oracle/oradata/orcl/redo/red01_b.log''
TO
''/u02/app/oracle/oradata/orcl/redo/red01_b.log'' ";
SQL "ALTER DATABASE RENAME
FILE ''/u02/app/oracle/oradata/orcl/redo/redo02.log''
TO
''/u01/app/oracle/oradata/orcl/redo/redo02.log'' ";
SQL "ALTER DATABASE RENAME
FILE ''/u03/app/oracle/oradata/orcl/redo/redo02_b.log''
TO
''/u02/app/oracle/oradata/orcl/redo/redo02_b.log'' ";
SQL "ALTER DATABASE RENAME
FILE
''/u02/app/oracle/oradata/orcl/redo/redo03.log''
TO
''/u01/app/oracle/oradata/orcl/redo/redo03.log'' ";
SQL "ALTER DATABASE RENAME
FILE ''/u03/app/oracle/oradata/orcl/redo/redo03_b.log''
TO
''/u02/app/oracle/oradata/orcl/redo/redo03_b.log'' ";
SQL "ALTER DATABASE RENAME
FILE
''/u02/app/oracle/oradata/orcl/redo/redo04.log''
TO
''/u01/app/oracle/oradata/orcl/redo/redo04.log'' ";
SQL "ALTER DATABASE RENAME
FILE ''/u03/app/oracle/oradata/orcl/redo/redo04_b.log''
TO
''/u02/app/oracle/oradata/orcl/redo/redo04_b.log'' ";
|
If you remember we stated that we
wanted the restore to stop at SCN 1275229 because that was the latest SCN in which we have
archive logs. In order to stop at a specific SCN we will use
the SET UNTIL SCN 1275229.
After all of that we just need to restore the database, switch the data
files so the control files have the new path and recover the database. Not much
at all.
Below is the entire script to do just that.
|
RUN {
SET NEWNAME FOR
DATAFILE 1 TO '/u01/app/oracle/oradata/orcl/system01.dbf';
SET NEWNAME FOR
DATAFILE 2 TO '/u01/app/oracle/oradata/orcl/sysaux01.dbf';
SET NEWNAME FOR
DATAFILE 3 TO '/u01/app/oracle/oradata/orcl/undotbs01.dbf';
SET NEWNAME FOR
DATAFILE 4 TO '/u01/app/oracle/oradata/orcl/users01.dbf';
SET NEWNAME FOR
DATAFILE 5 TO '/u01/app/oracle/oradata/orcl/example01.dbf';
SET NEWNAME FOR
DATAFILE 6 TO '/u01/app/oracle/oradata/orcl/test.dbf';
SET NEWNAME FOR
DATAFILE 7 TO '/u01/app/oracle/oradata/orcl/dbfs01.dbf';
SQL "ALTER
DATABASE RENAME FILE ''/u02/app/oracle/oradata/orcl/redo/redo01.log''
TO
''/u01/app/oracle/oradata/orcl/redo/redo01.log'' ";
SQL "ALTER
DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/red01_b.log''
TO
''/u02/app/oracle/oradata/orcl/redo/red01_b.log'' ";
SQL "ALTER
DATABASE RENAME FILE ''/u02/app/oracle/oradata/orcl/redo/redo02.log''
TO
''/u01/app/oracle/oradata/orcl/redo/redo02.log'' ";
SQL "ALTER
DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo02_b.log''
TO
''/u02/app/oracle/oradata/orcl/redo/redo02_b.log'' ";
SQL "ALTER
DATABASE RENAME FILE
''/u02/app/oracle/oradata/orcl/redo/redo03.log''
TO
''/u01/app/oracle/oradata/orcl/redo/redo03.log'' ";
SQL "ALTER
DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo03_b.log''
TO
''/u02/app/oracle/oradata/orcl/redo/redo03_b.log'' ";
SQL "ALTER
DATABASE RENAME FILE
''/u02/app/oracle/oradata/orcl/redo/redo04.log''
TO
''/u01/app/oracle/oradata/orcl/redo/redo04.log'' ";
SQL "ALTER
DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo04_b.log''
TO
''/u02/app/oracle/oradata/orcl/redo/redo04_b.log'' ";
SET UNTIL SCN 1275229;
RESTORE DATABASE;
SWITCH DATAFILE
ALL;
RECOVER DATABASE;
}
|
The script above was saved to the file restore.rman. Below is the
output.
|
RMAN> @restore.rman
RMAN>
RMAN> **end-of-file**
RMAN>
|
All that is left is to open the database with the RESETLOGS option.
|
RMAN> alter database open
resetlogs;
database opened
RMAN>
|