For high availability we need database replication, so during fail-over we can switch to standby database. Oracle golden gate provide data replication between data-centers. Replication is mainly depend on last updated time stamp. Oracle golden gate will check transaction time-stamp and replay it in another data-center. But sometime replication doesn't happen properly because of application' defect on updating time stamp.
One or more applications can use same database and they may be locate in different time-zone. If one application is updating data using one time-zone in a active database and another application is updating data with different time-zone in another database, such case replication between these two database will make database in-consistent state.
To find out data in-consistency we can compare one site data table with another site same table. For example we have table name '
customer' in database and data is replicating for this table on two active database A and B . If we want to compare data for this table, we can export customer table from A and B into a new database schema, so data comparison job will not impact the running database.
If the table contain only some configuration data, number of records might be less. But customer table can contain millions of record, therefore comparing data become very time consuming.
We can overcome this problem using oracle '
DBMS_PARALLEL_EXECUTE' package. This will allow you to logically split table using table
rowid and create job for each partition and run them parallel. You can partition table using different columns as well.
Below snapshot is to create job using oracle '
DBMS_PARALLEL_EXECUTE' package.
DECLARE
TASK_NAME VARCHAR2(40) :='A_B_DATA_COMPARE';
UPDATE_STATEMENT CONSTANT VARCHAR2 (200)
:= 'BEGIN DATA_COMP(:start_id, :end_id); END;';
BEGIN
DBMS_PARALLEL_EXECUTE.CREATE_TASK (TASK_NAME);
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID (TASK_NAME => TASK_NAME, TABLE_OWNER => USER , TABLE_NAME => TAB1_NAME , BY_ROW => TRUE , CHUNK_SIZE => 800000 );
DBMS_PARALLEL_EXECUTE.RUN_TASK (TASK_NAME => TASK_NAME , SQL_STMT => C_UPDATE_STATEMENT , LANGUAGE_FLAG => DBMS_SQL.NATIVE , PARALLEL_LEVEL =>64 );
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('FAIL TO RUN JOBS, ERROR CODE:' ||SQLCODE);
RETURN;
END;
You can pass number concurrent jobs, partition size and more importantly which procedure or function it will call with start rowid and end rowid. In the above example DBMS_PARALLEL_EXECUTE will partition customer table by rowid and each partition will contain 800000 record. Then it will call DATA_COMP procedure with a partition start rowid and end rowid. You can select record using rowid and loop through them using oracle cursor.
DECLARE
TASK_NAME VARCHAR2(40) :='A_B_DATA_COMPARE';
UPDATE_STATEMENT CONSTANT VARCHAR2 (200)
:= 'BEGIN DATA_COMP(:start_id, :end_id); END;';
BEGIN
DBMS_PARALLEL_EXECUTE.CREATE_TASK (TASK_NAME);
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID (TASK_NAME => TASK_NAME, TABLE_OWNER => USER , TABLE_NAME => TAB1_NAME , BY_ROW => TRUE , CHUNK_SIZE => 800000 );
DBMS_PARALLEL_EXECUTE.RUN_TASK (TASK_NAME => TASK_NAME , SQL_STMT => C_UPDATE_STATEMENT , LANGUAGE_FLAG => DBMS_SQL.NATIVE , PARALLEL_LEVEL =>64 );
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('FAIL TO RUN JOBS, ERROR CODE:' ||SQLCODE);
RETURN;
END;
This way you compare data more faster way, since it will parallel. Inside the cursor loop you can pull record from another table using primary key and compare the record by each column of A and B database customer table.
You can can get complete oracle PL/SQL example in
Github. I make the script dynamic, so only you need to pass compare two tables and report table name.
