2 如果LGWR 至少能够访问一个组内成员,对组内可访问成员的写入将照常进行,LGWR 忽略组内的不可用成员。如果该组不活动,即检查点已完成,那么丢弃和添加一个新的联机日志成员就可以解决问题,否则如果该组是当前活动的日志组,则必须首先强制日志切换。
SQL> SELECT group#, sequence#, bytes, members, status 2 FROM V$LOG; GROUP# SEQUENCE# BYTES MEMBERS STATUS ---------- ---------- ---------- ---------- ---------------- 1 411 1048576 2 INACTIVE 2 412 1048576 2 INACTIVE 3 413 1048576 2 INACTIVE 5 414 1048576 2 CURRENT SQL> select * from v$logfile 2 ORDER BY GROUP#;
GROUP# STATUS MEMBER ---------- ------- ---------------------------------------------------------- 1 D:/ORACLE1/ORA81/ORADATA/TEST/REDO01.LOG 1 E:/ORACLE1/ORA81/ORADATA/TEST/REDO04.LOG 2 D:/ORACLE1/ORA81/ORADATA/TEST/REDO02.LOG 2 E:/ORACLE1/ORA81/ORADATA/TEST/REDO05.LOG 3 D:/ORACLE1/ORA81/ORADATA/TEST/REDO03.LOG 3 E:/ORACLE1/ORA81/ORADATA/TEST/REDO06.LOG 5 E:/ORACLE1/ORA81/ORADATA/TEST/REDO07.LOG 5 E:/ORACLE1/ORA81/ORADATA/TEST/REDO08.LOG 8 rows selected SQL>
为了模拟日志组中有一个成员损坏的情况,我们打开文本编辑器,并且破坏E:/ORACLE1/ORA81/ORADATA/TEST/REDO07.LOG文件,然后,我们看到:
SQL> select * from v$logfile; GROUP# STATUS MEMBER ---------- ------- ------------------------------------------------------- 1 D:/ORACLE1/ORA81/ORADATA/TEST/REDO01.LOG 2 D:/ORACLE1/ORA81/ORADATA/TEST/REDO02.LOG 3 D:/ORACLE1/ORA81/ORADATA/TEST/REDO03.LOG 1 E:/ORACLE1/ORA81/ORADATA/TEST/REDO04.LOG 2 E:/ORACLE1/ORA81/ORADATA/TEST/REDO05.LOG 3 E:/ORACLE1/ORA81/ORADATA/TEST/REDO06.LOG 5 INVALID E:/ORACLE1/ORA81/ORADATA/TEST/REDO07.LOG 5 E:/ORACLE1/ORA81/ORADATA/TEST/REDO08.LOG 8 rows selected SQL> |
但是这时候数据还可以忽略这个损坏的文件而正常使用。要修复这个文件,我们需要做:
SQL> shutdown 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> host Microsoft Windows 2000 [Version 5.00.2195] (C) 版权所有 1985-2000 Microsoft Corp. E:/>COPY E:/ORACLE1/ORA81/ORADATA/TEST/REDO08.LOG E:/ORACLE1/ORA81/ORADATA/TEST/ REDO07.LOG 改写 E:/ORACLE1/ORA81/ORADATA/TEST/REDO07.LOG 吗? (Yes/No/All): YES 已复制 1 个文件。
上面这一步需要注意的是,如果新的日志文件的位置或文件名称需要改变(如,介质失效),则在数据库加载(Startup Mount)后需要对这个改变位置或名称的日志文件重命名,然后再打开数据,具体过程参见“重新定位或者重命名联机重做日志文件”。
E:/>exit SQL> startup ORACLE 例程已经启动。 Total System Global Area 36214812 bytes Fixed Size 75804 bytes Variable Size 19283968 bytes Database Buffers 16777216 bytes Redo Buffers 77824 bytes 数据库装载完毕。 数据库已经打开。 SQL> select * from v$logfile; GROUP# STATUS MEMBER ---------- ------- ------------------------------------------------------------ 1 D:/ORACLE1/ORA81/ORADATA/TEST/REDO01.LOG 2 D:/ORACLE1/ORA81/ORADATA/TEST/REDO02.LOG 3 D:/ORACLE1/ORA81/ORADATA/TEST/REDO03.LOG 1 E:/ORACLE1/ORA81/ORADATA/TEST/REDO04.LOG 2 E:/ORACLE1/ORA81/ORADATA/TEST/REDO05.LOG 3 E:/ORACLE1/ORA81/ORADATA/TEST/REDO06.LOG 5 UNKNOWN E:/ORACLE1/ORA81/ORADATA/TEST/REDO07.LOG 5 STALE E:/ORACLE1/ORA81/ORADATA/TEST/REDO08.LOG 8 rows selected SQL> alter system switch logfile; System altered SQL> select * from v$logfile; GROUP# STATUS MEMBER ---------- ------- ------------------------------------------------------- 1 D:/ORACLE1/ORA81/ORADATA/TEST/REDO01.LOG 2 D:/ORACLE1/ORA81/ORADATA/TEST/REDO02.LOG 3 D:/ORACLE1/ORA81/ORADATA/TEST/REDO03.LOG 1 E:/ORACLE1/ORA81/ORADATA/TEST/REDO04.LOG 2 E:/ORACLE1/ORA81/ORADATA/TEST/REDO05.LOG 3 E:/ORACLE1/ORA81/ORADATA/TEST/REDO06.LOG 5 E:/ORACLE1/ORA81/ORADATA/TEST/REDO07.LOG 5 E:/ORACLE1/ORA81/ORADATA/TEST/REDO08.LOG 8 rows selected SQL> 然后,看到一切正常了,则完全关闭数据(Normal),进行一个冷备份。 上面关于日志文件损坏和修复的相关部分会被记录在Alert.log文件中。 |
2 如果在日志切换时LGWR 无法访问下一个组的所有成员或者损坏的日志文件时改组中日志成员,则该实例关闭。如果组不活动,那么丢弃和添加一个新的日志组就可解决问题;如果活动,数据库可能需要从联机重做日志文件残留物进行介质恢复。
SQL> ALTER DATABASE DROP LOGFILE MEMBER 'E:/ORACLE1/ORA81/ORADATA/TEST/REDO08.LOG'; Database altered SQL> select * from v$logfile; GROUP# STATUS MEMBER ---------- ------- ----------------------------------------------------------- 1 D:/ORACLE1/ORA81/ORADATA/TEST/REDO01.LOG 2 D:/ORACLE1/ORA81/ORADATA/TEST/REDO02.LOG 3 D:/ORACLE1/ORA81/ORADATA/TEST/REDO03.LOG 1 E:/ORACLE1/ORA81/ORADATA/TEST/REDO04.LOG 2 E:/ORACLE1/ORA81/ORADATA/TEST/REDO05.LOG 3 E:/ORACLE1/ORA81/ORADATA/TEST/REDO06.LOG 5 E:/ORACLE1/ORA81/ORADATA/TEST/REDO07.LOG 7 rows selected SQL> |
为了模拟日志组中有一个成员损坏的情况,我们打开文本编辑器,并且破坏E:/ORACLE1/ORA81/ORADATA/TEST/REDO07.LOG文件,然后,我们看到:
SQL> ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE ORA-00313: 无法打开日志组 (线程 ) 的成员 SQL> STARTUP MOUNT ORACLE 例程已经启动。 Total System Global Area 36214812 bytes Fixed Size 75804 bytes Variable Size 19283968 bytes Database Buffers 16777216 bytes Redo Buffers 77824 bytes 数据库装载完毕。 SQL> ALTER DATABASE 2 DROP LOGFILE GROUP 5; 数据库已更改。 SQL> ALTER DATABASE OPEN; 数据库已更改。 SQL> select * from v$logfile; GROUP# STATUS MEMBER ---------- ------- ------------------------------------------------- 1 D:/ORACLE1/ORA81/ORADATA/TEST/REDO01.LOG 2 D:/ORACLE1/ORA81/ORADATA/TEST/REDO02.LOG 3 STALE D:/ORACLE1/ORA81/ORADATA/TEST/REDO03.LOG 1 E:/ORACLE1/ORA81/ORADATA/TEST/REDO04.LOG 2 E:/ORACLE1/ORA81/ORADATA/TEST/REDO05.LOG 3 STALE E:/ORACLE1/ORA81/ORADATA/TEST/REDO06.LOG 6 rows selected SQL> 然后,看到一切正常了,则完全关闭数据(Normal),进行一个冷备份。 上面关于日志文件损坏和修复的相关部分会被记录在Alert.log文件中。 |
在归档模式下,我们需要用alter database clear logfile group来恢复数据库:
SQL> select * from v$logfile; GROUP# STATUS MEMBER ---------- ------- -------------------------------------------------------- 1 D:/ORACLE1/ORA81/ORADATA/TEST/REDO01.LOG 2 D:/ORACLE1/ORA81/ORADATA/TEST/REDO02.LOG 3 D:/ORACLE1/ORA81/ORADATA/TEST/REDO03.LOG 1 E:/ORACLE1/ORA81/ORADATA/TEST/REDO04.LOG 2 E:/ORACLE1/ORA81/ORADATA/TEST/REDO05.LOG 3 E:/ORACLE1/ORA81/ORADATA/TEST/REDO06.LOG 4 F:/ORACLE1/ORA81/ORADATA/TEST/REDO07.LOG 4 F:/ORACLE1/ORA81/ORADATA/TEST/REDO08.LOG 8 rows selected
|
我们用GROUP4作试验,首先确定它不是CURRENT GROUP:
SQL> SELECT group#, sequence#, bytes, members, status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS ---------- ---------- ---------- ---------- ---------------- 1 440 1048576 2 INACTIVE 2 442 1048576 2 CURRENT 3 439 1048576 2 INACTIVE 4 441 1048576 2 INACTIVE
|
然后让GRUP4只有一个MEMBER(DROP掉GROUP4中其余的MEMBER):
SQL> alter database drop logfile member 'F:/ORACLE1/ORA81/ORADATA/TEST/REDO08.LOG'; Database altered SQL> select * from v$logfile; GROUP# STATUS MEMBER ---------- ------- ------------------------------------------------------------ 1 D:/ORACLE1/ORA81/ORADATA/TEST/REDO01.LOG 2 D:/ORACLE1/ORA81/ORADATA/TEST/REDO02.LOG 3 D:/ORACLE1/ORA81/ORADATA/TEST/REDO03.LOG 1 E:/ORACLE1/ORA81/ORADATA/TEST/REDO04.LOG 2 E:/ORACLE1/ORA81/ORADATA/TEST/REDO05.LOG 3 E:/ORACLE1/ORA81/ORADATA/TEST/REDO06.LOG 4 F:/ORACLE1/ORA81/ORADATA/TEST/REDO07.LOG 7 rows selected SQL> |
现在GROUP 4中只有一个MEMBER,并且不是CURRENT,我们损坏这个GROUP(F:/ORACLE1/ORA81/ORADATA/TEST/REDO07.LOG),然后,当SWITCH到这个损坏的组以后,数据库会HANG或者严重的话会CRASH,这时候我们可能收到下面的错误: