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

Home -> Community -> Usenet -> c.d.o.server -> Re: querying an identical database on an alternate server

Re: querying an identical database on an alternate server

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: Fri, 14 Dec 2007 15:28:05 -0800 (PST)
Message-ID: <17806f78-a598-44f8-9478-b93fbd13f243@t1g2000pra.googlegroups.com>


On Dec 14, 1:41 pm, "steven_nospam at Yahoo! Canada" <steven_nos..._at_yahoo.ca> wrote:
> Hi All,
>
> I am confused about using database links and need a little help. I am
> using Oracle 9.2.0.8 on AIX platform, and unfortunately, I am much
> more familiar with Informix configurations.
>
> Here is my situation:
>
> 1) I have two hosts (AIX servers): PROD and TEST
> 2) On each server, I have a very small database/instance called
> MENU_at_INST01.
>
> The MENU database contains tables that are used by our startup
> profiles to create initial menus that users will see when they log in,
> instead of a shell prompt. The menus determine what other databases
> they are allowed to access using a proprietary software.
>
> My problem is that over time, I occasionally (on demand) want to
> refresh the MENU database that is on the TEST server. Now I have a
> variety of options, including setting up a logical standby database,
> or exporting the tables from PROD and then importing them to TEST.
>
> However, one of the things I wanted to try was to use a PUBLIC
> DATABASE LINK. My weakness in this is the correct setup of the
> listener.ora and tnsnames.ora files.
>
> I tried to use the following:
>
> CREATE PUBLIC DATABASE LINK menulink
> CONNECT TO menuuser
> IDENTIFIED BY mnupwd
> USING 'prodmenu';
>
> The username "menuuser" and "mnupwd" are valid $ORAUSER and $ORAPASSWD
> on both servers. However, when I tried to perform a select statement:
>
> SELECT * FROM TAB_USERS_at_prodmenu;

If you created the database link with the name menulink then you need to connect to it with @menulink not @menuprod.

With 2 different databases you might want a 2 links name menutest and menuprod ... etc.

>
> I get the ORA-02019 error saying that the "prodmenu" remote database
> cannot be found. So I made a few changes in my tnsnames.ora file,
> since I think it needs to be able to loacte that "prodmenu" name. Here
> is what I added:

Your select statement needs to reference the link name ( not necessarily a public link opinions vary ) not the tnsnames entry.

The connect to on the create link statement is where the identication of a tnsnames or some other way of doing oracle names comes in.

>
> #######################################
> $ORACLE_HOME/network/admin/tnsnames.ora
> #######################################
> menu =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL= TCP)(HOST=test)(PORT= 1521))
> (CONNECT_DATA = (SID = INST01))
> )
>
> prodmenu =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL= TCP)(Host=prod)(Port= 1521))
> (CONNECT_DATA = (SID = INST01))
> )
>
> After setting that up, I instead was getting ORA-02085 errors, which
> says "database link MENULINK connects to MENU". It almost sounds like
> it was complaining that it found the database on the current server
> and not on the remote server.
>
> Can anyone give me an example of the "proper" structure of the files,
> or point me in the right direction? I have been reviewing the docs on
> tahiti.oracle.com, but can't seem to get the ideal info since there is
> so much in different manuals, and a lot of it discusses Oracle under
> Windows or automated configs through Network Config Asst instead of
> manually-created. Sometimes info overload is not a good thing! ;-)
>
> Thx in advance.
>
> Steve

Once you get something working for you document it and think about setting standards for your organization.

Database links can be useful but can also become a real problem if the proper usage of this technology is not evaluated. Doing table joins over a database link between local and remote ... not something that performs very well unless you are very careful. Received on Fri Dec 14 2007 - 17:28:05 CST

Original text of this message

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