任務：增加日誌組數至4組，且每組日誌成員大小為50M，每組2個成員。

--檢視日誌成員路徑及日誌組大小、狀態

SQL> desc v$log

Name Null? Type

----------------------------------------- -------- ----------------------------

GROUP# NUMBER

THREAD# NUMBER

SEQUENCE# NUMBER

BYTES NUMBER

BLOCKSIZE NUMBER

MEMBERS NUMBER

ARCHIVED VARCHAR2(3)

STATUS VARCHAR2(16)

FIRST_CHANGE# NUMBER

FIRST_TIME DATE

NEXT_CHANGE# NUMBER

NEXT_TIME DATE

SQL> select GROUP#,BYTES/1024/1024 m,MEMBERS from v$log;

GROUP# M MEMBERS

---------- ---------- -------------------------------------------

1 50 1

2 50 1

3 50 1

SQL> desc v$logfile

Name Null? Type

----------------------------------------- -------- ----------------------------

GROUP# NUMBER

STATUS VARCHAR2(7)

TYPE VARCHAR2(7)

MEMBER VARCHAR2(513)

IS_RECOVERY_DEST_FILE VARCHAR2(3)

SQL> col MEMBER for a50

SQL> select GROUP#,MEMBER from v$logfile;

GROUP# MEMBER

---------- --------------------------------------------------

3 /u01/app/oracle/oradata/ORA11GR2/redo03.log

2 /u01/app/oracle/oradata/ORA11GR2/redo02.log

1 /u01/app/oracle/oradata/ORA11GR2/redo01.log

--增加group 4,group 5,group 6,group 7四組日誌

SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/ORA11GR2/redo4_a.log','/u01/app/oracle/oradata/ORA11GR2/redo4_b.log') size 50M;

Database altered.

SQL> alter database add logfile group 5('/u01/app/oracle/oradata/ORA11GR2/redo5_a.log','/u01/app/oracle/oradata/ORA11GR2/redo5_b.log') size 50M;

Database altered.

SQL> alter database add logfile group 6('/u01/app/oracle/oradata/ORA11GR2/redo6_a.log','/u01/app/oracle/oradata/ORA11GR2/redo6_b.log') size 50M;

Database altered.

SQL> alter database add logfile group 7('/u01/app/oracle/oradata/ORA11GR2/redo7_a.log','/u01/app/oracle/oradata/ORA11GR2/redo7_b.log') size 50M;

Database altered.

--檢視日誌組狀態

SQL> select group#,member from v$logfile order by 1;

GROUP# MEMBER

---------- --------------------------------------------------

1 /u01/app/oracle/oradata/ORA11GR2/redo01.log

2 /u01/app/oracle/oradata/ORA11GR2/redo02.log

3 /u01/app/oracle/oradata/ORA11GR2/redo03.log

4 /u01/app/oracle/oradata/ORA11GR2/redo4_a.log

4 /u01/app/oracle/oradata/ORA11GR2/redo4_b.log

5 /u01/app/oracle/oradata/ORA11GR2/redo5_a.log

5 /u01/app/oracle/oradata/ORA11GR2/redo5_b.log

6 /u01/app/oracle/oradata/ORA11GR2/redo6_a.log

6 /u01/app/oracle/oradata/ORA11GR2/redo6_b.log

7 /u01/app/oracle/oradata/ORA11GR2/redo7_a.log

7 /u01/app/oracle/oradata/ORA11GR2/redo7_b.log

11 rows selected.

SQL> select GROUP#,BYTES/1024/1024 m,MEMBERS,STATUS from v$log;

GROUP# M MEMBERS STATUS

---------- ---------- ---------- --------------------------------------------

1 50 1 INACTIVE

2 50 1 CURRENT

3 50 1 INACTIVE

4 50 2 UNUSED

5 50 2 UNUSED

6 50 2 UNUSED

7 50 2 UNUSED

7 rows selected.

--切換日誌，檢視日誌組狀態

SQL> alter system switch logfile;

System altered.

SQL> select GROUP#,BYTES/1024/1024 m,MEMBERS,STATUS from v$log;

GROUP# M MEMBERS STATUS

---------- ---------- ---------- ----------------

1 50 1 INACTIVE

2 50 1 ACTIVE

3 50 1 INACTIVE

4 50 2 CURRENT

5 50 2 UNUSED

6 50 2 UNUSED

7 50 2 UNUSED

7 rows selected.

（switch logfile，當啟動歸檔時則將前面的檔案歸檔，應用下一個日誌組寫資料；當為非歸檔時，則也應用下一個日誌組寫資料，或者迴圈覆寫）

--手工執行檢查點事件

SQL> alter system checkpoint;

System altered.

SQL> select GROUP#,BYTES/1024/1024 m,MEMBERS,STATUS from v$log;

GROUP# M MEMBERS STATUS

---------- ---------- ---------- ----------------

1 50 1 INACTIVE

2 50 1 INACTIVE

3 50 1 INACTIVE

4 50 2 CURRENT

5 50 2 UNUSED

6 50 2 UNUSED

7 50 2 UNUSED

7 rows selected.

手工觸發檢查點就是將先前的使用的日誌active（可能在例項恢復，報告inactive）轉到inactive，然後將更新資料檔案、控制檔案，使資料檔案、控制檔案及redo日誌三者一致性（即更新SCN一致）

--刪除group 1,group 2,group 3

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select group#,bytes/1024/1024 m,members,status from v$log;

GROUP# M MEMBERS STATUS

---------- ---------- ---------- ----------------

4 50 2 CURRENT

5 50 2 UNUSED

6 50 2 UNUSED

7 50 2 UNUSED

說明：

日誌組的四種狀態：

unused:表明該日誌組從來沒有被使用過，我們可以看到重建的日誌組就是這個狀態。

current:是oracle當前正在使用的日誌組，即後臺程式LGWR正在寫的日誌組。

active:當發生日誌切換的時候，狀態為current的日誌組就會變成active狀態，表明記錄在這個日誌組的事務提交所造成的資料塊改變，還沒有完全從database buffer cache 寫入到data file中。當資料庫處於archivelog mode時，ARCn就會歸檔這個日誌組。所以處在這個狀態的日誌組是不允許被覆蓋的。崩潰恢復需要該狀態，可用於塊恢復，可能歸檔也可能未歸檔。

inactive:處於這個狀態的日誌組，表明記錄在這個日誌組的事務提交所造成的資料塊的改變，已經完全從database buffer cache寫入到data file中了，例項恢復不再需要這個狀態的日誌組了，所以這個日誌組是可以被覆蓋的。

【儲存管理】日誌管理

相關文章