Check VPN connection ORACLE

Hi everyone,
I have two ORACLE 11g Express DB with PL/SQL procedure (job) that replicate the data between over VPN connection. I'm facing with problems with VPN, It is not reliable...
I need kind of check the VPN before execution the PL/SQL procedure...
Can someone help me?!
Thanks in advance,
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
That really doesn't tell me much but it really doesn't have to.

The main thing I would look at is the update of the last replicated date column and make sure that never happens if all the inserts don't work.  That should just be error trapping and some final validation checks.

As long as you either commit or roll back the entire 'batch' and only update the last replicated date if the entire batch is committed and verified, then there really isn't any need to check the VPN status.
slightwv (䄆 Netminder) Commented:
Not sure checking the connection will be good enough.  If the connection isn't stable what happens if it is up when you check up but goes down right after or in the middle of replicating?

How are you replicating the data?

I would set up a fault-tolerant system that ensures all the data makes it while the connection is up and re-transfers data that doesn't when the connection is back up.

We'll need to know more about what you have before we can offer suggestions.
andrejaTJAuthor Commented:
Thank you slightwv,
That's my concern as well. I have a scenario as follow:
1. Table replica - site, date / time
2. The PL/SQL procedure is cheeking the  lats date / time replica from table replica
3. Then INSERT INTO SQL between last date / time replica and sys date/time
4. After replica, the procedure is inserting date/time of last replica

That's in short...
slightwv (䄆 Netminder) Commented:
I hope the procedure captures sysdate first thing and stores it in a variable for the rest of the run and you aren't using sysdate throughout the process.  If so, you might lose some data.

I would just code checks and balances into the procedure before the insert and commit in step #4.

For example:
If the insert in step 3 is one large insert you might have the SQL%ROWCOUNT variable available to show how many rows were inserted.

Then later in the process you can select count(*) from the remote after the commit for the dates involved and check the result against what you inserted.  If the counts are good then the replication seems to have worked and you should be able to perform step 4.

I've done something similar to what I'm going to suggest below when doing 'batch' processing:
Another option is to do away with a 'last replication' date and create your own work-queue table.  A trigger on the base tables can insert a row in the work-queue table that tells the replication process what rows need processing.

Then the procedure can pull a row from the queue table, replicate the data, check that it made it then remove the row from the queue table.  If something happens with the network, the procedure can just exit until the next scheduled run and the row is still in the queue table for the next attempt.

I had a procedure that was scheduled to run every 15 minutes and had a hard-coded limit of 500 for a single batch to keep resource usage to a minimum during peak hours.

The job would run, check for work in the queue and process up to 500 records then exit.  15 minutes later, it would run again every 15 minutes 24/7.

What makes this nice is if days/weeks/months down the road you find a record or 10 missing that didn't make it for whatever reason you can manually insert their data into the queue table and the process will pick them up.  No need for 'special' code to pick up any rows missed.

What also makes this nice, at least for me, is I have a database job that runs every morning that monitors things in my database I feel are important and emails me.  One of the things I check is my queue table.  If I have over 1000 rows in the queue table then something is wrong and my email tells me there is a problem that I need to check on.
andrejaTJAuthor Commented:
TX for the reply..
This is part of the procedure:


l_today_timestamp   TIMESTAMP := SYSTIMESTAMP; -- store the moment the procedure starts in this variable
last_replicated TIMESTAMP;  -- get the last date-time of replication into this variable
-- catch errors if they occure. Errors are written in REPL_ERRORS table.
err_num NUMBER;
err_msg VARCHAR2(100);
/* Get the last replication date from the replica_status table.
The date-time of the last replication is written into the REPLICA_STATUS table associated with the country which the replication is done with and the type of data replicated.
In this case, the record has values: tabela='SAD_HEADER_OUT', sad_repl_cou=other country, date_updated=last replication date time. */
SELECT r.date_updated into last_replicated FROM replica_status r WHERE r.tabela = 'SAD_HEADER_OUT'  and sad_repl_cou=cou_to;

/* insert into sad_header as select all records from sad_header_out where sad_header_out.insert_date is between the last replication date time and the date-time this procedure started.
SAD_HEADER_OUT.insert_date is the date-time when the SAD record was inserted into SAD_HEADER_OUT from the CDPS application.
It also checks that the SAD_HEADER_OUT.sad_repl_cou=the other country.It sets the SAD_HEADER.sad_repl_cou to 'AF'*/

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.