Chapter 5: MAINTAINING ONLINE REDO LOG FILES

Chapter 5: MAINTAINING ONLINE REDO LOG FILES

CHAPTER # 5

MAINTAINING ONLINE REDO LOG FILES

ONLINE REDO LOG FILES:

Online redo log file have the following characteristics:

1. Record all changes made to data
2. Provide recovery mechanism
3. Can be organized into group
4. At least two group required

Online redo log files are used in a situation such as an instance failure to recover committed data that has not yet been written to the data files.

STRUCTURE OF ONLINE REDO LOG FILES:

The DBA can set up oracle database to maintain copies of online redo log files to avoid losing information of single point failure.

ONLINE REDO LOG FILE GROUP:

1. A set of identical copies of online redo log files (means members) is called an online redo log file group.
2. The LGWR background process concurrently writes the same information to all online redo log files in a group.
3. the oracle server need a minimum of two online redo log file group for the normal operation of a database.

ONLINE REDO LOG FILE MEMBERS:

1. Each online redo log file in a group is called a member
2. Each member in a group has identical log sequence numbers and are the same size.
3. The current log sequence number stored in the control file and in the header of all data files.

CREATING INITIAL ONLINE REDO LOG FILES:

The initial set of online redo log file group and members are created during the database creation.

MAXLOGFILES: à define maximum online redo log file group

MAXLOGMEMBERS: à define maximum member in a group

Note:

The maximum and default value of MAXLOGFILES AND MAXLOGMEMBERS is depending on operating system.

HOW ONLINE REDO LOG FILES WORK:

The oracle server sequentially record 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 LGWR background process.

LGWR writes under the following situations:

1. when a transaction commit
2. when 1/3 full
3. when more than 1 MB full
4. Before DBWn writes

Online redo log files are used in a cyclic fashion.

Each online redo log file group is identified by a log sequence number that is overwritten each time the log is reused.

LOG SWITCHES:

LGWR writes to the online redo log files sequentially.

When the current online redo log file group is filled.

LGWR begins writing to the next group. This is called LOG SWITCH.

When the last available online redo log file is filled. LGWR return to the first online redo log group and start writing again.

CHECKPOINTS:

The checkpoint background process update the control file to reflect that it has completed a checkpoint successfully.

If the checkpoint is caused by log switch, CKPT also updates the header of the data files.

Check point can occur all the data file in the database or only specific data files.

CKPT occurs in the following situations:

1. At every log switch
2. when instance has been shutdown normal, transactional, or immediate option
3. when manually requested of the DBA
4. when forced by setting the FAST_START_MTTR_TARGET initialization parameter
5. when ALTER TABLESPACE [offiline normal | read only | begin backup ]

Information about CKPT is recorded in the alert_SID.log file if the

LOG_CHECKPOINT_TO_ALERT initialization parameter is set true.

The default value is FALSE.

FORCING LOG SWITCHES AND CKPT:

Log switches and checkpoints are automatically performed at certain point in the operation of database

But however

DBA can force a log switch & checkpoint by the following commands.

SQL > ALTER SYSTEM SWITCH LOGFILE;

SQL > ALTER SYSTEM CHECKPOINT;

Chech point can be forced by:

Setting FAST_START_MTTR_TARGET parameter

SQL > FAST_START_MTTR_TARGET = 600

Old parameters used before this are

FAST_START_IO_TARGET

LOG_CHECKPOINT_TIMEOUT these are depreciated and can not be used

PRACTICAL #

Before adding group and members first check how many group already in the database using the view

SQL > select * from V$LOG;

SQL > select * from V$LOGFILE;

ADDING ONLINE REDO LOG FILE GROUPS

In some cases you may need to create additional log file group. To solve availability problem this can be done by SQL command.

ALTER DATABASE ADD LOGFILE GROUP 4

('D:\ORACLE\ORADATA\ORCL\REDO04A.LOG' ,

 'D:\ORACLE\ORADATA\ORCL\REDO04B.LOG')

SIZE 1M;

ADDING ONLINE REDO LOG FILE MEMBER

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

ALTER DATABASE ADD LOGFILE MEMBER

 'D:\ORACLE\ORADATA\ORCL\REDO04C.LOG' TO GROUP 4 ,

 'D:\ORACLE\ORADATA\ORCL\REDO03B.LOG' TO GROUP 3

When adding member use the fully specified name of the log file members,

Otherwise the files are created in a default directory of the database server.

DROPPING ONLINE REDO LOGFILE GROUPS

Online redo log file group can be dropped by the SQL command

ALTER DATABASE DROP LOGFILE GROUP 4;

RESTRICTIONS:

While deleting online redo log file group we must consider the following restrictions

1. An instance requires at least 2 group of online redo log files.
2. An ACTIVE and CURRENT group cannot be dropped.
3. When an online redo log file group is dropped, the operating system file are nat deleted.

NOTE: If database is in archive mode group can not be dropped until archival process not completely done. Even group is in INACTIVE MODE

DROPPING ONLINE REDO LOG FILE MEMBERS

We can drop online redo log file member by using SQL statement

ALTER DATABASE DROP LOGFILE MEMBER

'D:\ORACLE\ORADATA\ORCL\REDO02B.LOG';

RESTRICTIONS:

While deleting online redo log file members we must consider the following restrictions

1. We cannot drop the last valid member of any group
2. If the group is current, you must force switch log file before you can drop the member.
3. If the database is in archived mode and the log file group to which the member belongs is not archived, the member can not be dropped
4. when a online redo log file member is dropped, the operation system file cannot be deleted.

REALLOCATING OR RENAMING ONLINE REDO LOG FILES

The location of online redo log files can be changed by renaming the online redo log files.

Before renaming the online redo log files, ensure that the new online redo log file can exists.

Relocate or rename online redo log files in one of the 2 following ways:

1. Add new members and drop old members
2. Alter database rename file command

PRACTICAL #

ALTER DATABASE RENAME FILE COMMAND use following steps:

STEP # 1:

Shutdown the database

SQL > SHUTDOWN;

STEP # 2:

Copy the online redo log file to the new location in operating system

STEP # 3:

To start database at mount

SQL > STARTUP MOUNT;

STEP # 4:

Rename the online redo log file member using the

SQL >ALTER DATABASE RENAME FILE

           ‘D:\ORACLE\ORADATA\ORCL\REDO03C.LOG’

           TO 
           ‘D:\ORACLE\ORADATA\REDO03B.LOG’;

CLEARING ONLINE REDO LOG FILES

An online redo log file might become corrupt while the database is open , and ultimately stop database activity because activity cannot continue.

In this situation ALTER DATABASE CLEAR LOGFILE command can be used to reinitialize the online redo log file with out shutdown the database.

The command can overcome two situations where dropping online redo log file is not possible:

• If the are only two log groups
• The corrupt online redo log file belongs to the current group

Use the UNARCHIVED keyword in the command to clear the corrupted online file to avoid archiving.

ALTER DATABASE CLEAR LOGFILE GROUP 2;

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

ONLINE REDO LOG FILE CONFIGURATION

To determine the appropriate number of online redo log files for a database, you must test different configurations.

Oracle server multiplexed group can contain different no. of members this is called asymmetric configuration (means all members in a group placed on same disk)

In this case we get temporary result because of disk failure

Oracle recommended try to built symmetric configuration (means member of any group also placed on different location)

By doing this if one member is not available the other is available and instance does not shutdown

Separate archive log file and online redo log files on different disks to reduce contention b/w the ARCn and LGWR

Data files and online files should be placed on different disk to reduce contention b/w LGWR and DBWn

SIZE OF ONLINE REDO LOG FILE

The minimum size of online redo log file is 50KB  and

Maximum size is specifying in the operating system.

MANAGING ONLINE REDO LOG FILES WITH (OMF)

Define the DB_CREATE_ONLINE_LOG_DEST_n parameter

To create online redo log file managed by OMF.

DB_CREATE_ONLINE_DEST_1

DB_CREATE_ONLINE_DEST_2

The name will be created automatically and displayed in alertSID.log 

The default size is 100 MB

GROUP CAN BE ADDED with no file specifications

ALTER DATABASE ADD LOGFILE ;

DROPPING A GROUP

ALTER DATABASE DROP FOGFILE GROUP 3;

 ARCHIVED LOG FILE cannot be created by OMF.

OBTAINING INFORMATION ABOUT GROUP AND MEMBERS

SQL > Select * from V$log;

SQL > Select * from V$logfile;

In the V$log views the status column having the values

1. UNUSED:

Indicates that the online redo log file group has never been written to

2. CURRENT:

Indicates the current online redo log file group. This implies that the online redo log file group is active.

3. ACTIVE:

Indicates that the online redo log file group is active but is not the current online        redo log file group. It is needed for crash recovery.

4. INACTIVE:

Indicates that the online redo log file group is no longer needed for instance recovery.

In the V$logfile views the status column having the values

1. INVALID:

Indicates that the file is inaccessible

2. STALE:

Indicates that the contents of the file are incomplete

3. DELETED:

Indicates that the file is no longer used

4. BLANK

Indicates that the file is in use.

  --------------------------------  END OF CHAPTER 05  -----------------------------