0

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

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

The lstnrctrl status is here.

C:\Windows\system32> 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:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\OLDKHQ133304\listener\alert\log.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
  • What have you got in your HOSTS file, exactly? The above looks like nonsense. – Philᵀᴹ Jul 27 '16 at 7:51
  • I dont want to put in my works domain name or my computer name so domain name is just representing the correct domain name that I got from ipconfig and the computer name is the abcef1212221 – DavyGravy Jul 27 '16 at 11:39
0

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.

  • I have updated the question with the lsnrctl status. I see that the default servive is the XE which I thouhgt was correct. I also tried with the clrextproc and the Plsectproc but they didnt work. – DavyGravy Jul 27 '16 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.