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;