Changing Apply Instance on Physical standby database in RAC environment

Primary DB è    BHU_A

Physical standby DB è BHU_B

TO LIST THE CONFIGURATION OF THE BROKER CONFIGURATION

DGMGRL> show configuration;

Configuration - DG_BHU

  Protection Mode: MaxAvailability

  Databases:

    BHU_A - Primary database

    BHU_B - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DISPLAY DB CONFIGURATION DETAILS

DGMGRL> show database 'BHU_B';

Database - BHU_B

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: OFF

  Instance(s):

    BHU_1 (apply instance)

    BHU_2

Database Status:

SUCCESS

DGMGRL> exit

Note: we have the apply instance on Node-1(BHU_1 instance) and we are moving the apply instance to the node-2(BHU_2 instance) of the standby database

Login to BHU_1 instance on the standby database(BHU_B) and cancel the recovery

bhuora01[BHU_1]>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Oct 19 13:41:22 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  CANCEL;

Database altered.

Now Login to Broker and checking the status

bhuora01[BHU_1]>dgmgrl

DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys

Password:

Connected.

DGMGRL> show configuration;

Configuration - DG_BHU

  Protection Mode: MaxAvailability

  Databases:

    BHU_A - Primary database

    BHU_B - Physical standby database

      Error: ORA-16766: Redo Apply is stopped

Fast-Start Failover: DISABLED

Configuration Status:

ERROR

DGMGRL> show database 'BHU_B';

Database - BHU_B

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       34 seconds

  Real Time Query: OFF

  Instance(s):

    BHU_1 (apply instance)

    BHU_2

  Database Error(s):

    ORA-16766: Redo Apply is stopped

Database Status:

ERROR

DGMGRL>

Now Login in to the second instance(BHU_2) of the standby database

bhuora02[BHU_2]>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Oct 19 13:40:35 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

SQL>

Now it should moved to the second instance of the standby database.

DGMGRL> show database 'BHU_B';

Database - BHU_B

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: OFF

  Instance(s):

    BHU_1

    BHU_2 (apply instance)

Database Status:

SUCCESS

DGMGRL> show configuration;

Configuration - DG_BHU

  Protection Mode: MaxAvailability

  Databases:

    BHU_A - Primary database

    BHU_B - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS