热备期间数据库crash（9201）

 

 

 

 

 

热备期间数据库crash（9201）

 

 

 

 

 

 

作者：张大鹏（Lunar）

                           Email:  moonlunar@163.com

                            MSN:  lunar52@hotmail.com

 

 

 

   

 

 

 

 

前言

热备使用alter tablespace xxx begin backup; 命令，这种状态仅仅是通知数据库，属于表空间xxx的数据文件正处于热备期间，这时属于该表空间的数据文件的文件头的某些数据结构被更新，而其他部分被冻结。也就是说，文件内容是当前的，即，当对该文件中的一个表更新的时候，则部分数据库块被更新，但是检查点完成时，检查点的SCN值不写入文件头，当发出alter tablespace xxx end backup;命令时，才更新文件头，即，此时才更新热备期间产生的所有检查点的SCN值。

所以，这种恢复的关键在于找出正在备份的数据文件，并且发出

alter tablespace xxx end backup;

来结束该表空间的热备状态。

 

 

检查数据库归档状态

SQL> archive log list;

数据库日志模式            存档模式

自动存档             启用

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

最早的概要日志序列     81

下一个存档日志序列   84

当前日志序列           84

SQL>

 

 

进行热备

 

 

另开一个窗口，制作测试数据

C:/>set nls_lang=AMERICAN_AMERICA.US7ASCII

 

C:/>sqlplus "/ as sysdba"

 

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 17:17:36 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> conn lunar/lunar

Connected.

SQL> select * from test;

 

no rows selected

 

SQL>  insert into test values(22);

 

1 row created.

 

SQL>  insert into test values(23);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL>  select * from test;

 

         F

----------

        22

        23

 

SQL>  insert into test values(100);

 

1 row created.

 

SQL> select * from test;

 

         F

----------

        22

        23

       100

 

SQL> conn / as sysdba

Connected.

SQL> alter system checkpoint;

 

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     81

Next log sequence to archive   84

Current log sequence           84

SQL> conn lunar/lunar

Connected.

SQL>  insert into test values(111);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL>  insert into test values(112);

 

1 row created.

 

SQL> select * from test;

 

         F

----------

        22

        23

       100

       111

       112

 

SQL>

 

 

 

 

再开一个窗口，模拟热备期间数据库crash

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 17:24:55 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> shutdown abort

ORACLE instance shut down.

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-01113: file 9 needs media recovery

ORA-01110: data file 9: 'E:/ORACLE/ORA92/TEST/RMAN01.DBF'

 

 

SQL>

 

恢复数据库

检查备份文件的状态

SQL> column filename format a35

SQL> select a.name filename,b.STATUS, b.CHANGE#,b.TIME

  2  from v$datafile a, v$backup b

  3  where a.FILE#=b.FILE#;

 

FILENAME                            STATUS                CHANGE# TIME

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

E:/ORACLE/ORA92/TEST/SYSTEM01.DBF   NOT ACTIVE   1045464 05-FEB-02

E:/ORACLE/ORA92/TEST/UNDOTBS01.DBF  NOT ACTIVE   1045508 05-FEB-02

E:/ORACLE/ORA92/TEST/DRSYS01.DBF    NOT ACTIVE   1045521 05-FEB-02

E:/ORACLE/ORA92/TEST/INDX01.DBF     NOT ACTIVE   1045535 05-FEB-02

E:/ORACLE/ORA92/TEST/TOOLS01.DBF    NOT ACTIVE   1045542 05-FEB-02

E:/ORACLE/ORA92/TEST/USERS01.DBF    NOT ACTIVE   1045550 05-FEB-02

E:/ORACLE/ORA92/TEST/XDB01.DBF      NOT ACTIVE   045558 05-FEB-02

E:/ORACLE/ORA92/TEST/RMAN01.DBF     ACTIVE      1045569 05-FEB-02

 

8 rows selected.

 

SQL>

 

 

恢复这个文件（让其结束备份）

SQL> alter database datafile 'E:/ORACLE/ORA92/TEST/RMAN01.DBF' end backup;

 

Database altered.

 

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     82

Next log sequence to archive   85

Current log sequence           85

SQL> conn lunar/lunar

Connected.

SQL> select * from test;

 

         F

----------

        22

        23

       100

       111

 

SQL>