The RMAN Backup and Recovery  procedure for performing restore and recovery are as follows:

 

  • Determine which database files must be restored from backup, and which backups which specific tapes, or specific backup sets or image copies on disk) to use for the restore operation. The files to be restored may include the control file, SPFILE, Archived Redo log files, and datafiles.

 

  • Place the database in the state appropriate for the type of recovery that you are performing. For example, if you are recovering a single tablespace or datafile, then you can keep the database open and take the tablespace or datafile offline.  If you are restoring all datafiles, then you must shut down the database and then mount it before you can perform the restore.

 

  • Restore lost database files from backup with the RESTORE command. You may restore files to their original locations, or you may have to restore them to other locations if, for instace, a disk has failed. You may also have to update the SPFILE if you have changed the control file locations, or the control file if you have changed the locations of datafiles or redo logs.

 

  • Perform media recovery on restored datafiles, if any, with the RECOVER command.

 

  •  Perform any final steps required to make the database available for users again. For example, re-open the database if necessary, as happens when restoring lost control files, or bring offline tablespaces online if restoring and recovering individual tablespaces.

 

Recover Tablespace

Use this procedure if a tablespace file is damaged besides SYS or SYSAUX. The tablespace can be recovered with the database open. RMAN will restore the damaged file and recover transactions since list backup.If the database is open then the tablespace should be taken offline before starting recovery.

In the example below the file /u03/ORACLE/DBNAME/INDXDYNM01.dbf is damaged and impacting the INDXDYNM tablespace. The database is open so the tablespace will first be taken off line and then put back on line once recovery was complete. If the database was in a mount state the database will not open so the steps to take the tablespace off line and online can be omitted

 

RMAN> run

{

sql ‘alter tablespace indxdynm offline immediate’;

restore tablespace indxdynm;

recover tablespace indxdynm;

sql ‘alter tablespace indxdynm online’;

}

Recovering From Lost Control Files

Control files should be multiplexed and if a control file is lost it can be copied from another location or the lost control file’s location can be commented out of the pfile if necessart in order to restart the database. In the unlikely event that all control files are lost, the procedure below can be used to restore the database. When restoring control files in the manner, the database must be in mounted mode for the restore and then must be opened with the RESET LOGS option. A new backup of the entire database must then be taken.

When using the control file as an RMAN repository you must know the DBID of the database and the format and location of the backup control file. It is recommended that you keep this information on hand so you do not have to search for it.The DBID is used internally by RMAN to identify the database and is part of the backup control file name. The location of the DBID in the backup control file will be determined by autobackup control file configuration parameter.

[email protected]:/opt/dba $ rman target /

Recovery Manager: Release 10.2.0.3.0 – Production on Tue Dec 18 16:04:49 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: DBNAME (DBID=6617979543)

RMAN> show all;

 

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/dbdump/DBNAME/auto_control_%F’;

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/dbdump/DBNAME/snapcf_DPFNVD1.f’;

Steps are as follows

1)       If database is running, perform a shutdown abort with sqlplus then exit;

2)       ensure ORACLE_SID is set and connect to RMAN (rman target /)

3)       From RMAN prompt, start database in NOMOUNT mode.

4)       set the dbid

5)       set the autobackup control file format so that rman can find the control file backup

6)       restore control file autobackup

7)       start the database in MOUNT mode

8)       recover database

9)       open database with reset logs option

10)   take new backup of database immediately as previous backups are no longer valid;

 

Example

All control files are lost on database DBNAME. DBID is 6617979543.

Control File autobackup format is ‘/dbdump/DBNAME/auto_control_%F’

Commands would be as follows:

SET DBID 6617979543;

RUN {

startup nomount;

SET CONTROLFILE AUTOBACKUP FORMAT

FOR DEVICE TYPE DISK TO ‘/dbdump/DBNAME/auto_control_%F’;

RESTORE CONTROLFILE FROM AUTOBACKUP;

startup mount

restore database;

recover database;

alter database open resetlogs;

}

 

Database Point in Time Recovery

Use this procedure to recover from an unwanted change (i.e. a failed data load,etc., drop table operation) or to restore a database to a pre determined snapshot after testing. For some operations with 10G, it is possible to use the flashback table feature to recover from some errors. This should be explored before doing a database point in time recovery.

Steps:

1)       Determine the target time, SCN, restore point, or log sequence that should end the point in time recovery.  For example, you can query (g)v$_archived_log as below:

select inst_id,thread#,substr(name,1,40) as file_name,sequence#,first_change#,to_char(first_time, ‘DD-MON-RR HH24:MI:SS’) as first_time

from gv$archived_log

where trunc(first_time) = trunc(sysdate)

order by thread#,sequence#;

 

2)       From RMAN performan a shutdown immediate and then startup mount.

3)       Use the SET UNTIL command inside the run block to specify when to end the recover. This will save you from having to enter later. Possible alternatives are:

SET UNTIL SCN 1000;

SET UNTIL TIME ‘Nov 15 2013 09:00:00′;

SET UNTIL SEQUENCE 9923;

SET UNTIL RESTORE POINT before_update;

4)       Restore the database

5)       Recover the database

6)       Open the database with reset logs (you may open the database read only first to verify correct restore if desired.

Example:

To restore to log sequence 37 you would enter the following blocks in RMAN:

SHUTDOWN IMMEDIATE;

startup mount;

RUN

{

SET UNTIL SEQUENCE 37;

RESTORE DATABASE;

RECOVER DATABASE;

}

ALTER DATABASE OPEN RESETLOGS;