Error
Error Code: ORA-28547

Oracle ORA-28547: Connection Failure

📦 Oracle Database
📋

Description

The ORA-28547 error indicates a failure during the network connection initialization between a client and the Oracle server. This typically arises from misconfiguration of Oracle Net Services, incompatible network protocols, or character set issues.
💬

Error Message

ORA-28547: connection to server failed, probable Oracle Net admin error
🔍

Known Causes

4 known causes
⚠️
Incorrect Connect String
The connection string is referencing a Heterogeneous Services agent instead of the intended Oracle server. Ensure the connect string points to the correct database instance.
⚠️
HS= Specification in Connect String
The connection string includes an (HS=) specification, which is intended for Heterogeneous Services and not direct Oracle server connections. Remove the (HS=) specification.
⚠️
Character Set Mismatch
The client process does not recognize the database character set due to an incorrect or unnecessary ORA_NLS10 environment variable setting. Verify the character set configuration on both the client and server.
⚠️
Connection Timeout
A connection timeout occurs due to the SQLNET.INBOUND_CONNECT_TIMEOUT parameter when all shared servers are busy in a shared server configuration. Increase the timeout value or investigate shared server capacity.
🛠️

Solutions

4 solutions available

1. Verify TNSNAMES.ORA Configuration easy

Ensures the client's TNSNAMES.ORA file correctly defines the database connection details.

1
Locate the `tnsnames.ora` file on the client machine attempting to connect. This is typically found in `$ORACLE_HOME/network/admin` or a directory specified by the `TNS_ADMIN` environment variable.
2
Open the `tnsnames.ora` file in a text editor.
3
Verify the service name or SID, host, and port for the target database. Ensure they exactly match the details provided by your DBA or database documentation.
YOUR_DB_ALIAS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = your_database_host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = your_database_service_name)
    )
  )
4
Save the changes and attempt to connect again.

2. Test Network Connectivity to the Listener easy

Confirms that the client can reach the Oracle Net listener process on the database server.

1
On the client machine, open a command prompt or terminal.
2
Use the `tnsping` utility to test connectivity to the database service name defined in your `tnsnames.ora` file.
tnsping YOUR_DB_ALIAS
3
Analyze the output. A successful `tnsping` will show 'OK' and indicate the time taken to reach the listener. If `tnsping` fails, it points to a network issue or an inaccessible listener.
4
If `tnsping` fails, try pinging the database host directly to rule out general network problems.
ping your_database_host

3. Check Oracle Net Listener Status and Configuration on Server medium

Verifies that the Oracle Net listener is running on the database server and is configured to accept connections for the intended service.

1
Log in to the database server as a user with access to Oracle binaries (e.g., the Oracle software owner).
2
Set the `ORACLE_HOME` environment variable to the Oracle installation directory.
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
3
Navigate to the `$ORACLE_HOME/bin` directory.
cd $ORACLE_HOME/bin
4
Check the status of the Oracle Net listener.
./lsnrctl status
5
If the listener is not running, start it.
./lsnrctl start
6
Examine the `listener.ora` file (usually located in `$ORACLE_HOME/network/admin` or specified by `TNS_ADMIN` on the server). Ensure it contains an entry for the correct host, port, and the `SID_LIST_LISTENER` or `SERVICES_LIST` correctly registers the database service name or SID.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = your_database_host)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (GLOBAL_DBNAME = your_database_service_name)
    )
  )
7
If `listener.ora` was modified, reload the listener configuration.
./lsnrctl reload

4. Review Firewall Rules medium

Ensures that no network firewalls are blocking the connection attempt on the specified port.

1
Identify the port used by the Oracle Net listener (default is 1521).
2
Check firewall configurations on the database server, the client machine, and any intermediate network devices (routers, firewalls) between them.
3
Ensure that inbound connections to the database server on the Oracle listener port are permitted from the client's IP address or subnet.
4
If you have access to manage firewalls, temporarily disable them (in a controlled environment) to confirm if they are the cause of the ORA-28547 error. Remember to re-enable them afterward.