Oracle Database Admin (DBA) Tutorial: Redo Log files management

1.  What is redo log file

•       Oracle writes all statements except, SELECT statement, to the log files. 

•        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 log file so that in case of power failure or system crash oracle can re-execute the statements next time when you open the database.

•       Every Oracle database must have at least 2 redo logfile groups.

2.  Redo log groups status

·       UNUSED - The Redo Log Group has never been used – this status only occurs for a newly added Redo Log Group.

·       CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.

·       ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.

·       CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.

·       INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.

3.  Create online Redo log Groups

Online redo logs are created when the database was created.

4.  Adding a New Redo Logfile Group

Ex:-

alter database add logfile group 4 ('/u02/oradata/orcl/redo04a.log‘,’ /u02/oradata/orcl/redo04a.log) size 50m;

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

Select '- MAXLOGFILES - '||records_total  from v$controlfile_record_section where type = 'REDO LOG';

5.  Adding Members to an existing group

Ex: alter database add logfile member '/u02/oradata/orcl/redo01b.log' 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

              Select 'MAXLOGMEMBERS ' || dimlm from x$kccdi;

6.  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 à alter system switch logfile

The following command can be used to drop a logfile member:-

alter database drop logfile member '/u02/oradata/GG/GGTEST/redo01b.log';

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.

7.  Dropping Logfile Group

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

              alter database drop logfile group 4;

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.

8.  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.

9.  Renaming or Relocating Logfiles

For Example, suppose you want to move  a logfile from ‘/u01/app/oracle/orcl/log1.ora’ to ‘/u02/oracle/ica/log1.ora’, then do the following steps:

1.       Shutdown the database

SQL>shutdown immediate;

2.       Move the logfile from old to new location using operating system command

$mv /u01/app/oracle/orcl/log1.ora  /u02/oracle/ica/

3.       Start and mount the database

SQL>startup mount

4.       Now give the following command to change the location in controlfile

SQL>alter database rename file ‘/u01/app/oracle/orcl/log1.ora’ to ‘/u02/oracle/ica/log2.ora’;

5.       Open the database

SQL>alter database open;

10.               Clearing REDO LOGFILES

·       A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database.

Ex: - Alter database clear logfile group 3;

·       This statement overcomes two situations where dropping redo logs is not possible:

                                                               i.      If there are only two log groups

                                                             ii.      The corrupt redo log file belongs to the current group

·       If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

11.               Viewing Information about Log files

•       select *from v$logfile;

•       Select *from v$log;