Ora Connection Error
Contents |
while creating a linked server to Oracle ★★★★★★★★★★★★★★★ SnehadeepJune 30, 201028 Share 0 0 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.
Full error error ora-12154 tns could not resolve the connect identifier specified message: OLE DB provider "MSDAORA" for linked server "LINKED_ORA" returned message "ORA-12154: TNS:could not resolve ora-12154 tns could not resolve service name the connect identifier specified". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB ora-12154 tns could not resolve the connect identifier specified windows 7 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 upAsp.net Ora-12154: Tns:could Not Resolve The Connect Identifier Specified
and troubleshoot a linked server to an Oracle database in SQL Server
http://support.microsoft.com/kb/280106 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 tns could not resolve the connect identifier specified sqlplus (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 [insa unique format. Though the TNSNames.ora file may not always be required. For instance, if you have installed only the Tableau-provided Oracle files and do not have a stand-alone Oracle client. The Oracle error messages refer to the TNSNames.ora file. tns could not resolve the connect identifier specified odbc Typically, correcting route or naming syntax in the Advanced Oracle Connection dialog box or using
Ora-12154 Sqlplus
your full .WORLD database name resolves most Oracle connection issues. If your connection error requires more troubleshooting, refer to the four common
Error While Trying To Retrieve Text For Error Ora-12154
connection errors listed below. ORA-03113: end-of-file on communication channelORA-12154: TNS: could not resolve the connect identifier specified HOST value incorrect or Global name incorrect or unknownORA-12514: TNS listener does not currently know of service requested in connect https://blogs.msdn.microsoft.com/dataaccesstechnologies/2010/06/30/ora-12154-tns-could-not-resolve-the-connect-identifier-specified-error-while-creating-a-linked-server-to-oracle/ descriptorSERVICE value incorrectORA-12541: TNS: no listenerPORT value incorrectORA-00932: inconsistent data types ORA-03113: end-of-file on communication channel ORA-03113 is a catch-all type error for any problem interrupting an Oracle session. There can be numerous causes for this error. Please refer to the list below for some troubleshooting guidance. Refer to Oracle documentation specific to this error: My Oracle Support.Refer to Oracle's B Troubleshooting Common Errors page.Oracle recommends that you check for network problems and review the http://kb.tableau.com/articles/knowledgebase/oracle-connection-errors SQL*Net setup.If you're connecting to Oracle 9.2.0.5, in many cases the primary cause of this error is Oracle bug 3010227. Ask your Oracle database administrator to apply Oracle patch 9.2.0.6 or another patch appropriate for your server. Set the Oracle initialization parameter 'STAR_TRANSFORMATION_ENABLED' to FALSE.Alternatively, if you would like to test this issue further follow the optional procedure listed below. Step 1 From the Tableau Desktop start page, select Connect to Data. Step 2 For Tableau Desktop 8.2 and later, on the Connect page, click Oracle. For Tableau Desktop 8.1 and earlier, on the Connect to Data page, click Oracle. Step 3 For Tableau Desktop 8.2 and later, connect to the Oracle server, and then click OK. For Tableau Desktop 8.1 and earlier, follow the steps in the Oracle Connection dialog box to complete the connection. For more information about completing the connection steps, refer to the Oracle Database topic in the Desktop Help. Step 4 For Tableau Desktop 8.2 and later, do the following: In the join area, hover over the Custom SQL table until the edit icon displays, and then click the icon.Copy the query in the Edit Custom SQL dialog box. For Tableau Desktop 8.1 and earlier, do the following: Under Step 5, select a table or view from the schema, and then select Custom S
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog http://www.dba-oracle.com/t_ora_28547_connection_to_server_failed_probable_oracle_net_admin_error.htm
ORA-28547: connection to server http://ss64.com/ora/syntax-ora-12154.html failed, probable Oracle Net admin error tips Oracle Error Tips by Stephanie F. The Oracle docs note this on the ORA-28547 error: ORA-28547: connection to server failed, probable Oracle Net admin error Cause: A failure occurred during initialization of a network connection from a client process to the Oracle server: The connection could not was completed but a disconnect occurred while trying to perform protocol-specific initialization, usually due to use of different network protocols by opposite sides of the connection. This usually is caused by incorrect Oracle Net administrative setup for database links or external procedure calls. The most frequent specific causes are: -- The connection uses a connect string which refers to a Heterogeneous Services agent instead of an Oracle could not resolve server. -- The connection uses a connect string which includes an (HS=) specification. ********************************** Action: Check Oracle Net administration in the following ways: -- When using TNSNAMES.ORA or an Oracle Names server, make sure that the client connection to the ORACLE server uses the correct service name or SID. -- Check LISTENER.ORA on the connection end point"s host machine to assure that this service name or SID refers to the correct server. -- Confirm in TNSNAMES.ORA or the equivalent service definition that the connect string does NOT contain (HS=). Per Internet sources, ORA-28547 can occur on Oracle Net Services; version 10.1.0 to 10.2 when, "Oracle Native Authentication Services is failing Client sqlnet trace, level SUPPORT, shows the underlying error is a TNS-12631" causing "All connections to the database, including SYSDBA, fail with an ORA-28547" To resolve ORA-28547, this document states that the following steps should be taken: As a workaround disable Oracle Native Authentication Services. Change SQLNET.AUTHENTICATION_SERVICES from (NTS) to (NONE) in the server sqlnet.ora As a solution change the database service to run as a user who is a member of the Domain Admin group. Oracle Log On user must be a privileged useserver but either failed to contact the server or did not find a matching service name on that server. TNS or 'Transparent Network Substrate' is a transport protocol that allows Oracle sessions to communicate across a network. DB Server <=> Listener <=> Network connection <=> Client (SQL*Net) <=> Application. In the path above, tnsping can be used to test the parts in bold and should be the first troubleshooting step. If tnsping fails, that generally indicates a problem finding the server connection - if TNSNAMES specifies an IP address, is it correct?, if it's a DNS name, is that resolving correctly? (try switching for an IP address to find out.) If tnsping succeeds, that can indicate a problem with the client configuration but could equally be that the connection is working as far as the listener but the database is down. Try comparing with another PC - if multiple machines are failing that usually points towards a server issue. Ora-12154 can be a tricky problem to nail down because there are many possible causes, try making a connection directly on the server or completely regenerate the TNSNAMES.ORA file on the client. ORA-12154 description: Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached. Action: Local naming (TNSNAMES.ORA file): - Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA) - Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible. - Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file. - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file can make it unusable. Directory Naming: - Verify that "LDAP" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA). - Verify that the LDAP directory server is up and that it is accessible. - Verify that the net service name or database name used as the connect identifier is configured in the directory. - Verify that the default context being used is correct by specifying a fully qualified net service nam