Explore Oracle Incomplete recovery-Checkpoint-based recovery Article 2

SCN-based recovery, article 2

 

1. Check the current SCN to facilitate data loss in the future for restoration:

SQL> select current_scn from v $ database;

 

CURRENT_SCN

-----------

1511297

 

SQL> select file #, checkpoint_change # from v $ datafile;

 

FILE # CHECKPOINT_CHANGE #

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

1 1510535

2 1510535

3 1510535

4 1510535

5 1510535

6 1510535

7 1510535

 

7 rows selected.

 

SQL> col name format a45

SQL & gt; set line 300

SQL> select name, checkpoint_change # fromv $ datafile_header;

 

NAME CHECKPOINT_CHANGE #

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

/DBBak2/oradata/WWL/system01.dbf 1510535

/DBBak2/oradata/WWL/undotbs01.dbf 1510535

/DBBak2/oradata/WWL/sysaux01.dbf 1510535

/DBBak2/oradata/WWL/users01.dbf 1510535

/DBBak2/oradata/WWL/wwl01.dbf 1510535

/DBBak2/oradata/WWL/wwl02.dbf 1510535

/DBBak2/oradata/WWL/wwl03.dbf 1510535

 

7 rows selected.

 

SQL>

 

2. Delete the test table

SQL> drop table wwl002 purge;

 

Table dropped.

 

SQL> drop table wwl003 purge;

 

Table dropped.

 

SQL> drop table wwl004 purge;

 

Table dropped.

 

SQL> drop table wwl005 purge;

 

Table dropped.

 

SQL> conn/as sysdba

Connected.

 

3. After the DML operation is performed on the database, the SCN Number of the database changes.

SQL> select current_scn from v $ database;

 

CURRENT_SCN

-----------

1511437

 

 

4. Start executing restore to the backup status:

RMAN> restore database;

 

Starting restore at 13-JUL-12

Using target database control file insteadof recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: sid = 47 devtype = DISK

 

Channel ORA_DISK_1: starting datafilebackupset restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Restoring datafile 00001 to/DBBak2/oradata/WWL/system01.dbf

Restoring datafile 00002 to/DBBak2/oradata/WWL/undotbs01.dbf

Restoring datafile 00003 to/DBBak2/oradata/WWL/sysaux01.dbf

Restoring datafile 00004 to/DBBak2/oradata/WWL/users01.dbf

Restoring datafile 00005 to/DBBak2/oradata/WWL/wwl01.dbf

Restoring datafile 00006 to/DBBak2/oradata/WWL/wwl02.dbf

Restoring datafile 00007 to/DBBak2/oradata/WWL/wwl03.dbf

Channel ORA_DISK_1: reading from backuppiece/DBSoft/product/10.2.0/db_1/dbs/01nft4mu_1_1

Channel ORA_DISK_1: restored backup piece 1

Piecehandle =/DBSoft/product/10.2.0/db_1/dbs/01nft4mu_1_1 tag = TAG20120712T095437

Channel ORA_DISK_1: restore complete, elapsed time: 00:01:06

Finished restore at 13-JUL-12

 

5. Start to restore to the status before we delete the table:

SQL> recover database until change1511297;

ORA-00279: change 1436429 generated at07/12/2012 09:54:38 needed for thread 1

ORA-00289: suggestion:/DBSoft/product/10.2.0/db_1/dbs/arch1_3_788372282.dbf

ORA-00280: change 1436429 for thread 1 isin sequence #3

 

 

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

Auto

ORA-00279: change 1440657 generated at07/12/2012 14:00:52 needed for thread 1

ORA-00289: suggestion:/DBSoft/product/10.2.0/db_1/dbs/arch1_00007880000452.dbf

ORA-00280: change 1440657 for thread 1 isin sequence #1

 

 

ORA-00279: change 1440855 generated at 07/12/needed for thread 1

ORA-00289: suggestion:/DBSoft/product/10.2.0/db_1/dbs/arch1_rj788454538.dbf

ORA-00280: change 1440855 for thread 1 isin sequence #1

 

 

ORA-00279: change 1441316 generated at07/12/2012 15:19:50 needed for thread 1

ORA-00289: suggestion:/DBSoft/product/10.2.0/db_1/dbs/arch1_rj788455190.dbf

ORA-00280: change 1441316 for thread 1 isin sequence #1

 

 

ORA-00279: change 1442275 generated at07/12/2012 15:52:01 needed for thread 1

ORA-00289: suggestion:/DBSoft/product/10.2.0/db_1/dbs/arch1_2017788457121.dbf

ORA-00280: change 1442275 for thread 1 isin sequence #1

 

 

ORA-00279: change 1442953 generated at07/12/2012 16:25:06 needed for thread 1

ORA-00289: suggestion:/DBSoft/product/10.2.0/db_1/dbs/arch1_rj788459106.dbf

ORA-00280: change 1442953 for thread 1 isin sequence #1

 

 

ORA-00279: change 1462958 generated at07/12/2012 16:28:16 needed for thread 1

ORA-00289: suggestion:/DBSoft/product/10.2.0/db_1/dbs/arch1_2_788459106.dbf

ORA-00280: change 1462958 for thread 1 is insequence #2

ORA-00278: log file '/DBSoft/product/10.2.0/db_1/dbs/arch1_788459106.dbf' no

Longer needed for this recovery

 

 

ORA-00279: change 1462963 generated at07/12/2012 17:17:59 needed for thread 1

ORA-00289: suggestion:/DBSoft/product/10.2.0/db_1/dbs/arch1_2017788462279.dbf

ORA-00280: change 1462963 for thread 1 isin sequence #1

 

 

ORA-00279: change 1483784 generated at07/12/2012 17:54:25 needed for thread 1

ORA-00289: suggestion:/DBSoft/product/10.2.0/db_1/dbs/arch1_2_788462279.dbf

ORA-00280: change 1483784 for thread 1 isin sequence #2

ORA-00278: log file '/DBSoft/product/10.2.0/db_1/dbs/arch1_788462279.dbf' no

Longer needed for this recovery

 

 

ORA-00279: change 1486119 generated at07/12/2012 20:35:27 needed for thread 1

ORA-00289: suggestion:/DBSoft/product/10.2.0/db_1/dbs/arch1_2017788474127.dbf

ORA-00280: change 1486119 for thread 1 isin sequence #1

 

 

ORA-00279: change 1487388 generated at07/12/2012 21:31:17 needed for thread 1

ORA-00289: suggestion:/DBSoft/product/10.2.0/db_1/dbs/arch1_2017788477477.dbf

ORA-00280: change 1487388 for thread 1 isin sequence #1

 

 

Log applied.

Media recovery complete.

SQL>

 

6. After the restoration is completed, the SCN is cleared.

SQL> select current_scn from v $ database;

 

CURRENT_SCN

-----------

0

 

7. At this time, we need to clear the redo.

SQL> alter database open restlogs;

 

8. As you can see, the data has been restored:

SQL> select * from tab;

 

TNAME TABTYPE CLUSTERID

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

WWL001 TABLE

WWL002 TABLE

WWL003 TABLE

WWL004 TABLE

WWL005 TABLE

 

SQL> select * from wwl005;

 

ID NAME

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

1 wwl

2 prodence

3 woo

4 xgx

5 cms

 

SQL>

 

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