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

This article has been moved to my new blog at edstevensdba.com.

 

Click here to go to the new location of this article.

63 thoughts on “ORA-12514: TNS:listener does not currently know of service

  1. Pingback: ORA-12154: TNS:could not resolve the connect identifier specified - dBforums

  2. Ed,

    Thanks for the clear explanation on how to statically register an instance. I wish I had found that earlier. But, in any regard, I think I followed what you are saying, and I still can’t get my instance to show up in the lsnrctl status. Can you think of anything that I can do to troubleshoot? I posted this question on OTN, and it had my listener.ora.

    https://forums.oracle.com/forums/thread.jspa?messageID=10397221

    I think I need this to do a rman duplication. I want to clone another database to this one, so I need a “remote” connection to the not-yet-created database.

    Any suggestions you can think of to try would be very helpful.

    Thanks,

    Stephen

    • Stephen,

      Check my reply on OTN. You mis-spelled the header for the SID_LIST section of your listener.ora file. You have “SID_LIST_LISTNER =”, should be “SID_LIST_LISTENER”. Missing the first “E” in LISTENER.

      • In sqlplus, the ‘@’ means “read this file (default extension of .sql) and execute the statements contained in it.

        So “@doit” is telling sqlplus to execute the file ‘doit.sql’. What was actually in ‘doit.sql’ is clearly shown in the lines that follow it in the examples.

  3. Pingback: ORA-12514: TNS:listener does not currently know of service « dbtechtalk

    • Adam – “doit.sql” is just the name I use for all my one-off, throw-away, demo scripts. In this case, it’s all right there in the post. The first line of the script is “set echo on”, so the file is nothing more than the commands you see after “@doit”.

  4. Thanks Ed,

    this was very clear and very helpful (I fixed the issue, but need to spend more time reading your blogs related to Oracle)

    thanks,
    Dave

  5. Ed:
    Hi,Please “Ctrl + F” to search “Since this was fully qualified in the service_names initialization parm, the value of db_domain was not applied”.
    Why db_domain was not applied? Can you tell me clearly? Thanks advance.

    Lonion

  6. Thanks, man. This was a great refresher on how this stuff works and helped me solve an issue I’ve been messing with all day.

    • First, let’s simplify and ignore your mention of default listener.

      Then yes, as shown in the figure 10 and its associated discussion. The database will always register a service using the value of db_name. That parameter is one of the few required to actually start an instance regardless of any service registration considerations. If you set also set db_domain (optional), that value will be appended to the value of db_name to create a service name. The service_names parameter can also be used (again, in addition to db_name) to register service names that have no syntactical (is that a word?) relationship to db_name.

      Now, why did a say to set aside the consideration of ‘default listener’?

      Because the creation of service names is totally independent of what listener the instance is registering with. See my discussion of the local_listener parameter here to see how the instance finds a listener with which to register its service names.

  7. Hi Ed,

    You have written a wonderful post, but unfortunately i cant make it work. All my listeners are in status UNKNOWN

    I am a complete newbie in oracle. Kindly guide.

    What am i doing wrong.

    Configuration : Windows XP Pro Service Pack 3 / Oracle Express Edition 11g

    _____________________________________________
    D:\oraclexe\app\oracle\product\11.2.0\server\bin>lsnrctl

    LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 – Production on 29-DEC-2013 23:50
    :48

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

    Welcome to LSNRCTL, type “help” for information.

    LSNRCTL> status
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    STATUS of the LISTENER
    ————————
    Alias LISTENER
    Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 – Produ
    ction
    Start Date 29-DEC-2013 23:17:34
    Uptime 0 days 0 hr. 33 min. 15 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Default Service XE
    Listener Parameter File D:\oraclexe\app\oracle\product\11.2.0\server\network\a
    dmin\listener.ora
    Listener Log File D:\oraclexe\app\oracle\diag\tnslsnr\localhost\listener
    \alert\log.xml
    Listening Endpoints Summary…
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.101)(PORT=1521)))
    Services Summary…
    Service “CLRExtProc” has 1 instance(s).
    Instance “CLRExtProc”, status UNKNOWN, has 1 handler(s) for this service…
    Service “PLSExtProc” has 1 instance(s).
    Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
    Service “vlnxora1” has 1 instance(s).
    Instance “vlnxora1”, status UNKNOWN, has 1 handler(s) for this service…
    The command completed successfully
    ______________________________________________
    tnsnames.ora
    XE =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = SHARED)
    (SERVICE_NAME = XE)
    )
    )

    vlnxora1 =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
    (CONNECT_DATA =
    (SERVICE_NAME = vlnxora1)
    )
    )
    _______________________________________
    listener.ora
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = D:\oraclexe\app\oracle\product\11.2.0\server)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = D:\oraclexe\app\oracle\product\11.2.0\server)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = vlnxora1)
    (SID_NAME = vlnxora1)
    (ORACLE_HOME = D:\oraclexe\app\oracle\product\11.2.0\server)

    )
    )

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

    DEFAULT_SERVICE_LISTENER = (XE)
    ____________________________________________
    tnsping

    D:\oraclexe\app\oracle\product\11.2.0\server\bin>tnsping vlnxora1

    TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 – Production on 30-DEC-2
    013 00:02:52

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

    Used parameter files:
    D:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.
    0.101)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = vlnxora1)))
    OK (40 msec)

    • Instance status of ‘unknown’ simply means that the instance is registered with the listener due to it inclusion in the SID_LIST section of listener.ora. It is no guarantee that there is actually an instance by that name. Likewise, the successful tnsping means only that the client was able to resolve your net service name (vlnxora1) to an address, and there was a listener at that address. It proves nothing about the state (or even the existence) of that instance. See my post https://edstevensdba.wordpress.com/2011/02/27/tnsping-101/ for more information on what tnsping does and doesn’t do.

      What you haven’t shown me is the error message you get when you “can’t make it work”. Given that the listener status does not show an instance status of ‘ready’ I’d guess that you do not have a running database instance named ‘vlnxora1’.

  8. In your experience you might have skated around the most difficult issue in tnsnames.ora – that is the very poor parsing that Oracle apply to reading the text. I’ve seen, on numerous occasions, tnsnames.ora files that looked okay – but didn’t get completely parsed. My advice is always to cut and paste a working ‘part’ of the file and never to try to replicate the format by hand, because it will fail. This is a killer for those new to Oracle ( very similar to the bugs in SQLPlus where an innocuous-looking empty line causes a grammatically correct-SQL to fail.)

    • Valid point that I should have mentioned. I’ve never been able to nail it down, but early on in my experience with Oracle I had come across situations where the parsing of tnsnames seemed to be sensitive to extra spaces or other seemingly innocuous format details. I, too, am in the habit of copying a working entry or using netca to create new entries.

  9. Hi,
    When i tried tnsping localhost in the system where server is installed, i didn’t get any service name in the output. SO i was not able to proceed further.
    Please find ora files as follows:
    tnsnames.ora
    # tnsnames.ora Network Configuration File: C:\app\VIVIVERM\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.

    LISTENER_ORCL =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    ORACLR_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
    (SID = CLRExtProc)
    (PRESENTATION = RO)
    )
    )

    ORCL =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl.us.oracle.com)
    )
    )

    sqlnet.ora
    ———–
    # sqlnet.ora Network Configuration File: C:\app\VIVIVERM\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
    # Generated by Oracle configuration tools.

    # This file is actually generated by netca. But if customers choose to
    # install “Software Only”, this file wont exist and without the native
    # authentication, they will not be able to connect to the database on NT.

    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

    listener.ora
    ————-
    # listener.ora Network Configuration File: C:\app\VIVIVERM\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
    # Generated by Oracle configuration tools.

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = C:\app\VIVIVERM\product\11.2.0\dbhome_1)
    (PROGRAM = extproc)
    (ENVS = “EXTPROC_DLLS=ONLY:C:\app\VIVIVERM\product\11.2.0\dbhome_1\bin\oraclr11.dll”)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = CLRExtProc)
    (ORACLE_HOME = C:\app\VIVIVERM\product\11.2.0\dbhome_1\BIN)
    (SID_NAME = CLRExtProc)
    )
    )

    LISTENER =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.240.101.204)(PORT = 1521))
    )

    ADR_BASE_LISTENER = C:\app\VIVIVERM

    I am not able to connect to the server using sql plus(using user@host:port:service) or sql developer. However i can connect by using connect command directly.

    Please suggest the changes in these files andwhat should be the tns details to be used .

    Thanks
    Vivek

    • Your TNS file specifies HOST=localhost. ‘localhost’ always resolves to IP address 127.0.0.1, which is a loopback address. No request to that address ever leaves the requesting machine.

      Then your listener specifies that it is listening on IP address 10.240.101.204. That is the address that should be specified in the HOST parameters in your tnsnames.ora.

      • Hi Ed Stevens,
        Thanks for the reply.
        I changed tnsnames.ora to specify host =10.240.101.204
        Now i used the following parameters to connect:
        host=10.240.101.204
        port=1521
        sid=CLRExtProc

        Now this is giving following error:
        Status : Failure -Test failed: Listener refused the connection with the following error:
        ORA-12518, TNS:listener could not hand off client connection

        Please suggest.

        Thanks
        Vivek

      • ALso the result of lsnrctl services is as follows:
        LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 – Production on 04-FEB-2014 22:37
        :31

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

        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=slc01nva.us.oracle.com)(
        PORT=1521)))
        Services Summary…
        Service “CLRExtProc” has 1 instance(s).
        Instance “CLRExtProc”, status UNKNOWN, has 2 handler(s) for this service…
        Handler(s):
        “DEDICATED” established:0 refused:0
        LOCAL SERVER
        “DEDICATED” established:0 refused:2
        LOCAL SERVER
        Service “orcl.us.oracle.com” has 1 instance(s).
        Instance “orcl”, status READY, has 1 handler(s) for this service…
        Handler(s):
        “DEDICATED” established:267 refused:0 state:ready
        LOCAL SERVER
        Service “orclXDB.us.oracle.com” has 1 instance(s).
        Instance “orcl”, status READY, has 1 handler(s) for this service…
        Handler(s):
        “D000” established:0 refused:0 current:0 max:1022 state:ready
        DISPATCHER
        (ADDRESS=(PROTOCOL=tcp)(HOST=slc01nva.us.oracle.com)(PORT=62548))
        The command completed successfully

      • Thanks Ed Stevens for the replies. I mapped these files with a database to which i could connect and modified file accordingly. Now i am able to connect. Just i replaced service name=orcl.us.oracle.com to sid=orcl.

      • Glad you got it working, but the preferred method is to specify service_name instead of sid. I’d suggest you review my post on ORA-12514 to see how to identify the services registered to your listener, and adjust your tnsnames accordingly.

  10. Very good explanation, thanks. I’m currently troubleshooting an issue. Getting
    UDE-12520: operation generated ORACLE error 12520
    ORA-12520: TNS:listener could not find available handler for requested type of server when trying to run an export, similar using sqlplus. I have an application down to get an export.

    Listener log entries: TNS-12520: TNS:listener could not find available handler for requested type of server

    • I don’t recall ever dealing with a TNS-12520 myself, but google turned up some interesting hits. Commonly, this is caused by the db reaching max sessions and not being able to accept any more. Does ‘lsnrctl status’ report any ‘blocked’ services? What is the value of the init parm ‘sessions’, and the count of current sessions when you get this error?

      SQL> show parameter processes
      SQL> select count(*) from v$session;

      If, when you are experiencing this error, the count of v$session is at or very near the value of ‘processes’, you should get some temporary relief by increasing the value of the ‘processes’ parameter. I say ‘temporary’ because if the root cause is an ill-behaved application that is not releasing sessions, you will eventually hit the wall again. However, if the app is reasonably well designed but simply has a larger user population, the increase of ‘processes’ is appropriate and should fix your issue.

  11. Pingback: Connecting to an Oracle Database using SSIS 2008 | Sjoukje Zaal's blog

  12. HI Ed,
    Thanks for a great post. I am experiencing an odd error that I hope you can provide some guidance with. At the client end (Windows server) I have set up a new TNS connection to an 11G database located on a local government’s server (Oracle client 11.2.0 installed). (We are already successfully connecting via TNS to another database server within this local government’s network from this windows server-have been for years.) This new TNS connection fails through SQL Developer (ORA-12520) and SSIS (ORA-12514), but I can test it successfully through ORACLE NET Manager. The connection strings are very similar in format between the TNS that is working and the one that is not. The only differences being host server, port number and service_name. I have no access into the local goverment’s network, though they assure me they can connect via TNS to this new database internally. The service_name has full stops in it, but I can’t imagine that being the issue. TNSPING works, but as you mentioned, that only confirms the host name. I can’t find anything online to assist. Is there anything you can suggest?
    many thanks,
    Claudia

    PS: Format of TNS connection to server that is not working
    test =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = AB_nonProd.internal.xxxx.xxxx.xxxx)(PORT = 28300))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ‘aaa_xxx.xxxx)
    (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))
    )

    • Claudia –
      You report both ora-12514 and ora-12520. Let’s take them one at a time.
      Your tnsnames shows that you are requesting a service name of ” ‘aaa_xxx.xxxx “. Note the leading single-quote? Is that a typo that you transcribed into your post, or is that what’s really there? In any event, whatever you specify as SERVICE_NAME in your tnsnames must match EXACTLY with a service name as reported by the listner when you issue a “lsnrctl status”. And by EXACTLY, I mean case counts. If it doesn’t match exactly, you’ll get ora-12514. To truly debug, I’d want to see the exact output of tnsping on the client machine and lsnrctl status on the db server. You say you can test “succesfully” with netmgr, but get the 12514 with SSIS. That means they are not using the same connection information. Where is SSIS getting it’s connection information? Do you possibly have two ORACLE_HOMEs on this machine?

      The ora-12520 is a bit different, and I can’t recall the last time I actually saw one. You said you get that error when connecting with SQL Dev. Depending on how you have your connection defined, you may not be using the tnsnames at all. If you define your connection as “basic”, it’s using either JDBC or Eazyconnect (honestly, I don’t know which but I think its jdbc) and so does not need tnsnames to resolve the address information. Anyway, when I googled ora-12520, it seems that it could be related to the database setting for PROCESSES. Else, you could be asking for a SHARED (as opposed to DEDICATED) connection, and have not yet configured SHARED_SERVERS and DISPATCHERS in the database.

      FWIW, I find it VERY odd that you specify to connect on port 28300. The default port for the listener is 1521. How did you arrive at 28300?

  13. Just wanted to add a quick reminder that it is very important to remove the listener.ora file completely when trying this. I tried on 12c standard windows version but didnt follow the instructions and removed any auto generated entries for my cdb and pdb’s in listener.ora but kept the CLREXTPROC entry.

    I couldnt get the listener to recognize any instances/services with that entry in place – regardless of how many times i tried to register the services. Once I remove the entry, the listener immediately recognized the running services after the db was bounced and everything worked normally through dynamic registration with no need for any manual registration.

    Just wanted to share my experience. Great article. Thanks!

    • I haven’t started playing around with 12c yet, but my guess is there is something else at play here. The only reason I deleted my listener.ora for the demo was to make it abundantly clear that there was no way a database was getting registered unless the database itself contacted the listener. I could have just as easily deleted the SID_LIST section and kept the rest.

      • Totally agree Ed. I’m sure something went wrong during the install – which is not uncommon IMHO for oracle installs on windows. I just wanted any other poor soul that hit a similar issue to know that one option to circumvent the error is to remove the listener.ora file. At some point I’ll need to spend the time to determine what the underlying problem was. Thanks again.

  14. Hi;
    I am using below string in VB.Net 2012 64 bit.
    my application(64 bit) into client win7 (64 bit)
    and oracle database 10g 32 bit on the win 2003(32 bit) , other server

    Dim strCon As String = “DATA SOURCE=Host-Name:Port/service-name;PERSIST SECURITY INFO=True;USER ID=user-name;Password=password;”
    Dim conn As New OracleConnection(strCon)
    conn.Open()

    Regard;
    Hossein Mokhtari

    • First off, I have taken the liberty of removing your email address from you post. Putting your email address into a public posting is an open invitation for every web spider on the planet to harvest the address and flood your inbox with offers for either certain, uh, ‘anotomical enchancement’ products or to cut you in on the action if you will help launder money out of Nigeria – “all we need is access to your bank account”.

      Now, to your question.
      I’m not a vb .net guy, but comparing your presentation to examples I find all over the web, it looks like you copied a syntax diagram instead of providing actual information. I see nothing in your assignment to strCon to identify an actual oracle database. No host name or IP address, no actual port number, no actual service name, nor anything that looks like a net service name (tnsnames entry) from which to derive the above. I also don’t see where you provide a “provider name”, and as I understand it, the provider being used will be a big determinant on how to specify your connection string.

      You did not state the actual error your were getting. Since you are posting to my page on ORA-12514, one might assume that is your error, but given your code, I do not see how your request could have gotten far enough to return that error.

    • Uhh…
      Yes, that is one way of specifying the LOCAL_LISTENER parameter and registering the instance with the listener. Is there a comment or question here?

      BTW, if your listener is listening on ‘localhost’ (ip address 127.0.0.1) it will never receive a request from any remote client. Network requests to localhost never leave the requesting machine.

  15. Ed, Very informative article. Thank you for putting the effort into sharing the knowledge. I am having an issue I am hoping you can tell me where I have gone wrong in my troubleshooting. I am new to Oracle. Any help would be greatly appreciated.

    I have an Oracle 9 installation that was working fine until yesterday. Only two changes that have occurred were:

    1) A network DNS change not directly related to this server.
    2) One of the mirrored OS drives was degraded on this server. It was rebuilt without issue and the server did not “Crash”

    My clients can no longer connect to the “Item” database.

    On the actual server I am receiving the ORA-12514: TNS listener could not resolve SERVICE_NAME given in connect descriptor error when I execute the following command:

    C:\sqlplus app_prc/password@item

    My tsanames.ora is as follows:

    ####################################
    # TNSNAMES.ORA Configuration File:D:\oracle\ora81\NETWORK\ADMIN\tnsnames.ora
    # Generated by Oracle Enterprise Manager V2
    # Date……….: Tue Feb 15 14:11:43 CST 2005
    ####################################

    ITEM =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.**.**.52)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = item))
    )

    EWJAMES =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.**.**.12)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = ewjqa))
    )

    # mycompany PRD instance
    EWJPRD.tmx.com =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.**.**.12)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ewjprd)
    )
    )

    SG =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver_db)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = sg01.myserver_db.myorg.com)
    )
    )

    BOOMI_EWJ8 =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver8)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = BOOMI))
    )

    BOOMI =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver8)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = BOOMI))
    )

    RAMP =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver8)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = RAMP))
    )

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA = (SID = PLSExtProc)(PRESENTATION = RO))
    )

    RAMP_EWJ8 =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver8)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = RAMP)
    )
    )

    ———————————————————
    The very first SERVICE_NAME = item is the DB to which I need to connect.
    ———————————————————
    TNSPING result:

    C:\Documents and Settings\Administrator.mycompany>tnsping item

    TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 – Production on 28-MAR-20
    15 13:37:13

    Copyright (c) 1997 Oracle Corporation. All rights reserved.

    Used parameter files:
    D:\oracle\ora92\network\admin\sqlnet.ora

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
    (HOST = 172.**.**.52)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = item)))
    OK (20 msec)

    ———————————————————————
    So This confirms we have a listener at the specified address and port
    ——————————————————————-

    lsnrctl status result:

    C:\Documents and Settings\Administrator.mycompany>lsnrctl status

    LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 – Production on 28-MAR-2015 13:42:
    00

    Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver8)(PORT=1521)))
    STATUS of the LISTENER
    ————————
    Alias ITEM
    Version TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 – Produc
    tion
    Start Date 27-MAR-2015 20:41:47
    Uptime 0 days 17 hr. 0 min. 13 sec
    Trace Level off
    Security OFF
    SNMP OFF
    Listener Parameter File D:\oracle\ora92\network\admin\listener.ora
    Listener Log File D:\oracle\ora92\network\log\item.log
    Listening Endpoints Summary…
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.**.**.52)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=EWJ8.ewjames.com)(PORT=8080))(Presen
    tation=HTTP)(Session=RAW))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myserver8.mycompany.com)(PORT=2100))(Presen
    tation=FTP)(Session=RAW))
    Services Summary…
    Service “BOOMI.world” has 2 instance(s).
    Instance “BOOMI”, status READY, has 1 handler(s) for this service…
    Instance “RAMP”, status READY, has 2 handler(s) for this service…
    Service “PLSExtProc” has 1 instance(s).
    Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
    The command completed successfully

    ———————————————————-
    The listener does not seem to know about SERVICE_NAME = item

    However as noted above my tnsnames.ora does contain a SERVICE_NAME = item

    —————————————————————-

    Contents of my listener.ora:

    # LISTENER.ORA Network Configuration File: D:\oracle\ora92\network\admin\listener.ora
    # Generated by Oracle configuration tools.

    ITEM =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.**.**.52)(PORT = 1521))
    )
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    )
    )

    RAMP =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver8)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = RAMP))
    )

    SID_LIST_ITEM =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ENVS=EXTPROC_DLLS=ANY)
    (ORACLE_HOME = D:\oracle\ora92)
    (PROGRAM = extproc)
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver8)(PORT = 1521))
    )
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    )
    )

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = BOOMI)
    (ENVS=EXTPROC_DLLS=ANY)
    (ORACLE_HOME = D:\oracle\ora92)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (SID_NAME = RAMP)
    (ENVS=EXTPROC_DLLS=ANY)
    (ORACLE_HOME = D:\oracle\ora92)
    (PROGRAM = extproc)
    )
    )
    ——————————————————————–
    I noted that my listener.ora does NOT contain an entry for “item” in the SID_LIST_LISTENER section:

    However I do not know if the entry was ever there as the server has been fine until yesterday.
    ———————————————————————
    The status of the Oracle services:

    OracleMTSRecoveryService STARTED AUTOMATIC
    OracleOraHome92Agent STARTED AUTOMATIC
    OracleOraHome92ClientCache MANUAL
    OracleOraHome92HTTPServer STARTED AUTOMATIC
    OracleOraHome92PagingServer MANUAL
    OracleOraHome92SNMPPeerEncapsulator MANUAL
    OracleOraHome92SNMPPeerMasterAgent MANUAL
    OracleOraHome92TNSListener MANUAL
    OracleOraHome92TNSListener? MANUAL
    OracleOraHome92TNSListenerboomi MANUAL
    OracleOraHome92TNSListenerEWJSGDB MANUAL
    OracleOraHome92TNSListenerITEM STARTED AUTOMATIC
    OracleServiceBOOMI STARTED AUTOMATIC
    OracleServiceITEM STARTED AUTOMATIC
    OracleServiceRAMP STARTED AUTOMATIC

    ——————————————————————–
    Being new to oracle I am not familiar with which services should be running.

    At this point I am lost as to my next step.

    Thanks

    Todd Little

    — masked out identifying data for security – Ed Stevens

    • First off, you realize of course that Oracle 9 has been out of support for quite a few years now. Premier support ended in 2007 and even limited extended support ended in 2012. What version of Windows is this running on? What exact version of Oracle 9? (9.0, 9.1, 9.2, etc)

      Also, it’s not a good idea to post full ip addresses and/or full domain names on the internet. ‘Nuff said.

      From your list of services, I see you actually have multiple listeners, with only one of them started – the one with the non-default name of ListenerITEM. From that evidence it appears that this machine has undergone several mis-guided attempts at configuring servers. I would guess that there are several ORACLE_HOMEs installed as well. Take a look in the registry at HKLM\software\oracle.

      Also, your listener.ora looks as if it is trying to define 3 listeners, one named ITEM, one named RAMP and one with the default name of LISTENER. All three are defined to listen on port 1521, which would be a collision if all three were actually running. In truth only one is running, the one named ITEM. This is seen both in the Windows service list, and in the output of ‘lsnrctl status’, where we see the name of Listener Log File as D:|oracle\ora91\network\log\item.log. You have no SID_LIST_ITEM entry for any database at all, just the extproc (external process).

      Here’s a fundamental rule of listeners: One single listener with the default name of LISTENER and using the default port of 1521 is quite capable of — indeed, was designed to — service multiple database instances of multiple versions running from multiple homes. There is nothing to be gained by trying to run multiple listeners, by trying to give each database its own listener.

      All that aside for a moment, in spite of everything I see no reason why the db instance should not be able to register with the listener that is running. We see that the Windows service for the database is started, but that is no guarantee the database itself is started. To check this, on the database host machine, open a command prompt and issue the following, then show me the result.

      c:> set ORACLE_SID=ITEM
      c:> sqlplus / as sysdba
      SQL> select status from v$instance;
      SQL> show parameter local_listener

      • Ed,

        Thanks for the timely response. The version is O9.2i
        This server has had many masters over the years and your assessment does not surprise me. The powers that be are considering a highly recommended upgrade, but until we get a green light we have to try to keep this one running. Below are the results you requested

        C:\Documents and Settings\Administrator.EWJAMES>sqlplus “/as sysdba”

        SQL*Plus: Release 9.2.0.1.0 – Production on Sun Mar 29 16:10:47 2015

        Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

        Connected to an idle instance.

        SQL> select status from v$instance
        2
        SQL> show parameter local_listener
        ORA-01034: ORACLE not available
        SQL>

        Thanks

      • “Connected to an idle instance” means the database instance is not started. The ORA-01034 makes perfect sense at that point as it is simply saying the same thing (instance not started) in response to the ‘show parameter’ command. Connect again as sysdba and issue a startup command.

        SQL> startup
        ORACLE instance started.

        Total System Global Area 839282688 bytes
        Fixed Size 2217992 bytes
        Variable Size 494929912 bytes
        Database Buffers 339738624 bytes
        Redo Buffers 2396160 bytes
        Database mounted.
        Database opened.
        SQL>

        If it doesn’t start up cleanly take a look at the alert log. I can’t recall exactly where it will be on a non-standard install of 9i on a Windows system (I haven’t seen a 9i database in over 10 years), but its name will be alert_ITEM.log. What you are looking for in it is all of the messages beginning with the last startup. The first message of a startup is “Starting ORACLE instance (normal)”

  16. Ed,

    Result of startup command.is below. Did not find a alert_ITEM.log. Found a alert_BOOMi.log and an item.log. The Item.log cannot be open as it is use.

    C:\Documents and Settings\Administrator.EWJAMES>sqlplus “/as sysdba”

    SQL*Plus: Release 9.2.0.1.0 – Production on Sun Mar 29 17:09:11 2015

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Connected to an idle instance.

    SQL> startup
    ORA-27100: shared memory realm already exists
    SQL>

    • ok, you’ve got something much more serious than listener issues, and those need to be addressed first.
      First, there has to be an alert_*.log for every database on the system. You said you found an alert_BOOMI.log.
      – What was the full path to that file?
      – What exact version of Windows is this on?
      – 32-bit or 64-bit.
      – Look in D:\oracle\ora92\database for any files named init*.ora and spfile*.ora. What do you find? I’d expect to see files for BOOMI, ITEM, and RAMP.
      The init*.ora files are pure text and can be viewed with notepad. The spfiles are actually binary files even though most of their contents are clear text. A safe way to examine the contents would be to make a copy, then open that copy with notepad. I’d like to see the contents of the file for the database in question.

      – You have 3 databases on this server. Have you confirmed the other two are open and available?

      – What exactly what has happened to this server? When I google your error message I keep getting hits about not enough available memory. If you haven’t changed anything. Could it be that the SA changed the /3gb or the /uae settings? You said a mirrored drive was rebuilt without issue and without reboot. Could it be that the SA had to restore any files (like the above init or spfiles?) from an old backup?

      This may be better discussed on OTN, where you can get more eyeballs on it. I’d suggest you open an SR with Oracle Support, but given how far out of support you are, I doubt you’d get very far. Might be worth a shot.

    • I’m afraid I am not available as a paid consultant.
      Looking over your init and spfiles, I see nothing amiss there.
      Looking over your alert log I see lots of unanswered questions. Friday evening the database was started 6 times between 1556 and 2042 and appears to have hung each time very, very early in the process. Each startup begins with “Starting ORACLE instance (normal)” Compare those last few to the first ones in the alert log.

      I’m afraid this is going to require Oracle support, or at least an on-site consultant to figure out. I hope you have a sound backup. It looks like, from your spfile, that you are not running this db in archivelog mode, which only serves to increase your risk.

      Sorry I coulnd’t be more help.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s