Matching data from tables in different Oracle databases

Hi Experts,
In a database 11g Standard Edition (no replication)
I need to create a stored procedure to perform the matching of data from tables with the same name but in different instances.
Both bases are updated with the same data by the application, but before courts in communication, drops the database, or server crash, data is NOT sent to the other end when service is restored.

Manually, and with just a table, I can do:
insert into tbl1
select * from tbl1 @ base2
between: 1 and: 2 - DATE fields, with the period to match
minus
select * from tbl1
between: 1 and: 2; - DATE fields, with the period to match

Open in new window

And then the other way around:
insert into tbl1
select * from tbl1 @ base1
between: 1 and: 2 - DATE fields, with the period to match
minus
select * from tbl1
between: 1 and: 2; - DATE fields, with the period to match

Open in new window

The issue is that I have 100 tables, and the process should sweep 100.
Only I do INSERT / UPDATE, depending on whether the records are empty, since the application may have created it, but no data.
You will have an example to follow, or some solution that can do what I need?
Thank you very much.
Regards
carlino70Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I've never tried using MERGE across a database link but it should be possible.

The MERGE statement will match rows based on some key value and either perform an update or insert if necessary.

If you can provide some sample data and expected results we can try to come up with a working model.

The docs on MERGE:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#sthref6542

There is also a lot of examples on MERGE out there if you look around.
0
carlino70Author Commented:
Yes, here it is:
I send a couple of tables for base for simulation.
The table structure is the same in both instances.
I send you the scripts insertion into tables @ base2, only with the values ¿¿previously inserted by the application (only prepares the fields: utcTime, POINTNUMBER, SITEID), the other fields are empty waiting for data from the other base crossed.
I hope you understand.
thank you very much
script-a-5min-tst1.sql
script-a-5min-tst2.sql
inserts-a-5min-tst1-base1.sql
inserts-a-5min-tst2-base1.sql
inserts-a-5min-tst1-base2.sql
inserts-a-5min-tst2-base2.sql
0
slightwv (䄆 Netminder) Commented:
To save me some time, can you explain what you want your final results to be?

Also, what aren't you using replication?

Based on the link below, Basic Replication is included in Standard Edition:
http://www.oracle.com/us/products/database/enterprise-edition/comparisons/index.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

carlino70Author Commented:
Final Results: Both tables, a_5min_tst1@base1 and  a_5min_tst1@base2, must to have the same data.

The data are loaded by this procedure, you should upgrade SITEID field with a value = 0, to differentiate it from what normally loaded

Also, what aren't you using replication?
For now, I understand that not using replication, in fact my tests are based on two test instances, so I do not have real stage Replication.

I see available is a form of replication, try to find out how it really works.

Thanks
0
carlino70Author Commented:
Hi, I confirm that I am NOT using any replication. The same data is sent to each instance individually by the application.
Then I need to create a tool that complete data in both directions, when there is a break in communication between servers.
Thank you!
0
slightwv (䄆 Netminder) Commented:
>>Hi, I confirm that I am NOT using any replication.

I understand this.  My question is: Why aren't you using replication?  Then the application only needs to insert/update one database and the other database is automatically kept in sync.


Back to the question asked:
I haven't gotten time to look at your code yet.  It will take some time to get everything set up and tested.
0
carlino70Author Commented:
ok, no problem.

I have some doubts about the using of Replication Tools, because I need use him only when the data in some tables in both instances are distinct.

Can I apply the replication just for a group of tables, in a period limited of time?,
I can enable replication only when necessary?
Are you refering to Stream Replication?
0
slightwv (䄆 Netminder) Commented:
>>Can I apply the replication just for a group of tables, in a period limited of time?,

No.

>>I can enable replication only when necessary?

No.

>>Are you refering to Stream Replication?

I don't believe Streams Replication is included with Standard Edition.  I'm not sure what 'Basic Replication' allows.  You would need to contact Oracle directly or dig through the docs.

>> because I need use him only when the data in some tables in both instances are distinct.

I'm not following your requirement.  You only want tables synced up 'some of the time'?  Typically you want things in sync or you don't.  Never heard of 'some rows need synced, others do not'.
0
carlino70Author Commented:
To perform this task, I will use native replication tool for Oracle.
0
carlino70Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for carlino70's comment #a39536792

for the following reason:

To perform this task, I will use native replication tool for Oracle.
0
slightwv (䄆 Netminder) Commented:
I suggested replication.  If you are going to use that, you should accept my post as the solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.