增大redo log文件大小

增大online redo log的大小并不像增大datafile的大小那么简单,它需要新建一个group,然后把原来的删除,下面用一个例子来说明:
1. 查看当前logfile情况:
A105024@O02DMS1>SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         4          1       1799  209715200          1 NO  CURRENT             6622373533 09-OCT-11
         5          1       1797  209715200          1 YES INACTIVE            6622360490 08-OCT-11
         6          1       1798  209715200          1 YES INACTIVE            6622360509 08-OCT-11
A105024@O02DMS1>select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                                                           IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         4         ONLINE  K:\INTEL_INDEX\O02DMS1\REDO04.O02DMS1                                      NO
         5         ONLINE  J:\INTEL_LOG\O02DMS1\REDO05.O02DMS1                                              NO
         6         ONLINE  I:\INTEL_DATA\O02DMS1\REDO06.O02DMS1                                             NO
group 4的大小为200M,我们需要把它增大为300M。
2. 新建一个group:
A105024@O02DMS1>alter database add logfile group 1 ('K:\INTEL_INDEX\O02DMS1\REDO01.O02DMS1') size 300M;

Database altered.

3. 确保要被drop的group是inactive却已被archived:

A105024@O02DMS1>SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          0  314572800          1 YES UNUSED                       0
          4          1       1799  209715200          1 NO  CURRENT             6622373533 09-OCT-11
         5          1       1797  209715200          1 YES INACTIVE            6622360490 08-OCT-11
         6          1       1798  209715200          1 YES INACTIVE            6622360509 08-OCT-11
可以看到,group 4是当前正在用的redo log,此时无法删除:
A105024@O02DMS1>alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01623: log 4 is current log for instance o02dms1 (thread 1) - cannot drop
ORA-00312: online log 4 thread 1: 'K:\INTEL_INDEX\O02DMS1\REDO04.O02DMS1'

此时应该手动做switch logfile和checkpoint:
A105024@O02DMS1>alter system switch logfile;

System altered.

A105024@O02DMS1>alter system checkpoint;

System altered.

A105024@O02DMS1>SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1       1800  314572800          1 NO  CURRENT             6622373663 09-OCT-11
         4          1       1799  209715200          1 YES INACTIVE            6622373533 09-OCT-11
         5          1       1797  209715200          1 YES INACTIVE            6622360490 08-OCT-11
         6          1       1798  209715200          1 YES INACTIVE            6622360509 08-OCT-11

可以看到,group 4的状态已经是inactive,且已被archived。

4. 删除原有的group:
A105024@O02DMS1>alter database drop logfile group 4;

Database altered.

A105024@O02DMS1>SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1       1800  314572800          1 NO  CURRENT             6622373663 09-OCT-11
         5          1       1797  209715200          1 YES INACTIVE            6622360490 08-OCT-11
         6          1       1798  209715200          1 YES INACTIVE            6622360509 08-OCT-11

A105024@O02DMS1>select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         5         ONLINE  J:\INTEL_LOG\O02DMS1\REDO05.O02DMS1                                              NO
         6         ONLINE  I:\INTEL_DATA\O02DMS1\REDO06.O02DMS1                                             NO
         1         ONLINE  K:\INTEL_INDEX\O02DMS1\REDO01.O02DMS1                                       NO


group 4被删除了,且group 1正在被使用。

注意:在数据库层面,group 4已经被删除了,但是在操作系统层面,原有的logfile还在,所以还得到操作系统层面把原有的logfile删除。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26277071/viewspace-708846/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/26277071/viewspace-708846/

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值