有的时候，我们利用 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_LINK
connect 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,owner
minus
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 17
OUS TABLE 308
OUS INDEX 1848
PROD_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 -3
PROD_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/06/10
-- 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 = <user>');
/* 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 = <user>');
end if;
end;
/
3、执行存储过程，查看表结果比对
truncate table tab_cnt; --对比前清空上次对比结果
set serveroutput on
exec 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 321928
PROD_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_SZ
BEGIN pro_tab_cnt('CC_SZ'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SYSTEM.PRO_TAB_CNT is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
2、源端和目标端表个数不一样会报错。
SQL> exec pro_tab_cnt('&user');
Enter value for user: PROD_CC
BEGIN 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 = <user>
ORA-06512: at "SYS.PRO_TAB_CNT", line 63
ORA-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: OUS
The 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;
/
--如果表数据一致则返回如下 equivalent
Scan ID: 1
The table is equivalent
--如果表数据不一致则返回如下 not equivalent 字样
Scan ID: 1
Tables 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 1
CUWB 6 1
OUS 1 28
OUS 2 29
PROD_CBMC 9 115
PROD_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);
BEGIN
SELECT '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_rank
FROM (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_name
ORDER BY table_name, column_name)
RANK
FROM dba_tab_columns
WHERE 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_name
AND 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
date
sqlplus system/Oracle<<EOF
set heading off linesize 170 pagesize 0 feedback off echo off trimout on trimspool on termout off verify off
spool source_check_$1.sql
SELECT '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.sql
exit;
EOF
date
chmod +x check_source.sh
nohup ./check_source.sh PROD_CC >./source_cd_1.log 2>&1 &
nohup ./check_source.sh PROD_CB >./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 &
nohup ./check_source.sh CUWB >./source_cd_1.log 2>&1 &
4、查看 hash 值
col TABLE_NAME for a30
col VALUE for a30
select 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
————————————————————————————
最后修改时间：2022-09-08 14:10:55
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
