oracle原地升级12c-19c

1、源库执行检查

dbupgdiag.sql

确保 sys 和 system 下没有重复对象

升级前最好打到最新 PSU/RU

禁用所有 before/after DDL 类型的触发器升级后再开启

select 'alter trigger ' || trigger_name || ' disable;' from dba_triggers where

trigger_type in ('BEFORE EACH ROW','AFTER EACH ROW','BEFORE EVENT','AFTER

EVENT') ;

--select 'alter trigger ' || trigger_name || ' enable;' from dba_triggers where

禁用 job 及 crontab

开归档

2、执行 preupgrade 脚本

$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1

$ export PATH=$ORACLE_HOME/bin:$PATH

$ export ORACLE_SID=orcl

$ $ORACLE_HOME/jdk/bin/java -jar

/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE

TEXT

==================

PREUPGRADE SUMMARY

==================

/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log

/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql

/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

Execute fixup scripts across the entire CDB:

2. Review logs under /u01/app/oracle/cfgtoollogs/orcl/preupgrade/

1. Execute postupgrade fixups with the below command

$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -

/u01/app/oracle/cfgtoollogs/orcl/preupgrade/ -b postup_orcl

/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

2. Review logs under /u01/app/oracle/cfgtoollogs/orcl/preupgrade/

Preupgrade complete: 2020-12-28T14:23:15

根据上面的输出，在升级时候要遵守 preupgrade.log 里面的建议。整个升级过程都在严

格遵守 preupgrade.log 的建议。

在升级前执行 preupgrade_fixups.sql 脚本，在升级后执行 postupgrade_fixups.sql 脚

本。

preupgrade.log 报告如下，需要根据报告的建议进行修改

执行 fixup 脚本:

set linesize 100

select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version

from dba_registry order by comp_name;

select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type

from dba_objects where status='INVALID' order by owner,object_type;

select owner,object_type,count(*) from dba_objects where status='INVALID' group

by owner,object_type order by owner,object_type ;