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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s