增大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删除。
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/