日志组状态及丢失的处理

--日志组状态及丢失的处理

在任何给定时间，重做日志组的状态都会是以下值之一：

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]

[email protected]> select group#,members from v$log;

    GROUP#    MEMBERS
---------- ----------
	 1	    1
	 2	    1
	 3	    1

[email protected]> 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

[email protected]> select group#,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
	 1	    1 CURRENT
	 2	    1 INACTIVE
	 3	    1 INACTIVE
           

--模拟inactive日志文件丢失	 
[email protected]> ho rm /u01/app/oracle/oradata/PROD2/redo02.log

[email protected]> select group#,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
	 1	    1 CURRENT
	 2	    1 INACTIVE
	 3	    1 INACTIVE

[email protected]> ho ls /u01/app/oracle/oradata/PROD2/redo02.log
ls: /u01/app/oracle/oradata/PROD2/redo02.log: No such file or directory

[email protected]> alter database clear logfile group 2;

Database altered.

[email protected]> ho ls /u01/app/oracle/oradata/PROD2/redo02.log
/u01/app/oracle/oradata/PROD2/redo02.log

[email protected]> select group#,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
	 1	    1 CURRENT
	 2	    1 UNUSED
	 3	    1 INACTIVE
           

--模拟active日志文件丢失
[email protected]> update scott.emp set sal=sal+1;

14 rows updated.

[email protected]> commit;

Commit complete.

[email protected]> alter system switch logfile;  

System altered.

[email protected]> select group#,members,status from v$log;  --当前组1为ACTIVE

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
	 1	    1 ACTIVE
	 2	    1 CURRENT
	 3	    1 INACTIVE
	 
[email protected]> ho rm /u01/app/oracle/oradata/PROD2/redo01.log  --删除组1

[email protected]> 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'


[email protected]> alter system checkpoint;  --触发检查点

System altered.

[email protected]> select group#,members,status from v$log;  --再次查看组1更改

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
	 1	    1 INACTIVE
	 2	    1 CURRENT
	 3	    1 INACTIVE

[email protected]> alter database clear logfile group 1;  --成功清除

Database altered.

如果清不掉则alter database clear unarchived logfile group 1;
           

--模拟current日志文件丢失
[email protected]> select group#,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
	 1	    1 UNUSED
	 2	    1 CURRENT
	 3	    1 INACTIVE

[email protected]> 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

[email protected]> ho rm /u01/app/oracle/oradata/PROD2/redo02.log
首先要检查其他文件组有没有存在ACTIVE/INACTIVE状态，如果存在先通过前面步骤解决。

[email protected]> alter system switch logfile;

System altered.

[email protected]> select group#,members,status from v$log;  --组2变为ACTIVE

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
	 1	    1 CURRENT
	 2	    1 ACTIVE
	 3	    1 INACTIVE

[email protected]> alter system checkpoint;

System altered.

[email protected]> select group#,members,status from v$log;  --组2变为INACTIVE

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
	 1	    1 CURRENT
	 2	    1 INACTIVE
	 3	    1 INACTIVE

[email protected]> 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'


[email protected]> alter database clear unarchived logfile group 2;

Database altered.

[email protected]> select group#,members,status from v$log;  --清除成功

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
	 1	    1 CURRENT
	 2	    1 UNUSED
	 3	    1 INACTIVE

[email protected]> alter system switch logfile;  --切换至少3次都成功则表示处理成功

System altered.

[email protected]> /

System altered.

[email protected]> /

System altered.

如果切换hang住，则：

[email protected]> select group#,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
	 1	    1 INACTIVE
	 2	    1 INACTIVE
	 3	    1 CURRENT

[email protected]> alter system archive log all;  --此处实验不需要所以报错
alter system archive log all
*
ERROR at line 1:
ORA-00271: there are no logs that need archiving

再执行一致性关闭数据库或者重新建立日志组彻底解决。