Oracle increases logfile Size

Sometimes because the logfile is too small, resulting in log switching frequently, causing the log file switch ... Wait for the event, this time need to increase the size of the logfile.

To increase the size of the logfile:

One: View initialization parameters

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

Why did you do it? Because these parameters of OMF are set, it is possible for Oracle to name logfile when creating logfile.

I enabled the Db_create_file_dest parameter, then Oracle will create the logfile in this path by default and create a logfile in db_recory_file_dest.

Two: View the current log situation

Sql> select * from V$log;

    group#    thread#  sequence#      BYTES     members archived status           first_change# First_time--------------------------------------------------------------------------------------------------& nbsp;        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

Three: Create a new log file group

sql> ALTER DATABASE Add logfile Group 6 size 150m;

Database Altered

sql> ALTER DATABASE Add logfile Group 7 size 150m;

Database Altered

Four: Switch log files to the newly created log file group

sql> alter system switch logfile;

System Altered

sql> alter system switch logfile;

System Altered

Five: Check the current log file information, if the status is inactive,archived Yes, then drop the old log file group

Ql> select * from V$log;

    group#    thread#  sequence#      BYTES     members archived status           first_change# First_time--------------------------------------------------------------------------------------------------& nbsp;        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

VI: Re-create the log file group

sql> ALTER DATABASE Add logfile Group 1 size 150m;

Database Altered

sql> ALTER DATABASE Add logfile Group 2 size 150m;

Database Altered Seven: switch log files again sql> alter system switch logfile;

System Altered

sql> alter system switch logfile;

System Altered

Eight: Check the log file information, if the status is inactive,archived Yes, then drop the old log file group

Sql> select * from V$log;

    group#    thread#  sequence#      BYTES     members archived status           first_change# First_time--------------------------------------------------------------------------------------------------& nbsp;        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; ---here to change status to inactive as soon as possible.

System Altered

Sql> select * from V$log;

    group#    thread#  sequence#      BYTES     members archived status           first_change# First_time--------------------------------------------------------------------------------------------------& nbsp;        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

Eight: Delete Intermediate log file groups

sql> ALTER DATABASE drop logfile Group 6;

Database Altered

sql> ALTER DATABASE drop logfile Group 7;

Database Altered

Oracle increases logfile Size