How to change redo logfile size

Posted on by Leave a comment
We can not change the size of existing redo log file, we need to add a new redo log files with size we want. Below is the procedure for the same.

SQL> select THREAD#,GROUP#,BYTES/1024/1024,status from v$log;

   THREAD#     GROUP# BYTES/1024/1024 STATUS
———- ———- ————— —————-
         1          2              50 INACTIVE
         1          3              50 CURRENT


Here We have two redo log files with size 50 MB. To increase the size we will add new redo logfiles with different size.

SQL> alter database add logfile THREAD 1 group 10 size 100M;

Database altered.

SQL> alter database add logfile THREAD 1 group 11 size 100M;

Database altered.


SQL> select THREAD#,GROUP#,BYTES/1024/1024,status from v$log;

   THREAD#     GROUP# BYTES/1024/1024 STATUS
———- ———- ————— —————-
         1          2              50 INACTIVE
         1          3              50 CURRENT
         1         10             100 UNUSED
         1         11             100 UNUSED



The two new redo log files are added to database. Now we need to remove the old redo log files once they come to “INACTIVE” state.

SQL> alter database drop logfile group 2;

Database altered.

I removed the redo log file group 2 as it is in INACTIVE state. We can not drop the redo logfile group 3 as it is in CURRENT state.


SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance MYDB (thread 1) – cannot drop
ORA-00312: online log 3 thread 1: ‘+REDO/mydb/onlinelog/group_3.259.881765243’
To make the redo logfile group 3 , INACTIVE , do redo logfile switch manually.
SQL> alter system switch logfile;

System altered.
 

Perform this log switching 3 , 4 times.
 
SQL> select THREAD#,GROUP#,BYTES/1024/1024,status from v$log;

   THREAD#     GROUP# BYTES/1024/1024 STATUS
———- ———- ————— —————-
         1          3              50 INACTIVE
         1         10             100 CURRENT
         1         11             100 INACTIVE
 

Now the logfile group 3 is in inactive state.
SQL> alter database drop logfile group 3;

Database altered.

Redo logfile group 3 is dropped.

SQL> select THREAD#,GROUP#,BYTES/1024/1024,status from v$log;

   THREAD#     GROUP# BYTES/1024/1024 STATUS
———- ———- ————— —————-
         1         10             100 CURRENT
         1         11             100 INACTIVE

Now the size of the redo logfiles is changed to 100MB from 50MB.
 
tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s