Thread n cannot allocate new log? Increase size of redo log groups.

Do you receive warnings often like :

Thread 2 cannot allocate new log, sequence 313     {WARNING}

You are running your database is ARCHIVELOG mode and the size of your redo log groups is small. You have to increase the groups size and one way of doing this is :

1. Add additional logfile groups. If you have 2 groups add 3rd and 4th.

ALTER DATABASE ADD LOGFILE GROUP 3 ('/location/of/datafile','/location/of/recoveryfile') size 500M;

This will add group 3 consisting of 2 files (logfile and recovery file) with total group size 500M.

2. Switch logfile to make group 1 and 2 INACTIVE.

Check status of the group:

SELECT GROUP#, STATUS FROM V$LOG;

Switch Logfile:

ALTER SYSTEM SWITCH LOGFILE;

3. Drop groups 1 and 2.

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;

Now you have 2 logfile groups with bigger size. If you want to name your groups 1 and 2 again:

4. Create logfile groups 1 and 2 with specified size like step 1.

5. Drop logfile groups 3 and 4.

If you are using ASM disks the location of your logfiles will be : ‘+DATA_DISK_GROUP’ and ‘+FLASH_RECOVERY_DISK_GROUP’

Advertisements

Leave a Reply

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

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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s