Online Redo LogFiles Operations

          How to Resize redo log file in oracle

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

    When a redo log member is dropped from the database, the operating system file will 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. For example here i have 4 redo log files and they are of 300MB 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         314572800       YES    INACTIVE

         2          6         314572800       YES    ACTIVE

         3          7         314572800        NO    CURRENT

         4          4         314572800        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;

system altered.

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

GROUP#  SEQUENCE#  BYTES    ARC     STATUS

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

         1          5        314572800     YES      INACTIVE

         2          6        314572800     YES      INACTIVE

         3          7        314572800     NO       CURRENT

         4          4        314572800     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                314572800      NO      CURRENT

         4          4                314572800     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 '/u03/oravis/VIS/data/redo1.log' size 100m;

alter database add logfile group 1 '/u03/oravis/VIS/data/redo1.log' size 100m

*

ERROR at line 1:

ORA-00301: error in adding log file '/u03/oravis/VIS/data/redo1.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 '/u03/oravis/VIS/data/redo1.log' size 100m;

Database altered.

SQL> alter database add logfile group 2 '/u03/oravis/VIS/data/redo2.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      314572800        NO      CURRENT

         4          4      314572800       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   314572800    YES     ACTIVE

         4          4   314572800    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 '/u03/oravis/VIS/data/redo3.log' size 100m;

Database altered.

SQL>alter database add logfile group 4 '/u03/oravis/VIS/data/log4.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

--------------------------------------------------------------------------------
*********************************************************
--------------------------------------------------------------------------------

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u03/oravis/VIS/recovery_area/VIS

Oldest online log sequence     809

Next log sequence to archive   811

Current log sequence           811

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

    GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS           ARC

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

         1        809  314572800          1 INACTIVE         YES

         2        810  314572800          1 INACTIVE         YES

         3        811  314572800          1 CURRENT          NO

SQL> alter system archive log current;

System altered.

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

    GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS           ARC

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

         1        812  314572800          1 CURRENT          NO

         2        810  314572800          1 INACTIVE         YES

         3        811  314572800          1 ACTIVE           YES

SQL> select name from v$archived_log;

NAME

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

/u03/oravis/VIS/recovery_area/VIS/1_805_931529841.dbf

/u03/oravis/VIS/recovery_area/VIS/1_806_931529841.dbf

/u03/oravis/VIS/recovery_area/VIS/1_807_931529841.dbf

/u03/oravis/VIS/recovery_area/VIS/1_808_931529841.dbf

/u03/oravis/VIS/recovery_area/VIS/1_809_931529841.dbf

/u03/oravis/VIS/recovery_area/VIS/1_810_931529841.dbf

/u03/oravis/VIS/recovery_area/VIS/1_811_931529841.dbf

7 rows selected.

SQL> !ls

1_805_931529841.dbf  1_806_931529841.dbf  1_807_931529841.dbf  1_808_931529841.dbf  1_809_931529841.dbf  1_810_931529841.dbf  1_811_931529841.dbf

SQL> !pwd

/u03/oravis/VIS/recovery_area/VIS