使用创建数据文件的方法修复数据库(9201)

 

 

 

 

 

 

 

使用创建数据文件的方法修复数据库(9201)

 

 

 

 

 

 

 

 

 

作者：张大鹏（Lunar）

                           Email:  [e-mail]

                            MSN:  [e-mail]

 

  

前言

当丢失某个数据文件的时候，可以使用重建数据文件的方法快速的恢复数据，这样做的前题是，必须具有当前的控制文件（或者最新的BACKUP TO TRACE的控制文件，一边重建控制文件）和所有的归档日志，以及当前的联机日志文件（即，使用从该文件创建一来到当前时间的所有归档日志和联机日志，应用自文件创建一来的所有重做记录来前滚恢复该数据文件）。

所以，在改变数据库结构后，应当立刻备份数据库，如果条件不允许备份数据库，那么至少应当备份控制文件。

这种恢复方法尤其试用于当文件损坏时，还没有备份这个数据文件或者，备份的数据文件不可用的情况

 

 

一 使用当前控制文件

 

制作测试数据

查看当前数据

SQL> archive log list;

数据库日志模式            存档模式

自动存档             启用

存档终点            e:/oracle/oradata/test/archive

最早的概要日志序列     1

下一个存档日志序列   2

当前日志序列           2

SQL> conn lunar/lunar

已连接。

SQL> select * from test;

 

         F

----------

        15

        16

        25

        26

 

创建新的表空间和数据文件

SQL> conn / as sysdba

已连接。

SQL> CREATE TABLESPACE test LOGGING DATAFILE

  2     'E:/oracle/ora92/test/test01.dbf' SIZE 2M

  3     AUTOEXTEND ON

  4     NEXT  64K

  5     MAXSIZE UNLIMITED

  6     EXTENT MANAGEMENT LOCAL;

 

表空间已创建。

 

SQL> alter user lunar quota unlimited on test;

 

用户已更改。

 

SQL>

 

 

备份控制文件

SQL> ALTER DATABASE

BACKUP CONTROLFILE TO 'D:/cold_backup/CONTROL_BK.CTL';

 

Database altered.

 

SQL> alter system set user_dump_dest='D:/cold_backup';

 

系统已更改。

 

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

 

数据库已更改。

 

SQL> alter system set user_dump_dest='e:/oracle/admin/test/udump';

 

系统已更改。

 

SQL>  host dir D:/cold_backup

 驱动器 D 中的卷是 PROGRAMS

 卷的序列号是 A06D-D424

 

 D:/cold_backup 的目录

 

2002-02-02  21:07       <DIR>          .

2002-02-02  21:07       <DIR>          ..

2003-02-06  14:34       <DIR>          archive

2003-02-06  14:44            1,875,968 CONTROL_BK.CTL

2003-02-06  14:44                4,578 test_ora_1844.trc

               2 个文件      1,880,874 字节

               3 个目录  2,367,717,376 可用字节

SQL>

 

在新的数据文件中插入测试数据

SQL> conn lunar/lunar

已连接。

SQL> create table lunar(aa number) TABLESPACE TEST;

 

表已创建。

 

SQL> select TABLE_NAME,TABLESPACE_NAME

  2   from user_tables

  3   where TABLE_NAME='LUNAR';

 

TABLE_NAME                     TABLESPACE_NAME

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

LUNAR                          TEST

 

SQL>

 

 

SQL> insert into lunar values(1);

 

已创建 1 行。

 

SQL> insert into lunar values(2);

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from lunar;

 

        AA

----------

         1

         2

 

SQL> insert into lunar values(3);

 

已创建 1 行。

 

SQL>

 

 

再打开一个AS SYSDBA的窗口，产生checkpoin

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 Thu Feb 6 14:48:11 2003

 

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> show user

USER 为"LUNAR"

SQL> insert into lunar values(4);

 

已创建 1 行。

 

SQL> insert into lunar values(5);

 

已创建 1 行。

 

SQL>

 

 

回到AS SYSDBA的窗口，SWITCH LOGFILE

SQL> alter system switch logfile;

 

System altered.

 

SQL>

 

 

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

SQL> show user

USER 为"LUNAR"

SQL> insert into lunar values(6);

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL>

 

 

回到AS SYSDBA的窗口，CHECKPOINT

SQL>  alter system checkpoint;

 

System altered.

 

SQL>

 

 

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

SQL> insert into lunar values(7);

 

已创建 1 行。

 

SQL> select * from lunar;

 

        AA

----------

         1

         2

                            （ commit ）

         3

                            （ checkpoint ）

         4

         5

                            （ switch logfile ）

         6

                            （ commit，checkpoint ）

         7

                            （ crash ）

 

已选择7行。

 

应该可以恢复到所有commit的数据，即6

 

回到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/test01.dbf

 

C:/>exit

 

SQL>

 

启动数据库，报错

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

Database mounted.

ORA-01157: cannot identify/lock data file 8 - see DBWR trace file

ORA-01110: data file 8: 'E:/ORACLE/ORA92/TEST/TEST01.DBF'

 

SQL>

 

恢复数据库

重建损坏的数据文件

SQL> alter database create datafile 'E:/oracle/ora92/test/test01.dbf';

 

Database altered.

 

SQL>

 

恢复损坏的数据文件（前滚数据）

SQL> recover datafile 'E:/oracle/ora92/test/test01.dbf';

Media recovery complete.

SQL>

 

打开数据库

SQL> alter database open;

 

Database altered.

 

SQL>

 

验证恢复

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

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

Oldest online log sequence     2

Next log sequence to archive   5

Current log sequence           5

SQL> conn lunar/lunar

Connected.

SQL> select * from lunar;

 

        AA

----------

         1

         2

         3

         4

         5

         6

 

6 rows selected.

 

SQL> select TABLE_NAME,TABLESPACE_NAME

  2  from user_tables

  3  where TABLE_NAME='LUNAR';

 

TABLE_NAME                     TABLESPACE_NAME

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

LUNAR                          TEST

 

SQL>

 

 

二 使用备份控制文件（TO FILE）

 

制作测试数据

查看当前数据

SQL> conn / as sysdba

Connected.

SQL> drop tablespace test including contents;

 

Tablespace dropped.

 

SQL> host del E:/oracle/ora92/test/test01.dbf

 

SQL>  archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

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

Oldest online log sequence     7

Next log sequence to archive   10

Current log sequence           10

SQL>

 

创建新的表空间和数据文件

SQL> CREATE TABLESPACE test LOGGING DATAFILE

  2     'E:/oracle/ora92/test/test01.dbf' SIZE 2M

  3     AUTOEXTEND ON

  4     NEXT  64K

  5     MAXSIZE UNLIMITED

  6     EXTENT MANAGEMENT LOCAL;

 

表空间已创建。

 

SQL> alter user lunar quota unlimited on test;

 

用户已更改。

 

SQL>

 

 

备份控制文件

SQL> ALTER DATABASE BACKUP CONTROLFILE

TO 'D:/cold_backup/CONTROL_BK.CTL';

 

Database altered.

 

SQL> alter system set user_dump_dest='D:/cold_backup';

 

System altered.

 

SQL>  ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

 

Database altered.

 

SQL>  alter system set user_dump_dest='e:/oracle/admin/test/udump';

 

System altered.

 

SQL>  host dir D:/cold_backup

 驱动器 D 中的卷是 PROGRAMS

 卷的序列号是 A06D-D424

 

 D:/cold_backup 的目录

 

2002-02-02  21:07       <DIR>          .

2002-02-02  21:07       <DIR>          ..

2003-02-06  14:34       <DIR>          archive

2003-02-06  16:25            1,875,968 CONTROL_BK.CTL

2003-02-06  16:25                4,982 test_ora_1868.trc

               2 个文件      1,880,950 字节

               3 个目录  2,365,833,216 可用字节

 

SQL>

 

在新的数据文件中插入测试数据

SQL> conn lunar/lunar

已连接。

SQL> create table lunar(aa number) TABLESPACE TEST;

 

表已创建。

 

SQL> select TABLE_NAME,TABLESPACE_NAME

  2   from user_tables

  3   where TABLE_NAME='LUNAR';

 

TABLE_NAME                     TABLESPACE_NAME

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

LUNAR                          TEST

 

SQL>

 

 

SQL> insert into lunar values(1);

 

已创建 1 行。

 

SQL> insert into lunar values(2);

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from lunar;

 

        AA

----------

         1

         2

 

SQL> insert into lunar values(3);

 

已创建 1 行。

 

SQL>

 

 

再打开一个AS SYSDBA的窗口，产生checkpoin

Microsoft Windows 2000 [Version 5.00.2195]

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

 

C:/>sqlplus "/ as sysdba"

 

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Feb 6 14:48:11 2003

 

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;

 

系统已更改。

 

SQL>

 

 

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

SQL> show user

USER 为"LUNAR"

SQL> insert into lunar values(4);

 

已创建 1 行。

 

SQL> insert into lunar values(5);

 

已创建 1 行。

 

SQL>

 

 

回到AS SYSDBA的窗口，SWITCH LOGFILE

SQL> alter system switch logfile;

 

System altered.

 

SQL>

 

 

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

SQL> show user

USER 为"LUNAR"

SQL> insert into lunar values(6);

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL>

 

 

回到AS SYSDBA的窗口，CHECKPOINT

SQL>  alter system checkpoint;

 

System altered.

 

SQL>

 

 

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

SQL> insert into lunar values(7);

 

已创建 1 行。

 

SQL> select * from lunar;

         

        AA

----------

         1

         2

                            （ commit ）

         3

                            （ checkpoint ）

         4

         5

                            （ switch logfile ）

         6

                            （ commit，checkpoint ）

         7

                            （ crash ）

 

已选择7行。

 

应该可以恢复到所有commit的数据，即6

 

回到AS SYSDBA的窗口，模拟数据库crash

SQL> shutdown abort

ORACLE instance shut down.

SQL>

 

 

删除新增的数据文件

C:/>del E:/oracle/ora92/test/test01.dbf

 

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

 

C:/>dir E:/oracle/ora92/test/control*.* E:/oracle/ora92/test/test01.dbf

 驱动器 E 中的卷是 ORACLE

 卷的序列号是 707A-C50C

 

 E:/oracle/ora92/test 的目录

 

 

 E:/oracle/ora92/test 的目录

 

找不到文件

 

C:/>

 

 

启动数据库，报错

C:/>set nls_lang=AMERICAN_AMERICA.US7ASCII

 

C:/>sqlplus "/ as sysdba"

 

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Feb 6 15:30:13 2003

 

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>

 

 

恢复数据库

把备份的控制文件拷贝回来

C:/>copy D:/cold_backup/CONTROL_BK.CTL E:/oracle/ora92/test/control02.ctl

已复制         1 个文件。

 

C:/>copy D:/cold_backup/CONTROL_BK.CTL E:/oracle/ora92/test/control03.ctl

已复制         1 个文件。

 

C:/>

 

 

装载但不打开数据库

SQL> startup mount;

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.

SQL>

 

 

重建损坏的数据文件（在OS上建立该文件）

SQL> alter database create datafile 'E:/oracle/ora92/test/test01.dbf';

 

Database altered.

 

SQL>

 

恢复数据库（using backup controlfile）

SQL> recover database using backup controlfile;

ORA-00279: change 1156110 generated at 02/06/2003 16:25:09 needed for thread 1

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

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

 

 

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

Auto       （已经应用了最后一个归档日志）

ORA-00279: change 1156200 generated at 02/06/2003 16:28:05 needed for thread 1

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

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

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

for this recovery

 

 

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

ORA-27041: unable to open file

OSD-04002: ??????

O/S-Error: (OS 2) ???????????

 

 

SQL>

 

再次恢复（until cancel using backup controlfile）

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 1156200 generated at 02/06/2003 16:28:05 needed for thread 1

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

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

 

 

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

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

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

 

 

ORA-01112: media recovery not started

说明需要当前日志文件来恢复数据库

 

 

使用当前日志文件来恢复数据库（前滚）

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 1156200 generated at 02/06/2003 16:28:05 needed for thread 1

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

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

 

 

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

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

ORA-00310: archived log contains sequence 10; sequence 11 required

ORA-00334: archived log: 'E:/ORACLE/ORA92/TEST/REDO01.LOG'

 

 

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

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

 

 

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 1156200 generated at 02/06/2003 16:28:05 needed for thread 1

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

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

 

 

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

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

Log applied.

Media recovery complete.

SQL>

 

 

 

resetlogs打开数据库

SQL> alter database open resetlogs;

 

Database altered.

 

SQL>

 

验证恢复

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>

SQL> conn lunar/lunar

Connected.

SQL> select * from lunar;

 

        AA

----------

         1

         2

         3

         4

         5

         6

 

6 rows selected.

 

SQL> select TABLE_NAME,TABLESPACE_NAME

  2  from user_tables

  3  where TABLE_NAME='LUNAR';

 

TABLE_NAME                     TABLESPACE_NAME

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

LUNAR                          TEST

 

SQL>