| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Advice for dblinks between two prod DBs.
Uhhh.
Drop database link link_name;
-----Original Message-----
From: Guerra, Abraham J [mailto:AGUERRA_at_amfam.com]
Sent: Wednesday, November 22, 2006 9:33 AM
To: Mercadante, Thomas F (LABOR); Rich.Jesse_at_qg.com;
oracle-l_at_freelists.org
Subject: RE: Advice for dblinks between two prod DBs.
How would you delete dblinks that came over when you clone a database with a new name? I would be interested to know...
Abraham
-----Original Message-----
From: Mercadante, Thomas F (LABOR)
[mailto:Thomas.Mercadante_at_labor.state.ny.us]
Sent: Wednesday, November 22, 2006 8:25 AM
To: Guerra, Abraham J; Rich.Jesse_at_qg.com; oracle-l_at_freelists.org
Subject: RE: Advice for dblinks between two prod DBs.
Only a fool would take this advice.
Updating internal tables directly with the advice of Oracle support is asking for a complete database restore.
I would strongly suggest you do not do this.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Guerra, Abraham J
Sent: Wednesday, November 22, 2006 9:22 AM
To: Rich.Jesse_at_qg.com; oracle-l_at_freelists.org
Subject: RE: Advice for dblinks between two prod DBs.
Hello Rich,
Try running this before you drop and recreate your database link:
update sys.props$
set value$ = 'DBNAME_YOU_CLONE_FROM'
where name = 'GLOBAL_DB_NAME';
commit;
drop database link....;
update sys.props$
set value$ = 'NEW_DBNAME'
where name = 'GLOBAL_DB_NAME';
commit;
create database link ....;
Hope it helps,
Abraham
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jesse, Rich
Sent: Wednesday, November 22, 2006 8:17 AM
To: oracle-l_at_freelists.org
Subject: RE: Advice for dblinks between two prod DBs.
While testing, I think I've hit my first hurdle. As of 9i, a DBA can create objects for another user, even if that user does not have privs to (e.g. CREATE TABLE scott.dba_table...). But due to the syntax, I can't seem to be able to do that with a dblink. "CREATE DATABASE LINK scott.mylink..." creates a dblink called "scott.mylink" in the current (DBA) schema. Is there any way to do this without granting CREATE DATABASE LINK to the owner? I really don't want the app owners creating their own dblinks. The next thing that'll happen is a link from test to prod because the data's newer there.
I'm also struggling with GLOBAL_NAMES true/false. If it's needed to be true (and I'm still investigating under what circumstances, if any, that needs to be "true") then what do I do when that DB is cloned from prod to test? Drop/recreate the link and make sure that packages are coded to not use the link name explicitly? Fudge the TNSNAMES.ORA in the test ORACLE_HOME (not my first choice for hopefully obvious reasons)?
Bleah. If I could just manage dblinks like other objects from a DBA account, I think most of my problems would go away...
Thoughts?
R2
-----Original Message-----
From: Jesse, Rich
Sent: Tuesday, November 21, 2006 11:23 AM
To: oracle-l_at_freelists.org
Subject: Advice for dblinks between two prod DBs.
Hey all,
I've been reading through the Distributed Database Concepts part of the 10gR2 Admin Guide to get a better understanding of how to setup dblink(s), which I've avoided since 7.4 (8.0?) when I first messed with them. After a developer inquired about using dblinks for our new ERP, I'm thinking I have a need for them now. Here's my scenario:
I have a million questions about distributed query and transaction performance, but first the setup. I want this to be usable, but flexible. My knee jerk says to create two dblinks for each app account -- one for queries, the other for DML. My main reason for this is security -- each dblink's remote account would need to be explicitly granted access to the specific ERP API views/packages needed. Is this overboard? One dblink for each app? Or one public dblink for all app accounts? <shudder>
I'm also looking for pointers to dblink hints and gotchas, like if I find an icky query running on the remote DB, how do I trace it back to the other DB (and, therefore, to the client)?. My search terms seem to be too generic to turn up anything of much use.
TIA!
Rich
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 22 2006 - 08:35:54 CST
 |
 |