--日志组状态及丢失的处理
在任何给定时间,重做日志组的状态都会是以下值之一:
CURRENT:LGWR当前正在向该重做日志组写入重做数据。
ACTIVE:不再向该重做日志组写入数据,但是恢复实例时仍然需要它。
INACTIVE:不再向该重做日志组写入数据,且恢复实例时也不再需要它。
UNUSED:未被使用的日志组。
清除日志文件:
alter database clear logfile ... 当日志文件不需要实例恢复也不需要介质恢复也不需要归档
alter database clear unarchived logfile ... 需要归档
在任何给定时间,重做日志组的状态都会是以下值之一:
CURRENT:LGWR当前正在向该重做日志组写入重做数据。
ACTIVE:不再向该重做日志组写入数据,但是恢复实例时仍然需要它。
INACTIVE:不再向该重做日志组写入数据,且恢复实例时也不再需要它。
UNUSED:未被使用的日志组。
清除日志文件:
alter database clear logfile ... 当日志文件不需要实例恢复也不需要介质恢复也不需要归档
alter database clear unarchived logfile ... 需要归档
alter database clear unarchived logfile ... unrecoverable datafile 需要归档需要恢复
--10g,11g,12c[cdb]
SYS@PROD2> select group#,members from v$log;
GROUP# MEMBERS
---------- ----------
1 1
2 1
3 1
SYS@PROD2> select member from v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD2/redo03.log
/u01/app/oracle/oradata/PROD2/redo02.log
/u01/app/oracle/oradata/PROD2/redo01.log
SYS@PROD2> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 1 INACTIVE
--模拟inactive日志文件丢失
SYS@PROD2> ho rm /u01/app/oracle/oradata/PROD2/redo02.log
SYS@PROD2> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 1 INACTIVE
SYS@PROD2> ho ls /u01/app/oracle/oradata/PROD2/redo02.log
ls: /u01/app/oracle/oradata/PROD2/redo02.log: No such file or directory
SYS@PROD2> alter database clear logfile group 2;
Database altered.
SYS@PROD2> ho ls /u01/app/oracle/oradata/PROD2/redo02.log
/u01/app/oracle/oradata/PROD2/redo02.log
SYS@PROD2> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 UNUSED
3 1 INACTIVE
--模拟active日志文件丢失
SYS@PROD2> update scott.emp set sal=sal+1;
14 rows updated.
SYS@PROD2> commit;
Commit complete.
SYS@PROD2> alter system switch logfile;
System altered.
SYS@PROD2> select group#,members,status from v$log; --当前组1为ACTIVE
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 CURRENT
3 1 INACTIVE
SYS@PROD2> ho rm /u01/app/oracle/oradata/PROD2/redo01.log --删除组1
SYS@PROD2> alter database clear logfile group 1; --不能直接清除,报错说组1是被需要用于实例恢复
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance PROD2 (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD2/redo01.log'
SYS@PROD2> alter system checkpoint; --触发检查点
System altered.
SYS@PROD2> select group#,members,status from v$log; --再次查看组1更改
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 CURRENT
3 1 INACTIVE
SYS@PROD2> alter database clear logfile group 1; --成功清除
Database altered.
如果清不掉则alter database clear unarchived logfile group 1;
--模拟current日志文件丢失
SYS@PROD2> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 UNUSED
2 1 CURRENT
3 1 INACTIVE
SYS@PROD2> select member from v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD2/redo03.log
/u01/app/oracle/oradata/PROD2/redo02.log
/u01/app/oracle/oradata/PROD2/redo01.log
SYS@PROD2> ho rm /u01/app/oracle/oradata/PROD2/redo02.log
首先要检查其他文件组有没有存在ACTIVE/INACTIVE状态,如果存在先通过前面步骤解决。
SYS@PROD2> alter system switch logfile;
System altered.
SYS@PROD2> select group#,members,status from v$log; --组2变为ACTIVE
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 ACTIVE
3 1 INACTIVE
SYS@PROD2> alter system checkpoint;
System altered.
SYS@PROD2> select group#,members,status from v$log; --组2变为INACTIVE
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 1 INACTIVE
SYS@PROD2> alter database clear logfile group 2; --清除失败需要归档
alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of instance PROD2 (thread 1) needs to be archived
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD2/redo02.log'
SYS@PROD2> alter database clear unarchived logfile group 2;
Database altered.
SYS@PROD2> select group#,members,status from v$log; --清除成功
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 UNUSED
3 1 INACTIVE
SYS@PROD2> alter system switch logfile; --切换至少3次都成功则表示处理成功
System altered.
SYS@PROD2> /
System altered.
SYS@PROD2> /
System altered.
如果切换hang住,则:
SYS@PROD2> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 INACTIVE
3 1 CURRENT
SYS@PROD2> alter system archive log all; --此处实验不需要所以报错
alter system archive log all
*
ERROR at line 1:
ORA-00271: there are no logs that need archiving
再执行一致性关闭数据库或者重新建立日志组彻底解决。