丢失全部数据文件，控制文件，redo log file（9201）——处理方法二

丢失全部数据文件，控制文件，redo log file（9201）

 

 

 

 

作者：张大鹏（Lunar）

                           Email:  [e-mail]

                            MSN:  [e-mail]

 

  

 

 

 

 

二 使用重建控制文件做（热备）恢复

做热备份前的测试数据

SQL> conn lunar/lunar

Connected.

SQL> truncate table test;

 

Table truncated.

 

SQL> insert into test values(15);

 

1 row created.

 

SQL> insert into test values(16);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

 

 

热备数据库

SQL> conn / as sysdba

Connected.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            e:/oracle/oradata/test/archive

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SQL> @D:/hot_backup/hotbackup_for_win

已复制         1 个文件。

已复制         1 个文件。

已复制         1 个文件。

已复制         1 个文件。

已复制         1 个文件。

已复制         1 个文件。

已复制         1 个文件。

已复制         1 个文件。

SQL>

 

 

做热备份后的测试数据

提交部分测试数据（备份后增加的）

SQL> conn lunar/lunar

Connected.

SQL> select * from test;

        15

        16

 

2 rows selected.

 

SQL> insert into test values(25);

 

1 row created.

 

SQL> insert into test values(26);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> insert into test values(27);

 

1 row created.

 

SQL>

 

再开一个窗口，产生检查点

Microsoft Windows 2000 [Version 5.00.2195]

(C) 版权所有 1985-2000 Microsoft Corp.

 

C:/>set nls_lang=AMERICAN_AMERICA.US7ASCII

 

C:/>sqlplus "/ as sysdba"

 

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 21:28:47 2002

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

 

SQL> alter system checkpoint;

 

System altered.

 

SQL>

 

回到原来的窗口，继续添加数据

SQL> insert into test values(28);

 

1 row created.

 

SQL>

 

 

回到as sysdba的窗口，switch logfile

SQL> alter system switch logfile;

 

System altered.

 

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            e:/oracle/oradata/test/archive

Oldest online log sequence     1

Next log sequence to archive   3

Current log sequence           3

SQL>

 

回到原来的窗口，commit后，继续添加数据

SQL> commit;

 

Commit complete.

 

SQL> insert into test values(29);

 

1 row created.

 

SQL>

 

回到as sysdba的窗口，产生检查点

SQL>  alter system checkpoint;

 

System altered.

 

SQL>

 

 

 

回到原来的窗口，不commit继续添加数据

 

SQL> insert into test values(30);

 

1 row created.

 

SQL> select * from test;

        15           (new datas after hot backup)

        16

        25

        26   

( commit )

        27   

(checkpoint )

        28

                            ( switch logfile )

                            (commit )

        29

                            (checkpoint )

        30

                            ( shutdown abort)

8 rows selected.

 

SQL>

只能恢复到switch logfile前全部commit的数据，即：26

 

回到as sysdba的窗口，模拟数据库crash

SQL> shutdown abort

ORACLE instance shut down.

SQL>

 

删除所有数据库文件

SQL> host

Microsoft Windows 2000 [Version 5.00.2195]

(C) 版权所有 1985-2000 Microsoft Corp.

 

C:/>del E:/oracle/ora92/test/*.*

E:/oracle/ora92/test/*.*, 是否确认 (Y/N) ? y

 

C:/>dir E:/oracle/ora92/test

 驱动器 E 中的卷是 ORACLE

 卷的序列号是 707A-C50C

 

 E:/oracle/ora92/test 的目录

 

2002-01-13  21:59       <DIR>          .

2002-01-13  21:59       <DIR>          ..

               0 个文件              0 字节

               2 个目录  2,802,962,432 可用字节

 

C:/>

C:/>exit

 

SQL>

 

 

恢复数据库

将最近的热备拷贝回来

C:/>copy D:/hot_backup/*.* E:/oracle/ora92/test

D:/hot_backup/hotbackup_for_win.sql

D:/hot_backup/hotbackup.sql

D:/hot_backup/hotbackup.log

D:/hot_backup/SYSTEM01.DBF

D:/hot_backup/UNDOTBS01.DBF

D:/hot_backup/DRSYS01.DBF

D:/hot_backup/INDX01.DBF

D:/hot_backup/TOOLS01.DBF

D:/hot_backup/USERS01.DBF

D:/hot_backup/XDB01.DBF

D:/hot_backup/RMAN01.DBF

D:/hot_backup/CONTROL02.CTL

D:/hot_backup/test_ora_1972.trc

已复制        13 个文件。

 

C:/>

C:/>exit

 

SQL>

 

编辑并修改备份的控制文件（backup to trace）

CREATE CONTROLFILE REUSE DATABASE "TEST1" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 5

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 1

    MAXLOGHISTORY 226

LOGFILE

  GROUP 1 'E:/ORACLE/ORA92/TEST/REDO01.LOG'  SIZE 30M,

  GROUP 2 'E:/ORACLE/ORA92/TEST/REDO02.LOG'  SIZE 30M,

  GROUP 3 'E:/ORACLE/ORA92/TEST/REDO03.LOG'  SIZE 30M,

  GROUP 4 (

    'E:/ORACLE/ORA92/TEST/REDO04A.LOG',

    'E:/ORACLE/ORA92/TEST/REDO04B.LOG'

  ) SIZE 1M

DATAFILE

  'E:/ORACLE/ORA92/TEST/SYSTEM01.DBF',

  'E:/ORACLE/ORA92/TEST/UNDOTBS01.DBF',

  'E:/ORACLE/ORA92/TEST/DRSYS01.DBF',

  'E:/ORACLE/ORA92/TEST/INDX01.DBF',

  'E:/ORACLE/ORA92/TEST/TOOLS01.DBF',

  'E:/ORACLE/ORA92/TEST/USERS01.DBF',

  'E:/ORACLE/ORA92/TEST/XDB01.DBF',

  'E:/ORACLE/ORA92/TEST/RMAN01.DBF'

CHARACTER SET ZHS16GBK

;

 

 

nomount数据库

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  135338868 bytes

Fixed Size                   453492 bytes

Variable Size             109051904 bytes

Database Buffers           25165824 bytes

Redo Buffers                 667648 bytes

SQL>

 

 

重建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "TEST1" RESETLOGS  ARCHIVELOG

  2      MAXLOGFILES 5

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 1

  6      MAXLOGHISTORY 226

  7  LOGFILE

  8    GROUP 1 'E:/ORACLE/ORA92/TEST/REDO01.LOG'  SIZE 30M,

  9    GROUP 2 'E:/ORACLE/ORA92/TEST/REDO02.LOG'  SIZE 30M,

 10    GROUP 3 'E:/ORACLE/ORA92/TEST/REDO03.LOG'  SIZE 30M,

 11    GROUP 4 (

 12      'E:/ORACLE/ORA92/TEST/REDO04A.LOG',

 13      'E:/ORACLE/ORA92/TEST/REDO04B.LOG'

 14    ) SIZE 1M

 15  DATAFILE

 16    'E:/ORACLE/ORA92/TEST/SYSTEM01.DBF',

 17    'E:/ORACLE/ORA92/TEST/UNDOTBS01.DBF',

 18    'E:/ORACLE/ORA92/TEST/DRSYS01.DBF',

 19    'E:/ORACLE/ORA92/TEST/INDX01.DBF',

 20    'E:/ORACLE/ORA92/TEST/TOOLS01.DBF',

 21    'E:/ORACLE/ORA92/TEST/USERS01.DBF',

 22    'E:/ORACLE/ORA92/TEST/XDB01.DBF',

 23    'E:/ORACLE/ORA92/TEST/RMAN01.DBF'

 24  CHARACTER SET ZHS16GBK

 25  ;

 

Control file created.

 

SQL>

 

 

恢复数据库（USING BACKUP CONTROLFILE）

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE

ORA-00279: change 1087799 generated at 02/05/2002 21:19:44 needed for thread 1

ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_1.ARC

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

 

 

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

auto

ORA-00279: change 1087918 generated at 02/05/2002 21:23:09 needed for thread 1

ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_2.ARC

ORA-00280: change 1087918 for thread 1 is in sequence #2

ORA-00278: log file 'E:/ORACLE/ORADATA/TEST/ARCHIVE/1_1.ARC' no longer needed

for this recovery

 

 

ORA-00279: change 1088081 generated at 02/05/2002 21:31:09 needed for thread 1

ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_3.ARC

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

ORA-00278: log file 'E:/ORACLE/ORADATA/TEST/ARCHIVE/1_2.ARC' no longer needed

for this recovery

 

 

ORA-00308: cannot open archived log 'E:/ORACLE/ORADATA/TEST/ARCHIVE/1_3.ARC'

ORA-27041: unable to open file

OSD-04002: 无法打开文件

O/S-Error: (OS 2) 系统找不到指定的文件。

 

 

SQL> host dir E:/oracle/ora92/test

 驱动器 E 中的卷是 ORACLE

 卷的序列号是 707A-C50C

 

 E:/oracle/ora92/test 的目录

 

2002-01-13  21:59       <DIR>          .

2002-01-13  21:59       <DIR>          ..

2002-02-05  21:49          367,009,792 SYSTEM01.DBF

2002-02-05  21:49           52,436,992 UNDOTBS01.DBF

2002-02-05  21:49          104,865,792 DRSYS01.DBF

2002-02-05  21:49           31,465,472 INDX01.DBF

2002-02-05  21:49           31,465,472 TOOLS01.DBF

2002-02-05  21:49           31,465,472 USERS01.DBF

2002-02-05  21:49           52,436,992 XDB01.DBF

2002-02-05  21:49           52,436,992 RMAN01.DBF

2002-02-05  21:49            1,875,968 CONTROL02.CTL

2002-02-05  21:49            1,875,968 CONTROL03.CTL

              10 个文件    727,334,912 字节

               2 个目录  2,075,586,560 可用字节

 

SQL>

再次恢复（until cancel USING BACKUP CONTROLFILE）

SQL> ALTER DATABASE OPEN RESETLOGS;

ALTER DATABASE OPEN RESETLOGS

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: 'E:/ORACLE/ORA92/TEST/SYSTEM01.DBF'

 

 

SQL> RECOVER DATABASE until cancel USING BACKUP CONTROLFILE;

ORA-00279: change 1088081 generated at 02/05/2002 21:31:09 needed for thread 1

ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_3.ARC

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

 

 

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

cancel

Media recovery cancelled.

SQL>

RESETLOGS打开数据库

SQL>  ALTER DATABASE OPEN RESETLOGS;

 

Database altered.

 

SQL> host dir E:/oracle/ora92/test

 驱动器 E 中的卷是 ORACLE

 卷的序列号是 707A-C50C

 

 E:/oracle/ora92/test 的目录

 

2002-01-13  21:59       <DIR>          .

2002-01-13  21:59       <DIR>          ..

2002-02-05  21:52           31,457,792 REDO01.LOG

2002-02-05  21:52           31,457,792 REDO02.LOG

2002-02-05  21:52           31,457,792 REDO03.LOG

2002-02-05  21:52            1,049,088 REDO04A.LOG

2002-02-05  21:52            1,049,088 REDO04B.LOG

2002-02-05  21:52          367,009,792 SYSTEM01.DBF

2002-02-05  21:52           52,436,992 UNDOTBS01.DBF

2002-02-05  21:52          104,865,792 DRSYS01.DBF

2002-02-05  21:52           31,465,472 INDX01.DBF

2002-02-05  21:52           31,465,472 TOOLS01.DBF

2002-02-05  21:52           31,465,472 USERS01.DBF

2002-02-05  21:52           52,436,992 XDB01.DBF

2002-02-05  21:52           52,436,992 RMAN01.DBF

2002-02-05  21:49            1,875,968 CONTROL02.CTL

2002-02-05  21:49            1,875,968 CONTROL03.CTL

              15 个文件    823,806,464 字节

               2 个目录  1,979,092,992 可用字节

 

SQL>

 

 

验证恢复

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            e:/oracle/oradata/test/archive

Oldest online log sequence     0

Next log sequence to archive   1

Current log sequence           1

SQL> conn lunar/lunar

Connected.

SQL> select * from test;

        15

        16

        25

        26

 

4 rows selected.

 

SQL>

丢失了部分数据commit的数据，因为那些数据是在redo中的，还没有被归档