模拟数据库redo日志丢失导致数据库宕机故障，数据库恢复方法

  

一、问题

启动数据库，失败，报ORA-03113错误

 

C:\Windows\System32>sqlplus sys/system as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 19 14:57:24 2015

 

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

 

Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area 2538741760 bytes

Fixed Size                  2257872 bytes

Variable Size             570428464 bytes

Database Buffers         1946157056 bytes

Redo Buffers               19898368 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 4036

Session ID: 5 Serial number: 5

 

 

SQL> select status from v$instance;

ERROR:

ORA-03114: not connected to ORACLE

 

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64

bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

C:\Windows\System32>sqlplus sys/system as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 19 14:58:30 2015

 

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

 

Connected to an idle instance.

 

SQL> select status from v$instance;

select status from v$instance

*

ERROR at line 1:

ORA-01034: ORACLE not available

Process ID: 0

Session ID: 0 Serial number: 0

 

 

SQL> shutdown immediate

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

 

 

 

二、检查alert日志，

 

Mon Oct 19 14:54:34 2015

Errors in file C:\APP\diag\rdbms\tangb\tangb\trace\tangb_lgwr_4812.trc:

ORA-00313: ??????? 1 (???? 1) ???

ORA-00312: ???? 1 ?? 1: 'C:\APP\ORADATA\TANGB\REDO01.LOG'

ORA-27041: ??????

OSD-04002: 无法打开文件

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

Errors in file C:\APP\diag\rdbms\tangb\tangb\trace\tangb_lgwr_4812.trc:

ORA-00313: ??????? 1 (???? 1) ???

ORA-00312: ???? 1 ?? 1: 'C:\APP\ORADATA\TANGB\REDO01.LOG'

ORA-27041: ??????

OSD-04002: 无法打开文件

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

Errors in file C:\APP\diag\rdbms\tangb\tangb\trace\tangb_ora_3564.trc:

ORA-00313: ??????? 1 (???? ) ???

ORA-00312: ???? 1 ?? 1: 'C:\APP\ORADATA\TANGB\REDO01.LOG'

USER (ospid: 3564): terminating the instance due to error 313

 

找不到redo日志文件，进到操作系统对应目录下，所有redo日志文件丢失。

 

三、检查redo日志状态

启动到mount状态检查redo日志状态

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 2538741760 bytes

Fixed Size                  2257872 bytes

Variable Size             570428464 bytes

Database Buffers         1946157056 bytes

Redo Buffers               19898368 bytes

Database mounted.

SQL> SELECT group#,member FROM v$logfile;

 

    GROUP#                 MEMBER

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

         3                 C:\APP\ORADATA\TANGB\REDO03.LOG

         2                 C:\APP\ORADATA\TANGB\REDO02.LOG

         1                 C:\APP\ORADATA\TANGB\REDO01.LOG

 

SQL> SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;

 

    GROUP#    MEMBERS    BYTE_MB STATUS

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

         1          1         50 INACTIVE

         3          1         50 CURRENT

         2          1         50 INACTIVE

其中group 1,2 redo日志文件已经归档，可以使用clear方法重建redo日志文件

 

四、使用clear方式恢复非current状态redo日志

#如未归档使用alter database clear unarchived logfile group 1; 重建

 

SQL> alter   database   clear   logfile   group   1;

 

Database altered.

 

SQL> alter   database   clear   logfile   group   2;

 

Database altered.

 

SQL> alter   database   clear   logfile   group   3;

alter   database   clear   logfile   group   3

*

ERROR at line 1:

ORA-00350: log 3 of instance tangb (thread 1) needs to be archived

ORA-00312: online log 3 thread 1: 'C:\APP\ORADATA\TANGB\REDO03.LOG'

 

 

由于group 3日志文件为current状态日志文件，使用普通clear方式不可行。

 

五、使用recover+resetlogs方式恢复current状态redo日志

下面采用recover方式中间redo日志文件

SQL> recover database until cancel;

Media recovery complete.

SQL> alter database open noresetlogs;

alter database open noresetlogs

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 4848

Session ID: 5 Serial number: 3

 

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64

bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

使用noresetlogs方式打开数据库不可行，下面使用resetlogs方式打开数据库

 

C:\Windows\System32>sqlplus sys/system as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 19 15:09:40 2015

 

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

 

Connected to an idle instance.

 

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 2538741760 bytes

Fixed Size                  2257872 bytes

Variable Size             570428464 bytes

Database Buffers         1946157056 bytes

Redo Buffers               19898368 bytes

Database mounted.

SQL> recover database until cancel;

Media recovery complete.

SQL> alter database open resetlogs;

 

Database altered.

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64

bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

六、数据库打开正常

 

C:\Windows\System32>sqlplus sys/system as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 19 15:13:43 2015

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;

 

    GROUP#    MEMBERS    BYTE_MB STATUS

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

         1          1         50 CURRENT

         2          1         50 UNUSED

         3          1         50 UNUSED

 

SQL>

 

检查redo日志状态，显示正常。

##################################################################################################################################

使用recover database until cancel可以轻松恢复或者说重新建立所有的redo，不再区分是否是当前日志；

Resetlogs其实就是根据控制文件让系统自动重新生成redo,如果noresetlog的话，就不会重新生成redo,缺少了文件，db自然无法启动

 

七、数据库备份

由于使用resetlogs方式打开数据库，数据库之前的备份已经失效，需要马上对数据库进行全备。

 

 

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

转载于:http://blog.itpub.net/532823/viewspace-2080372/