调整联机重做日志大小(change redo log size)

Oracle 联机日志加上Oracle归档日志记录了整个 数据库完整的变更信息。是 Oracle体系结构中的重要组成部分。因此联机日志的规划也相当重要。但随着业务的增加或当前联机日志文件的大小成为数据库瓶颈之一时，不得不调整联机日志的大小。本文演示了如何调整联机日志的大小。

1、演示环境

[sql]

sys@CNBO1> select * from v$version where rownum<2;

BANNER

----------------------------------------------------------------

Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

sys@CNBO1> archive log list;

Database log mode Archive Mode -->已处于归档模式

Automatic archival Enabled

Archive destination /u02/database/CNBO1/archive/

Oldest online log sequence 4881

Next log sequence to archive 4884

Current log sequence 4884

2、日志使用情况

[sql]

sys@CNBO1> @log_stat

GROUP# MEMBER SEQUENCE# SIZE_MB STATUS

---------- --------------------------------------------- ---------- ---------- -------

1 /u02/database/CNBO1/redolog/log1aCNBO1.log 4881 20 ACTIVE

1 /u02/database/CNBO1/redolog/log1bCNBO1.log 4881 20 ACTIVE

2 /u02/database/CNBO1/redolog/log2aCNBO1.log 4882 20 ACTIVE

2 /u02/database/CNBO1/redolog/log2bCNBO1.log 4882 20 ACTIVE

3 /u02/database/CNBO1/redolog/log3aCNBO1.log 4883 20 ACTIVE

3 /u02/database/CNBO1/redolog/log3bCNBO1.log 4883 20 ACTIVE

4 /u02/database/CNBO1/redolog/log4aCNBO1.log 4884 20 ACTIVE

4 /u02/database/CNBO1/redolog/log4bCNBO1.log 4884 20 CURRENT

8 rows selected.

sys@CNBO1> select group#,archived,status from v$log;

GROUP# ARC STATUS

---------- --- ----------------

1 YES ACTIVE

2 YES ACTIVE

3 YES ACTIVE

4 NO CURRENT

3、调整联机日志大小

[sql]

sys@CNBO1> alter system archive log all; -->归档所有日志文件

alter system archive log all

*

ERROR at line 1:

ORA-00271: there are no logs that need archiving -->提示没有日志需要进行归档

sys@CNBO1> alter database drop logfile group 1; -->由于group1 处于active状态，不可删除

alter database drop logfile group 1

*

ERROR at line 1: -->下面提示crash recovery会用到

ORA-01624: log 1 needed for crash recovery of instance CNBO1 (thread 1)

ORA-00312: online log 1 thread 1: '/u02/database/CNBO1/redolog/log1aCNBO1.log'

ORA-00312: online log 1 thread 1: '/u02/database/CNBO1/redolog/log1bCNBO1.log'

sys@CNBO1> alter system switch logfile; -->切换日志

System altered.

sys@CNBO1> select group#,archived,status from v$log; -->此时状态已发生变化

GROUP# ARC STATUS

---------- --- ----------------

1 NO CURRENT

2 YES INACTIVE

3 YES INACTIVE

4 YES ACTIVE

sys@CNBO1> alter database drop logfile group 2; -->删除第2个日志组

Database altered.

sys@CNBO1> ho ls /u02/database/CNBO1/redolog/log2* -->可以看到，物理文件并没有被删除

/u02/database/CNBO1/redolog/log2aCNBO1.log /u02/database/CNBO1/redolog/log2bCNBO1.log

sys@CNBO1> alter database add logfile group 2 ('/u02/database/CNBO1/redolog/log2aCNBO1.log',

2 '/u02/database/CNBO1/redolog/log2bCNBO1.log') size 100m reuse; -->添加日志组，并使用新的size

Database altered.