Friday, February 8, 2013

21 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

21 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

 

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