1. Complete Closed Database Recovery.

- It is assumed that your control files are still accessible.
- You have a backup, done for example with backup database plus archivelog;

i) Your first step is to make sure that the target database is shut down:
$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;
ORACLE instance shut down.

ii) Next, you need to start up your target database in mount mode.

- RMAN cannot restore datafiles unless the database is at least in mount mode, because RMAN needs to be able to access the control file to determine which backup sets are necessary to recover the database.

- If the control file isn't available, you have to recover it first. Issue the STARTUP MOUNT command shown in the following example to mount the database:

SQL> startup mount;
Oracle instance started.
Database mounted.

iii) Use RMAN to restore the database and recover the database.
To use RMAN, connect to the target database:
$ rman target / catalog rman/rman@rmancat

- When the restore command is executed, RMAN will automatically go to its last good backup set and restore the datafiles to the state they were in when that backup set was created.

- When restoring database files, RMAN reads the datafile header and makes the determination as to whether the file needs to be restored. The recovery is done by allocating a channel for I/O and then issuing the RMAN restore database command.

you don't need to allocate a channel explicitly. Instead, you can use the default channel mode:

RMAN> restore database;
RMAN> recover database;
SQL> alter database open;


2. System tablespace is missing

In this case complete recovery is performed, only the system tablespace is missing, so the database can be opened without resetlogs option.

$ rman target /
RMAN> startup mount;
RMAN> restore tablespace SYSTEM;
RMAN> recover tablespace SYSTEM;
RMAN> alter database open;




3. Complete Open Database Recovery. Non system tablespace is missing, database is up

$ rman target /
RMAN> sql ‘alter tablespace offline immediate’ ;
RMAN> restore tablespace ;
RMAN> recover tablespace ;
RMAN> sql ‘alter tablespace online’ ;

To restore/recover only datafile(s)
$ rman target /

RMAN>. sql 'alter database datafile offline';
RMAN> restore datafile ;
RMAN> recover datafile ;
RMAN> sql 'alter database datafile online' ;

Note: datafile_name(within single quotes) can also be used instead of file#




4.Complete Open Database Recovery (when the database is initially closed). Non system tablespace is missing

A user datafile is reported missing when trying to startup the database. The datafile can be turned offline and the database started up. Restore and recovery are performed using Rman. After recovery is performed the datafile can be turned online again.

sqlplus “/ as sysdba “
startup mount
alter database datafile offline;
alter database open;
exit;

$rman target /
RMAN> restore datafile ;
RMAN> recover datafile ;
RMAN> sql 'alter tablespace online';

Note: datafile_name(within single quotes) can also be used instead of file#




5.To restore a tablespace to a new location


$ rman target / catalog rman/rman@rcat

Take the tablespace offline.
Specify an accessible location to which you can restore the damaged datafile for the offline tablespace.

Restore the datafile to the new location.
Switch the restored datafile so that the control file considers it the current datafile.
To restore the datafiles for tablespace USERS to a new location on disk:

run {
allocate channel ch1 type disk;
sql 'ALTER TABLESPACE USERS OFFLINE IMMEDIATE';
set newname for datafile '/disk1/oracle/users_1.dbf' to '/disk2/oracle/users_1.dbf';
restore tablespace users;
# make the control file recognize the restored file as current
switch datafile all;
}

RMAN> recover tablespace USERS;
RMAN> sql 'alter tablespace USERS online';



6. Recovery of a Datafile that has no backups (database is up)

If a non system datafile that was not backed up since the last backup, is missing, recovery can be performed if all archived logs since the creation of the missing datafile exist. Since the database is up you can check the tablespace name and put it offline. The option offline immediate is used to avoid that the update of the datafile header.
Pre requisites: All relevant archived logs.

$ rman target /
RMAN> sql ‘alter database datafile offline’;
RMAN> restore datafile ;
-- no need to create a blank file, restore command takes care of that.
RMAN> recover datafile ;
RMAN> sql 'alter database datafile online';

Note: datafile_name(within single quotes) can also be used instead of file#




7. Control File Recovery

Case-1 – Autobackup is available

Always multiplex your controlfiles. If you loose only one controlfile you can replace it with the one you have in place, and startup the Database. If both controlfiles are missing, the database will crash.
Pre requisites: A backup of your controlfile and all relevant archived logs. When using Rman alway set configuration parameter autobackup of controlfile to ON.

rman target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> recover database;
RMAN> alter database open resetlogs;

Make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

Case-2 – Autobackup is not available but controlfile backupset is available

rman target /
RMAN> startup nomount;
RMAN> restore controlfile from ;
RMAN> alter database mount;
RMAN> restore database; --required if datafile(s) have been added after the backup
RMAN> recover database;
RMAN> alter database open resetlogs;

Make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

Case-3 – If no backup is available, create the controlfile manually using script and then recover as given above.

Note: RMAN automatically searches in specific locations for online and archived redo logs during recovery that are not recorded in the RMAN repository, and catalogs any that it finds. RMAN attempts to find a valid archived log in any of the current archiving destinations with the current log format. The current format is specified in the initialization parameter file used to start the instance (or all instances in a Real Application Clusters installation). Similarly, RMAN attempts to find the online redo logs by using the filenames as specified in the control file.




8. Incomplete Recovery, Until time/sequence/scn

Incomplete recovery may be necessary when the database crashes and needs to be recovered, and in the recovery process you find that an archived log is missing. In this case recovery can only be made until the sequence before the one that is missing. Another scenario for incomplete recovery occurs when an important object was dropped or incorrect data was committed on it. In this case recovery needs to be performed until before the object was dropped.
Pre requisites: A full closed or open database backup and archived logs, the time or sequence that the 'until' recovery needs to be performed.

If the database is open, shutdown it to perform full restore.

shutdown abort
startup nomount
=============================
$ rman target / rcvcat rman/rman@rcat
RMAN> run {
set until time "to_date('2012/01/23 16:00:00',
'YYYY/MM/DD HH14:MI:SS')";
allocate channel d1 type disk;
restore controlfile to '/tmp/cf';
replicate controlfile from '/tmp/cf';
sql 'alter database mount';
restore database;
recover database;
}

Make a new complete backup, as the database is opened in new incarnation and previous archive logs are not relevant.




9. Recovering After the Loss of All Members of an Online Redo Log Group

If a media failure damages all members of an online redo log group, then different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database.

If the damaged log group is inactive, then it is not needed for crash recovery; if it is active, then it is needed for crash recovery.
SQL> startup mount

Case-1 If the group is INACTIVE
Then it is not needed for crash recovery
Clear the archived or unarchived group. (For archive status, check in v$log)

1.1 Clearing Inactive, Archived Redo

alter database clear logfile group 1 ;
alter database open ;

1.2 Clearing Inactive, Not-Yet-Archived Redo

alter database clear unarchived logfile group 1 ;
OR
(If there is an offline datafile that requires the cleared log to bring it online, then the keywords UNRECOVERABLE DATAFILE are required. The datafile and its entire tablespace have to be dropped because the redo necessary to bring it online is being cleared, and there is no copy of it. )

alter database clear unarchived logfile group 1 unrecoverable datafile;

Take a complete backup of database.

And now open database:
alter database open ;

Case-2 If the group is ACTIVE

Restore backup and perform an incomplete recovery.
And open database using resetlogs
alter database open resetlogs;

Case-3 If the group is CURRENT

Restore backup and perform an incomplete recovery.
And open database using resetlogs
alter database open resetlogs;




10. Restoring database to new host from RMAN backup

1) Restore database backup and archive log backup(if hot) to target server.
2) Copy ORACLE_HOME from source to target if its not already there.
3) If you dont have a controlfile backup which was taken after the cold backup then take a control file backup on source.

RMAN> backup current controlfile to '';
or
SQL> alter database backup controlfile to '';

4) Copy this controlfile backup to target node

5) On target:
Create pfile or copy it from target and change following parameters:

IFILE
*_DUMP_DEST
LOG_ARCHIVE_DEST
CONTROL_FILES

$ export NLS_DATE_FORMAT=”yyyy-mm-dd hh24:mi”
$ rman target /
RMAN> sql ‘create spfile from pfile’ ;
RMAN> startup nomount ;
RMAN> restore controlfile from '/filename.ctl>' ;
RMAN> alter database mount ;
RMAN> list backup ; - Note the scn number or time of the backup you want to restore

$ rman target /
RMAN> restore database until time ‘’ ;
OR
RMAN> restore database until scn ;
OR
RMAN> restore database from tag ‘’ ;

And now…
RMAN> recover database;
RMAN> alter database open resetlogs ;

Note: Above method can also be used where you want to restore database from old backups instead of latest one.



11. Restoring backups from tape.

Use the following steps to restore backups from tape.

$ export NLS_DATE_FORMAT=”yyyy-mm-dd hh24:mi”

RMAN> list backup ; -- Note the scn or time of the backup you want to restore.

RMAN> run{
allocate channel t1 type 'SBT_TAPE' parms="ENV=(NB_ORA_CLIENT=ibm5003bk)";
restore database until scn ; --scn number as in list backup output
recover database ;
}

Notes:
1) until scn can be used with recover command as well for incomplete recovery.
Other option is to use set until within run block just before restore.
2) from tag ‘’ can also be used (instead of until clause) with restore command