Redolog file Resize Drop Re-create

One of the best ways I have found to resize or recreate online redo log files and keep the current sequence is to perform it online.

In this example, we will resize all online redo logs from 250MB to 500MB while the database is running and use SQL*Plus to drop or recreate them in stages.

Before looking at the tasks involved to perform the resize, let's look at the current online redo log groups and their sizes:

SQL> Select group#, member FROM v$logfile;

GROUP#         MEMBER                                        

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

 1             /SAP/Oracle/OriglogA/redo_01a.log    

 1             /SAP/Oracle/MirrlogA/redo_01b.log     

 2             /SAP/Oracle/OriglogB/redo_02a.log    

 2             /SAP/Oracle/MirrlogB/redo_02b.log        

 3             /SAP/Oracle/OriglogC/redo_03a.log    

 3             /SAP/Oracle/MirrlogC/redo_03b.log

6 rows selected.

  

SQL> Select Group#,Members,Status,Bytes/10241024 from v$log;

GROUP#   MEMBERS  STATUS       BYTES/1024/1024

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

1        2        CURRENT      250

2        2        INACTIVE     250

3        2        INACTIVE     250

3 rows selected.

Now let's take a look at the steps involved to resize / recreate all online redo log groups:

1. Make the last redo log CURRENT

Force a log switch until the last redo log is marked "CURRENT" by issuing the following command:

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

GROUP#   STATUS

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

1        CURRENT

2        INACTIVE

3        INACTIVE

3 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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

 GROUP#   STATUS

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

 1        INACTIVE

 2        INACTIVE

 3        CURRENT

3 rows selected.

2. Drop first redo log

After making the last online redo log file the CURRENT one, drop the first online redo log:

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

3. Re-create dropped online redo log group 1 with different size (if desired):

SQL> alter database add logfile group 1 ('/SAP/Oracle/OriglogA/redo_01a.log','/SAP/Oracle/MirrlogA/redo_01b.log') size 500M;

Database altered.

      or

SQL> alter database add logfile group 1 ('/SAP/Oracle/OriglogA/redo_01a.log','/SAP/Oracle/MirrlogA/redo_01b.log') size 500M reuse;

Database altered.

4. Drop second redo log

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

GROUP#    STATUS

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

1         INACTIVE

2         INACTIVE

3         CURRENT

3 rows selected.

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Database altered.

5. Re-create dropped online redo log group 2 as of now status INACTIVE

SQL> alter database add logfile group 2 ('/SAP/Oracle/OriglogB/redo_02a.log ','/SAP/Oracle/MirrlogB/redo_02b.log') size 500M;

Database altered.

      or

SQL> alter database add logfile group 2 ('/SAP/Oracle/OriglogB/redo_02a.log','/SAP/Oracle/MirrlogB/redo_02b.log') size 500M reuse;

Database altered.

6. Drop Third redo log

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

GROUP#  STATUS

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

1       INACTIVE

2       INACTIVE

3       CURRENT

3 rows selected.

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

ALTER DATABASE DROP LOGFILE GROUP 3

*

ERROR at line 1:

ORA-01624: log 3 needed for crash recovery of instance ORA920 (thread 1)

ORA-00312: online log 3 thread 1: '<file_name>'

Note: As a DBA, you should already be aware that if you are going to drop a logfile group, it cannot be the current logfile group. I have run into instances;

however, where attempting to drop the logfile group resulted in the following error as a result of the logfile group having an active status:

7. Easy problem to resolve. Simply perform a checkpoint on the database:

SQL> Alter system switch logfile;

System altered;

SQL> ALTER SYSTEM CHECKPOINT;

System altered.

Now check the status of logfile group 3

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

GROUP#  STATUS

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

1       CURRENT

2       INACTIVE

3       INACTIVE

3 row selected.

We can drop logfile group 3

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Database altered.

8. Re-create dropped online redo log group 3 as of now status INACTIVE

SQL> alter database add logfile group 3 ('/SAP/Oracle/OriglogC/redo_03a.log ','/SAP/Oracle/MirrlogC/redo_03b.log') size 500M;

Database altered.

      or

SQL> alter database add logfile group 3 ('/SAP/Oracle/OriglogC/redo_03a.log','/SAP/Oracle/MirrlogC/redo_03b.log') size 500M reuse;

Database altered.

Now check the current logfile size

SQL> Select group#, member FROM v$logfile;

GROUP#    MEMBER                                        

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

 1        /SAP/Oracle/OriglogA/redo_01a.log    

 1        /SAP/Oracle/MirrlogA/redo_01b.log     

 2        /SAP/Oracle/OriglogB/redo_02a.log    

 2        /SAP/Oracle/MirrlogB/redo_02b.log        

 3        /SAP/Oracle/OriglogC/redo_03a.log    

 3        /SAP/Oracle/MirrlogC/redo_03b.log

6 rows selected.

SQL> Select group#,members,bytes/1024/1024 from  v$log;

GROUP#   MEMBERS  STATUS     BYTES/1024/1024

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

1        2        CURRENT    500

2        2        INACTIVE   500

3        2        INACTIVE   500

3 rows selected.

REUSE: By ensuring REUSE command, logfile being active

WINDOWS: After droping logfile group in windows machine, manually need to delete from respective location.

######################################################  All The Best ######################################################