Monday, November 28, 2011

ORA-16516: current state is invalid for the attempted operation


Problem: when I am trying to do a switch over using DG broker and I am facing the issue. 

DGMGRL> switchover to 'BHU_A';
Performing switchover NOW, please wait...
Error: ORA-16516: current state is invalid for the attempted operation

Failed.
Unable to switchover, primary database is still "BHU_B"
DGMGRL>

Reason
After analyzing I found the apply has been stopped in the standby database and noted that we should have a zero lag before proceeding with the switchover

DGMGRL> show database verbose 'BHU_A';

Database - BHU_A

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds
  Apply Lag:       24 minutes 26 seconds
  Real Time Query: OFF
  Instance(s):
    BHU_1 (apply instance)
    BHU_2

<Removed some lines of the output>

Enabling the Apply On process using the DG Broker

DGMGRL> edit database 'BHU_A' set state='APPLY-ON';
Succeeded.

DGMGRL> show database verbose 'BHU_A';

Database - BHU_A

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    BHU_1 (apply instance)
    BHU_2
<Removed some lines of the output>
Starting the Switchover process again after checking the LAG.
Note: since I have write note for the above error message only. If you are performing the switchover process, a lot of check needs to be performed.

DGMGRL> switchover to 'BHU_A'
Performing switchover NOW, please wait...
New primary database "BHU_A" is opening...
Operation requires shutdown of instance "BHU_1" on database "BHU_B"
Shutting down instance "BHU_1"...
ORACLE instance shut down.
Operation requires startup of instance "BHU_1" on database "BHU_B"
Starting instance "BHU_1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "BHU_A"
DGMGRL>


I hope this document helps you to solve the issue.

1 comment:

  1. For 12.x versions, it should be CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; for both Primary and Standbys. Also there will be no impact to performance on Primary Prod DB if any/or all standby's down. Just that the archive logs will not be deleted on Primary until they are applied. So if you rebuild your standby then do a crosscheck on Primary and delete expired.

    ReplyDelete