Forum Stats

  • 3,840,393 Users
  • 2,262,599 Discussions
  • 7,901,261 Comments

Discussions

How to have SqlDeveloper on a client pc access a 21C Server

PhilMan2
PhilMan2 Member Posts: 380 Bronze Badge
edited Jan 17, 2022 9:23PM in Database Upgrade

I had a server using 18C on Windows 10. I was able to access the database using SQL Developer 19.1 from a separate Windows 10 client. This all worked fine.

I wanted to upgrade the server hardware and software. On new hardware that came with Windows 11, I loaded 21C (XE) and also loaded APEX 21.2, ORDS 21.4 and SqlDeveloper 21.4. After I restored the data and set everything up I can access the apps on the new 21C server through a browser.

The problem is with SqlDeveloper. When I log into the server using TeamViewer and run SQL Developer on the server itself, it works as expected.

However, when I run SqlDeveloper from my Windows 10 PC, it gives an error connecting to the server. "IO Error: The Network Adapter could not establish the connection. Vendor code 17002".

The new server was named the same as the old, and the old is offline, so I expected no problems. When SqlDeveloper couldn't connect, I looked at one post on this forum https://community.oracle.com/tech/developers/discussion/comment/16815236#Comment_16815236

and noticed a few things in the listener.ora file that needed to change for 21C. The windows paths are different, plus I had to change the DLL in the ENVS from oraclr18.dll to oraclr.dll.

On the server, when I type hostname from a DOS prompt, I receive the response ParishServer1 as expected.

Here's a copy of the server's edited listener.ora file:

# listener.ora Network Configuration File: C:\app\product\21c\dbhomeXE\network\admin\listener.ora
# Generated by Oracle configuration tools.
DEFAULT_SERVICE_LISTENER = XE
SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = CLRExtProc)
     (ORACLE_HOME = C:\app\product\21c\dbhomeXE)
     (PROGRAM = extproc)
     (ENVS = "EXTPROC_DLLS=ONLY:C:\app\product\21c\dbhomeXE\bin\oraclr.dll")
   )
 )

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = 
       (PROTOCOL = TCP)
       (HOST = ParishServer1)
       (PORT = 1521)
     )
     (ADDRESS = 
      (PROTOCOL = IPC)
      (KEY = EXTPROC1521)
     )
   )
 )

Here's a copy of the edited tnsnames.ora file:

# tnsnames.ora Network Configuration File: C:\app\product\21c\dbhomeXE\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ParishServer1 =
 (DESCRIPTION =
   (ADDRESS = 
    (PROTOCOL = TCP)
    (HOST = ParishServer1)
    (PORT = 1521)
   )
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = xepdb1)
   )
 )
XE =
 (DESCRIPTION =
   (ADDRESS = 
    (PROTOCOL = TCP)
    (HOST = localhost)
    (PORT = 1521)
   )
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = XE)
   )
 )
XEPDB1 =
 (DESCRIPTION =
   (ADDRESS = 
    (PROTOCOL = TCP)
    (HOST = localhost)
    (PORT = 1521)
   )
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = XEPDB1)
   )
 ) 
LISTENER_XE =
 (ADDRESS = 
   (PROTOCOL = TCP)
   (HOST = localhost)
   (PORT = 1521)
 )
ORACLR_CONNECTION_DATA =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = 
      (PROTOCOL = IPC)
      (KEY = EXTPROC1521)
    )
   )
   (CONNECT_DATA =
     (SID = CLRExtProc)
     (PRESENTATION = RO)
   )
 )

The Service Name for the SqlDeveloper Database Connection on the Windows 10 client PC is set to XEPDB1. Obviously, I can ping from the client to the server and can browse from the client to the server. It's just SqlDeveloper that is giving me a problem with this release. Is there anything special I have to do to the Server to allow SqlDeveloper from my client that I'm not already doing?

Best Answer

Answers