Re: select from remote to update/replace local database

From: <Heidi_Schmidt_at_gillette.com>
Date: Tue, 8 Aug 2000 14:44:17 -0400
Message-Id: <10583.114109@fatcity.com>

If you go with opening up the VPN access lists this is just a fyi and a suggestion if you feel comfortable teaming up with your systems admin or network admin
is to use the UNIX command snoop
to listen to the session and see what port the request goes out on and what it comes back in on.
It can show you the stages in the connection and at what point it may or may not be failing.

I'd love to hear your solution because I know with each version of sqlnet the networking functionality changes and aspects of how it works changes.

When I dealt with a proxy firewall (circa 1996-7) we had to realize that sqlnet works in a way like ftp protocol. The request goes out on one port number 1521 for sqlnet and port number 21 for ftp but it comes back on a port number higher than 30000

Best of luck and skill.

Heidi

                                                                                           
                    "Peter J.                                                              
                    Schoenster"          To:     Multiple recipients of list ORACLE-L      
                    <webmaster_at_re        <ORACLE-L_at_fatcity.com>                            
                    de.com>              cc:                                               
                    Sent by:             Subject:     Re: select from remote to            
                    root_at_fatcity.        update/replace local database                     
                    com                                                                    
                                                                                           
                                                                                           
                    08/08/00                                                               
                    03:08 PM                                                               
                    Please                                                                 
                    respond to                                                             
                    ORACLE-L

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:44:17 CDT