Database Recovery command

RECOVER Command:-

• Restore all database files from a backup and then recover the database:-

SQL> RECOVER DATABASE

• Restore the damaged data files from a backup and then recover the data files:-

SQL> RECOVER TABLESPACE index_tbs

SQL> RECOVER DATAFILE '/oradata/indx01.dbf'

Incomplete Recovery:-

• Recover a database until time:-

SQL> RECOVER DATABASE UNTIL

     TIME '2005-12-14:12:10:03';

• Recover a database until cancel:-

SQL> RECOVER DATABASE UNTIL CANCEL;

• Recover using the backup control file:-

SQL> RECOVER DATABASE

     UNTIL TIME '2005-12-14:12:10:03'

     USING BACKUP CONTROLFILE;

Time-Based Recovery:-

SQL> SHUTDOWN IMMEDIATE

$ cp /BACKUP/* /u01/db01/ORADATA

SQL> STARTUP MOUNT

SQL> RECOVER DATABASE UNTIL TIME '2005-11-28:11:44:00';

SQL> ALTER DATABASE OPEN RESETLOGS;

User-Managed Cancel-Based Recovery: Example

After searching through the directory for the redo log files, you notice that redo log log2a.rdo

cannot be located and has not been archived. Therefore, you cannot recover past this point.

Querying V$LOG_HISTORY confirms the absence of archived log sequence 48 (log2a.rdo):

SQL> SELECT * FROM v$log_history;

Time-Based Recovery

Using RMAN: -

RMAN> RUN {

2> SET UNTIL TIME = '2005-11-28:11:44:00';

3> RESTORE DATABASE;

4> RECOVER DATABASE;

5> ALTER DATABASE OPEN RESETLOGS; }

Log Sequence Recovery

Using RMAN:-

RMAN> RUN {

2> SET UNTIL SEQUENCE 120 THREAD 1;

3> ALTER DATABASE MOUNT;

4> RESTORE DATABASE;

5> RECOVER DATABASE; # recovers through log 119

6> ALTER DATABASE OPEN RESETLOGS;

7> }

Restore Points:-

SQL> CREATE RESTORE POINT before_load;

RMAN> RECOVER DATABASE UNTIL RESTORE POINT before_load;

Note: If a whole backup is not performed, at least archive the current redo log:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT

and back up the control file:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO

'/u01/data/backup.ctl';

Recovering a Control File Auto backup:-

RMAN> RESTORE CONTROLFILE TO

'/oradata/ctlfile.bak' FROM AUTOBACKUP;

RMAN> RECOVER DATABASE;

ALTER DATABASE OPEN

RESETLOGS;

ALTER TABLESPACE temp ADD

TEMPFILE...;