Oracle 11g: How to restore a backup

Commands used by RMAN are :

    - RESTORE Used to retrieve a set of files from a backup.

RESTORE {DATABASE | TABLESPACE name [,name] | DATAFILE name [,name]}

You can restore the database to a specific SCN, time, restore point or redo log sequence number.

    - RECOVER Applies modifications registered inside incremental backups, archived log and redo logs to all the files previously restored.

RECOVER {DATABASE | TABLESPACE name [,name] | DATAFILE name [,name]}

How to restore/recover an entire database

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;

note: In case of you lost your online redo logs, you can indicate to RMAN that during the recovery procress you do not want to apply redo logs :

RMAN> RECOVER DATABASE NOREDO;

How to restore/recover a tablespace
If the tablespace is not a system tablespace then the database could be opened during the restoration otherwise the database must be in MOUNT.

RMAN> sql 'alter tablespace users offline immediate';
RMAN> RESTORE TABLESPACE USERS;
RMAN> RECOVER TABLESPACE USERS;
RMAN> sql 'alter tablespace users online';

How to restore/recover a datafile
If the datafile is not a datafile of a system tablespace then the database could be opened during the restoration otherwise the database must be in MOUNT.
Anyway the datafile must be set offline before the restoration.

RMAN> sql 'alter database datafile 4 offline immediate';
RMAN> RESTORE DATAFILE 4;
RMAN> RECOVER DATAFILE 4;
RMAN> sql 'alter database datafile 4 online';

How to restore/recover a database until the last successful transaction

RMAN> RESTORE DATABASE UNTIL CANCEL;
RMAN> RECOVER DATABASE UNTIL CANCEL;

How to restore/recover a database at a specific point in time
The following example restore a database as it should be 10 minutes before :

RMAN> RECOVER DATABASE UNTIL TIME 'sysdate-10/60/24';

If you use a specific time, it is then necessary to define the NLS_LANG & NLS_DATE_FORMAT system parameters. These parameters will be used by rman to decode the date ans time passed for the restoration.

$> export NLS_LANG = american_america.us7ascii
$> export NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS'
$> RMAN target / catalog rcatowner/rcatowner@rcat_db
RMAN> RESTORE DATABASE UNTIL TIME '18/11/2011 10:40:00';
RMAN> RECOVER DATABASE UNTIL TIME '18/11/2011 10:40:00';
RMAN> ALTER DATABASE OPEN RESETLOGS;

or using the SET command :

$> export NLS_LANG = american_america.us7ascii
$> export NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS'
$> RMAN target / catalog rcatowner/rcatowner@rcat_db
RMAN> run {
SET UNTIL TIME '18/11/2011 10:40:00';
RESTORE DATABASE;
RECOVER DATABASE;
}
RMAN> ALTER DATABASE OPEN RESETLOGS;

remark : If you want to check that the point in time is correct by checking all the data restore, you need to open the database in READ ONLY. By this way, the redo logs will not be reseted and you can move your point in time if not relevant.

RMAN> ALTER DATABASE OPEN READ ONLY;

Once the point in time is correct you can open the database and reset the redo logs.

RMAN> ALTER DATABASE OPEN RESETLOGS;

This remark could also be applied for restoration at a specific SCN or sequence number.

How to restore/recover a database at a specific SCN

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> run {
SET UNTIL SCN #;
RESTORE DATABASE;
RECOVER DATABASE;
}
RMAN> ALTER DATABASE OPEN RESETLOGS;

remark :"SET UNTIL" DOESN'T WORK WITH A TABLESPACE!. It is not possible to restore only one tablespace at a specific time in the past. The database will not open.

remark :If the SCN you need to reach is not part of the current incarnation of the database, you need to reset the database to the incarnation juste before your scn and after perform the recovery. Here is the example of the restoration of the database at the SCN 5726167:

RMAN> run {
2> set until scn 5726167;
3> restore database;
4> recover database;
5> }

executing command: SET until clause

Starting restore at 18-11-11 13:28:10
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/18/2011 13:28:10
RMAN-20208: UNTIL CHANGE is before RESETLOGS change

RMAN> list incarnation;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TEST     2058702964       PARENT  1          13-08-09 23:00:48
2       2       TEST     2058702964       PARENT  754488     30-08-11 17:42:15
3       3       TEST     2058702964       PARENT  5571932    16-11-11 14:39:08
4       4       TEST     2058702964       ORPHAN  5576815    16-11-11 16:11:53
5       5       TEST     2058702964       ORPHAN  5577397    16-11-11 16:18:46
6       6       TEST     2058702964       ORPHAN  5598121    16-11-11 16:37:33
7       7       TEST     2058702964       PARENT  5576413    16-11-11 18:02:08
8       8       TEST     2058702964       CURRENT 5726169    18-11-11 11:41:56

RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> reset database to incarnation 7;
database reset to incarnation 7

RMAN> run {
2> set until scn 5726167;
3> restore database;
4> recover database;
5> }

RMAN> ALTER DATABASE OPEN RESETLOGS;

note : The RESET DATABASE need to alter the control file, so the database must be in mount state but not open. In case of an opend database you will receive the following exception : ORA-19910: can not change recovery target incarnation in control file

How to restore/recover a database at a specific sequence number

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> run {
SET UNTIL SEQUENCE #;
RESTORE DATABASE;
RECOVER DATABASE;
}
RMAN> ALTER DATABASE OPEN RESETLOGS;

How to restore a control file
To restore a control file the database must be in NOMOUNT state:

RMAN> STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE;
RMAN> ALTER DATABASE MOUNT;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

If you want to restore from an autobackup and you have an rman catalog :

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

If you don't have a catalog, you need to define the DBID:

RMAN> SET DBID 1090778584;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

How to restore a parameter file
To restore a parameter file, the database must be in NOMOUNT state:

RMAN> STARTUP FORCE NOMOUNT;
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
RMAN> STARTUP FORCE;

You can change the name of the parameter file :

RMAN> RESTORE SPFILE TO <parameter_file_name> FROM AUTOBACKUP;

Using sql, it is possible to generate the parameter file using

SQL> CREATE PFILE [='pfile_name']
FROM { { SPFILE [='spfile_name']} | MEMORY};

or

SQL> CREATE SFILE [='sfile_name']
FROM { { PFILE [='pfile_name']} | MEMORY};

How to restore/recover a database at a specific incarnation

RMAN> LIST INCARNATION;
RMAN> RESET DATABASE TO INCARNATION #;