Check VPN connection ORACLE

Posted on 2014-04-26
Last Modified: 2014-04-28
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,
Question by:andrejaTJ
  • 3
  • 2
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40025105
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.

Author Comment

ID: 40025266
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...
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40027396
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.

Author Comment

ID: 40027438
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'*/

LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 250 total points
ID: 40027473
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.

Featured Post

Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Creating a Vendor Admin user 23 81
Oracle collections 15 29
Toad 12.10 Enterprise visual interface 4 22
PL/SQL: ORA-00979: not a GROUP BY expression 3 32
Phishing is at the top of most security top 10 efforts you should be pursuing in 2016 and beyond. If you don't have phishing incorporated into your Security Awareness Program yet, now is the time. Phishers, and the scams they use, are only going to …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
After creating this article (, I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
After creating this article (, I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question