Can't connect to database with SQL Plus.

Hi. I need help creating a database with Oracle 10g.

I've been trying on and off for about a month to get this up and running. I'm an application developer, not a DBA, but we need a database for a testing environment...

I've searched Google and the Oracle documentation far and wide. It seems making a database is very easy.

However, I'm never, ever, able to actually CONNECT to the database. I've got SQL Plus, and it's able to connect to our hosted databases by adding their entry to the tnslistener.ora.

Is there something I'm missing? I always get ORA-12505 or ORA-12154 when I try to connect to these.

This is all done on 1 PC so everything is local, that way I can rule out any network issues before moving on.

The PC's host name is "sodium". I've tried changing the hostnames from localhost to sodium in the tnsnames.ora file but this seems to have no effect.

Here's my tnsnames.ora file - is this file only for sqlplus or does it affect the Oracle DB server as well? Also, when I change this, do I need to restart all the Oracle services? Note that the KILI connection works perfectly to this remote host.

---
# tnsnames.ora Network Configuration File: D:\oracle\product\10.1.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

KILI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = kili.**********)(PORT = 1521))
)
(CONNECT_DATA =
(SID = D36E)
)
)

#ORA1 =
# (DESCRIPTION =
# (ADDRESS_LIST =
# (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
# )
# (CONNECT_DATA =
# (SID = ORA1)
# )
# )

ORA1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora1)
)
)

FINDER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SID = finder)
)
)

PUBLICOM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = publicom)
)
)

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

Here is my listener.ora

---
# listener.ora Network Configuration File: D:\oracle\product\10.1.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.1.0\db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
)
---

I havn't touched anything in the listener.ora, this is how it came after the install.



Also, I am able to do the following under the command prompt

SET ORACLE_SID=ORA1
sqlplus /nolog
connect system/ora1

This connects me properly. I can also repeat this with SID=finder, and SID=publicommon. Oddly enough, I *cannot* connect to KILI using this method, which works fine with SQLPlus.

Also,

lsnrctl stat

returns :

----

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 07-JUL-2005 11:32:36

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Production
Start Date 07-JUL-2005 10:19:50
Uptime 0 days 1 hr. 12 min. 47 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oracle\product\10.1.0\db_1\network\admin\listener.ora
Listener Log File D:\oracle\product\10.1.0\db_1\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Thanks,
Greg
cormierg1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

paquicubaCommented:
Do you have more than one version of ORACLE installed in the same machine?
cormierg1Author Commented:
No. Oracle 10g is the only version installed.
slightwv (䄆 Netminder) Commented:
first: the tnsnames.ora file is used by all oracle products to retrieve DB connect information.

As you can see from the listener status:  There is not an instance being listened for.

10g (and I think 9i) DBs are supposed to automatically register with the listener.  I have found this to not always be the case.  There are a few smoke an mirrors tricks you can do.

If you are 100% sure the instance is up and running properly on the machine:

Here's the one I would try first.  First I suggest always using IP address in these files instead of hostname.  There are times with multiple NICs in a box that Oracle can get confused.

Add the following entry to your tnsnames.ora file (replace <INSTANCE_NAME> with the actual name of the instance):
LISTENER_<INSTANCE_NAME> =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

then connect directly to the instance:

sqlplus /nolog
SQL> connect / as sysdba


then set the local_listener parameter:

alter system set local_listener='LISTENER_<INSTANCE_NAME>' scope=spfile;
shutdown immediate;
startup;


then try the: lsnrctl status and verify it sees the instance.
 
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

paquicubaCommented:
Is this 'Kili' a remote database?

KILI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = kili.**********)(PORT = 1521))
)
(CONNECT_DATA =
(SID = D36E)
)
)

Get rid of 'localhost' and use your 'host name'  --> (HOST = localhost)  
cormierg1Author Commented:
slightwv : I'm not sure what all that is doing, could you explain it? The thing is, I get "TNS:Listener does not know of SID in given..." so the listener must be running, it just can't find the SID. Also, I am able to connect from the command prompt version. Doesn't that imply the listener is working in some way?

paquicuba : KILI is a remote DB, and it DOES work through SQLPlus.



I can either use localhost, or 127.0.0.1. The computer is set up on DHCP, and it's impossible to get it set up with a static IP on our network. I changed all the localhosts to 127.0.0.1 in tnsnames.ora and this has had no effect.

Everytime I change tnsnames.ora do I need to restart all the Oracle services?

Thanks,
Greg
paquicubaCommented:
Oracle doesn't work on DHCP computers, you need to install a loopback adapter to assign a local IP address to that computer.

Check this out:

http://download-east.oracle.com/docs/html/B13693_02/pre_require.htm#BABBDGBI
slightwv (䄆 Netminder) Commented:
OK here it goes.  I hope I catch most of it (it's a lot to cover here).

The listener is running but it does not know about the oracle instance that is running.  You need to tell the listener about it.  Again, in 9i and 10g the DB does this when it starts up but there are times when you restart the listener and not the DB that Oracle gets a bit confused.

The best thing to do is force the DB to register with the listener by hardcoding the pieces (there are a couple of ways.  I chose one of them to post).

You can connect from any version of SQL*PLus as long as you don't specify a server/instance.  In the windows version just fill out the username and password.  Leave 'Host String' blank.  This is a direct connect to the DB and does not use SQL*Net at all.  It bypasses the tnsnames.ora configuration and doesn't look for a listener.

Filling out the 'Host String' on the windows login is the same as specifying it on the command line version like:  connect user/password@SOMEDB

As soon as Oracle sees '@SOMEDB' it looks for the SOMEDB entry in the tnsnames.ora file and then goes in search of a linstener based on that entry.  The listener then accepts the request and sees if it is responsible for obtaining connectiong for the instance it was passed.  Then more magic happens and you get a connetion.



I've never used Oracle with DHCP but I'm thinking as long as the machine ALWAYS gets the same IP address it should be OK.  Oracle networking and a few other server processes are very tied to the network configuration and all kinds of problems happen when you change it.
anand_2000vCommented:
if you are on the machine where u want the db created then
1) c:\set oracle_sid=orcl -- assuming that the database u want to create has the sid orcl
2) c:\sqlplus
username: sys as sysdba
password:<the password for ur datbase>

and u will be in.
schwertnerCommented:
I gave a solution for this Oracle  ..... feature!

http://www.experts-exchange.com/Databases/Oracle/Product_Info/Q_21426652.html

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
>> I gave a solution for this Oracle  ..... feature!

That is one of the other ways I was talking about.  ;)

It just depends on if you want the listener to be forced to know about the database or have the database tell the listener about itself.  Both work.
cormierg1Author Commented:
Why would setting up a loopback listener help? No remote PC's will be able to connect to the database since it's a loopback!?

anand : The dos prompt version already works. I want the windows version to work, and other services (websphere) to be able to use the DB...

I will try the link schwertner sent.
cormierg1Author Commented:
Schwert, your previous solution did the trick. Perfect!

It's running fine now using "localhost".
anand_2000vCommented:
not required... a simple solution will be to go to My computer properties->advanced and then set an environment variable
ORACLE_SID with the values orcl.

Why go all the way aroung to configure the loopback listener and then the tnsnames configuration?
schwertnerCommented:
cormierg1, this is not a trick! This is a MUST for the Listener to work correctly to serve connection to
other machines.
I am curious if Oracle has changed this   ...  feature :-)))) in Release 2 of 10g!
What's yours expectation, guys???
slightwv (䄆 Netminder) Commented:
>>What's yours expectation, guys???

If they fixed this then they broke 20 other things!!!!!  I'm waiting for R2 for some new features promised in it:  Use of wildcards on CTXRULE indicies.  I hope it's stable enough for production but I doubt it.
schwertnerCommented:
I have just installed 10g release 2 on Linux.

I can connect from other machine to the new created instance without making changes in LISTENER.ORA.
There is an entry to create handle to the Listener port of the 10g Rel.2 server machine.
cormierg1Author Commented:
Anand, you realize that will work for only 1 database.. and also, it will only work for the local PC? Why would you go to all that trouble to have it not actually working correctly?
slightwv (䄆 Netminder) Commented:
schwertner,

Unfortunatly, I have to wait for the Windows release (I miss UNIX at times).  Don't keep us in suspense:  What's the new tricks??

anand_2000vCommented:
my bad,
I was under the wrong impression that this is only for ur local machine.
schwertnerCommented:
Release 2 on 10g sofar runs well. The installation runs smootly. These days I will import the database there to see what are the new bugs.
There are two interesting things:
1. The both shell scripts have to be run at the very end of the installation.
2. As in the previous version the installation invokes DBCA without giving possibility to choose the character set.
So the DB is created with WEISO... character set and one have to drop it and to create another with DBCA.
schwertnerCommented:
Damn it!

I just have tried to restart 10g Release 2 and it didn't restart!

Same missing lines in LISTENER.ORA!
After the fix it seems to work well!

The import of the 9i database was OK.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.