How to resize the redo logfile /online redo logfile in Oracle 11g?

Let us we discuss How to resize the redo logfile /online redo logfile in Oracle 11g?

The redo logfile contains the history of changes data in database <

Oracle allows you to have more than one copy of each redo log file, This important feature is called "multiplexing redo log files."

The steps to be followed to resize the redo logfile

Note: before doing the resize of redo logfile we should take a full database backup for safety purpose

 

S-1:

check the group, members list from v$ view

SQL> select group#, members, from V$log group by group#, members; GROUP# MEMBERS --------- ---------- 2 1 3 1 1 1

 

S-2:

check the current redo logfile size



SQL> select group#, members, sum(bytes)/1024/1024  MB  from V$log group by group#,
members;

    GROUP#    MEMBERS         MB
---------- ----------    ----------
         2          1        20
         3          1        20
         1          1        20

 

S-3:

to view the current logfile path


  SQL> select group#,member  from V$logfile;

    GROUP#        MEMBER
--------------   ---------------------------------------------------------------------------

         2          C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_DVNQT05O_.LOG

         1           C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_DVNQSYGO_.LOG

         3           C:\ORACLEXE\APP\ORACLE\ORADATA\XE\REDO03.LOG


  

 

S-4:

To add new redo logfile



 QL> alter database add logfile member 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\REDO04
1.LOG' to group 4;

Database altered.

SQL> alter database add logfile group 5 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\REDO0
5.LOG' size 30m;

Database altered.

SQL> alter database add logfile group 6 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\REDO0
6.LOG' size 30m;

Database altered.

 

S-5:

After adding new redo logfile check that logfile status


SQL> select  group#,status from V$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE
         4 UNUSED
         5 UNUSED
         6 UNUSED

 

S-6:

To active the newly added logfile run this command


SQL>alter system switch logfile;'XE'

 

S-7:

to make inactive old group redo logfile



SQL>alter system checkpoint;


 

S-8:

Now it is a time to drop the "old" groups redo logs



 SQL> alter database drop logfile group 2;

Database altered.


SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.


  

 

S-9:

to view the current status of redo logfile



  SQL> select  group#,status from V$log;

    GROUP# STATUS
---------- ----------------
         4 CURRENT
         5 INACTIVE
         6 INACTIVE

 

Note: Now you have to go in the path of the dropped file groups

Source: Oracle,Google