一、升级路线
- 10.2.0.5，11.1.0.7，11.2.0.2以上版本可以直接升级到12c。
- 10.2.0.5以前的版本和11.2.0.1版需要先升级到中间版本，再升级到12c。
二、环境说明
- 操作系统：Red Hat 8 Linux 64位
- 源数据库版本：Oracle 11.2.0.3
- 目标数据库版本：Oracle 12.1.0.2
三、升级步骤简述
- 备份源数据库（RMan）
- 在源数据库运行Pre-Upgrade Information Tool（preupgrd.sql）
- 准备新版本的Oracle Home
- 关闭数据库实例
- 使用12c启动数据库，进入升级模式
- 执行并行升级实用程序（catctl.pl）
- 执行Post Upgrade Status Tool（utlu121s.sql）
- 执行附加脚本（catuppst.sql和utlrp.sql）
- 完成升级阶段
四、升级步骤
4.1、备份数据库
4.2、在源数据库运行Pre-Upgrade Information Tool
4.2.1、运行预升级工具脚本
使用目标版本数据库（Oracle 12C）目录/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/下的preupgrd.sql脚本。在源数据库的SQL*Plus内执行。
1 sqlplus /nolog 2 3 SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 14 15:43:31 2023 4 5 Copyright (c) 1982, 2011, Oracle. All rights reserved. 6 7 SQL> conn / as sysdba 8 Connected. 9 SQL> @/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/preupgrd.sql
此工具是用来分析源数据库存在的问题，并生成用来生成解决问题的脚本。
Loading Pre-Upgrade Package...
***************************************************************************
Executing Pre-Upgrade Checks in RCAT...
***************************************************************************
************************************************************
====>> ERRORS FOUND for RCAT <<====
The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
prior to attempting your upgrade.
Failure to do so will result in a failed upgrade.
1) Check Tag: PURGE_RECYCLEBIN
Check Summary: Check that recycle bin is empty prior to upgrade
Fixup Summary:
"The recycle bin will be purged."
You MUST resolve the above error prior to upgrade
************************************************************
************************************************************
====>> PRE-UPGRADE RESULTS for RCAT <<====
ACTIONS REQUIRED:
1. Review results of the pre-upgrade checks:
/u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade.log
2. Execute in the SOURCE environment BEFORE upgrade:
/u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade_fixups.sql
3. Execute in the NEW environment AFTER upgrade:
/u01/app/oracle/cfgtoollogs/rcat/preupgrade/postupgrade_fixups.sql
************************************************************
***************************************************************************
Pre-Upgrade Checks in RCAT Completed.
***************************************************************************
***************************************************************************
***************************************************************************
以上结果提示，必须先清空源数据的回收站。
建议关注预升级检查记录：/u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade.log。
4.2.2、升级前脚本
升级前在源环境运行升级前修复脚本：/u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade_fixups.sql，此脚本用来修复在源数据发现的问题。
1 SQL> @/u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2023-09-14 15:51:38 Version: 12.1.0.2 Build: 006
Beginning Pre-Upgrade Fixups...
Executing in container RCAT
**********************************************************************
Check Tag: EM_PRESENT
Check Summary: Check if Enterprise Manager is present
Fix Summary: Execute emremove.sql prior to upgrade.
**********************************************************************
Fixup Returned Information:
WARNING: --> Enterprise Manager Database Control repository found in the database
In Oracle Database 12c, Database Control is removed during
the upgrade. To save time during the Upgrade, this action
can be done prior to upgrading using the following steps after
copying rdbms/admin/emremove.sql from the new Oracle home
- Stop EM Database Control:
$> emctl stop dbconsole
- Connect to the Database using the SYS account AS SYSDBA:
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
Without the set echo and serveroutput commands you will not
be able to follow the progress of the script.
**********************************************************************
**********************************************************************
Check Tag: AMD_EXISTS
Check Summary: Check to see if AMD is present in the database
Fix Summary: Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> OLAP Catalog(AMD) exists in database
Starting with Oracle Database 12c, OLAP Catalog component is desupported.
If you are not using the OLAP Catalog component and want
to remove it, then execute the
ORACLE_HOME/olap/admin/catnoamd.sql script before or
after the upgrade.
**********************************************************************
**********************************************************************
Check Tag: PURGE_RECYCLEBIN
Check Summary: Check that recycle bin is empty prior to upgrade
Fix Summary: The recycle bin will be purged.
**********************************************************************
Fixup Succeeded
**********************************************************************
**********************************************************************
Check Tag: APEX_UPGRADE_MSG
Check Summary: Check that APEX will need to be upgraded.
Fix Summary: Oracle Application Express can be manually upgraded prior to database upgrade.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Oracle Application Express (APEX) can be
manually upgraded prior to database upgrade
APEX is currently at version 3.2.1.00.10 and will need to be
upgraded to APEX version 4.2.5 in the new release.
Note 1: To reduce database upgrade time, APEX can be manually
upgraded outside of and prior to database upgrade.
Note 2: See MOS Note 1088970.1 for information on APEX
installation upgrades.
**********************************************************************
**********************************************************************
[Pre-Upgrade Recommendations]
**********************************************************************
*****************************************
********* Dictionary Statistics *********
*****************************************
Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
^^^ MANUAL ACTION SUGGESTED ^^^
**************************************************
************* Fixup Summary ************
1 fixup routine was successful.
3 fixup routines returned INFORMATIONAL text that should be reviewed.
**************** Pre-Upgrade Fixup Script Complete *********************
提出的建议如下：
- 发现了Enterprise Manager，在升级过程中会删除EM库，为了减少升级时间，可以先删除。已自动处理。
-
发现了OLAP Catalog组件，在12C中不再支持OLAP Catalog组件，可以在升级前或升级后使用catnoamd.sql脚本删除，这里就不删除了，升级后再说。
- 回收站是空的，不需要处理。如果回收站不是空的，将自动清空回收站。
-
发现了APEX，APEX版本升级会占用比较多的时间，oracle建议可以手工升级，参考: 1088970.1，这里也不管了，一起升级。
- 建议在数据库升级的24小时之前执行EXECUTE dbms_stats.gather_dictionary_stats;，收集数据字典统计信息。
1 SQL> EXECUTE dbms_stats.gather_dictionary_stats;
4.2.3、升级后脚本
升级后在新环境运行升级后修复脚本：/u01/app/oracle/cfgtoollogs/rcat/preupgrade/postupgrade_fixups.sql，此脚本用来修复数据库升级后的问题。
4.3、准备12c Oracle Home
编辑/etc/oratab文件，将RCAT的环境变量指向Oracle 12C的Oracle Home。
1 [oracle@orasrv admin]$ vim /etc/oratab
rcat:/u01/app/oracle/product/12.1.0/dbhome_1:Y
4.4、关闭数据库实例
1 SQL> shutdown immediate
4.5、使用12c启动数据库，进入升级模式
1 SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 14 16:36:59 2023 2 3 Copyright (c) 1982, 2014, Oracle. All rights reserved. 4 5 SQL> conn / as sysdba 6 Connected to an idle instance. 7 SQL> startup upgrade 8 ORA-01078: failure in processing system parameters 9 LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initrcat.ora'
以上提示说明需要参数文件initrcat.ora，我们将11g数据库的参数文件复制到12c的相应目录
1 [oracle@orasrv ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilercat.ora /u01/app/oracle/product/12.1.0/dbhome_1/dbs/
再次启动
1 SQL> startup upgrade
4.6、执行并行升级实用程序
1 SQL> @/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catupgrd.sql
DOC>######################################################################
DOC>######################################################################
DOC> NOTE
DOC>
DOC> The catupgrd.sql is being deprecated in the 12.1 release of the
DOC> Oracle Database. Customers are encouraged to use catctl.pl as
DOC> the replacement for catupgrd.sql when upgrading the database dictionary.
DOC>
DOC> cd $ORACLE_HOME/rdbms/admin
DOC> $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql
DOC>
DOC> Refer to the Oracle Database Upgrade Guide for more information.
DOC>
DOC> This database upgrade procedure must be called with the following
DOC> argument when invoking from the SQL prompt:
DOC>
DOC> @catupgrd.sql PARALLEL=NO
DOC>
DOC>######################################################################
DOC>######################################################################
DOC>#
old 2: WHERE UPPER('&&1') = 'PARALLEL=NO' OR
new 2: WHERE UPPER('') = 'PARALLEL=NO' OR
old 3: UPPER('&&1') = 'PARALLEL=YES'
new 3: UPPER('') = 'PARALLEL=YES'
SELECT (to_number(count(*)))/(to_number(count(*))) FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
在12c中，使用升级脚本catctl.pl代替了catupgrd.sql，升级脚本的执行方式也发生了变化。根据以上提示可知，在Linux环境下需按以下方式执行：
[oracle@orasrv ~]$ cd $ORACLE_HOME/rdbms/admin [oracle@orasrv admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 2 catupgrd.sql
-n后参数2代表并行度为2，可按CPU核数进行设置。接下来就进入到漫长的升级过程：
Argument list for [catctl.pl]
SQL Process Count n = 2
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = 0
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = 0
Run in c = 0
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 0
catctl.pl version: 12.1.0.2.0
Oracle Base = /u01/app/oracle
Analyzing file catupgrd.sql
Log files in /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin
catcon: ALL catcon-related output will be written to catupgrd_catcon_4741.lst
catcon: See catupgrd*.log files for output generated by scripts
catcon: See catupgrd_*.lst files for spool files, if any
Number of Cpus = 2
SQL Process Count = 2
------------------------------------------------------
Phases [0-73]
Serial Phase #: 0 Files: 1 Time: 99s
Serial Phase #: 1 Files: 5 Time: 23s
Restart Phase #: 2 Files: 1 Time: 0s
Parallel Phase #: 3 Files: 18 Time: 27s
Restart Phase #: 4 Files: 1 Time: 0s
Serial Phase #: 5 Files: 5 Time: 32s
Serial Phase #: 6 Files: 1 Time: 26s
Serial Phase #: 7 Files: 4 Time: 5s
Restart Phase #: 8 Files: 1 Time: 0s
Parallel Phase #: 9 Files: 62 Time: 63s
Restart Phase #:10 Files: 1 Time: 0s
Serial Phase #:11 Files: 1 Time: 43s
Restart Phase #:12 Files: 1 Time: 0s
Parallel Phase #:13 Files: 91 Time: 49s
Restart Phase #:14 Files: 1 Time: 0s
Parallel Phase #:15 Files: 111 Time: 64s
Restart Phase #:16 Files: 1 Time: 0s
Serial Phase #:17 Files: 3 Time: 40s
Restart Phase #:18 Files: 1 Time: 0s
Parallel Phase #:19 Files: 32 Time: 56s
Restart Phase #:20 Files: 1 Time: 0s
Serial Phase #:21 Files: 3 Time: 43s
Restart Phase #:22 Files: 1 Time: 0s
Parallel Phase #:23 Files: 23 Time: 105s
Restart Phase #:24 Files: 1 Time: 0s
Parallel Phase #:25 Files: 11 Time: 71s
Restart Phase #:26 Files: 1 Time: 0s
Serial Phase #:27 Files: 1 Time: 20s
Restart Phase #:28 Files: 1 Time: 0s
Serial Phase #:30 Files: 1 Time: 0s
Serial Phase #:31 Files: 257 Time: 51s
Serial Phase #:32 Files: 1 Time: 0s
Restart Phase #:33 Files: 1 Time: 0s
Serial Phase #:34 Files: 1 Time: 43s
Restart Phase #:35 Files: 1 Time: 0s
Restart Phase #:36 Files: 1 Time: 17s
Serial Phase #:37 Files: 4 Time: 74s
Restart Phase #:38 Files: 1 Time: 0s
Parallel Phase #:39 Files: 13 Time: 55s
Restart Phase #:40 Files: 1 Time: 0s
Parallel Phase #:41 Files: 10 Time: 46s
Restart Phase #:42 Files: 1 Time: 0s
Serial Phase #:43 Files: 1 Time: 44s
Restart Phase #:44 Files: 1 Time: 0s
Serial Phase #:45 Files: 1 Time: 38s
Serial Phase #:46 Files: 1 Time: 0s
Restart Phase #:47 Files: 1 Time: 0s
Serial Phase #:48 Files: 1 Time: 115s
Restart Phase #:49 Files: 1 Time: 0s
Serial Phase #:50 Files: 1 Time: 69s
Restart Phase #:51 Files: 1 Time: 0s
Serial Phase #:52 Files: 1 Time: 51s
Restart Phase #:53 Files: 1 Time: 0s
Serial Phase #:54 Files: 1 Time: 147s
Restart Phase #:55 Files: 1 Time: 0s
Serial Phase #:56 Files: 1 Time: 81s
Restart Phase #:57 Files: 1 Time: 0s
Serial Phase #:58 Files: 1 Time: 124s
Restart Phase #:59 Files: 1 Time: 0s
Serial Phase #:60 Files: 1 Time: 200s
Restart Phase #:61 Files: 1 Time: 0s
Serial Phase #:62 Files: 1 Time: 1016s
Restart Phase #:63 Files: 1 Time: 0s
Serial Phase #:64 Files: 1 Time: 32s
Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > catupgrd_datapatch_upgrade.log 2> catupgrd_datapatch_upgrade.err
returned from sqlpatch
Time: 75s
Serial Phase #:66 Files: 1 Time: 36s
Serial Phase #:68 Files: 1 Time: 0s
Serial Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose > catupgrd_datapatch_normal.log 2> catupgrd_datapatch_normal.err
returned from sqlpatch
Time: 259s
Serial Phase #:70 Files: 1 Time: 149s
Serial Phase #:71 Files: 1 Time: 0s
Serial Phase #:72 Files: 1 Time: 0s
Serial Phase #:73 Files: 1 Time: 90s
Grand Total Time: 3586s
LOG FILES: (catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/rcat/upgrade/upg_summary.log
Grand Total Upgrade Time: [0d:0h:59m:46s]
升级完成后，数据库实例自动关闭，需要重新启动数据库到open状态
SQL> startup
继续执行4.2.3所述升级后修复脚本
1 SQL> @/u01/app/oracle/cfgtoollogs/rcat/preupgrade/postupgrade_fixups.sql
Post Upgrade Fixup Script Generated on 2023-09-14 15:51:38 Version: 12.1.0.2 Build: 006
Beginning Post-Upgrade Fixups...
**********************************************************************
Check Tag: OLD_TIME_ZONES_EXIST
Check Summary: Check for use of older timezone data file
Fix Summary: Update the timezone using the DBMS_DST package after upgrade is complete.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Older Timezone in use
Database is using a time zone file older than version 18.
After the upgrade, it is recommended that DBMS_DST package
be used to upgrade the 12.1.0.2.0 database time zone version
to the latest version which comes with the new release.
Please refer to My Oracle Support note number 977512.1 for details.
**********************************************************************
**********************************************************************
Check Tag: NOT_UPG_BY_STD_UPGRD
Check Summary: Identify existing components that will NOT be upgraded
Fix Summary: This fixup does not perform any action.
**********************************************************************
Fixup Returned Information:
This fixup does not perform any action.
If you want to upgrade those other components, you must do so manually.
**********************************************************************
**********************************************************************
[Post-Upgrade Recommendations]
**********************************************************************
*****************************************
******** Fixed Object Statistics ********
*****************************************
Please create stats on fixed objects two weeks
after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
^^^ MANUAL ACTION SUGGESTED ^^^
**************************************************
************* Fixup Summary ************
2 fixup routines generated INFORMATIONAL messages that should be reviewed.
*************** Post Upgrade Fixup Script Complete ********************
PL/SQL procedure successfully completed.
提出的建议如下：
- 使用旧的Timezone文件版本，数据库升级完成后进行Timezone版本升级。
- 确认没有升级的现有组件，忽略。
- 在数据库升级后的两周内运行EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;收集对象的统计信息。
1 SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
4.7、执行Post-Upgrade Status Tool
升级完成后可以执行Post-Upgrade Status Tool（$ORACLE_HOME/rdbms/admin/utlu121s.sql），查看升级的概况信息。
1 SQL> @?/rdbms/admin/utlu121s.sql
Oracle Database 12.1 Post-Upgrade Status Tool 09-15-2023 10:47:44 Component Current Version Elapsed Time Name Status Number HH:MM:SS Oracle Server UPGRADED 12.1.0.2.0 00:18:54 JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:01:34 Oracle Workspace Manager VALID 12.1.0.2.0 00:00:52 OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:30 OLAP Catalog OPTION OFF 11.2.0.3.0 00:00:00 Oracle OLAP API VALID 12.1.0.2.0 00:00:11 Oracle XDK VALID 12.1.0.2.0 00:00:48 Oracle Text VALID 12.1.0.2.0 00:00:54 Oracle XML Database VALID 12.1.0.2.0 00:01:12 Oracle Database Java Packages VALID 12.1.0.2.0 00:00:08 Oracle Multimedia VALID 12.1.0.2.0 00:01:45 Spatial UPGRADED 12.1.0.2.0 00:03:02 Oracle Application Express VALID 4.2.5.00.08 00:15:53 Final Actions 00:01:56 Post Upgrade 00:02:20
除了OLAP Catalog组件外，其它组件都升级到希望的版本。
因不再被12c支持，可以移除OLAP Catalog组件。
1 SQL> @?/olap/admin/catnoamd.sql
4.8、执行附加脚本
执行catuppst.sql脚本完成接下来的升级操作，它不要求数据库处于升级模式。
1 SQL> @?/rdbms/admin/catuppst.sql
执行utlrp.sql脚本重新编译PL/SQL和Java代码。
1 SQL> @?/rdbms/admin/utlrp.sql
等待执行完成。
4.9、完成升级阶段
4.9.1、检查环境变量
- ORACLE_BASE
- ORACLE_HOME
- PATH,LD_LIBRARY_PATH and SHLIB_PATH
4.9.2、检查初始化参数文件
确认初始化参数修改正确。
4.9.3、检查口令文件
可以使用orapwd命令重建口令文件。
4.9.4、COMPATIBLE参数
COMPATIBLE参数控制数据库兼容性级别，如果数据库不会再降级到以前的版本，可以设置此参数。
1 SQL> alter system set compatible='12.1.0.2.0' scope=spfile;
重启数据库实例。
4.9.5、升级TIMEZONE文件版本
标签：Files,0s,12C,手工,Phase,12.1,Time,Oracle,Serial From： https://www.cnblogs.com/aries0228/p/17702943.html