Managing REDO LOGFILES

Every Oracle database must have at least 2 redo logfile groups. Oracle writes all statements except, SELECT statement, to the logfiles. This is done because Oracle performs deferred batch writes i.e. it does write changes to disk per statement instead it performs write in batches. So in this case if a user updates a row, Oracle will change the row in db_buffer_cache and records the statement in the logfile and give the message to the user that  row is updated. Actually the row is not yet written back to the datafile but still it give the message to the user that row is updated. After 3 seconds the row is actually written to the datafile. This is known as deferred batch writes. 

Since Oracle defers writing to the datafile there is chance of power failure or system crash before the row is written to the disk. That’s why Oracle writes the statement in redo logfile so that in case of power failure or system crash oracle can re-execute the statements next time when you open the database.

Adding a New Redo Logfile Group

To add a new Redo Logfile group to the database give the following command

SQL>alter database add logfile group 3
‘/u01/oracle/ica/log3.ora’ size 10M;

Note: You can add groups to a database up to the MAXLOGFILES setting you have specified at the time of creating the database. If you want to change MAXLOGFILE setting you have to create a new controlfile.

Adding Members to an existing group

To add new member to an existing group give the following command

SQL>alter database add logfile member
‘/u01/oracle/ica/log11.ora’ to group 1;

Note: You can add members to a group up to the MAXLOGMEMBERS setting you have specified at the time of creating the database. If you want to change MAXLOGMEMBERS setting you have create a new controlfile

Important: Is it strongly recommended that you multiplex logfiles i.e. have at least two log members, one member in one disk and another in second disk, in a database.

Dropping Members from a group

You can drop member from a log group only if the group is having more than one member and if it is not the current group. If you want to drop members from the current group, force a log switch or wait so that log switch occurs and another group becomes current. To force a log switch give the following command

SQL>alter system switch logfile;

The following command can be used to drop a logfile member

SQL>alter database drop logfile member ‘/u01/oracle/ica/log11.ora’;

Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.

Dropping Logfile Group

Similarly, you can also drop logfile group only if the database is having more than two groups and if it is not the current group.

SQL>alter database drop logfile group 3;

Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.