作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)

大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来学习 OGG|Oracle 数据迁移后比对一致性，欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达！

有的时候，我们利用 OGG，DSG，DataX 或者其他数据迁移同步工具将数据从 Oracle 11g 迁移到 Oracle 19c 时，有极小极小的可能会导致源端和目标端两边的数据不一致，比如少个索引，少个约束啥的，需要进一步比对数据一致性。当然有的时候也是为了给领导证明迁移过程中没有数据丢失的一种手段吧，Oracle 的 Oracle GoldenGate Veridata 不仅可用于检查数据的不一致，而且能在数据不一致的情况下进行修复，但是需要付费才可以，实施起来也有点难度，本次先放过，我这里主要就是利用了 Oracle 的 DBLInk 技术，同时连接到一个库下进行 count 查询比对行数不一样的表，下面来一起看看具体的细节，有五种方法供参考。

注意：当然对于不同版本的数据库，尤其是没有打 190716 PSU 的 11.2.0.4 的数据库而言，如果你使用了 DBLInk 的话，那么你的数据库版本要都一样，不一样的话，很频繁的 DBLInk 会导致你的数据库的 SCN 异常耗尽，出现 ORA-19706: invalid SCN 错误。SCN 的异常增长通常来说：每秒最大允许的 16K/32K 增长速率已经足够了，但是不排除由于 BUG，或者人为调整导致 SCN 异常增长过大。特别是后者，比如数据库通过特殊手段强制打开，手工把 SCN 递增得很大。同时 Oracle 的SCN会通过 DBLInk 进行传播。如果 A 库通过 DBLInk 连接到 B 库，如果 A 库的 SCN 高于 B 库的 SCN，那么 B 库就会递增 SCN 到跟 A 库一样，反之如果 A 库的 SCN 低于 B 库的 SCN，那么 A 库的 SCN 会递增到跟B库的 SCN 一样。也就是说，涉及到 DBLInk 进行操作的多个库，它们会将 SCN 同步到这些库中的最大的 SCN。对 DBLInk 依赖很严重的系统可能会导致业务系统问题，严重情况下甚至会宕库，所以不同版本的没有补丁的数据库 DBLink 最好少用。

首先创建 DBLInk

--创建 dblink，从目标库 link 到源库create database link PROD_LINKconnect to system identified by "Oracle123456"      --源库用户密码using 'PROD_LINK';

检查两边对象是否一致，如下存在两个 schema 的表分区，索引及自建类型不一致，并列出源端总个数。

select OWNER,OBJECT_TYPE,count(OBJECT_NAME) from dba_objects@PROD_LINK  where owner in ('PROD_CC','PROD_OP','PROD_CB','CUWB','OUS','CC_GMP')  group by OBJECT_TYPE,ownerminus select OWNER,OBJECT_TYPE,count(OBJECT_NAME) from dba_objects  where owner in ('PROD_CC','PROD_OP','PROD_CB','CUWB','OUS','CC_GMP')  group by OBJECT_TYPE,owner order by 1,3,2;
OWNER                          OBJECT_TYPE             COUNT(OBJECT_NAME)------------------------------ ----------------------- ------------------OUS                            TABLE PARTITION                         17OUS                            TABLE                                  308OUS                            INDEX                                 1848PROD_CC                        TYPE                                     1

着重检查不一致的对象

select OWNER,OBJECT_NAME from dba_objects@PROD_LINK  where owner='OUS' and OBJECT_TYPE='TABLE PARTITION'minus select OWNER,OBJECT_NAME from dba_objects  where owner='OUS' and OBJECT_TYPE='TABLE PARTITION';

下面着重比较两端表行数是否一致。

方法一

1、创建统计表,在目标端使用 SYS 用户创建一张表用于统计行结果 ROW_COUNT_STATS

drop table sys.ROW_COUNT_STATS;CREATE TABLE sys.ROW_COUNT_STATS(SCHEMANAME VARCHAR2(30),TABLENAME VARCHAR2(50),ROW_CNT_SOURCE NUMBER,ROW_CNT_TARGET NUMBER,CNT_DIFF NUMBER);

2、配置 TNS，创建 dblink

--先创建名为 PROD_LINK 的 TNS，然后创建 dblink
PROD_LINK =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.118)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = sourcedb)    )  )
create public database link PROD_LINK connect to system identified by 123456 using 'PROD_LINK';   --这里写源库用户和密码

3、使用 SYS 用户创建 TABLE_LIST 表,将需要比对的用户和表插入新创建的 TABLE_LIST 表。

drop table sys.table_list;create table sys.table_list(schemaname varchar2(30),tablename varchar2(50));
insert into sys.table_list select owner,table_name from dba_tables@PROD_LINK where owner in ('PROD_CC','PROD_OP','PROD_CB','CUWB','OUS','CC_GMP') order by owner;commit;

4、使用下面的命令比对数据是否一致

declare  v_schemaname varchar2(60);  v_tablename  varchar2(60);  v_tarcount   NUMBER(16) := 0;   v_srccount   NUMBER(16) := 0;   v_sql1       varchar2(2000);  v_sql2       varchar2(2000);  v_sql3       varchar2(2000);  v_cntdiff    NUMBER(16) := 0;     cursor cur_tablist is    select SCHEMANAME,TABLENAME from sys.TABLE_LIST;begin  open cur_tablist;  loop    fetch cur_tablist    into v_schemaname,v_tablename;    exit when cur_tablist%notfound;    v_sql1 := 'select count(*) from "'||v_schemaname||'"."'||v_tablename ||'"';    --dbms_output.put_line(v_sql1);    execute immediate v_sql1 into v_tarcount;    v_sql2 := 'select count(*) from "'||v_schemaname||'"."'||v_tablename ||'"@PROD_LINK';    execute immediate v_sql2 into v_srccount;    v_cntdiff :=v_tarcount - v_srccount;    v_sql3 := 'insert into sys.ROW_COUNT_STATS (schemaname,tablename,row_cnt_source,row_cnt_target,cnt_diff) values ('''||upper(v_schemaname)||''','''||v_tablename||''',' || v_srccount || ',' || v_tarcount || ',' || v_cntdiff || ')';      execute immediate v_sql3;   end loop;  close cur_tablist;end;/

完成后，可以在 ROW_COUNT_STATS 表中生成报告，以确定是否存在差异。根据该表的 CNT_DIFF 列结果进行判断，如果是 0，则表示数量一致，如果不为 0，则表示两端数据存在差异。

示例代码如下：

select * from sys.row_count_stats;select * from sys.row_count_stats where CNT_DIFF !=0;
19:26:29 SYS@testogg> select * from sys.row_count_stats where CNT_DIFF !=0;SCHEMANAME                     TABLENAME                                          ROW_CNT_SOURCE ROW_CNT_TARGET   CNT_DIFF------------------------------ -------------------------------------------------- -------------- -------------- ----------PROD_CC                        T_ORIGINAL                                                 871882         871879         -3PROD_OP                        T_SYS_RETRYABLE_TASK                                       372263         372262         -1

方法二

pro_tab_cnt v3.0 存储过程对比数据（需要 SYS 用户执行,已测试，源端和目标端用户需要一样），来源于网友分享。

所有操作均在目标库执行，不影响原库数据，在目标库创建表存放对比结果。

1、创建表 tab_cnt 用于存放结果

drop table tab_cnt purge;create table tab_cnt (owner varchar2(32),tab_name varchar2(50),compare_date date,src_or_tag varchar2(30), table_cnt number);alter  table tab_cnt add primary key(owner,tab_name,src_or_tag);

2、创建存储过程

PROD_LINK 前面已经创建成功，如下 SQL 创建存储过程。

附 pro_tab_cnt v3.0 存储过程

create or replace procedure pro_tab_cnt(v_user in dba_tables.owner%type) is  -- Created : 2022/09/07  -- Purpose : the comparison on table count of the source and target schema  v_tb_cnt1 int;  v_tb_cnt2 int;  v_src varchar2(32);  v_tag varchar2(32);  v_sql varchar2(600);  cursor c_tb_name is    select owner, table_name      from dba_tables@PROD_LINK     where owner = v_user     order by owner, table_name;begin v_src:='source'; v_tag:='target';  /* 目标库不存在的表 */  select count(*)    into v_tb_cnt1    from dba_tables@PROD_LINK a   where not exists (select 1            from dba_tables b           where a.owner = b.owner             and a.table_name = b.table_name)     and a.owner = v_user;  /* 源库不存在的表 */  select count(*)    into v_tb_cnt2    from dba_tables a   where not exists (select 1            from dba_tables@PROD_LINK b           where a.owner = b.owner             and a.table_name = b.table_name)     and a.owner = v_user;  /* 判断 source db 和 target db 表是否一样 */  if (v_tb_cnt1 = 0 and v_tb_cnt2 = 0) then    dbms_output.put_line('The tatles of both source side and target side is equal!');    for i in c_tb_name loop   v_sql := 'insert into TAB_CNT        select /*+parallel(dt,32)*/         '''||i.owner||''' as owner,         '''||i.table_name||''' as tab_name,         sysdate,         '''||v_tag||''' as src_or_tag,         count(1) from ' ||i.owner||'.'|| i.table_name|| ' dt  union all  select /*+parallel(ds,32)*/    '''||i.owner||''' as owner,    '''||i.table_name||'''  as tab_name,    sysdate,    '''||v_src||'''  as src_or_tag,    count(1)     from ' || i.owner || '.'|| i.table_name || '@PROD_LINK ds';       dbms_output.put_line(v_sql);     execute immediate v_sql;     commit;    end loop;    /* source db 存在 target db 不存在的表 */  elsif (v_tb_cnt1 <> 0) then    raise_application_error(-20001,                            'The tables of both source side and target side isn''t equal, please execute the following sql to check: ' ||                            chr(10) ||                            'select owner,table_name from dba_tables@PROD_LINK a where not exists (select 1 from dba_tables b where a.owner=b.owner and a.table_name=b.table_name) and a.owner = ');    /* target db 存在 source db 不存在的表 */  elsif (v_tb_cnt2 <> 0) then    raise_application_error(-20002,                            'The tables of both source side and target side isn''t equal, please execute the following sql to check: ' ||                            chr(10) ||                            'select owner,table_name from dba_tables a where not exists (select 1 from dba_tables@PROD_LINK b where a.owner=b.owner and a.table_name=b.table_name) and a.owner = ');  end if;end;/

3、执行存储过程，查看表结果比对

truncate table tab_cnt;    --对比前清空上次对比结果
set serveroutput onexec pro_tab_cnt('&user');  --对比某个用户的所有表的count
 --查看 count 不同的表,正常情况下这个结果为空。select owner,tab_name,table_cnt from tab_cnt where src_or_tag ='source'               minus                                                                                 select owner,tab_name,table_cnt from tab_cnt where src_or_tag ='target'; 
no rows selected

如果出现类似如下结果说明两端数据不一致，并显示源端的用户名、表名及行数。

select owner,tab_name,table_cnt from tab_cnt where src_or_tag ='source'               17:20:25   2  minus                                                                                 17:20:25   3  select owner,tab_name,table_cnt from tab_cnt where src_or_tag ='target'; 
OWNER                          TAB_NAME                                            TABLE_CNT------------------------------ -------------------------------------------------- ----------PROD_SOP                       T_AUTH_SOFT_CA_TX_HIS                                  321928PROD_SOP                       T_SYS_FILE                                             571055
--如下是目标端实际行数17:20:36 SYS@testogg> select count(*) from PROD_SOP.T_AUTH_SOFT_CA_TX_HIS;
  COUNT(*)----------    321964
17:22:19 SYS@testogg> select count(*) from PROD_SOP.T_SYS_FILE;
  COUNT(*)----------    571076

注意:1、SYSTEM 用户创建存储过程可能会报错，存储过程无效

SYSTEM@testogg> exec pro_tab_cnt('&user');Enter value for user: CC_SZBEGIN pro_tab_cnt('CC_SZ'); END;
      *ERROR at line 1:ORA-06550: line 1, column 7:PLS-00905: object SYSTEM.PRO_TAB_CNT is invalidORA-06550: line 1, column 7:PL/SQL: Statement ignored

2、源端和目标端表个数不一样会报错。

SQL> exec pro_tab_cnt('&user');Enter value for user: PROD_CCBEGIN pro_tab_cnt('PROD_CC'); END;
*ERROR at line 1:ORA-20002: The tables of both source side and target side isn't equal, please execute the following sql to check:select owner,table_name from dba_tables a where not exists (select 1 from dba_tables@PROD_LINK b where a.owner=b.owner and a.table_name=b.table_name) and a.owner = ORA-06512: at "SYS.PRO_TAB_CNT", line 63ORA-06512: at line 1
select owner,table_name from dba_tables a where not exists (select 1 from dba_tables@PROD_LINK b where a.owner=b.owner and a.table_name=b.table_name) and a.owner = 'PROD_CC';
OWNER                          TABLE_NAME------------------------------ --------------------------------------------------------------------------------------------------------------------------------PROD_CC                        SYS_EXPORT_TABLE_01

解决办法：

经查看，目标端是一个用户级别的数据泵导出时创建的表，先将其删除，再尝试。

drop table PROD_CC.SYS_EXPORT_TABLE_01 purge;

SQL> exec pro_tab_cnt('&user');Enter value for user: OUSThe tatles of both source side and target side is equal!
PL/SQL procedure successfully completed.
Elapsed: 00:00:32.48

方法三

使用 Oracle 自带的包过程 DBMS_COMPARISON 比较

该包是 Oracle 提供的包，可用于比较两个数据库中的数据库对象。此包还使您能够聚合数据库对象，以便它们在不同的数据库中保持一致。通常，此包用于在多个数据库共享一个数据库对象的环境中。当同一数据库对象的副本存在于多个数据库中时，该数据库对象是共享数据库对象。多个数据字典视图包含有关与包进行比较的信息。

DBMS_COMPARE 包进行数据验证的具体使用步骤如下。
1）创建比较任务。
2）执行比较任务。
3）手动修复不一致的数据。

此处以 Scott 用户下的 emp 表为例，创建任务的命令如下：

BEGIN    DBMS_COMPARISON.CREATE_COMPARISON(comparison_name => 'SCOTT_EMP_COMPARE',      schema_name => 'SCOTT',      object_name => 'EMP',      dblink_name => 'PROD_LINK'); END; /

上述代码段中的参数说明如下。
·comparison_name：自定义对比名称。
·dblink_name：目标端与源端数据库连接。

带入创建的自定义名称 SCOTT_EMP_COMPARE，执行比较任务，命令如下：

SET SERVEROUTPUT ON 
DECLARE  CONSISTENT   BOOLEAN;  COMPARE_INFO DBMS_COMPARISON.COMPARISON_TYPE;BEGIN  CONSISTENT := DBMS_COMPARISON.COMPARE(COMPARISON_NAME => 'SCOTT_EMP_COMPARE',                                        SCAN_INFO       => COMPARE_INFO,                                        PERFORM_ROW_DIF => TRUE);  DBMS_OUTPUT.PUT_LINE('Scan ID: ' || COMPARE_INFO.SCAN_ID);  IF CONSISTENT = TRUE THEN    DBMS_OUTPUT.PUT_LINE('The table is equivalent');  ELSE    DBMS_OUTPUT.PUT_LINE('Tables are not equivalent… there is data divergence.');    DBMS_OUTPUT.PUT_LINE('Check the dba_comparison and dba_comparison_scan_summary views for locate the differences for compare_id:' ||                         COMPARE_INFO.SCAN_ID);  END IF;END;/
--如果表数据一致则返回如下 equivalentScan ID: 1The table is equivalent
--如果表数据不一致则返回如下 not equivalent 字样Scan ID: 1Tables are not equivalent there is data divergence.Check the dba_comparison and dba_comparison_scan_summary views forlocate the differences for compare_id:2

not equivalent 程序包检测到数据差异，并记录到系统视图 
DBA_COMPARISON、DBA_COMPARISON_SCAN_SUMMARY 和 DBA_COMPARISON_ROW_DIF 
中。接下来查询 DBA_COMPARISON_ROW_DIF，以确认不同步的数据。

select * from DBA_COMPARISON_ROW_DIF;

然后，通过上面返回的 rowid 查询具体的数据，结果所示。

限制：对于包含要比较的数据库对象的数据库，数据库字符集必须相同。
DBMS_COMPARISON 包无法比较以下数据类型的列中的数据：
LONG、LONG RAW、ROWID、UROWID、CLOB、NCLOB、BLOB、BFILE
用户定义的类型（包括 object types, REFs, varrays, and nested tables）
Oracle 提供的类型（包括任何类型、XML 类型、空间类型和媒体类型）

不能比较 Lob 大字段，我这个表刚好有 CLOB，而且还只能单表比较，故很少使用这个方法比较，更多详细信息可查看官方文档：

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_COMPARISON.html#GUID-5197C17C-7197-4AE2-844E-8751D75E5879

方法四

使用 hash 函数进行数据对比

1、源端目标端两边分别创建存放 hash 数据的表

drop table system.get_has_value;
create table system.get_has_value (dbname varchar2(20),owner varchar2(30),table_name varchar2(100),value varchar2(100),error varchar2(2000));

2、创建需要验证的表

drop sequence system.sequence_checkout_table;create sequence system.sequence_checkout_table start with 1 increment by 1 order cycle maxvalue 10 nocache; DROP TABLE SYSTEM.checkout_table;CREATE TABLE SYSTEM.checkout_table as select sys_context('USERENV', 'INSTANCE_NAME') dbnme,owner,table_name, system.sequence_checkout_table.NEXTVAL groupid from dba_tables where owner in ('PROD_CC','PROD_OP','PROD_CB','CUWB','OUS','CC_GMP');

结果显示：

SELECT owner, groupid, COUNT (*)FROM SYSTEM.checkout_table GROUP BY owner, groupid,dbnme Order by owner,groupid;
OWNER                             GROUPID   COUNT(*)------------------------------ ---------- ----------CUWB                                    1          1CUWB                                    6          1OUS                                    1         28OUS                                    2         29PROD_CBMC                               9        115PROD_CBMC                              10        100......

3、创建 hash 函数

grant select on sys.dba_tab_columns to system;drop PROCEDURE SYSTEM.get_hv_of_data;
CREATE OR REPLACE PROCEDURE SYSTEM.get_hv_of_data(avc_owner VARCHAR2,avc_table VARCHAR2)AS   lvc_sql_text    VARCHAR2 (30000);   ln_hash_value   NUMBER;   lvc_error       VARCHAR2 (100);BEGINSELECT   'select /*+parallel(a,25)*/sum(dbms_utility.get_hash_value('|| column_name_path|| ',0,power(2,30))) from '|| owner|| '.'|| table_name || ' a 'INTO  LVC_SQL_TEXT FROM (SELECT  owner,table_name,column_name_path,ROW_NUMBER()OVER  (PARTITION  BY  table_name ORDER BY table_name,curr_level DESC)column_name_path_rankFROM (SELECT owner,table_name,column_name,RANK,LEVEL  AS  curr_level,LTRIM (SYS_CONNECT_BY_PATH  (column_name,  '||''|''||'),'||''|''||')column_name_path FROM(SELECT owner,table_name,'"'  ||  column_name  ||  '"'  column_name,ROW_NUMBER  ()OVER  (PARTITION  BY  table_nameORDER  BY  table_name,  column_name)RANKFROM  dba_tab_columnsWHERE owner=UPPER(avc_owner)AND  table_name  =  UPPER  (avc_table)AND  DATA_TYPE  IN  ('TIMESTAMP(3)','INTERVAL  DAY(3)  TO  SECOND(0)','TIMESTAMP(6)','NVARCHAR2','CHAR','BINARY_DOUBLE','NCHAR','DATE','RAW','TIMESTAMP(6)','VARCHAR2','NUMBER')ORDER  BY  table_name,  column_name)CONNECT  BY table_name = PRIOR table_nameAND  RANK -1 = PRIOR RANK))WHERE column_name_path_rank = 1;EXECUTE IMMEDIATE lvc_sql_text INTO ln_hash_value;lvc_sql_text :='insert into system.get_has_value(owner,table_name,value) values(:x1,:x2,:x3)';EXECUTE IMMEDIATE lvc_sql_text USING avc_owner, avc_table, ln_hash_value;commit;DBMS_OUTPUT.put_line (avc_owner || '.' || avc_table || '  ' || ln_hash_value);EXCEPTION   WHEN NO_DATA_FOUND   THEN      lvc_error := 'NO DATA FOUND';    lvc_sql_text :='insert into system.get_has_value(owner,table_name,error) values(:x1,:x2,:x3)';      EXECUTE IMMEDIATE lvc_sql_text USING avc_owner, avc_table, lvc_error;      commit;   WHEN OTHERS   THEN   lvc_sql_text :='insert into system.get_has_value(owner,table_name,value) values(:x1,:x2,:x3)';      EXECUTE IMMEDIATE lvc_sql_text USING avc_owner, avc_table, SQLERRM;      commit;END;/
vim check_source.sh
datesqlplus system/Oracle<set heading off linesize 170 pagesize 0 feedback off echo off trimout on trimspool on termout off verify offspool source_check_$1.sqlSELECT 'exec system.get_hv_of_data('''|| owner|| ''','''|| table_name|| ''')'   FROM system.checkout_table   WHERE  owner = UPPER('$1')    AND table_name NOT IN (SELECT table_name FROM dba_tables WHERE owner = UPPER('$1') AND iot_type IS NOT NULL)   AND table_name IN (SELECT table_name            FROM (SELECT table_name, COUNT (*)                FROM dba_tab_columns                WHERE owner = UPPER ('$1')                AND DATA_TYPE IN ('TIMESTAMP(3)',                'INTERVAL DAY(3) TO SECOND(0)',                'TIMESTAMP(6)',                'NVARCHAR2',                'CHAR',                'BINARY_DOUBLE',                'NCHAR',                'DATE',                'RAW',                'VARCHAR2',                'NUMBER') GROUP BY table_name                    HAVING COUNT (*) > 0)             )ORDER BY table_name;spool off
set serveroutput on@source_check_$1.sqlexit;EOFdate chmod +x check_source.sh
nohup ./check_source.sh PROD_CC >./source_cd_1.log 2>&1 &nohup ./check_source.sh PROD_OP >./source_cd_1.log 2>&1 &nohup ./check_source.sh OUS >./source_cd_1.log 2>&1 &

4、查看 hash 值

col TABLE_NAME for a30col VALUE for a30select OWNER,TABLE_NAME,VALUE,ERROR from system.get_has_value;
select * from system.get_has_value where VALUE is not null and  rownum<=10;
select * from system.get_has_value where  ERROR is not null;

结果：运行 hash 计算函数脚本，在LINUX环境对所有表进行 hash 计算耗时比较久，空表没有计算出 hash 值，异常的小写表名没有计算出 HASH 值。

SQL> select count(*) from TAB1; COUNT(*)----------         0

方法五

SQL Developer 工具也能够比对两个库的信息，但是比较费时，能凑合使用，这里就不演示了。

主要步骤如下：

先填写两个库的连接信息

选择要比对的用户及对象类型，例如表

然后点击完成，静静地等待比较结果，比较耗时。

全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~

❤️ 欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！

————————————————————————————
公众号：JiekeXu DBA之路
CSDN ：https://blog.csdn.net/JiekeXu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————



Oracle 表碎片检查及整理方案
OGG|Oracle GoldenGate 基础
2021 年公众号历史文章合集整理
2020 年公众号历史文章合集整理
我的 2021 年终总结和 2022 展望
Oracle 19c RAC 遇到的几个问题
利用 OGG 迁移 Oracle11g 到 19C
OGG|Oracle GoldenGate 微服务架构
Oracle 查询表空间使用率超慢问题一则
国产数据库|TiDB 5.4 单机快速安装初体验
Oracle ADG 备库停启维护流程及增量恢复
Linux 环境搭建 MySQL8.0.28 主从同步环境