Tuesday, August 8, 2017

ORA-00261: log 11 of thread 1 is being archived or modified


##########################
## Error
##########################

ORA-00261: log 11 of thread 1 is being archived or modified

### Full Error

ORA-00261: log 11 of thread 1 is being archived or modified
ORA-00312: online log 11 thread 1: '+REDO01/oralin/onlinelog/group_11.1611.951155487'
ORA-00312: online log 11 thread 1: '+REDO02/oralin/onlinelog/group_11.1611.951155489'
ORA-00312: online log 11 thread 1: '+REDO03/oralin/onlinelog/group_11.1611.951155489'

##########################
#  Error Occurred
##########################

Error occured while trying to drop standby redo log groups in dataguard standby database in 11.2.0.4.0 version

##########################
## Command Executed
##########################

Alter database drop logfile group 11, group 14, group 18

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Reproduce the Error
#########################################

SQL> Alter database drop logfile group 11, group 14, group 18;
Alter database drop logfile group 11, group 14, group 18
*
ERROR at line 1:
ORA-00261: log 11 of thread 1 is being archived or modified
ORA-00312: online log 11 thread 1: '+REDO01/oralin/onlinelog/group_11.1611.951155487'
ORA-00312: online log 11 thread 1: '+REDO02/oralin/onlinelog/group_11.1611.951155489'
ORA-00312: online log 11 thread 1: '+REDO03/oralin/onlinelog/group_11.1611.951155489'

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

The purpose of dropping the redo log group is to recreate the redo log groups with an increased log size.

Redo log member is in ACTIVE state due to which we are unable to drop the redo log groups.

=====================================================================================================================

#########################################
# 3) Verify MRP process is down
#########################################

Make sure MRP process is down in standby database.

=====================================================================================================================

##########################
## Solution
##########################

Clear the data stored in STANDBY Redo Log group to bring the REDO LOG MEMBER status from ACTIVE to INACTIVE state.

Alter database clear logfile group 11, group 14, group 18;

SQL> Alter database clear logfile group 11, group 14, group 18;
Database altered.


=====================================================================================================================
Once Redo logs are cleared. Drop command worked successfully.
=====================================================================================================================

SQL> Alter database drop logfile group 11, group 14, group 18;
Database altered.

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================