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: Juan Carlos Reyes Pacheco <juancarlosreyesp_at_gmail.com>
Date: Thu, 23 Nov 2006 13:52:19 -0400
Message-ID: <cd4305c10611230952j649d0fc7sed0dabd2dbc9db45@mail.gmail.com>


Hi Abraham
Why don't you try

Alter database rename global_name to GLOBAL_DB_NAME;

After migratin a 9i, one thing we had to do is change the global name and when creating a database link, include the .WORLD extension, for example REMOTE.WORLD
And in the sqlnet.ora file, add the line names.default_domain = world

On 11/22/06, Guerra, Abraham J <AGUERRA_at_amfam.com> wrote:
>
> 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:
>
> - 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
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
----------------------------------------
http://www.oracleboliviaspecialists.com/

Oracle Certified Profesional 9i 10g
Oracle Certified Professional Developer 6i

10 years of experience from Oracle 7 to Oracle10g and developer 6i

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 23 2006 - 11:52:19 CST

Original text of this message

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