【REDO】重做日誌檔案（redo log files）管理（增，刪，改，查，切）
彙總整理一下有關重做日誌檔案（redo log files）管理相關的操作（增，刪，改，查，切）。供參考。
1.當前日誌相關資訊
sys@ora11g> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- --------- ------------- ---------
1 1 10 209715200 1 YES INACTIVE 461938 09-MAR-09
2 1 11 209715200 1 NO CURRENT 485885 09-MAR-09
3 1 9 209715200 1 YES INACTIVE 432636 04-MAR-09
sys@ora11g> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ---------------------------------------- ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO
2.新增重做日誌組
sys@ora11g> alter database add logfile group 4 ('/oracle/u02/oradata/ora11g/redo04_01.log','/oracle/u02/oradata/ora11g/redo04_02.log') size 50m;
Database altered.
sys@ora11g> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- --------- ------------- ---------
1 1 10 209715200 1 YES INACTIVE 461938 09-MAR-09
2 1 11 209715200 1 NO CURRENT 485885 09-MAR-09
3 1 9 209715200 1 YES INACTIVE 432636 04-MAR-09
4 1 0 52428800 2 YES UNUSED 0
sys@ora11g> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ---------------------------------------- ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO
3.新增日誌檔案
sys@ora11g> alter database add logfile member
'/oracle/u02/oradata/ora11g/redo01_02.log' to group 1,
'/oracle/u02/oradata/ora11g/redo02_02.log' to group 2,
'/oracle/u02/oradata/ora11g/redo03_02.log' to group 3;
Database altered.
sys@ora11g> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- --------- ------------- ---------
1 1 10 209715200 2 YES INACTIVE 461938 09-MAR-09
2 1 11 209715200 2 NO CURRENT 485885 09-MAR-09
3 1 9 209715200 2 YES INACTIVE 432636 04-MAR-09
4 1 0 52428800 2 YES UNUSED 0
sys@ora11g> select * from v$logfile order by 1;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ---------------------------------------- ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO
1 INVALID ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO
2 INVALID ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO
3 INVALID ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO
8 rows selected.
4.重新命名日誌成員
在重新命名日誌組成員之前新的目標必須已經存在。Oracle的sql命令只是把控制檔案中的內部指標指向新的日誌檔案。
1）關閉資料庫
sys@ora11g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
2）使用作業系統命令重新命名或移動日誌檔案
ora11g@RHEL53 /oracle/u02/oradata/ora11g$ mv redo01.log redo01_01.log
ora11g@RHEL53 /oracle/u02/oradata/ora11g$ mv redo02.log redo02_01.log
ora11g@RHEL53 /oracle/u02/oradata/ora11g$ mv redo03.log redo03_01.log
3）啟動資料庫例項到mount狀態，重新命名控制檔案中的日誌檔案成員。
NotConnected@> select * from v$logfile order by 1,4;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ------------------------------------------ ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO
1 ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO
8 rows selected.
NotConnected@> alter database rename file '/oracle/u02/oradata/ora11g/redo01.log' to '/oracle/u02/oradata/ora11g/redo01_01.log';
Database altered.
NotConnected@> alter database rename file '/oracle/u02/oradata/ora11g/redo02.log' to '/oracle/u02/oradata/ora11g/redo02_01.log';
Database altered.
NotConnected@> alter database rename file '/oracle/u02/oradata/ora11g/redo03.log' to '/oracle/u02/oradata/ora11g/redo03_01.log';
Database altered.
4）open資料庫，驗證結果
NotConnected@> alter database open;
Database altered.
sys@ora11g> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- -------- ------------- ---------
1 1 18 209715200 2 YES INACTIVE 486960 09-MAR-09
2 1 19 209715200 2 YES INACTIVE 486964 09-MAR-09
3 1 21 209715200 2 NO CURRENT 486973 09-MAR-09
4 1 20 52428800 2 YES INACTIVE 486968 09-MAR-09
sys@ora11g> select * from v$logfile order by 1,4;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ------------------------------------------ ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01_01.log NO
1 ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_01.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO
8 rows selected.
5）最後，不要忘記備份控制檔案
sys@ora11g> alter database backup controlfile to trace;
Database altered.
5.刪除一個非活動的重做日誌組的成員
sys@ora11g> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- -------- ------------- ---------
1 1 18 209715200 2 YES INACTIVE 486960 09-MAR-09
2 1 19 209715200 2 YES INACTIVE 486964 09-MAR-09
3 1 21 209715200 2 NO CURRENT 486973 09-MAR-09
4 1 20 52428800 2 YES INACTIVE 486968 09-MAR-09
sys@ora11g> alter database drop logfile member '/oracle/u02/oradata/ora11g/redo04_02.log';
Database altered.
sys@ora11g> !ls -l /oracle/u02/oradata/ora11g/redo04_02.log
-rw-r----- 1 oracle oinstall 52429312 Mar 9 16:28 /oracle/u02/oradata/ora11g/redo04_02.log
sys@ora11g> !rm -f /oracle/u02/oradata/ora11g/redo04_02.log
sys@ora11g> select * from v$logfile order by 1,4;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ------------------------------------------ ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01_01.log NO
1 ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_01.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO
7 rows selected.
6.刪除一個非活動的重做日誌組
sys@ora11g> alter database drop logfile group 4;
Database altered.
sys@ora11g> !rm -f /oracle/u02/oradata/ora11g/redo04_01.log
sys@ora11g> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ------------------------------------------ ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01_01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_01.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_01.log NO
1 ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO
6 rows selected.
7.強制切換日誌
sys@ora11g> alter system switch logfile;
System altered.
sys@ora11g> alter system archive log current;
System altered.
8.小結
1）日誌檔案非常重要，當多路複用重做日誌檔案時，應該把一個組的成員儲存在不同的磁碟上。
2）在完成日誌檔案維護後一定要記得備份最新的控制檔案！
3）以上試驗是在11g環境下完成的，在10g環境中一樣適用。
Good luck.
secooler
09.03.09
-- The End --
1.當前日誌相關資訊
sys@ora11g> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- --------- ------------- ---------
1 1 10 209715200 1 YES INACTIVE 461938 09-MAR-09
2 1 11 209715200 1 NO CURRENT 485885 09-MAR-09
3 1 9 209715200 1 YES INACTIVE 432636 04-MAR-09
sys@ora11g> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ---------------------------------------- ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO
2.新增重做日誌組
sys@ora11g> alter database add logfile group 4 ('/oracle/u02/oradata/ora11g/redo04_01.log','/oracle/u02/oradata/ora11g/redo04_02.log') size 50m;
Database altered.
sys@ora11g> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- --------- ------------- ---------
1 1 10 209715200 1 YES INACTIVE 461938 09-MAR-09
2 1 11 209715200 1 NO CURRENT 485885 09-MAR-09
3 1 9 209715200 1 YES INACTIVE 432636 04-MAR-09
4 1 0 52428800 2 YES UNUSED 0
sys@ora11g> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ---------------------------------------- ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO
3.新增日誌檔案
sys@ora11g> alter database add logfile member
'/oracle/u02/oradata/ora11g/redo01_02.log' to group 1,
'/oracle/u02/oradata/ora11g/redo02_02.log' to group 2,
'/oracle/u02/oradata/ora11g/redo03_02.log' to group 3;
Database altered.
sys@ora11g> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- --------- ------------- ---------
1 1 10 209715200 2 YES INACTIVE 461938 09-MAR-09
2 1 11 209715200 2 NO CURRENT 485885 09-MAR-09
3 1 9 209715200 2 YES INACTIVE 432636 04-MAR-09
4 1 0 52428800 2 YES UNUSED 0
sys@ora11g> select * from v$logfile order by 1;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ---------------------------------------- ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO
1 INVALID ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO
2 INVALID ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO
3 INVALID ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO
8 rows selected.
4.重新命名日誌成員
在重新命名日誌組成員之前新的目標必須已經存在。Oracle的sql命令只是把控制檔案中的內部指標指向新的日誌檔案。
1）關閉資料庫
sys@ora11g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
2）使用作業系統命令重新命名或移動日誌檔案
ora11g@RHEL53 /oracle/u02/oradata/ora11g$ mv redo01.log redo01_01.log
ora11g@RHEL53 /oracle/u02/oradata/ora11g$ mv redo02.log redo02_01.log
ora11g@RHEL53 /oracle/u02/oradata/ora11g$ mv redo03.log redo03_01.log
3）啟動資料庫例項到mount狀態，重新命名控制檔案中的日誌檔案成員。
NotConnected@> select * from v$logfile order by 1,4;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ------------------------------------------ ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO
1 ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO
8 rows selected.
NotConnected@> alter database rename file '/oracle/u02/oradata/ora11g/redo01.log' to '/oracle/u02/oradata/ora11g/redo01_01.log';
Database altered.
NotConnected@> alter database rename file '/oracle/u02/oradata/ora11g/redo02.log' to '/oracle/u02/oradata/ora11g/redo02_01.log';
Database altered.
NotConnected@> alter database rename file '/oracle/u02/oradata/ora11g/redo03.log' to '/oracle/u02/oradata/ora11g/redo03_01.log';
Database altered.
4）open資料庫，驗證結果
NotConnected@> alter database open;
Database altered.
sys@ora11g> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- -------- ------------- ---------
1 1 18 209715200 2 YES INACTIVE 486960 09-MAR-09
2 1 19 209715200 2 YES INACTIVE 486964 09-MAR-09
3 1 21 209715200 2 NO CURRENT 486973 09-MAR-09
4 1 20 52428800 2 YES INACTIVE 486968 09-MAR-09
sys@ora11g> select * from v$logfile order by 1,4;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ------------------------------------------ ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01_01.log NO
1 ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_01.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO
8 rows selected.
5）最後，不要忘記備份控制檔案
sys@ora11g> alter database backup controlfile to trace;
Database altered.
5.刪除一個非活動的重做日誌組的成員
sys@ora11g> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- -------- ------------- ---------
1 1 18 209715200 2 YES INACTIVE 486960 09-MAR-09
2 1 19 209715200 2 YES INACTIVE 486964 09-MAR-09
3 1 21 209715200 2 NO CURRENT 486973 09-MAR-09
4 1 20 52428800 2 YES INACTIVE 486968 09-MAR-09
sys@ora11g> alter database drop logfile member '/oracle/u02/oradata/ora11g/redo04_02.log';
Database altered.
sys@ora11g> !ls -l /oracle/u02/oradata/ora11g/redo04_02.log
-rw-r----- 1 oracle oinstall 52429312 Mar 9 16:28 /oracle/u02/oradata/ora11g/redo04_02.log
sys@ora11g> !rm -f /oracle/u02/oradata/ora11g/redo04_02.log
sys@ora11g> select * from v$logfile order by 1,4;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ------------------------------------------ ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01_01.log NO
1 ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_01.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO
7 rows selected.
6.刪除一個非活動的重做日誌組
sys@ora11g> alter database drop logfile group 4;
Database altered.
sys@ora11g> !rm -f /oracle/u02/oradata/ora11g/redo04_01.log
sys@ora11g> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ------------------------------------------ ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01_01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_01.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_01.log NO
1 ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO
6 rows selected.
7.強制切換日誌
sys@ora11g> alter system switch logfile;
System altered.
sys@ora11g> alter system archive log current;
System altered.
8.小結
1）日誌檔案非常重要，當多路複用重做日誌檔案時，應該把一個組的成員儲存在不同的磁碟上。
2）在完成日誌檔案維護後一定要記得備份最新的控制檔案！
3）以上試驗是在11g環境下完成的，在10g環境中一樣適用。
Good luck.
secooler
09.03.09
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-566453/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【REDO】刪除REDO LOG重做日誌組後需要手工刪除對應的日誌檔案
- redo重做日誌管理
- Oracle11g redo log 建立、新增、刪除（重做日誌組，重做日誌檔案）Oracle
- MySQL重做日誌（redo log）MySql
- 重做日誌（redo log）相關總結
- 【REDO】刪除聯機重做日誌檔案組的注意事項
- Archived Redo Logs歸檔重做日誌介紹及其優點Hive
- 【REDO】刪除聯機重做日誌檔案組成員的注意事項
- REDO日誌管理
- redo log日誌挖掘（二）
- redo log日誌挖掘（一）
- oracle檔案管理之 redo logOracle
- oracle聯機日誌檔案REDO LOGFILE簡述Oracle
- REDO日誌切換頻率
- Redo log檔案被刪除恢復
- 線上重建redo log日誌組
- redo日誌組資訊查詢
- Oracle Redo(重做日誌) 模擬故障和恢復Oracle Redo
- MySQL 日誌系統 redo log、binlogMySql
- Oracle調整redo log日誌大小Oracle
- redo日誌檔案學習筆記（一)筆記
- 修改online redo日誌檔案大小
- 增大redo log檔案大小
- 【Mysql】三大日誌 redo log、bin log、undo logMySql
- 【聽海日誌】之DATAGUARD新增redo log
- Oracle redo 日誌切換時間頻率Oracle Redo
- 【redo】日誌檔案的丟失解決方法
- 2 Day DBA-管理Oracle例項-管理聯機重做日誌-切換日誌檔案Oracle
- 12C關於CDB、PDB 日誌檔案redo log的總結
- online redo log 操作 -- 更改RAC資料庫日誌檔案大小資料庫
- redo日誌損壞
- MySQL 日誌 undo | redoMySql
- oracle redo 日誌刪除後的恢復Oracle Redo
- 檢查REDO日誌相關資訊並生成HTML檔案的指令碼HTML指令碼
- DB2 的事務日誌（redo log）DB2
- 檢視Oracle的redo日誌切換頻率Oracle
- 【恢復】Redo日誌檔案丟失的恢復
- mysql日誌：redo log、binlog、undo log 區別與作用MySql