Introduction:
Quite some time ago I had to turn a Rac environment into a single Instance / Database. However I did notice there where still redo groups of the second instance present which I wanted to drop. This is just a quick blog to show steps I have followed :
## Checking about the redologs and the thread information:
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- — —————- ————- ——————- ———— ——————-
1 1 1931 2147483648 512 2 YES INACTIVE 1.3789E+13 14.08.2014 18:46:19 1.3789E+13 18.08.2014 07:08:18
2 1 1933 2147483648 512 2 YES INACTIVE 1.3789E+13 18.08.2014 07:08:20 1.3789E+13 18.08.2014 07:08:22
3 1 1934 2147483648 512 2 NO CURRENT 1.3789E+13 18.08.2014 07:08:22 2.8147E+14
4 2 128 2147483648 512 2 YES INACTIVE 146319194 19.04.2012 23:21:10 146397537 20.04.2012 09:19:04
5 2 127 2147483648 512 2 YES INACTIVE 146311735 19.04.2012 22:35:29 146319194 19.04.2012 23:21:10
6 2 126 2147483648 512 2 YES INACTIVE 146169044 18.04.2012 23:20:54 146311735 19.04.2012 22:35:29
## Now lets try to drop a group that is part of 2nd Thread
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01623: log 4 is current log for instance MYDBP12 (thread 2) – cannot drop
ORA-00312: online log 4 thread 2: ‘+MYDBP1_REDO/MYDBP1/onlinelog/group_4.260.772812657’
ORA-00312: online log 4 thread 2: ‘+MYDBP1_DATA/MYDBP1/onlinelog/group_4.267.772812685’
## Checking Threads
SQL> select THREAD#, STATUS, ENABLED from v$thread;
THREAD# STATUS ENABLED
———- —— ——–
1 OPEN PUBLIC
2 CLOSED PUBLIC
## And as we saw in first query which groups belong to the thread we want to drop:
SQL> select group# from v$log where THREAD#=2;
GROUP#
———-
4
5
6
## First we need to make sure that the thread will not interfere anymore so we disable it
SQL>
SQL> alter database disable thread 2;
Database altered.
## Now we can drop the logfile group:
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
## Believe is one thing , checking is better to see if thread is gone:
SQL> select THREAD#, STATUS, ENABLED from v$thread;
THREAD# STATUS ENABLED
———- —— ——–
1 OPEN PUBLIC
## Lets check the groups from redo:
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- — —————- ————- ——————- ———— ——————-
1 1 1931 2147483648 512 2 YES INACTIVE 1.3789E+13 14.08.2014 18:46:19 1.3789E+13 18.08.2014 07:08:18
2 1 1933 2147483648 512 2 YES INACTIVE 1.3789E+13 18.08.2014 07:08:20 1.3789E+13 18.08.2014 07:08:22
3 1 1934 2147483648 512 2 NO CURRENT 1.3789E+13 18.08.2014 07:08:22 2.8147E+14
Happy Dba again !
Enjoy reading and till we meet again,
Mathijs
