13 Replies Latest reply: Jan 15, 2013 6:46 AM by EdStevens RSS

    ORA-12514: TNS:listener does not currently know of service requested in con

    695836
      Hi all,
      I have configured a standby database on 10.2.0 RHEL 4 machine.
      While issuing
      After issuing the following command at standby
      SQL> startup mount
      SQL>alter database recover manged standby database disconnect from session;
      and the following command at primary site
      SQL> alter system switch logfile;
      I get the following error at the alert log of primary database.

      ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

      If I tnsping standby database from primary,it works but when i try to connect to standby from primary as:

      SQL>conn usr/pwd@standby
      I get the same error:
      ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

      The lsnrctl services command at standby has the following entries:
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
      Services Summary...
      Service "PLSExtProc" has 1 instance(s).
        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:0 refused:0
               LOCAL SERVER
      Service "standby" has 1 instance(s).
        Instance "treasury", status READY, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:0 refused:0 state:ready
               LOCAL SERVER
      Service "standby_XPT" has 1 instance(s).
        Instance "treasury", status READY, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:0 refused:0 state:ready
               LOCAL SERVER
      The command completed successfully
      Thanks
        • 1. Re: ORA-12514: TNS:listener does not currently know of service requested in
          Maran Viswarayar
          Can you post your TNSNAMES.ora entry(PRINARY) and listener.ora of the standby database?


          Are they on the same machine?
          • 2. Re: ORA-12514: TNS:listener does not currently know of service requested in
            695836
            Hi,
            Listener at standby:
            # listener.ora Network Configuration File: /u/oracle/network/admin/listener.ora
            # Generated by Oracle configuration tools.
            
            SID_LIST_LISTENER =
              (SID_LIST =
                (SID_DESC =
                  (SID_NAME = PLSExtProc)
                  (ORACLE_HOME = /u/oracle)
                  (PROGRAM = extproc)
                )
              )
            
            LISTENER =
              (DESCRIPTION_LIST =
                (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                  (ADDRESS = (PROTOCOL = TCP)(HOST = treasuryDC)(PORT = 1521))
                )
              )
            tnsnames.ora file at primary is quite big.I doubt if i can post it here.
            The standby database is at different machine.
            • 3. Re: ORA-12514: TNS:listener does not currently know of service requested in
              Maran Viswarayar
              Just the portion of the entry(stdby) would do that...


              Looks like you standby database instance is not registered with the listner

              use netmnaager to register your instance with the listner on the standby...
              or
              i would change the listner to
              SID_LIST_LISTENER =
                (SID_LIST =
                  (SID_DESC =
                    (SID_NAME = PLSExtProc)
                    (ORACLE_HOME = /u/oracle)
                    (PROGRAM = extproc)
                  )
                 (SID_DESC =
                    (GLOBAL_DBNAME = STANDBY)
                    (ORACLE_HOME =  /u/oracle)
                    (SID_NAME = STADNBY)
                  )
                )
               
              LISTENER =
                (DESCRIPTION_LIST =
                  (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                    (ADDRESS = (PROTOCOL = TCP)(HOST = treasuryDC)(PORT = 1521))
                  )
                )
              • 4. Re: ORA-12514: TNS:listener does not currently know of service requested in
                695836
                tnsnames.ora at primary
                standby =
                  (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
                    (CONNECT_DATA =
                      (SERVER = DEDICATED)
                      (SERVICE_NAME = treasury)
                    )
                • 5. Re: ORA-12514: TNS:listener does not currently know of service requested in
                  695836
                  Hi,
                  How would that additinal entry in listener.ora file help?
                  Can you please explain?

                  Thanks
                  • 6. Re: ORA-12514: TNS:listener does not currently know of service requested in
                    Maran Viswarayar
                    Just replace the GLOBAL,and SID_NAME to treasury and also replace the SID_NAME with SERVICE_NAME


                    Its nothig just register the instance with your listener

                    Edited by: Maran Viswarayar on Jan 19, 2010 6:38 PM
                    • 7. Re: ORA-12514: TNS:listener does not currently know of service requested in
                      Maran Viswarayar
                      Listner is a process which listens to the incoming connections and then handshakes to the instance..

                      Thats the reason your primary not able to connect to the standby
                      • 8. Re: ORA-12514: TNS:listener does not currently know of service requested in
                        695836
                        After modifying as suggested,this is how it looks now:
                        SID_LIST_LISTENER =
                          (SID_LIST =
                            (SID_DESC =
                              (SID_NAME = PLSExtProc)
                              (ORACLE_HOME = /u/oracle)
                              (PROGRAM = extproc)
                            )
                            (SID_DESC =
                              (GLOBAL_DBNAME = treasury)
                              (ORACLE_HOME =  /u/oracle)
                              (SERVICE_NAME = treasury)
                           )
                        )
                        Upon restarting the listener,i get the following error:
                        TNSLSNR for Linux: Version 10.2.0.1.0 - Production
                        System parameter file is /u/oracle/network/admin/listener.ora
                        Log messages written to /u/oracle/network/log/listener.log
                        Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
                        Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=treasuryDC)(PORT=1521)))TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
                         NL-00305: the specified path name does not exist
                        Edited by: Hashmi on Jan 19, 2010 2:46 AM
                        • 9. Re: ORA-12514: TNS:listener does not currently know of service requested in
                          695836
                          Ok,it works now but now i am getting the follwoign error:
                          Error 1017 received logging on to the standby
                          ------------------------------------------------------------
                          Check that the primary and standby are using a password file
                          and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
                          and that the SYS password is same in the password files.
                                returning error ORA-16191
                          It may be necessary to define the DB_ALLOWED_LOGON_VERSION
                          initialization parameter to the value "10".  Check the
                          manual for information on this initialization parameter.
                          ------------------------------------------------------------
                          Tue Jan 19 16:13:54 2010
                          Errors in file /u/oracle/admin/treasury/bdump/treasury_arc1_29733.trc:
                          ORA-16191: Primary log shipping client not logged on standby
                          PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16191.
                          • 10. Re: ORA-12514: TNS:listener does not currently know of service requested in con
                            710790
                            Hi Hashmi,

                            Please edit your tnsnames.ora at the primary database as follows.
                            standby =
                              (DESCRIPTION =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
                                (CONNECT_DATA =
                                  (SERVER = DEDICATED)
                                  (SERVICE_NAME = standby)
                                  (INSTANCE_NAME = treasury) 
                                )
                            Then try connecting to the standby database as follows.
                            sqlplus username/password@standby
                            I hope this will work for you.

                            Regards,
                            Pabolu
                            • 11. Re: ORA-12514: TNS:listener does not currently know of service requested in con
                              user8982949
                              Hello Yasir,,,

                              Did you find a solution for the above error.. now am in a fix with the same issue...

                              can you please post me the solution to my mail id ..

                              reddy.abhishek@gmail.com

                              $ sqlplus sys@manpowerdr as sysdba

                              SQL*Plus: Release 10.2.0.4.0 - Production on Mon Sep 12 08:26:38 2011

                              Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

                              Enter password:
                              ERROR:
                              ORA-12514: TNS:listener does not currently know of service requested in connect
                              descriptor



                              $ lsnrctl status

                              LSNRCTL for HPUX: Version 10.2.0.4.0 - Production on 12-SEP-2011 08:26:04

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

                              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.153.4.5)(PORT=1521)))
                              STATUS of the LISTENER
                              ------------------------
                              Alias LISTENER
                              Version TNSLSNR for HPUX: Version 10.2.0.4.0 - Production
                              Start Date 11-SEP-2011 13:36:04
                              Uptime 0 days 18 hr. 49 min. 59 sec
                              Trace Level off
                              Security ON: Local OS Authentication
                              SNMP OFF
                              Listener Log File /u01/app/ora10g/product/10.2.0/network/log/listener.lo g
                              Listening Endpoints Summary...
                              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=momphist)(PORT=1521)))
                              Services Summary...
                              Service "LABHIST" has 1 instance(s).
                              Instance "LABHIST", status READY, has 1 handler(s) for this service...
                              Service "labhistXDB" has 1 instance(s).
                              Instance "LABHIST", status READY, has 1 handler(s) for this service...
                              Service "labhist_XPT" has 1 instance(s).
                              Instance "LABHIST", status READY, has 1 handler(s) for this service...
                              Service "manpowerdr" has 1 instance(s).
                              Instance "manpower", status READY, has 1 handler(s) for this service...
                              Service "manpowerdr_XPT" has 1 instance(s).
                              Instance "manpower", status READY, has 1 handler(s) for this service...
                              The command completed successfully
                              • 12. Re: ORA-12514: TNS:listener does not currently know of service requested in con
                                477650
                                Hi,
                                If you are having tns or listener problems like one of the following error messages:

                                TNS-01150: The address of the specified listener name is incorrect

                                ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

                                Try using the Oracle Net Configuration Assistant. It's in the "Configuration and Migration Tools" subfolder of your windows start menu folder for the oracle db.

                                It seems that the listener, etc is very particular on the syntax of these configuration files. An extra space or forgetting a parenthesis ")" will generate this error and it can take a very long time to identify the problem character.
                                Thanks,
                                -Alexei
                                • 13. Re: ORA-12514: TNS:listener does not currently know of service requested in con
                                  EdStevens
                                  user474647 wrote:
                                  Hi,
                                  If you are having tns or listener problems like one of the following error messages:

                                  TNS-01150: The address of the specified listener name is incorrect

                                  ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

                                  Try using the Oracle Net Configuration Assistant. It's in the "Configuration and Migration Tools" subfolder of your windows start menu folder for the oracle db.

                                  It seems that the listener, etc is very particular on the syntax of these configuration files. An extra space or forgetting a parenthesis ")" will generate this error and it can take a very long time to identify the problem character.
                                  Thanks,
                                  -Alexei
                                  did you notice that you were responding to an OP that is over three years old, and already marked as answered?