2

I am setting up Oracle in virtual box and ran into this error: TNS:Listener does not currently know of SID given in connect descriptor.

Here are the steps I took to download oracle on virtual box:

  1. Downloaded VirtualBox 5.2.0.
  2. Got the Oracle DB Developer VM file (DeveloperDaysVM2017-06-13_01.ova).
  3. Opened the file inside virtual box and opened it from virtual box.
  4. Now when I go to Sql Developer on my Mac machine and try to connect to oracle in Virtual Box I got that error above. I included a screenshot below.

enter image description here

For some reason when I do: telnet 127.0.0.1 1521 it seems to work. But when I do it through Sql Developer I am getting the error in red in the pic above. Can someone give me some pointers?

My tnsnames.ora file:

ORCL12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl12c)
    )
  )

LISTENER_ORCL12C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

listeners.ora file:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl12c)
      (SID_NAME = orcl12c)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )
  )

#HOSTNAME by pluggable not working rstriction or configuration error.
DEFAULT_SERVICE_LISTENER = (orcl12c)
  • You should check your listener.ora and tnsnames.ora . Be sure that sid name is well defined. – OscarAkaElvis Nov 09 '17 at 23:10
  • @Oscar I ran `lsnrctl status` and the status was fine and I have updated my answer above to include the `tnsnames.ora`. Can you please take a look? –  Nov 09 '17 at 23:14
  • 1
    on your connect descriptor you are setting "xe" (as we can see in screenshot" and you should put "orcl12c" – OscarAkaElvis Nov 09 '17 at 23:21
  • A very similar question has been answered here: https://stackoverflow.com/questions/5661610/tns-12505-tnslistener-does-not-currently-know-of-sid-given-in-connect-descript – Paul Podgorsek Nov 09 '17 at 23:22

2 Answers2

1

You should check your listener.ora and tnsnames.ora . Be sure that sid name is well defined.

On your connect descriptor you are setting "xe" (as we can see in screenshot" and you should put "orcl12c".

For the user and password try:

sys/manager

system/manager

OscarAkaElvis
  • 5,384
  • 4
  • 27
  • 51
  • Even with those two username/password combinations I am getting an error saying invalid password/username :( –  Nov 09 '17 at 23:30
  • That are the usual oracle passwords for standard oracle databases. Where did you downloaded your database? you should check there I think. – OscarAkaElvis Nov 09 '17 at 23:32
  • I got it from here: http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html and it says towards the bottom the username/password is oracle/oracle. But even that doesn't work... –  Nov 09 '17 at 23:34
  • I added a pic to my question above if you can take a look at it. I will remove it after as it is not part of the question but it's just easier to show. –  Nov 09 '17 at 23:35
  • 1
    It's suppossed that all accounts on that vm have set password to "oracle". Did you tested sys/oracle or system/oracle ? – OscarAkaElvis Nov 09 '17 at 23:38
  • YES that worked! Finally lol... I changed the role to SYSDBA and used the password as sys/oracle. Thank you so much for all the help! –  Nov 09 '17 at 23:40
  • Which was the password finally worked? can you set the helpful flag on the comment to see what worked for you? – OscarAkaElvis Nov 09 '17 at 23:41
  • sys/oracle worked. I also had to set the Role to SYSDBA (in the screenshot above). –  Nov 09 '17 at 23:45
0

Here is what was the issue in my case: (some differences though: I use service orcl for connection and Oracle Virtual Lab appliance for the database link: https://www.oracle.com/database/technologies/databaseappdev-vm.html)

root cause

There are usually two TNS listener involved:

TNS listener on your local PC coming from installed Oracle Client (localhost:1521)
TNS listener in Virtual machine coming from Virtual Lab Oracle DB (localhost:1521)

And this is the issue. Naturally an SQL connection would be linked to the local TNS listener and not to the Virtual Box.

solution

use different port (1522) in connection and set port forwarding on NAT network adapter for Virtual Machine (1522 → 1521)

I noticed when I could connect fine from sql developer which is integrated in VirtualBox image and I could also see a TNS listener in the services of my Windows laptop. Also SSH connection was working fine (port forwarding 2222 -> 22).

enter image description here

Robi Wan Kenobi
  • 1,441
  • 1
  • 10
  • 10