Oracle 日誌管理一例

xchui702發表於2011-07-20
Oracle

 

今天檢查一個資料庫時發現有很多關於log的大量等待如下：
log file parallel write
log file sync
log buffer space

查詢相關資料得到關於這些事件的詳細敘述：

log buffer space

Waiting for space in the log buffer because the session is writing data into the log buffer faster than LGWR can write it out. Consider making the log buffer bigger if it is small, or moving the log files to faster disks such as striped disks.

Wait Time: Usually 1 second, but 5 seconds if it is waiting for a Switch Logfile to complete

Parameters: None

log file parallel write

Writing redo records to the redo log files from the log buffer.

Wait Time: Time it takes for the I/Os to complete. Even though redo records are written in parallel, the parallel write is not complete until the last I/O is on disk.

Parameter Description
files Number of files to be written
blocks Number of blocks to be written
requests Number of I/O requests

log file sync

When a user session commits, the session's redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write the log buffer to the redo log file. When the LGWR has finished writing, it will post the user session.

Wait Time: The wait time includes the writing of the log buffer and the post.

Parameter Description
buffer# The number of the physical buffer in the redo log buffer that needs to be synchronized

所有這些都表示logfile 的IO有問題。
檢視logfile的佈局，發現當前是5組logfile, 每組3個成員，其中1,2,3組所有member都在一個磁碟上，而4,5組在另外一個磁碟上。顯然此是問題所在。
 
新的佈局安排如下：
五組中所有的所有第一個member在disk1上，第二個在磁碟disk2上，第三個在磁碟disk3上。
 
完成這些操作最簡單的方法是：
當某個組是inactive或active狀態(非online)時，mv  logfile member 到新的位置，然後使用 alter database rename file 'member old location' to 'member new location'; (注：在rename時候，會檢查新位置檔案的狀態,所以要先移過去).
例子命令如下：
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         1         ONLINE  /ora/u02/oradata/cudwh01/cudwh01_redo_1a.log                 NO
         1         ONLINE  /ora/u04/oradata/cudwh01/cudwh01_redo_1b.log                 NO
         1         ONLINE  /ora/u04/oradata/cudwh01/cudwh01_redo_1c.log                 NO
         4         ONLINE  /ora/u02/oradata/cudwh01/cudwh01_redo_4a.log                 NO
         4         ONLINE  /ora/u04/oradata/cudwh01/cudwh01_redo_4b.log                 NO
         4         ONLINE  /ora/u05/oradata/cudwh01/cudwh01_redo_4c.log                 NO
6 rows selected.
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------
         1          1      52359  209715200        512          3 NO  ACTIVE              1.1527E+10 20-JUL-11      1.1527E+10 20-JUL-11
         4          1      52360  209715200        512          3 NO  CURRENT             1.1527E+10 20-JUL-11      2.8147E+14
SQL> ! mv /ora/u04/oradata/cudwh01/cudwh01_redo_1c.log /ora/u05/oradata/cudwh01/cudwh01_redo_1c.log
SQL> alter database rename file '/ora/u04/oradata/cudwh01/cudwh01_redo_1c.log' to '/ora/u05/oradata/cudwh01/cudwh01_redo_1c.log';
Database altered.

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/674865/viewspace-702614/，如需轉載，請註明出處，否則將追究法律責任。

相關文章