探索ORACLE不完全恢复之--基于SCN恢复 第一篇

SQL set line 300

SQL  select name,checkpoint_change# from v$datafile_header;

 

NAME                                         CHECKPOINT_CHANGE#

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

/DBBak2/oradata/WWL/system01.dbf                         1487389

/DBBak2/oradata/WWL/undotbs01.dbf                        1487389

/DBBak2/oradata/WWL/sysaux01.dbf                         1487389

/DBBak2/oradata/WWL/users01.dbf                          1487389

/DBBak2/oradata/WWL/wwl01.dbf                            1487389

/DBBak2/oradata/WWL/wwl02.dbf                            1487389

/DBBak2/oradata/WWL/wwl03.dbf                            1487389

 

7 rows selected.

 

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

 

    FILE# CHECKPOINT_CHANGE#

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

        1            1487389

        2            1487389

        3            1487389

        4            1487389

        5            1487389

        6            1487389

        7            1487389

 

7 rows selected.

 

SQL

 

 

2、删除测试用表：

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.

 

 

3、开始做基于SCN的恢复：

SQL recover database until change1487389;

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_1_788450452.dbf

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

 

 

ORA-00279: change 1440855 generated at07/12/2012 15:08:58 needed for thread 1

ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788454538.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_1_788455190.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_1_788457121.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_1_788459106.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 isin sequence #2

ORA-00278: log file/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788459106.dbf no longer needed forthis 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_1_788462279.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_1_788462279.dbf no longer needed forthis 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_1_788474127.dbf

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

 

 

Log applied.

Media recovery complete.

SQL alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS orNORESETLOGS option for database open

 

 

SQL alter database open resetlogs;

 

Database altered.

 

SQL     

 

4、至此，数据已经恢复完成：

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

 

 

ORACLE不完全恢复的几种情况 ORACLE不完全恢复的几种情况        RMAN不完全恢复的三个标准模式：基于time、基于scn和基于sequence，与手工不完全恢复相比原理类似，语法稍有不同： 2、不完全恢复的几种情况 ### 1）不完全恢复用于最小化测试备份 ###①备份一部分数据文件
prudentwoo 10g/11g OCP 11g OCM，ITPUB和CSDN专家及专家讲师；有着多年数据库从业经验，资深Oracle数据库专家，现就职于北京海量数据技术股份有限公司担任高级dba职务，为央视，银行，电信等各行业及企业提供过技术支持服务