14 Replies Latest reply on Sep 15, 2009 12:29 PM by 530054

    Switchover is not smooth using dataguard DGMGRL Command line utility

    444853
      Hi,
      We have simple oracle 10g(10.1.0.3) dataguard implementation with a primary and
      a physical standby database. Basic dataguard functionality works properly. We
      are trying to simplyfy the switchover and failover operation using DGMGRL CLI.
      After configuring and enabling DR in DGMGRL, i'm trying switchover from primary
      to physical standby. While doing this switchover operation, i'm getting
      "ORA-12514: TNS:listener does not currently know of service requested in
      connect descriptor" error. Did dynamic service registration for net service.
      Following are the parameter values from primary.
      *.instance_name='TTRORDBP'
      *.db_name='TTRORDBP'
      *.db_unique_name='ttrordbp'
      *.local_listener='TTRORDBP_DGMGRL'
      *.service_names='ttrordbp, TTRORDBP_XPT, TTRORDBP_DGMGRL','TTRORDBP_XPT'
      Also have attached listener.ora and tnsnames.ora files from primary & standby as an attachment.
      Understanding is that, DGMGRL is looking for specific service name (db_unique_name_DGMGRL.domain, i guess in my case TTRORDBP_DGMGRL) in order to complete auto shutdown and restart of primary and standby DBs while performing switchover and failover.
      I'm guess the net service is getting de-registered here... Could you please help me in get rid of this
      error. Would really appreciate your help on this.

      Another question, Is it must to do static service registration for auto
      shutdown and restart of DBs by DGMGRL?

      Thanks very much.

      Following are what i did from primary DB.
      DGMGRL> show configuration
      Configuration
      Name: TTRDR
      Enabled: YES
      Protection Mode: MaxAvailability
      Databases:
      TTRORDBS - Physical standby database
      TTRORDBP - Primary database
      Current status for "TTRDR":
      SUCCESS
      DGMGRL> SWITCHOVER TO 'TTRORDBS';
      Performing switchover NOW. Please wait...
      Operation requires shutdown of instance "TTRORDBP" on database "TTRORDBP".
      Shutting down instance "TTRORDBP"...
      ORA-01109: database not open
      Database dismounted.
      ORACLE instance shut down.
      Operation requires shutdown of instance "TTRORDBS" on database "TTRORDBS".
      Shutting down instance "TTRORDBS"...
      ORA-01109: database not open
      Database dismounted.
      ORACLE instance shut down.
      Operation requires startup of instance "TTRORDBP" on database "TTRORDBP".
      Starting instance "TTRORDBP"...
      Unable to connect to database
      ORA-12514: TNS:listener does not currently know of service requested in connect
      descriptor
      Failed.

      You are no longer connected to ORACLE
      Please connect again.
      Unable to start instance "TTRORDBP".
      You must start instance "TTRORDBP" manually.
      Operation requires startup of instance "TTRORDBS" on database "TTRORDBS".
      You must start instance "TTRORDBS" manually.
      Switchover succeeded. New primary is "TTRORDBS"
      DGMGRL> exit
        • 1. Re: Switchover is not smooth using dataguard DGMGRL Command line utility
          207680
          Are you tnsnames correctly specified in create configuration cmd ? If you show configuration gives success then whats the problem with switchover. Can you paste your create configuration cmd ?

          Regards,
          http://askyogesh.com
          • 2. Re: Switchover is not smooth using dataguard DGMGRL Command line utility
            63881
            We get the same error, in the similar situation. We already created primary db and physical standby db with grid controller dataguard broker. When it comes to switch over we get the same error. Any help will be appreciated.
            Derya.
            • 3. Re: Switchover is not smooth using dataguard DGMGRL Command line utility
              HeatherK
              Hello,

              We had the same problem. We looked in our listener.log file (under <ORACLE_HOME>\network\log) and saw this entry:

              (CONNECT_DATA=(SERVICE_NAME=mynode_XPT.mydomain.com)(INSTANCE_NAME=mynode)(SERVER=dedicated)(CID=(PROGRAM=e:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE)(HOST=CADDB1)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=129.135.142.92)(PORT=23315)) * establish * mynode_XPT.mydomain.com * 12514
              TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

              This service needs to be statically registered with the listener. Data Guard Broker dynamically registers this service, but the dynamic registration is revoked when the database is shut down. Add an entry like this to your listener.ora file and restart the TNS listener:

              (SID_DESC =
              (GLOBAL_DBNAME = mynode_XPT.mydomain.com)
              (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
              (SID_NAME = mynode)
              )

              Of course, you have to change the oracle_home, mynode and mydomain to the correct values.

              We have filed a TAR with Oracle and are trying to convince them to update their documentation.

              HeatherK
              • 4. Re: Switchover is not smooth using dataguard DGMGRL Command line utility
                476937
                We are running into the same situation but the 12514 error is because it could not find the <sid>DGMGRL service. I added it to the listener.ora as a globalname as suggested in this thread and its working now.
                • 5. Re: Switchover is not smooth using dataguard DGMGRL Command line utility
                  28295
                  Make sure no leading space in front of LISTENER= or SID_LIST_LISTENER=
                  in the LISTENER.ORA.
                  • 6. Re: Switchover is not smooth using dataguard DGMGRL Command line utility
                    634557
                    Hi,
                    Im new to Dataguard
                    I tried on working with dataguard
                    I have sucessfully configured primary and standby database (via remote log archiving)
                    Im
                    able to sucessfully transfer archive redolog from primary to standby
                    able to apply sucessfully on standby database

                    but im getting the same error while switchover from primary to standby

                    "
                    Operation requires shutdown of instance "orcl" on database "orcl"
                    Shutting down instance "orcl"...
                    Database closed.
                    Database dismounted.
                    ORACLE instance shut down.
                    Operation requires startup of instance "orcl" on database "orcl"
                    Starting instance "orcl"...
                    Unable to connect to database
                    ORA-12514: TNS:listener does not currently know of service requeste
                    descriptor

                    Failed.
                    You are no longer connected to ORACLE
                    Please connect again.
                    Unable to start instance "orcl"
                    You must start instance "orcl" manually
                    "
                    I haven made listener entry for daraguard service

                    please help me on this

                    Im using oracle 10.2.0.1
                    primary database orcl
                    primary database unique name orcl

                    standby server orcl
                    standby database unique name stndby

                    my listener file on primary database

                    SID_LIST_LISTENER =
                    (SID_LIST =
                    (SID_DESC =
                    (SID_NAME = PLSExtProc)
                    (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
                    (PROGRAM = extproc)
                    )
                    (SID_DESC =
                    (GLOBAL_DBNAME = orcl)
                    (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
                    (SID_NAME = orcl)
                    )
                    )

                    LISTENER =
                    (DESCRIPTION_LIST =
                    (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)(PORT = 1521))
                    )
                    )

                    LOCAL_LISTENER =
                    (DESCRIPTION_LIST =
                    (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)(PORT = 1521))
                    )
                    )


                    and in initilization parameter file i have included
                    services name as orcl.perfsys

                    local_listener =(DESCRIPTION =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)(PORT = 1521))

                    my listener file on standby database

                    SID_LIST_LISTENER =
                    (SID_LIST =
                    (SID_DESC =
                    (SID_NAME = PLSExtProc)
                    (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
                    (PROGRAM = extproc)
                    )
                    (SID_DESC =
                    (GLOBAL_DBNAME = orcl)
                    (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
                    (SID_NAME = orcl)
                    )
                    )

                    LISTENER =
                    (DESCRIPTION_LIST =
                    (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.55)(PORT = 1521))
                    )
                    )

                    LOCAL_LISTENER =
                    (DESCRIPTION_LIST =
                    (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.55)PORT = 1521))
                    )
                    )

                    Please help me what to add on listener file to manually start service for dataguard
                    and futher steps to perform automatic switchover

                    thanks
                    • 7. Re: Switchover is not smooth using dataguard DGMGRL Command line utility
                      588955
                      Hello - did you get any solution? Or what is the possible solutions I have tried couple of options like adding GLOBAL_DBNAME, taking the trailing space out etc and opened a SR also - but still not able to fix this. I am using DGMGRL command line to switch over.

                      Thanks in advance.
                      • 8. Re: Switchover is not smooth using dataguard DGMGRL Command line utility
                        588955
                        Here is more info on it:


                        DGMGRL> show configuration

                        Configuration
                        Name: olam
                        Enabled: YES
                        Protection Mode: MaxPerformance
                        Fast-Start Failover: DISABLED
                        Databases:
                        q8olam - Primary database
                        q5olam - Physical standby database

                        Current status for "olam":
                        SUCCESS


                        Primary:
                        ----------
                        $ lsnrctl status LISTENER_Q8OLAM

                        LSNRCTL for Solaris: Version 10.2.0.4.0 - Production on 13-AUG-2008 17:59:57

                        Copyright (c) 1991, 2007, Oracle. All rights reserved.

                        Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=q3oam1d3.edc.cingular.net)(PORT=1524)(QUEUESIZE=300))
                        STATUS of the LISTENER
                        ------------------------
                        Alias LISTENER_Q8OLAM
                        Version TNSLSNR for Solaris: Version 10.2.0.4.0 - Production
                        Start Date 12-AUG-2008 13:26:09
                        Uptime 1 days 4 hr. 33 min. 48 sec
                        Trace Level off
                        Security ON: Local OS Authentication
                        SNMP OFF
                        Listener Parameter File /opt/app/q3oam1d3/oracle/product/10.2.0/network/admin/listener.ora
                        Listener Log File /opt/app/q3oam1d3/ora01/Q8OLAM/logs/netlogs/listener_q8olam.log
                        Listening Endpoints Summary...
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=135.214.177.15)(PORT=1524)))
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=Q8OLAM.world)))
                        Services Summary...
                        Service "Q8OLAM" has 1 instance(s).
                        Instance "Q8OLAM", status READY, has 1 handler(s) for this service...
                        Service "Q8OLAM_DGB" has 1 instance(s).
                        Instance "Q8OLAM", status READY, has 1 handler(s) for this service...
                        Service "Q8OLAM_DGMGRL.edc.cingular.net" has 1 instance(s).
                        Instance "Q8OLAM", status UNKNOWN, has 1 handler(s) for this service...
                        Service "Q8OLAM_XPT" has 1 instance(s).
                        Instance "Q8OLAM", status READY, has 1 handler(s) for this service...
                        The command completed successfully
                        • 9. Re: Switchover is not smooth using dataguard DGMGRL Command line utility
                          588955
                          Standby:
                          ----------
                          $ lsnrctl status LISTENER_Q5OLAM

                          LSNRCTL for Solaris: Version 10.2.0.4.0 - Production on 13-AUG-2008 18:00:42

                          Copyright (c) 1991, 2007, Oracle. All rights reserved.

                          Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=q3oam1d2.edc.cingular.net)(PORT=1524)(QUEUESIZE=300))
                          STATUS of the LISTENER
                          ------------------------
                          Alias LISTENER_Q5OLAM
                          Version TNSLSNR for Solaris: Version 10.2.0.4.0 - Production
                          Start Date 12-AUG-2008 13:26:02
                          Uptime 1 days 4 hr. 34 min. 39 sec
                          Trace Level off
                          Security ON: Local OS Authentication
                          SNMP OFF
                          Listener Parameter File /opt/app/q3oam1d2/oracle/product/10.2.0/network/admin/listener.ora
                          Listener Log File /opt/app/q3oam1d2/ora01/Q5OLAM/logs/netlogs/listener_q5olam.log
                          Listening Endpoints Summary...
                          (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=135.214.177.13)(PORT=1524)))
                          (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=Q5OLAM)))
                          Services Summary...
                          Service "Q5OLAM" has 1 instance(s).
                          Instance "Q5OLAM", status READY, has 1 handler(s) for this service...
                          Service "Q5OLAM_DGB" has 1 instance(s).
                          Instance "Q5OLAM", status READY, has 1 handler(s) for this service...
                          Service "Q5OLAM_DGMGRL.edc.cingular.net" has 1 instance(s).
                          Instance "Q5OLAM", status UNKNOWN, has 1 handler(s) for this service...
                          Service "Q5OLAM_XPT" has 1 instance(s).
                          Instance "Q5OLAM", status READY, has 1 handler(s) for this service...
                          The command completed successfully
                          • 10. Re: Switchover is not smooth using dataguard DGMGRL Command line utility
                            588955
                            Following error I got in listener log file:

                            12-AUG-2008 13:23:53 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=usqcef02)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER_Q5OLAM)(VERSION
                            =169870336)) * status * 0
                            12-AUG-2008 13:24:27 * service_update * Q5OLAM * 0
                            12-AUG-2008 13:24:44 * (CONNECT_DATA=(SERVICE_NAME=q5olam_DGB)(INSTANCE_NAME=Q5OLAM)(SERVER=dedicated)(CID=(PROGRAM=oracle)(HOST=usqcef01)(USER=
                            oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=135.214.176.43)(PORT=37315)) * establish * q5olam_DGB * 0
                            12-AUG-2008 13:24:45 * (CONNECT_DATA=(SERVICE_NAME=q5olam_DGB)(INSTANCE_NAME=Q5OLAM)(SERVER=dedicated)(CID=(PROGRAM=dgmgrl)(HOST=usqcef01)(USER=
                            oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=135.214.176.43)(PORT=37317)) * establish * q5olam_DGB * 0
                            12-AUG-2008 13:24:52 * (CONNECT_DATA=(SERVICE_NAME=q5olam_DGB)(INSTANCE_NAME=Q5OLAM)(SERVER=dedicated)(CID=(PROGRAM=oracle)(HOST=usqcef01)(USER=
                            oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=135.214.176.43)(PORT=37326)) * establish * q5olam_DGB * 12518
                            TNS-12518: TNS:listener could not hand off client connection
                            TNS-12547: TNS:lost contact
                            TNS-12560: TNS:protocol adapter error
                            TNS-00517: Lost contact
                            Solaris Error: 32: Broken pipe
                            12-AUG-2008 13:24:53 * service_died * Q5OLAM * 12537
                            12-AUG-2008 13:24:53 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=usqcef02)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER_Q5OLAM)(VERSION
                            =169870336)) * status * 0
                            12-AUG-2008 13:25:27 * (CONNECT_DATA=(SERVICE_NAME=Q5OLAM_DGMGRL)(INSTANCE_NAME=Q5OLAM)(SERVER=dedicated)(CID=(PROGRAM=dgmgrl)(HOST=usqcef01)(US
                            ER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=135.214.176.43)(PORT=37368)) * establish * Q5OLAM_DGMGRL * 12514
                            TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
                            No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=135.214.177.13)(PORT=1524)))
                            No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=Q5OLAM)))
                            Listener completed notification to CRS on stop
                            12-AUG-2008 13:25:52 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=usqcef02)(USER=oracle))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER_Q5OLAM)(VERSION=1
                            69870336)) * stop * 0

                            TNSLSNR for Solaris: Version 10.2.0.4.0 - Production on 12-AUG-2008 13:26:02

                            Copyright (c) 1991, 2007, Oracle. All rights reserved.

                            System parameter file is /opt/app/q3oam1d2/oracle/product/10.2.0/network/admin/listener.ora
                            Log messages written to /opt/app/q3oam1d2/ora01/Q5OLAM/logs/netlogs/listener_q5olam.log
                            Trace information written to /opt/app/q3oam1d2/ora01/Q5OLAM/logs/netlogs/listener_q5olam.trc
                            Trace level is currently 0

                            Started with pid=16302
                            Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=135.214.177.13)(PORT=1524)))
                            Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=Q5OLAM)))
                            Listener completed notification to CRS on start

                            TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
                            12-AUG-2008 13:26:02 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=usqcef02)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER_Q5OLAM)(VERSION
                            =169870336)) * status * 0
                            12-AUG-2008 13:27:07 * service_register * Q5OLAM * 0
                            12-AUG-2008 13:27:13 * (CONNECT_DATA=(SERVICE_NAME=q5olam_XPT)(SERVER=dedicated)(CID=(PROGRAM=oracle)(HOST=usqcef01)(USER=oracle))) * (ADDRESS=(
                            PROTOCOL=tcp)(HOST=135.214.176.43)(PORT=37494)) * establish * q5olam_XPT * 12528
                            TNS-12528: TNS:listener: all appropriate instances are blocking new connections
                            12-AUG-2008 13:27:13 * (CONNECT_DATA=(SERVICE_NAME=Q5OLAM)(GLOBAL_NAME=Q5OLAM)(CID=(PROGRAM=oracle)(HOST=usqcef01)(USER=oracle))) * (ADDRESS=(PR
                            OTOCOL=tcp)(HOST=135.214.176.43)(PORT=37495)) * establish * Q5OLAM * 12528
                            TNS-12528: TNS:listener: all appropriate instances are blocking new connections
                            12-AUG-2008 13:27:19 * service_update * Q5OLAM * 0
                            12-AUG-2008 13:27:21 * (CONNECT_DATA=(SERVICE_NAME=q5olam_DGB)(SERVER=dedicated)(CID=(PROGRAM=oracle)(HOST=usqcef01)(USER=oracle))) * (ADDRESS=(
                            PROTOCOL=tcp)(HOST=135.214.176.43)(PORT=37506)) * establish * q5olam_DGB * 12514
                            TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
                            12-AUG-2008 13:27:22 * (CONNECT_DATA=(SERVICE_NAME=Q5OLAM)(GLOBAL_NAME=Q5OLAM)(CID=(PROGRAM=oracle)(HOST=usqcef01)(USER=oracle))) * (ADDRESS=(PR
                            OTOCOL=tcp)(HOST=135.214.176.43)(PORT=37507)) * establish * Q5OLAM * 0
                            12-AUG-2008 13:27:24 * service_update * Q5OLAM * 0
                            12-AUG-2008 13:27:30 * service_update * Q5OLAM * 0
                            12-AUG-2008 13:27:31 * (CONNECT_DATA=(SERVICE_NAME=q5olam_DGB)(INSTANCE_NAME=Q5OLAM)(SERVER=dedicated)(CID=(PROGRAM=oracle)(HOST=usqcef01)(USER=
                            oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=135.214.176.43)(PORT=37516)) * establish * q5olam_DGB * 0
                            12-AUG-2008 13:27:31 * (CONNECT_DATA=(SERVICE_NAME=Q5OLAM)(GLOBAL_NAME=Q5OLAM)(CID=(PROGRAM=dgmgrl)(HOST=usqcef01)(USER=oracle))) * (ADDRESS=(PR
                            OTOCOL=tcp)(HOST=135.214.176.43)(PORT=37517)) * establish * Q5OLAM * 0
                            12-AUG-2008 13:27:35 * service_update * Q5OLAM * 0
                            • 11. Re: Switchover is not smooth using dataguard DGMGRL Command line utility
                              654268
                              I couldn't get through whole posts but as far as i can get the issue is with dynamic registration of service being used here.

                              as soon as the DB is down listener service is no longer register for the remote sysdba to connect. only way you can start/stop db is by connecting as internal user on the server itself.

                              please correct me if I am wrong.

                              try creating service entry on both primary and secondary and don't use dynamic registration.

                              verify that you can connect to shutdown secondary instance from primary server as sysdba and then retry the same.

                              thanks
                              • 12. Re: Switchover is not smooth using dataguard DGMGRL Command line utility
                                Jskariah-Oracle
                                Yes. This is correct. The dgmgrl should be connected to a service which is statically registered to the listener. And the service name should be <db_unique_name>DGMGRL.<dbdomain.com>

                                It should be configured in the following way. Please follow it and reconfigure the listener.ora and tnsnames.ora. Also the broker connect identifier should be connecting to the _DGMGRL service.

                                Listener.ora
                                ~~~~~~~~~~~~~~~

                                LISTENER =
                                (DESCRIPTION_LIST =
                                (DESCRIPTION =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = 1521)(IP = FIRST))
                                )
                                )
                                SID_LIST_LISTENER =
                                (SID_LIST =
                                (SID_DESC =
                                (SID_NAME = stdby)
                                (ORACLE_HOME = /oracle/product/11.1.0/db_1)
                                (GLOBAL_DBNAME = stdby_DGMGRL)
                                )
                                )




                                Tnsnames.ora
                                ~~~~~~~~~~~~~~~

                                stdby =
                                (DESCRIPTION =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = 152.69.210.97)(PORT = 1521))
                                (CONNECT_DATA =
                                (SERVER = DEDICATED)
                                (SERVICE_NAME = stdby_DGMGRL)
                                (INSTANCE_NAME = stdby)
                                )
                                )

                                Hope this helps.

                                Thx,Jomon
                                • 13. Re: Switchover is not smooth using dataguard DGMGRL Command line utility
                                  340688
                                  So, there is no way to get DG to use dynamic service registrations? That kinda sucks - yet one more thing to keep in mind whenever you change the path information (ie, upgrade).
                                  • 14. Re: Switchover is not smooth using dataguard DGMGRL Command line utility
                                    530054
                                    Try this

                                    PROD =
                                    (DESCRIPTION =
                                    (ADDRESS_LIST =
                                    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.oracle.com)(PORT = 1521)))
                                    (CONNECT_DATA =
                                    *(UR=A)*
                                    (SERVICE_NAME = prod10ib.oracle.com)
                                    )
                                    )

                                    It should you allow to connect through a listener even when it's in blocked/restricted.
                                    Alternatively, consider using SID

                                    more info
                                    362656.1
                                    444120.1