最近幫忙跟進個oracle11g upgrade 升級到19c 的專案，由於業主方不太熟悉oracle upgrade相關升級流程，以及升級影響範圍相關的事項，擔心應用停機升級以後會導致資料庫儲存的業務資料不一致。🙂↔️
雖然我們一直跟業主方強調，upgrade 升級只會升級oracle 二進位制安裝檔案和資料庫系統資料字典相關的內容進行升級，不會修改業務資料，而且我們是遷移+升級不會修改源庫內容，有回退方案。😎
但是業主表示需要提供資料驗證的方案，這個流程不能缺，沒辦法只能提供資料對比方案：
- 透過生產的備份集+歸檔搭建2套DG庫，A庫用於升級19C，B庫用於保留原始資料。
- A庫升級完成以後搭建DBLINK和B庫進行資料比對，查詢A庫升級後和B庫資料是否會不一致。
以下提供兩個儲存過程來判斷校驗升級前後資料是否有差異：
- diff_plobj_proc：該儲存過程對比源端、目標端業務使用者所有的物件（PL/SQL物件，表，檢視，序列等等）。
- diff_row_proc：該儲存過程對比源端、目標端業務使用者所有的表物件的資料是否一致，差集如果不等於0為資料不一致。
diff_plobj_proc 過程程式碼：
--------------------------------注意：-------------------------------------------- diff_plobj_proc 過程建議在目標端例項（19C）上SYS、system 或者具有DBA角色的使用者執行。 因為 diff_plobj_proc 儲存過程邏輯沒有考慮到業務使用者對於系統表訪問許可權這塊的訪問， 所以目標端（19C）建立訪問源端（11G）的DBLINK使用的資料庫使用者最好是有SYSTEM 或者 SYS 或者 DBA的許可權， 才能訪問DBA_系列的系統試圖。 --------------------------------注意：-------------------------------------------- drop TABLE diff_plobj_table; CREATE TABLE diff_plobj_table( source_schema_name VARCHAR2(200), source_obj_name VARCHAR2(200) , source_obj_type VARCHAR2(200) , source_status VARCHAR2(200) , target_schema_name VARCHAR2(200) , target_obj_name VARCHAR2(200) , target_obj_type VARCHAR2(200) , target_status VARCHAR2(200), proc_exce_time DATE ); COMMENT ON TABLE diff_plobj_table IS 'plsql物件比對錶'; COMMENT ON COLUMN diff_plobj_table.source_schema_name IS 'source_schema_name 源端業務模式名'; COMMENT ON COLUMN diff_plobj_table.source_obj_name IS 'source_obj_type 源端物件名稱'; COMMENT ON COLUMN diff_plobj_table.source_obj_type IS 'source_obj_type 源端物件型別'; COMMENT ON COLUMN diff_plobj_table.source_status IS 'source_status 源端物件狀態'; COMMENT ON COLUMN diff_plobj_table.target_schema_name IS 'target_schema_name 目標端業務模式名'; COMMENT ON COLUMN diff_plobj_table.target_obj_name IS 'target_obj_type 目標端物件名稱'; COMMENT ON COLUMN diff_plobj_table.target_obj_type IS 'target_obj_type 目標端物件型別'; COMMENT ON COLUMN diff_plobj_table.target_status IS 'target_status 目標端物件狀態'; COMMENT ON COLUMN diff_plobj_table.proc_exce_time IS 'diff_plobj_proc 儲存過程執行的時間'; -- diff_plobj_proc 過程對比源端、目標段的業務使用者的物件 CREATE OR REPLACE PROCEDURE diff_plobj_proc( p_source_db_link_name IN VARCHAR2, p_source_schema_name IN VARCHAR2, p_target_schema_name IN VARCHAR2 ) IS /* 統一將傳進來的引數轉換成大寫 */ v_source_db_link_name VARCHAR2(200) := '@' || UPPER(p_source_db_link_name); v_source_schema_name VARCHAR2(200) := UPPER(p_source_schema_name); v_target_schema_name VARCHAR2(200) := UPPER(p_target_schema_name); v_sql VARCHAR2(4000); v_link_name_flag VARCHAR2(10); -- 定義個 ref 遊標變數用來接收 v_sql 返回的結果集 v_sql_ref_cursor SYS_REFCURSOR; -- 定義個 record 型別用來儲存 v_sql 返回的結果集 TYPE v_sql_record_type IS RECORD ( source_schema_name VARCHAR2(500), source_obj_name VARCHAR2(500), source_obj_type VARCHAR2(500), source_status VARCHAR2(500), target_schema_name VARCHAR2(500), target_obj_name VARCHAR2(500), target_obj_type VARCHAR2(500), target_status VARCHAR2(500), proc_exce_time DATE ); v_sql_record_row v_sql_record_type; BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE diff_plobj_table'; /* 判斷傳入的 v_source_db_link_name 是否有效，如果無效報錯 */ BEGIN v_sql := 'SELECT 1 FROM DUAL' || v_source_db_link_name; EXECUTE IMMEDIATE v_sql INTO v_link_name_flag; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001, v_source_db_link_name || '：dblink無效,請檢查dblink是否可用!'); END; /* 動態 SQL 拼接 */ v_sql := 'SELECT DISTINCT ' || 'a.owner AS source_schema_name, ' || '(CASE ' || ' WHEN a.object_type = ''INDEX'' THEN ' || ' (SELECT LISTAGG(''IDX_'' || COLUMN_NAME, ''_'') WITHIN GROUP (ORDER BY COLUMN_POSITION) ' || ' FROM ALL_IND_COLUMNS aic ' || ' WHERE INDEX_NAME = a.object_name) ' || ' ELSE a.object_name ' || ' END) AS source_obj_name, ' || 'a.object_type AS source_obj_type, ' || 'a.status AS source_status, ' || 'b.owner AS target_schema_name, ' || '(CASE ' || ' WHEN b.object_type = ''INDEX'' THEN ' || ' (SELECT LISTAGG(''IDX_'' || COLUMN_NAME, ''_'') WITHIN GROUP (ORDER BY COLUMN_POSITION) ' || ' FROM ALL_IND_COLUMNS bic ' || ' WHERE INDEX_NAME = b.object_name) ' || ' ELSE b.object_name ' || ' END) AS target_obj_name, ' || 'b.object_type AS target_obj_type, ' || 'b.status AS target_status, ' || 'SYSDATE AS proc_exce_time ' || 'FROM (SELECT owner, object_name, object_type, status FROM DBA_OBJECTS' || v_source_db_link_name || ' WHERE owner = ''' || v_source_schema_name || ''') a ' || 'FULL JOIN (SELECT owner, object_name, object_type, status FROM DBA_OBJECTS WHERE OWNER = ''' || v_target_schema_name || ''') b ' || 'ON a.OBJECT_NAME = b.OBJECT_NAME AND a.OBJECT_TYPE = b.OBJECT_TYPE'; -- 開啟遊標並執行動態 SQL OPEN v_sql_ref_cursor FOR v_sql; -- 使用 LOOP 迴圈遍歷遊標 LOOP FETCH v_sql_ref_cursor INTO v_sql_record_row; EXIT WHEN v_sql_ref_cursor%NOTFOUND; -- 插入 diff_plobj_table 表 INSERT INTO diff_plobj_table VALUES v_sql_record_row; END LOOP; CLOSE v_sql_ref_cursor; COMMIT; END; / -- 執行儲存過程 ，我的環境 源端和目標端的 業務SCHEMA 不一樣，生產環境是一樣的。 BEGIN diff_plobj_proc( p_source_db_link_name => 'SCOTT2_LINK', p_source_schema_name => 'SCOTT2', p_target_schema_name => 'SCOTT3'); END; / -- 查詢表 diff_plobj_table 能獲取源端和目標端的物件明細。 SELECT * FROM diff_plobj_table; -- 查詢源端、目標端物件資料比對 SELECT * FROM (SELECT COUNT(1) source_cnt, source_obj_type FROM diff_plobj_table GROUP BY source_obj_type) so FULL JOIN (SELECT COUNT(1) target_cnt, TARGET_OBJ_TYPE FROM diff_plobj_table GROUP BY TARGET_OBJ_TYPE) tg ON so.SOURCE_OBJ_TYPE = tg.TARGET_OBJ_TYPE ORDER BY 1;
diff_row_proc 過程程式碼：
--------------------------------注意：-------------------------------------------- diff_row_proc 過程建議在目標端例項（19C）上SYS、system 或者具有DBA角色的使用者執行。 因為 diff_row_proc 儲存過程邏輯沒有考慮到業務使用者對於系統表訪問許可權這塊的訪問， 所以目標端（19C）建立訪問源端（11G）的DBLINK使用的資料庫使用者最好是有SYSTEM 或者 SYS 或者 DBA的許可權， 才能訪問DBA_系列的系統試圖。 --------------------------------注意：-------------------------------------------- drop TABLE diff_row_table; CREATE TABLE diff_row_table( source_schema_name VARCHAR2(200), source_table_name VARCHAR2(200) , source_table_row_total_cnt INT, target_schema_name VARCHAR2(200) , target_table_name VARCHAR2(200) , target_table_row_total_cnt INT , source_target_diff_row INT, proc_exce_time DATE ); COMMENT ON TABLE diff_row_table IS '對比源端、目標段錶行資料差異表'; COMMENT ON COLUMN diff_row_table.source_schema_name IS 'source_schema_name 源端業務模式名'; COMMENT ON COLUMN diff_row_table.source_table_name IS 'source_table_name 源端表物件名稱'; COMMENT ON COLUMN diff_row_table.source_table_row_total_cnt IS 'source_table_row_total_cnt 源端表物件行數統計'; COMMENT ON COLUMN diff_row_table.target_schema_name IS 'target_schema_name 目標端業務模式名'; COMMENT ON COLUMN diff_row_table.target_table_name IS 'target_table_name 目標端表物件名稱'; COMMENT ON COLUMN diff_row_table.target_table_row_total_cnt IS 'target_table_row_total_cnt 目標端表物件行數統計'; COMMENT ON COLUMN diff_row_table.source_target_diff_row IS '源端和目標端表對比行數差異，透過主鍵對比'; COMMENT ON COLUMN diff_row_table.proc_exce_time IS 'diff_row_table 儲存過程執行的時間'; -- 儲存過程 diff_row_proc CREATE OR REPLACE PROCEDURE diff_row_proc ( p_source_db_link_name IN VARCHAR2, p_source_schema_name IN VARCHAR2, p_target_schema_name IN VARCHAR2 ) AS v_source_db_link_name VARCHAR2(200) := '@' || UPPER(p_source_db_link_name); v_source_schema_name VARCHAR2(200) := UPPER(p_source_schema_name); v_target_schema_name VARCHAR2(200) := UPPER(p_target_schema_name); v_link_name_flag CHAR; v_sql VARCHAR2(4000); -- 儲存源端表表名的陣列變數 v_source_table_name_arr DBMS_SQL.VARCHAR2_TABLE; -- 儲存目標端表名的陣列變數 v_target_table_name_arr DBMS_SQL.VARCHAR2_TABLE; -- 獲取源端業務使用者所有表名SQL變數（動態SQL） v_source_table_sqlstr VARCHAR2(4000); -- 獲取目標端業務使用者所有表名SQL變數（動態SQL） v_target_table_sqlstr VARCHAR2(4000); -- 定義 ref 遊標變數用來接收 v_source_table_sqlstr 返回的結果集 v_source_sql_ref_cursor SYS_REFCURSOR; -- 定義 ref 遊標變數用來接收 v_target_table_sqlstr 返回的結果集 v_target_sql_ref_cursor SYS_REFCURSOR; -- 定義變數儲存行數和 MINUS 操作的結果計數 v_source_row_count PLS_INTEGER; v_target_row_count PLS_INTEGER; v_diff_count PLS_INTEGER; v_match_found BOOLEAN := FALSE; BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE DIFF_ROW_TABLE'; /* 判斷傳入的 v_source_db_link_name 是否有效，如果無效報錯 */ BEGIN v_sql := 'SELECT 1 FROM DUAL' || v_source_db_link_name; EXECUTE IMMEDIATE v_sql INTO v_link_name_flag; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001, v_source_db_link_name || '：dblink無效,請檢查dblink是否可用!'); END; -- 源端獲取業務表名 SQL v_source_table_sqlstr := 'SELECT a.TABLE_NAME FROM DBA_TABLES' || v_source_db_link_name || ' a WHERE a.owner = ''' || v_source_schema_name || ''' ORDER BY NUM_ROWS'; DBMS_OUTPUT.PUT_LINE(v_source_table_sqlstr); -- 目標端獲取業務表名 SQL v_target_table_sqlstr := 'SELECT a.TABLE_NAME FROM DBA_TABLES' || ' a WHERE a.owner = ''' || v_target_schema_name || ''' ORDER BY NUM_ROWS'; -- 開啟遊標並執行動態 SQL OPEN v_source_sql_ref_cursor FOR v_source_table_sqlstr; OPEN v_target_sql_ref_cursor FOR v_target_table_sqlstr; -- 使用 BULK COLLECT INTO 將結果集批次插入陣列 FETCH v_source_sql_ref_cursor BULK COLLECT INTO v_source_table_name_arr; FETCH v_target_sql_ref_cursor BULK COLLECT INTO v_target_table_name_arr; -- 關閉遊標 CLOSE v_source_sql_ref_cursor; CLOSE v_target_sql_ref_cursor; -- 比較表名並進行 MINUS 操作 FOR i IN 1..v_source_table_name_arr.COUNT LOOP v_match_found := FALSE; -- 重置標誌 FOR j IN 1..v_target_table_name_arr.COUNT LOOP IF v_source_table_name_arr(i) = v_target_table_name_arr(j) THEN v_match_found := TRUE; -- 獲取源端錶行數 v_sql := 'SELECT COUNT(*) FROM ' || v_source_schema_name || '.' || v_source_table_name_arr(i) || v_source_db_link_name; EXECUTE IMMEDIATE v_sql INTO v_source_row_count; -- 獲取目標端錶行數 v_sql := 'SELECT COUNT(*) FROM ' || v_target_schema_name || '.' || v_target_table_name_arr(j); EXECUTE IMMEDIATE v_sql INTO v_target_row_count; -- 進行 MINUS 操作並儲存結果 BEGIN BEGIN v_sql := 'SELECT /*+ PARALLEL(8) */COUNT(*) FROM (' || 'SELECT * FROM ' || v_source_schema_name || '.' || v_source_table_name_arr(i) || v_source_db_link_name || ' MINUS ' || 'SELECT * FROM ' || v_target_schema_name || '.' || v_target_table_name_arr(j) || ')'; EXECUTE IMMEDIATE v_sql INTO v_diff_count; EXCEPTION WHEN OTHERS THEN v_diff_count := -999; END; END; -- 將結果插入 diff_row_table INSERT INTO diff_row_table ( source_schema_name, source_table_name, source_table_row_total_cnt, target_schema_name, target_table_name, target_table_row_total_cnt, source_target_diff_row, proc_exce_time ) VALUES ( v_source_schema_name, v_source_table_name_arr(i), v_source_row_count, v_target_schema_name, v_target_table_name_arr(j), v_target_row_count, v_diff_count, SYSDATE ); COMMIT; -- 跳出內層迴圈 EXIT; END IF; END LOOP; -- 如果未找到匹配的表名，輸出提示資訊並插入記錄 IF NOT v_match_found THEN DBMS_OUTPUT.PUT_LINE('Source table ' || v_source_table_name_arr(i) || ' has no matching target table'); -- 插入沒有匹配表的記錄 INSERT INTO diff_row_table ( source_schema_name, source_table_name, source_table_row_total_cnt, target_schema_name, target_table_name, target_table_row_total_cnt, source_target_diff_row, proc_exce_time ) VALUES ( v_source_schema_name, v_source_table_name_arr(i), NULL, -- 源端錶行數 v_target_schema_name, NULL, -- 目標端表名 NULL, -- 目標端錶行數 NULL, -- 源端和目標端表對比行數差異 SYSDATE ); COMMIT; END IF; END LOOP; END diff_row_proc; -- 呼叫 diff_row_proc 比對源端和目標端的資料 ，該儲存過程需要跑一段時間（可能很久） BEGIN diff_row_proc( p_source_db_link_name => 'SCOTT2_LINK', p_source_schema_name => 'SCOTT2', p_target_schema_name => 'SCOTT3' ); END; / -- 期間可以持續觀察 diff_row_table 表 -- 如果 SOURCE_TARGET_DIFF_ROW 有 -999 的值，則表示源端表和目標端表包含大欄位，需要手工獲取主鍵或者唯一列進行MINUS進行差集比對。 -- 如果 SOURCE_TARGET_DIFF_ROW 有 > 0 的值，則表示源端表和目標端表資料不一致。 SELECT * FROM diff_row_table WHERE SOURCE_TARGET_DIFF_ROW <> 0;
最近一直在寫c，pl/sql 也好久沒寫了，上面倆過程花了哥3個多小時才寫出來😅，真的是撿一門丟一門，醉了😵💫。