增加 logfile 大小

有时候由于logfile太小，导致日志切换频繁，引起log file switch ...之类的等待事件，这个时候需要增加 logfile的大小。

增加logfile 大小的步骤:

一:查看初始化参数

SQL> show parameter db_create_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string C:/oracle/product/10.2.0/oradata
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string

为什么要这么做呢？因为设置了OMF的这些参数，就可以在创建logfile 的时候让ORACLE去命名logfile。

我启用了db_create_file_dest这个参数，那么oracle就会默认的在这个路径下创建logfile 以及在 db_recory_file_dest中创建一个logfile.

二:查看当前日志的情况

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
4 1 60 104857600 2 YES INACTIVE 1746560 12/21/2009
5 1 61 104857600 2 NO CURRENT 1746623 12/21/2009

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
4 ONLINE C:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ROBINSON/ONLINELOG/LOG1A.LOG NO
4 ONLINE C:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ROBINSON/ONLINELOG/LOG1B.LOG NO
5 ONLINE C:/ORACLE/PRODUCT/10.2.0/ORADATA/ROBINSON/ONLINELOG/LOG2A.LOG NO
5 ONLINE C:/ORACLE/PRODUCT/10.2.0/ORADATA/ROBINSON/ONLINELOG/LOG2B.LOG NO

三：创建新的日志文件组

SQL> alter database add logfile group 6 size 150m;

Database altered

SQL> alter database add logfile group 7 size 150m;

Database altered

四：切换日志文件到新创建的日志文件组

SQL> alter system switch logfile ;

System altered

SQL> alter system switch logfile ;

System altered

五：查看当前日志文件信息，如果status为inactive,ARCHIVED为YES，那么drop 老的日志文件组

QL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
4 1 60 104857600 2 YES INACTIVE 1746560 12/21/2009
5 1 61 104857600 2 YES ACTIVE 1746623 12/21/2009
6 1 62 157286400 2 YES ACTIVE 1748762 12/21/2009
7 1 63 157286400 2 NO CURRENT 1748764 12/21/2009
SQL> alter database drop logfile group 4;

Database altered

SQL> alter database drop logfile group 5;

Database altered

六：重新创建日志文件组

SQL> alter database add logfile group 1 size 150m;

Database altered

SQL> alter database add logfile group 2 size 150m;

Database altered
七：再次切换日志文件
SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

八：查看日志文件信息，如果status为inactive,archived 为yes，那么drop 老的日志文件组

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 64 157286400 2 YES ACTIVE 1749065 12/21/2009
2 1 65 157286400 2 NO CURRENT 1749067 12/21/2009
6 1 62 157286400 2 YES INACTIVE 1748762 12/21/2009
7 1 63 157286400 2 YES ACTIVE 1748764 12/21/2009

SQL> alter system flush buffer_cache; ---此处是为了尽快让status变成inactive.

System altered

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 64 157286400 2 YES INACTIVE 1749065 12/21/2009
2 1 65 157286400 2 NO CURRENT 1749067 12/21/2009
6 1 62 157286400 2 YES INACTIVE 1748762 12/21/2009
7 1 63 157286400 2 YES INACTIVE 1748764 12/21/2009

八:删除中间日志文件组

SQL> alter database drop logfile group 6;

Database altered

SQL> alter database drop logfile group 7;

Database altered