Forum Stats

  • 3,875,121 Users
  • 2,266,808 Discussions
  • 7,912,087 Comments

Discussions

Data Guard 12C Common service for DG failover does not show up in LSNRCTL status

User_T633C
User_T633C Member Posts: 6 Green Ribbon

Added a common service for both primary(HCV171MCSCPDA01.STATE.MI.US) and standby(HCV171MCSCPDA02.STATE.MI.US) in tnsnames.ora

SBP12CPR_DG =

        (DESCRIPTION =

         (FAILOVER=ON)

         (ADDRESS_LIST=

                 (ADDRESS = (PROTOCOL = TCP) (HOST = HCV171MCSCPDA01.STATE.MI.US) (PORT = 1524) )

                 (ADDRESS = (PROTOCOL = TCP) (HOST = HCV171MCSCPDA02.STATE.MI.US)(PORT = 1524) )

         )

         (CONNECT_DATA = (SERVICE_NAME = SBP12CPR_PRIM.STATE.MI.US) (SERVER = DEDICATED)  )

    )


But in lsnrctl status , it does not show up the service SBP12CPR_PRIM.STATE.MI.US So at sqlplus client side, It does not connect to common service after switching . At Client side sqlplus, it does not connect with alias SBP12CPR_DG .


So I made changes in Listener.ora

LSNRPROD =

 (DESCRIPTION_LIST =

  (DESCRIPTION =

   (ADDRESS = (PROTOCOL = TCP)(HOST = hcv171mcscpda02)(PORT = 1524))

   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1524))

  )

 )



SID_LIST_LSNRPROD =

 (SID_LIST =

 (SID_DESC =

  (GLOBAL_DBNAME = SBP12CPR_S.STATE.MI.US)

  (ORACLE_HOME = /data/app/oracle/product/12.1.0/dbhome_1)

  (SID_NAME = SBP12CPR_S)

 )

 (SID_DESC =

  (GLOBAL_DBNAME = SBP12CPR_PRIM.STATE.MI.US)

  (ORACLE_HOME = /data/app/oracle/product/12.1.0/dbhome_1)

  (SID_NAME = SBP12CPR_S)

 )

 )

 )


Now I stop and start LSNRPROD , and common service SBP12CPR_PRIM.STATE.MI.US shows in lsnrctl status

Listening Endpoints Summary...

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hcv171mcscpda02)(PORT=1524)))

 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1524)))

Services Summary...

Service "SBP12CPR_PRIM.STATE.MI.US" has 1 instance(s).

 Instance "SBP12CPR_S", status UNKNOWN, has 1 handler(s) for this service...

Service "SBP12CPR_S.STATE.MI.US" has 1 instance(s).

 Instance "SBP12CPR_S", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully



Now I can connect with common service.

Answers

  • User_T633C
    User_T633C Member Posts: 6 Green Ribbon
    edited Apr 1, 2022 4:55PM

    corrected the host name in listener.ora and then works fine.

    --prime node 

    LSNRPROD =

     (DESCRIPTION_LIST =

      (DESCRIPTION =

       (ADDRESS = (PROTOCOL = TCP)(HOST = hcv171mcscpda01)(PORT = 1524))

       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1524))

      )

     )


    SID_LIST_LSNRPROD =

     (SID_LIST =

     (SID_DESC =

      (GLOBAL_DBNAME = SBP12CPR.STATE.MI.US)

      (ORACLE_HOME = /data/app/oracle/product/12.1.0/dbhome_1)

      (SID_NAME = SBP12CPR)

     )



    --secomdary node

    LSNRPROD =

     (DESCRIPTION_LIST =

      (DESCRIPTION =

       (ADDRESS = (PROTOCOL = TCP)(HOST = hcv171mcscpda02)(PORT = 1524))

       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1524))

      )

     )


    SID_LIST_LSNRPROD =

     (SID_LIST =

     (SID_DESC =

      (GLOBAL_DBNAME = SBP12CPR_S.STATE.MI.US)

      (ORACLE_HOME = /data/app/oracle/product/12.1.0/dbhome_1)

      (SID_NAME = SBP12CPR_S)

     )

     )

     )