ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 日志管理一例

Oracle 日志管理一例

原创 Linux操作系统 作者:xchui702 时间:2011-07-20 10:35:59 0 删除 编辑

 

今天检查一个数据库时发现有很多关于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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle 恢复一例
请登录后发表评论 登录
全部评论

注册时间:2011-05-16

  • 博文量
    38
  • 访问量
    118281