1 2 3 Previous Next 37 Replies Latest reply: Jan 22, 2013 8:01 AM by 986383 RSS

    ORA-12514 When trying to add the secondary physical database

    894939
      Hi All,

      I'm getting ORA-12514 when I'm trying to add my physical standby database in DGMGRL. Please find below my listner.ora configurations in both primary and secondary DB.

      Primary
      --------------

      ADMIN_RESTRICTIONS_LISTENER = on
      LISTENER =
      (ADDRESS_LIST =
      (ADDRESS =
      (PROTOCOL = IPC)
      (KEY = DGP.WORLD)
      )
      (ADDRESS=
      (PROTOCOL = IPC)
      (KEY = DGP)
      )
      (ADDRESS =
      (COMMUNITY = SAP.WORLD)
      (PROTOCOL = TCP)
      (HOST = oracledgp)
      (PORT = 1527)
      )
      )
      STARTUP_WAIT_TIME_LISTENER = 0
      CONNECT_TIMEOUT_LISTENER = 10
      TRACE_LEVEL_LISTENER = OFF
      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = DGP)
      (ORACLE_HOME = /oracle/DGP/112_64)
      (GLOBAL_DBNAME=DGP.DGMGRL.oracledgp)
      )
      (SID_DESC =
      (SID_NAME = DGP)
      (ORACLE_HOME = /oracle/DGP/112_64)
      (GLOBAL_DBNAME=DGR.DGMGRL.oracledgr)
      )

      )

      Secondary
      ----------------------

      ################
      ADMIN_RESTRICTIONS_LISTENER = on
      LISTENER =
      (ADDRESS_LIST =
      (ADDRESS =
      (PROTOCOL = IPC)
      (KEY = DGP.WORLD)
      )
      (ADDRESS=
      (PROTOCOL = IPC)
      (KEY = DGP)
      )
      (ADDRESS =
      (COMMUNITY = SAP.WORLD)
      (PROTOCOL = TCP)
      (HOST = oracledgr)
      (PORT = 1527)
      )
      )
      STARTUP_WAIT_TIME_LISTENER = 0
      CONNECT_TIMEOUT_LISTENER = 10
      TRACE_LEVEL_LISTENER = OFF
      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = DGP)
      (ORACLE_HOME = /oracle/DGP/112_64)
      (GLOBAL_DBNAME=DGR)
      )
      (SID_DESC =
      (SID_NAME = DGP)
      (ORACLE_HOME = /oracle/DGP/112_64)
      (GLOBAL_DBNAME=DGP)
      )
      )



      Please help I tried but as this is common error still I cannot find the specific error here...

      Thanks,
      Shanaka.
        • 1. Re: ORA-12514 When trying to add the secondary physical database
          Balazs Papp
          listener.log contains the details of CONNECT_DATA
          there you can find the SID/SERVICE_NAME that is used for connection (which is missing on the secondary site)
          • 2. Re: ORA-12514 When trying to add the secondary physical database
            teits
            Hi,

            12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
            // *Cause:  The listener received a request to establish a connection to a
            // database or other service. The connect descriptor received by the listener
            // specified a service name for a service (usually a database service)
            // that either has not yet dynamically registered with the listener or has
            // not been statically configured for the listener. This may be a temporary
            // condition such as after the listener has started, but before the database
            // instance has registered with the listener.
            // *Action:
            // - Wait a moment and try to connect a second time.
            // - Check which services are currently known by the listener by executing:
            // lsnrctl services <listener name>
            // - Check that the SERVICE_NAME parameter in the connect descriptor of the
            // net service name used specifies a service known by the listener.
            // - If an easy connect naming connect identifier was used, check that
            // the service name specified is a service known by the listener.
            // - Check for an event in the listener.log file.


            firstly, your GLOBAL_DBNAME seem not to be configured properly.
            it should look like:
            SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=sid_name)
            (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain) ---this section edited to ur db_unique_name
            (ORACLE_HOME=oracle_home)))
            --Alternatively, you can use a different static service name. If you do, be sure to modify the StaticConnectIdentifier instance-specific property to reflect the different service name.

            http://gavinsoorma.com/2010/03/11g-data-guard-broker-dgmgrl-configuration-quick-steps/

            Tobi
            • 3. Re: ORA-12514 When trying to add the secondary physical database
              894939
              Hi Todi,

              I have edited the file as below....

              STARTUP_WAIT_TIME_LISTENER = 0
              CONNECT_TIMEOUT_LISTENER = 10
              TRACE_LEVEL_LISTENER = OFF
              SID_LIST_LISTENER =
              (SID_LIST =
              (SID_DESC =
              (SID_NAME = DGP)
              (ORACLE_HOME = /oracle/DGP/112_64)
              (GLOBAL_DBNAME=DGP.DGMGRL.oracledgp)
              )
              (SID_DESC =
              (SID_NAME = DGP)
              (ORACLE_HOME = /oracle/DGP/112_64)
              (GLOBAL_DBNAME=DGR.DGMGRL.oracledgr)
              )

              )


              Hope this is correct...

              Thanks
              • 4. Re: ORA-12514 When trying to add the secondary physical database
                mseberg
                Hello;

                It should be :

                <db_unique_name>_DGMGRL.domain

                incorerct

                GLOBAL_DBNAME=DGP.DGMGRL.oracledgp


                Use _DGMGRL
                _DGMGRL
                not
                .DGMGRL
                Data Guard Listener Example

                http://www.visi.com/~mseberg/Data_Guard_Listener_Example.html

                So you still need the _DGMGRL or at least a static entry in the listener.ora.

                For additional information see -

                Data Guard Broker 11g Release 2 (11.2) E17023-04




                Best Regards

                mseberg

                Edited by: mseberg on Nov 14, 2012 4:43 AM
                • 5. Re: ORA-12514 When trying to add the secondary physical database
                  894939
                  Dear mseberg,

                  Thank you very much for the information , even after I edited the listner.ora accordingly I still get the same error when try to add secondary DB. Is there any place where we can check regarding this error.

                  Regards,
                  Shanaka.
                  • 6. Re: ORA-12514 When trying to add the secondary physical database
                    Balazs Papp
                    Did you read my post?
                    Did you check the listener log?
                    • 7. Re: ORA-12514 When trying to add the secondary physical database
                      Shivananda Rao
                      (SID_DESC =
                      (SID_NAME = DGP)
                      (ORACLE_HOME = /oracle/DGP/112_64)
                      (GLOBAL_DBNAME=DGR.DGMGRL.oracledgr)
                      You have mentioned GLOBAL_DBNAME as DGP.DGMGRL.oracledgr but you have also mentioned GLOBAL_DBNAME as DGR as shown above. Change it to DGP_DGMGRL.oracledgr in primary
                      • 8. Re: ORA-12514 When trying to add the secondary physical database
                        894939
                        Hi Balazs ,

                        Yes boss I have red the log you've mentioned but it doesn't contain any failure message regarding trying to connect to DR database.

                        The only message it contains is

                        <msg time='2012-11-12T06:10:59.661+05:30' org_id='oracle' comp_id='tnslsnr'
                        type='UNKNOWN' level='16' host_id='oracledgp'
                        host_addr='PR_IP'>
                        <txt>12-NOV-2012 06:10:59 * (CONNECT_DATA=(SID=DGP)(GLOBAL_NAME=DGP.WORLD)(CID=(PROGRAM=dw.sapDGP_DVEBMGS00)(HOST=oracledgp)(USER=dgpadm))) * (ADDRESS=(PROTOCOL=tcp)(HOST=PR_IP)(PORT=41057)) * establish * DGP * 0
                        </txt>
                        </msg>
                        <msg time='2012-11-12T06:11:01.836+05:30' org_id='oracle' comp_id='tnslsnr'
                        type='UNKNOWN' level='16' host_id='oracledgp'
                        host_addr='PR_IP'>



                        Thanks,
                        Shanaka.

                        Edited by: user9163607 on Nov 14, 2012 3:36 AM
                        • 9. Re: ORA-12514 When trying to add the secondary physical database
                          894939
                          Rao, I have changed that in both PR and DR :)
                          • 10. Re: ORA-12514 When trying to add the secondary physical database
                            CKPT
                            Am not sure you have bounced the listener or not after making changes in GLOBAL_DBNAME. Please do if you haven't done.

                            and besides give the output of
                            $lnsrctl status
                            $lsnrctl services

                            also share the oracle net service you are using to add in DGMGRL.
                            • 11. Re: ORA-12514 When trying to add the secondary physical database
                              mseberg
                              Hello again;

                              Since you are using ADMIN_RESTRICTIONS_LISTENER your best bet is to modify the listener.ora file manually and then stop and start the listener.

                              A lsnrctl status should show something like this :
                              Service "STANDBY_DGMGRL.domainname" has 1 instance(s).
                              You need to see this for for the Primary and Standby Databases.


                              If your GLOBAL_DBNAME is not set to this format on all listeners you will not only have this issue ( ORA-12514 ), but you will probably have switchover issues
                              down the line if any part is setup wrong.
                              GLOBAL_DBNAME = db_unique_name_DGMGRL.db_domain
                              Take a look at this note since you are not using the default port.


                              ORA-16664 ORA-16792 Data guard Broker [ID 1228797.1]

                              These two notes may also help :

                              Switchover Failed With ORA-12521 using Dataguard Broker [ID 1380949.1]
                              Automatic Restart of Databases during Switchover fail with ORA-12514 in DGMGRL [ID 308943.1]

                              Best Regards

                              mseberg
                              • 12. Re: ORA-12514 When trying to add the secondary physical database
                                teits
                                NO. but i believe you have gotten that line right by now. should be *<db_unique_name>_DGMGRL.domain*


                                If you you still have issues, do on both DB and paste here:
                                $ lsnrctl reload
                                $ lsnrctl status
                                
                                SQL> show parameter db_unique_name
                                SQL> show parameter db_domain     
                                Tobi
                                • 13. Re: ORA-12514 When trying to add the secondary physical database
                                  894939
                                  HI All,

                                  Please find the lsnrctl status output below.

                                  Primary
                                  -----------------------------------
                                  oracledgp:oradgp 51> lsnrctl status

                                  LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-NOV-2012 08:56:10

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

                                  Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=DGP.WORLD))
                                  STATUS of the LISTENER
                                  ------------------------
                                  Alias LISTENER
                                  Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
                                  Start Date 14-NOV-2012 16:36:23
                                  Uptime 0 days 16 hr. 19 min. 46 sec
                                  Trace Level off
                                  Security ON: Local OS Authentication
                                  SNMP OFF
                                  Listener Parameter File /oracle/DGP/112_64/network/admin/listener.ora
                                  Listener Log File /oracle/diag/tnslsnr/oracledgp/listener/alert/log.xml
                                  Listening Endpoints Summary...
                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DGP.WORLD)))
                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DGP)))
                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracledgp.keells.int)(PORT=1527)))
                                  Services Summary...
                                  Service "DGP_DGMGRL.oracledgp" has 1 instance(s).
                                  Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
                                  Service "DGR_DGMGRL.oracledgr" has 1 instance(s).
                                  Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
                                  The command completed successfully


                                  Secondary
                                  ------------------------------------------
                                  oracledgr:oradgp 51> lsnrctl status

                                  LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-NOV-2012 14:28:44

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

                                  Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=DGP.WORLD))
                                  STATUS of the LISTENER
                                  ------------------------
                                  Alias LISTENER
                                  Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
                                  Start Date 14-NOV-2012 22:06:38
                                  Uptime 0 days 16 hr. 22 min. 6 sec
                                  Trace Level off
                                  Security ON: Local OS Authentication
                                  SNMP OFF
                                  Listener Parameter File /oracle/DGP/112_64/network/admin/listener.ora
                                  Listener Log File /oracle/diag/tnslsnr/oracledgr/listener/alert/log.xml
                                  Listening Endpoints Summary...
                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DGP.WORLD)))
                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DGP)))
                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracledgr.keells.int)(PORT=1527)))
                                  Services Summary...
                                  Service "DGP_DGMGRL.oracledgr" has 1 instance(s).
                                  Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
                                  Service "DGR_DGMGRL.oracledgp" has 1 instance(s).
                                  Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
                                  The command completed successfully



                                  lsnrctl services_

                                  Primary

                                  oracledgp:oradgp 52> lsnrctl services

                                  LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-NOV-2012 08:59:47

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

                                  Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=DGP.WORLD))
                                  Services Summary...
                                  Service "DGP_DGMGRL.oracledgp" has 1 instance(s).
                                  Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
                                  Handler(s):
                                  "DEDICATED" established:0 refused:0
                                  LOCAL SERVER
                                  Service "DGR_DGMGRL.oracledgr" has 1 instance(s).
                                  Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
                                  Handler(s):
                                  "DEDICATED" established:0 refused:0
                                  LOCAL SERVER
                                  The command completed successfully


                                  secondary

                                  oracledgr:oradgp 52> lsnrctl services

                                  LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-NOV-2012 14:29:07

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

                                  Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=DGP.WORLD))
                                  Services Summary...
                                  Service "DGP_DGMGRL.oracledgr" has 1 instance(s).
                                  Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
                                  Handler(s):
                                  "DEDICATED" established:0 refused:0
                                  LOCAL SERVER
                                  Service "DGR_DGMGRL.oracledgp" has 1 instance(s).
                                  Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
                                  Handler(s):
                                  "DEDICATED" established:0 refused:0
                                  LOCAL SERVER
                                  The command completed successfully



                                  Thanks,
                                  Shanaka.

                                  Edited by: Shanaka Chandrasekera on Nov 14, 2012 7:36 PM
                                  • 14. Re: ORA-12514 When trying to add the secondary physical database
                                    894939
                                    Dear Todi,

                                    Please find details below

                                    lsnrctl reload_

                                    PR
                                    SQL> host
                                    oracledgp:oradgp 51> lsnrctl reload

                                    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-NOV-2012 09:12:09

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

                                    Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=DGP.WORLD))
                                    The command completed successfully
                                    oracledgp:oradgp 52>

                                    DR

                                    oracledgr:oradgp 54> lsnrctl reload

                                    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-NOV-2012 14:43:24

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

                                    Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=DGP.WORLD))
                                    The command completed successfully
                                    oracledgr:oradgp 55>


                                    SQL_

                                    PR

                                    SQL> show parameter db_unique_name

                                    NAME TYPE VALUE
                                    ------------------------------------ ----------- ------------------------------
                                    db_unique_name string DGP
                                    SQL> show parameter db_domain

                                    NAME TYPE VALUE
                                    ------------------------------------ ----------- ------------------------------
                                    db_domain string oracledgp
                                    SQL>


                                    DR

                                    SQL> show parameter db_unique_name

                                    NAME TYPE VALUE
                                    ------------------------------------ ----------- ------------------------------
                                    db_unique_name string DGR
                                    SQL> show parameter db_domain

                                    NAME TYPE VALUE
                                    ------------------------------------ ----------- ------------------------------
                                    db_domain string oracledgr


                                    Thanks,
                                    Shanaka.
                                    1 2 3 Previous Next