Solved

Check VPN connection ORACLE

Posted on 2014-04-26
5
443 Views
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,
Andreja
0
Comment
Question by:andrejaTJ
  • 3
  • 2
5 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.
0
 

Author Comment

by:andrejaTJ
Comment Utility
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...
TX
Andreja
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.
0
 

Author Comment

by:andrejaTJ
Comment Utility
TX for the reply..
This is part of the procedure:

CREATE OR REPLACE PROCEDURE "TADEC_TJ"."UPDATE_SAD_HEADER" (cou_to VARCHAR2, cou_from VARCHAR2)  AS


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);
BEGIN
/* 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'*/
EXECUTE IMMEDIATE 'INSERT INTO TADEC_' || cou_to || '.SAD_HEADER  


etc....
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
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.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

BIND is the most widely used Name Server. A Name Server is the one that translates a site name to it's IP address. There is a new bug in BIND (https://kb.isc.org/article/AA-01272), affecting all versions of BIND 9 from BIND 9.1.0 (inclusive) thro…
We recently endured a series of broadcast storms that caused our ISP to shut us down for brief periods of time. After going through a multitude of tests, we determined that the issue was related to Intel NIC drivers on some new HP desktop computers …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now