Re: querying an identical database on an alternate server

On Dec 14, 11:41 am, "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;
>
> 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

Go through this and give us the additional information to help you: http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/ds_admin.htm#24251 http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/ds_admin.htm#sthref4096

jg

--
@home.com is bogus.
http://resources.zdnet.co.uk/articles/features/0,1000002000,39291463,00.htm