Changing Redo log file size


My test DB has 50 MB Redo Log Files, 1 Redo Thread, 2 Redo Log Groups and One member in each group, and i will try to change redo file size from 50 MB to 100 MB.

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;

 

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 14-march

2          1         11   52428800          1 YES       ACTIVE                                                 1156732 14-march

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 14-march

2          1         11   52428800          1 YES       INACTIVE                                               1156732 14-march

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 TESTDB2(thread 1)

ORA-00312: online log 2 thread 1: ‘/n01/oradata1/testdb/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 ‘/n01/oradata1/testdb/redo03.log’ size 100M;

 

Database altered.

Now check the logfiles:

SQL> select * from v$logfile;

 

GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE

———- ——————— ——————— ———————————————————— ———

3                       ONLINE                /n01/oradata1/testdb/redo03.log                  NO

2                       ONLINE                /n01/oradata1/testdb/redo02.log                  NO

1                       ONLINE                /n01/oradata1/testdb/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 14-march

2          1         11   52428800          1 YES       INACTIVE                                               1156732 14-march

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 ‘/n01/oradata1/testdb/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 ‘/n01/oradata1/testdb/redo02.log’ size 100M;

 

Database altered.

 

SQL> select * from v$logfile;

 

GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE

———- ——————— ——————— ———————————————————— ———

3                       ONLINE                /n01/oradata1/testdb/redo03.log                  NO

2                       ONLINE                /n01/oradata1/testdb/redo02.log                  NO

1                       ONLINE                /n01/oradata1/testdb/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 14-march-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 14-march

2          1         13  104857600          1 NO        CURRENT                                                1157376 14-march

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 14-march

2          1         13  104857600          1 NO        CURRENT                                                1157376 14-march

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 14-march

3          1          0  104857600          1 YES       UNUSED                                                       0

 

SQL> select * from v$logfile;

 

GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE

———- ——————— ——————— ———————————————————— ———

3                       ONLINE                /n01/oradata1/testdb/redo03.log                  NO

2                       ONLINE                /n01/oradata1/testdb/redo02.log                  NO

Delete the file ‘/n01/oradata1/testdb/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 ‘/n01/oradata1/testdb/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 14-march-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                /n01/oradata1/testdbredo03.log                  NO

2                       ONLINE                /n01/oradata1/testdbredo02.log                  NO

1                       ONLINE                /n01/oradata1/testdb/edo01.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                /n01/oradata1/testdb/redo02.log                  NO

1                       ONLINE                /n01/oradata1/testdb/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 14-march

 

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 14-march

2          1         13  104857600          1 YES       ACTIVE                                                 1157376 14-march

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s