ORA-01609: log 1 is the current log for thread 1 – cannot drop membersORA-00312: online log 1 thread 1:’/apps01/base/oradata/dbx01/onlinelog/group_1.262.1083714911’ORA-00312: online log 1 thread 1:’/apps01/FRA/DBX01/onlinelog/o1_mf_1_jnl5w6gs_.log’
Cause:
I was trying to drop the duplicate logfiles in standby database and get error during drop due to current active thread.
SQL> select GROUP#, MEMBER from V$LOGFILE;
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
4
/apps01/base/oradata/dbx01/onlinelog/group_4.265.1083714917
4
/apps01/FRA/DBX01/onlinelog/o1_mf_4_jnl5ww8k_.log
3
/apps01/base/oradata/dbx01/onlinelog/group_3.264.1083714915
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
3
/apps01/FRA/DBX01/onlinelog/o1_mf_3_jnl5wllv_.log
2
/apps01/base/oradata/dbx01/onlinelog/group_2.263.1083714913
2
/apps01/FRA/DBX01/onlinelog/o1_mf_2_jnl5wd63_.log
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
1
/apps01/base/oradata/dbx01/onlinelog/group_1.262.1083714911
1
/apps01/FRA/DBX01/onlinelog/o1_mf_1_jnl5w6gs_.log
8 rows selected.
Now i drop the duplicate redolog file but it fail due to active recovery purpose
SQL> alter database drop logfile member '/apps01/base/oradata/dbx01/onlinelog/group_1.262.1083714911';
alter database drop logfile member '/apps01/base/oradata/dbx01/onlinelog/group_1.262.1083714911'
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1:
'/apps01/base/oradata/dbx01/onlinelog/group_1.262.1083714911'
ORA-00312: online log 1 thread 1:
'/apps01/FRA/DBX01/onlinelog/o1_mf_1_jnl5w6gs_.log'
Workaround:
As this is a standby database, i cannot open it due to incomplete recovery. Drop the remaining logfiles which are not required and status is ‘not current‘ first
SQL> alter database drop logfile member '/apps01/base/oradata/dbx01/onlinelog/group_2.263.1083714913';
Database altered.
SQL> alter database drop logfile member '/apps01/base/oradata/dbx01/onlinelog/group_4.265.1083714917';
Database altered.
SQL> alter database drop logfile member '/apps01/base/oradata/dbx01/onlinelog/group_3.264.1083714915';
Database altered.
Now check the status of the duplicate logs
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 52428800 512 2 NO
CURRENT 1967801 21-SEP-21 2.8147E+14 0
4 1 0 52428800 512 1 YES
UNUSED 1936695 21-SEP-21 1967801 21-SEP-21 0
3 1 0 52428800 512 1 YES
UNUSED 1905993 20-SEP-21 1936695 21-SEP-21 0
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------------- ------------- --------- ------------ --------- ----------
2 1 0 52428800 512 1 YES
UNUSED 1898210 20-SEP-21 1905993 20-SEP-21 0
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01666: control file is for a standby database
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
On primary:
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH OPENING 26
ARCH CONNECTED 0
ARCH CLOSING 27
ARCH OPENING 27
LNS OPENING 27
On standby:
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 25
After the sequence match the primary scn, stop MRP or defer log_archive_dest_state_enable=false in primary and open the standby database.
Now you can drop the duplicate redo member !
SQL> startup;
ORACLE instance started.
Total System Global Area 1509949440 bytes
Fixed Size 2924640 bytes
Variable Size 989859744 bytes
Database Buffers 503316480 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> alter database drop logfile member '/apps01/base/oradata/dbx01/onlinelog/group_1.262.1083714911';
Database altered.