INCREASING REDO LOG SIZE

Step 1 : Check the Status of Redo Logfile

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

GROUP#    STATUS

------------    ------------

             1     INACTIVE

             2     ACTIVE

             3     CURRENT

Here we see that the Group# 3 is being used Currently and the Group# 2 though not being used Currently, however is ACTIVE (means if the Database crashes now, you will need this Group for recovery.) We need to make this group Inactive before proceeding ahead:

Step  2 :  Forcing a Checkpoint  :

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

Step 3 : checking the  status of the log

GROUP#    STATUS

------------    ------------

             1     INACTIVE

             2     INACTIVE

             3     CURRENT

Since the status of group 1,2  are inactive .so we will drop the group 1 and group 2 redo log file.

Step 4 : Check the path for the all the redo logs

 SQL>  SELECT * FROM V$LOGFILE;

Step 5 : Drop Redo Log File  group 1

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Check it has dropped

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

Step  6  : Create new redo log file for group 1

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 '/redo/PEMSAPMS/redo01.log' size 200m;

Step 7 : Drop Redo Log File  group 2

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Step  8  : Create new redo log file for group 2

sql> alter database add logfile group 2 '/redo/PEMSAPMS/redo02.log' size 200m;

Step 9: Make the redo logs created active

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#    STATUS

------------    ------------

             1     ACTIVE

             2     INACTIVE

             3     CURRENT

Step 10 : Drop Redo Log File  group 3

SQL> alter system checkpoint global;

System altered.

GROUP#    STATUS

------------    ------------

             1     INACTIVE

             2     CURRENT

             3     INACTIVE

Step 11 : Drop Redo Log File  group 3

 SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Step  12  : Create new redo log file for group 3

 SQL > alter database add logfile group 3 '/redo/PEMSAPMS/redo03.log' size 200m;

Step 13 Make the redo logs created active

alter system switch logfile ;