How to fix “ORA-12514 TNS Listener Does Not Currently Know of Service Requested”. Free immediate support

How to Fix ORA-12514: TNS Listener Does Not Recognize the Requested Service

ORA-12514 is a common Oracle database error that occurs when the TNS Listener doesn't recognize the service requested during a connection attempt. This guide walks you through verifying service names, checking configuration files like TNSNAMES.ORA and LISTENER.ORA, restarting the listener, and troubleshooting connection issues with tools like TNSPing. Follow these clear steps to resolve the error and ensure reliable database connectivity.

Use our free chatbot to get help with technical issues quickly and effectively.

Key Takeaways

What is ORA-12514? ORA-12514 is an Oracle database error indicating that the TNS Listener does not currently recognize the service requested in a connection attempt.
Primary Cause: The listener isn’t configured to recognize the service or database instance.
Solution Approach: Fix involves validating configuration settings in TNSNAMES.ORA and LISTENER.ORA, restarting the Oracle listener, and confirming that database services are properly started.

Step-by-Step Guide to Resolve ORA-12514 Error

Fixing the ORA-12514: TNS Listener Does Not Currently Know of Service Requested issue involves careful configuration checking and troubleshooting. Below is a detailed step-by-step guide.

Step 1: Verify Service Name or SID

Identify the Service Name or SID:
Confirm your database’s SERVICE_NAME or SID by logging in to the database:
```
sqlplus / as sysdba
SHOW PARAMETER SERVICE_NAMES;
```
Note: The SERVICE_NAME in your connection string must match this exactly.
Check Connection String:
Double-check the connection string in your client application or tool:
```
db_host = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.20.117)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"
```
Key Tip: Service names are case-sensitive! For example, orcl ≠ ORCL.

Step 2: Check Listener Status

Use lsnrctl to Inspect Listener Status:
Run the following command to check if the listener recognizes the database service:
```
lsnrctl status
```
Look for output similar to this:
```
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.20.117)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))
```
Tip: If no services are listed or the expected service name is missing, the listener must be reconfigured.
Restart the Listener:
```
lsnrctl stop
lsnrctl start
```

Step 3: Validate Configuration Files

Check `TNSNAMES.ORA` File:

The TNSNAMES.ORA file should correctly define your database’s connection descriptor:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

Locate the file (commonly in $ORACLE_HOME/network/admin on Linux/Unix or C:\Oracle\network\admin on Windows).
Ensure the SERVICE_NAME entry matches the database’s service shown in Step 1.

Check `LISTENER.ORA` File:

This file needs to define a listener and its supported SID_LIST.

Example Configuration:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_home)
      (PROGRAM = extproc)
    )
  )
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  )

Update the SID_DESC to match your database’s SID.
Restart the listener after making any changes.

Example Command:

lsnrctl reload

Step 4: Verify Database Instance Status

The database instance must be up and registered with the listener:

Check Database Status:

SELECT name, open_mode FROM v$database;

ALTER SYSTEM REGISTER;

Step 5: Troubleshoot Connection Issues with `TNSPing`

Run TNSPing Diagnostic Tool:
Test connectivity between your client and the database:
```
tnsping orcl
```
- A successful test should display the resolved host and port.
- If it fails, verify that the network between the client and listener is functional.
Enable Listener Tracing (Optional):
Enable detailed tracing to debug:
```
lsnrctl set log_level TRACE
```

Step 6: Update System Environment Variables

In some cases, adding missing environment variables may solve the issue:

Check ORACLE_SID:
Ensure ORACLE_SID is set:
```
export ORACLE_SID=orcl
```

Validate ORACLE_HOME:

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_home

Recommended Tools for Data Backup and Recovery

Before making significant changes, it’s wise to back up your data. Here are tools to help:

EaseUS Todo Backup: Protects database files and settings.
MiniTool ShadowMaker: Reliable for SQL backups.
EaseUS MS SQL Recovery: SQL-specific recovery solutions.

Frequently Asked Questions (FAQs)

What causes ORA-12514?

This error occurs when the Oracle listener doesn’t recognize the database service due to an incorrect SERVICE_NAME, an unregistered database, or a misconfigured listener.

How do I find my database’s `SERVICE_NAME`?

Run the following SQL:

SHOW PARAMETER SERVICE_NAMES;

Can I prevent ORA-12514 from reoccurring?

Automate database registration by adding SERVICE_NAMES to the INIT.ORA or SPFILE.
Use robust software like EaseUS Todo Backup to manage configurations regularly.

Why does restarting the listener fix the issue?

Restarting forces the listener to re-read its configuration and register active database services.

This expert guide is designed to comprehensively resolve ORA-12514 errors quickly and efficiently. Reviewing your configurations closely and following these troubleshooting steps will ensure a smooth Oracle database connection!

Post Views: 85