RMAN Recover Quick Reference

Determine which Database files to Restore and Recover

--Find the status of the database
select instance_name, status
from v$instance;

--Check the status of the datafiles
col file# format 999
col status for a7
col error for a10
col tablespace_name for a10
col name for a30

select file#, status, error, recover,
tablespace_name,name
from v$datafile_header
where recover = 'YES' or (recover is null
and error is not null);

col df# for 999
col df_name for a35
col tbsp_name for a7
col status for a7
col error for a10
col change# for 9999999

select r.file# df#, d.name df_name, t.name tbsp_name
,d.status, r.error, r.change#, r.time
from v$recover_file r, v$datafile d, v$tablespace t
where t.ts# = d.ts#
and d.file# = r.file#

crazy:



Determine your DBID:

If you need to recover your SPFILE or controlfile
you will need your DBID. YOU SHOULD KEEP A COPY OF YOUR DBID
it can save you hassel later.

You might be able to find it:
1. the filename for controlfile autobackup contains it
2. any log files from backups.


Restore Preview

We can use this command to preview what backup pieces RMAN is going to use to perform the backup.
Use full if we want to over ride what RMAN is going to do.

RMAN> restore preview database;

Restore and Recover a whole database

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

Restore and Recovery of tablespace / datafile:

Here we need to recover a tablespace or its datafiles.

We need to offline the tablespace or datafile first

SQL> alter tablespace users offline immediate;
--or--
SQL> alter database datafile
'+DATA/prod/datafile/users.259.665279427'
offline;

Now we can restore and recover

RMAN> restore datafile
'+DATA/prod/datafile/users.259.665279427';
--or--
RMAN> restore tablespace users;

RMAN> recover tablespace users;
--or--
RMAN> recover datafile
'+DATA/prod/datafile/users.259.665282993';

SQL> alter tablespace users online;

Restore and Recovery from loss of all controlfiles

If we are not using a Recovery Catalog, RMAN stores backup information in the current control file.
If we lose our controlfiles we will need to recover the controlfiles from autobackup.

This means RMAN will not have current information on the backups and the controlfile that is restored will have scn's from the past.

RMAN will not know the DBID of the database that needs to resotred. So we need to manually set this:

RMAN> set dbid 65705449

All the 'configure' parameters will be back to defualt as any changes you made will be stored in the lost controlfiles. We need to tell RMAN where to look for the controlfile autobackkups.

set controlfile autobackup format
for device type disk to
'/u01/app/backup/%F';

Now we can run the restore of the controlfiles:

RMAN> restore controlfile from autobackup;

As the SCN for each datafile header will be higher in the datafile than the restored controlfiles, We need to perform a recovery to make this insync.

A restore of controlfiles means we must also reset the logs

RMAN> recover database;

RMAN> alter database open resetlogs;

Restore and Recovery from loss of spfile

If the database is closed at the time we need to set the DBID (see top).

RMAN> connect target

connected to target database: PROD (DBID=65705449)

RMAN> startup force nomount;

startup failed: ORA-01078:
failure in processing system parameters
ORA-01565: error in identifying file
'+DATA/prod/spfileprod.ora'

starting Oracle instance without
parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 159019008 bytes

Fixed Size 1298584 bytes
Variable Size 67112808 bytes
Database Buffers 83886080 bytes
Redo Buffers 6721536 bytes

Because there is no spfile to start the instance RMAN will use a dummy file.
Now we need to tell rman where the autobackup is located and restore the file

RMAN> set controlfile autobackup
format for device type disk to
'/u01/app/backup/%F';

RMAN> restore spfile from autobackup;
--or--
RMAN> restore spfile from autobackup maxdays 30;

By defualt rman will only search back for 7 days, by using 'maxdays' we can tell RMAN to search back further.



******************************************
keywords: restore recover
******************************************
rdbms version: 11g
******************************************