connecting to an oracle sql database from sql developer on the same computer. When the computer is a domain computer

Question

Hi I am trying to do DBA course that requires that I use Oracle database.

I am using my corporate pc to do it.

I set up the loopback adapter to enable localhost communication and setup the hosts file in system 32. When I start lsnrcrl

However when I try to connect I get an error.

<msg time='2016-07-27T08:59:00.083+02:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='ABCDEF121221'
 host_addr='192.168.1.168'>

<txt>27-JUL-2016 08:59:00 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=SQLDeveloper)(HOST=__jdbc__)(USER=ABC))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=50619)) * establish * xe * 12505
 </txt>

Extra info

Listener.ora is set to localhost.
hostsfile in system32 is set to 192.168.1.1/domain name.ABCDEF1212221/ ABCDEF1212221

The lstnrctrl status is here.

C:Windowssystem32> lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 27-JUL-2016  13:19:16

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

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 -       Production
Start Date                26-JUL-2016 15:30:00
Uptime                    0 days 21 hr. 49 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File      C:oraclexeapporacleproduct11.2.0servernetworkadminlistener.ora
Listener Log File         C:oraclexeapporaclediagtnslsnrOLDKHQ133304listeneralertlog.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)  (HOST=abcdefg1212221.corporate.corporate.net)(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...
The command completed successfully

connectivity listener oracle

Balazs Papp · Answer

The last number of the log entry is the return code of the operation. On a successful connection, it is 0. In your case, it is 12505.

12505, 00000, "TNS:listener does not currently know of SID given in connect descriptor"
// *Cause:  The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a SID for an instance (usually a database instance) that either
// has not yet dynamically registered with the listener or has not been
// statically configured for the listener. This may be a temporary condition
// such as after the listener has started, but before the database instance
// has registered with the listener.
// *Action:
//  - Wait a moment and try to connect a second time.
//  - Check which instances are currently known by the listener by executing:
//    lsnrctl services <listener name>
//  - Check that the SID parameter in the connect descriptor specifies
//    an instance known by the listener.
//  - Check for an event in the listener.log file.

This simply means, you provided an incorrect SID.

xe is the default SID in SQL Developer.

Check the correct SID from the output of lsnrctl status, and use that for connecting.

Or even better, let's just finally forget the SID, and use SERVICE_NAME.

Another possibility is that your database instance was not started, or you do not even have a database created yet.

connecting to an oracle sql database from sql developer on the same computer. When the computer is a domain computer

One Answer

Add your own answers!

Ask a Question