http://www.oracle-dba-online.com/managing_redo_logfiles.htm

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

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

 

---

http://neeraj-dba.blogspot.nl/2011/11/how-to-resize-redolog-file-in-oracle.html

How to resize redolog file in oracle

We cannot resize the redo log files. We must drop the redolog file and recreate them .This is only method to resize the redo log files. A database requires atleast two groups of redo log files,regardless the number of the members. We cannot the drop the redo log file if its status is current or active . We have change the status to "inactive" then only we can drop it.

When a redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structure. After dropping a redo log file, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped redo log file. In my case i have four redo log files and they are of 50MB in size .I will resize to 100 MB.  Below are steps to resize the redo log files.

Step 1 : Check the Status of Redo Logfile
 SQL>  select group#,sequence#,bytes,archived,status from v$log;
     GROUP#  SEQUENCE#      BYTES    ARC    STATUS
 ----------    ----------    ----------      -----       -------------
         1          5   52428800      YES          INACTIVE
         2          6   52428800      YES          ACTIVE
         3          7   52428800      NO           CURRENT
         4          4   52428800      YES          INACTIVE

 Here,we cannot drop the current and active redo log file .

Step  2 :  Forcing a Checkpoint  :

The SQL statement alter system checkpoint explicitly forces Oracle to perform a checkpoint for either the current instance or all instances. Forcing a checkpoint ensures that all changes to the database buffers are written to the datafiles on disk .A global checkpoint is not finished until all instances that require recovery have been recovered.

 SQL> alter system checkpoint global ;
 system altered.

 SQL> select group#,sequence#,bytes,archived,status from v$log;

     GROUP#    SEQUENCE#        BYTES    ARC       STATUS
 ----------    ----------    ----------    -----     ----------------
         1          5      52428800     YES       INACTIVE
         2          6      52428800     YES       INACTIVE
         3          7      52428800     NO        CURRENT
         4          4      52428800     YES       INACTIVE
 Since the status of group 1,2,4 are inactive .so we will drop the group 1 and group 2 redo log file.

Step  3  :  Drop Redo Log File :
 SQL> alter database drop logfile group 1;
 Database altered.

 SQL> alter database drop logfile group 2;
 Database altered.

 SQL>  select group#,sequence#,bytes,archived,status from v$log;
     GROUP#  SEQUENCE#      BYTES    ARC    STATUS
 ----------    ----------    ----------    ---     ----------------
         3          7             52428800       NO       CURRENT
         4          4             52428800       YES      INACTIVE

Step  4  : Create new redo log file

If we don't delete the old redo logfile by OS command when creating the log file with same name then face the below error . Therefore to solve it delete the file by using OS command .

 SQL> alter database add logfile group 1 'C:\app\neerajs\oradata\orcl\redo01.log' size 100m;
 alter database add logfile group 1 'C:\app\neerajs\oradata\orcl\redo01.log' size 100m
 *
 ERROR at line 1:
ORA-00301: error in adding log file 'C:\app\neerajs\oradata\orcl\redo01.log' - file cannot be created
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

 SQL> alter database add logfile group 1 'C:\app\neerajs\oradata\orcl\redo01.log' size 100m;
 Database altered.

 SQL> alter database add logfile group 2 'C:\app\neerajs\oradata\orcl\redo02.log' size 100m;
 Database altered.

 SQL>  select group#,sequence#,bytes,archived,status from v$log;
     GROUP#      SEQUENCE#      BYTES     ARC       STATUS
 ----------    ----------     ----------       ---      ----------------
          1          0      104857600      YES     UNUSED
          2          0      104857600      YES     UNUSED
          3          7       52428800      NO      CURRENT
          4          4       52428800      YES     INACTIVE

Step 5 :  Now drop the remaining two old redo log file
 SQL> alter system switch logfile ;
 System altered.

 SQL> alter system switch logfile ;
 System altered.

 SQL>  select group#,sequence#,bytes,archived,status from v$log;
     GROUP#  SEQUENCE#      BYTES ARC STATUS
 ---------- ---------- ---------- --- ----------------
          1          8  104857600     YES     ACTIVE
          2          9  104857600     NO      CURRENT
          3          7   52428800     YES     ACTIVE
          4          4   52428800     YES     INACTIVE

 SQL> alter system checkpoint global;
 System altered.

 SQL>  select group#,sequence#,bytes,archived,status from v$log;
     GROUP#  SEQUENCE#      BYTES ARC STATUS
 ---------- ---------- ---------- --- ----------------
          1          8    104857600     YES     INACTIVE
          2          9    104857600     NO      CURRENT
          3          7     52428800     YES     INACTIVE
          4          4     52428800     YES     INACTIVE

 SQL> alter database drop logfile group 3;
 Database altered.

 SQL> alter database drop logfile group 4;
 Database altered.

 SQL>  select group#,sequence#,bytes,archived,status from v$log;
     GROUP#  SEQUENCE#      BYTES ARC STATUS
 ---------- ---------- ---------- --- ----------------
          1          8  104857600      YES      INACTIVE
          2          9  104857600      NO       CURRENT

Step 6 : Create the redo log file
 SQL> alter database add logfile group 3 'C:\app\neerajs\oradata\orcl\redo03.log' size 100m;
 Database altered.

 SQL> alter database add logfile group 4 'C:\app\neerajs\oradata\orcl\redo04.log' size 100m;
 Database altered.

 SQL>  select group#,sequence#,bytes,archived,status from v$log;
     GROUP#  SEQUENCE#      BYTES ARC STATUS
 ---------- ---------- ---------- --- ----------------
          1          8        104857600     YES       INACTIVE
          2          9        104857600     NO        CURRENT
          3          0        104857600     YES       UNUSED
          4          0        104857600     YES       UNUSED