2 Replies Latest reply on Jul 17, 2019 10:37 AM by david uk

    Unable to get Dataguard configuration working

    david uk

      Good afternoon everyone,

      I'm looking for some help and guidance for a problem with Oracle dataguard. I have two physical servers (primary and standby) and a third client machine (broker). I have successfully installed Oracle 18c on primary, configured it for service as a master and used rman to copy over to standby and configured standby as the physical standby server.

      I have then configured the dataguard broker on the client machine, created and applied a configuration and everything is reported as fine.

       

      DGMGRL> show configuration

       

      Configuration - DMS_ORCL

       

        Protection Mode: MaxAvailability

        Members:

        orclprmy - Primary database

          orclstby - (*) Physical standby database

       

      Fast-Start Failover: ENABLED

       

      Configuration Status:

      SUCCESS   (status updated 56 seconds ago)

       

      DGMGRL> show database orclprmy

       

      Database - orclprmy

       

        Role:               PRIMARY

        Intended State:     TRANSPORT-ON

        Instance(s):

          orclprmy

       

      Database Status:

      SUCCESS

       

      DGMGRL> show database orclstby

       

      Database - orclstby

       

        Role:               PHYSICAL STANDBY

        Intended State:     APPLY-ON

        Transport Lag:      0 seconds (computed 0 seconds ago)

        Apply Lag:          0 seconds (computed 0 seconds ago)

        Average Apply Rate: 0 Byte/s

        Real Time Query:    OFF

        Instance(s):

          orclstby

       

      Database Status:

      SUCCESS

       

      To test that it's working I have run the switch-over command

      switchover to orclstby

      and that's when my problems start.

       

      The process starts to run and then fails

       

      Performing switchover NOW, please wait...

      Operation requires a connection to database "orclstby"

      Connecting ...

      Connected to "orclstby"

      Connected as SYSDBA.

      Error: ORA-1034: ORACLE not available

      Error: ORA-16625: cannot reach member "orclprmy"

       

       

      Failed.

      Unable to switchover, primary database is still "orclprmy"

       

      This leaves me with a failed failover, but the database are also reporting inconsistencies. If I look at primary it is reporting itself as standby (after mounting, it starts as dismounted)

      SQL> select db_unique_name, database_role,open_mode, protection_mode from v$database;

       

       

      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE

      ------------------------------ ---------------- --------------------

      PROTECTION_MODE

      --------------------

      orclprmy                       PHYSICAL STANDBY MOUNTED

      MAXIMUM AVAILABILITY

       

      If I look at standby it's reporting itself as primary

      SQL> select db_unique_name, database_role,open_mode, protection_mode from v$database;

       

       

      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE

      ------------------------------ ---------------- --------------------

      PROTECTION_MODE

      --------------------

      orclstby                       PRIMARY          MOUNTED

      MAXIMUM AVAILABILITY

       

      All three machines can connect to each other's database using sqlplus user/pass@orclprmy or sqlplus user/pass@orclstby.

       

      Dataguard is also very confused. If I connect to primary it says

       

      DGMGRL> show configuration

       

      Configuration - DMS_ORCL

       

        Protection Mode: MaxAvailability

        Members:

        orclstby - Primary database

          Error: ORA-16597: Oracle Data Guard broker detects two or more primary databases

       

          orclprmy - Physical standby database

       

      Fast-Start Failover: ENABLED

       

      Configuration Status:

      ERROR   (status updated 0 seconds ago)

       

      But if I connect to standby it says

       

      DGMGRL> show configuration

       

      Configuration - DMS_ORCL

       

        Protection Mode: MaxAvailability

        Members:

        orclprmy - Primary database

          Error: ORA-1034: ORACLE not available

       

          orclstby - Physical standby database

       

      Fast-Start Failover: ENABLED

       

      Configuration Status:

      ERROR   (status updated 0 seconds ago)

       

      And after starting the idle instance

      DGMGRL> show configuration

       

      Configuration - DMS_ORCL

       

        Protection Mode: MaxAvailability

        Members:

        orclprmy - Primary database

          Error: ORA-16623: member detected role change

       

          orclstby - Physical standby database

       

      Fast-Start Failover: ENABLED

       

      Configuration Status:

      ERROR   (status updated 0 seconds ago)

       

      The really annoying thing is that this is a copy of what I tried in a lab and it worked as expected.

       

      Any pointers on where to look as to why it is reporting that it can't connect during the failover, or why dataguard is reporting something different to the databases? I've been looking around and can't find an answer.

       

      Many thanks in advance.