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’

Facebook is down for maintenance

Today, Saturday the 13th of Dec 2008 at 2:30 AM Facebook is down for maintenance and I can’t login. Come on facebook this does not suit you.

Adding a new datafile to Oracle standby

Do you have a manual Oracle standby database in place catching archive logs from production site? The process will break if you add a new datafile to the production database due to error

ORA-01244: unnamed datafile(s) added to controlfile by media recovery

And you will find some unnamed datafiles in your standby database:

SELECT name FROM v$datafile;

You cannot apply archive logs until you create a datafile in standby database too. The controlfile has added a new datafile for you in the metadata because it is synchronizing with the production database but this datafile does not exist on disk. So you have to convert that fake datafile to a real one:

ALTER DATABASE CREATE DATAFILE '/name/of/your/unnamed/datafile.dbf' AS '/path/to/real/datafile.dbf' SIZE 1G;

This command will create a datafile on the disk and will change the name/path of the unnamed datafile. If you are using ASM the command will be:

ALTER DATABASE CREATE DATAFILE '/name/of/your/unnamed/datafile.dbf' AS '+YOUR_DISK_GROUP' SIZE 1G;