日志組狀态及丢失的處理

--日志組狀态及丢失的處理

在任何給定時間，重做日志組的狀态都會是以下值之一：

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

再執行一緻性關閉資料庫或者重建立立日志組徹底解決。