RE: Advice for dblinks between two prod DBs.
- From: "Mercadante, Thomas F \(LABOR\)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <AGUERRA@xxxxxxxxx>, <Rich.Jesse@xxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 22 Nov 2006 09:25:22 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Guerra, Abraham J
Sent: Wednesday, November 22, 2006 9:22 AM
To: Rich.Jesse@xxxxxx; oracle-l@xxxxxxxxxxxxx
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@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...
Thoughts?
R2
-----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 10.2.0.2.0 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
functions).
- 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.
TIA!
Rich
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
