0

I am new to Oracle SQL, but I want to give it a shot and play around with the HR schema. I don't want to use a VM, because I had a lot of issues with them.

Setup: Oracle Server setup on WIN64 PC. HR and SYS works fine. (10.0.0.10 --> to understand later on)

Trying to connect via MacOS and SQL Developer (10.0.0.23).

On my Mac I am able to ping 10.0.0.10 and the other way around (10.0.0.10 (PC) to 10.0.0.23 (MAC)).

Ping from Mac to Pc

So then I setup my listener (listener.ora) and my tnsnames.ora in the way oracle wants me to do (examples folder).

Listener Setup tnsnames setup

After this setup I rebooted the server. Now I tried to connect as HR to my 10.0.0.10 machine. (On my win pc I can connect and use HR and sys schema.) connectionHR

After trying to connect, a "Testing connection" window opens and closes after 1 minute.

Status: No success: I/O-Error: The Network Adapter could not establish the connection

Ive also tried to import the tnsnames.ora file and start from this file, but this wouldn't work either.

EDIT: I updated my listener.

Anmeldung bei (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS des LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Startdatum                27-DEZ-2020 00:11:42
Uptime                    0 Tage 0 Std. 3 Min. 14 Sek.
Traceebene               off
Sicherheit                ON: Local OS Authentication
SNMP                      OFF
Parameterdatei des Listener C:\Users\Ben\Desktop\db_home\network\admin\listener.ora
Logdatei des Listener    D:\benOracle\diag\tnslsnr\DESKTOP-FT8E4S1\listener\alert\log.xml
Zusammenfassung Listening-Endpunkte...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DESKTOP-FT8E4S1)(PORT=5500))(Security=(my_wallet_directory=D:\BENORACLE\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services ▄bersicht...
Dienst "52448234712340b69f274bcc790ecfe0" hat 1 Instanzen.
  Instanz "orcl", Status READY, hat 1 Handler f³r diesen Dienst...
Dienst "CLRExtProc" hat 1 Instanzen.
  Instanz "CLRExtProc", Status UNKNOWN, hat 1 Handler f³r diesen Dienst...
Dienst "cfc353defce0494a8a440e7d1f817c2d" hat 1 Instanzen.
  Instanz "orcl", Status READY, hat 1 Handler f³r diesen Dienst...
Dienst "orcl" hat 1 Instanzen.
  Instanz "orcl", Status READY, hat 1 Handler f³r diesen Dienst...
Dienst "orclXDB" hat 1 Instanzen.
  Instanz "orcl", Status READY, hat 1 Handler f³r diesen Dienst...
Dienst "orclpdb" hat 1 Instanzen.
  Instanz "orcl", Status READY, hat 1 Handler f³r diesen Dienst...
Der Befehl wurde erfolgreich ausgef³hrt.

Host= Localhost Why is host still the local ? --> This is in German. Comment if translation is needed.

C:> netstat -a -b --> results 2 listeners on the same port

All connections on the port 1521:

Improve this question
10
  • I edited it, but it still throws an error. Listener refused the connection with the following error: ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
    – ben.reisinger
    Commented Dec 26, 2020 at 22:57
  • On your DB server: lsnrctl status. Does it list your instance? You may need to connect locally and make sure you haven’t set local_listener to only register with a specific listener, but most likely you need to include a domain.
    – Andrew Sayer
    Commented Dec 26, 2020 at 23:04
  • Normally, when I run this comman i get 8 Instances or more
    – ben.reisinger
    Commented Dec 26, 2020 at 23:07
  • I updated the question with the lsnrctl status output.
    – ben.reisinger
    Commented Dec 26, 2020 at 23:21
  • Stop the listenermac listener: lsnrctl stop listenermac. Remove it from the listener.ora and restart the main listener again.
    – Andrew Sayer
    Commented Dec 26, 2020 at 23:54

1 Answer 1

Reset to default
1

Your listener.Ora file is set to listen on localhost rather than your 10.0.0.10 ip. Change that, then restart the listener and make sure your instance is getting registered with it. Note that your tnsnames.Ora Isn’t involved here as you’re specifying everything in your sql developer connection, but you also need to correct the host there.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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