一、升级路线
- 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.
- ***************************************************************************
- ***************************************************************************
- ***************************************************************************
- 发现了Enterprise Manager，在升级过程中会删除EM库，为了减少升级时间，可以先删除。已自动处理。
- 发现了OLAP Catalog组件，在12C中不再支持OLAP Catalog组件，可以在升级前或升级后使用catnoamd.sql脚本删除，这里就不删除了，升级后再说。
- 回收站是空的，不需要处理。如果回收站不是空的，将自动清空回收站。
- 发现了APEX，APEX版本升级会占用比较多的时间，oracle建议可以手工升级，参考: 1088970.1，这里也不管了，一起升级。
- 建议在数据库升级的24小时之前执行EXECUTE dbms_stats.gather_dictionary_stats;，收集数据字典统计信息。
- 1 SQL> @/u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade_fixups.sql
升级后在新环境运行升级后修复脚本：/u01/app/oracle/cfgtoollogs/rcat/preupgrade/postupgrade_fixups.sql，此脚本用来修复数据库升级后的问题。
4.3、准备12c Oracle Home
编辑/etc/oratab文件，将RCAT的环境变量指向Oracle 12C的Oracle Home。- 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 *********************
- 1 SQL> EXECUTE dbms_stats.gather_dictionary_stats;
- 1 [oracle@orasrv admin]$ vim /etc/oratab
- rcat:/u01/app/oracle/product/12.1.0/dbhome_1:Y
- 1 SQL> shutdown immediate<br>
- 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'
- 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<br>
- 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<br>
- 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
- [oracle@orasrv ~]$ cd $ORACLE_HOME/rdbms/admin
- [oracle@orasrv admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 2 catupgrd.sql
- 使用旧的Timezone文件版本，数据库升级完成后进行Timezone版本升级。
- 确认没有升级的现有组件，忽略。
- 在数据库升级后的两周内运行EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;收集对象的统计信息。
- 1 SQL> @/u01/app/oracle/cfgtoollogs/rcat/preupgrade/postupgrade_fixups.sql
升级完成后可以执行Post-Upgrade Status Tool（$ORACLE_HOME/rdbms/admin/utlu121s.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.
- 1 SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
因不再被12c支持，可以移除OLAP Catalog组件。- 1 SQL> @?/rdbms/admin/utlu121s.sql
执行catuppst.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<br>
- 1 SQL> @?/olap/admin/catnoamd.sql
4.9、完成升级阶段
4.9.1、检查环境变量
- ORACLE_BASE
- ORACLE_HOME
- PATH,LD_LIBRARY_PATH and SHLIB_PATH
- 更新oratab文件
4.9.2、检查初始化参数文件
确认初始化参数修改正确。
4.9.3、检查口令文件
可以使用orapwd命令重建口令文件。
4.9.4、COMPATIBLE参数
COMPATIBLE参数控制数据库兼容性级别，如果数据库不会再降级到以前的版本，可以设置此参数。- 1 SQL> @?/rdbms/admin/catuppst.sql
4.9.5、升级TIMEZONE文件版本
查看数据库当前timezone 版本：- 1 SQL> @?/rdbms/admin/utlrp.sql
- 1 SQL> alter system set compatible='12.1.0.2.0' scope=spfile;<br>
准备升级timezone到18- 1 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value <br> 2 FROM DATABASE_PROPERTIES
- 3 WHERE PROPERTY_NAME LIKE 'DST_%'
- 4 ORDER BY PROPERTY_NAME;
- 5 PROPERTY_NAME VALUE
- 6 --------------------------- ---------------------------
- 7 DST_PRIMARY_TT_VERSION 11 <br> 8 DST_SECONDARY_TT_VERSION 0
- 9 DST_UPGRADE_STATE NONE<br><br>
- 1 SQL> SELECT version FROM v$timezone_file;
- 2
- 3 VERSION
- 4 ----------
- 5 11
- 1 SQL> set serveroutput on
- 2 SQL> exec DBMS_DST.BEGIN_PREPARE(18);
- 1 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
- 2 2 FROM DATABASE_PROPERTIES
- 3 3 WHERE PROPERTY_NAME LIKE 'DST_%'
- 4 4 ORDER BY PROPERTY_NAME;
- 5
- 6 PROPERTY_NAME VALUE
- 7 ----------------------------- --------------------------
- 8 DST_PRIMARY_TT_VERSION 11
- 9 DST_SECONDARY_TT_VERSION 18
- 10 DST_UPGRADE_STATE PREPARE<br>
- 1 SQL> BEGIN
- 2 2 DBMS_DST.FIND_AFFECTED_TABLES
- 3 3 (affected_tables => 'sys.dst$affected_tables',
- 4 4 log_errors => TRUE,
- 5 5 log_errors_table => 'sys.dst$error_table');
- 6 6 END;
- 7 7 /
- 8
- 9 PL/SQL procedure successfully completed.
- 10
- 11 SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
- 12
- 13 Table truncated.
- 14
- 15 SQL> TRUNCATE TABLE sys.dst$affected_tables;
- 16
- 17 Table truncated.
- 18
- 19 SQL> TRUNCATE TABLE sys.dst$error_table;
- 20
- 21 Table truncated.
- 1 SQL> EXEC DBMS_DST.END_PREPARE;
- 2
- 3 PL/SQL procedure successfully completed.
- 1 SQL> shutdown immediate;
- 2 Database closed.
- 3 Database dismounted.
- 4 ORACLE instance shut down.
- 5
- 6 SQL> startup upgrade;
- 7 ORACLE instance started.
- 8
- 9 Total System Global Area 1610612736 bytes
- 10 Fixed Size 2924928 bytes
- 11 Variable Size 671092352 bytes
- 12 Database Buffers 922746880 bytes
- 13 Redo Buffers 13848576 bytes
- 14 Database mounted.
- 15 Database opened.
- 16
- 17 SQL> set serveroutput on
- 18 SQL> purge dba_recyclebin;
- 19
- 20 DBA Recyclebin purged.
- 21
- 22 SQL> alter session set "_with_subquery"=materialize;
- 23
- 24 Session altered.
- 25
- 26 SQL> EXEC DBMS_DST.BEGIN_UPGRADE(18);
- 27 An upgrade window has been successfully started.
- 28
- 29 PL/SQL procedure successfully completed.
- 30
- 31 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
- 32 2 FROM DATABASE_PROPERTIES
- 33 3 WHERE PROPERTY_NAME LIKE 'DST_%'
- 34 4 ORDER BY PROPERTY_NAME;
- 35
- 36 PROPERTY_NAME VALUE
- 37 -------------------------------------- --------------------------------------
- 38 DST_PRIMARY_TT_VERSION 18
- 39 DST_SECONDARY_TT_VERSION 11
- 40 DST_UPGRADE_STATE UPGRADE
- 1 SQL> shutdown immediate;
- 2 Database closed.
- 3 Database dismounted.
- 4 ORACLE instance shut down.
- 5 SQL>
- 6 SQL> startup
- 7 ORACLE instance started.
- 8
- 9 Total System Global Area 1610612736 bytes
- 10 Fixed Size 2924928 bytes
- 11 Variable Size 671092352 bytes
- 12 Database Buffers 922746880 bytes
- 13 Redo Buffers 13848576 bytes
- 14 Database mounted.
- 15 Database opened.
- 16 SQL> alter session set "_with_subquery"=materialize;
- 17
- 18 Session altered.
- 19 执行timezone升级过程：
- 20 SQL> set serveroutput on
- 21 SQL> VAR numfail number
- 22 SQL> BEGIN
- 23 2 DBMS_DST.UPGRADE_DATABASE(:numfail,
- 24 3 parallel => TRUE,
- 25 4 log_errors => TRUE,
- 26 5 log_errors_table => 'SYS.DST$ERROR_TABLE',
- 27 6 log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
- 28 7 error_on_overlap_time => FALSE,
- 29 8 error_on_nonexisting_time => FALSE);
- 30 9 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
- 31 10 END;
- 32 11 /
- 33 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
- 34 Number of failures: 0
- 35 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
- 36 Number of failures: 0
- 37 Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
- 38 Number of failures: 0
- 39 Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
- 40 Number of failures: 0
- 41 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
- 42 Number of failures: 0
- 43 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
- 44 Number of failures: 0
- 45 Table list: "APEX_040200"."WWV_FLOW_WORKSHEET_NOTIFY"
- 46 Number of failures: 0
- 47 Table list: "APEX_040200"."WWV_FLOW_FEEDBACK_FOLLOWUP"
- 48 Number of failures: 0
- 49 Table list: "APEX_040200"."WWV_FLOW_FEEDBACK"
- 50 Number of failures: 0
- 51 Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES2"
- 52 Number of failures: 0
- 53 Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES"
- 54 Number of failures: 0
- 55 Failures:0
- 56
- 57 PL/SQL procedure successfully completed.
- 58 结束升级，校验升级信息：
- 59 SQL> VAR fail number
- 60 SQL> BEGIN
- 61 2 DBMS_DST.END_UPGRADE(:fail);
- 62 3 DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
- 63 4 END;
- 64 5 /
- 65 An upgrade window has been successfully ended.
- 66 Failures:0
- 67
- 68 PL/SQL procedure successfully completed.
如果当前库上有RMAN Catalog，可以使用UPGRADE CATALOG进行升级。
参考：https://docs.oracle.com/database/121/BRADV/rcmcatdb.htm#BRADV188
4.9.7、为Oracle XML DB配置FTP和HTTP端口以及HTTP身份验证
Oracle Database 12c DBCA不为Oracle XML DB配置端口，因此您必须手动配置它们。您还应该为HTTP配置摘要身份验证，以利用改进的安全功能。
4.9.8、启用Database Vault
Register Database Vault by using the DVSYS.DBMS_MACADM.ENABLE_DV procedure.
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
