Oracle Database did not allow to resize redo log files since we need to drop and re-create to overcome the situation.
Following points need to be considered when we drop the redo log files
- Can not drop redo log files if it's in Current or Active status
- Database requires at least two redo log groups regardless number of members
- OS files are wont delete when we drop redo log files. So delete from OS to create redo log files with same name
Steps
1. Check the redo log status
SQL> select group#,bytes,archived,status,members from v$log;
GROUP# BYTES ARC STATUS MEMBERS
---------- ---------- --- ---------------- ----------
1 209715200 YES INACTIVE 2
2 209715200 YES INACTIVE 2
3 209715200 NO CURRENT 2
In this case we are try to resize group#3, but we can not drop as the status is CURRENT
2. Switch CURRENT redo log group
SQL> alter system switch logfile;
SQL> select group#,bytes,archived,status,members from v$log;
GROUP# BYTES ARC STATUS MEMBERS
---------- ---------- --- ---------------- ----------
1 209715200 NO CURRENT 2
2 209715200 YES INACTIVE 2
3 209715200 YES ACTIVE 2
Need to change status from ACTIVE to INACTIVE for drop redo log file
3. Change the status of redo log group. checkpoint force to write all changes from the database buffers to data files.
SQL> alter system checkpoint global;
SQL> select group#,bytes,archived,status,members from v$log;
GROUP# BYTES ARC STATUS MEMBERS
---------- ---------- --- ---------------- ----------
1 209715200 NO CURRENT 2
2 209715200 YES INACTIVE 2
3 209715200 YES INACTIVE 2
4. Check the members of group and drop the redo log group
SQL> select member from v$logfile where group#=3; MEMBER -------------------------------- C:\ORADATA\DEVCDB\REDO030.LOG C:\ORADATA\DEVCDB\REDO031.LOG
SQL> alter database drop logfile group 3;
5. Delete specific redo log files from Operating system
6. Create the redo log files with changes
For Multiple File
SQL> alter database add logfile group 3 ('C:\ORADATA\DEVCDB\REDO030.LOG','C:\ORADATA\DEVCDB\REDO031.LOG') size 500M;
For Single File
SQL> alter database add logfile group 3 'C:\ORADATA\DEVCDB\REDO031.LOG' size 500M;7. Check whether added redo log files are available
SQL> select member from v$logfile where group#=3; MEMBER -------------------------------- C:\ORADATA\DEVCDB\REDO030.LOG C:\ORADATA\DEVCDB\REDO031.LOG
SQL> select group#,bytes,archived,status,members from v$log;
GROUP# BYTES ARC STATUS MEMBERS
---------- ---------- --- ---------------- ----------
1 209715200 NO CURRENT 2
2 209715200 YES INACTIVE 2
3 209715200 YES UNUSED 2
