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
Youāve the most impressive websites. http://www.kayswell.com
Thank you for providing me with these article examples. May I ask you a question? http://www.kayswell.com
Thank you for being of assistance to me. I really loved this article. http://www.kayswell.com
Youāve been great to me. Thank you! http://www.kayswell.com
Hello There. I found your blog the use of msn. This is a really well written article. I will make sure to bookmark it and come back to read extra of your useful info. Thanks for the post.
Weāre a group of volunteers and starting a new scheme in our community. Your website offered us with helpful information to work on. Youāve done a formidable task and our whole community will be thankful to you. http://www.kayswell.com
Wow, marvelous weblog structure! How long have you ever been running a blog for? you make running a blog glance easy. The overall look of your web site is wonderful, as smartly as the content material!
Itās amazing to pay a visit this site and reading the views of all friends regarding this paragraph, while I am also keen of getting know-how. http://www.ifashionstyles.com
Hey there! Would you mind if I share your blog with my myspace group? Thereās a lot of people that I think would really enjoy your content. Please let me know. Thanks http://www.ifashionstyles.com
I am actually pleased to read this blog posts which contains lots of useful facts, thanks for providing these kinds of information.
Thanks for your help and for writing this post. Itās been great.
Hi, i think that i saw you visited my weblog so i came to āreturn the favorā.I am trying to find things to enhance my site!I suppose its ok to use some of your ideas!!
Iām not sure if this is a format issue or something to do with internet browser compatibility but I figured Iād post to let you know. The style and design look great though! Hope you get the issue fixed soon. Many thanks http://www.kayswell.com
I used to be able to find good advice from your blog articles.
Have you ever thought about adding a little bit more than just your articles? I mean, what you say is valuable and everything. Nevertheless think about if you added some great pictures or video clips to give your posts more, āpopā! Your content is excellent but with pics and videos, this site could certainly be one of the best in its niche. Terrific blog!
Right now it sounds like WordPress is the preferred blogging platform out there right now. (from what Iāve read) Is that what you are using on your blog?
Right now it sounds like WordPress is the preferred blogging platform out there right now. (from what Iāve read) Is that what you are using on your blog?
If some one desires expert view concerning blogging afterward i advise him/her to pay a visit this weblog, Keep up the good work. http://www.kayswell.com
Somebody essentially assist to make critically posts I would state. That is the first time I frequented your website page and to this point? I amazed with the research you made to make this actual put up amazing. Magnificent task!
Iāve been surfing online more than 3 hours today, yet I never found any interesting article like yours. Itās pretty worth enough for me. Personally, if all webmasters and bloggers made good content as you did, the web will be much more useful than ever before. http://www.kayswell.com
Hello very nice web site!! Man .. Beautiful .. Amazing .. I will bookmark your site and take the feeds also? I am satisfied to seek out so many useful information here in the put up, we want work out extra techniques on this regard, thank you for sharing. http://www.ifashionstyles.com
Whatās up, after reading this awesome post i am too happy to share my familiarity here with colleagues.
constantly i used to read smaller articles or reviews that also clear their motive,and that is also happening with this piece of writing which I am reading here.
This is a topic thatās near to my heartā¦ Many thanks! http://www.kayswell.com Where are your contact details though?
Heya this is kinda of off topic but I was wanting to know if blogs use WYSIWYG editors or if you have to manually code with HTML. Iām starting a blog soon but have no coding know-how so I wanted to get guidance from someone with experience. Any help would be enormously appreciated!
Itās genuinely very difficult in this busy life to listen news on Television, so I just use world wide web for that reason, and take the most up-to-date news. http://www.ifashionstyles.com
Hello there! This is kind of off topic but I need some advice from an established blog. Is it hard to set up your own blog? I’m not very techincal but I can figure things out pretty quick. I’m thinking about setting up my own but I’m not sure where to begin. Do you have any ideas or suggestions? Thank you
Sweet blog! I found it while searching on Yahoo News. Do you have any tips on how to get listed in Yahoo News?Iāve been trying for a while but I never seem to get there! Many thanks http://www.ifashionstyles.com
Great blog youāve got here.. Itās difficult to find excellent writing like yours these days. I seriously appreciate individuals like you! Take care!!
Hi, Neat post. There is a problem with your site in web explorer, might test this? IE still is the market chief and a large component of other people will leave out your wonderful writing because of this problem. http://www.kayswell.com