RE: Advice for dblinks between two prod DBs.

  • From: "Guerra, Abraham J" <AGUERRA@xxxxxxxxx>
  • To: <Rich.Jesse@xxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 22 Nov 2006 08:22:08 -0600

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';


drop database link....;

update sys.props$ 
set value$ = 'NEW_DBNAME'
  where name = 'GLOBAL_DB_NAME';


create database link ....;

Hope it helps,


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jesse, Rich
Sent: Wednesday, November 22, 2006 8:17 AM
To: oracle-l@xxxxxxxxxxxxx
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...



-----Original Message-----
From: Jesse, Rich 
Sent: Tuesday, November 21, 2006 11:23 AM
To: oracle-l@xxxxxxxxxxxxx
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:

- We're launching a new ERP system, which uses Oracle EE (.3
if it comes out soon).

- New ERP is both client/server and n-tier and does not use binds.

- I do not want to use CURSOR_SHARING and all of it's happy caveats.

- With the shared pool pressure I'm seeing in the test ERP DB, I think
the best place for our home-grown apps is another DB.

- Our home-grown apps require access to ERP data via ERP's API (views
and packages in the ERP DB).

- Each home-grown app has it's own schema.

- All of home-grown app SQL is in DB packages (and some procedures and

- I don't think there'll be any user accounts in home-grown app DB.

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.


Other related posts: