Friday, February 8, 2013

13 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

13 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

 

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