DATABASE LINKS ACCROSS SEVERAL SERVERS

[jayjaybigs]

Hello,

I have several databases on different servers that are currently being assessed by several applications via database link.

My aim is to rebuild the database link periodically as a temporary security measures. Bearing in mind the different servers, how can I rebuild the database links centrally in one location and propagate to different databases on different servers which will be equally propagated to different application servers for login-in into applicable databases.

Has anyone done this before?

Thank you for insight and responses.

jayjay

 

[SantaMufasa]

Yes, JayJay, I to this regularly.

I have a PL/SQL script that reads my current "tnsnames.ora" file, parses out the pertinent database-link information, then recombines the information into a functional command to:

'create database link '||db_link_name||
' connect to SYSTEM identified by '||password||' using '''||SID||''';');

I also have a script that drops all existing database links from the schema in which I am running the "CREATEs..." so that there are not "duplicate" errors.

From past posts, I believe that you have the experience necessary to replicate this task, correct? If not, then post your best attempt out here and we'll offer help/suggestions.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jayjaybigs]

Hello, I have several links in a database.

My object is to:
1)Output each link.
2)drop the link
3)re-create the link with a new password.

Here is my effort:
========================

begin

for r in (select owner,db_link,username,host from dba_db_links where owner = 'PUBLIC')

loop

dbms_output.put_line('Link: '||r.db_link||', host: '||r.host||', username: '||r.username);

execute immediate 'drop public database link '||r.db_link';
execute immedaite 'create public database link '||r.db_link' connect to '||r.username identified by
hello12 using '||r.host'

end loop;
end;
/
However, this is not working, please advise

 

[slaing]

JayJay -

What error messages are you getting ?

Are they when you try to run the script or when you try to use the links that it creates ?

After fixing a few typos in the code you posted, the following works for me :

begin

for r in (select owner,db_link,username,host from dba_db_links where owner = 'PUBLIC')

loop

dbms_output.put_line('Link: '||r.db_link||', host: '||r.host||', username: '||r.username);

execute immediate 'drop public database link '||r.db_link;
execute immediate 'create public database link '||r.db_link||' connect to '||r.username||' identified by mypass using '''||r.host||'''';

end loop;
end;
/

Steve