Explore Oracle Incomplete recovery-backup-based control file recovery

Recovery Based on backup control file (unsing backup controlfile)

Applicable:Recovery Based on backup control files is applicable to the following situations: the tablespace is accidentally deleted; all control files are damaged.

 

1. Close the database and perform a full-database cold backup:

SQL> select file_name from dba_data_files;

 

FILE_NAME

--------------------------------------------------------------------------------

/DBBak2/oradata/WWL/users01.dbf

/DBBak2/oradata/WWL/sysaux01.dbf

/DBBak2/oradata/WWL/undotbs01.dbf

/DBBak2/oradata/WWL/system01.dbf

/DBBak2/oradata/WWL/WWL001.dbf

/DBBak2/oradata/WWL/WWL002.dbf

/DBBak2/oradata/WWL/WWL003.dbf

 

7 rowsselected.

 

SQL> shutdown immediate

Databaseclosed.

Databasedismounted.

Oracleinstance shut down.

 

SQL>! Mkdir/DBBak2/oradata/WWL2

 

SQL>! Cp/DBBak2/oradata/WWL/*/DBBak2/oradata/WWL2/

 

Through the above operations, the database now has a cold backup, which contains anotherWwlTablespace, now the simulated user accidentally deletedWwlTablespace.

 

SQL> startup

ORACLEinstance started.

TotalSystem Global Area 100663296 bytes

FixedSize 1217884 bytes

VariableSize 88083108 bytes

DatabaseBuffers 8388608 bytes

RedoBuffers 2973696 bytes

Databasemounted.

Databaseopened.

SQL>

 

Simulate tablespace Deletion

SQL> drop tablespace wwl including contents;

Tablespacedropped.

 

The current control file does not containWwlThe tablespace is used, but the control file of the cold backup is still included. Therefore, the previous control file must be usedWwlRestore a tablespace.

 

Restore the data files backed up before:

SQL> shutdown immediate

Databaseclosed.

Databasedismounted.

ORACLEinstance shut down.

 

 

SQL>! Rm-rf/DBBak2/oradata/WWL/*. dbf

 

SQL>! Rm-rf/DBBak2/oradata/WWL/*. ctl

 

SQL>! Cp/DBBak2/oradata/WWL2/*. dbf/DBBak2/oradata/WWL/

 

SQL>! Cp/DBBak2/oradata/WWL2/*. ctl/DBBak2/oradata/WWL/

 

SQL>

 

Use the control file for recovery:

SQL> startup

ORACLEinstance started.

 

TotalSystem Global Area 100663296 bytes

FixedSize 1217884 bytes

VariableSize 88083108 bytes

DatabaseBuffers 8388608 bytes

RedoBuffers 2973696 bytes

Databasemounted.

ORA-00338: log 5 of thread 1 is more recent than control file

ORA-00312: online log 5 thread 1: '/DBBak2/oradata/WWL/redo5a. Log'

ORA-00312: online log 5 thread 1: '/DBBak2/oradata/WWL/redo5b. Log'

 

 

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 1782560 generated at 07/31/2012 14:37:24 needed for thread 1

ORA-00289: suggestion:/DBSoft/product/10.2.0/db_1/dbs/arch/1_3_790085314.dbf

ORA-00280: change 1782560 for thread 1 is in sequence #3

 

 

Specifylog: {<RET> = suggested | filename | AUTO | CANCEL}

Cancel

Mediarecovery canceled.

 

SQL> alter database open resetlogs;

 

Databasealtered.

 

SQL>

 

So far, recovery has been successful.

Related reading:

Exploring the RMAN_01 concept of Oracle

Explore the basic use of RMAN_02 in Oracle

Explore Oracle-based RMAN_03 non-consistent backup

Explore Oracle-based RMAN_04 non-consistent backup

Explore RMAN_05 Incremental Backup in Oracle

Exploring Oracle's RMAN_06 backup policy

Explore Oracle RMAN_07 restoration of single data file loss

Explore Oracle RMAN_07 recovery of whole business tablespace loss

Explore Oracle RMAN_07 disk corruption data loss recovery

Explore Oracle RMAN_07 recovery of all database files lost

Explore Oracle RMAN_07 redo log redu file loss recovery

Explore Oracle RMAN_07 parameter file loss recovery

Explore Oracle RMAN_07 control file loss recovery

Explore Oracle RMAN_07 system tablespace loss recovery