【原创】DBMS_COMPARISON在实际中…

    在之前的博客中已经做过关于DBMS_COMPARISON的实验，现在在生产库中进行实际应用。实际中的环境如下图所示：

 

生产库中的数据需要通过流复制同步到明细库中去，但是流复制并不是100%可靠的。通过查询同一张表里数据就会发现两个数据库中的数据并不一致。这时就可以利用DBMS_COMPARISON包进行比较和同步。但是这个包的使用限制较多，暂时只能进行单表的全表数据的比较与同步，带过滤条件的数据的比较无法实施。

 

1.数据字典：

表名：TA_COMPARE_LOG（日志表）

该日志表用于记录MANUAL_COMPARE的相关信息。

列名	数据类型	是否可为空	默认值	主键与否	含义
scan_id	NUMBER	not null	0	主键	标识发现数据不一致的扫描
is_equal	NVARCHAR2(1)	not null			表数据对比后是否一致
comparison_name	NVARCHAR2(32)	not null			任务名
object_name	NVARCHAR2(32)	not null			参与对比的表名
current_dif_count	NUMBER	not null	0		不一致数据的行数
compare_date	DATE	not null			执行对比的日期
is_converge	NVARCHAR2(1)	not null	0		是否执行同步
is_recheck	NVARCHAR2(1)	not null	0		是否执行重新比较
is_purge	NVARCHAR2(1)	not null	0		是否清洗比较结果集
compare_owner	NVARCHAR2(32)				执行对比任务的用户
loc_rows_merged	NUMBER	not null	0		本地插入数据行数
rmt_rows_merged	NUMBER	not null	0		远端插入数据行数
loc_rows_deleted	NUMBER	not null	0		本地删除数据行数
rmt_rows_deleted	NUMBER	not null	0		远端删除数据行数
recheck_result	NVARCHAR2(1)	not null	0		重新检查结果

表名：TA_COMPARE_TABLES

该表记录执行对比的用户（批次）和参与对比的表。

TA_COMPARE_TABLES：

列名	数据类型	是否可为空	默认值	主键与否	含义
table_name	NVARCHAR2(32)				每一批参与比较的表名
table_owner	NVARCHAR2(32)				执行比较任务的批次

 

 2. 执行流程

  1、建立比较计划：向TA_COMPARE_TABLES插入要比较的表和表所属批次。

  2、利用create_comparison批量建立每个表的比较任务以备后续调用。  

  3、使用建立的存储过程manual_compare执行比较计划：比较，同步，记录日志...

3.存储过程manual_compare的一些说明

    P_IS_COMPARE               IN VARCHAR2, --是否执行比较，为0则直接进行后续操作。
    P_IS_CONVERGE              IN VARCHAR2, --是否执行同步
    P_IS_RECHECK               IN VARCHAR2, --是否执行重新检查
    P_IS_PURGE                 IN VARCHAR2, --是否执行清洗

主要根据以上四个参数的组合区分不同的操作，逻辑如下：

 

P_IS_COMPARE	P_IS_CONVERGE	P_IS_RECHECK	P_IS_PURGE                 IN	所属情况	处理方式	是否常用
0	0	0	1	上次比较之后无后续操作，此次不需要旧的结果集。	仅进行结果集的清洗。	是
0	1	1	1	上次比较之后无后续操作，此次紧接着上次继续。	同步，检查，清洗。	是
1	0	0	0	仅仅进行比较。	仅仅进行比较。	否
1	0	0	1	仅仅进行比较。	比较后清洗结果。	否
1	1	1	1	正常流程	比较，同步，检查，清洗结果集。	是

表格里所列的都是常用的操作，其余的组合方式可能会导致不可预知的错误。而且P_IS_COMPARE 和P_IS_PURGE是一对关联，P_IS_CONVERGE和P_IS_RECHECK 是一对关联。

4.源码

CREATE OR REPLACE PROCEDURE MANUAL_COMPARE(
    P_IS_COMPARE               IN VARCHAR2, --是否执行比较，为0则直接进行后续操作。
    P_IS_CONVERGE              IN VARCHAR2, --是否执行同步
    P_IS_RECHECK               IN VARCHAR2, --是否执行重新检查
    P_IS_PURGE                 IN VARCHAR2, --是否执行清洗
    P_COMPARE_OWNER            IN VARCHAR2) --执行比较任务的用户
 is
  COMPARE_INFO      DBMS_COMPARISON.COMPARISON_TYPE;
  COMPARE_RETURN    BOOLEAN; --比较过程返回值，'true'表示未发现数据不一致，'false'表示发现数据不一致。
  V_TABLE_NAME      VARCHAR2(32); --参与比较的表名
  V_comparison_name VARCHAR2(32); --比较任务名
  V_SCAN_ID         VARCHAR2(32); --日志表中仅执行了compare的扫描
  IS_EQUAL          VARCHAR2(1); --compare的结果，即表数据是否一致
  MAX_SCANID        NUMBER;
  cursor C_COMPARE_TABLES is
    SELECT TABLE_NAME
      FROM TA_COMPARE_TABLES
     WHERE TABLE_OWNER = P_COMPARE_OWNER;
  --建立游标，获取比较任务
  ---------------------------------------------------------------
  cursor C_COMPARE_SCAN_ID is
    SELECT scan_id, comparison_name
      FROM TA_COMPARE_LOG
     WHERE compare_owner = P_COMPARE_OWNER
       AND is_converge = '0';
  --建立游标，获取仅执行了compare的scan_id
  ---------------------------------------------------------------
  cursor C_PURGE_COMPARE_NAME is
    SELECT comparison_name
      FROM TA_COMPARE_LOG
     WHERE compare_owner = P_COMPARE_OWNER
       AND is_converge = '0';
  --建立游标，获取仅执行了compare的任务，用于只进行清洗的工作。
  ---------------------------------------------------------------
  v_cnt integer; --比较结果集数目，用于判断是否有比较结果集
  --**************************************************************
  --
  --**************************************************************
BEGIN
  --DBMS_OUTPUT.ENABLE ();
  --------------------------------------------------------------------------------
 
  IF P_IS_COMPARE IN ('0') THEN
    --上次compare完后，没有进行后续操作。
 --如果不需要后续操作，则直接清洗结果集。
 --否则继续后续操作。
 IF P_IS_PURGE IN('1') THEN
     OPEN C_PURGE_COMPARE_NAME;
    LOOP
      FETCH C_PURGE_COMPARE_NAME
        INTO V_comparison_name;
      EXIT WHEN C_PURGE_COMPARE_NAME%NOTFOUND;
      DBMS_COMPARISON.PURGE_COMPARISON(V_comparison_name);
      UPDATE TA_COMPARE_LOG
         SET IS_PURGE = '1'
       WHERE COMPARE_OWNER = P_COMPARE_OWNER;
      COMMIT;
    END LOOP;
    CLOSE C_PURGE_COMPARE_NAME;
 ELSE
       OPEN C_COMPARE_SCAN_ID;
    LOOP
      FETCH C_COMPARE_SCAN_ID
        INTO V_SCAN_ID, V_comparison_name;
      EXIT WHEN C_COMPARE_SCAN_ID%NOTFOUND;
      --如果需执行同步（P_IS_CONVERGE='1'），则执行同步操作。然后更新日志表，记录本地和远端插入和删除的数据量。
      IF P_IS_CONVERGE IN ('1') THEN
        DBMS_COMPARISON.CONVERGE(COMPARISON_NAME  => V_comparison_name,
                                 SCAN_ID          => V_SCAN_ID,
                                 CONVERGE_OPTIONS => DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS,
                                 SCAN_INFO        => COMPARE_INFO);
        UPDATE TA_COMPARE_LOG
           SET IS_CONVERGE      = '1',
               LOC_ROWS_MERGED  = COMPARE_INFO.LOC_ROWS_MERGED,
               RMT_ROWS_MERGED  = COMPARE_INFO.RMT_ROWS_MERGED,
               LOC_ROWS_DELETED = COMPARE_INFO.LOC_ROWS_DELETED,
               RMT_ROWS_DELETED = COMPARE_INFO.RMT_ROWS_DELETED
         WHERE SCAN_ID = V_SCAN_ID;
        COMMIT;
        --如果需执行检查比较（P_IS_RECHECK='1'），则执行recheck过程，然后更新日志表的IS_RECHECK和RECHECK_RESULT字段：
        --表数据一致，则IS_RECHECK='1'，RECHECK_RESULT='1'，表示已经recheck且表数据一致，
        --否则IS_RECHECK='1'，RECHECK_RESULT='0'，表示已经recheck但表数据不一致！
        IF P_IS_RECHECK IN ('1') THEN
          COMPARE_RETURN := DBMS_COMPARISON.RECHECK(COMPARISON_NAME => V_comparison_name,
                                                    SCAN_ID         => V_SCAN_ID);
          IF COMPARE_RETURN = TRUE THEN
            UPDATE TA_COMPARE_LOG
               SET IS_RECHECK = '1', RECHECK_RESULT = '1'
             WHERE SCAN_ID = V_SCAN_ID;
            COMMIT;
          ELSE
            UPDATE TA_COMPARE_LOG
               SET IS_RECHECK = '1', RECHECK_RESULT = '0'
             WHERE SCAN_ID = V_SCAN_ID;
            COMMIT;
          END IF;
        END IF;
      END IF;
      --如果需要清洗（P_IS_PURGE='1'），则执行清洗过程。然后更新日志表IS_PURGE='1'，表示已清洗比较结果集。
      IF P_IS_PURGE IN ('1') THEN
        DBMS_COMPARISON.PURGE_COMPARISON(V_comparison_name);
        UPDATE TA_COMPARE_LOG SET IS_PURGE = '1' WHERE SCAN_ID = V_SCAN_ID;
        COMMIT;
      END IF;
    END LOOP;
    CLOSE C_COMPARE_SCAN_ID;
    END IF;
  ELSE
  --执行比较
    OPEN C_COMPARE_TABLES;
    LOOP
      --获取参与对比的表名，没有表参与对比则退出。
      FETCH C_COMPARE_TABLES
        INTO V_TABLE_NAME;
      EXIT WHEN C_COMPARE_TABLES%NOTFOUND;
      BEGIN
        SELECT COUNT(*)
          INTO v_cnt
          FROM DBA_COMPARISON
         WHERE REMOTE_OBJECT_NAME = V_TABLE_NAME;
        IF v_cnt >= 1 THEN
          --建有有比较任务
          SELECT comparison_name
            INTO V_comparison_name
            FROM DBA_COMPARISON
           WHERE REMOTE_OBJECT_NAME = V_TABLE_NAME
             AND ROWNUM <= 1;
          --循环比较某一批每一张表，表数据一致则设置IS_EQUAL := '1'，否则设置为'0'。
          COMPARE_RETURN := DBMS_COMPARISON.COMPARE(COMPARISON_NAME => V_comparison_name,
                                                    SCAN_INFO       => COMPARE_INFO,
                                                    PERFORM_ROW_DIF => TRUE);
          IF COMPARE_RETURN = TRUE THEN
            IS_EQUAL := '1';
          ELSE
            IS_EQUAL := '0';
          END IF;
          --向日志表TA_COMPARE_LOG录入执行比较后的相关记录（详见TA_COMPARE_LOG表的数据字典）
          INSERT INTO TA_COMPARE_LOG
          (scan_id,is_equal,comparison_name,object_name,current_dif_count,compare_date,
           is_converge,is_recheck,is_purge,compare_owner,LOC_ROWS_MERGED,RMT_ROWS_MERGED,
           LOC_ROWS_DELETED,RMT_ROWS_DELETED,RECHECK_RESULT)
          SELECT COMPARE_INFO.SCAN_ID, IS_EQUAL,A.COMPARISON_NAME,A.OBJECT_NAME,Z.CURRENT_DIF_COUNT DIFFERENCE,
                 SYSDATE,'0','0','0', P_COMPARE_OWNER,'0','0','0','0','0'
            FROM DBA_COMPARISON A, DBA_COMPARISON_SCAN_SUMMARY Z
           WHERE A.COMPARISON_NAME = Z.COMPARISON_NAME
             AND A.OWNER = Z.OWNER
             AND Z.SCAN_ID = COMPARE_INFO.SCAN_ID;
          COMMIT;
          --如果需执行同步（P_IS_CONVERGE='1'），则执行同步操作。然后更新日志表，记录本地和远端插入和删除的数据量。
          IF P_IS_CONVERGE IN ('1') THEN
            DBMS_COMPARISON.CONVERGE(COMPARISON_NAME  => V_comparison_name,
                                     SCAN_ID          => COMPARE_INFO.SCAN_ID,
                                     CONVERGE_OPTIONS => DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS,
                                     SCAN_INFO        => COMPARE_INFO);
            UPDATE TA_COMPARE_LOG
               SET IS_CONVERGE      = '1',
                   LOC_ROWS_MERGED  = COMPARE_INFO.LOC_ROWS_MERGED,
                   RMT_ROWS_MERGED  = COMPARE_INFO.RMT_ROWS_MERGED,
                   LOC_ROWS_DELETED = COMPARE_INFO.LOC_ROWS_DELETED,
                   RMT_ROWS_DELETED = COMPARE_INFO.RMT_ROWS_DELETED
             WHERE SCAN_ID = COMPARE_INFO.SCAN_ID;
            COMMIT;
            --如果需执行检查比较（P_IS_RECHECK='1'），则执行recheck过程，然后更新日志表的IS_RECHECK和RECHECK_RESULT字段：
            --表数据一致，则IS_RECHECK='1'，RECHECK_RESULT='1'，表示已经recheck且表数据一致，
            --否则IS_RECHECK='1'，RECHECK_RESULT='0'，表示已经recheck但表数据不一致！
            IF P_IS_RECHECK IN ('1') THEN
              COMPARE_RETURN := DBMS_COMPARISON.RECHECK(COMPARISON_NAME => V_comparison_name,
                                                        SCAN_ID         => COMPARE_INFO.SCAN_ID);
              IF COMPARE_RETURN = TRUE THEN
                UPDATE TA_COMPARE_LOG
                   SET IS_RECHECK = '1', RECHECK_RESULT = '1'
                 WHERE SCAN_ID = COMPARE_INFO.SCAN_ID;
                COMMIT;
              ELSE
                UPDATE TA_COMPARE_LOG
                   SET IS_RECHECK = '1', RECHECK_RESULT = '0'
                 WHERE SCAN_ID = COMPARE_INFO.SCAN_ID;
                COMMIT;
              END IF;
            END IF;
          END IF;
          --如果需要清洗（P_IS_PURGE='1'），则执行清洗过程。然后更新日志表IS_PURGE='1'，表示已清洗比较结果集。
          IF P_IS_PURGE IN ('1') THEN
            DBMS_COMPARISON.PURGE_COMPARISON(V_comparison_name);
            UPDATE TA_COMPARE_LOG
               SET IS_PURGE = '1'
             WHERE SCAN_ID = COMPARE_INFO.SCAN_ID;
            COMMIT;
          END IF;
        END IF;
      EXCEPTION
        WHEN OTHERS THEN
          --DBMS_OUTPUT.PUT_LINE(V_TABLE_NAME || '发生错误!');
          SELECT MAX(scan_id) INTO MAX_SCANID FROM TA_COMPARE_LOG;
           INSERT INTO TA_COMPARE_LOG(scan_id,is_equal,comparison_name,object_name,current_dif_count,compare_date,
           is_converge,is_recheck,is_purge,compare_owner,LOC_ROWS_MERGED,RMT_ROWS_MERGED,
           LOC_ROWS_DELETED,RMT_ROWS_DELETED,RECHECK_RESULT)
          VALUES(MAX_SCANID,'0','',V_TABLE_NAME,0,SYSDATE,
           '0','0','0',P_COMPARE_OWNER,0,0,0,0,'0');
          COMMIT;
      END;
    END LOOP;
    CLOSE C_COMPARE_TABLES;
  END IF;
  --**************************************************************************************************
  --DBMS_OUTPUT.PUT_LINE(P_TABLES );
END MANUAL_COMPARE;