RMAN tutorial

Reference: http://www.cuddletech.com/articles/oracle/node57.html

Using RMAN

Why would a sysadmin like RMAN?

RMAN can perform online (hot) backups
RMAN can allow for partial or complete recovery
No fear of incomplete backups
DBA initiated backups and recovery without the interaction of the SA
Intigration with existing backup infistructure
The problem with backing up Oracle using traditional methods is similar to the problems with backing up filesystems, unless you shutdown the database and perform a cold backup there is no way to know that all the transactions and changes have been written to datafiles. The SGA maintans a huge amount of data in active memory which can cause a problem. It's alittle like editing a configuration file on the system and then halting the system and wondering where your changes went. In order to ensure consistance of the database we need a hot backup method. If we restore a filesystem backup of the database that was taken while Oracle was running we run the risk of lossing database changes at best or having a corrupt database at worst.

Enabling ARCHIVELOG Mode
Most of the High Availability features of Oracle require you to enable ARCHIVELOG mode for your database. When you enable this mode redo logs will be archived instead of overwritten. The archivelogs are stored in a seperate place usually can backed up regularly by your standard filesystem backup system (NetBackup or whatever). Archive logs are utilized by RMAN, Data Guard, Flashback and many others.
If your going to enable archivelog mode on a real database thats important to you, I would recommend shutting down the database and doing a cold backup just in case. Keeping a "final noarchivelog mode backup" seems to be a good and excepted practice.
Enabling archive mode is simple, just connect to your database in mounted but closed mode (startup mount) and alter the database. But if you don't tune alittle you'll run into problems down the road, so lets specify some parameters too. Namely, consider LOG_ARCHIVE_DEST.
Lets start by checking the current archive mode.
SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;
LOG_MODE
------------
NOARCHIVELOG
So we're in NOARCHIVELOG mode and we need to change. We can use a database alter statement, but that won't be perminant, so lets just update the pfile directly. The pfile should be in either $ORACLE_BASE/admin/SID/pfile or $ORACLE_HOME/admin/SID/pfile. I'll add the following lines to the end of the file:
############################
# Archive Log Destinations -benr(10/15/04)
############################
log_archive_dest_1='location=/u02/oradata/cuddle/archive'
log_archive_start=TRUE
Note that we're not actually required to specify the location of the log destination, but if you don't it'll end up in strange places (in my test it went to $ORACLE_HOME/dbs making a mess). You can specify as many as 10 diffrent archive log destinations by using the paramters log_archive_dest_1 through log_archive_dest_10. Remember, if you run out of space in your archive log destination the database will shut down!
Now we can startup the database in mount mode and put it in archivelog mode.
[oracle@vixen pfile]$sqlplus sys/passwd as sysdba;
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 15 16:00:58 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1300928 bytes
Variable Size 157820480 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
You can see here that we put the database in ARCHIVELOG mode by using the SQL statement "alter database archivelog", but Oracle won't let us do this unless the instance is mounted but not open. To make the change we shutdown the instance, and then startup the instance again but this time with the "mount" option which will mount the instance but not open it. Then we can enable ARCHIVELOG mode and open the database fully with the "alter database open" statement.
There are several system views that can provide us with information reguarding archives, such as:
V$DATABASE
Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.
V$ARCHIVED_LOG
Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.
V$ARCHIVE_DEST
Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES
Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG
Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.
V$LOG
Displays all redo log groups for the database and indicates which need to be archived.
V$LOG_HISTORY
Contains log history information such as which logs have been archived and the SCN range for each archived log.
Using these tables we can verify that we are infact in ARCHIVELOG mode:
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> select DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST;

Basic RMAN Backup
Lets do a real simple backup using RMAN that writes it's output to a local file instead of the tape subsystem just to see how it works. In this case, we've got our database (SID: cuddle) up and running.
[oracle@vixen oracle]$ echo $ORACLE_SID
cuddle
[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: CUDDLE (DBID=251015092)
using target database controlfile instead of recovery catalog
RMAN> backup database;
Starting backup at 02-NOV-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=162 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/cuddle/system01.dbf
input datafile fno=00003 name=/u02/oradata/cuddle/sysaux01.dbf
input datafile fno=00002 name=/u02/oradata/cuddle/undotbs01.dbf
input datafile fno=00004 name=/u02/oradata/cuddle/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-NOV-04
channel ORA_DISK_1: finished piece 1 at 02-NOV-04
piece handle=/u01/app/oracle/product/10.1.0/db_1/dbs/05g438u6_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 02-NOV-04
channel ORA_DISK_1: finished piece 1 at 02-NOV-04
piece handle=/u01/app/oracle/product/10.1.0/db_1/dbs/06g4391f_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 02-NOV-04
RMAN> quit
Recovery Manager complete.
[oracle@vixen oracle]$
This is the most basic backup you can do with RMAN. We didn't tell RMAN how or where to backup the database, just simply to do it.
The rman command is passed 2 arguments: the first "nocatalog" tells RMAN that we aren't using a recovery catalog database and the second "target /" is similar to a SQL*Plus connect statement, with information that RMAN requires to connect to the target database. The target is the database we wish to backup.
Notice that RMAN returns some interesting information prior to giving us a prompt. It confirms that RMAN is connected to the target and lists that target. The DBID seen after the target database SID can be very important for later recoveries and it is recommend that you write it down somewhere for future use. RMAN then confirms that because we aren't using a recovery catalog to store backup metadata that it will instead store the data in the target databases control files.
The RMAN command backup database; sends RMAN on its merry way backing up the database. Notice that we didn't tell it where or how to backup the data. By default the backup peices will be placed in the $ORACLE_HOME/dbs directory. This can get very messy since your system PFILES are in there too, and therefore we recommend that you don't use this location for your normal backups.
Two backup peices were created. The first contains the datafiles holding the tablespaces including the undo tablespace. The second backup peice contains the current SPFILE and curent controlfile.
Lets stop and think carefully for just a moment. Now, we've opted to use ARCHIVELOG mode which means we can do hot backups. However, we didn't want the hassle and administrative overhead of maintaining a recovery catalog. So here is the rub: recall that you need a PFILE/SPFILE to start an instance and you need the controlfile to point to all the files to be mounted. If the database were completely destroyed we would certainly need both the SPFILE and the Controlfile to access the backup peices made by RMAN.... but they are inside the backup we just made! Nice little loop of confusion huh? We'll talk about this later, but for now just keep it in mind.

Basic Recovery
To see how RMAN can be useful for recovery, lets take a database and damage it. Lets simulate a tablespace being deleted because of a bad script or stupid DBA and then try to recover the database.
[oracle@vixen oracle]$ mv /u02/oradata/cuddle/users01.dbf /u02/oradata/cuddle/users01.dbf.oops
Okey, there is our disaster. Lets connect to RMAN and look for suitable backups to recover.
[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: CUDDLE (DBID=251015092)
using target database controlfile instead of recovery catalog
RMAN> list backup;
List of Backup Sets
===================
.........
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 528M DISK 00:01:43 02-NOV-04
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20041102T134437
Piece Name: /u01/app/oracle/product/10.1.0/db_1/dbs/05g438u6_1_1
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1267667 02-NOV-04 /u02/oradata/cuddle/system01.dbf
2 Full 1267667 02-NOV-04 /u02/oradata/cuddle/undotbs01.dbf
3 Full 1267667 02-NOV-04 /u02/oradata/cuddle/sysaux01.dbf
4 Full 1267667 02-NOV-04 /u02/oradata/cuddle/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 2M DISK 00:00:03 02-NOV-04
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20041102T134437
Piece Name: /u01/app/oracle/product/10.1.0/db_1/dbs/06g4391f_1_1
Controlfile Included: Ckp SCN: 1267704 Ckp time: 02-NOV-04
SPFILE Included: Modification time: 15-OCT-04
RMAN>
We can see that we have good and current backups of this database avalible. Lets now try to recover in the basic way.
MAN> restore datafile '/u02/oradata/cuddle/users01.dbf';
Starting restore at 02-NOV-04
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u02/oradata/cuddle/users01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/product/10.1.0/db_1/dbs/05g438u6_1_1 tag=TAG20041102T134437
channel ORA_DISK_1: restore complete
Finished restore at 02-NOV-04
RMAN> recover datafile '/u02/oradata/cuddle/users01.dbf';
Starting recover at 02-NOV-04
using channel ORA_DISK_1
starting media recovery
media recovery complete
Finished recover at 02-NOV-04
RMAN>
Here, because the controlfiles and spfile are in tact, we can simply tell RMAN to restore the missing datafile, specifying which datafile by it's fully qualified path (which you can also see in your "list backup;").
Once the datafile is restored, we can recover it to ensure it's consistant.
Once your done, you'll either want to bring the datafile and tablespaces online using alter statements, or at the very least use SQL*Plus to verify that the tablespaces are online by looking at the Oracle data dictionary.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
SQL> select FILE#,STATUS,ENABLED,NAME from v$datafile;
FILE# STATUS ENABLED
---------- ------- ----------
NAME
---------------------------------
1 SYSTEM READ WRITE
/u02/oradata/cuddle/system01.dbf
2 ONLINE READ WRITE
/u02/oradata/cuddle/undotbs01.dbf
3 ONLINE READ WRITE
/u02/oradata/cuddle/sysaux01.dbf
4 OFFLINE READ WRITE
/u02/oradata/cuddle/users01.dbf
In this above case the tablespace is online but we find the datafile is offline. Lets just fix that up by using an alter statement:
SQL> alter database datafile '/u02/oradata/cuddle/users01.dbf' online;
Database altered.
SQL> alter tablespace USERS online;
Tablespace altered.
We didn't need to alter the tablespace because it was already online, but I did it any way to demonstrate. Once you've successfully altered the database to bring both the datafile and the tablespaces online you'll want to run the queries above again to double check.

Listing Backups
Lets spend just a minute looking alittle more at the history of backups using the "list" RMAN command. Using the "list backup" RMAN statement we can list the backups we've made.
[oracle@vixen oracle]$echo $ORACLE_SID
cuddle
[oracle@vixen oracle]$rman nocatalog
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: CUDDLE (DBID=251015092)
using target database controlfile instead of recovery catalog
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 441M DISK 00:01:23 15-OCT-04
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20041015T175207
Piece Name: /export/rman/rman_CUDDLE_01g2k8m8_1_1.bus
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 396472 15-OCT-04 /u02/oradata/cuddle/system01.dbf
2 Full 396472 15-OCT-04 /u02/oradata/cuddle/undotbs01.dbf
3 Full 396472 15-OCT-04 /u02/oradata/cuddle/sysaux01.dbf
4 Full 396472 15-OCT-04 /u02/oradata/cuddle/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 2M DISK 00:00:02 15-OCT-04
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20041015T175207
Piece Name: /export/rman/rman_CUDDLE_02g2k8ou_1_1.bus
Controlfile Included: Ckp SCN: 396502 Ckp time: 15-OCT-04
SPFILE Included: Modification time: 15-OCT-04
RMAN>
Here we can see 2 backup pieces, the first contains the datafiles for the "cuddle" database, is 441MB in side, was made to disk and took 1 minute and 23 seconds to make. We aslo see the peices tag (notice the tag is the same for both peices). Notice also that each datafile in the backup peice has a Checkpoint System Change Number (Ckp SCN) associated with it (SCNs were covered earlier). The second peice is 2MB in size, took 2 seconds to backup and was done to disk. Notice that the second peice lists the modification time for the SPFILE and the SCN for the Controlfile.
The list command has a number of argument that can allow you to tailor the output to just about any way you want to see it. A nice and consice output is seen using "list backup summary".
See a complete list of options to the RMAN list command in the Oracle Database Recovery Manager Reference manual:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10770/toc.htm

Advanced Backup
The method we used for a basic RMAN backup shows how to use RMAN but it doesn't provide an efficient way to automate the proccess using traditional tools like the cron. Using alittle RMAN scripting the proccess can easily be controlled from a script and run from cron or any other schedualling method you'd like.
The foundation of this method of using RMAN is built on the run block. Within the block are a list of RMAN commands to be sequentually run. When the block is handled by RMAN it will first verify that each of the input lines in the block are valid and proper, it will then execute each statement line by line sequentually. This is as close a method as possible to ensure the operation is atomic.
Here is a simple RMAN run block (the backup_full.rman we'll use in a minute):
run {
allocate channel d1 type disk;
backup full database format '/export/rman/rman_%n_%T_%s_%p.bus';
}
In this run block we're simply allocating a disk channel and preforming a backup to the /export/rman directory using a specific naming convension for the output backup set.
This run block can be run directly from the RMAN prompt by entering it line by line or calling the script. However, the more appropriate way to execute it is from a standard command line where it can be wrapped in a script and/or controlled from cron.
[oracle@vixen RMAN]$ rman nocatalog target / \
> cmdfile='backup_full.rman' log='/export/rman/rman.log'
RMAN> 2> 3> 4> 5>
[oracle@vixen RMAN]$
Here the rman executable is called as it would normally be, but we supply the location of an RMAN script to run with the cmdfile argument and a place to output the logging information with the log argument.
When RMAN is executing it will output the RMAN prompts but nothing else. This can be useful for debugging, but should probly be redirected for cleanliness when used from a script or cron.
Comments can be put in RMAN scripts using a hash (#).
More details on command line options can synatex for run blocks can be found in the Oracle Database Recovery Manager Reference:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10770/

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;
<>
-- 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.