Resolving ORA-12560 – TNS:protocol adapter error

Introduction
Understanding the Cause
Step-by-Step Solutions
Examples
Conclusion

Introduction

The ORA-12560: TNS:protocol adapter error is a common error encountered in Oracle databases, particularly during database startup or when attempting to connect to a database. This error indicates an issue with the network protocol stack that Oracle uses to communicate with the database. Resolving this error is crucial for ensuring smooth database operations.

Understanding the Cause

The ORA-12560 error can occur due to several reasons, including:

• Oracle Services Not Started: The most common cause is that the Oracle database services are not running.
• Incorrect Environment Variables: Incorrectly set ORACLE_HOME or ORACLE_SID environment variables can lead to this error.
• Network Issues: Problems with the network configuration or firewall settings can cause this error.
• Installation Issues: Issues during the installation of Oracle software can lead to this error.
• User Permissions: The user may not have the necessary permissions to access the Oracle database.

Step-by-Step Solutions

To resolve ORA-12560, follow these troubleshooting steps:

1. Check Oracle Services

Ensure that the necessary Oracle services are running. On Windows, check the services in the Services console:

# Open Services console and check for Oracle services
services.msc
        

On Linux, check the status of the Oracle services:

# Check Oracle services on Linux
$ ps -ef | grep pmon
        

2. Set Correct Environment Variables

Verify that the ORACLE_HOME and ORACLE_SID environment variables are set correctly. On Windows, you can set these variables in the System Properties:

# Set environment variables on Windows
set ORACLE_HOME=C:\oracle\product\12.2.0\dbhome_1
set ORACLE_SID=ORCL
        

On Linux, set these variables in the terminal:

# Set environment variables on Linux
$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
$ export ORACLE_SID=ORCL
        

3. Check Network Configuration

Ensure that the network configuration is correct and that there are no firewall issues blocking the connection. Verify the listener.ora and tnsnames.ora files:

# Check listener.ora and tnsnames.ora for correct configuration
$ cat $ORACLE_HOME/network/admin/listener.ora
$ cat $ORACLE_HOME/network/admin/tnsnames.ora
        

Also, verify that the Oracle Listener is running:

# Start the Oracle Listener
$ lsnrctl start
        

4. Check User Permissions

Ensure that the user has the necessary permissions to access the Oracle database. On Windows, check the user's group membership. On Linux, verify the user's permissions:

# Check user permissions on Linux
$ id oracle
        

5. Reinstall Oracle Software

If the error persists, it might be due to issues during the installation of Oracle software. Reinstalling Oracle might resolve the issue.

# Uninstall and reinstall Oracle software
        

Examples

Example 1: Starting Oracle Services

If you receive the ORA-12560 error, first check if the Oracle services are running. On Windows, you can start the services using the command prompt:

# Start Oracle services on Windows
net start OracleServiceORCL
        

On Linux, use the following command:

# Start Oracle services on Linux
$ sqlplus / as sysdba
SQL> STARTUP
        

Example 2: Setting Environment Variables

Ensure that the ORACLE_HOME and ORACLE_SID environment variables are correctly set. On Windows, use the command prompt:

# Set environment variables on Windows
set ORACLE_HOME=C:\oracle\product\12.2.0\dbhome_1
set ORACLE_SID=ORCL
        

On Linux, use the terminal:

# Set environment variables on Linux
$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
$ export ORACLE_SID=ORCL
        

Example 3: Verifying Network Configuration

Check the listener.ora and tnsnames.ora files to ensure they are correctly configured. Sample entries are:

# listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = your_host)(PORT = 1521))
    )
  )

# tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = your_host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )
        

Example 4: Starting Oracle Listener

If the Oracle Listener is not running, you can start it using the following command:

# Start Oracle Listener
$ lsnrctl start
        

Conclusion

The ORA-12560: TNS:protocol adapter error can be a frustrating issue to encounter, but it is typically resolved by ensuring that the Oracle services are running, the environment variables are set correctly, the network configuration is correct, and the user has the necessary permissions. By following the steps outlined in this guide, you should be able to effectively troubleshoot and resolve this error, ensuring smooth database operations.

Related content

• ORA-12154: TNS: Could Not Resolve the Connect Identifier Specified
• Resolving ORA-12505: TNS Listener Does Not Currently Know of SID
• Resolving ORA-12519: TNS:no appropriate service handler found
• Resolving ORA-12537: TNS: Connection Closed Error
• Resolving ORA-12560 – TNS:protocol adapter error

Rate Your Experience

: 89 : 1