querying an identical database on an alternate server

From: steven_nospam at Yahoo! Canada <steven_nospam_at_yahoo.ca>
Date: Fri, 14 Dec 2007 11:41:50 -0800 (PST)
Message-ID: <274934a4-acaf-4fba-b4ab-e6c2ea06f464@s19g2000prg.googlegroups.com>

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:

I have two hosts (AIX servers): PROD and TEST
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;

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:

#######################################

$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 Received on Fri Dec 14 2007 - 13:41:50 CST