Drop online redolog group
It is very dangerous when we deal with online redolog files in database which records the active transaction changes into it and needed for media ,block and crash recovery based on their states
We have four usual states of redologs in a typical oracle database
1)active 2)inactive 3)current 4) unused
active – require for instance crash recovery in case of failure and block recovery
inactive – not require for recovery and no logs written by lgwr
current – lgwr writing the logs to current group
unused – states fresh newly added group
If we want to drop an online redolog group , we have to first bring the particular group to inactive state.
SQL> select a.bytes/1024/1024,a.thread#,a.sequence#,a.thread#,a.status,b.member from v$log a,v$logfile b where a.GROUP# = b.GROUP#;
A.BYTES/1024/1024 THREAD# SEQUENCE# THREAD# STATUS MEMBER
----------------- ---------- ---------- ---------- ---------------- ----------------------
50 1 213 1 INACTIVE /app01/oradata/orcl12x/redo03.log
50 1 215 1 CURRENT /app01/oradata/orcl12x/redo02.log
50 1 214 1 INACTIVE /app01/oradata/orcl12x/redo01.log
If the redologs are switching frequently due to heavy transactions,then issue the checkpoint to save the changes to the datafile.
SQL> alter system checkpoint;
System altered.
Oracle forces the checkpoint on the instance and saves the buffered blocks to the disk
we can drop the logfiles when checkpoint completes.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------------- ------------- --------- ------------ --------- ----------
1 1 214 52428800 512 1 NO
INACTIVE 3922477 26-AUG-20 3931944 26-AUG-20 0
2 1 215 52428800 512 1 NO
CURRENT 3931944 26-AUG-20 2.8147E+14 0
3 1 213 52428800 512 1 NO
INACTIVE 3811949 26-AUG-20 3922477 26-AUG-20 0
We can drop either group 1 or group 3 as we need atleast minimum two redo groups for logging and add new groups.
If there is frequent log switch happen again,we can add two more groups before dropping the existing one and drop the old groups
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01567: dropping log 3 would leave less than 2 log files for instance
orcl12x (thread 1)
ORA-00312: online log 3 thread 1: '/app01/oradata/orcl12x/redo03.log'
Once we drop the logfile,we can add a logfile with more size
SQL> alter database add logfile group 1 '/app01/oradata/orcl12x/redo01.log
2 ' size 200M;
Database altered.
Now to verify the newly added logfile
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------------- ------------- --------- ------------ --------- ----------
1 1 0 209715200 512 1 YES
UNUSED 0 0 0
2 1 215 52428800 512 1 NO
CURRENT 3931944 26-AUG-20 2.8147E+14 0
3 1 213 52428800 512 1 NO
INACTIVE 3811949 26-AUG-20 3922477 26-AUG-20 0