Re: querying an identical database on an alternate server

On Dec 14, 6:28 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> 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.

Thanks for the tip on the typo. Yes, I had meant to put menulink, not menuprod. I deleted and recreated the link properly, and it started working. so my link was created as follows:

CREATE PUBLIC DATABASE LINK menulink
CONNECT TO menuuser
IDENTIFIED BY mnupwd
USING 'prodmenu';

And my tnsnames.ora contains the following on both servers:

prodmenu =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL= TCP)(Host=PROD)(Port= 1521))     (CONNECT_DATA = (SID = menu)(GLOBAL_NAME=prodmenu))   )

testmenu =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL= TCP)(Host=TEST)(Port= 1521))     (CONNECT_DATA = (SID = menu)(GLOBAL_NAME=testmenu))   )

Since my init.ora contains a setting "GLOBAL_NAMES=true", I am assuming that it is grabbing the global name I defined in the tnsnames.ora for the link (in my USING segment of the CREATE DATABASE LINK). Thanks for the advice too. As I said, this was more for me to try and understand the logic, but also because this database very rarely changes. We did not wnt to implement a standby database setup for it (too much overhead), and we could have just exported the data, copied it across to the other server, then imported. The thought was that we could potentially do a SELECT statement for any entries that are in PROD.TABLE1 which are not found in TEST.TABLE1 and do an insert statement. With only 8-10 tables in the database, and perhaps 10-20 new entries per month in "some" of the tables, it could be automated to check periodically, and test server would just update itself whenever it sees new entries in prod server.

Thx,

Steve Received on Mon Dec 17 2007 - 11:12:35 CST