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

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

 

 

 

 

作者：张大鹏（Lunar）

                           Email:  [e-mail]

                            MSN:  [e-mail]

 

 

 

 

 

 

四 使用重建控制文件做（冷备）恢复

做冷备份前的测试数据

C:/>sqlplus "/ as sysdba"

 

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 23:20:52 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>  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;

 

no rows selected

 

SQL>

 

冷备数据库

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     0

Next log sequence to archive   1

Current log sequence           1

SQL> exit

Disconnected from 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

 

 

单开一个窗口做冷备

C:/>d:

 

D:/>cd D:/clonedb

 

D:/clonedb>cold_backup.bat > cold_backup.log

 

D:/clonedb>

 

查看冷备日志

 

 

D:/clonedb>echo Begin Cold_Backup Database...

Begin Cold_Backup Database...

 

D:/clonedb>date /t

2002-02-05 星期二

 

D:/clonedb>time /t

23:22

 

D:/clonedb>sqlplus "/ as sysdba" @shutdown.sql

 

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 23:22:24 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

 

Connected.

Database closed.

Database dismounted.

ORACLE instance shut down.

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

Database mounted.

Database opened.

Database closed.

Database dismounted.

ORACLE instance shut down.

Disconnected from 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

 

D:/clonedb>copy E:/oracle/ora92/test/*.* d:/clonedb

E:/oracle/ora92/test/cold_backup.bat

E:/oracle/ora92/test/shutdown.sql

E:/oracle/ora92/test/startup.sql

E:/oracle/ora92/test/cold_backup.log

E:/oracle/ora92/test/CONTROL03.CTL

E:/oracle/ora92/test/REDO01.LOG

E:/oracle/ora92/test/REDO02.LOG

E:/oracle/ora92/test/REDO03.LOG

E:/oracle/ora92/test/REDO04A.LOG

E:/oracle/ora92/test/REDO04B.LOG

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

E:/oracle/ora92/test/CONTROL02.CTL

E:/oracle/ora92/test/test_ora_1024.trc

E:/oracle/ora92/test/TEMP01.DBF

E:/oracle/ora92/test/PWDtest.ORA

E:/oracle/ora92/test/test_ora_1800.trc

已复制        22 个文件。

 

D:/clonedb>copy E:/oracle/ora92/database/PWDtest.ORA d:/clonedb

已复制         1 个文件。

 

D:/clonedb>copy E:/oracle/oradata/test/archive d:/clonedb/archive

E:/oracle/oradata/test/archive/*

已复制         0 个文件。

 

D:/clonedb>sqlplus "/ as sysdba" @startup.sql

 

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 23:26:37 2002

 

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

 

Connected to an idle instance.

 

Connected to an idle instance.

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

Database mounted.

Database opened.

 

System altered.

 

 

Database altered.

 

 

System altered.

 

Disconnected from 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

 

D:/clonedb>date /t

2002-02-05 星期二

 

D:/clonedb>time /t

23:26

 

D:/clonedb>echo The Cold Backup Database Successed. 

The Cold Backup Database Successed.

 

制作冷备份后的测试数据

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

SQL> conn lunar/lunar

Connected.

SQL> select * from test;

 

         F

----------

        15

        16

 

SQL>

SQL> insert into test values(25);

 

1 row created.

 

SQL>

SQL>  insert into test values(26);

 

1 row created.

 

SQL>

SQL>  commit;

 

Commit complete.

 

SQL>

SQL>  insert into test values(27);

 

1 row created.

 

SQL>

SQL> select * from test;

 

         F

----------

        15

        16

        25

        26

        27

 

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   2

Current log sequence           2

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

        16

        25           (new datas after cold backup)

        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>

 

 

删除所有数据库文件（数据文件，控制文件，redo log file）

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,413,568 可用字节

 

C:/>sqlplus "/ as sysdba"

 

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 22:55:33 2002

 

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

 

Connected to an idle instance.

 

SQL> startup

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

ORA-00205: error in identifying controlfile, check alert log for more info

 

 

SQL>

 

SQL> shutdown abort

ORACLE instance shut down.

SQL>

 

 

恢复数据库

将最近的冷备拷贝过来

 

SQL>  host

Microsoft Windows 2000 [Version 5.00.2195]

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

 

D:/clonedb>copy D:/clonedb/*.* E:/oracle/ora92/test

D:/clonedb/cold_backup.log

D:/clonedb/CONTROL02.CTL

D:/clonedb/CONTROL03.CTL

D:/clonedb/DRSYS01.DBF

D:/clonedb/INDX01.DBF

D:/clonedb/REDO01.LOG

D:/clonedb/REDO02.LOG

D:/clonedb/REDO03.LOG

D:/clonedb/REDO04A.LOG

D:/clonedb/REDO04B.LOG

D:/clonedb/RMAN01.DBF

D:/clonedb/SYSTEM01.DBF

D:/clonedb/TEMP01.DBF

D:/clonedb/TOOLS01.DBF

D:/clonedb/UNDOTBS01.DBF

D:/clonedb/USERS01.DBF

D:/clonedb/XDB01.DBF

D:/clonedb/PWDtest.ORA

D:/clonedb/test_ora_1912.trc

D:/clonedb/cold_backup.bat

D:/clonedb/shutdown.sql

D:/clonedb/startup.sql

已复制        22 个文件。

 

D:/clonedb>

 

 

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 1090465 generated at 02/06/2002 00:12:04 needed for thread 1

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

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

 

 

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

auto

ORA-00279: change 1090629 generated at 02/06/2002 00:18:31 needed for thread 1

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

ORA-00280: change 1090629 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-00308: cannot open archived log 'E:/ORACLE/ORADATA/TEST/ARCHIVE/1_2.ARC'

ORA-27041: unable to open file

OSD-04002: 无法打开文件

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

 

 

SQL>

 

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-06  00:16                2,864 cold_backup.log

2002-02-06  00:30            1,875,968 CONTROL02.CTL

2002-02-06  00:30            1,875,968 CONTROL03.CTL

2002-02-06  00:31          104,865,792 DRSYS01.DBF

2002-02-06  00:31           31,465,472 INDX01.DBF

2002-02-06  00:11           31,457,792 REDO01.LOG

2002-02-06  00:11           31,457,792 REDO02.LOG

2002-02-06  00:11           31,457,792 REDO03.LOG

2002-02-06  00:12            1,049,088 REDO04A.LOG

2002-02-06  00:12            1,049,088 REDO04B.LOG

2002-02-06  00:31           52,436,992 RMAN01.DBF

2002-02-06  00:31          367,009,792 SYSTEM01.DBF

2002-02-05  22:27           20,979,712 TEMP01.DBF

2002-02-06  00:31           31,465,472 TOOLS01.DBF

2002-02-06  00:31           52,436,992 UNDOTBS01.DBF

2002-02-06  00:31           31,465,472 USERS01.DBF

2002-02-06  00:31           52,436,992 XDB01.DBF

2002-02-05  21:49                3,072 PWDtest.ORA

2002-02-06  00:16                4,902 test_ora_1912.trc

2002-02-05  22:41                  340 cold_backup.bat

2002-02-05  11:28                   72 shutdown.sql

2002-02-05  12:19                  281 startup.sql

              22 个文件    844,797,707 字节

               2 个目录  1,957,179,392 可用字节

 

SQL>

 

 

去掉从冷备拷贝回来的redo log file

SQL> host del E:/oracle/ora92/test/REDO*.*

 

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-06  00:16                2,864 cold_backup.log

2002-02-06  00:30            1,875,968 CONTROL02.CTL

2002-02-06  00:30            1,875,968 CONTROL03.CTL

2002-02-06  00:31          104,865,792 DRSYS01.DBF

2002-02-06  00:31           31,465,472 INDX01.DBF

2002-02-06  00:31           52,436,992 RMAN01.DBF

2002-02-06  00:31          367,009,792 SYSTEM01.DBF

2002-02-05  22:27           20,979,712 TEMP01.DBF

2002-02-06  00:31           31,465,472 TOOLS01.DBF

2002-02-06  00:31           52,436,992 UNDOTBS01.DBF

2002-02-06  00:31           31,465,472 USERS01.DBF

2002-02-06  00:31           52,436,992 XDB01.DBF

2002-02-05  21:49                3,072 PWDtest.ORA

2002-02-06  00:16                4,902 test_ora_1912.trc

2002-02-05  22:41                  340 cold_backup.bat

2002-02-05  11:28                   72 shutdown.sql

2002-02-05  12:19                  281 startup.sql

              17 个文件    748,326,155 字节

               2 个目录  2,053,668,864 可用字节

 

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 1090629 generated at 02/06/2002 00:18:31 needed for thread 1

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

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

 

 

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-06  00:16                2,864 cold_backup.log

2002-02-06  00:30            1,875,968 CONTROL02.CTL

2002-02-06  00:30            1,875,968 CONTROL03.CTL

2002-02-06  00:34          104,865,792 DRSYS01.DBF

2002-02-06  00:34           31,465,472 INDX01.DBF

2002-02-06  00:34           31,457,792 REDO01.LOG

2002-02-06  00:34           31,457,792 REDO02.LOG

2002-02-06  00:34           31,457,792 REDO03.LOG

2002-02-06  00:34            1,049,088 REDO04A.LOG

2002-02-06  00:34            1,049,088 REDO04B.LOG

2002-02-06  00:34           52,436,992 RMAN01.DBF

2002-02-06  00:34          367,009,792 SYSTEM01.DBF

2002-02-05  22:27           20,979,712 TEMP01.DBF

2002-02-06  00:34           31,465,472 TOOLS01.DBF

2002-02-06  00:34           52,436,992 UNDOTBS01.DBF

2002-02-06  00:34           31,465,472 USERS01.DBF

2002-02-06  00:34           52,436,992 XDB01.DBF

2002-02-05  21:49                3,072 PWDtest.ORA

2002-02-06  00:16                4,902 test_ora_1912.trc

2002-02-05  22:41                  340 cold_backup.bat

2002-02-05  11:28                   72 shutdown.sql

2002-02-05  12:19                  281 startup.sql

              22 个文件    844,797,707 字节

               2 个目录  1,957,175,296 可用字节

 

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中的，还没有被归档