This is one of the most common errors while creating linked server to Oracle database. Today I will discuss the reason for this error and possible resolutions.
ORA-12514 TNS:listener does not currently know of service requested in connect descriptor Connectivity errors are common questions on Oracle related forums and in many cases they are highly misunderstood by the poster of the questions and some responders. ORA-12514 TNS:listener does not currently know of service requested in connect descri. ORA-12514, TNS:listener does not currently know of service requested in connect descriptor. With errorcode: 45257. Resolution: Was added to the listener of Oracle, tag SIDLISTLISTENER.
Full error message:
OLE DB provider 'MSDAORA' for linked server 'LINKED_ORA' returned message 'ORA-12154: TNS:could not resolve the connect identifier specified'.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider 'MSDAORA' for linked server 'LINKED_ORA'.
First of all make sure you have reviewed the following Microsoft KB article that has a lot of good information on troubleshooting Oracle linked server issues.
How to set up and troubleshoot a linked server to an Oracle database in SQL Server
Also make sure you have installed Oracle Client on the SQL server. If the SQL server is 64 bit then we need to install 64 bit Oracle provider. You can also create linked server using Oracle ODBC driver together with Microsoft OLE DB provider for ODBC. Once again on a 64 bit SQL server you need to install the 64-Bit OLEDB Provider for ODBC (MSDASQL) and 64 bit Oracle ODBC drivers. However 64-Bit OLEDB Provider for ODBC (MSDASQL) is already there in Windows Vista/Windows Server 2008 and later OS.
This particular error message is a very general error message and can happen for quite a number of reasons. For general understanding of the error, you can review oracle documentation like this http://ora-12154.ora-code.com/
In SQL Server Linked Server, it could indicate a few things (not limited to)–
1.SQL Server (and oracle net libraries) is not able to get the TNS alias from tnsnames.ora file.
2.Something is wrong with the way the alias is created in the tnsnames.ora file (incorrect syntax)
3.TNS alias could not be resolved into a connect descriptor
Below is a list of things that you can try to resolve this issue.
1. Verify that the tnsnames.ora file has the alias and the service name that the customer is using.
TNS entry for the Oracle database
OracleDB_Dev =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server01.mydomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OracleDB)
(SERVER = DEDICATED)
)
)
In the above tnsnames.ora file Alias = OracleDB_Dev
Service Name: OracleDB (Actual Oracle service name [instance name in SQL])
2. Check the sqlnet.ora file under ‘Admin’ folder in Oracle home [Dir:appproduct11.1.0client_1networkadmin] and ensure that we have TNSNames in NAMES.DIRECTORY_PATH
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
3. Verify if you can connect to Oracle from the SQL server machine using tools installed with Oracle Client [For example 'SQL Developer' or “SQL Plus”] with the same user id/password or TNS alias.
5. Check if the environment variable 'PATH' has the path for tnsnames.ora file specified.
Sample Value of Environment Variable PATH:
E:appproduct11.1.0client_1bin;C:Program FilesBusiness ObjectsCommon3.5binNOTES;C:Program FilesBusiness ObjectsCommon3.5binNOTESDATA;%Systemroot%Microsoft.NETFrameworkv1.1.4322;%SystemRoot%system32;%SystemRoot%;%SystemRoot%System32Wbem;C:Program FilesDellSysMgtomabin;C:Program FilesMicrosoft SQL Server80ToolsBINN;C:Program FilesCommon FilesMicrosoft Sharedweb server extensions60TEMPLATEADMIN1033;C:Program FilesMicrosoft SQL Server80ToolsBinn;C:Program FilesMicrosoft SQL Server90DTSBinn;C:Program FilesMicrosoft SQL Server90Toolsbinn;C:Program FilesMicrosoft SQL Server90ToolsBinnVSShellCommon7IDE;C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies;C:Program FilesMicrosoft Network Monitor 3
Note: make sure that the path is a valid path and there is no space.
6. Check the value of the key”Oracle_Home” in the registry underHKEY_LOCAL_MACHINESOFTWAREORACLEKEY_OraClient11g_home1and verify that it has the right path for the Oracle home.
7.Check for the registry key “TNS_ADMIN” at HKEY_LOCAL_MACHINESOFTWAREORACLE. If it exists then make sure it has the right value as “Dir:appproduct11.1.0client_1networkadmin”. If you don’t see the key then create the key and set appropriate value as below.
Regedit->HKEY_LOCAL_MACHINE->Software->Oracle->RightClick NEW->StringValue and name
it TNS_ADMIN and give the value“X:appproduct11.1.0client_1networkadmin”
Note: This is not a must but in some cases this is what fixed the issue.
8.Check if SQL server start up account has permission to the Oracle Home. Also collect Process monitor log and check for “access denied”. Process monitor log should show if we are able to find the tnsnames.ora file.
Ora-12514 Oracle 11g Windows 7
9. Make sure you don’t have multiple Oracle homes or multiple Oracle clients installed. Check the 'HKEY_LOCAL_MACHINESOFTWAREORACLEALL_HOMESHOME_COUNTER' key value.
10. Check if Oracle OLE DB provider is running InProcess. If ‘yes’ then try to run out-of- process and see if that resolves the issue.
Note: You can check and verify if MS OLE DB Provider for Oracle is running InProcess from the registry key at HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerProvidersMSDAORA
11. You can try collecting simultaneous Network trace from both SQL and Oracle servers and check if there are any communications between the two servers.
12. Try to connect to Oracle from the SQL server using the UDL.Use the same TNS name. If you get the same error that means the issue is not specific to SSMS or linked server.
Creating and Configuring Universal Data Link (.udl) Files
13. Try to specify all the information in the data source instead of using the TNS alias to connect to the Oracle database (this is a way to bypass tnsnames.ora file when connecting to Oracle).
Sample Data Source:
Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= server01.mydomain.com)(PORT=1521)))(CONNECT_DATA=(SID=OracleDB)(SERVER=DEDICATED)));
Author : Mohammad(MSFT) SQL Developer Engineer, Microsoft
Reviewed by : Azim(MSFT), SQL Developer Technical Lead , Microsoft
'Hi Everyone
I have a problem with restarting our 8=2E1=2E6 test database after=
issuing the shutdown command=2E
The puThe following details the commands issued, and the error=
messages that I have received back=2E
SQL> shutdown immediate
Database closed=2E
Database dismounted=2E
ORACLE instance shut down=2E
SQL> startup
ORACLE instance started=2E
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in=
connect descriptor
SQL>
What is puzzling to me is that if i issue the oradim -shutdown=
followed by the oradim -startup commands the database is then=
open and I am able to connect to it and execute sql commands ok=2E=
I think my problem lies in the listener file as when i check=
the status this is the error messages that I get
U:>lsnrctl
LSNRCTL for 32-bit Windows: Version 8=2E1=2E6=2E0=2E0 - Production on=
17-SEP-2004 16:08:32
(c) Copyright 1998, 1999, Oracle Corporation=2E All rights=
reserved=2E
Welcome to LSNRCTL, type 'help' for information=2E
LSNRCTL> status
Connecting to (ADDRESS=3D(PROTOCOL=3Dipc)(KEY=3DPNPKEY))
TNS-01103: Protocol specific component of the address is=
incorrectly specified
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 2: No such file or directory
Ora-12514 Windows Oracle 11g
LSNRCTL> START
Starting tnslsnr: please wait=2E=2E=2E
, error 1060=2E
TNSLSNR for 32-bit Windows: Version 8=2E1=2E6=2E0=2E0 - Production
System parameter file is=
E:OracleOra81networkadminlistener=2Eora
Log messages written to E:OracleOra81networkloglistener=2Elog=
Listening on:=
(DESCRIPTION=3D(ADDRESS=3D(PROTOCOL=3Dipc)(PIPENAME=3D=2EpipePNPKEYipc=
))
)
Attempted to listen on: (ADDRESS=3D(PROTOCOL=3Dtcp)(PORT=3D1521))
TNS-12542: TNS:address already in use
TNS-12560: TNS:protocol adapter error
TNS-00512: Address already in use
32-bit Windows Error: 48: Unknown error
It is interesting to note however that the current listener is=
LISTENER, yet the listener=2Eora file has it listed as =9181=92, and=
the windows net services has it as Listener81
I can however ping the Test database so I'm sure that the=
tnsnames=2Eora file is ok=2E
Can anyone explain to me how I am able to connect to the database=
fine by restarting the windows service, yet I am unable to=
connect with the 'shutdown' and 'startup' commands?
Ora-12504
Thanks for any help you can give me=2E