Chapter 27 – Incomplete Database Recovery

Complete Recovery

• Assuming backups of the datafiles, copies of all necessary archive logs, online redo logs and the controlfile are available; the db can suffer any damage to the datafiles without losing any data:
• Complete Recovery:
  • Take damaged datafiles offline
  • Restore damaged datafiles
  • Recover damaged datafiles
  • Bring recovered datafiles online
• Syntax:
  • alter tablespace users offline immediate; (immediate is used because the tablespace is damaged and the DBWn will try to write dirty blocks to disk [aka checkpoint the tablespace], which may fail)
  • restore tablespace users;
  • recover tablespace users delete archivelog; (delete archivelog refers to the deletion of the restored logs after they are applied. recovery may also use online log files.)
  • alter tablespace users online;
• Complete recovery can be done with the database OPEN unless the SYSTEM or UNDO tablespaces are damaged (this will terminate the instance).

When is Incomplete Recovery Necessary?

• Incomplete recovery means losing data on purpose. It occurs when complete recovery is impossible or you want to lose some information that was entered by mistake.
• It is not possible to skip the recovery of a bad transaction and recover all other work.
• There are cases where incomplete recovery won’t be possible with the current controlfile:
  • All copies of the controlfile are lost (and it is not possible to recover with a CREATE CONTROLFILE command)
  • The current controlfile does not accurately describe the database that needs to be restored (typically due to dropped tablespaces)
• The syntax for recovery using a backup controlfile includes the UNTIL keyword even if the recovery is complete.
• All datafiles except temporary tablespaces are restored. Undo tablespaces are restored.

The Method for Incomplete Recovery

• It is often a good idea to perform a complete offline backup before beginning an incomplete recovery to ensure no loss of data.
• Steps to incomplete recovery:
  • Mount the database
  • Restore all datafiles (they don’t need to be from same backupset / time), and controlfile if necessary
  • Recover the database UNTIL time, cancel or change number
  • OPEN with RESETLOGS
• OPEN with RESETLOGS initializes the online redo files creating a new incarnation of the database. An incarnation of a database has a new thread of redo, beginning at log switch sequence number 1.
• Backups and archivelogs are specific to an incarnation and therefore those generated by one incarnation must be kept separate from those generated by a previous incarnation.
• Only SYSDBA can perform incomplete recoveries

UNTIL TIME Recovery

• SQL> shutdown immediate;
• SQL> startup mount;
• —No restore b/c OS or other means are used to restore necessary files—
• SQL> recover database UNTIL TIME '2006-08-15:14:07:00'
  • The time format must be in the format YYYY-MM-DD:HH24:MM:SS irrespective of NLS_DATE_FORMAT
• SQL> alter database open resetlogs;
• —–ALTERNATIVE in RMAN—–
• RMAN> run {
• RMAN> shutdown immediate;
• RMAN> startup mount;
• RMAN> sql "alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''";
• RMAN> set until time '15-aug-2006 14:11:00';
• RMAN> restore database;
• RMAN> recover database;
• RMAN> alter database open resetlogs;}

UNTIL CANCEL / UNTIL SEQUENCE (RMAN) Recovery

• Cancel-based recovery is typically needed after complete recovery has failed: the recovery required an archive or online log that was missing.
• SQL> shutdown immediate;
• SQL> startup mount;
• —No restore b/c OS or other means are used to restore necessary files—
• SQL> recover database UNTIL CANCEL;
• SQL> alter database open resetlogs;
• —–ALTERNATIVE in RMAN—–
• RMAN> run {
• RMAN> shutdown immediate;
• RMAN> startup mount;
• RMAN> set until sequence 10305 thread 1;
• RMAN> restore database;
• RMAN> recover database;
• RMAN> alter database open resetlogs;}

UNTIL CHANGE / UNTIL SCN Recovery

• SQL> shutdown immediate;
• SQL> startup mount;
• —No restore b/c OS or other means are used to restore necessary files—
• SQL> recover database UNTIL CHANGE 309121;
• SQL> alter database open resetlogs;
• —–ALTERNATIVE in RMAN—–
• RMAN> run {
• RMAN> shutdown immediate;
• RMAN> startup mount;
• RMAN> set until scn 309121;
• RMAN> restore database;
• RMAN> recover database;
• RMAN> alter database open resetlogs;}

Incomplete Recovery Using EM

• Maintenance -> Perform Recovery

Recovery of the Controlfile

• SQL> alter database backup controlfile to trace
  • USER_DUMP_DEST will determine where trace file is created
• You should always create a trace file whenever you make physical changes to the structure of the database.
• SQL> alter database backup controlfile to ‘<filename>’

Restore a Controlfile with SQL*Plus

• startup mount;
• SQL> recover database until cancel using backup controlfile;

Backup / Restore a Controlfile with RMAN

• RMAN> backup as copy current controlfile;
• RMAN> backup as backupset current controlfile;
• RMAN> backup tablespace system include current controlfile;
• RMAN> configure controlfile autobackup on;
• RMAN> restore controlfile from autobackup;

Parameters

• USER_DUMP_DEST
• CONTROL_FILES

Advertisement