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
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;
While deleting online redo log file group we must consider the following restrictions
- An instance requires at least 2 group of online redo log files.
- An ACTIVE and CURRENT group cannot be dropped.
- 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
While deleting online redo log file members we must consider the following restrictions
- We cannot drop the last valid member of any group
- If the group is current, you must force switch log file before you can drop the member.
- 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
- 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:
- Add new members and drop old members
- 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
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
Indicates that the online redo log file group has never been written to
Indicates the current online redo log file group. This implies that the online redo log file group is 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.
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
Indicates that the file is inaccessible
Indicates that the contents of the file are incomplete
Indicates that the file is no longer used
Indicates that the file is in use.