Home » Server Options » Replication » near real time replication, staging tables
near real time replication, staging tables [message #45134] Sun, 15 February 2004 06:53 Go to next message
padma
Messages: 22
Registered: April 2001
Junior Member
Hi All,

We have an oracle database that receives data from another system(SQL Server based) through replication. The data is populated into the staging tables in schema A, which do not have any integrity constraints defined on them.

The data from staging tables in schema A, should be synchronized in real time with another set of tables in another schema B, in near real time in the same database.

The tables in schema B implement integrity constraints(pk,fk) etc. There are minor differences in the datamodel. The schema B has few more tables. 

There are around 30,000 records , around 6 or 7 such tables. Few hundred records may change every hour.

One of the major concerns is that the data flow from the source system may not flow into the staging tables in oracle in the same order.

One of the ways we thought of acheiving this is through triggers and also maintaining another table(lets say Load_Queue), to maintain information of records that do not follow integrity constraints. Another process would be set up that tries to push any records from Load_Queue into the target tables schema B on a continous basis.

Is there any better way to acheive the same where,

1) We can load the data in real time?

2) Load only the clean data that adheres to the integrity constraints?

3) Log the bad dat else where?

Any thoughts/ advice is welcome. I am trying to find  better ways to implement this.

Best Regards,

Padma

 

 

 

 
Re: near real time replication, staging tables [message #45156 is a reply to message #45134] Tue, 17 February 2004 11:38 Go to previous message
Nanduri V Rao
Messages: 13
Registered: January 2000
Junior Member
I think the approach you explained of dumping everything into a set of tables that do not have constraints and then with the help of triggers push them into the correct tables should be right. But if you remove bad data from the staging table then you would loose the data that might be valid a little later as an FK might have arrived before its PK. So you got to keep trying on the entire data. But in this case trigger may not help. You need to look for a scheduler.

All this required if you are considering any Messaging technology. If you do consider messaging then you could validate each message as it arrives and segregate the data accordingly. But yet you have to re-validate the bad data as discussed above.

Hope this helps.
Previous Topic: Materialized Views
Next Topic: Oracle remote small database
Goto Forum:
  


Current Time: Sat May 21 13:10:26 CDT 2022