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.