Recently, I helped to follow up an oracle11g upgrade to 19c project, due to the owner is not familiar with oracle upgrade related upgrade process, as well as upgrade the scope of the impact of matters related to the concern that the application downtime after the upgrade will lead to inconsistent business data saved in the database. 🙂↔️
Although we have always emphasized to the owner that upgrade upgrade will only upgrade oracle binary installation files and database system data dictionary related content for upgrade, will not modify the business data, and we are migrating + upgrading will not modify the content of the source library, there are fallback options. 😎
However, the owner indicated that he needed to provide a data validation program, a process that could not be missing, and had no choice but to provide a data comparison program:
- Build 2 DG libraries from production backup sets + archives, library A for upgrading 19C and library B for retaining original data.
- After the upgrade of library A is completed, build DBLINK and library B for data comparison, and query whether the data of library A and library B will be inconsistent after the upgrade.
The following two stored procedures are provided to determine whether there are differences in the data before and after the calibration upgrade:
- diff_plobj_proc: This procedure compares all objects (PL/SQL objects, tables, views, sequences, etc.) of the business user at the source and target.
- diff_row_proc: This procedure compares the data of all the table objects of the business users at the source and target ends to see whether they are consistent, and the difference set is inconsistent if it is not equal to 0.
diff_plobj_proc process code:
-------------------------------- Note: -------------------------------------------- The diff_plobj_proc procedure is recommended to be executed on the target instance (19C) by SYS, system, or a user with the DBA role. Because the diff_plobj_proc procedure logic does not take into account the business user's access to the system table access privileges, the target (19C) creates access to the source (11G). Therefore, the database user who creates the DBLINK on the target side (19C) to access the source side (11G) should have SYSTEM or SYS or DBA privileges to access the DBA_series system tables. To access the DBA_series system attempts. -------------------------------- Note: -------------------------------------------- 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 object comparison table'; COMMENT ON COLUMN diff_plobj_table.source_schema_name IS 'source_schema_name Source business schema name'; COMMENT ON COLUMN diff_plobj_table.source_obj_name IS 'source_obj_type Source object name'; COMMENT ON COLUMN diff_plobj_table.source_obj_type IS 'source_obj_type source_object_type'; COMMENT ON COLUMN diff_plobj_table.source_status IS 'source_status Source object status'; COMMENT ON COLUMN diff_plobj_table.target_schema_name IS 'target_schema_name Target business schema name'; COMMENT ON COLUMN diff_plobj_table.target_obj_name IS 'target_obj_type Target object name'; COMMENT ON COLUMN diff_plobj_table.target_obj_type IS 'target_obj_type target_object_type'; COMMENT ON COLUMN diff_plobj_table.target_status IS 'target_status target_object_status'; COMMENT ON COLUMN diff_plobj_table.proc_exce_time IS 'diff_plobj_proc procedure run time'; -- The diff_plobj_proc process compares the objects of business users in the source and target segments 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 /* Uniformly convert incoming parameters to uppercase*/ 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); -- Define a ref cursor variable to receive the result set from v_sql. v_sql_ref_cursor SYS_REFCURSOR; -- Define a record type to hold the result set returned by 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'; /* Determine whether the incoming v_source_db_link_name is valid, and report an error if it is not.*/ 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 is not working, please check if dblink is available!'); END; /* Dynamic SQL Splicing*/ v_sql := 'SELECT DISTINCT ' || ' 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, ' || ' AS source_status, ' || ' 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, ' || ' 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'; -- Open cursor and execute dynamic SQL OPEN v_sql_ref_cursor FOR v_sql; -- Traversing a Cursor with a LOOP Loop LOOP FETCH v_sql_ref_cursor INTO v_sql_record_row; EXIT WHEN v_sql_ref_cursor%NOTFOUND; -- Insert into diff_plobj_table table INSERT INTO diff_plobj_table VALUES v_sql_record_row; END LOOP; CLOSE v_sql_ref_cursor; COMMIT; END; / -- Execute the stored procedure , my environment Source and target business SCHEMA is not the same, the production environment is the same. BEGIN diff_plobj_proc( p_source_db_link_name => 'SCOTT2_LINK', p_source_schema_name => 'SCOTT2', p_target_schema_name => 'SCOTT3'); END; / -- The diff_plobj_table lookup table gets a breakdown of the objects at the source and target. SELECT * FROM diff_plobj_table; -- Query source and target object data comparison 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 procedure code:
-------------------------------- Note: -------------------------------------------- The diff_row_proc procedure is recommended to be executed on the target instance (19C) by SYS, system, or a user with the DBA role. Because the diff_row_proc procedure logic does not take into account the business user's access to the system table access rights. Therefore, the database user who creates the DBLINK on the target (19C) to access the DBLINK on the source (11G) should have SYSTEM or SYS or DBA privileges to access the DBA_series system tables. To access the DBA_series system attempts. -------------------------------- Note: -------------------------------------------- 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 'Comparison of source and target segment table row data discrepancy table'; COMMENT ON COLUMN diff_row_table.source_schema_name IS 'source_schema_name Source business schema name'; COMMENT ON COLUMN diff_row_table.source_table_name IS 'source_table_name Source table object name'; COMMENT ON COLUMN diff_row_table.source_table_row_total_cnt IS 'source_table_row_total_cnt source_table_object_row_total_cnt'; COMMENT ON COLUMN diff_row_table.target_schema_name IS 'target_schema_name Target business schema name'; COMMENT ON COLUMN diff_row_table.target_table_name IS 'target_table_name Target table object name'; COMMENT ON COLUMN diff_row_table.target_table_row_total_cnt IS 'target_table_row_total_cnt target_table_row_total_cnt target_table_object_row_total_cnt'; COMMENT ON COLUMN diff_row_table.source_target_diff_row IS 'Difference in rows between source and target table comparison by primary key'; COMMENT ON COLUMN diff_row_table.proc_exce_time IS 'diff_row_table procedure run time'; -- Procedure 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); -- Array variable holding the table name of the source table v_source_table_name_arr DBMS_SQL.VARCHAR2_TABLE; -- Array variable holding the name of the table on the target v_target_table_name_arr DBMS_SQL.VARCHAR2_TABLE; -- Get all table name SQL variables of the business user at the source (dynamic SQL) v_source_table_sqlstr VARCHAR2(4000); -- Get all table name SQL variables for business users on the target side (dynamic SQL) v_target_table_sqlstr VARCHAR2(4000); -- Define a ref cursor variable to receive the result set returned by v_source_table_sqlstr. v_source_sql_ref_cursor SYS_REFCURSOR; -- Define a ref cursor variable to receive the result set returned by v_target_table_sqlstr. v_target_sql_ref_cursor SYS_REFCURSOR; -- Define variables to store the number of rows and the result count of the MINUS operation. 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'; /* Determine whether the incoming v_source_db_link_name is valid, and report an error if it is not.*/ 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 is not working, please check if dblink is available!'); END; -- Get business table name from source SQL v_source_table_sqlstr := 'SELECT a.TABLE_NAME FROM DBA_TABLES' || v_source_db_link_name || ' a WHERE = ''' || v_source_schema_name || ''' ORDER BY NUM_ROWS'; DBMS_OUTPUT.PUT_LINE(v_source_table_sqlstr); -- Target get business table name SQL v_target_table_sqlstr := 'SELECT a.TABLE_NAME FROM DBA_TABLES' || ' a WHERE = ''' || v_target_schema_name || ''' ORDER BY NUM_ROWS'; -- Open cursor and execute dynamic 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 to batch insert the result set into an array 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 cursor CLOSE v_source_sql_ref_cursor; CLOSE v_target_sql_ref_cursor; -- Compare table names and perform MINUS operations FOR i IN 1..v_source_table_name_arr.COUNT LOOP v_match_found := FALSE; -- Reset flag 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; -- Get source table rows 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; -- Get the number of table rows on the target v_sql := 'SELECT COUNT(*) FROM ' || v_target_schema_name || '.' || v_target_table_name_arr(j); EXECUTE IMMEDIATE v_sql INTO v_target_row_count; -- Perform MINUS operations and store the results 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; -- Insert results into 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; -- Jumping out of the inner circle EXIT; END IF; END LOOP; -- If a matching table name is not found, output a message and insert a record IF NOT v_match_found THEN DBMS_OUTPUT.PUT_LINE('Source table ' || v_source_table_name_arr(i) || ' has no matching target table'); -- Insert records that do not match the 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, -- Number of source table rows v_target_schema_name, NULL, -- Target Table Name NULL, -- Number of table rows on the target NULL, -- Difference in number of rows between source and target table comparison SYSDATE ); COMMIT; END IF; END LOOP; END diff_row_proc; -- Call diff_row_proc to compare the source and target, this procedure takes a while to run (possibly a long time) BEGIN diff_row_proc( p_source_db_link_name => 'SCOTT2_LINK', p_source_schema_name => 'SCOTT2', p_target_schema_name => 'SCOTT3' ); END; / -- The diff_row_table table can be watched continuously during the -- If SOURCE_TARGET_DIFF_ROW has a value of -999, it means that the source and target tables contain large fields, and you need to manually get the primary key or the unique column for the MINUS to perform a difference set comparison. -- If SOURCE_TARGET_DIFF_ROW has a value of > 0, it indicates that the source and target table data do not match. SELECT * FROM diff_row_table WHERE SOURCE_TARGET_DIFF_ROW <> 0;
Recently, I have been writing c, pl/sql have not written for a long time, the above two processes took my brother more than 3 hours to write out 😅, really pick up a door to lose a door, drunk 😵💫.