DSG ORACLE复杂软件比对步骤.doc

DSG软件比对步骤一、 安装步骤均在目标端操作1.2 dsg用户需要的权限-all privileges - 1 Role for DSG GRANT DBA TO DSG; ALTER USER DSG DEFAULT ROLE ALL; - 4 System Privileges for DSG GRANT EXECUTE ANY PROCEDURE TO DSG; GRANT SELECT ANY DICTIONARY TO DSG; GRANT SELECT ANY SEQUENCE TO DSG; GRANT SELECT ANY TABLE TO DSG;1.3 创建DBLINK，连接生产库-for create dblink DROP DATABASE LINK DBVERIFY;CREATE DATABASE LINK DBVERIFY CONNECT TO DSG IDENTIFIED BY USING tnsnames;1.4 创建比对使用的临时表-for create check table;CREATE TABLE DSG.DSG_NO_SUP -不支持的表( OWNER VARCHAR2(10 BYTE), TABLE_NAME VARCHAR2(50 BYTE), NO_SUP_TYPE VARCHAR2(20 BYTE) ;- Create tablecreate table DSG_CHECK_TABLE -当前比对的表( DS_OWNER VARCHAR2(50) not null, DT_OWNER VARCHAR2(50), DS_NAME VARCHAR2(50) not null, DT_NAME VARCHAR2(50), TABLE_PARTITION VARCHAR2(50) not null, SYNONYM_NO NUMBER default 0, CHECK_TYPE VARCHAR2(20), DS_COUNT NUMBER, DT_COUNT NUMBER, MINUS_COUNT NUMBER, SEQ NUMBER, STATUS NUMBER, START_DIFF DATE, MINUS_DIFF DATE, DS_COUNT_DIFF DATE, DT_COUNT_DIFF DATE, ERR_MSG VARCHAR2(500), CHECK_SQL VARCHAR2(4000);- Create/Recreate primary, unique and foreign key constraints alter table DSG_CHECK_TABLE add constraint PK_DSG_CHECK_TABLE primary key (DS_OWNER, DS_NAME, TABLE_PARTITION);- Create table for get all check his ,but it is only once timecreate table DSG_CHECK_HIS -历史比对的表( DS_OWNER VARCHAR2(50) not null, DT_OWNER VARCHAR2(50), DS_NAME VARCHAR2(50) not null, DT_NAME VARCHAR2(50), TABLE_PARTITION VARCHAR2(50), SYNONYM_NO NUMBER, CHECK_TYPE VARCHAR2(20), DS_COUNT NUMBER, DT_COUNT NUMBER, MINUS_COUNT NUMBER, SEQ NUMBER, STATUS NUMBER, START_DIFF DATE, MINUS_DIFF DATE, DS_COUNT_DIFF DATE, DT_COUNT_DIFF DATE, ERR_MSG VARCHAR2(500), CHECK_SQL VARCHAR2(4000);创建存储过程见pac_check_owner.pck二、 比对软件描述2.1 初始化比对对象，包括表，对象（存储过程，函数，包等等）PROCEDURE INIT_CHECK_OWNER(DS_OWNER IN VARCHAR2, DT_OWNER IN VARCHAR2); -比对单个表,首先count比对，如果两边的count值一致，然后再进行minus2.2 初始化最后一次比对的表 PROCEDURE INIT_CHECK_DATA; 2.3 比对单表 在dsg_check_table里面status=2的是有问题的 PROCEDURE CHECK_TABLE(P_DS_OWNER IN VARCHAR2, P_DT_OWNER IN VARCHAR2, P_TABLE_NAME IN VARCHAR2, P_TABLE_TYPE IN NUMBER, P_CHECK_TYPE IN NUMBER);2.4 对比两边分区表 PROCEDURE CHECK_TAB_PARTITION(P_DS_OWNER IN VARCHAR2, P_DT_OWNER IN VARCHAR2, P_TABLE_NAME IN VARCHAR2, P_PART_NAME IN VARCHAR2, P_CHECK_TYPE IN NUMBER);2.5 发起数据比对 PROCEDURE CHECK_START(P_TYPE NUMBER DEFAULT 0 -0 IS ALL,1 IS SIMPLE );2.6 停止数据比对,status = 3 where status= 0 PROCEDURE CHECK_STOP;2.7 重新启动并发，断点序传,status=0 where status = 3 PROCEDURE CHECK_CONTINUE;2.8 比对结束,打印比对统计信息 PROCEDURE CHECK_SUMMARY_PRINT;2.9 发起比对对象 PROCEDURE CHECK_OBJECTS;2.10 找出不支持的表 PROCEDURE CHECK_NO_SUP(P_OWNER IN VARCHAR2);2.11 找出所有的job，以备可以手工处理 PROCEDURE GET_ALL_JOB(P_OWNER IN VARCHAR2);2.12 找出所有的lob数据 PROCEDURE SHOW_ALL_LOB_TAB;2.13 发起比对两边索引 - PROCEDURE CHECK_INDEX;2.14 重建unusable索引，enable外健 PROCEDURE REPAIR_OBJ;2.15 编译所有的对象 PROCEDURE RECOMPILE_OBJ;2.16 打印出所有数据的大小，包括数据，索引，lob数据，lob索引 - PROCEDURE SHOW_CHECK_TAB_SIZE;2.17 找出所有含有chained_rows的表 PROCEDURE FIND_CHAINED_ROW;三、 比对步骤3.1 初始化比对用户SQL exec pac_check_owner.INIT_CHECK_OWNER(AMBER,AMBER);PL/SQL procedure successfully completedExecuted in 0.672 seconds3.2 发起数据比对（可以打开多个窗口，并发比对）SQL exec pac_check_owner.CHECK_START;PL/SQL procedure successfully completedExecuted in 33.828 seconds3.3检查比对结果SQL set serveroutput onSQL exec pac_check_owner.CHECK_SUMMARY_PRINT;= TYPE VALUE_OK VALUE_ERRVALUE_NONE TABLE 408 9 0 SEQUENCE 0 0 1Other is ok=PL/SQL procedure successfully completedExecuted in 0 seconds3.4查找比对出错记录SQL select ds_owner,ds_name,table_partition from dsg_check_table where status = 2;DS_OWNER DS_NAME TABLE_PARTITION-AMBER INDY_AUDITOR_WEBACTION P200809AMBER MLOG$_INDY_USER_ACTION NORMALAMBER MLOG$_SP_ISSUE NORMALAMBER MLOG$_SP_PROCESS NORMALAMBER MLOG$_SP_PROCESS_OPER NORMALAMBER SP_PRO