DBA Admin05 - Redo Log Files

Redo log files are two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an
associated redo log to protect the database in case of an instance failure.

If log files are not accessible for writing then Oracle Database returns an error, and the database instance shuts down.

Redo Log Data Dictionary Views

The following views provide information on redo logs.

V$LOG = Displays the redo log file information from the control file

V$LOGFILE = Identifies redo log groups and members and member status

V$LOG_HISTORY = Contains log history information

Redo Log Contents

Redo log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database. Redo records are buffered in a circular fashion in the redo log buffer of the SGA and are written to one of the redo log files by the Log Writer (LGWR) database background process.

Redo Log writing process

The redo log of a database consists of two or more redo log files. LGWR writes to redo log files in a circular fashion. When the current redo log file fills, LGWR begins writing to the next available redo log file. When the last available redo log file is filled, LGWR returns to the first redo log file and writes to it, starting the

cycle again. The redo log file that LGWR is actively writing to is called the current redo log file.

Redo log files that are required for instance recovery are called active redo log files. Redo log files that are no longer required for instance recovery are called inactive redo log files.

Multiplexing Redo Log Files

To protect against a failure involving the redo log itself, Oracle Database allows a multiplexed redo log, meaning that two or more identical copies of the redo log can be automatically maintained in separate locations. For the most benefit, these locations should be on separate disks.

Creating Redo Log Groups

The following statement adds a new group of redo logs to the database.

ALTER DATABASE ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 4M;

or

ALTER DATABASE ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 4M;

To create new redo log members for an existing group, use the SQL statement ALTER DATABASE with the ADD LOGFILE MEMBER clause.

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2;

Steps for Renaming Redo Log Members

1. Shut down the database.

2. Copy the redo log files to the new location.

3. Startup the database, mount, but do not open it.

CONNECT / as SYSDBA

STARTUP MOUNT

4. Rename the redo log members.

ALTER DATABASE

RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo'

TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo';

5. Open the database for normal operation.

ALTER DATABASE OPEN;

Dropping Log Groups

1. SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

GROUP# ARC STATUS

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

1 YES ACTIVE

2 NO CURRENT

3 YES INACTIVE

4 YES INACTIVE

2. ALTER DATABASE DROP LOGFILE GROUP 3;

To drop individual member of log file group use the following command.

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/log3c.rdo';

How to force log switch

ALTER SYSTEM SWITCH LOGFILE;

Verifying Blocks in Redo Log Files

You can configure the database to use checksums to verify blocks in the redo log files. If you set the initialization parameter DB_BLOCK_CHECKSUM to TYPICAL (the default), the database computes a checksum for each database block when it is written to disk, including each redo log block as it is being written to the current log. The checksum is stored the header of the block. The value of the DB_BLOCK_CHECKSUM parameter can be changed dynamically using the ALTER SYSTEM statement. There is a slight overhead and decrease in database performance with DB_BLOCK_CHECKSUM enabled. Monitor your database performance to decide if the benefit of using data block checksums to detect corruption outweighs the performance impact.

How to clear redo log files

The following statement clears the log files in redo log group number 3:

ALTER DATABASE CLEAR LOGFILE GROUP 3;

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

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;