有的時候，我們利用 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
墨天輪：http://m.sunline.cc/u/4347
騰訊云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————
最后修改時間：2022-09-08 14:10:55
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
