40 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

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)))'

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

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

 

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

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;

 

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

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';

 

 

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

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