Redo logs

Adding a New Redo Logfile Group
To add a new Redo Logfile group to the database give the following command
SQL>alter database add logfile group 3
‘/u01/oracle/ica/log3.ora’ size 10M;
Note: You can add groups to a database up to the MAXLOGFILES setting you have specified at the time of creating the database. If you want to change MAXLOGFILE setting you have to create a new controlfile.
Adding Members to an existing group
To add new member to an existing group give the following command
SQL>alter database add logfile member
‘/u01/oracle/ica/log11.ora’ to group 1;
Note: You can add members to a group up to the MAXLOGMEMBERS setting you have specified at the time of creating the database. If you want to change MAXLOGMEMBERS setting you have create a new controlfile
Important: Is it strongly recommended that you multiplex logfiles i.e. have at least two log members, one member in one disk and another in second disk, in a database.
Dropping Members from a group
You can drop member from a log group only if the group is having more than one member and if it is not the current group. If you want to drop members from the current group, force a log switch or wait so that log switch occurs and another group becomes current. To force a log switch give the following command
SQL>alter system switch logfile;
The following command can be used to drop a logfile member
SQL>alter database drop logfile member ‘/u01/oracle/ica/log11.ora’;
Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.
Dropping Logfile Group
Similarly, you can also drop logfile group only if the database is having more than two groups and if it is not the current group.
SQL>alter database drop logfile group 3;
Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.
Resizing Logfiles
You cannot resize logfiles. If you want to resize a logfile create a new logfile group with the new size and subsequently drop the old logfile group.
Renaming or Relocating Logfiles
To Rename or Relocate Logfiles perform the following steps
For Example, suppose you want to move a logfile from ‘/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log1.ora’, then do the following
Steps
1. Shutdown the database
SQL>shutdown immediate;
2. Move the logfile from Old location to new location using operating system command
$mv /u01/oracle/ica/log1.ora /u02/oracle/ica/log1.ora
3. Start and mount the database
SQL>startup mount
4. Now give the following command to change the location in controlfile
SQL>alter database rename file ‘/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log2.ora’;
5. Open the database
SQL>alter database open;
Clearing REDO LOGFILES
A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used reinitialize the file without shutting down the database.
The following statement clears the log files in redo log group number 3:
ALTER DATABASE CLEAR LOGFILE GROUP 3;
This statement overcomes two situations where dropping redo logs is not possible:
• If there are only two log groups
• The corrupt redo log file belongs to the current group
If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
This statement clears the corrupted redo logs and avoids archiving them. The cleared redo logs are available for use even though they were not archived.
If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. The database writes a message in the alert log describing the backups from which you cannot recover
Viewing Information About Logfiles
To See how many logfile groups are there and their status type the following query.
SQL>SELECT * FROM V$LOG;
GROUP# THREAD# SEQ BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
------ ------- ----- ------- ------- --- --------- ------------- ---------
1 1 20605 1048576 1 YES ACTIVE 61515628 21-JUN-07
2 1 20606 1048576 1 NO CURRENT 41517595 21-JUN-07
3 1 20603 1048576 1 YES INACTIVE 31511666 21-JUN-07
4 1 20604 1048576 1 YES INACTIVE 21513647 21-JUN-07
To See how many members are there and where they are located give the following query
SQL>SELECT * FROM V$LOGFILE;
GROUP# STATUS MEMBER
------ ------- ----------------------------------
1 /U01/ORACLE/ICA/LOG1.ORA
2 /U01/ORACLE/ICA/LOG2.ORA











ADDED

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 5;

Database altered.