Our client had requirement to pull data from MSSQL server to Oracle Database. I installed Oracle Gateway Server for MSSQL and configured database link to access the MSSQL Database.
When trying to access a heterogenous database link between Oracle Database 12c and MSSQL database, some users were getting the below error
ORA-12154: TNS:could not resolve the connect identifier specified.
Analysis
Database link was created on the Oracle Database environment to access data from MSSQL Server.
Database link was created using below command
create public database link DBLINK_INTG connect to “MYSQL” identified by “Password” using ‘dg4msql’;
Error
SQL>select sysdate from dual@DBLINK_INTG;
ORA-12154: TNS:could not resolve the connect identifier specified
12154. 00000 – “TNS:could not resolve the connect identifier specified”
*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:
– If you are using 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 may make it unusable.
– If you are using 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 name or a full LDAP DN
as the connect identifier
– If you are using easy connect naming:
– Verify that “EZCONNECT” is listed as one of the values of the
NAMES.DIRETORY_PATH parameter in the Oracle Net profile
(SQLNET.ORA).
– Make sure the host, port and service name specified
are correct.
When trying to access a heterogenous database link between Oracle Database 12c and MSSQL database, some users were getting the below error
ORA-12154: TNS:could not resolve the connect identifier specified.
Analysis
Database link was created on the Oracle Database environment to access data from MSSQL Server.
Database link was created using below command
create public database link DBLINK_INTG connect to “MYSQL” identified by “Password” using ‘dg4msql’;
Error
SQL>select sysdate from dual@DBLINK_INTG;
ORA-12154: TNS:could not resolve the connect identifier specified
12154. 00000 – “TNS:could not resolve the connect identifier specified”
*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:
– If you are using 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 may make it unusable.
– If you are using 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 name or a full LDAP DN
as the connect identifier
– If you are using easy connect naming:
– Verify that “EZCONNECT” is listed as one of the values of the
NAMES.DIRETORY_PATH parameter in the Oracle Net profile
(SQLNET.ORA).
– Make sure the host, port and service name specified
are correct.
Solution
Recreate Heterogenous Database link using below command
create public database link DBLINK_INTG connect to “MYSQL” identified by “Password” using ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Test1.domain.com)(PORT=1524))(CONNECT_DATA=(SID=dg4msql))(HS=OK))’
A large percentage of of whatever you assert happens to be astonishingly appropriate and it makes me ponder the reason why I hadn’t looked at this with this light before. This particular article truly did turn the light on for me personally as far as this particular topic goes. However at this time there is actually one particular position I am not necessarily too cozy with so while I attempt to reconcile that with the core theme of your point, permit me observe exactly what the rest of the subscribers have to point out.Well done.
Thank you for providing me with these article examples. May I ask you a question?
Serious car buyers should always do this first: Run a VIN check to see hidden issues like accident damage, salvage history, or mileage tampering. Use this link to check your VIN: https://vinifycheck.com/vin-check
It’s perfect time to make some plans for the longer term and it’s time to be happy. I have read this submit and if I may I wish to recommend you few fascinating issues or suggestions. Maybe you can write next articles regarding this article. I want to learn even more things approximately it!
Iād like to find out more? Iād love to find out more details. http://www.kayswell.com
In accordance with my observation, after a foreclosed home is sold at an auction, it is common for your borrower in order to still have some sort ofthat remaining balance on the bank loan. There are many loan providers who try and have all rates and liens paid off by the up coming buyer. On the other hand, depending on specified programs, legislation, and state legal guidelines there may be some loans which are not easily handled through the shift of lending options. Therefore, the responsibility still remains on the customer that has got his or her property in foreclosure process. Thanks for sharing your notions on this website.
Nice post. I used to be checking constantly this blog and I’m impressed! Very helpful info specifically the closing section š I deal with such info much. I used to be seeking this certain info for a long time. Thanks and best of luck.
Almost all of what you mention is astonishingly appropriate and that makes me wonder why I had not looked at this with this light before. Your piece truly did turn the light on for me personally as far as this specific issue goes. However there is just one point I am not too comfortable with and whilst I make an effort to reconcile that with the actual core theme of the position, allow me observe what the rest of the visitors have to point out.Well done.
Excellent post. I was checking constantly this weblog and I am impressed! Very helpful info particularly the last section š I handle such info much. I was looking for this certain info for a very long time. Thanks and best of luck.
Hello.This post was really fascinating, particularly because I was searching for thoughts on this issue last Saturday.
Thanks for your marvelous posting! I really enjoyed reading it, you happen to be a great author.I will make certain to bookmark your blog and will eventually come back later in life. I want to encourage you to continue your great job, have a nice afternoon!
I beloved up to you will receive carried out proper here. The sketch is tasteful, your authored subject matter stylish. however, you command get got an nervousness over that you wish be delivering the following. sick for sure come further in the past again since exactly the similar just about very often inside of case you shield this hike.
Your articles are extremely helpful to me. May I ask for more information? http://www.ifashionstyles.com
Thank you for writing this post! http://www.hairstylesvip.com
Your articles are extremely helpful to me. Please provide more information! http://www.hairstylesvip.com
Nice post. I learn something totally new and challenging on websites