oracle redo log 操作和故障處理

peace72

oracle redo log 操作和故障處理
1.ADD LOG GROUP:
只增加一個(gè)日志成員的寫法，用此種方法以后一定要再至少創(chuàng)建一個(gè)日志成員：
ALTER DATABASE [database] ADD LOGFILE GROUP integer filespec SIZE size [REUSE] [,GROUP integer filespec SIZE size [REUSE] ...] ;
同時(shí)增加多個(gè)日志成員的寫法（注意多了個(gè)小括號(hào)）：
ALTER DATABASE [database] ADD LOGFILE GROUP integer ( filespec , filespec , filespec ...] ) SIZE size [REUSE];
說明：
--- filespec ： 包括地址的全文件名
--- 注意可以同時(shí)創(chuàng)建多個(gè)文件
--- REUSE: 表示如果已經(jīng)存在此文件就重用它！
eg:
alter database add logfile group 5 '/opt/oracle/oradata/dbtest/redo05_1.log' SIZE 10M;
alter database add logfile group 5 '/opt/oracle/oradata/dbtest/redo05_1.log' size 10m reuse;
alter database add logfile group 6 '/opt/oracle/oradata/dbtest/redo06_1.log' size 10m,
group 7 '/opt/oracle/oradata/dbtest/redo07_1.log' size 10m;
alter database add logfile group 8
('/opt/oracle/oradata/dbtest/redo08_1.log','/opt/oracle/oradata/dbtest/redo08_2.log') size 10m;
alter database add logfile group 5
('/opt/oracle/oradata/dbtest/redo05_1.log','/opt/oracle/oradata/dbtest/redo05_2.log') size 10m reuse,
group 6 '/opt/oracle/oradata/dbtest/redo06_1.log' size 10m;
---------------------------------------------------------------------
2.ADD LOG MEMBER:
ALTER DATABASE [database] ADD LOGFILE MEMBER 'filename' [REUSE] [, 'filename' [REUSE]...] TO GROUP integer | ( all_file_in_the_group_spec );
說明：
--- filespec ： 包括地址的全文件名
--- REUSE: 表示如果已經(jīng)存在此文件就重用它！
eg:
alter database add logfile member '/opt/oracle/oradata/dbtest/redo04_3.log' to group 4;
alter database add logfile member '/opt/oracle/oradata/dbtest/redo02_3.log','/opt/oracle/oradata/dbtest/redo02_4.log' to group 2;
alter database add logfile member '/opt/oracle/oradata/dbtest/redo04_4.log' to ('/opt/oracle/oradata/dbtest/redo04_1.log','/opt/oracle/oradata/dbtest/redo04_2.log','/opt/oracle/oradata/ dbtest/redo04_3.log');
3.DROP LOG GROUP:
ALTER DATABASE [database] DROP LOGFILE GROUP integer | ( all_file_in_the_group_spec ) [,GROUP integer | ( all_file_in_the_group_spec ) ... ];
說明：
--- An instance requires at least two groups of online redo log files.（每個(gè)實(shí)例至少要有兩個(gè)組）
--- An active or current group cannot be dropped.（一個(gè)處于active或current狀態(tài)的組不能被刪除）
--- When an online redo log group is dropped, the operating system files are not deleted. （所用的刪除操作是指刪除數(shù)據(jù)庫中的信息，在操作系統(tǒng)中不刪除。）
--- all_file_in_the_group_spec ： 此日志組包括的所有的成員的全文件名
--- 當(dāng)刪除用的是drop logfile group 5的時(shí)候，則group 5中的所有成員也同樣在數(shù)據(jù)庫中被刪除
eg:
alter database drop logfile group 5;
alter database drop logfile ('/opt/oracle/oradata/dbtest/redo05_1.log','/opt/oracle/oradata/dbtest/redo05_2.log')
alter database drop logfile group 6 , ('/opt/oracle/oradata/dbtest/redo05_1.log','/opt/oracle/oradata/dbtest/redo05_2.log');
----------------------------------------------------------
oracle redo日志恢復(fù)
如果數(shù)據(jù)庫是正常shutdown,非當(dāng)前日志都可以直接clear來重新生成，而且不丟失數(shù)據(jù)，因?yàn)檎�常關(guān)閉db,數(shù)據(jù)已經(jīng)寫入dbf文件了。唯獨(dú)當(dāng)前日志不可以，當(dāng)前日志必須用其他方法恢復(fù)，不管是不是正常關(guān)閉，
select * from v$log;
alter database clear logfile group 1;
如果數(shù)據(jù)庫是正常關(guān)閉的，用recover database until cancel可以輕松恢復(fù)或者說重新建立所有的redo，不再區(qū)分是否是當(dāng)前日志，而且由于正常關(guān)閉，不會(huì)丟失任何數(shù)據(jù)，唯一可能丟失的情況就是如果日志還沒有歸檔.
recover database until cancel
繼續(xù)用Resetlogs方法打開數(shù)據(jù)庫，其實(shí)就是根據(jù)控制文件讓系統(tǒng)自動(dòng)重新生成redo,如果noresetlog的話，就不會(huì)重新生成redo,缺少了文件，db自然無法啟動(dòng)，這種恢復(fù)方法 由于要resetlogs,所以在恢復(fù)完成后，日志清零，以前的備份不再起作用，所以建議立即備份
alter database open resetlogs;
-----------------------------------------------------------------
oracle redo日志故障處理
我們知道，當(dāng)數(shù)據(jù)庫發(fā)生日志切換時(shí)（Log Switch）,Oracle會(huì)觸發(fā)一個(gè)檢查點(diǎn)（Checkpoint），檢查點(diǎn)進(jìn)程（Checkpoint Process，CKPT）會(huì)通知DBWR（Database?Writer）進(jìn)程去執(zhí)行寫操作。在日志文件所保護(hù)的處于Buffer cache中的臟數(shù)據(jù)（dirty buffer）未寫回磁盤之前，日志文件不能被覆蓋或重用。如果數(shù)據(jù)庫異常繁忙，或者DBWR的寫出過慢，就可能出現(xiàn)檢查點(diǎn)未完成，Oracle卻已經(jīng)用完所有日志文件的情況。在這種情況下，數(shù)據(jù)庫的日志無法生成，整個(gè)數(shù)據(jù)庫將處于停頓狀態(tài)，此時(shí)日志文件中會(huì)記錄類似如下信息："Thread 1 cannot allocate new log"，表明系統(tǒng)的checkpoint 沒有來得及完成，也就是說 buffer cache 中的dirty data還沒有完全寫到數(shù)據(jù)文件，就已經(jīng)有大量的日志需要寫入到系統(tǒng)。而系統(tǒng)只能通知應(yīng)用：checkpoint 還沒有完成，你只能等待。這個(gè)時(shí)候，系統(tǒng)就基本處于hang 狀態(tài)了
下面我們來看看日志的實(shí)時(shí)刷新信息
[root@radius ~]# tail -f $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log
Thu Jan 6 22:26:35 2011
Thread 1 advanced to log sequence 151078 (LGWR switch)
Current log# 3 seq# 151078 mem# 0: /ora/oradata/radius/redo03.log
Thu Jan 6 22:27:08 2011
Thread 1 cannot allocate new log, sequence 151079
Checkpoint not complete
Current log# 3 seq# 151078 mem# 0: /ora/oradata/radius/redo03.log
Thu Jan 6 22:27:12 2011
Thread 1 advanced to log sequence 151079 (LGWR switch)
Current log# 1 seq# 151079 mem# 0: /ora/oradata/radius/redo01.log
Thu Jan 6 22:27:45 2011
Thread 1 cannot allocate new log, sequence 151080
Checkpoint not complete
Current log# 1 seq# 151079 mem# 0: /ora/oradata/radius/redo01.log
可以看出切換日志間隔不到一分鐘，增加日志組容量和數(shù)據(jù)刻不容緩吶
1.查詢下當(dāng)前redo log 情況
SQL> select group#,status,archived,bytes/1024/1024 from v$log;
GROUP# STATUS ARC BYTES/1024/1024
---------- ---------------- --- ---------------
1 INACTIVE YES 50
2 CURRENT NO 50
3 UNUSED YES 50
可以看出有3組50M redo log
2.根據(jù)目前的切換日志的頻率，決定將日志組增加至5組，每組200M.
SQL> alter database add logfile group 4 '/ora/oradata/radius/redo04.log' size 200m;
Database altered.
SQL> alter database add logfile group 5 '/ora/oradata/radius/redo05.log' size 200m;
Database altered.
SQL> alter database add logfile group 6 '/ora/oradata/radius/redo06.log' size 200m;
Database altered.
SQL> alter database add logfile group 7 '/ora/oradata/radius/redo07.log' size 200m;
Database altered.
SQL> alter database add logfile group 8 '/ora/oradata/radius/redo08.log' size 200m;
Database altered.
查詢現(xiàn)在redo log狀態(tài)
SQL> select group#,status,archived,bytes/1024/1024 from v$log;
GROUP# STATUS ARC BYTES/1024/1024
---------- ---------------- --- ---------------
1 INACTIVE YES 50
2 ACTIVE YES 50
3 CURRENT NO 50
4 UNUSED YES 200
5 UNUSED YES 200
6 UNUSED YES 200
7 UNUSED YES 200
8 UNUSED YES 200
3.刪除之前的三個(gè)50M的redo log組
由于當(dāng)前日志還在group# 3 redo log上，所以需要切換日志到其他的日志組中去.
SQL> alter system switch logfile;
System altered.
SQL> select group#,status,archived,bytes/1024/1024 from v$log;
GROUP# STATUS ARC BYTES/1024/1024
---------- ---------------- --- ---------------
1 INACTIVE YES 50
2 ACTIVE YES 50
3 ACTIVE YES 50
4 CURRENT NO 200
5 UNUSED YES 200
6 UNUSED YES 200
7 UNUSED YES 200
8 UNUSED YES 200
從上面的status字段可以看出group# 2,3 redo log 的狀態(tài)均是ACTIVE,也就是內(nèi)存中的臟數(shù)據(jù)還沒有寫到數(shù)據(jù)文件中，這時(shí)oracle是不允許你刪除的，如果你硬要?jiǎng)h除會(huì)出現(xiàn)下面這個(gè)提示
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance radius (thread 1)
ORA-00312: online log 2 thread 1: '/ora/oradata/radius/redo02.log'
如果你需要馬上刪除這個(gè)redo log ，你可以使用checkpoint來將臟數(shù)據(jù)寫進(jìn)數(shù)據(jù)文件（磁盤）中，之后再將group# 1,2,3的redo log一一刪除。
SQL> alter system checkpoint;
System altered.
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.
查看當(dāng)前redo log狀態(tài)
SQL> select group#,status,archived,bytes/1024/1024 from v$log;
GROUP# STATUS ARC BYTES/1024/1024
---------- ---------------- --- ---------------
4 CURRENT NO 200
5 INACTIVE YES 200
6 INACTIVE YES 200
7 INACTIVE YES 200
8 ACTIVE YES 200
4.刪除不可用的redo log
上一步相當(dāng)于是邏輯上從Oracle中刪除了幾個(gè)redo log，這些redo log的物理文件仍然還在硬盤上躺著，需要使用Linux命令刪除之。


來源：http://blog.chinaunix.net/space. ... blog&id=2435069