Next: Loose Ends Up: Using RMAN Previous: Advanced Backup Contents

Advanced Recovery

If you've spent any time with RMAN previous to reading this book you'll have noticed that it's not really built for complete disaster recovery. As a sysadmin, I'm concerned about what I do when the entire enviroment is in ruin and I can't depend on any other system being avalible. So, for the advanced recovery we're going to examine how you would recover a database is the only thing you have avalible is the backup pieces. In this case, I'm going to use my 2 backup peices from the advanced RMAN backup we just did to recover the database after destroying every trace of the database. Lets review an important point first. RMAN can be utilized using a recovery catalog. When used, this database is updated by RMAN with information pertaining to backup peices and RMAN metadata. One recovery catalog database can be utilized by multiple databases on diffrent systems, possibly a "small" installation of Oracle on your backup server. If we don't use a recovery catalog we're forced to put backup information in some other place... the controlfiles. Storing backup information inside the database controlfile is a real touchy subject. On one hand it makes perfect sense because your store information about all the other resources of your database in there anyway. On the other hand, its an insanely stupid idea because the controlfile is one of the files your backing up! Therefore, if you have to use a controlfile to store backup information you'll need to keep some things in mind. Particularlly, if you want to restore the database you'll need to recover the controlfiles first either from the RMAN backup peices (the hard way) or even possibly recover it from a file system backup of the system before recovering the backup peices made by Oracle. All this goes away if you have a recovery catalog because when you start a database restore RMAN can simply ask the recovery catalog for the peice containing the control file and restore it first. In the following example we will not be using a recovery catalog, and we are using an SPFILE instead of a regular PFILE, since SPFILEs are default in 10g. So, to start off we need to double check the location of the backup pieces, set the ORACLE_SID (even though there is no database, you must still have a SID) and use RMAN to start the instance for our recovery. Because no PFILE or SPFILE is present it will use the default system parameter file:

[oracle@vixen oracle]$ ls -l /export/rman/
total 908326
-rw-r--r--   1 oracle   oinstall    1465 Nov  8 17:31 rman.log
-rw-r-----   1 oracle   oinstall 461864960 Nov  8 17:31 rman_TESTINGx_20041108_3_1.bus
-rw-r-----   1 oracle   oinstall 2949120 Nov  8 17:31 rman_TESTINGx_20041108_4_1.bus
[oracle@vixen oracle]$ echo $ORACLE_SID
testing
[oracle@vixen oracle]$ rman nocatalog target /
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup force nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.1.0/db_1/dbs/inittesting.ora'
trying to start the Oracle instance without parameter files ...
Oracle instance started

Total System Global Area     167772160 bytes
Fixed Size                     1300832 bytes
Variable Size                115877536 bytes
Database Buffers              50331648 bytes
Redo Buffers                    262144 bytes

RMAN> quit
Recovery Manager complete.
[oracle@vixen oracle]$

Ok, the instance is started and we can now preform the first part of our recovery. If you recall from earlier discussions you need the PFILE or SPFILE in order to properly start and instance, and we need the database controlfile in order to access the RMAN backup information it contains because we're not using a recovery catalog. Since both the SPFILE and controlfile are inside the backup set we'll need to use the PL/SQL RMAN interface to specifically point RMAN in the right direction. Here is the PL/SQL you'll need to use (restore_foundation.sql):

DECLARE
v_dev varchar2(50);             -- device type allocated for restore
v_done boolean;                 -- has the controlfile been fully extracted yet
type t_fileTable is table of varchar2(255)
index by binary_integer;
v_fileTable t_fileTable;        -- Stores the backuppiece names
v_maxPieces number:=1;          -- Number of backuppieces in backupset

BEGIN
-- Initialise the filetable & number of backup pieces in the backupset
-- This section of code MUST be edited to reflect the customer's available
-- backupset before the procedure is compiled and run. In this example, the
-- backupset consists of 4 pieces:

v_fileTable(1):='/export/rman/rman_TESTINGx_20041108_4_1.bus';
v_fileTable(2):='/export/rman/rman_TESTINGx_20041108_3_1.bus';
v_maxPieces:=2;

-- Allocate a device. In this example, I have specified 'sbt_tape' as I am
-- reading backuppieces from the media manager. If the backuppiece is on disk,
-- specify type=>null

v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>null, ident=>'d1');

-- Begin the restore conversation
sys.dbms_backup_restore.restoreSetDatafile;

-- Specify where the controlfile is to be recreated
sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/u02/oradata/testing/control01.ctl');
sys.dbms_backup_restore.restorespfileto('/u02/oradata/testing/spfile');

-- Restore the controlfile
FOR i IN 1..v_maxPieces LOOP
        sys.dbms_backup_restore.restoreBackupPiece(done=>v_done, handle=>v_fileTable(i), params=>null);
        IF v_done THEN
                GOTO all_done;
        END IF;
END LOOP;

<<all_done>>
-- Deallocate the device
sys.dbms_backup_restore.deviceDeallocate;

END;
/

The parts of this code you'll need to edit are the array elements of the v_fileTable array including the number of v_maxPieces as the number of elements. Then the deviceAllocate() function tells RMAN we're using disk instead of tape. But the most important lines are the restoreControlfileTo() and restorespfileto() functions. The arguments supplied to both will indicate where RMAN should put the controlfile and SPFILE. Run this PL/SQL code by putting it in a file named restore_foundation.sql and execute it like this (it'll ask for a value, just enter 1, this doesn't mean anything):

[oracle@vixen RMAN]$ mkdir /u02/oradata/testing
[oracle@vixen RMAN]$ vi restore_foundation.sql
[oracle@vixen RMAN]$ sqlplus / as sysdba @restore_foundation
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Nov 9 15:19:30 2004
Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Enter value for number: 1
old  10: -- Initialise the filetable & number of backup pieces in the backupset
new  10: -- Initialise the filetable 1 of backup pieces in the backupset

PL/SQL procedure successfully completed.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@vixen RMAN]$ cd /u02/oradata/testing
[oracle@vixen testing]$ ls -l
total 5618
-rw-r-----   1 oracle   oinstall 2867200 Nov  9 15:19 control01.ctl
-rw-r--r--   1 oracle   oinstall     857 Nov  9 15:19 spfile
[oracle@vixen testing]$

Now we have the neccisary files to start an instance to restore from properly. cat the spfile to deterime what paths need to be created for proper startup, namely the dump directories. Once the directories are created you should duplicate the controlfile so that there are the typical 3 copies. And finally, you must create a password file for the instance. Moving the spfile into $ORACLE_HOME/dbs isn't neccisary, but a good idea.

[oracle@vixen testing]$ cat spfile     
*.background_dump_dest='/u01/app/oracle/product/10.1.0/db_1/admin/testing/bdump'
*.compatible='10.1.0.2.0'
...
[oracle@vixen testing]$ cp control01.ctl control02.ctl
[oracle@vixen testing]$ cp control01.ctl control03.ctl
[oracle@vixen testing]$ mkdir -p /u01/app/oracle/product/10.1.0/db_1/admin/testing/bdump
[oracle@vixen testing]$ mkdir -p /u01/app/oracle/product/10.1.0/db_1/admin/testing/cdump
[oracle@vixen testing]$ mkdir -p /u01/app/oracle/product/10.1.0/db_1/admin/testing/udump
[oracle@vixen testing]$ orapwd file=/u01/app/oracle/product/10.1.0/db_1/dbs/orapwtesting password=passwd entries=2
[oracle@vixen testing]$ cp spfile /u01/app/oracle/product/10.1.0/db_1/dbs/spfiletesting.ora

Now we've got the meat of our instance ready to be utilized for a real restoration of the datafiles. If the instance is currently started, shut it down (shutdown abort;) and restart the instance using the proper SPFILE. The database will be started in mount mode which will start the instance and read the controlfile(s) but not actually open the datafiles.

[oracle@vixen testing]$ rman nocatalog target /
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle.  All rights reserved.

connected to target database: DUMMY (not mounted)
using target database controlfile instead of recovery catalog

RMAN> startup force mount pfile='/u01/app/oracle/product/10.1.0/db_1/dbs/spfiletesting.ora'

Oracle instance started
database mounted

Total System Global Area     289406976 bytes
Fixed Size                     1301536 bytes
Variable Size                262677472 bytes
Database Buffers              25165824 bytes
Redo Buffers                    262144 bytes

RMAN>

If everything has gone well so far, you can now list the backups avalible and restore the database.

RMAN> list backup;

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    2M         DISK        00:00:01     08-NOV-04
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20041108T172608
        Piece Name: /u01/app/oracle/product/10.1.0/db_1/dbs/rman_TESTINGx_20041108_2_1.bus
  Controlfile Included: Ckp SCN: 387742       Ckp time: 08-NOV-04
  SPFILE Included: Modification time: 08-NOV-04

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    440M       DISK        00:01:31     08-NOV-04
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20041108T172932
        Piece Name: /export/rman/rman_TESTINGx_20041108_3_1.bus
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 388558     08-NOV-04 /u02/oradata/testing/system01.dbf
  2       Full 388558     08-NOV-04 /u02/oradata/testing/undotbs01.dbf
  3       Full 388558     08-NOV-04 /u02/oradata/testing/sysaux01.dbf
  4       Full 388558     08-NOV-04 /u02/oradata/testing/users01.dbf

RMAN>
RMAN> restore database;

Starting restore at 09-NOV-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/testing/system01.dbf
restoring datafile 00002 to /u02/oradata/testing/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/testing/sysaux01.dbf
restoring datafile 00004 to /u02/oradata/testing/users01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/export/rman/rman_TESTINGx_20041108_3_1.bus tag=TAG20041108T172932
channel ORA_DISK_1: restore complete
Finished restore at 09-NOV-04

RMAN>

At this point you'll need to attempt a recovery of the database. Normally a recovery is preformed by applying all the archivelogs against the instance, but since we don't have any archivelogs we'll get an error instead. Even though you'll get an error you must attempt it anyway, if you do not you'll be unable to open the database later.

RMAN> recover database;
Starting recover at 09-NOV-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

starting media recovery

unable to find archive log
archive log thread=1 sequence=5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/09/2004 15:39:29
RMAN-06054: media recovery requesting unknown log: thread 1 seq 5 lowscn 388558

RMAN>

With the restoration and recovery complete all the datafiles will be in the proper place. You can now shutdown the current instance and startup the database properly. Once the database is mounted you'll need to reset the logs to open the database.

RMAN> shutdown immediate;
database dismounted
Oracle instance shut down

RMAN> quit
Recovery Manager complete.
[oracle@vixen testing]$ echo $ORACLE_SID
testing
[oracle@vixen testing]$ sqlplus / as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Nov 9 15:31:45 2004
Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup pfile=/u01/app/oracle/product/10.1.0/db_1/dbs/spfiletesting.ora
ORACLE instance started.

Total System Global Area  289406976 bytes
Fixed Size                  1301536 bytes
Variable Size             262677472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
Database altered.

SQL> quit

And your done! You can test our your database by querring a couple tables and connecting as various diffrent users. If you don't want to be bugged with specifying the pfile during startup you'll just need to symlink the spfile to $ORACLE_HOME/dbs/init(SID).ora. After the database is back up, you'll want to ensure that you either restore from filesystem backups or recreate the listener configuration. Please note that the PL/SQL interface we used above is undocumented and Oracle will not assist you in using it. It also (supposedly) changes between releases. Unfortunetly, this is the only way. The only documentation that even mentions it is avalible only if you have a MetaLink account, in DocID 60545.1.

Next: Loose Ends Up: Using RMAN Previous: Advanced Backup Contents

2005-02-10