MAINTAINING ONLINE REDO LOG FILES

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

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.

`

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.

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.