Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Advice for dblinks between two prod DBs.

RE: Advice for dblinks between two prod DBs.

From: <oracle-l-bounce_at_freelists.org>
Date: Wed, 22 Nov 2006 08:33:03 -0600
Message-ID: <A787F31512A25E4F9782045CFE320C801B8FC042@NHQ1ACCOEX05VS1.corporate.amfam.com>


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.



This transmission may contain confidential, proprietary, or privileged information which is intended solely for use by the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, dissemination, copying or distribution of this transmission or its attachments is strictly prohibited. In addition, unauthorized access to this transmission may violate federal or State law, including the Electronic Communications Privacy Act of 1985. If you have received this transmission in error, please notify the sender immediately by return e-mail and delete the transmission and its attachments.

-----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-l
Received on Wed Nov 22 2006 - 08:33:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US