Re: ORA-12505

it's hot here wrote:
> Hi,
>
> I am trying to create and use a database link to database A. I create it
> like this:
>
> CREATE PUBLIC DATABASE LINK dev_link
> CONNECT TO <user>
> IDENTIFIED BY <pass>
> USING '<SID>';
>
> where the items in <> are replaced by the actual values. This part is
> OK.
>
> When I try and use dev_link in database B, I get an ORA-12505:
>
> SELECT *
> FROM TABLE_at_DEV_LINK
> *
> ERROR at line 14:
> ORA-06550: line 14, column 10:
> PL/SQL: ORA-04052: error occurred when looking up remote object
> <user>.TABLE_at_DEV_LINK
> ORA-00604: error occurred at recursive SQL level 1
> ORA-12505: TNS:listener could not resolve SID given in connect
> descriptor
> ORA-06550: line 13, column 5:
> PL/SQL: SQL Statement ignored
>
> I've searched for some help to this problem and found not a lot that I
> can understand: this happens because database B does not know about
> database A? If that's the case, how can I make B aware of A? I would
> like to do this with a script, that is, not in a GUI setting. I want
> this to run every day (since database B is refreshed every night)
>
> Thanks,
> Andrew
>
>

When you create the database link, you specify a TNS alias which is stored in your TNSNAMES.ORA config file. There can be many locations for this file, but database links use the config file found in $ORACLE_HOME/network/admin.

You can test this TNS alias by using SQL*Plus on your database server to connect to the remote database as follows:

sqlplus userid/password_at_tnsalias

I would be that you would get the same error.

The ORA-12505 error is telling me that the SID or SERVICE_NAME as defined for the TNS alias is not correct. The Oracle Listener on the remote server does not know about this SID or SERVICE_NAME. So you'll have to make sure that this is correct.

What confuses me is where you stated that you defined the synonym with "USING '<SID>'". It is not the SID that is specified, but rather the TNS alias instead.

And a word of caution...public database links are rarely a good idea. That means that everyone in your database can access the remote database. This may be a security hole.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

-- 
Posted via a free Usenet account from http://www.teranews.com