1 2 3 4 Previous Next 79 Replies Latest reply: Mar 13, 2013 10:36 AM by EdStevens Go to original post RSS
      • 15. Re: Cant connect my database using net service_name
        972355
        I am getting confused to changing one by one.

        Could you please assist (to connect my db) using net_service_name?

        - I need some steps to follow.

        I googled but i could n't find out clear document.

        Thanks ..
        • 16. Re: Cant connect my database using net service_name
          TSharma-Oracle
          Check my edited response.
          • 17. Re: Cant connect my database using net service_name
            sb92075
            969352 wrote:
            I am getting error.

            SQL> conn u1/u1@ORCLMYTEST
            ERROR:
            ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


            *$ lsnrctl reload*

            LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 09-MAR-2013 20:23:32

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

            Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
            The command completed successfully
            ORA-12514 ALWAYS only occurs due to a problem on DB Server system.
            One cause of this problem is when the Oracle database is down & needs to be started.
            A remote client send a request to the Listener asking to be connected to a specific service.
            If/when the listener does not know anything about that service, the listener responds with ORA-12514

            post results from the following two OS commands:
            lsnrctl status
            lsnrctl service

            Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.

            This line contains valuable debugging details. So post this line & surrounding lines.

            for additional debugging suggestions read the URL below:
            http://edstevensdba.wordpress.com/2011/03/19/ora-12514/
            • 18. Re: Cant connect my database using net service_name
              972355
              Check my edited response.

              SQL> select * from global_name;

              GLOBAL_NAME

              MYDB.TEST.ORCLTEST.COM
              • 19. Re: Cant connect my database using net service_name
                TSharma-Oracle
                So now in listener.ora , change your global_names entry to MYDB.TEST.ORCLTEST.COM value.

                In tnsnames.ora your service_name should be orcltest

                In listener.ora , your global_names hould be MYDB.TEST.ORCLTEST.COM

                and than reload your listener
                • 20. Re: Cant connect my database using net service_name
                  972355
                  post results from the following two OS commands:
                  lsnrctl status
                  lsnrctl service

                  MY OUTPUT

                  *[oracle@linuxserver admin]$ lsnrctl status*

                  LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 09-MAR-2013 20:40:38

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

                  Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                  STATUS of the LISTENER
                  ------------------------
                  Alias LISTener_ORcl
                  Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
                  Start Date 09-MAR-2013 11:10:14
                  Uptime 0 days 9 hr. 30 min. 24 sec
                  Trace Level off
                  Security ON: Local OS Authentication
                  SNMP OFF
                  Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
                  Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener_orcl.log
                  Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)(PORT=1521)))
                  Services Summary...
                  Service "MYDB.PROD.ORCLPROD.COM" has 1 instance(s).
                  Instance "orclprod", status UNKNOWN, has 1 handler(s) for this service...
                  Service "MYDB.TEST.ORCLTEST.COM" has 1 instance(s).
                  Instance "orcltest", status UNKNOWN, has 1 handler(s) for this service...
                  Service "asdf" has 1 instance(s).
                  Instance "asdf", status READY, has 1 handler(s) for this service...
                  Service "asdf1" has 1 instance(s).
                  Instance "asdf1", status READY, has 1 handler(s) for this service...
                  Service "asdf1XDB" has 1 instance(s).
                  Instance "asdf1", status READY, has 1 handler(s) for this service...
                  Service "asdf1_XPT" has 1 instance(s).
                  Instance "asdf1", status READY, has 1 handler(s) for this service...
                  Service "asdfXDB" has 1 instance(s).
                  Instance "asdf", status READY, has 1 handler(s) for this service...
                  Service "asdf_XPT" has 1 instance(s).
                  Instance "asdf", status READY, has 1 handler(s) for this service...
                  Service "orclprod" has 1 instance(s).
                  Instance "orclprod", status READY, has 1 handler(s) for this service...
                  Service "orclprodXDB" has 1 instance(s).
                  Instance "orclprod", status READY, has 1 handler(s) for this service...
                  Service "orclprod_XPT" has 1 instance(s).
                  Instance "orclprod", status READY, has 1 handler(s) for this service...
                  Service "orcltest" has 1 instance(s).
                  Instance "orcltest", status READY, has 1 handler(s) for this service...
                  Service "orcltestXDB" has 1 instance(s).
                  Instance "orcltest", status READY, has 1 handler(s) for this service...
                  Service "orcltest_XPT" has 1 instance(s).
                  Instance "orcltest", status READY, has 1 handler(s) for this service...
                  The command completed successfully


                  *[oracle@linuxserver admin]$ lsnrctl service*

                  LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 09-MAR-2013 20:41:47

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

                  Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                  Services Summary...
                  Service "MYDB.PROD.ORCLPROD.COM" has 1 instance(s).
                  Instance "orclprod", status UNKNOWN, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:0 refused:0
                  LOCAL SERVER
                  Service "MYDB.TEST.ORCLTEST.COM" has 1 instance(s).
                  Instance "orcltest", status UNKNOWN, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:0 refused:0
                  LOCAL SERVER
                  Service "asdf" has 1 instance(s).
                  Instance "asdf", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:8 refused:0 state:ready
                  LOCAL SERVER
                  Service "asdf1" has 1 instance(s).
                  Instance "asdf1", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:9 refused:0 state:ready
                  LOCAL SERVER
                  Service "asdf1XDB" has 1 instance(s).
                  Instance "asdf1", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "D000" established:0 refused:0 current:0 max:1022 state:ready
                  DISPATCHER <machine: linuxserver, pid: 26157>
                  (ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)(PORT=36302))
                  Service "asdf1_XPT" has 1 instance(s).
                  Instance "asdf1", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:9 refused:0 state:ready
                  LOCAL SERVER
                  Service "asdfXDB" has 1 instance(s).
                  Instance "asdf", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "D000" established:0 refused:0 current:0 max:1022 state:ready
                  DISPATCHER <machine: linuxserver, pid: 21996>
                  (ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)(PORT=35654))
                  Service "asdf_XPT" has 1 instance(s).
                  Instance "asdf", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:8 refused:0 state:ready
                  LOCAL SERVER
                  Service "orclprod" has 1 instance(s).
                  Instance "orclprod", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:0 refused:0 state:ready
                  LOCAL SERVER
                  Service "orclprodXDB" has 1 instance(s).
                  Instance "orclprod", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "D000" established:0 refused:0 current:0 max:1022 state:ready
                  DISPATCHER <machine: linuxserver, pid: 15607>
                  (ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)(PORT=34516))
                  Service "orclprod_XPT" has 1 instance(s).
                  Instance "orclprod", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:0 refused:0 state:ready
                  LOCAL SERVER
                  Service "orcltest" has 1 instance(s).
                  Instance "orcltest", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:0 refused:0 state:ready
                  LOCAL SERVER
                  Service "orcltestXDB" has 1 instance(s).
                  Instance "orcltest", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "D000" established:0 refused:0 current:0 max:1022 state:ready
                  DISPATCHER <machine: linuxserver, pid: 31586>
                  (ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)(PORT=36727))
                  Service "orcltest_XPT" has 1 instance(s).
                  Instance "orcltest", 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 sb;
                  • 21. Re: Cant connect my database using net service_name
                    sb92075
                    Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.

                    This line contains valuable debugging details. So post this line & surrounding lines.
                    This line contains valuable debugging details. So post this line & surrounding lines.
                    This line contains valuable debugging details. So post this line & surrounding lines.
                    This line contains valuable debugging details. So post this line & surrounding lines.
                    This line contains valuable debugging details. So post this line & surrounding lines.
                    • 22. Re: Cant connect my database using net service_name
                      TSharma-Oracle
                      So now in listener.ora , change your global_names entry to MYDB.TEST.ORCLTEST.COM value.

                      In tnsnames.ora your service_name should be orcltest

                      In listener.ora , your global_names hould be MYDB.TEST.ORCLTEST.COM

                      and than reload your listener
                      • 23. Re: Cant connect my database using net service_name
                        972355
                        I am sorry for posting this .. Really i am getting confused changing file entries ..

                        Again i am posting my clear  entries for listener.ora  and tnsnames.ora

                        TNSNAMES.ora

                        ORCLMYTEST =
                        (DESCRIPTION =
                        (ADDRESS_LIST =
                        (ADDRESS = (PROTOCOL = TCP)(HOST = LINUXSERVER)(PORT = 1521))
                        )
                        (CONNECT_DATA =
                        (SERVER = DEDICATED)
                        (SERVICE_NAME = orcltest)
                        )
                        )

                        ASDF =
                        (DESCRIPTION =
                        (ADDRESS = (PROTOCOL = TCP)(HOST = linuxserver)(PORT = 1521))
                        (CONNECT_DATA =
                        (SERVER = DEDICATED)
                        (SERVICE_NAME = asdf)
                        )
                        )


                        ********************************************************************

                        LISTENER.ora

                        LISTENER_ORCL =
                        (DESCRIPTION_LIST =
                        (DESCRIPTION =
                        (ADDRESS = (PROTOCOL =TCP)(HOST = linuxserver)(PORT = 1521))
                        )
                        )
                        SID_LIST_LISTENER_ORCL =
                        (SID_LIST =
                        (SID_DESC =
                        (SID_NAME = orcltest)
                        (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
                        (GLOBAL_DBNAME = MYDB.TEST.ORCLTEST.COM)
                        )
                        (SID_DESC =
                        (SID_NAME = orclprod)
                        (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
                        (GLOBAL_DBNAME = MYDB.PROD.ORCLPROD.COM)
                        )
                        )

                        Thanks ..
                        • 24. Re: Cant connect my database using net service_name
                          972355
                          Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.

                          This line contains valuable debugging details. So post this line & surrounding lines.
                          This line contains valuable debugging details. So post this line & surrounding lines.
                          This line contains valuable debugging details. So post this line & surrounding lines.
                          This line contains valuable debugging details. So post this line & surrounding lines.
                          This line contains valuable debugging details. So post this line & surrounding lines.


                          - I dont  understand  sb ..
                          • 25. Re: Cant connect my database using net service_name
                            TSharma-Oracle
                            These seems to be right entries. Just reload the listener and try connecting to db again.
                            • 26. Re: Cant connect my database using net service_name
                              972355
                              $ export ORACLE_SID=orcltest
                              [oracle@linuxserver ~]$ sqlplus /nolog

                              SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 9 20:51:24 2013

                              Copyright (c) 1982, 2005, Oracle. All rights reserved.

                              SQL> conn u1/u1@ORCLMYTEST
                              ERROR:
                              ORA-12514: TNS:listener does not currently know of service requested in connect
                              descriptor

                              - MAY i  know what is the exact error here ?

                              As per experts suggestion i changed tnsnames.ora file entries. then what is error here ?
                              • 27. Re: Cant connect my database using net service_name
                                sb92075
                                969352 wrote:
                                Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.

                                This line contains valuable debugging details. So post this line & surrounding lines.
                                This line contains valuable debugging details. So post this line & surrounding lines.
                                This line contains valuable debugging details. So post this line & surrounding lines.
                                This line contains valuable debugging details. So post this line & surrounding lines.
                                This line contains valuable debugging details. So post this line & surrounding lines.


                                - I dont  understand  sb ..
                                edit file /u01/app/oracle/product/10.2.0/db_1/network/log/listener_orcl.log

                                find records that contain the value "12514"

                                post back here the records that contain value "12514" & a few records above & below the records containing "12514"
                                • 28. Re: Cant connect my database using net service_name
                                  TSharma-Oracle
                                  Are you able to connect to your second instance using network service?
                                  • 29. Re: Cant connect my database using net service_name
                                    972355
                                    These seems to be right entries. Just reload the listener and try connecting to db again.

                                    *$ lsnrctl reload*

                                    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 09-MAR-2013 20:23:32

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

                                    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                                    The command completed successfully

                                    $ export ORACLE_SID=orclprod
                                    [oracle@linuxserver ~]$ export ORACLE_SID=orcltest
                                    [oracle@linuxserver ~]$ sqlplus /nolog

                                    SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 9 20:55:22 2013
                                    Copyright (c) 1982, 2005, Oracle. All rights reserved.

                                    SQL> conn u1/u1@ORCLMYTEST
                                    ERROR:
                                    ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
                                    1 2 3 4 Previous Next