0

I came across a very interesting situation today and I'm putting this question to the community in hopes that someone can explain why this happened. I haven't been able to find an explanation in Oracle's documentation.

We have a database link configured in our 12c instance, and assume the link is named database.domain.subdomain.com. This link connects to a 10g instance.

In queries that leverage this link, we use the partial link name: select * from table1@database (omitting .domain.*). This works fine.

A complete rebuild of the remote server and instance was necessary due to a hardware failure. When it was brought back up, we encountered a problem querying the table with the same query select * from table1@database however using the full name instead of partial worked fine: select * from table1@database.domain.subdomain.com

Querying other tables using the partial name was fine as well select * from table2@database

I discovered that there was a session open from before the rebuild. After I terminated this session, select * from table1@database resumed working as expected.

My question is, why did was I unable to query that specific object using the partial dblink name?

  • 1
    You mentioned there was a rebuild of the remote server that the Oracle instance is hosted on. Is there a chance there was a dns entry that pointed all traffic that went to "database" to reroute to "database.domain.subdomain.com" that is no longer there? Can you tnsping database? Can you tnsping database.domain.subdomain.com? Or can you make sure that the Listener config is the same as it was prior to the rebuild? – MguerraTorres Apr 10 '18 at 19:55
1

I had exactly the same problem: migrated Oracle 11.2 database to new installation 12.1 via DataPump. Database links in then 12c only worked when accessed with fully qualified names, but the application had been coded using only their partial names. The problem was the domain of the new database's Global Name was different - see http://www.dba86.com/docs/oracle/12.1/ADMIN/ds_admin.htm#ADMIN029 (search for 'partial' within the dblinks section). I created additional dblinks with fully qualified names using new domain name, by extracting the ddl from the 11.2 database: SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) FROM dba_db_links a; - see excellent hints from Alex Lima https://oraclespin.com/2008/03/28/how-to-extract-the-db-links-ddl-with-the-password and Simon Krenger https://www.krenger.ch/blog/find-password-for-database-link/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.