Redo Log Files

1. Adding A New redo log Group

sql> ALTER DATABASE ADD LOGFILE('/oracledata/test/redo3a.log','/test_multiplex/redo3b.log') SIZE 52429312;

The command above will add a new redo log group to your database. The group will have two members with size 52429312. A group number will automatically be assigned to your new group. If you want to set it explicitly you may use command below.

sql> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 15 ('/oracledata/test/redo3a.log','/test_multiplex/redo3b.log') SIZE 52429312;

In a RAC (Real Application Clusters) environment you have to specify which instance the redo log group will belong to by setting thread number. Every instance in a RAC database has a thread number. For ex: the command below will add the redo log group to instance with thread number 1.

sql> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 15 ('/oracledata/test/redo3a.log','/test_multiplex/redo3b.log') SIZE 52429312;

2. Dropping A redo log Group

Before dropping a redo log group, make sure that at the moment, logwriter process is not writing to the redo log file you plan to drop.

sql> SELECT GROUP#, THREAD#,ARCHIVED, STATUS FROM V$LOG ;

GROUP#	THREAD#	ARC	STATUS
1	1	YES	INACTIVE
2	1	NO	CURRENT
3	1	YES	INACTIVE

The value for "ARCHIVED" column must be "YES" and STATUS column must be "INACTIVE".

If the status is "CURRENT" it means that logwriter is currently using this group to store redo records. After the redo files in this group is filled it will switch to next group. You may wait till next log switch operation or you may manually trigger it by command below:

SQL> alter system switch logfile ;

If status is "ACTIVE" it means that there are some redo log records, which have not been applied to datafiles yet. The status is supposed to turn to INACTIVE soon after the next checkpoint operation. If you do not want to wait till then you may manually trigger checkpoint by command below.
 

sql> alter system checkpoint ;

If the status has turned to INACTIVE you may drop it by specifying the group number.

sql> ALTER DATABASE DROP LOGFILE GROUP 6;

This command will drop the redo log group, which is identified by number 6.
Logwriter writes to redo log files in a circular fashion. When a group is filled it moves to next group. After all groups in database are filled it returns back to first redo log group. Because of that, there has to be at least 2 groups in an instance. Oracle won't permit dropping a group if there are only two groups in the instance.
After dropping a group, you may delete relevant files from operating system.

3. Adding A New Member To An Existing Group

As mentioned at the beginning of article it is recommended to multiplex redo log members for fault tolerance. You can accomplish this by adding new members to an existing group.

sql> ALTER DATABASE ADD LOGFILE MEMBER '/u02/oradata/mydb/redo02.log' TO GROUP 2;

The above command adds a new redo log file to redo log group with number 2.

4. Dropping A Member From An Existing Group

As stated in subject 3 (Dropping A redo log Group) again the group, whose member you want to drop should be inactive.

sql> ALTER DATABASE DROP LOGFILE MEMBER '/u03/oradata/mydb/redo03.log'

5. Relocating A redo log Group

You may want to relocate your existing redo log group and change the path of redo log files. Here are the steps to accomplish it
Step 1: Shutdown database

sql> shutdown immediate ;

Step 2: Backup your existing control file and redo log file at operating system level.
Step 3: Move your redo log member to a new location. You may also change the file name if you want.

# mv /u02/oradata/mydb/redo02.log /u03/oradata/mydb/redo03.log

Step 4: Startup in nomount mode

sql> startup nomount;

Step 5: Path of redo log files is kept in control file. You have to update that record by command below:

sql> ALTER DATABASE RENAME FILE '/u02/oradata/mydb/redo02.log' TO '/u03/oradata/mydb/redo03.log'

Control file is modified and the path of redo log member is updated. Now you can open your database.

sql> alter database open;

     As you see this way of relocating online redo log files requires downtime.  You may also change the path of your redo log files by adding a new group at desired location and dropping the ones at former location. This approach requires no downtime.

*****************************************************************

Yesterday we found and realized that there has been too much log switching in one of our databases and it was impacting the database performance. This excessive log switching was happening because our Redo Log files were not enough large as per the the database activity.
We had 50 MB Redo Log Files, 1 Redo Thread, 2 Redo Log Groups and One member in each group.
We decided to increase the Redo Log size to 100MB.
The Redo Logs must be dropped and recreated for changing the redo log size. It can be done online without shutting down the database. However, you need to make sure that the Redo Group being dropped should be INACTIVE when you do this.
We have the following Log Groups and members:

SQL> select * from v$logfile;

 GROUP# STATUS   TYPE    MEMBER                                        IS_RECOVE
------- ------ -------- ---------------------------------------------- ---------
 2              ONLINE   /app01/oratest/oradata/BOTI/BOTI/redo02.log    NO
 1              ONLINE   /app01/oratest/oradata/BOTI/BOTI/redo01.log    NO

And the status of the Log Groups is:

SQL> select * from v$log;

 GROUP#   THREAD#  SEQUENCE# BYTES    MEMBERS ARCHIVED  STATUS  FIRST_CHANGE# FIRST_TIME
-------- --------- --------- -------- ------- -------- -------- ------------- ---------------
 1         1        12       52428800  1       NO      CURRENT  1156736         27-JAN-11
 2         1        11       52428800  1       YES     ACTIVE   1156732         27-JAN-11

Here we see that the Group# 1 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:
For this, execute a checkpoint:

SQL> alter system checkpoint;

System altered.

Now again check the status of the Redo Groups:

SQL> select * from v$log;

 GROUP#  THREAD# SEQUENCE#  BYTES     MEMBERS ARCHIVED STATUS    FIRST_CHANGE# FIRST_TIME
-------- ------- --------- ---------- ------- -------- --------- ------------- ---------------
 1        1       12        52428800    1     NO       CURRENT    1156736      27-JAN-11
 2        1       11        52428800    1     YES      INACTIVE   1156732      27-JAN-11

The group 2 is now Inactive. Hence we can proceed with dropping this group:

SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01567: dropping log 2 would leave less than 2 log files for instance BOTI (thread 1)
ORA-00312: online log 2 thread 1: '/app01/oratest/oradata/BOTI/BOTI/redo02.log'

What went wrong ???
It’s a basic requirement in Oracle Database that there should always be a minimum 2 Redo Log Groups available with the Database. Hence we can not drop any Redo Group if there are only 2 Groups.
To overcome this issue, we need to add one more Redo group to the database.
Execute the following step:

SQL> alter database add logfile group 3 '/app01/oratest/oradata/BOTI/BOTI/redo03.log' size 100M;

Database altered.

Now check the logfiles:

SQL> select * from v$logfile;

 GROUP# STATUS  TYPE    MEMBER                                       IS_RECOVE
------- ------- ------- -------------------------------------------- ---------
 3              ONLINE  /app01/oratest/oradata/BOTI/BOTI/redo03.log    NO
 2              ONLINE  /app01/oratest/oradata/BOTI/BOTI/redo02.log    NO
 1              ONLINE  /app01/oratest/oradata/BOTI/BOTI/redo01.log    NO

… and the status of the Groups:

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES     MEMBERS ARCHIVED STATUS    FIRST_CHANGE# FIRST_TIME
------- ------- --------- --------- ------- -------- --------- ------------- ---------------
 1       1       12        52428800  1      NO       CURRENT   1156736       27-JAN-11
 2       1       11        52428800  1      YES      INACTIVE  1156732       27-JAN-11
 3       1        0       104857600  1      YES      UNUSED    0

The status of the new Group is UNUSED because it has never been used.
Now, we have 3 Redo Groups in our database. We can now proceed with Dropping Redo Group# 2.

SQL> alter database drop logfile group 2;

Database altered.

Also, delete the file ‘/app01/oratest/oradata/BOTI/BOTI/redo02.log’ from File system also.
Now add the Redo Group 2 back to the database with changed Redo size:

SQL> alter database add logfile group 2 '/app01/oratest/oradata/BOTI/BOTI/redo02.log' size 100M;

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE    MEMBER                                       IS_RECOVE
------ ------ ------ --------------------------------------------- ---------
 3            ONLINE  /app01/oratest/oradata/BOTI/BOTI/redo03.log  NO
 2            ONLINE  /app01/oratest/oradata/BOTI/BOTI/redo02.log  NO
 1            ONLINE  /app01/oratest/oradata/BOTI/BOTI/redo01.log  NO

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES     MEMBERS ARCHIVED STATUS  FIRST_CHANGE# FIRST_TIME
------- ------- --------- --------- ------- -------- ------- ------------- ---------------
 1       1      12         52428800    1     NO      CURRENT 1156736        27-JAN-11
 2       1      0         104857600    1     YES     UNUSED  0
 3       1      0         104857600    1     YES     UNUSED  0

Now we have to drop the 1st Redo Log Group. Before that, we need to change the status of this group:

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES     MEMBERS ARCHIVED STATUS  FIRST_CHANGE# FIRST_TIME
------ ------- --------- --------- ------- -------- ------- ------------- ---------------
 1      1       12        52428800   1      YES     ACTIVE  1156736        27-JAN-11
 2      1       13       104857600   1      NO      CURRENT 1157376        27-JAN-11
 3      1        0       104857600   1      YES     UNUSED  0

Still the Grpoup is in Active status. Issue a checkpoint:

SQL> alter system checkpoint;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES     MEMBERS ARCHIVED STATUS   FIRST_CHANGE# FIRST_TIME
------ ------- --------- --------- ------- -------- -------- ------------- ---------------
 1      1       12        52428800  1       YES     INACTIVE 1156736       27-JAN-11
 2      1       13       104857600  1       NO      CURRENT  1157376       27-JAN-11
 3      1        0       104857600  1       YES     UNUSED   0

Now we can drop the Redo Group# 1.

SQL> alter database drop logfile group 1;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES     MEMBERS ARCHIVED STATUS  FIRST_CHANGE# FIRST_TIME
------ ------- --------- --------- ------- -------- ------- ------------- ---------------
 2      1       13       104857600   1     NO       CURRENT 1157376       27-JAN-11
 3      1        0       104857600   1     YES      UNUSED  0

SQL> select * from v$logfile;

GROUP# STATUS TYPE   MEMBER                                      IS_RECOVE
------ ------ ------ ------------------------------------------- ---------
 3            ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log NO
 2            ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO

Delete the file ‘/app01/oratest/oradata/BOTI/BOTI/redo01.log’ from OS.
Now we need to add this group back to the database with 100MB Redo Log file:

SQL> alter database add logfile group 1 '/app01/oratest/oradata/BOTI/BOTI/redo01.log' size 100M;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES     MEMBERS ARCHIVED STATUS  FIRST_CHANGE# FIRST_TIME
------ ------- --------- --------- ------- -------- ------- ------------- ---------------
 1        1      0       104857600   1     YES      UNUSED  0
 2        1     13       104857600   1     NO       CURRENT 1157376       27-JAN-11
 3        1      0       104857600   1     YES      UNUSED  0

Here we can see that all the Redo Groups are now showing 100MB size (column BYTES).

SQL> select * from v$logfile;

GROUP# STATUS TYPE   MEMBER                                      IS_RECOVE
------ ------ ------ ------------------------------------------- ---------
 3            ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log NO
 2            ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
 1            ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO

Though it’s suggested that you should keep 3 groups, if you want you can drop the Redo Group 3 which we added for this exercise.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE   MEMBER                                      IS_RECOVE
------ ------ ------ ------------------------------------------- ---------
 2            ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
 1            ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES     MEMBERS ARCHIVED STATUS  FIRST_CHANGE# FIRST_TIME
------ ------- --------- --------- ------- -------- ------- ------------- ---------------
 1       1       0       104857600   1     YES      UNUSED  0
 2       1      13       104857600   1     NO       CURRENT 1157376       27-JAN-11

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES     MEMBERS ARCHIVED STATUS  FIRST_CHANGE# FIRST_TIME
------ ------- --------- --------- ------- -------- ------- ------------- ---------------
 1      1         14     104857600  1      NO       CURRENT 1157584       27-JAN-11
 2      1         13     104857600  1      YES      ACTIVE  1157376       27-JAN-11

You have changed the Redo Log file size from 50M to 100M.

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