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 ‘’
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
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 ‘
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
Comentários