In database development and management, the core programming language of Oracle database is PL/SQL, which is mainly applied in stored procedures, functions and data operations. Many developers are attempting to connect to remote server databases using PL/SQL tools, but connection failures occur due to configuration errors or environmental issues. How can this be resolved?

1、 Core Conditions for Connecting to a remote Database

Before starting the configuration, it is necessary to ensure that the basic conditions of remote database information are met: database IP address or hostname, listening port (default 1521), Service Name (Service Name) or SID, valid username and password.

The local environment requires the installation of the Oracle Client (Instant Client or the full version), PL/SQL Developer or Oracle SQL Developer tools. Network connectivity can be tested via telnet or ping. Remote database users need to have the roles of CONNECT and RESOURCE. The server firewall opens port 1521 (or a custom port).

2、Configure the connection between the Oracle client and TNS

For lightweight requirements, it is recommended to use Oracle Instant Client. Download the corresponding version (such as the 64-bit Windows version) from the Oracle official website and extract it to the local directory (such as C:\Oracle\instantclient_19_21). Add a new system variable TNS_ADMIN, pointing to the client directory (such as C:\Oracle\instantclient_19_21), and add the client Path (such as C:\Oracle\instantclient_19_21) in the PATH variable.

Create or modify the tnsnames.ora file under the TNS_ADMIN directory and define the remote database connection descriptor:

REMOTE_DB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)

REMOTE_DB: Custom connection alias

HOST: Remote database IP

SERVICE_NAME: Database service name (can be queried through the lsnrctl status command of the remote server)

Configure the PL/SQL Developer connection. Open PL/SQL Developer, enter Tools → Preferences → Connection, and set Oracle Home as the Instant Client directory (such as C:\Oracle\instantclient_19_21). Set the OCI Library to OCi.dll again (the path is like C:\Oracle\instantclient_19_21\oci.dll).

Return to the main interface, enter the username and password, and select the Database as REMOTE_DB (that is, the alias defined in tnsnames.ora).

3、Network Connectivity Testing and Debugging

Verify the TNS configuration using TNSPING. Execute the following command in the command line to confirm whether the TNS resolution is normal:

tnsping REMOTE_DB

If OK (xx msec) is returned, it indicates that the configuration is correct. If TNS03505: Failed to resolve name is reported, the file format or path of tnsnames.ora needs to be checked.

Use TELNET to test the port connectivity. If the Telnet test is successful but the connection times out, the network port may be blocked.

telnet 192.168.1.100 1521

If the window is closed or a connection failure prompt appears, it is necessary to check whether the firewall of the remote server allows port 1521 and whether the database listener is started (remotely execute lsnrctl status).

Remote view the listener log on the server (the default path $ORACLE_HOME/network/log/listener log), to confirm whether the connection request to:

tail f $ORACLE_HOME/network/log/listener.log

If there is no access record of the corresponding IP in the log, the local network policy or routing configuration needs to be checked.

4、Common Mistakes and Solutions

Question 1: ORA12170: TNS connection timeout. This is because the client cannot reach the server port. In this situation, it is necessary to confirm that the server IP and port are correct, turn off the local firewall or anti-virus software (such as Windows Defender), and forward the port through the SSH tunnel (applicable to cloud servers) :

ssh L 1521:localhost:1521 user@remote_host

At this point, the client needs to connect to localhost:1521.

Question 2 ORA12541: The TNS has no monitoring program. Because the database listener is not started or configured incorrectly. It is necessary to execute "lsnrctl start" from the remote server to start the listening. Check whether the HOST in the listener.ora file is 0.0.0.0 or the actual IP.

Question 3 ORA01017: The username/password is invalid. It is because of incorrect authentication information or insufficient user permissions. Direct login verification using SQLPlus is required:

sqlplus username/password@REMOTE_DB

Contact the DBA to reset your password or grant permissions:

sql
ALTER USER username IDENTIFIED BY new_password;
GRANT CONNECT, RESOURCE TO username;

5、Security Enhancements and Advanced Configurations

Use SSL encrypted connection to configure SSL through Oracle Wallet to avoid plaintext data transmission. Generate the wallet and create the certificate:

orapki wallet create wallet /wallet_dir pwd wallet_password
orapki wallet add wallet /wallet_dir dn "CN=client" keysize 1024 pwd wallet_password

Modify sqlnet.ora to enable encryption:

SQLNET.AUTHENTICATION_SERVICES = (TCPS)
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /wallet_dir)))

Connection Pool and performance optimization enable the connection pool in PL/SQL Developer to reduce the overhead of frequent connection establishment: Enter Tools → Preferences → Oracle → Connection, and set the Pool Size to 510. Adjust the SQLNET timeout parameter (sqlnet.inbound_connect_timeout) to avoid long-term blocking.

In cross-version compatibility processing, if the client and server versions do not match (for example, the client 19c connects to the 11g database), the compatibility protocol needs to be specified in tnsnames.ora:

REMOTE_DB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
(VERSION = 11)
)

The above is the entire process of mastering PL/SQL to connect to remote databases. Whether it is a local development environment or a cloud host, the most crucial points are accurately configuring TNS, ensuring network availability, and reasonably responding to version differences or security requirements.