ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Oracle 9i升级19C 逻辑迁移详细方法（二）

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

原创 Oracle 作者：jason_yehua 时间：2023-10-07 21:04:47 0 删除 编辑

# 目标端同步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;


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31547506/viewspace-2987247/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Oracle 9i升级19C 逻辑迁移详细方法（一）
下一篇： 数据库监听夯故障分析
请登录后发表评论 登录
全部评论
jason_yehua

注册时间：2019-05-22

  • 博文量
    122
  • 访问量
    99627

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593