unable to connect to database

[sjpknight]

Hi - I'm an Oracle newbie having just installed it on my winxp box for a university project. I've been trying for some time now to connect to it using sql+ but haven't been able to do so. I think I've got my tnsnames.ora file configured correctly:

SIMONDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.162)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = Oracle)
)
)

I'd like to be able to connect using the service name "Oracle", but it just doesn't seem to work! Any help would be much appreciated!

[hrishy]

Hi

The tns entry should look like this

oracle =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.162)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = Oracle)
)
)

and then you could do something like

sqlplus scott@oracle

[sjpknight]

Hi - I've tried changing the tns entry as per below, but still no joy connecting unfortunately. Just to elaborate a bit - I can perform sql queries etc using the 10g DB homepage, but when I try and do the same thing using sql+, or any application that requires me to connect using a host string, I get a "ORA-12514: TNS: Listener could not resolve SERVICE_NAME given in connect descriptor" error.

I wonder whether something went wrong with the install as I get the impression from some documentation that I should be able to configure this kind of stuff during installation, but I didn't seem to have the option to do that (installed 10g Express Edition).

I'm currently doing all my work using a SYSTEM login - would creating an additional user help at all?

Yours cluelessly.....

[davey23uk]

your tns setup is wrong, the listener doesnt know what you are trying to connect to

[sjpknight]

Um - Ok. So I've attempted to create a listener using the net configuration assistant and have ended up with this:

ORCLISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = home.knet)(PORT = 1521))
)
)
)

in my network/admin folder under listener.ora. However, the assistant advises that "the information provided for this listener is currently in use by other software on this computer. You can proceed with the configuration as it is, but it will not be possible to start this listener until the conflict is resolved etc." This message is displayed even after an Oracle restart and a WinXP reboot. And I still can't use SQL+.

Did I mention that I was a newbie? Some more detailed assistance would be extremely gratefully recieved :-)

[davey23uk]

do

lsnrctl status

see what services your listener is actually listening for (you do actually have a database right?)

[sjpknight]

Hi again - ouput from lsnrctl status as below:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 11-OCT-2007 12:39:29
Uptime 0 days 0 hr. 13 min. 24 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File D:\oraclexe\app\oracle\product\10.2.0\server\network\admin\listener.ora
Listener Log File D:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=home)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=8080))(Presentation=HTTP)(Session=RAW))
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 "XEXDB" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "XE_XPT" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

Still not sure what the problem is though?

[davey23uk]

right so you have XE installed, but your tnsnames entry has service_name = Oracle, your service_name is xe - thats what is listed in the services

[sjpknight]

Ok - so have changed tnsnames so that it looks like

xe =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.162)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = xe)
(INSTANCE_NAME = xe)
(GLOBAL_NAME = xe)
)
)

but still no joy! Have tried connecting and still get the same error as detailed above (this is after restarting the db by the way).

[davey23uk]

remove the global_name and instance_name and also 192.168.1.162 is the IP of the server with the database on?