Redolog file management

Managing Redo Logfiles in Oracle :

 

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;

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;

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.

Resizing Logfiles

You cannot resize logfiles. If you want to resize a logfile create a new logfile group with the new size and subsequently drop the old logfile group.

Viewing Information About Logfiles

To See how many logfile groups are there and their status type the following query.

SQL>SELECT * FROM V$LOG;

GROUP# THREAD#   SEQ   BYTES  MEMBERS  ARC STATUS     FIRST_CHANGE# FIRST_TIM

------ ------- ----- -------  -------  --- ---------  ------------- ---------

     1       1 20605 1048576        1  YES ACTIVE          61515628 21-JUN-07

     2       1 20606 1048576        1  NO  CURRENT         41517595 21-JUN-07

     3       1 20603 1048576        1  YES INACTIVE        31511666 21-JUN-07

     4       1 20604 1048576        1  YES INACTIVE        21513647 21-JUN-07

To See how many members are there and where they are located give the following query

SQL>SELECT * FROM V$LOGFILE;

GROUP#   STATUS  MEMBER

------  -------  ----------------------------------

     1           /U01/ORACLE/ICA/LOG1.ORA

     2           /U01/ORACLE/ICA/LOG2.ORA