Resize / recreate / crate/ drop redo log files

We cannot resize the redo log files. We must drop the redo log file and recreate them .This is only method to resize the redo log files.

We cannot the drop redo log file if its status is current or active. We need to change the status to "inactive" then only we can drop it.

A database requires at least two groups of redo log files , regardless the number of the members.

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.

• Here we are changing 1GB to 2GB redolog size  first check datafile size and status from v$redolog

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

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

1 1 25 1073741824 2 NO CURRENT 794921 20-SEP-12

2 1 24 1073741824 2 NO ACTIVE 794904 20-SEP-12

3 1 23 1073741824 2 NO ACTIVE 794833 20-SEP-12

• We can find the redo log group member information from v$logfile hare is two members in redo log file

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER

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

3 ONLINE E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\REDO03.LOG

2 ONLINE E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\REDO02.LOG

1 ONLINE E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\REDO01.LOG

1 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\RDO01A.LOG

2 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\RDO02A.LOG

3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\RDO03A.LOG

• We collect the information about redo log member now we have needed to drop and recreate the redo with bigger size.

NOTE:  don’t drop current or active member first change it into

SQL> alter database drop logfile group 1;

alter database drop logfile group 1

*

ERROR at line 1:

ORA-01623: log 1 is current log for instance orcl10g (thread 1) - cannot drop

ORA-00312: online log 1 thread 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\REDO01.LOG'

ORA-00312: online log 1 thread 1: 'C:\RDO01A.LOG'

• Switch logfile from current status means now log writer start writing in next redolog group .

SQL>alter system switch logfile;

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

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

1 1 25 209715200 2 NO ACTIVE 794921 20-SEP-12

2 1 24 209715200 2 NO ACTIVE 794904 20-SEP-12

3 1 26 209715200 2 NO CURRENT 794996 20-SEP-12

• To make active to inactive you need to be perform checkpoint on the system .means all changes from redolog group to written into datafile

SQL> alter system checkpoint;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

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

1 1 25 209715200 2 NO INACTIVE 794921 20-SEP-12

2 1 24 209715200 2 NO INACTIVE 794904 20-SEP-12

3 1 26 209715200 2 NO CURRENT 794996 20-SEP-12

• Now you can drop the redo log group

SQL> alter database drop logfile group 1;

Database altered.

       

• Add the redolog group with required size

SQL> alter database add logfile group 1 ('E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\REDO01.LOG','C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\RDO01A.LOG') size 2G reuse;

Database altered.

Now resize the other redo group also

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 ('E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\REDO02.LOG','C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\RDO02A.LOG') size 1G reuse;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 ('E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\REDO03.LOG','C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\RDO03A.LOG') size 1G reuse;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

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

1 1 14 2147483648 1 NO CURRENT 786787 20-SEP-12

2 1 12 2147483648 1 NO INACTIVE 786729 20-SEP-12

3 1 13 2147483648 1 NO INACTIVE 786746 20-SEP-12