I am using Oracle 10g R2. Recently, after rebooting the server, I started having a problem where I couldn't connect to the instance. I am only connecting locally on the server itself.
Oddly enough, the issue corrects itself if I start the Database Administration Assistant, and select my instance to supposedly change its settings.
Does anybody have a clue on the roots of this problem?
EDIT: I don't think I read your question properly: The listener should not affect connections on the local machine, so you can probably ignore the rest of the answer, unless it gives you a hint! How were you testing your connection? Was ORA-12514 the only error?
(I'm assuming you're on Windows here) I guess the listener is not starting automatically when you reboot the server, and it's getting starting in oracle administration assistant - I don't use that tool unfortunately so couldn't say.
Next time you reboot, before starting oracle administration assistant, open a command prompt and type lsnrctl status. If the listener has not yet started you will get something like this:
C:\Documents and Settings\user>lsnrctl status
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 27-OCT-2008 14:00:21
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC01)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server.domain.co.uk)
(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 61: Unknown error
C:\Documents and Settings\user>lsnrctl status
if it is running, you will get something like this:
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 27-OCT-2008 14:03
:33
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC01)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 27-OCT-2008 14:03:27
Uptime 0 days 0 hr. 0 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC01ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server.domain.co.uk)(PORT=1521))
)
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCL1" has 1 instance(s).
Instance "ORCL1", 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...
The command completed successfully
C:\Documents and Settings\user>
If the listener is not starting, check that service is set to automatic. If it is, check the listener.ora makes sense, see what output you get from lsnrctl start, etc.
Hope that helps, or at least sends you down the right path
@akaDruid: I am testing my connection simply by trying to start SQLPlus on the server.
You haven't specified if this is windows or unix?
I've seen similar issues on unix when ORACLE_HOME was defined slightly differently on the account which starts up oracle, and on connecting accounts, one account had ORACLE_HOME=/usr/oracle , the other had ORACLE_HOME=/usr/oracle/ , the trailing slash messed things up.
How exactly are you staring up the server, do you have a script to do it, or are you connecting internally and issuing "startup"
again, if this is windows, I have no idea :)