The ORA-12504 error is a common issue faced by Oracle Database users. This error occurs when the Service_Name parameter in the Connect_Data portion of the TNSNAMES.ORA file doesn't match any service names known to the listener. This guide will provide a step-by-step solution to resolve this issue and help you get your Oracle Database up and running without any TNS Listener issues.
Table of Contents
- Prerequisites
- Step 1: Verify the TNSNAMES.ORA File
- Step 2: Check the Listener.ora File
- Step 3: Verify Oracle Services
- Step 4: Test the Connection
- FAQ
- Related Links
Prerequisites
Before you begin, ensure you have the following:
- Access to the Oracle Database Server
- Oracle Client installed on your machine
- Administrative privileges for the Oracle Database and services
Step 1: Verify the TNSNAMES.ORA File
The first step in resolving the ORA-12504 error is to verify the contents of the TNSNAMES.ORA file. This file is typically located in the %ORACLE_HOME%\network\admin directory.
- Open the TNSNAMES.ORA file in a text editor.
- Locate the alias entry that you are trying to connect to.
- Verify that the
SERVICE_NAMEparameter in theCONNECT_DATAsection matches the service name known to the listener.
Example:
MYDB_ALIAS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mydbhost.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydbservice)
)
)
In this example, the SERVICE_NAME parameter is set to mydbservice. Make a note of this value, as you will need it in the next steps.
Step 2: Check the Listener.ora File
The Listener.ora file contains the configuration details for the Oracle Listener. Verify that the SERVICE_NAME parameter matches the value in the TNSNAMES.ORA file. This file is also located in the %ORACLE_HOME%\network\admin directory.
- Open the Listener.ora file in a text editor.
- Locate the
SID_LIST_LISTENERsection. - Verify that the
SERVICE_NAMEparameter matches the value from the TNSNAMES.ORA file.
Example:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mydbservice)
(ORACLE_HOME = D:\Oracle\product\11.2.0\dbhome_1)
(SID_NAME = mydbservice)
)
)
In this example, the GLOBAL_DBNAME and SID_NAME parameters are both set to mydbservice, which matches the value from the TNSNAMES.ORA file.
Step 3: Verify Oracle Services
Ensure that the Oracle services are running on the server.
- Open the Services console (
services.msc) on the server. - Locate the
OracleService<SID>andOracleOraDb11g_home1TNSListenerservices. - Ensure that both services are running. If they are not, start them.
Step 4: Test the Connection
After verifying the configuration files and ensuring that the services are running, test the connection using SQL*Plus or another Oracle client tool.
- Open SQL*Plus.
- Enter the following command to test the connection:
CONNECT username/password@MYDB_ALIAS
Replace username, password, and MYDB_ALIAS with your actual database credentials and alias.
If the connection is successful, the ORA-12504 error should be resolved. If the issue persists, review the configuration files and services once more.
FAQ
1. What is the ORA-12504 error?
The ORA-12504 error occurs when the SERVICE_NAME parameter in the CONNECT_DATA section of the TNSNAMES.ORA file does not match any service names known to the listener.
2. Where can I find the TNSNAMES.ORA file?
The TNSNAMES.ORA file is typically located in the %ORACLE_HOME%\network\admin directory.
3. Where can I find the Listener.ora file?
The Listener.ora file is also located in the %ORACLE_HOME%\network\admin directory.
4. How can I test the connection after fixing the ORA-12504 error?
You can test the connection using SQL*Plus or another Oracle client tool by entering the CONNECT username/password@MYDB_ALIAS command.
5. What should I do if the ORA-12504 error persists?
If the error persists after following this guide, review the TNSNAMES.ORA and Listener.ora files for any discrepancies and ensure that the Oracle services are running on the server.