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’
