Oracle Fundamental I - Chapter 7: Maintaining Online Redo Log Files

In this chapter, we are talking about online redo log files,
The only redo log files play the important role to record the data transactions, and provide the recovery mechanism to ensure data integrity.

Online redo log files have the those characteristics:

• Record all changes made to data
• Provide a recovery mechanism
• Can be organized into groups
• At least two groups required

The online redo logs files provide the mean to redo transactionin the even to a database failure. When a transaction coming, it's written into the redo log buffer (Please refer to chapter 1 for the architecture); and then gets flushed to the online redo log files. When a media failure occurs, the redo log files provides the recovery mechanism to recover the transaction data. This includes not yet committed transactions, undo segment inforamtion, and schema and object management statements.
A transaction not being 'logged' into redo log files includes issuing 'NOLOGGING' clause in the statement or when using direct load insert.

Redo log files are group based, and at two online redo log groups are required.

Online Redo Log File Groups:

• A set of identical copies of online redo log files is called an online redo log file group.
• The LGWR background process concurrently writes the same information to all online redo log files in a group.
• The Oracle server needs a minimum of two online redo log file groups for the normal operation of a database.

Online Redo Log File Memebers

• Each online redo log file in a groiup is called a member.
• Each member in a group has identical log sequence numbers and are of the same size. The log sequence number is assigned each time that the oracle server writes to a log group to uniquely identify each online redo log file. The current log sequence number is tored in the control file and in the header of all data files.

The initial set of online redo log file groups and members are created during the database creation:
Parameters below limit the number of online redo log files:

• MAXLOGFILES: it's the parameter in CREATE DATABASE command specifies the absolute maximum of online redo log file groups.
• The maximum and default value for MAXLOGFILES depends on operating system.
• MAXLOGMEMBERS: the parameter in CREATE DATABASE command determines the maximum number of members per redo log file group.

You need to know the mechanism of how Online Redo Log Files work to be able to utilize it ensuing the data availability.
The Oracle server sequentially records all changes made to the database in the Redo Log Buffer. The redo entries are written from the Redo Log Buffer to the current online redo log file group by the LGWR process. LGWR writes under the following situations:

• When a transaction commites
• When the Redo LOg Buffer becomes one-third full
• When there is more than a megabyte of changed records in the Redo Log Buffer
• Before the DBWn writes modified blocks in the Database Buffer Cache to the data files

Log Switches
LGWR writes to the online redo log files sequenctially. When the current online redo log file group is filled, LGWR begins writing to the next group. This is called a log switch.

Checkpoints
During a checkpoint:

• DBWn writes a number of dirty database buffers, which are covered by the logthat is being checkpointed, to the data files.
• The checkpoint backgroud process CKPT updates the control file to reflect that it has completed a checkpoint successfully. If the checkpint is caused by a log switch, CKPT also updates the headers of the data files.

A checkpint occurs in the following sutuations:

• At every log switch
• When an instance has been shut down with the normal, trasactional, or immediate option
• When forced by setting the FAST_START_MTTR_TARGET initialization parameter
• When manually requested by the database administrator
• When the ALTER TABLESPACE [OFFLINE NORMAL|READ ONLY|BEGIN BACKUP] command causes checkpointing on specific data files

You can force log switches and checkpoints as below:

• Forcing a log switch:

SQL> ALTER SYSTEM SWITCH LOGFILE;

• Checkpoints can be forced by setting FAST_START_MTTR_TARGET parameter, this parameter force the Oracle instance to reach the goal of that instance recovery should not take more than certain seconds, in this case 600 seconds:

FAST_START_MTTR_TARGET = 600

• ALTER SYSTEM CHECKPOINT command

ALTER SYSTEM CHECKPOINT;

Adding Online Redo Log File Groups:
In some cases you might need to create additional log file groups. To create a new group of online redo log files, use the following SQL command:
         ALTER DATABASE [database]
            ADD LOGFILE [GROUP interger] filespec
               [, [GROUP integer] filespec]...]
Ex:

ALTER DATABASE ADD LOGFILE GROUP 3 (‘$HOME/ORADATA/u01/log3a.rdo’,   ‘$HOME/ORADATA/u02/log3b.rdo’) Size 1M;

Adding new member to exsiting online redo log group:

You can also add new members to existing online redo log file groups using the following command:

       
                 ALTER DATABASE [database]
                      ADD LOGFILE MEMBER
                         [ ‘FILENAME’ [REUSE] [, ‘FILENAME’ [REUSE]…
                       TO {GROUP integer
                       | (‘filename’ [, ‘filename’]…)
                      }  ]…

Ex:
 

ALTER DATABASE ADD LOGFILE MEMBER ‘$HOME/ORADATA/u04/log1c.rdo’ TO GROUP1, ‘$HOME/ORADATA/u04/log2c.rdo’ TO GROUP2, ‘$HOME/ORADATA/u04/log3c.rdo’ TO GROUP3;

Dropping Online Redo Log File Groups:

You can also drop the Online Redo Log File Groups with following command:

                   ALTER DATABASE [database]
                           DROP LOGFILE {GROUP integer| ('filename' [,'filename']...)}
                             [,{GROUP integer| ('filename'[,'filename']...)}]...

Ex:

 

ALTER DATABASE DROP LOGFILE GROUP 3;

Dropping Online Redo Log File Members:

You may drop an online redo log file member by following command:

            ALTER DATABASE [database]
                    DROP LOGFILE MEMBER 'filename' [, 'filename']...

Ex:

 

ALTER DATABASE DROP LOGFILE MEMBER ‘$HOME/ORADATA/u04/log3c.rdo’;

Relocating or Renaming Online Redo Log Files:

Some time you need to rename an online redo log file. To do this, you go through the following steps to implement this:

1. Shut down the database                                                                                                                                               SQL> shutdown;

2. Copy the online redo log fiels to the new location.

3. Startup the databae and mount, but do not open it.                                                                     

           SQL> CONNECT /  as SYSDBA

           SQL> STARTUP MOUNT;

4. Rename the online redo log memebers using the ALTER DATABASE RENAME FILE:

Ex:

ALTER DATABASE RENAME FILE    ‘$HOME/ORADATA/u01/log2a.rdo’      TO  ‘$HOME/ORADATA/u02/log1c.rdo’;

5. Open the database for normal operation:

      SQL> ALTER DATABASE OPEN;

Clearing Online Redo Log Files

ALTER DATABASE CLEAR LOGFILE command can be used to reinitialize an online redo log file.

Ex:

 

ALTER DATABASE CLEAR LOGFILE GROUP 2;

Use the UNARCHIVED keyword to avoid archiving the corrupted online reedo log file

Ex:

 

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

There are a coupld of dynamic views for online redo log files that you can retrive information of online redo log files,ex:

• V$LOG
• V$LOGFILE

Archived Redo Log Files

• filled online redo log fiels can be archived.
• There are two advantages in running the database in ARCHIVELOG mode and archiving online redo  log files:
• Recovery: A database backup together with online and archived redo log fiels can quarantee recovery of all committed transactions.
• Backup: This can be performed while the database is open.
• By default, the database is created in NOARCHIVELOG mode.
• Accomplished manually through SQL statements
• When successfullyu archived:
• An entry in the control file is made
• Records: archive log name, log sequence number, and high and low system change number (SCN).
• Filled online redo log files cannot be reused until:
• A checkpoint has taken place
• File has been archived by ARCn
• Can be multiplexed
• Maintained by the DBA

Note: The archived log files is one important point for backup and recovery, which will be discussed more detail in the Fundamental II.