Redolog日志文件删除恢复

Redolog日志文件删除恢复

1.查看当前重做日志组及成员个数状态

SQL> select GROUP#,MEMBERS,STATUS from v$log;

    GROUP#    MEMBERS STATUS

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

         1          1 INACTIVE

         2          1 CURRENT

         3          1 INACTIVE

SQL>

2.查看日志组成员：

SQL> select GROUP#,MEMBER from v$logfile;

    GROUP# MEMBER

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

         3 D:\APP\WWANG\ORADATA\MDSP\REDO03.LOG

         2 D:\APP\WWANG\ORADATA\MDSP\REDO02.LOG

         1 D:\APP\WWANG\ORADATA\MDSP\REDO01.LOG

3.停掉数据库并删除非当前组日志组1的成员REDO01.LOG

4.启动数据库startup 报错

SQL> startup

ORACLE 例程已经启动。

Total System Global Area  326414336 bytes

Fixed Size                  1374388 bytes

Variable Size             260048716 bytes

Database Buffers           58720256 bytes

Redo Buffers                6270976 bytes

数据库装载完毕。

ORA-03113: 通信通道的文件结尾

进程 ID: 3024

会话 ID: 9 序列号: 3

相应的alert日志

ALTER DATABASE   MOUNT

Tue Jun 05 22:20:51 2012

MMNL started with pid=16, OS id=660

Successful mount of redo thread 1, with mount id 666894147

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE   MOUNT

Tue Jun 05 22:20:57 2012

ALTER DATABASE OPEN

Errors in file d:\app\wwang\diag\rdbms\mdsp\mdsp\trace\mdsp_lgwr_2340.trc:

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

ORA-00312: ???? 1 ?? 1: 'D:\APP\WWANG\ORADATA\MDSP\REDO01.LOG'

ORA-27041: ??????

OSD-04002: 无法打开文件

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

Errors in file d:\app\wwang\diag\rdbms\mdsp\mdsp\trace\mdsp_lgwr_2340.trc:

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

ORA-00312: ???? 1 ?? 1: 'D:\APP\WWANG\ORADATA\MDSP\REDO01.LOG'

ORA-27041: ??????

OSD-04002: 无法打开文件

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

Errors in file d:\app\wwang\diag\rdbms\mdsp\mdsp\trace\mdsp_ora_3024.trc:

ORA-00313: 鏃犳硶鎵撳紑鏃ュ織缁?1 (鐢ㄤ簬绾跨▼ ) 鐨勬垚鍛?

ORA-00312: 鑱旀満鏃ュ織 1 绾跨▼ 1: 'D:\APP\WWANG\ORADATA\MDSP\REDO01.LOG'

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

Instance terminated by USER, pid = 3024

5．从alert日志文件可以看出日志文件D:\APP\WWANG\ORADATA\MDSP\REDO01.LOG'不存了，被删除了

6.把数据库启动到mount状态恢复日志文件

SQL> startup mount

ORACLE 例程已经启动。

Total System Global Area  326414336 bytes

Fixed Size                  1374388 bytes

Variable Size             260048716 bytes

Database Buffers           58720256 bytes

Redo Buffers                6270976 bytes

数据库装载完毕。

恢复日志文件：

SQL> alter database clear logfile 'D:\APP\WWANG\ORADATA\MDSP\REDO01.LOG';

数据库已更改。

SQL> alter database open;

SQL> select GROUP#,MEMBERS,STATUS from v$log;

    GROUP#    MEMBERS STATUS

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

         1          1 UNUSED

         2          1 CURRENT

         3          1 INACTIVE