我们有一个本地运行的应用程序，我们遇到了以下错误:
ORA-12514: TNS:监听器当前不知道所请求的服务 在连接描述符中
我已经使用正确解决的tnspring测试了连接 我尝试使用SQLPlus进行连接，但失败了，出现了与上面相同的错误。我在SQLPlus中使用了以下语法:
sqlplus username/password@addressname[or host name]
我们已核实:
服务器上的TNS Listener正在运行。 服务器上的Oracle本身正在运行。
我们不知道这个环境发生了什么变化。 还有什么可以测试的吗?
当前回答
对于那些可能在虚拟机中运行Oracle的人(比如我)，我看到这个问题是因为我的虚拟机内存不足，这似乎阻止了OracleDB正确启动/运行。增加我的虚拟机内存和重新启动修复了这个问题。
其他回答
在我的例子中，tnsnames中缺少SERVICE_NAME周围的圆括号。ora文件。
<DBNAME> =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)(HOST = nupark-cnvr-ora )(PORT=1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <DBNAME> ***CLOSING ROUND BRACKET WAS MISSING HERE***
)
)
LISTENER_<DBNAME> =
(ADDRESS = (PROTOCOL = TCP)(HOST = nupark-cnvr-ora)(PORT = 1521))
这里有很多答案，但这里有一个工作示例的代码，你可以立即复制、粘贴和测试:
对我来说，在指定正确的SERVICE_NAME后，错误12514得到了解决。 您可以在服务器上的文件tnsnames中找到它。ora自带3个预定义的服务名称(其中一个是“XE”)。
I installed the Oracle Express database OracleXE112 which already comes with some preinstalled demo tables. When you start the installer you are asked for a password. I entered "xxx" as password. (not used in production) My server runs on the machine 192.168.1.158 On the server you must explicitely allow access for the process TNSLSNR.exe in the Windows Firewall. This process listens on port 1521. OPTION A: For C# (.NET2 or .NET4) you can download ODAC11, from which you have to add Oracle.DataAccess.dll to your project. Additionally this DLL depends on: OraOps11w.dll, oci.dll, oraociei11.dll (130MB!), msvcr80.dll. These DLLs must be in the same directory as the EXE or you must specify the DLL path in: HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP.NET\4.112.4.0\DllPath. On 64 bit machines write additionally to HKLM\SOFTWARE\Wow6432Node\Oracle\... OPTION B: If you have downloaded ODAC12 you need Oracle.DataAccess.dll, OraOps12w.dll, oci.dll, oraociei12.dll (160MB!), oraons.dll, msvcr100.dll. The Registry path is HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP.NET\4.121.2.0\DllPath OPTION C: If you don't want huge DLL's of more than 100 MB you should download ODP.NET_Managed12.x.x.x.xxxxx.zip in which you find Oracle.ManagedDataAccess.dll which is only 4 MB and is a pure managed DLL which works in 32 bit and 64 bit processes as well and depends on no other DLL and does not require any registry entries. The following C# code works for me without any configuration on the server side (just the default installation):
using Oracle.DataAccess.Client;
or
using Oracle.ManagedDataAccess.Client;
....
string oradb = "Data Source=(DESCRIPTION="
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.158)(PORT=1521)))"
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));"
+ "User Id=SYSTEM;Password=xxx;";
using (OracleConnection conn = new OracleConnection(oradb))
{
conn.Open();
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.CommandText = "select TABLESPACE_NAME from DBA_DATA_FILES";
using (OracleDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
listBox.Items.Add(dr["TABLESPACE_NAME"]);
}
}
}
}
如果SERVICE_NAME=XE错误，则会得到错误12514。SERVICE_NAME是可选的。你也可以把它放在一边。
我也有同样的问题。对我来说，就是写作
sqlplus myusername/mypassword@localhost
这样做使它连接到默认的服务名，我猜。
在我的情况下，错误是由于监听器没有注册db的服务。我通过注册服务解决了这个问题。例子:
我在tnsnames.ora中的描述符:
LOCALDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LOCALDB)
)
)
因此，我继续在侦听器中注册服务。奥拉手动:
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = LOCALDB)
(ORACLE_HOME = C:\Oracle\product\11.2.0\dbhome_1)
(SID_NAME = LOCALDB)
)
最后，通过命令重新启动监听器:
> lsnrctl stop
> lsnrctl start
完成了!
我得到了同样的错误，因为指定的远程SID是错误的:
> sqlplus $DATASOURCE_USERNAME/$DATASOURCE_PASSWORD@$DB_SERVER_URL/$REMOTE_SID
我查询了系统数据库:
Select * from global_name;
找到了我的远程SID(“XE”)。
这样我就可以毫无问题地连接了。
