Steps to Resize the Online Redo Logfiles
>sqlplus /nolog
SQL> connect / as sysdba
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
——— ——————–
PROD READ WRITE
Please check the below first:
——————————————-
SQL>select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
———- ——- ——- —————————————- ——————————
3 ONLINE /m03/oradata/PROD/redo03.log NO
2 ONLINE /m02/oradata/PROD/redo02.log NO
1 ONLINE /m01/oradata/PROD/redo01.log NO
Please do the below steps one by one:
Size is 800MB
SQL> alter database add logfile group 4 ‘/redo/oradata/PROD/redo04.log’ size 800m;
Database altered.
SQL> alter database add logfile group 5 ‘/redo/oradata/PROD/redo05.log’ size 800m;
Database altered.
SQL> alter database add logfile group 6 ‘/redo/oradata/PROD/redo06.log’ size 800m;
Database altered.
SQL> alter database add logfile group 7 ‘/redo/oradata/PROD/redo07.log’ size 800m;
Database altered.
Then check the below one :
——————————–
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
———- ———- ———- ———- ———- ———- —
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
—————- ————- ——— ———— ———
1 1 51315 314572800 512 1 YES
INACTIVE 3.0910E+11 17-JUN-14 3.0910E+11 17-JUN-14
2 1 51316 314572800 512 1 NO
CURRENT 3.0910E+11 17-JUN-14 2.8147E+14
3 1 51314 314572800 512 1 YES
INACTIVE 3.0910E+11 17-JUN-14 3.0910E+11 17-JUN-14
4 1 0 838860800 512 1 YES
UNUSED 0 0
5 1 0 838860800 512 1 YES
UNUSED 0 0
6 1 0 838860800 512 1 YES
UNUSED 0 0
7 1 0 838860800 512 1 YES
UNUSED 0 0
From the above we can see log group 2 is current, and this is one of the
groups we must drop. Therefore let’s switch out of this group into
one of the newly created log groups.
Switch until we are into log group 4, so we can drop log groups 1, 2, and 3:
SQL> alter system switch logfile;
** repeat as necessary until group 4 is CURRENT **
Run the query again to verify the current log group is group 4:
SQL> select group#, status from v$log;
GROUP# STATUS
——— —————-
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT
5 UNUSED
6 UNUSED
Note: redo log Group 1 or 2 or 3 can be active after “alter system switch log file” which means could not be dropped, in this case,
you need to do “alter system checkpoint” to make redo log groups 1,2 and 3 inactive.
Now drop redo log groups 1, 2, and 3:
SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;
Verify the groups were dropped, and the new groups’ sizes are correct.
Please check the alertlog and if everything seems to be fine.
Please remove the old redolog files from OS level also.
