Compare content of databases in Oracle

Question

we have a request to build an ASP.Net application that will be used as interface to launch commands on Oracle databases.

We need to find a way to compare the content of 2 databases. We prefer not to code this process. instead we would like to rely on Oracle to do it (we would like to call Oracle commands).

What is the best approach for this?

We are using Oracle 10.x

any help will be appreciated

Answer 1

This is what you could do

• for each table in each database in each environment add a column called LAST_MODIFIED_DATE TIMESTAMP(6)
• add a before insert or update trigger to set LAST_MODIFIED_DATE=CURRENT_TIMESTAMP for each table
• be sure the timezone of each database is correctly set in the NLS Parameters or you will be getting odd answers. I assume each database is in the same timezone
• create a table in a logging schema to record the list of changed values, something along the lines of CREATE TABLE delta_changes ( P_KEY NUMBER(20) table_name VARCHAR2 (250), id NUMBER(10), change_date TIMESTAMP(6), logging_date TIMESTAMP(6) DEFAULT CURRENT TIMESTAMP(6));
• and create a sequence for the primary key and a pre insert trigger on delta_changes
• create a database link between the two databases
• create a packaged procedure along these lines
• create a job to run the packaged procedure on a schedule of your choice

PROCEDURE get_changes(table_name_in := NULL) is

CURSOR all_changes is SELECT table_name FROM dba_tables WHERE owner = your_schema; v_execute VARCHAR2(1000); BEGIN

IF table_name_in IS NULL THEN

FOR EACH t_name in all_changes LOOP
v_execute := 'INSERT INTO delta_changes (table_name,id,change_date,logging_date) VALUES ('||t_name.table_name||',(SELECT ID, LAST_MODIFIED_DATE, CURRENT_TIMESTAMP FROM '||t_name.table_name||', '||t_name.table_name||'@TheOtherDatabase WHERE '||t_name.table_name||'.primary_key = '||t_name.table_name||'.primary_key||'@TheOtherDatabase and t_name.table_name.LAST_MODIFIED_DATE > t_name.table_name.LAST_MODIFIED_DATE@TheOtherDatabase;'; EXECUTE IMMEDIATE v_execute;
END LOOP;
ELSE
v_execute :='INSERT INTO delta_changes (table_name,id,change_date,logging_date) VALUES ('||table_name_in||',(SELECT ID, LAST_MODIFIED_DATE, CURRENT_TIMESTAMP FROM '||table_name_in||', '||table_name_in||'@TheOtherDatabase WHERE '||table_name_in||'.primary_key = '||t_name.table_name||'.primary_key'||'@TheOtherDatabase and t_name.table_name.LAST_MODIFIED_DATE > t_name.table_name.LAST_MODIFIED_DATE@TheOtherDatabase;';
END IF;
--add exception handling here-log all errors to another table and RAISE error again
END GET_CHANGES;

This solution has a number of ways it could be improved such as:

• use bind variables
• which users should have execute on this packaged procedure?
• or use Oracle advanced queues to log the changes on one database and then extract them to compare on the other
• please excuse my syntax as I'm doing this by memory

Answer 2

Here is a brute force example that doesn't require modifications to the tables. It merely gives you information about what is different, it does not do anything to synchronize it.

Sample data:

--DROP TABLE tableA;
--DROP TABLE tableB;

CREATE TABLE tableA AS (
   SELECT level-1 ID, chr(level-1+70) bb, chr(level-1+100) cc FROM dual CONNECT BY level<=4
);

CREATE TABLE tableB AS (
   SELECT level ID, chr(level+70) data1, chr(level+100) cc FROM dual
   UNION ALL
   SELECT level+2 ID, chr(level+70) data1, chr(level+100) cc FROM dual CONNECT BY level<=3
);

SELECT * FROM tableA;
SELECT * FROM tableB;

Compare:

WITH UnionedRows AS
(
   SELECT * FROM tableA
   UNION
   SELECT * FROM tableB
)
SELECT ID, sum(MyCount),
   case
      when sum(MyCount) = 111 then 'In Table A and Table B - Identical.'
      when sum(MyCount) =  11 then 'In Table A.'
      when sum(MyCount) = 112 then 'In Table A and Table B - Different.'
      when sum(MyCount) = 101 then 'In Table B.'
      else 'Error.'
   end Status
FROM
(
   SELECT ID, count(*) MyCount FROM UnionedRows GROUP BY ID
   UNION ALL
   SELECT ID, 10 FROM tableA
   UNION ALL
   SELECT ID, 100 FROM tableB
) GROUP BY ID ORDER BY ID;

You will need to modify this code to use your primary keys, table names and database links, but the nice thing is that you don't need to list all the column names for every table.

Answer 3

Compare schema (link1, link2) or compare data?