Re: select from remote to update/replace local database

From: Peter J. Schoenster <webmaster_at_rede.com>
Date: Tue, 8 Aug 2000 12:27:47 -0600
Message-Id: <10583.114102@fatcity.com>

On 8 Aug 2000, at 10:47, Charlie Mengler wrote:

> I'm confused.
> Is VPN s/w required to access the "remote" system?
> If yes, then without it NO access is available; not SQL*Net & not
> FTP. If no, then any protocol should work. If your system resides
> at a fixed IP#, then the remote firewall can be configured to all
> access between the two sites using any mutually agreed upon
> protocol.

Yes, seems (hate that word) that no VPN software is available for our server (solaris).

And thanks to Joanne Graham <joanneg_at_grenville.co.uk> for a clearer picture of the snapshot.

I used an Oracle expert to install Oracle on our Solaris box. I'm the guy who is developing the web/db/ connection via Perl scripts and all this is working fine ... no one seems to know about how we will connect to the remote oracle database and the need for VPN was mentioned only after we received username/password, sid and port to connect to the remote oracle and then determined that gee .. we couldn't get in. Oh well. Oddly enough it has now fallen to ME to determine the best way for us to get the data from the remote database. I have passed that question to the guy how very capably and efficiently installed Oracle 8.1.5 (and with the perl modules) on the solaris server (that in my estimation was never properly installed).

Here were my scenarios (from a guy who is still reading chapter 3):

Remote database has a script that runs via cron, executes the sql statements, saves output to files, writes status of output as a code to a file,then ftp's these files to our server (including file with code). We have a cron job on our server that looks for the file with the code (indicating status report) and then loads this data into our database (checking code and also verifying files) or doesn't load it (if code is not correct or files do not seem correct). This is quite easy to setup in Perl. This is my favorite solution.
I read of 'snapshots'/'materialized views' whereby one database could update another database ... seems that the sql statements could reside on the remote database and be activated via oracle's scheduling function. I guess we would have to configure our database to accept these connections from the remote database. This seems good but I keep thinking that the connection could drop halfway ...umm ... but that could trigger a rollback on our database which might solve that problem .. but I'm not sure about this.
We setup an NT box that has the VPN software. We write a script or we use a 'snapshot/materialized view' on the Oracle on this box that then makes the connection the remote database. We then connect to our 'local/remote' database to update our tables. Convoluted, costly ... , who maintains this NT remotely or locally? This is ripe for disaster imho.

If anyone ever wants to know the end of this saga let me know and when I get there (or fall off the mountain) I'll let you know :)

Peter

"Reality is that which, when you stop believing in it, doesn't go Received on Tue Aug 08 2000 - 13:27:47 CDT