Oracle 11.2.0.4 升级到12.1.0.2 测试

绝地灬酷狼 2022-06-05 07:07 207阅读 0赞

一、说明  
大致步骤如下：  
  
1.检查升级条件  
  
2.安装12c 数据库  
  
3.执行DBUA 升级DB，当然，也可以采用手工执行命令的方式，但根据官方资料看，建议放弃这个想法，DBUA 真的简化了很多，如果手工执行，会多很多步骤，这样会增加出错的概率。  
  
4.检查数据库状态和无效对象。  
  
详细说明可以参考MOS：  
  
Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (文档 ID 1503653.1)  
  
具体操作如下。

二、升级路线说明

直接升级到12c和间接升级到12c 的路线如下图：

![Center][]

三、检查当前11g 数据库  
  
从MOS上看，升级的过程，还是有些bug，所以oracle 建议在升级之前对源库做些检查。 包含内容如下：  
  
1.冷备或者热备数据库，Oracle 建议冷备。  
  
2.确保Oracle 所有组件都是有效的。  
  
3.确保SYS和SYSTEM 用户下没有重名对象。 这种情况是可能存在的，因为oracle 里有namespace的概念，只要namespace 不同，对象名可以相同。  
  
4.禁用任何用户的触发，等升级完成之后，再启用。

5.留意Bug 20369415 - UPGRADE TO 12C FAILS - XDB ERROR ORA-1830 ORA-6512: AT "SYS.XS\_OBJECT\_MIGRATION"

![Center 1][]

当然，12c 考虑到升级的方便性，可以直接从MOS上下载dbupgdiag.sql脚本，该脚本可以完成上述的检测内容。  
Note 556610.1  Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

SQL> @dbupgdiag.sql  
  
...  
  
PL/SQL procedure successfully completed.  
  
  
                \*\*\* End of LogFile \*\*\*  
  
  
Enter value for log\_path: /tmp  
  
Upload db\_upg\_diag\_cebpm\_14\_Nov\_2017\_1240.log from "/tmp" directory  
  
注意看log，如果检测到无效对象，需要运行$ORACLE\_HOME/rdbms/admin/utlrp.sql脚本,修复无效对象，如果有需要可以多次执行。

四、安装12c 软件  
正如最开始所说,我们需要把12c安装到新的ORACLE\_HOME，然后执行DBUA完成升级。  
  
Oracle 12c软件安装具体过程这里就不再说明。注意：安装结束，执行root.sh，完成安装.(这里仅仅是安装软件，不需要创建实例)

五、升级DB前的准备工作  
  
执行Pre-Upgrade 脚本，如果是一次性完成DB安装和库的升级，那么就需要从MOS上下载：  
  
How to Download and Run Oracle's Database Pre-Upgrade Utility (Doc ID 884522.1)

\--这是解压缩：

test:/u01/src@oracle>unzip preupgrade\_12.1.0.2.0\_17\_lf.zip  
Archive:  preupgrade\_12.1.0.2.0\_17\_lf.zip  
  inflating: preupgrd.sql              
  inflating: utluppkg.sql              
test:/u01/src@oracle>

在安装完12c 的DB 软件之后，12c的目录里也带这2个脚本。

test:/u01/app/oracle/product/12.1.0/db\_1/rdbms/admin@oracle>ll preupgrd.sql utluppkg.sql  
\-rw-r--r-- 1 oracle dba  14083 5月  15 2014 preupgrd.sql  
\-rw-r--r-- 1 oracle dba 495482 6月  22 2014 utluppkg.sql

这里我们直接执行。

SQL> @$ORACLE\_HOME/rdbms/admin/preupgrd.sql

Loading Pre-Upgrade Package... *************************************************************************** Executing Pre-Upgrade Checks in TEST... *************************************************************************** ************************************************************ ====>> ERRORS FOUND for TEST <<==== 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 TEST <<==== ACTIONS REQUIRED: 1. Review results of the pre-upgrade checks: /u01/app/oracle/cfgtoollogs/test/preupgrade/preupgrade.log 2. Execute in the SOURCE environment BEFORE upgrade: /u01/app/oracle/cfgtoollogs/test/preupgrade/preupgrade_fixups.sql 3. Execute in the NEW environment AFTER upgrade: /u01/app/oracle/cfgtoollogs/test/preupgrade/postupgrade_fixups.sql ************************************************************ *************************************************************************** Pre-Upgrade Checks in TEST Completed. *************************************************************************** *************************************************************************** *************************************************************************** SQL> @/u01/app/oracle/cfgtoollogs/test/preupgrade/preupgrade_fixups.sql Pre-Upgrade Fixup Script Generated on 2017-11-20 17:17:55 Version: 12.1.0.2 Build: 006 Beginning Pre-Upgrade Fixups... Executing in container TEST ********************************************************************** Check Tag: DEFAULT_PROCESS_COUNT Check Summary: Verify min process count is not too low Fix Summary: Review and increase if needed, your PROCESSES value. ********************************************************************** Fixup Returned Information: WARNING: --> Process Count may be too low Database has a maximum process count of 150 which is lower than the default value of 300 for this release. You should update your processes value prior to the upgrade to a value of at least 300. For example: ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE or update your init.ora file. ********************************************************************** ********************************************************************** 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.12 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 ^^^ ***************************************** *********** Hidden Parameters *********** ***************************************** Please review and remove any unnecessary hidden/underscore parameters prior to upgrading. It is strongly recommended that these be removed before upgrade unless your application vendors and/or Oracle Support state differently. Changes will need to be made in the init.ora or spfile. ******** Existing Hidden Parameters ******** _diag_adr_enabled = TRUE ^^^ MANUAL ACTION SUGGESTED ^^^ ************************************************** ************* Fixup Summary ************ 1 fixup routine was successful. 2 fixup routines returned INFORMATIONAL text that should be reviewed. **************** Pre-Upgrade Fixup Script Complete *********************

这里一定要查看/u01/app/oracle/cfgtoollogs/test/preupgrade/preupgrade.log里的内容，内容如下：

Oracle Database Pre-Upgrade Information Tool 11-20-2017 17:30:38 Script Version: 12.1.0.2.0 Build: 006 ********************************************************************** Database Name: TEST Container Name: Not Applicable in Pre-12.1 database Container ID: Not Applicable in Pre-12.1 database Version: 11.2.0.4.0 Compatible: 11.2.0.4.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone file: V14 ********************************************************************** [Update parameters] [No parameters to update] ********************************************************************** ********************************************************************** [Renamed Parameters] [No Renamed Parameters in use] ********************************************************************** ********************************************************************** [Obsolete/Deprecated Parameters] [No Obsolete or Desupported Parameters in use] ********************************************************************** [Component List] ********************************************************************** --> Oracle Catalog Views [upgrade] VALID --> Oracle Packages and Types [upgrade] VALID --> JServer JAVA Virtual Machine [upgrade] VALID --> Oracle XDK for Java [upgrade] VALID --> Oracle Workspace Manager [upgrade] VALID --> OLAP Analytic Workspace [upgrade] VALID --> Oracle Text [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Oracle Multimedia [upgrade] VALID --> Oracle Spatial [upgrade] VALID --> Expression Filter [upgrade] VALID --> Rule Manager [upgrade] VALID --> Oracle Application Express [upgrade] VALID --> Oracle OLAP API [upgrade] VALID ********************************************************************** [Tablespaces] ********************************************************************** --> SYSTEM tablespace is adequate for the upgrade. minimum required size: 1163 MB --> SYSAUX tablespace is adequate for the upgrade. minimum required size: 1366 MB --> UNDOTBS1 tablespace is adequate for the upgrade. minimum required size: 400 MB --> TEMP tablespace is adequate for the upgrade. minimum required size: 60 MB [No adjustments recommended] ********************************************************************** ********************************************************************** [Pre-Upgrade Checks] ********************************************************************** 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 11.2.0.4.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. INFORMATION: --> There are existing Oracle components that will NOT be upgraded by the database upgrade script. Typically, such components have their own upgrade scripts, are deprecated, or obsolete. Those components are: OWB INFORMATION: --> Oracle Application Express (APEX) can be manually upgraded prior to database upgrade APEX is currently at version 3.2.1.00.12 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 ^^^ ***************************************** *********** Hidden Parameters *********** ***************************************** Please review and remove any unnecessary hidden/underscore parameters prior to upgrading. It is strongly recommended that these be removed before upgrade unless your application vendors and/or Oracle Support state differently. Changes will need to be made in the init.ora or spfile. _diag_adr_enabled = TRUE ^^^ MANUAL ACTION SUGGESTED ^^^ ********************************************************************** [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 ^^^ ********************************************************************** ************ Summary ************ 0 ERRORS exist in your database. 0 WARNINGS exist in your database. 3 INFORMATIONAL messages that should be reviewed prior to your upgrade. After your database is upgraded and open in normal mode you must run rdbms/admin/catuppst.sql which executes several required tasks and completes the upgrade process. You should follow that with the execution of rdbms/admin/utlrp.sql, and a comparison of invalid objects before and after the upgrade using rdbms/admin/utluiobj.sql If needed you may want to upgrade your timezone data using the process described in My Oracle Support note 1509653.1 ***********************************

主要修改如下：修改完成后，重启DB，让修改生效。

SQL> ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;  
SQL> EXECUTE dbms\_stats.gather\_dictionary\_stats;

如果你要升级的数据库开启过em，则还要执行下面的脚本：

SQL>@/u01/app/oracle/product/12.1.0/db\_1/rdbms/admin/emremove.sql

另外还要修改下面的参数:

SQL> alter system set "\_diag\_adr\_enabled"=true;

SQL> @$ORACLE\_HOME/olap/admin/catnoamd.sql

否则会遇到bug 13499432：

'ORA-04063: View "SYS.V\_$DIAG\_VIPS\_PACKAGE\_MAIN\_INT" Has Errors' During Upgrade (文档 ID 1674422.1)

![Center 2][]

另外Oracle 建议在升级12c之前手工升级APEX，这样可以减少停机时间。升级参考MOS：1088970.1。 看升级过程，在使用PCIE 闪存卡，16G 内存的情况下APEX耗时18分钟。

六、安装12c 的数据库并执行DBUA

test:/home/oracle@oracle>export DISPLAY=:IP:0.0  
test:/home/oracle@oracle>cd /u01/app/oracle/product/12.1.0/db\_1/bin/  
test:/u01/app/oracle/product/12.1.0/db\_1/bin@oracle>./dbua

![Center 3][]

由于测试环境里面有2个实例，这里我们选择要升级的test实例。

![Center 4][]

![Center 5][]

![Center 6][]

![Center 7][]

test:/home/oracle@oracle>mkdir -p /home/oracle/oradata/12ctest/  
  
注意：如果需要Move database，那么目录一定要和之前的不同，否则会升级失败。  
  
这里演示的是移动了数据文件，实际上在升级的时候，没必要进行MOVE，因为升级完之后，还是NON CDB，后期还需要plug 到CDB中，到时还有一次copy的过程。

![Center 8][]

注意：这里的监听我们是从12c的环境下启动。

![Center 9][]

![Center 10][]

![Center 11][]

开始升级

![Center 12][]

升级完成

![Center 13][]

查看Upgrade Results:

![Center 14][]

![Center 15][]

七、执行Postupgrade 脚本

SQL> @/u01/app/oracle/cfgtoollogs/test/preupgrade/postupgrade_fixups.sql Post Upgrade Fixup Script Generated on 2017-11-20 17:30: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.

八、其他善后工作和检查工作  
  
8.1 环境变量修改

test:/home/oracle@oracle>echo $ORACLE_HOME
    /u01/app/oracle/product/12.1.0/db_1

8.2 将实例切换成CDB  
  
安装完成之后，之前的数据库还是NON CDB 架构的，需要切换成CDB架构。

SQL> select name,cdb from v$database;
    
    NAME      CDB
    --------- ---
    TEST      NO

在操作之前我们先用DBCA创建一个空的CDB,然后把test 实例迁入到CDB中。  
  
具体操作过程参考：  
  
http://blog.csdn.net/shiyu1157758655/article/details/78592265

SQL> show pdbs
    
        CON_ID CON_NAME              OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
         2 PDB$SEED              READ ONLY  NO
         3 TEST               READ WRITE NO

我这里采用的是COPY的方式，所以完成之后，可以把之前的目录全干掉。  
  
8.3 检查兼容性

SQL> show pdbs
    
        CON_ID CON_NAME              OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
         2 PDB$SEED              READ ONLY  NO
         3 TEST               READ WRITE NO
    
    SQL> alter session set  container=TEST;
    
    Session altered.
    
    SQL> show parameter compa
    
    NAME                     TYPE     VALUE
    ------------------------------------ ----------- ------------------------------
    cell_offload_compaction          string     ADAPTIVE
    compatible                 string     12.1.0.2.0
    noncdb_compatible             boolean     FALSE
    plsql_v2_compatibility             boolean     FALSE

8.4 检查组件状态

SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
    
    COMP_NAME					   VERSION			  STATUS
    -------------------------------------------------- ------------------------------ --------------------------------------------
    Oracle Application Express			   4.2.5.00.08			  VALID
    OWB						   11.2.0.4.0			  VALID
    Spatial 					   12.1.0.2.0			  VALID
    Oracle Multimedia				   12.1.0.2.0			  VALID
    Oracle XML Database				   12.1.0.2.0			  VALID
    Oracle Text					   12.1.0.2.0			  VALID
    Oracle Workspace Manager			   12.1.0.2.0			  VALID
    Oracle Database Catalog Views			   12.1.0.2.0			  VALID
    Oracle Database Packages and Types		   12.1.0.2.0			  VALID
    JServer JAVA Virtual Machine			   12.1.0.2.0			  VALID
    Oracle XDK					   12.1.0.2.0			  VALID
    
    COMP_NAME					   VERSION			  STATUS
    -------------------------------------------------- ------------------------------ --------------------------------------------
    Oracle Database Java Packages			   12.1.0.2.0			  VALID
    OLAP Analytic Workspace 			   12.1.0.2.0			  VALID
    Oracle OLAP API 				   12.1.0.2.0			  VALID
    
    14 rows selected.

8.5 检查TimeZone 版本

因为在使用DBUA 升级的时候，勾选了upgrate TimeZone Data。 所以也自动升级了：
    
    SQL> SELECT NAME,VALUE$ FROM PROPS$ WHERE NAME='DST_PRIMARY_TT_VERSION';
     
    NAME                           VALUE$
    ------------------------------ --------------------
    DST_PRIMARY_TT_VERSION         18
     
    SQL> SELECT version FROM v$timezone_file;
     
       VERSION
    ----------
            18
     
    1 row selected.

注意：  
  
在12c 中timezone 版本是18。  
  
在Oracle 11g中timezone 最高版本是14，我们升级的时候，就是14。  
  
11.2.0.1.0的timezone最高支持到11，如果要升级到11.2.0.3.0，必须要将timezone升级到14.

此次试验参考了DAVA的文档，在此表示感谢。

[Center]: /images/20220605/31161429f6804929891d1d05ba351e9c.png
[Center 1]: /images/20220605/5002be1f731e4b74b6e4d8b50d31c777.png
[Center 2]: /images/20220605/f607c73f9edf483c8818cfcedb91737d.png
[Center 3]: /images/20220605/be4de6b613b94f79ad2f5281b30ffa7a.png
[Center 4]: /images/20220605/ca8f9c501f7e4316915b2c296bd8393e.png
[Center 5]: /images/20220605/e5dcafb4ea5147efbe32a034d9d15904.png
[Center 6]: /images/20220605/95fe09d0905d4c0195511c1b4aa97fb3.png
[Center 7]: /images/20220605/c09af8c74e484c3685d6f34eee249d08.png
[Center 8]: /images/20220605/c4322706c2a94be5b02096c224fc23b1.png
[Center 9]: /images/20220605/0d1fae1deadc4496850e2ffc4103e3d5.png
[Center 10]: /images/20220605/47b4326606f44273b13d79a1b9b02b89.png
[Center 11]: /images/20220605/0d8f52e9ea704e6eaa40e402f4331733.png
[Center 12]: /images/20220605/7abfc58f4c8c4288b2881f122da1b9fd.png
[Center 13]: /images/20220605/4381bdbf07284fc7bfbafab15e7ef895.png
[Center 14]: /images/20220605/6bd7aa91361a4413afe4283b1839806a.png
[Center 15]: /images/20220605/f622775f9c4d4082af3ef9dcff5351b5.png