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:
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
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 matchinsert 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 matchOnly 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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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!
Then I need to create a tool that complete data in both directions, when there is a break in communication between servers.
Thank you!
>>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.
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.
ASKER
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?
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?
>>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'.
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'.
ASKER
To perform this task, I will use native replication tool for Oracle.
ASKER
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.
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.
I suggested replication. If you are going to use that, you should accept my post as the solution.
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.