随着硬件生命周期,数据库等保要求等,不少客户需要把数据库从11.2.0.4版本升级到19c.对于这样的客户的核心生产库升级,一般考虑到回退情况,大部分会选择异机进行升级,提供一个完整版测试
在源库(11.2.0.4)中做升级之前检查
--触发器检查（禁用和启用脚本生成）
SELECT 'ALTER TRIGGER ' || owner || '.' || trigger_name || ' DISABLE;' AS disable_commands
FROM dba_triggers
WHERE trigger_type IN ('BEFORE EVENT', 'AFTER EVENT')
AND triggering_event LIKE '%DDL%'
AND status = 'ENABLED';
SELECT 'ALTER TRIGGER ' || owner || '.' || trigger_name || ' ENABLE;' AS disable_commands
FROM dba_triggers
WHERE trigger_type IN ('BEFORE EVENT', 'AFTER EVENT')
AND triggering_event LIKE '%DDL%'
AND status = 'ENABLED';
--收集字典统计信息
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
--检查时区信息
select * from v$timezone_file;
select distinct owner from dba_tab_columns where
DATA_TYPE='TIMESTAMP(6) WITH TIME ZONE';
--物化视图刷新检查
SELECT DISTINCT(TRUNC(last_refresh)) FROM dba_snapshot_refresh_times;
SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
--无效index检查
set linesize 400
select owner,index_name from dba_indexes where status in ('INVALID','UNUSABLE') ;
select index_owner,index_name,partition_name,status from dba_ind_partitions where status in ('INVALID','UNUSABLE') ;
--清空审计表和所属表空间检查
truncate table aud$;
SELECT owner,tablespace_name FROM dba_tables WHERE table_name='AUD$';
--清理19c中无法升级组件
alter session set nls_language='American';
SET ECHO ON;
SET SERVEROUTPUT ON;
@olspreupgrade.sql
@emremove.sql
@catnoamd.sql
@catnoexf.sql
@$ORACLE_HOME/apex/apxremov.sql
@?/rdbms/admin/utlprp.sql 32
select owner,object_type from dba_objects where object_name =upper('htmldb_system');
drop package htmldb_system;
drop public synonym htmldb_system;
--检查数据文件不处于备份状态
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
--清空回收站
PURGE DBA_RECYCLEBIN;
--检查 SYS 及 SYSTEM默认表空间
SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');
--检查组件状态
set pagesize 500
set linesize 400
col comp_name for a40
select substr(comp_name,1,40) comp_name, status, substr (version,1,10) version from dba_registry order by comp_name;
--记录无效对象
create table system.invalid_obj_10g_beforeup tablespace sysaux as 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;
--禁用Block Change Tracking
SELECT filename, status, bytes FROM v$block_change_tracking;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
--执行 preupgrade 脚本
$ORACLE_HOME/jdk/bin/java -jar /u01/xff/preupgrade.jar FILE TEXT DIR /tmp
@/tmp/preupgrade_fixups.sql
--执行dbupgdiag.sql脚本
alter session set nls_language='American';
SET ECHO ON;
SET SERVEROUTPUT ON;
@dbupgdiag.sql
rman备份还原数据库
--在11.2.0.4库备份操作
backup filesperset = 5 as compressed backupset database format
'/u01/rmanback/full_%T_%U.rman';
sql 'alter system archive log current';
sql 'alter system archive log current';
Backup filesperset = 10 as compressed backupset archivelog all format
'/u01/rmanback/arch_%T_%U.rman' not backed up delete input;
backup format '/u01/rmanback/ctl_%T_%U.rman' current controlfile;
--在19c库还原操作
SQL>startup nomount pfile='/tmp/pfile'
RMAN> restore controlfile from '/u01/rmanback/ctl_20251003_0a459rsp_1_1.rman';
RMAN> alter database mount;
RMAN> catalog start with '/u01/orabak/';
RMAN> restore database;
RMAN> recover database; --可以增量追加归档
SQL> alter database open resetlogs upgrade;
正式升级操作（19c环境）
startup pfile='/u01/xff/pfile.upgrade' mount;
alter database open upgrade;
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
cd $ORACLE_HOME/bin
./dbupgrade
sqlplus / as sysdba
startup
@?/rdbms/admin/utlusts.sql TEXT
@?/rdbms/admin/utlrp.sql 32
@?/rdbms/admin/utlusts.sql TEXT
set pagesize500
set linesize 400
col comp_name for a40
select substr(comp_name,1,40) comp_name, status, substr (version,1,10) version from dba_registry order by comp_name;
升级完成后操作
--执行postupgrade_fixups
@/tmp/postupgrade_fixups.sql
--收集字典统计信息
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
--升级时区
cd $ORACLE_HOME/rdbms/admin
@utltz_countstats.sql
@utltz_countstar.sql
@?/rdbms/admin/utltz_upg_check.sql
@?/rdbms/admin/utltz_upg_apply.sql
主要日志
--升级操作日志
[oracle@oracledb:/u01/app/oracle/product/19c/db/bin]$ ./dbupgrade
Argument list for [/u01/app/oracle/product/19c/db/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.16.0.0.0DBRU_LINUX.X64_220701]
/u01/app/oracle/product/19c/db/rdbms/admin/orahome = [/u01/app/oracle/product/19c/db]
/u01/app/oracle/product/19c/db/bin/orabasehome = [/u01/app/oracle/product/19c/db]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19c/db]
Analyzing file /u01/app/oracle/product/19c/db/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20251003103800]
catcon::set_log_file_base_path: ALL catcon-related output will be written to
[/tmp/cfgtoollogs/upgrade20251003103800/catupgrd_catcon_15770.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20251003103800/catupgrd*.log]
files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20251003103800/catupgrd_*.lst]
files for spool files, if any
Number of Cpus = 4
Database Name = orcl
DataBase Version = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to
[/u01/xxx/upgrade20251003103801/catupgrd_catcon_15770.lst]
catcon::set_log_file_base_path: catcon: See [/u01/xxx/upgrade20251003103801/catupgrd*.log]
files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/xxx/upgrade20251003103801/catupgrd_*.lst]
files for spool files, if any
Log file directory = [/u01/xxx/upgrade20251003103801]
Parallel SQL Process Count = 4
Components in [orcl]
Installed [APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX DV EM MGW ODM OLS RAC WK]
DataBase Version = 11.2.0.4.0
------------------------------------------------------
Phases [0-107] Start Time:[2025_10_03 10:38:01]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [orcl] Files:1 Time: 30s
*************** Catalog Core SQL ***************
Serial Phase #:1 [orcl] Files:5 Time: 18s
Restart Phase #:2 [orcl] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [orcl] Files:19 Time: 9s
Restart Phase #:4 [orcl] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [orcl] Files:7 Time: 7s
***************** Catproc Start ****************
Serial Phase #:6 [orcl] Files:1 Time: 5s
***************** Catproc Types ****************
Serial Phase #:7 [orcl] Files:2 Time: 4s
Restart Phase #:8 [orcl] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [orcl] Files:70 Time: 9s
Restart Phase #:10 [orcl] Files:1 Time: 0s
************* Catproc Package Specs ************
Serial Phase #:11 [orcl] Files:1 Time: 25s
Restart Phase #:12 [orcl] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [orcl] Files:95 Time: 2s
Restart Phase #:14 [orcl] Files:1 Time: 0s
Parallel Phase #:15 [orcl] Files:122 Time: 4s
Restart Phase #:16 [orcl] Files:1 Time: 0s
Serial Phase #:17 [orcl] Files:25 Time: 1s
Restart Phase #:18 [orcl] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [orcl] Files:32 Time: 6s
Restart Phase #:20 [orcl] Files:1 Time: 0s
Serial Phase #:21 [orcl] Files:3 Time: 4s
Restart Phase #:22 [orcl] Files:1 Time: 1s
Parallel Phase #:23 [orcl] Files:25 Time: 79s
Restart Phase #:24 [orcl] Files:1 Time: 0s
Parallel Phase #:25 [orcl] Files:12 Time: 49s
Restart Phase #:26 [orcl] Files:1 Time: 0s
Serial Phase #:27 [orcl] Files:1 Time: 0s
Serial Phase #:28 [orcl] Files:4 Time: 1s
Serial Phase #:29 [orcl] Files:1 Time: 0s
Restart Phase #:30 [orcl] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [orcl] Files:1 Time: 0s
Restart Phase #:32 [orcl] Files:1 Time: 0s
Serial Phase #:34 [orcl] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [orcl] Files:297 Time: 9s
Serial Phase #:36 [orcl] Files:1 Time: 0s
Restart Phase #:37 [orcl] Files:1 Time: 0s
Serial Phase #:38 [orcl] Files:10 Time: 2s
Restart Phase #:39 [orcl] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [orcl] Files:3 Time: 21s
Restart Phase #:41 [orcl] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [orcl] Files:13 Time: 50s
Restart Phase #:43 [orcl] Files:1 Time: 1s
Parallel Phase #:44 [orcl] Files:11 Time: 3s
Restart Phase #:45 [orcl] Files:1 Time: 0s
Parallel Phase #:46 [orcl] Files:3 Time: 0s
Restart Phase #:47 [orcl] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [orcl] Files:1 Time: 5s
Restart Phase #:49 [orcl] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [orcl] Files:1 Time: 8s
************ Upgrade Component Start ***********
Serial Phase #:51 [orcl] Files:1 Time: 0s
Restart Phase #:52 [orcl] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [orcl] Files:2 Time: 161s
***************** Upgrading XDB ****************
Restart Phase #:54 [orcl] Files:1 Time: 0s
Serial Phase #:56 [orcl] Files:3 Time: 9s
Serial Phase #:57 [orcl] Files:3 Time: 1s
Parallel Phase #:58 [orcl] Files:10 Time: 1s
Parallel Phase #:59 [orcl] Files:25 Time: 2s
Serial Phase #:60 [orcl] Files:4 Time: 3s
Serial Phase #:61 [orcl] Files:1 Time: 0s
Serial Phase #:62 [orcl] Files:32 Time: 1s
Serial Phase #:63 [orcl] Files:1 Time: 0s
Parallel Phase #:64 [orcl] Files:6 Time: 4s
Serial Phase #:65 [orcl] Files:2 Time: 8s
Serial Phase #:66 [orcl] Files:3 Time: 29s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [orcl] Files:1 Time: 0s
Serial Phase #:69 [orcl] Files:1 Time: 0s
Parallel Phase #:70 [orcl] Files:2 Time: 14s
Restart Phase #:71 [orcl] Files:1 Time: 0s
Parallel Phase #:72 [orcl] Files:2 Time: 0s
Serial Phase #:73 [orcl] Files:2 Time: 0s
***************** Upgrading SDO ****************
Restart Phase #:74 [orcl] Files:1 Time: 0s
Serial Phase #:76 [orcl] Files:1 Time: 16s
Serial Phase #:77 [orcl] Files:2 Time: 0s
Restart Phase #:78 [orcl] Files:1 Time: 0s
Serial Phase #:79 [orcl] Files:1 Time: 8s
Restart Phase #:80 [orcl] Files:1 Time: 0s
Parallel Phase #:81 [orcl] Files:3 Time: 10s
Restart Phase #:82 [orcl] Files:1 Time: 0s
Serial Phase #:83 [orcl] Files:1 Time: 2s
Restart Phase #:84 [orcl] Files:1 Time: 0s
Serial Phase #:85 [orcl] Files:1 Time: 4s
Restart Phase #:86 [orcl] Files:1 Time: 0s
Parallel Phase #:87 [orcl] Files:4 Time: 28s
Restart Phase #:88 [orcl] Files:1 Time: 0s
Serial Phase #:89 [orcl] Files:1 Time: 0s
Restart Phase #:90 [orcl] Files:1 Time: 0s
Serial Phase #:91 [orcl] Files:2 Time: 4s
Restart Phase #:92 [orcl] Files:1 Time: 0s
Serial Phase #:93 [orcl] Files:1 Time: 0s
Restart Phase #:94 [orcl] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [orcl] Files:1 Time: 6s
Restart Phase #:96 [orcl] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:97 [orcl] Files:1 Time: 1s
************* Final Upgrade scripts ************
Serial Phase #:98 [orcl] Files:1 Time: 22s
******************* Migration ******************
Serial Phase #:99 [orcl] Files:1 Time: 15s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [orcl] Files:1 Time: 0s
Serial Phase #:101 [orcl] Files:1 Time: 0s
Serial Phase #:102 [orcl] Files:1 Time: 35s
***************** Post Upgrade *****************
Serial Phase #:103 [orcl] Files:1 Time: 14s
**************** Summary report ****************
Serial Phase #:104 [orcl] Files:1 Time: 0s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [orcl] Files:1 Time: 0s
Serial Phase #:106 [orcl] Files:1 Time: 0s
Serial Phase #:107 [orcl] Files:1 Time: 50s
------------------------------------------------------
Phases [0-107] End Time:[2025_10_03 10:51:23]
------------------------------------------------------
Grand Total Time: 802s
LOG FILES: (/u01/xxx/upgrade20251003103801/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/xxx/upgrade20251003103801/upg_summary.log
Grand Total Upgrade Time: [0d:0h:13m:22s]
--组件状态
SQL>@?/rdbms/admin/utlusts.sql TEXT
Oracle Database Release 19 Post-Upgrade Status Tool 10-03-2025 10:53:4
Database Name: ORCL
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 19.16.0.0.0 00:05:52
JServer JAVA Virtual Machine UPGRADED 19.16.0.0.0 00:01:28
Oracle XDK UPGRADED 19.16.0.0.0 00:00:16
Oracle Database Java Packages UPGRADED 19.16.0.0.0 00:00:05
OLAP Analytic Workspace UPGRADED 19.16.0.0.0 00:00:04
Oracle Text UPGRADED 19.16.0.0.0 00:00:18
Oracle Workspace Manager UPGRADED 19.16.0.0.0 00:00:26
Oracle Real Application Clusters OPTION OFF 19.16.0.0.0 00:00:00
Oracle XML Database UPGRADED 19.16.0.0.0 00:00:58
Oracle Multimedia UPGRADED 19.16.0.0.0 00:00:13
Spatial UPGRADED 19.16.0.0.0 00:01:11
Oracle OLAP API UPGRADED 19.16.0.0.0 00:00:05
Datapatch 00:00:19
Final Actions 00:00:37
Post Upgrade 00:00:12
Total Upgrade Time: 00:11:54
Database time zone version is 14. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.
--编译无效对象
SQL>@?/rdbms/admin/utlrp.sql 32
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2025-10-03 10:53:48
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2025-10-03 10:55:35
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
--编译之后组件状态正常
SQL>@?/rdbms/admin/utlusts.sql TEXT
Oracle Database Release 19 Post-Upgrade Status Tool 10-03-2025 10:55:3
Database Name: ORCL
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.16.0.0.0 00:05:52
JServer JAVA Virtual Machine VALID 19.16.0.0.0 00:01:28
Oracle XDK VALID 19.16.0.0.0 00:00:16
Oracle Database Java Packages VALID 19.16.0.0.0 00:00:05
OLAP Analytic Workspace VALID 19.16.0.0.0 00:00:04
Oracle Text VALID 19.16.0.0.0 00:00:18
Oracle Workspace Manager VALID 19.16.0.0.0 00:00:26
Oracle Real Application Clusters OPTION OFF 19.16.0.0.0 00:00:00
Oracle XML Database VALID 19.16.0.0.0 00:00:58
Oracle Multimedia VALID 19.16.0.0.0 00:00:13
Spatial VALID 19.16.0.0.0 00:01:11
Oracle OLAP API VALID 19.16.0.0.0 00:00:05
Datapatch 00:00:19
Final Actions 00:00:37
Post Upgrade 00:00:12
Post Compile 00:01:47
Total Upgrade Time: 00:13:41
Database time zone version is 14. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.
--时区升级之后结果
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;
PROPERTY_NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
32
DST_SECONDARY_TT_VERSION
0
DST_UPGRADE_STATE
NONE
3 rows selected.
具体参考:Oracle 19c – 手动升级到 Non-CDB Oracle Database 19c 的完整核对清单 (Doc ID 2577572.1)
