How To Resize the Online Redo Logfiles

How To Resize the Online Redo Logfiles
resize Redo Logfiles
how to drop redo logfile


Log status:

*UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS,when it is not the current redo log.

*CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.

*ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.

*CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared,the status changes to UNUSED.

*CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.

*INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.

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

GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
1 50 CURRENT
2 50 INACTIVE
3 50 INACTIVE



col MEMBER format a40
select group#, member from v$logfile


GROUP# MEMBER
---------- ----------------------------------------
3 /opt/app/oracle/oradata/orcl/redo03.log
2 /opt/app/oracle/oradata/orcl/redo02.log
1 /opt/app/oracle/oradata/orcl/redo01.log


create new redo log files


alter database add logfile group 4 '/opt/app/oracle/oradata/orcl/redo04.log' size 520M;

alter database add logfile group 5 '/opt/app/oracle/oradata/orcl/redo05.log' size 520M;

alter database add logfile group 6 '/opt/app/oracle/oradata/orcl/redo06.log' size 520M;




SQL> alter database add logfile group 4 '/opt/app/oracle/oradata/orcl/redo04.log' size 520M;

Database altered.

SQL> alter database add logfile group 5 '/opt/app/oracle/oradata/orcl/redo05.log' size 520M;

Database altered.

SQL> alter database add logfile group 6 '/opt/app/oracle/oradata/orcl/redo06.log' size 520M;

Database altered.

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

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

6 rows selected.


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

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




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

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



SQL> alter database drop logfile group 2 ;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

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

GROUP# STATUS
---------- ----------------
1 ACTIVE
4 ACTIVE
5 ACTIVE
6 CURRENT


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

GROUP# STATUS
---------- ----------------
1 ACTIVE
4 ACTIVE
5 CURRENT
6 ACTIVE



>>>>>>>>>>>>>>>>>>>>>>>>>>>wait some time <<<<<<<<<<<<<<<<<<<<<<<<

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

now group 1 is inactive


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


now drop the 1

SQL> alter database drop logfile group 1;

Database altered.


finally delete the file from OS level


SQL> !
oracle@apt-amd-02:/opt/app/oracle/product/11.2/sysman/admin/emdrep/bin> cd /opt/app/oracle/oradata/orcl/


oracle@apt-amd-02:/opt/app/oracle/oradata/orcl> rm redo01.log redo02.log redo03.log