Database replication and consistency check

duplicationminusoracleoracle-10greplication

I got a question about two databases (in Oracle 10g) that I have, let's call them A and B. A have some information (in various tables) and I want B to get a partial copy of some tables from A, and constantly check changes in A and 'sync' them in B.

I want to ask you about some method, technique or maybe ideas, knowing that I can't make any change in A (just selects, no triggers).

I thank you help and patience (for possible edits) in advance.

Additional information:

Thanks for the answers, I don't know if it's relevant, but I found the MINUS operator, though I'm not sure if it works with a "sub table" (select).

Best Answer

Your options are rather limited because of your requirements to "constantly check ... sync" and "can't make any change in A". Things such as materialized view logs, dbms_alert, streams, and a standby database are all off the table.

If the tables in A are constantly having all of their rows updated then (as Jack Douglas said) a materialized view would be the easiest to setup. In the more likely event that most of the records don't change in A from moment to moment, you will probably want to setup a package (or packages) on B that select from A to merge and delete as necessary on B. This will only be as up to date as the frequency in which it is run, but given your requirements it may be the best you can do.

Specifically, your package should do the following:

Delete from B rows that do not exist in A.
Merge A into B updating when matched and inserting when not matched.

If you want to avoid hitting the table in A multiple times you could insert the entirety of the table into a global temporary table on B and then do your Delete/Merge from there.

Concerning Minus: Minus can tell you all the rows from a query of A that are not in B. By union-ing this with B minus query of A you can get all rows that are different, but this would probably take longer to process even before adding the insert/update part. If A doesn't get updates or deletes then you could insert the results of the first minus, but an insert into B...where not exists A... would still be faster and simpler.

Related Solutions

Tracing, debugging and fixing Row Lock Contentions

Would it be possible to find out which line of a stored procedure is causing these row lock contentions?

Not exactly but you can get the SQL statement causing the lock and in turn identify the related lines in the procedure.

SELECT sid, sql_text
FROM v$session s
LEFT JOIN v$sql q ON q.sql_id=s.sql_id
WHERE state = 'WAITING' AND wait_class != 'Idle'
AND event = 'enq: TX - row lock contention';

What would be the general guideline to reduce/avoid/eliminate such problems with coding?

The Oracle Concepts Guide section on locks says, "A row is locked only when modified by a writer." Another session updating the same row will then wait for the first session to COMMIT or ROLLBACK before it can continue. To eliminate the problem you could serialize the users, but here are some things that can reduce the problem perhaps to the level of it not being an issue.

COMMIT more frequently. Every COMMIT releases locks, so if you can do the updates in batches the likelihood of another session needing the same row is reduced.
Make sure you aren't updating any rows without changing their values. For example, UPDATE t1 SET f1=DECODE(f2,’a’,f1+1,f1); should be rewritten as the more selective (read fewer locks) UPDATE t1 SET f1=f1+1 WHERE f2=’a’;. Of course if the changing the statement will still lock the majority of rows in the table then the change will only have a readability benefit.
Make sure you are using sequences rather than locking a table to add one to the highest current value.
Make sure you aren’t using a function that is causing an index to not be used. If the function is necessary consider making it a function based index.
Think in sets. Consider whether a loop running a block of PL/SQL doing updates could be rewritten as a single update statement. If not then perhaps bulk processing could be used with BULK COLLECT ... FORALL.
Reduce the work that gets done between the first UPDATE and the COMMIT. For example, if the code sends an email after each update, consider queuing the emails and sending them after committing the updates.
Design the application to handle waiting by doing a SELECT ... FOR UPDATE NOWAIT or WAIT 2. You can then catch the inability to lock the row and inform the user that another session is modifying the same data.

How to Make MySQL Replication Reliable – InnoDB and MyISAM Tips

OBSERVATION #1

You mentioned Ask developers put all the temporary tables into a separated database

If your developers are using CREATE TEMPORARY TABLE commands to create temporary tables, they need to use CREATE TABLE instead. Here is why:

With MySQL Replication processing a temporary table, this is what occurs

1) Master run CREATE TEMPORARY TABLE
2) Command inserted into binary log
3) Replication copies this over to the Slave's Relay Logs via I/O Thread
4) Slave SQL Thread runs CREATE TEMPORARY TABLE
5) Slave processes data with that temp table

Once in a while, someone may run STOP SLAVE; to run a backup. If STOP SLAVE; is issued just after step 4, the temp created disappears and so does its data. When you run START SLAVE; Replication breaks instantly complaining the table does not exist. This is normal because when a DB Connections terminates deliberately or accidently, all temp tables opened using CREATE TEMPORARY TABLE in the DB session are dropped. Running STOP SLAVE; kill the SQL thread who was holding opening the temp table.

The only workaround for this is to create the table using CREATE TABLE instead of CREATE TEMPORARY TABLE. When run STOP SLAVE;, the temp table you created normally does not disappear.

I have seen this happen maybe 10 times in my DBA career. Fixing it using the binary logs to find out the name of the temp tables, to create those tables using CREATE TABLE, then starting replication up was the only maintenance possible without just brute force copying the master.

OBSERVATION #2

mk-table-sync only works on tables with primary keys and/or unique keys. It works maybe 99% of the time. I have seen instances where the checksum of a table on the master and slave were different. I would run mk-table-sync, there were still differences (Of course, I was doing mk-table-sync in circular replication with 3 masters, which can be a little dangerous. Using it in Master/Slave is far more stable)

OBSERVATION #3

You mentioned There is some unsafe queries. Does it get some problems with MIXED based replication?

It depends. The most popular unsafe query is any UPDATE or DELETE that uses ORDER BY ... LIMIT. With SBR, this could possibly cause MySQL to UPDATE or DELETE rows from a table on the Slave in a different order tham that of the Master. With RBR, I believe the exact changes in a row are more identifiable to UPDATE or DELETE on the Slave.

SOLUTION : Avoid using unsafe queries. Then, you will not worry !!!

OBSERVATION #4

I just read your second link. ROFL !!! I am familiar with the poster of the answer.