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.