This discussion is archived
1 2 3 4 6 Previous Next 79 Replies Latest reply: Mar 13, 2013 8:36 AM by EdStevens Go to original post RSS
  • 15. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Check my edited response.
  • 17. Re: Cant connect my database using net service_name
    sb92075 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    $ 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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Are you able to connect to your second instance using network service?
  • 29. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    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 6 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points