关于数据文件的恢复试验

本实验是模拟了删除数据文件后，对数据文件的恢复。
前提：有恢复必备的备份及归档文件
本次删除数据文件13#（D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF），test3是该数据文件上的表。
向test3里插入数据
SQL> insert into test3 select * from test3;

已创建8192行。

SQL> commit;

提交完成。

SQL> select count(*) from test3;

  COUNT(*)
----------
     16384
 
 SQL>shutdown immediate
 
 干净关闭数据库后，删除掉数据文件D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF。
    
C:\Documents and Settings\zhangyouhai>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Oct 31 10:02:35 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  979283968 bytes
Fixed Size                  1334040 bytes
Variable Size             226493672 bytes
Database Buffers          746586112 bytes
Redo Buffers                4870144 bytes
Database mounted.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\SYSTEM01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\SYSAUX01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\UNDOTBS01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\USERS01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEST_DATA01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEST_IDX01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEST_DATA02.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEST_IDX02.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\SYSTEM02.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\SYSAUX02.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEST_DATA03.DBF

NAME
--------------------------------------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\TEST.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF
D:\ROWCHAIN22.DBF

14 rows selected.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01110: data file 13: 'D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF'

在rman下进行数据文件介质的恢复

C:\Documents and Settings\zhangyouhai>rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Wed Oct 31 10:05:01 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: MDLCNPRO (DBID=3079513499, not open)

RMAN> restore datafile 13;

Starting restore at 31-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=540 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00013 to D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF
channel ORA_DISK_1: reading from backup piece D:\BACKUP\RMAN\147_FULL_MDLCNPRO_20121030_3553.BAK
channel ORA_DISK_1: piece handle=D:\BACKUP\RMAN\147_FULL_MDLCNPRO_20121030_3553.BAK tag=TAG20121030T114211
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 31-OCT-12

查看当前log信息

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------------
         1          1        370  209715200          2 NO  CURRENT
   2546490807 31-OCT-12

         3          1        369  209715200          2 YES INACTIVE
   2546471038 31-OCT-12

         2          1        368  209715200          2 YES INACTIVE
   2546449644 31-OCT-12

SQL> set linesize 200
SQL> /

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1        370  209715200          2 NO  CURRENT             2546490807 31-OCT-12
         3          1        369  209715200          2 YES INACTIVE            2546471038 31-OCT-12
         2          1        368  209715200          2 YES INACTIVE            2546449644 31-OCT-12

查看系统检查点

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
        2546501753

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

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
        2546501753   2546501753
        2546501753   2546501753
        2546501753   2546501753
        2546501753   2546501753
        2546501753   2546501753
        2546501753   2546501753
        2546501753   2546501753
        2546501753   2546501753
        2546501753   2546501753
        2546501753   2546501753
        2546501753   2546501753

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
        2546501753   2546501753
        2546501753   2546501753
        2546501753   2546501753

14 rows selected.

SQL> select checkpoint_change# from v$datfile_header;
select checkpoint_change# from v$datfile_header
                               *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13: 'D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF'

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------------
        13 ONLINE  ONLINE                                                                    2546250165 30-OCT-12

我们看到恢复的13号文件需要从2546250165开始恢复，下面查一下这个scn所属那个archive log

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'
  2  ;

Session altered.

SQL> select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,FIRST_TIME from v$archived_log where 2546250164 between FIRST_CHANGE# and next_CHANGE#;

 SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# FIRST_TIME
---------- ------------- ------------ -------------------
       358    2546248788   2546271458 2012-10-30 11:29:02
      
 从上边的查询看出本次recover需要从358#日志恢复数据文件

SQL> RECOVER DATAFILE 13;
ORA-00279: change 2546250165 generated at 10/30/2012 11:50:51 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00358_0794846985.001
ORA-00280: change 2546250165 for thread 1 is in sequence #358

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 2546271458 generated at 10/30/2012 12:28:54 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00359_0794846985.001
ORA-00280: change 2546271458 for thread 1 is in sequence #359

ORA-00279: change 2546291601 generated at 10/30/2012 12:59:02 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00360_0794846985.001
ORA-00280: change 2546291601 for thread 1 is in sequence #360

ORA-00279: change 2546315757 generated at 10/30/2012 13:58:55 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00361_0794846985.001
ORA-00280: change 2546315757 for thread 1 is in sequence #361

ORA-00279: change 2546334055 generated at 10/30/2012 14:29:05 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00362_0794846985.001
ORA-00280: change 2546334055 for thread 1 is in sequence #362

ORA-00279: change 2546361678 generated at 10/30/2012 15:28:58 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00363_0794846985.001
ORA-00280: change 2546361678 for thread 1 is in sequence #363

ORA-00279: change 2546378388 generated at 10/30/2012 16:23:39 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00364_0794846985.001
ORA-00280: change 2546378388 for thread 1 is in sequence #364

ORA-00279: change 2546383271 generated at 10/30/2012 16:24:00 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00365_0794846985.001
ORA-00280: change 2546383271 for thread 1 is in sequence #365

ORA-00279: change 2546387490 generated at 10/30/2012 16:27:13 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00366_0794846985.001
ORA-00280: change 2546387490 for thread 1 is in sequence #366

ORA-00279: change 2546413409 generated at 10/30/2012 16:58:59 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00367_0794846985.001
ORA-00280: change 2546413409 for thread 1 is in sequence #367

Log applied.
Media recovery complete.
SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select * from v$recover_file;

no rows selected

SQL> select count(*) from test3;

  COUNT(*)
----------
     16384
    
查询结果得出的记录与之前的记录16384相同。恢复完成。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23891491/viewspace-747983/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23891491/viewspace-747983/