Oracle 9i升级19C 逻辑迁移详细方法（二）

# 目标端同步sequence -- 中间服务器(XX)执行，按schema逐个生成 cd /home/oracle/oracle_work export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK sqlplus XXX/XXXi@XX:1521/XX 检查dblink，确认9i的dblink指向DG备端： alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; --select sysdate from dual@to9ibccdb; select sysdate from dual@to9ibccdb; select sysdate from dual@to19cdcdb; --生成删除sequence脚本 vi /u01/dumdata/index/dropseq.sql select 'drop sequence ' || SEQUENCE_OWNER || '.' || SEQUENCE_NAME || ';'   from (select b.SEQUENCE_OWNER SEQUENCE_OWNER,                b.sequence_name sequence_name,                b.INCREMENT_BY INCREMENT_BY,                b.MIN_VALUE MIN_VALUE,                b.MAX_VALUE MAX_VALUE,                b.last_number last_number,                b.CYCLE_FLAG CYCLE_FLAG,                b.CACHE_SIZE CACHE_SIZE,                (b.last_number - a.last_number) gap_than_zero           from dba_sequences@to19cdcdb a, dba_sequences@to9idb b          where b.sequence_owner in ('XXX')            and a.sequence_owner = b.sequence_owner(+)            and a.sequence_name = b.sequence_name(+)          order by 5, 1, 2 desc) where gap_than_zero <> 0 order by SEQUENCE_OWNER; -- 生成创建sequence脚本 vi /u01/dumdata/index/createseq.sql select 'create sequence ' || SEQUENCE_OWNER || '.' || SEQUENCE_NAME ||        '  increment by ' || INCREMENT_BY || ' minvalue ' || MIN_VALUE ||        ' maxvalue ' || MAX_VALUE || ' start with ' || (LAST_NUMBER) || ' ' ||        decode(CYCLE_FLAG, 'Y', 'CYCLE', 'NOCYCLE ') || decode(cache_size,0,' nocache ',' cache '||cache_size) || ';'   from (select b.SEQUENCE_OWNER SEQUENCE_OWNER,                b.sequence_name sequence_name,                b.INCREMENT_BY INCREMENT_BY,                b.MIN_VALUE MIN_VALUE,                b.MAX_VALUE MAX_VALUE,                b.last_number last_number,                b.CYCLE_FLAG CYCLE_FLAG,                b.CACHE_SIZE CACHE_SIZE,                (b.last_number - a.last_number) gap_than_zero           from dba_sequences@to19cdcdb a, dba_sequences@to9idb b          where b.sequence_owner in ('XXX')            and a.sequence_owner = b.sequence_owner(+)            and a.sequence_name = b.sequence_name(+)          order by 5, 1, 2 desc) where gap_than_zero <> 0 order by SEQUENCE_OWNER; --生成授权sequence脚本 vi /u01/dumdata/index/grantseq.sql select 'grant '||s.privilege||' on '||s.owner||'.'||s.table_name||' to '||s.grantee||';' from   (select owner, grantee, privilege, table_name   from dba_tab_privs@to9idb   where grantee not in ('SYS','OWBSYS_AUDIT','FFPDARCHIVE','FFPDARCHIVE1','SYSTEM','OUTLN','DIP','TSMSYS','DBSNMP','ORACLE_OCM','WMSYS','EXFSYS','DMSYS','CTXSYS','XDB','ANONYMOUS','ORDSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','MDSYS','OLAPSYS','MDDATA','SCOTT','SYSMAN','MGMT_VIEW','GOLDENGATE','PATROL','SPA','MGMT_USER','APEX_030200','APPQOSSYS','ORDDATA','OWBSYS','FLOWS_FILES','QCOAGT') and owner not in ('GOLDENGATE')   and table_name in (select sequence_name from dba_sequences where SEQUENCE_OWNER in ('XXX'))) s   full join   (select owner, grantee, privilege, table_name   from dba_tab_privs@to19cdcdb   where grantee not in ('SYS','OWBSYS_AUDIT','FFPDARCHIVE','FFPDARCHIVE1','SYSTEM','OUTLN','DIP','TSMSYS','DBSNMP','ORACLE_OCM','WMSYS','EXFSYS','DMSYS','CTXSYS','XDB','ANONYMOUS','ORDSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','MDSYS','OLAPSYS','MDDATA','SCOTT','SYSMAN','MGMT_VIEW','GOLDENGATE','PATROL','SPA','MGMT_USER','APEX_030200','APPQOSSYS','ORDDATA','OWBSYS','FLOWS_FILES','QCOAGT') and owner not in ('GOLDENGATE')   and table_name in (select sequence_name from dba_sequences where SEQUENCE_OWNER in ('XXX'))) d   on s.grantee=d.grantee and s.privilege=d.privilege where s.privilege is not null and d.privilege is null order by s.owner; --执行删除/创建/授权sequence脚本 su - oracle sqlplus '/as sysdba' @/u01/dumdata/index/dropseq.sql @/u01/dumdata/index/createseq.sql @/u01/dumdata/index/grantseq.sql -- 在OEM 251 环境增加导入3个sequence select 'create sequence ' || SEQUENCE_OWNER || '.' || SEQUENCE_NAME ||        '  increment by ' || INCREMENT_BY || ' minvalue ' || MIN_VALUE ||        ' maxvalue ' || MAX_VALUE || ' start with ' || (LAST_NUMBER) || ' ' ||        decode(CYCLE_FLAG, 'Y', 'CYCLE', 'NOCYCLE ') ||        decode(cache_size, 0, ' nocache ', ' cache ' || cache_size) || ';'   from dba_sequences@to19cdcdb a  where a.sequence_owner in ('XXX')    and a.sequence_name = 'EQPSEQUENCE'; sqlplus sys/CszDb_2212*@SZFW251 as sysdba create sequence XXX.EQPSEQUENCE  increment by 1 minvalue 1 maxvalue 999999999999999999999999999 start with 228186089 NOCYCLE  cache 20; -- 以下2个固定创建 create sequence XXX.CORESEQUENCE minvalue 1 maxvalue 9999999999999999999999999999 start with 1000000 increment by 1 cache 100; create sequence XXX.PARTITIONSEQUENCE minvalue 1 maxvalue 9999999999999999999999999999 start with 1 increment by 1 cache 100;     -- 创建job，参考创建job文档 --编译无效对象并验证 @?/rdbms/admin/utlprp 32; --对比无效对象 -- 中间服务器(XXX)执行   <<<<<<<<很多失效对象 select owner, object_name, object_type from dba_objects@to19cdcdb  where status='INVALID' and owner in ('XXX') minus select owner, object_name, object_type from dba_objects@to9idb  where status='INVALID' and owner in ('XXX') order by owner, object_type; ## 检查索引owner和表的owner是否一致    <<<发现生产库有不一致情况 select * from dba_indexes  where owner in ('XXX')  and owner <> table_owner #### 表记录对比 sqlplus XXX/XXXi@XXX:1521/XXX create table comp_tab_rows( owner varchar2(30), tab_name varchar2(30), rows_9i number, rows_19c number); create index idx_tab_rows on comp_tab_rows(owner, tab_name); truncate table comp_tab_rows; insert into comp_tab_rows(owner, tab_name) select owner, table_name from ( select owner, table_name, num_rows from dba_tables@to9idb  where owner in ('XXX') order by owner, num_rows ); commit; /* 创建2个查询存储过程，已经建好 create or replace procedure gen_9i_tab_rows(schema_name in varchar2) as   ls_sql  varchar2(500);   li_count  pls_integer;   ls_begin_date   varchar2(30);   ls_end_date     varchar2(30); begin   select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into ls_begin_date from dual;   dbms_output.put_line('Begin time: ' || ls_begin_date);      for cc in (select * from comp_tab_rows where owner = schema_name ) loop     ls_sql := 'select count(1) from ' || cc.owner || '."' || cc.tab_name || '"@to9ibccdb';     begin       execute immediate ls_sql into li_count;     exception       when others then         if sqlcode = -942 then           dbms_output.put_line(cc.owner || '.' || cc.tab_name || ' is not exists!');           update comp_tab_rows set rows_9i = -1 where owner = cc.owner and tab_name = cc.tab_name;         else           dbms_output.put_line(SQLERRM);         end if;     end;     --dbms_output.put_line(cc.owner || '.' || cc.tab_name || ': ' || li_count);     update comp_tab_rows set rows_9i = li_count where owner = cc.owner and tab_name = cc.tab_name;     commit;   end loop;   commit;      select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into ls_end_date from dual;   dbms_output.put_line('End time: ' || ls_end_date); exception   when others then     dbms_output.put_line(SQLERRM); end; / create or replace procedure gen_19c_tab_rows(schema_name in varchar2) as   ls_sql  varchar2(500);   li_count  pls_integer;   ls_begin_date   varchar2(30);   ls_end_date     varchar2(30); begin   select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into ls_begin_date from dual;   dbms_output.put_line('Begin time: ' || ls_begin_date);      for cc in (select * from comp_tab_rows where owner = schema_name ) loop     ls_sql := 'select count(1) from ' || cc.owner || '."' || cc.tab_name || '"@to19cdcdb';          begin       execute immediate ls_sql into li_count;     exception       when others then         if sqlcode = -942 then           dbms_output.put_line(cc.owner || '.' || cc.tab_name || ' is not exists!');           update comp_tab_rows set rows_19c = -1 where owner = cc.owner and tab_name = cc.tab_name;         else           dbms_output.put_line(SQLERRM);         end if;     end;     --dbms_output.put_line(cc.owner || '.' || cc.tab_name || ': ' || li_count);     update comp_tab_rows set rows_19c = li_count where owner = cc.owner and tab_name = cc.tab_name;     commit;   end loop;   commit;      select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into ls_end_date from dual;   dbms_output.put_line('End time: ' || ls_end_date); exception   when others then     dbms_output.put_line(SQLERRM); end; / create or replace procedure gen_oem_tab_rows as   ls_sql  varchar2(500);   li_count  pls_integer;   ls_begin_date   varchar2(30);   ls_end_date     varchar2(30); begin   select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into ls_begin_date from dual;   dbms_output.put_line('Begin time: ' || ls_begin_date);   for cc in (select owner, table_name from dba_tables@tofw251 t where t.owner = 'XXX') loop     ls_sql := 'select count(1) from ' || cc.owner || '."' || cc.table_name || '"@tofw251';     begin       execute immediate ls_sql into li_count;     exception       when others then         if sqlcode = -942 then           dbms_output.put_line(cc.owner || '.' || cc.table_name || ' is not exists!');           update comp_tab_rows set rows_19c = -1 where owner = cc.owner and tab_name = cc.table_name;         else           dbms_output.put_line(cc.owner || '.' || cc.table_name);           dbms_output.put_line(SQLERRM);         end if;     end;     --dbms_output.put_line(cc.owner || '.' || cc.table_name || ': ' || li_count);     update comp_tab_rows set rows_251 = li_count where owner = cc.owner and tab_name = cc.table_name;     commit;   end loop;   commit;   select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into ls_end_date from dual;   dbms_output.put_line('End time: ' || ls_end_date); exception   when others then     dbms_output.put_line(SQLERRM); end; / */ 执行存储过程查询19c库上表的行数： set serveroutput on exec gen_19c_tab_rows('XX'); -- 10s select * from comp_tab_rows where owner in ('XXX') and rows_19c is null; 执行存储过程查询9i库上表的行数： set serveroutput on exec gen_9i_tab_rows('XXX'); -- 13s -- oem exec gen_oem_tab_rows(); cat tbcnt3.log | awk '{print "update comp_tab_rows set rows_log = "$2" where tab_name = \047"$1"\047;"}' > tbcnt.sql 对比行数： select * from comp_tab_rows where rows_9i <> rows_19c or rows_9i is null or rows_19c is null; # 检查索引并行度 set line 120 set pagesize 5000 select 'alter index ' || owner || '.' || index_name || ' noparallel;' from dba_indexes where DEGREE > 1; # 收集统计信息   <<<< 整个收集完成大概15分钟 set timing on EXEC dbms_stats.gather_dictionary_stats; -- Elapsed: 00:01:10.96 EXEC dbms_stats.gather_fixed_objects_stats; -- Elapsed: 00:04:42.88 --EXEC dbms_stats.gather_database_stats; --EXEC dbms_stats.gather_database_stats(estimate_percent => 15, cascade => TRUE); --EXEC dbms_stats.gather_database_stats(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 32); EXEC dbms_stats.gather_schema_stats(ownname=>'XXX', degree=>32, cascade=>true, estimate_percent => dbms_stats.auto_sample_size, method_opt=> 'for all columns size auto'); Elapsed: 00:06:40.33 # 启用触发器 -- 目标服务器(XX)执行 @/home/oracle/oracle_work/enabled_triggers.sql set line 120 set pagesize 5000 col owner for a20 col trigger_name for a30 select 'alter trigger ' || owner || '.' || trigger_name || ' enable;' from dba_triggers  where owner in ('XXX')  and status = 'DISABLED' order by owner; 其他用户： select 'alter trigger ' || owner || '.' || trigger_name || ' enable;' from dba_triggers  where owner in ('XXX') and status = 'DISABLED' order by owner; # crontab等启用 -- 目标服务器(XXX)执行 -- enable root crontab #数据库参数放开 -- 目标服务器(XXX)执行 su - oracle sqlplus '/as sysdba' alter system set job_queue_processes=100; # 开启归档 show parameter dest_1 shutdown IMMEDIATE startup MOUNT alter database ARCHIVELOG alter database open # 创建闪回点 create restore point rp_20230906 guarantee flashback database;