Search This Blog

Total Pageviews

Thursday 16 June 2011

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

Oracle DBA

anuj blog Archive