Ora 12504 ошибка

I downloaded SQLPLUS from Oracle:

http://www.oracle.com/technetwork/topics/winx64soft-089540.html

Basic Lite and SQL*Plus

I then fired up SQL*Plus:

c:Program FilesOracleinstantclient_12_1>sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 15 15:25:36 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

and tried to connect to a database:

connect userpassword@hostname

and received the error message:

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

What am I missing?

I ran the queries suggested by Jakub, I got

SQL> select sys_context('USERENV','SERVICE_NAME') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------

SYS$USERS

SQL> select sys_context('USERENV','SID') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------

877

SQL>

Moshe Katz

15.8k7 gold badges68 silver badges116 bronze badges

asked Apr 15, 2015 at 19:36

You’re missing service name:

 SQL> connect username/password@hostname:port/SERVICENAME

EDIT

If you can connect to the database from other computer try running there:

select sys_context('USERENV','SERVICE_NAME') from dual

and

select sys_context('USERENV','SID') from dual

answered Apr 15, 2015 at 20:07

jakub.petrjakub.petr

2,9412 gold badges23 silver badges33 bronze badges

I ran into the exact same problem under identical circumstances. I don’t have the tnsnames.ora file, and I wanted to use SQL*Plus with Easy Connection Identifier format in command line. I solved this problem as follows.

The SQL*Plus® User’s Guide and Reference gives an example:

sqlplus hr@»sales-server:1521/sales.us.acme.com»

Pay attention to two important points:

The connection identifier is quoted. You have two options:
1. You can use SQL*Plus CONNECT command and simply pass quoted string.
2. If you want to specify connection parameters on the command line then you must add backslashes as shields before quotes. It instructs the bash to pass quotes into SQL*Plus.
The service name must be specified in FQDN-form as it configured by your DBA.

I found these good questions to detect service name via existing connection: 1, 2. Try this query for example:

SELECT value FROM V$SYSTEM_PARAMETER WHERE UPPER(name) = 'SERVICE_NAMES'

answered Oct 19, 2016 at 17:28

alexeioninalexeionin

3803 silver badges7 bronze badges

Just a small observation: you keep mentioning conn usrpass, and this is a typo, right? Cos it should be conn usr/pass. Or is it different on a Unix based OS?

Furthermore, just to be sure: if you use tnsnames, your login string will look different from when you use the login method you started this topic out with.

tnsnames.ora should be in $ORACLE_HOME$networkadmin. That is the Oracle home on the machine from which you are trying to connect, so in your case your PC. If you have multiple oracle_homes and wish to use only one tnsnames.ora, you can set environment variable tns_admin (e.g. set TNS_ADMIN=c:oracletns), and place tnsnames.ora in that directory.

Your original method of logging on (usr/pwd@10.15.240.13:port/servicename) should always work. So far I think you have all the info, except for the port number, which I am sure your DBA will be able to give you. If this method still doesn’t work, either the server’s IP address is not available from your client, or it is a firewall issue (blocking a certain port), or something else not (directly) related to Oracle or SQL*Plus.

hth!
Regards,
Remco

answered Jun 30, 2015 at 15:08

I faced such strange behavior only in one server and only from script
it worked when I put env variable TNS_ADMIN
despite it was default location network/admin

answered Jul 11, 2021 at 12:33

BenBen

1882 silver badges15 bronze badges

The following worked for me:

/usr/lib/oracle/21/client64/bin/impdp username/password@aws-rds-hostname:1521/DATABASENAME DIRECTORY=DATA_PUMP_DIR dumpfile=filename.dmp logfile=filename.log full=y

answered May 12, 2022 at 11:19

Источник

This error occurs when there was no default service configured for the listener and SERVICE_NAME was missing from the request received by the listener.

When a user tries to connect to the listener, it may be servicing several different databases. If user specifies a service name in the connect request then the listener will connect the user to that specific database but if there is no service name in the listener it will connect the user to the service name specified by the DEFAULT_SERVICE parameter.

To fix this issue there are two solutions:-

Sol 1: Configure DEFAULT_SERVICE parameter in LISTENER.ORA with a valid service name:-

LISTENER=
   (DESCRIPTION=
     (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=myserver.example.com)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
 SID_LIST_LISTENER=
   (SID_LIST=
     (SID_DESC=
       (GLOBAL_DBNAME=orcl.example.com)
       (SID_NAME=orcl))
 DEFAULT_SERVICE_LISTENER=oracle.example.com

Once the DEFAULT_SERVICE parameter is configured, reload the listener to bring this change into effect:-

lsnrctl reload

Sol 2. If using a service name, check that the connect descriptor corresponding to the service name in TNSNAMES.ORA of the client has a SERVICE_NAME or SID component in the CONNECT_DATA.

#When using SERVICE_NAME in CONNECT_DATA component
orcl.example.com =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.example.com)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = orcl)
     )
   )#When using SID in CONNECT_DATA component 
orcl.example.com =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.example.com)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SID = orcl)
     )
   )

Источник

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.

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%networkadmin 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_NAME parameter in the CONNECT_DATA section 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%networkadmin directory.

Open the Listener.ora file in a text editor.
Locate the SID_LIST_LISTENER section.
Verify that the SERVICE_NAME parameter matches the value from the TNSNAMES.ORA file.

Example:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mydbservice)
      (ORACLE_HOME = D:Oracleproduct11.2.0dbhome_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> and OracleOraDb11g_home1TNSListener services.
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%networkadmin directory.

3. Where can I find the Listener.ora file?

The Listener.ora file is also located in the %ORACLE_HOME%networkadmin 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.

Oracle Database Documentation
Understanding Oracle Net Services
Troubleshooting Oracle Net Services

Источник

I’m trying to connect my local Oracle 11g database using server explorer in Visual Studio 2010. I tried to setup everything as per instruction, but I’m still getting an ORA-12504 error.

Here is the error:

Here is my tnsnames.ora contents:

# tnsnames.ora Network Configuration File: C:apprsahiproduct11.2.0dbhome_1networkadmintnsnames.ora
# Generated by Oracle configuration tools.


VENUS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SPRPRG020)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = VENUS)
    )
  )

LISTENER_VENUS =
  (ADDRESS = (PROTOCOL = TCP)(HOST = SPRPRG020)(PORT = 1521))

listener.ora file contents,

# listener.ora Network Configuration File: C:apprsahiproduct11.2.0dbhome_1networkadminlistener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = SPRPRG020)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:apprsahi

asked Jan 28, 2011 at 6:35

Try this, in Visual Studio’s Server Explorer Data Connection panel:

Data source name: SPRPRG020/VENUS

i.e. the Data source name format is SERVER_NAME_OR_IP/DATABASE_NAME

mjv

72.7k14 gold badges112 silver badges156 bronze badges

answered Jun 6, 2012 at 4:22

Rene LunaRene Luna

2012 silver badges3 bronze badges

In the «Connection Properties» -> «Advanced» set in the «Data Source» field the information in format HOST/SERVICE_NAME. E.g. 127.0.0.1/TEST

answered Apr 27, 2018 at 12:42

Without having much knowledge in connecting ODP.NET with an Oracle DB, I suggest you start using SERVICE_NAME (e.g: VENUS) in your connection parameters.

Oracle states that:

From 11g onwards, the feature of using the hostname provided in the /etc/hosts file, as the alias / service name has been changed. As a result, the user will have to provide the service name while connecting OR configure the target listener with a default service name.

One way to bypass the SERVICE_NAME client side requirement is to force the Listener always sending you on the same SERVICE by telling it to do so in the tnsnames.ora

DEFAULT_SERVICE_LISTENER =  VENUS

But I do not suggest you do that as it’s quite restricting.

answered Feb 2, 2011 at 1:35

I have experienced the same problem when I was trying to create LinkedServer with Oracle 11g. My oracle instance name was: PC-2.my.xgen and my listener name was «NB«. The last parameter of first statement is actually the concatenation of Oracle server instance and listener name.

So I have write down the following statements in SQL server.

--add a linked server into SQL server
--last parameter contains OracleInstance / Listener Name of desired database
EXEC sp_addlinkedserver   'OracleLinkedServer4', 'Oracle',   'MSDAORA', 'PC-2.my.xgen/nb'

--add login information into linked server
EXEC sp_addlinkedsrvlogin 'OracleLinkedServer4', false,  Null, 'system',  '123456'

In Oracle database «NB» I have a table named : CRD_CIL_NOTIFICATION_TYPE. So I have written down the following statement to get the records.

select * from OracleLinkedServer4..SYSTEM.CRD_CIL_NOTIFICATION_TYPE

answered Nov 29, 2011 at 9:20

Mansoor GeeMansoor Gee

1,0718 silver badges20 bronze badges

In the «Connection Properties» -> «Advanced» set in the «Data Source» field the information in format HOST:PORT/SERVICE_NAME. E.g. 10.xxx.xxx.xxx:1521/SIDNAME

answered Jun 29, 2020 at 5:20

Источник

Published: 25 Jul 2016
Last Modified Date: 24 Aug 2022

Issue

When attempting to connect to an Oracle database, the following error message occurs:

«Oracle database error 12504: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA»

Environment

Tableau Desktop
Oracle

Resolution

Specify the service and port number in the Oracle connection menu. If these values are unknown, contact your Oracle Database Administrator for this additional information.

Cause

The «Service» and «Port» values are not being specified in the Oracle connection dialogue menu. As a result, Tableau Desktop is unable to access the database because of a lack of information.

Additional Information

Discuss this article…

Источник

Related posts

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

1. What is the ORA-12504 error?

2. Where can I find the TNSNAMES.ORA file?

3. Where can I find the Listener.ora file?

4. How can I test the connection after fixing the ORA-12504 error?

5. What should I do if the ORA-12504 error persists?

Issue

Environment

Resolution

Cause

Additional Information

Не пропустите эти материалы по теме: