Please help, trouble with database links!

[ramaninara]

Let me apologize in advance for the long posting! Somebody recently asked a similar question but no answer yet.

The following is the problem:

-- The server I am using has two 8i databases "Prod" & "Dev".
-- A remote server has a 7.3 database "I".
-- The goal is to create a public database link for "I" from "Prod".

-- The link was working fine until one evening, but stopped the next morning. (ORA-12154: TNS:could not resolve service name). The previous night a routine script was run that shuts down, backs up datafiles & starts up the "Prod" database. This may be the culprit, I am not sure.

1) We are ABLE to create links from "Dev" to "Prod" & "I" and they work fine.
2) We are UNABLE to create WORKING public links from "Prod" to "Dev" or "I" - we can create links, but they just don't work! We are using the sames names for links as the TNS alias & the SID (although global_name is set to false).
3) We don't have privileges to create links in "I" - so that is not an option.
4) We are still ABLE to connect to all 3 databases directly.
5) We have dropped & recreated the links seceral times but no luck.
6) The initsid files (for "Dev" & "Prod") are identical & so are their v$parameter values (except for sid name, controlfile paths, etc).

We have tried recreating TNSNames.ora (using Oracle Net8 Assistant), dropping & recreating the links several times, starting & shutting down the databases, checking for syntax errors in TNSNames.ora, SQLNet.ora, etc.

Could anyone please help as soon as possible?

[darshil_db]

Hi,

U need not recreate tnsnames.ora all time. As far as I understood u didn't give any entry to tnsnames.ora(explicitlly, not through net assist) Whenever u create a link u must enter the remote database's instance name in tnsnames.ora After that u shutdown and restart database.

This is very common problem. I think adding name in tnsnames.ora your problem will be resolved.

Good luck

Darshil