Friday, February 8, 2013

67 Oracle Data Link trouble shooting

I am testing the Oracle data link. This post is to describe the issue and trouble shooting.

 

List all the data link

Query all_db_links table.

select *  from all_db_links

image

Create data link

When create the data link, we need to provide the connection string . The connection string is best to defined   in the tnsname.ora for easy maintained . However, to eliminate the problem about the tnsname.ora, we can embodied the entire connection string in the SQL statement .

 

Below is the example to create the public data link . The data link name is “TEST_LINK”. Oracle would append the db_domain when query the db_links table.

 

CREATE DATABASE LINK TEST_LINK USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = panda2.localdomain)(PORT = 7001)))(CONNECT_DATA = (ORACLE_SID = oradb2)))'

image

Below example is moving the full connection string to the tnsname.ora.

create database link TEST_LINK2 using 'ORADB2.panda2.localdomain';

 

image

Below example is to create the full Databsae link name with remote database . This is necessary if global_names set to true on either of the source or target, otherwise, we would get the ORA-02085 error.

create database link ORADB2.panda2.localdomain using 'ORADB2.panda2.localdomain';

 

 

How to resolve ORA-02085 error

This error is because the global_names. When connect to the data link, if global names parameter set to true on the local database, Oracle would expect the Data link name same as the remote global name.

 

ORA-02085: database link TEST_LINK2.PANDA1.LOCALDOMAIN connects to
ORADB2.PANDA2.LOCALDOMAIN

image

The data link name is the real name show up ALL_DB_LINKS table , DB_LINK column. Global name can be found in the global_name table.

show parameter global_names
select * from global_name;
select DB_LINK from ALL_DB_LINKS;

 

image

Here are solutions

1. Turn off the global names: In some case, this may not be doable or desire

2. Turn off global names in the session level

image

3. The best way is to create the DB_LINK name that is the remote database’s global name

create database link ORADB2.panda2.localdomain using 'ORADB2.panda2.localdomain';

 

image

 

How to resolve ORA-01017 error

This error message indicate the authentication fail.

ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from TEST_LINK2

image

Here are possible reasons:

For the private database link, if login/password is not defined during the create database link, that mans it is using the current user’s credential . We have to make sure the remote database has the same account and password set.

If the private database link is create by sys, the connection would fail because sys has to login as sysdba and the database link connection can not specify the sysdba role.

 

 

Reference

http://www.akadia.com/services/ora_database_links.html

67 comments:

  1. Getting blog comments on your site is always a good indication that the niche blog comments reader is involved in what it is you are posting about. Good bad or indifferent this is a good barometer that your blog posting is not a wasted effort. The interactivity of your readers is even more important if you are running a home business blog.

    ReplyDelete
  2. A little-known free way to get tons of targeted visitors is via blog comments. Commenting on niche guest post can be fun, and it's a great way to drive traffic for your business!

    ReplyDelete
  3. wow... what a great blog, this writter who wrote this article it's realy a great blogger, this article so inspiring me to be a better person OpenVZ VPS Hosting

    ReplyDelete
  4. Thank you because you have been willing to share information with us. we will always appreciate all you have done here because I know you are very concerned with our. Odzyskiwanie danych Łódź

    ReplyDelete
  5. Nice to be visiting your blog again, it has been months for me. Well this article that i’ve been waited for so long. I need this article to complete my assignment in the college, and it has same topic with your article. Thanks, great share. odzyskiwanie danych Warszawa

    ReplyDelete
  6. If you're a click this site owner, this article will discuss why you should be insuring your company vehicles. Anytime your company vehicle is on the road, it is at risk.

    ReplyDelete
  7. This site is really a walk-through for all of the data you wanted about it and didn’t know who to ask. Glimpse here, and you’ll undoubtedly discover it. Pest Control In Abu Dhabi

    ReplyDelete
  8. Starting a business is tough.. no exaggeration. To start and run a business is both nerve-racking, petrifying.. and a dozen other emotions. It doesn't matter if you are drafting the initial business plan, just opened the doors, or been around for awhile. Planning will alleviate much of the risk, but not all. You can't control everything in life, nor in business. Want to grow your company? Here are five guidelines to help you do that. vladimir vrbaski republika

    ReplyDelete
  9. Very efficiently written information. It will be beneficial to anybody who utilizes it, including me. Keep up the good work. For sure i will check out more posts. This site seems to get a good amount of visitors. Blog Commenting Service

    ReplyDelete
  10. Likely you've noticed, but there are a growing number of people with food "issues." It might be a food intolerance, a food sensitivity, or even a very serious, life threatening food allergy. I'm one of them. Maybe you are too. Adverse Impact Calculator

    ReplyDelete
  11. The NRI loans are very helpful to make the dreams true of an NRI. An NRI can get various loans like home loans, business loans and education loans. Several lenders are available with their different loan schemes so, the borrower should know about all the terms and conditions and then apply for the most suitable one for him. Lakota Cash Payday Loan

    ReplyDelete
  12. While it seems like the whole country is feeling a cash crunch in these hard economical times many just don't seem to have enough money to pay for bare necessities anymore, and most have already pawned everything they can at the local pawn shop. There are many options to get cash fast including payday loans and signature loans. lakota cash

    ReplyDelete
  13. Setting up a business can be an overwhelming task with a lot to comprehend. It https://buycheapcad.com/autodesk-infrastructure-design-suite.html starts with a dream and a vision, but how do you turn that dream into reality? The dream of becoming your own boss and having the freedom to make your own decisions can be a complicated one at the beginning.

    ReplyDelete
  14. Often the Are generally Weight reduction plan is unquestionably an low-priced and flexible weight-reduction plan product modeled on individuals seeking out shed some pounds combined with at some point maintain a far healthier your life. la weight loss shrutika hot

    ReplyDelete
  15. Financing a franchise is hard to do these days just like nearly all businesses on the planet. Yet, with loan programs like those from the SBA, your chances of getting approved and funded can only improve. how to download Minecraft pe

    ReplyDelete
  16. Wanted posting. Loads of excellent writing here. I wish I saw it found the site sooner. Congrats! how to get free cash app money

    ReplyDelete
  17. Glad to be one of the visitors on this awe inspiring web site : D. free vc glitch

    ReplyDelete
  18. Cheap Replica Handbags You guide me by sharing this toolbox i face problem whenever i exploit this promotion reading your blog i am able to easily use. Google Play subscription 2019

    ReplyDelete
  19. Thanks for every other excellent post. The place else may just anyone get that kind of info in such an ideal manner of writing? I’ve a presentation subsequent week, and I am at the look for such information. how to get free effects on video star

    ReplyDelete
  20. Glad to be one of the visitors on this awe inspiring web site : D. free amazon prime account

    ReplyDelete
  21. Nice post. I learn something more challenging on distinct blogs everyday. It will always be stimulating to read content off their writers and practice a little something from their store. I’d choose to use some with all the content in my small weblog whether you do not mind. Natually I’ll provide a link on your own internet weblog. Many thanks sharing. Pokemon Go Joystick

    ReplyDelete
  22. Schools across the globe are going through a growth spurt of sorts, which is both painful and unavoidable. I'm talking, of course, about technology integration. Maybe your class is using a COW (Computer on Wheels) cart once a week or maybe every student in your school is suddenly holding an iPad and administrators are throwing around the dreaded phrase "going paperless." anime app android

    ReplyDelete
  23. Hi this is somewhat of off topic but I was wondering if blogs use WYSIWYG editors or if you have to manually code with HTML. I’m starting a blog soon but have no coding knowledge so I wanted to get guidance from someone with experience. Any help would be greatly appreciated! minecraft pe free

    ReplyDelete
  24. This is a excellent blog, would you be involved in doing an interview about just how you designed it? If so e-mail me! how to get disney plus for free

    ReplyDelete
  25. House Lawyers… [...]just below, are some totally unrelated sites to ours, however, they are definitely worth checking out[...]… moviebox pro for iphone

    ReplyDelete
  26. Howdy! Do you know if they make any plugins to safeguard against hackers? I’m kinda paranoid about losing everything I’ve worked hard on. Any recommendations? install showbox

    ReplyDelete
  27. I have to convey my respect for your kindness for all those that require guidance on this one field. Your special commitment to passing the solution up and down has been incredibly functional and has continually empowered most people just like me to achieve their dreams. Your amazing insightful information entails much to me and especially to my peers. Thanks a ton; from all of us. View Private Instagram Photos

    ReplyDelete
  28. Excellent publish from specialist also it will probably be a fantastic know how to me and thanks very much for posting this useful data with us all. pokemon go gps

    ReplyDelete
  29. My brother recommended I may like this web site. He was entirely right. This publish actually made my day. You can not consider just how so much time I had spent for this information! Thank you! showbox download

    ReplyDelete
  30. I have been exploring for a little bit for any high-quality articles or blog posts on this sort of area . Exploring in Yahoo I at last stumbled upon this website. Reading this information So i am happy to convey that I’ve an incredibly good uncanny feeling I discovered exactly what I needed. I most certainly will make certain to do not forget this web site and give it a glance regularly. google play gift card

    ReplyDelete
  31. Hey there! Wonderful stuff, please do tell us when you post again something similar! free spotify premium

    ReplyDelete
  32. WHY IS BUSINESS ANALYTICS IMPORTANT? The foremost goal of any organization is profitability, and customer satisfaction is a huge component of the success of a business. When an organization innovates loans for bad credit guaranteed stays ahead of its competition, it fulfills and goes beyond customer expectations.

    ReplyDelete
  33. For leaders to succeed, they must have a vision. Visionary leaders must have the ability to envision their desired future and have the capability to take action in order to execute what they have planned within their home business blueprint. Leaders who do not have a vision are incapable of bringing development to themselves or provide business coaching to those surrounding them. Visionary leaders know the best time to sit back and wait instead of making spontaneous, hasty decisions that may have a negative impact to themselves or those in society. They understand that even though it is important to take action and work toward reaching their goals, acting blindly without considering the welfare of the society brings more harm than good. Follow us on the characteristics that create an exceptional leader in the home based business industry. Stone Chip Repair

    ReplyDelete
  34. Glad to be one of several visitants on this amazing web site : D. rhinoceros 3d 5

    ReplyDelete
  35. Aw, this was a really nice post. In thought I would like to place in writing in this way moreover – taking time and actual effort to create a very good article… but what / things I say… I procrastinate alot and also no indicates apparently get something done. Moviebox Pro Download

    ReplyDelete
  36. I am crazy about this blog. I have visit so many time to this blog. I was found this blog from Google. I have received a nice stuff of information. I really appreciate to meet to it and i emphasize to this blog. My curiosity to learn more and more on this blog. birthday gifts

    ReplyDelete
  37. As I website owner I believe the content material here is very superb, thanks for your efforts. Install Spotify++

    ReplyDelete
  38. Hey There. I found your blog using msn. This is a very well written article. I will be sure to bookmark it and return to read more of your useful information. Thanks for the post. I will definitely return. facetune apk

    ReplyDelete
  39. is it me or does she look like a white version of Khloe Kardashian??hmmm putlocker

    ReplyDelete
  40. I just couldn’t get away from your web site before suggesting that I truly appreciated the typical information an individual give on your guests? Is likely to be yet again routinely to examine fresh articles Hairstylist in in San Bernardino

    ReplyDelete
  41. Youre so cool! I dont suppose Ive read anything such as this just before. So nice to discover somebody with some original ideas on this subject. realy thanks for starting this up. this fabulous website is a thing that is needed on the net, someone after a little originality. beneficial problem for bringing a new challenge on the internet! 예스카지노 사이트

    ----------------------------
    There are actually lots of particulars like that to take into consideration. That may be a nice level to bring up. I provide the ideas above as general inspiration but clearly there are questions just like the one you convey up the place a very powerful thing will probably be working in sincere good faith. I don?t know if greatest practices have emerged round issues like that, but I’m sure that your job is clearly recognized as a good game. Both boys and girls feel the impression of just a moment’s pleasure, for the rest of their lives. 더킹카지노 주소

    ReplyDelete
  42. Hello there, Can I copy that photograph and apply that on my personal blog? 스포츠중계


    -------------------------------------------------------


    I really like what you guys tend to be up too. This type of clever work and reporting! Keep up the fantastic works guys I’ve you guys to blogroll. 일본야구중계

    ReplyDelete
  43. I definitely enjoying every little bit of it. It is a great website and nice share. I want to thank you. Good job! You guys do a great blog, and have some great contents. Keep up the good work. link alternatif dewapoker

    ReplyDelete
  44. Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Anyway I’ll be subscribing to your feed and I hope you post again soon. https://royalcbd.com/product/cbd-oil-500mg/

    ReplyDelete
  45. I’m really enjoying the theme/design of your weblog. Do you ever run into any web browser compatibility issues? A handful of my blog visitors have complained about my blog not operating correctly in Explorer but looks great in Opera. Do you have any solutions to help fix this problem? Times Interest Earned Ratio

    ReplyDelete
  46. There is noticeably a bundle to comprehend this. I assume you made particular nice points in functions also. https://royalcbd.com/product/cbd-gummies-10mg/

    ReplyDelete
  47. Hi there, just became aware of your blog through Google, and found that it’s truly informative. I’ll be grateful if you continue this in future. Lots of people will benefit from your writing. Cheers! 'Shyam Advisory'

    ReplyDelete
  48. Oh my goodness! a fantastic post dude. Many thanks However We are experiencing problem with ur rss . Don’t know why Can not sign up to it. Could there be anybody finding identical rss difficulty? Anyone who knows kindly respond. Thnkx 먹튀사이트

    ReplyDelete
  49. Will you care and attention essentially write-up most of the following in my webpage in essence your website mention of this blog? 안전놀이터

    ReplyDelete
  50. Nice read, I just passed this onto a colleague who was doing a little research on that. And he actually bought me lunch as I found it for him smile Thus let me rephrase that: Thanks for lunch! nba중계


    --------------------------------------------


    Great post however , I was wanting to know if you could write a litte more on this subject? I’d be very thankful if you could elaborate a little bit further. Bless you! 스포츠중계

    ReplyDelete
  51. Hi, i think that i saw you visited my blog thus i came to “return the favor”.I am attempting to find things to improve my web site!I suppose its ok to use a few of your ideas!! best business circle

    ReplyDelete
  52. “My wife and i have been quite thrilled that Peter could deal with his researching via the precious recommendations he grabbed from your web site. It’s not at all simplistic to just possibly be giving for free facts which some people have been trying to sell. Therefore we consider we need the blog owner to appreciate for this. The main illustrations you made, the simple blog menu, the friendships your site help to instill – it’s got mostly astounding, and it’s really aiding our son in addition to the family recognize that this idea is brilliant, which is extraordinarily indispensable. Thank you for all!” https://is.gd/eV1TNJ

    ReplyDelete
  53. If your business is doing well in this competitive market, congratulations! You must be doing something right. The question is, is business as good as it could be? A good way to test that theory is to bring in some outside business counsel to scan the landscape and see if there is more you could do to expand and grow even more. Buy Trustpilot Reviews

    ReplyDelete
  54. I received my license and certification as soon as I finished school for massage therapy. I knew right away that I was going to start building my own practice out of my house as soon as I finished school (which is why a prerequisite of the home I was purchasing had to have an extra room for my business space). I built up my clientele and was quickly recognized as one of the top massage businesses in the area. affect vs effect

    ReplyDelete
  55. Online shopping has caught up as a recent trend. Why not? It has made shopping so much easier as now you can shop from the comfort of your home from your laptop or smart phone without going through the hassle of getting ready and going out. Buy away from the wide range of products available to you in your searched category. product review site

    ReplyDelete
  56. Learn about the fantastic and incredible benefits of marketing your business using articles. Article marketing is considered one of the most powerful and cost-effective ways to increase your business sales and profits. Using articles you can not only increase your revenues but also be seen as an expert in your field. In addition, articles are used by savvy business owners to enhance their reputations in their communities. Read this article to learn how article marketing can help your business. https://randomgenerator.biz/

    ReplyDelete
  57. Nowadays, many business owners are conducting their business online. Because they have seen the number of responses they receive from their online audience, business owners have seen this as a huge help for promoting their company. This is why there are so many of them who hire social media marketers who are knowledgeable in this growing trend. But given the fact that there are now so many companies and individuals who offer social media marketing services, it is important to look for the right provider. As such, the 4 principles of social media marketing need to be observed by the chosen online marketer. Công ty thiết kế web

    ReplyDelete
  58. This comment has been removed by the author.

    ReplyDelete
  59. The entrepreneur who really wants to be successful with his or her small business should be ingenious to look for new and innovative ways to advertise products and services to reach a wider market share. It has been found credible by many research studies that it is worth the time of any creative entrepreneur to build his or her business online. Particularly, the Internet has today offered remarkable ways an entrepreneur can build any business online and generate revenue. Architecture

    ReplyDelete
  60. Ghana as a country can boost of considerable number of hardworking women who despite their limitations with formal literacy education, try to make ends meet while providing their families with all the material needs. This is true of these diligent women who support their husbands in tendering the home. There are countless single mothers who have taken upon themselves the dual roles of fathers and mothers, providing the physical and spiritual needs of their children. The greatest contribution of these industrious Ghanaian business women that has not received literary attention is their extraordinary business skills. Corporate Service Provider Dubai

    ReplyDelete
  61. Buy 1 Star Google Reviews The website is looking bit flashy and it catches the visitors eyes. Design is pretty simple and a good user friendly interface. Mangocity IT

    ReplyDelete
  62. MangoCity It .I recently found many useful information in your website especially this blog page. Among the lots of comments on your articles. Thanks for sharing. BUY VERIFIED TRUSTPILOT REVIEWS

    ReplyDelete
  63. I dream of owning my own business and ditching my boss and having enough money to be comfortable. I want to start a business now. What do I do? hours calculator week

    ReplyDelete
  64. I rattling glad to find this website on bing, just what I was looking for : D as well saved to fav. https://letterboxd.com/sbobetlivecasin/

    ReplyDelete
  65. Ready to start your new adventure in business? Here's a starting point to get you on your way. Workshops In New york

    ReplyDelete
  66. You are about to learn about a review tip offered to a local business CEO about who was assassinating their reputation and possibly causing the business to lose profits right from the businesses bottom line, which could possibly be happening in your business? I was going to wait for the CEO to reply after my initial contact but I knew this person was busy so I wanted to give the CEO all the facts up front to make any decisions on in this matter that had to be made. Curtains Blinds & Shutters

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates