一次redolog丢失的恢复
接到同事的电话，某省的一个用于监控的siteview数据库启动不了了，登录后检查alertlog发现：
Completed first pass scan
9868 redo blocks read, 655 data blocks need recovery
Thu Apr 19 16:52:42 2007
Started recovery at
Thread 1: logseq 700, block 194931, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 3 Seq 700 Reading mem 0
Mem# 0 errs 0: C:\ORACLE\ORADATA\IMALLSV\REDO03.LOG
Thu Apr 19 16:52:43 2007
Ended recovery at
Thread 1: logseq 700, block 204799, scn 0.147914177
655 data blocks read, 655 data blocks written, 9868 redo blocks read
Crash recovery completed successfully
Thu Apr 19 16:52:43 2007
Errors in file c:\oracle\admin\imallsv\bdump\imallsv_lgwr_4180.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\IMALLSV\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Thu Apr 19 16:52:43 2007
Errors in file c:\oracle\admin\imallsv\bdump\imallsv_lgwr_4180.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\IMALLSV\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
ORA-313 signalled during: alter database open...
9868 redo blocks read, 655 data blocks need recovery
Thu Apr 19 16:52:42 2007
Started recovery at
Thread 1: logseq 700, block 194931, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 3 Seq 700 Reading mem 0
Mem# 0 errs 0: C:\ORACLE\ORADATA\IMALLSV\REDO03.LOG
Thu Apr 19 16:52:43 2007
Ended recovery at
Thread 1: logseq 700, block 204799, scn 0.147914177
655 data blocks read, 655 data blocks written, 9868 redo blocks read
Crash recovery completed successfully
Thu Apr 19 16:52:43 2007
Errors in file c:\oracle\admin\imallsv\bdump\imallsv_lgwr_4180.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\IMALLSV\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Thu Apr 19 16:52:43 2007
Errors in file c:\oracle\admin\imallsv\bdump\imallsv_lgwr_4180.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\IMALLSV\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
ORA-313 signalled during: alter database open...
原来是redolog被干掉了，其实对于redolog的丢失，如果丢失的不是active或者current（这个在win中一般不会被删除，因为使用时被锁定），我们可以用以下的方式恢复：
C:\Documents and Settings\Administrator>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 4月 19 17:05:03 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
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 immediate
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 487660924 bytes
Fixed Size 454012 bytes
Variable Size 209715200 bytes
Database Buffers 276824064 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
1 1 699 104857600 1 NO INACTIVE
147462354 17-4月 -07
2 1 0 104857600 1 NO UNUSED
0 16-4月 -07
3 1 700 104857600 1 NO INVALIDATED
147678398 17-4月 -07
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR 位于第 1 行:
ORA-01139: RESETLOGS 选项仅在不完全数据库恢复后有效
SQL> recover database until cancel;
完成介质恢复。
SQL> alter database open resetlogs;
数据库已更改。
SQL>
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 487660924 bytes
Fixed Size 454012 bytes
Variable Size 209715200 bytes
Database Buffers 276824064 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 4月 19 17:05:03 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
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 immediate
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 487660924 bytes
Fixed Size 454012 bytes
Variable Size 209715200 bytes
Database Buffers 276824064 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
1 1 699 104857600 1 NO INACTIVE
147462354 17-4月 -07
2 1 0 104857600 1 NO UNUSED
0 16-4月 -07
3 1 700 104857600 1 NO INVALIDATED
147678398 17-4月 -07
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR 位于第 1 行:
ORA-01139: RESETLOGS 选项仅在不完全数据库恢复后有效
SQL> recover database until cancel;
完成介质恢复。
SQL> alter database open resetlogs;
数据库已更改。
SQL>
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 487660924 bytes
Fixed Size 454012 bytes
Variable Size 209715200 bytes
Database Buffers 276824064 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
至此，恢复完成。^_^
· 【文章发布信息】发表于： 2008-04-18 @ 17:48:41 · ||分类： ..experience, Working case
CopyRight ©
匿名 于 2008-06-12 @ 17:06:24 留言 ：
应该在mount状态下，把被删除的日志组用alter database drop logfile group删除吧。
然后在open后，用alter database add logfile group添加上吧。