CNDBA社区
1 说明
在之前的Blog中结束过oracle 生命周期的概念，参考：
Oracle 生命周期 说明
http://www.cndba.cn/dave/article/130
Oracle 10g 以后的升级，步骤都差不多，实验之前，翻看了下官方手册和MOS上升级的内容。 步骤没有变化。
大致步骤如下：
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)
具体操作如下。
2 升级路线说明
直接升级到12c和间接升级到12c 的路线如下图：
3 检查当前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"。
当然，12c 考虑到升级的方便性，可以直接从MOS上下载dbupgdiag.sql脚本，该脚本可以完成上述的检测内容。
Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)
如果检测到无效对象，运行$ORACLE_HOME/rdbms/admin/utlrp.sql脚本，修复无效对象，如果有需要可以多次执行。
SQL> @dbupgdiag.sql ……刷屏…… ……几分钟……
注意看log，如有无效对象，需要运行脚本。
4 安装12c 软件
正如最开始所说，我们需要把12c 安装到新的ORACLE_HOME，然后执行DBUA完成升级。 当然也可以一次搞定，但根据多次失败的经验来说，分开，更靠谱点。建议。
创建新目录：
[oracle@ahzhixin ~]$ echo $ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1 [oracle@ahzhixin ~]$ mkdir -p /u01/app/oracle/product/12.1.0/db_1 运行runInstaller。 [oracle@ahzhixin database]$ pwd /u01/software/database [oracle@ahzhixin database]$ ls install readme.html response rpm runInstaller sshsetup stage welcome.html [oracle@ahzhixin database]$ ./runInstaller Starting Oracle Universal Installer... Checking Temp space: must be greater than 500 MB. Actual 8946 MB Passed Checking swap space: must be greater than 150 MB. Actual 1867 MB Passed Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-11-04_03-52-08PM. Please wait ... ……
安装结束，执行root.sh，完成安装。
5 升级DB前的准备工作
执行Pre-Upgrade 脚本，如果是一次性完成DB安装和库的升级，那么就需要从MOS上下载：
Note 884522.1 How to Download and Run Oracle’s Database Pre-Upgrade Utility
--这是解压缩：
[oracle@ahzhixin ~]$ unzip preupgrade_12.1.0.2.0_10_lf.zip Archive: preupgrade_12.1.0.2.0_10_lf.zip inflating: utluppkg.sql inflating: preupgrd.sql
在安装完12c 的DB 软件之后，12c的目录里也带这2个脚本。 我们这里直接安装：
SQL> @/u01/app/oracle/product/12.1.0/db_1/rdbms/admin/preupgrd.sql
Loading Pre-Upgrade Package...
***************************************************************************
Executing Pre-Upgrade Checks in DAVE...
***************************************************************************
************************************************************
====>> ERRORS FOUND for DAVE <<====
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 DAVE <<====
ACTIONS REQUIRED:
1. Review results of the pre-upgrade checks:
/u01/app/oracle/cfgtoollogs/dave/preupgrade/preupgrade.log
2. Execute in the SOURCE environment BEFORE upgrade:
/u01/app/oracle/cfgtoollogs/dave/preupgrade/preupgrade_fixups.sql
3. Execute in the NEW environment AFTER upgrade:
/u01/app/oracle/cfgtoollogs/dave/preupgrade/postupgrade_fixups.sql
************************************************************
***************************************************************************
Pre-Upgrade Checks in DAVE Completed.
***************************************************************************
***************************************************************************
***************************************************************************
SQL> @/u01/app/oracle/cfgtoollogs/dave/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2015-11-05 23:36:57 Version: 12.1.0.2 Build: 006
Beginning Pre-Upgrade Fixups...
Executing in container DAVE
**********************************************************************
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: 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: 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.
3 fixup routines returned INFORMATIONAL text that should be reviewed.
**************** Pre-Upgrade Fixup Script Complete *********************
SQL> ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
2 ;
SQL> @/u01/app/oracle/product/12.1.0/db_1/rdbms/admin/emremove.sql
old 69: IF (upper('&LOGGING') = 'VERBOSE')
new 69: IF (upper('VERBOSE') = 'VERBOSE')
This script will drop the Oracle Enterprise Manager related schemas and objects.
This script might take few minutes to complete; it has 6 phases to complete the process.
The script may take longer if you have SYSMAN and related sessions are active
from Oracle Enterprise Manager(OEM) application.
Recommendations:
You are recommended to shutdown DB Control application immediately before running this
OEM repository removal script.
To shutdown DB Control application, you need to run: emctl stop dbconsole
Steps to be performed manually (after this script is run):
Please note that you need to remove the DB Control Configuration Files
manually to remove DB Control completly; remove the following
directories from your filesystem:
<ORACLE_HOME>/<hostname_sid>
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>
If the dbcontrol is upgraded from lower version, for example, from 10.2.0.3 to 10.2.0.4,
then the following directory also needs to be removed from the file system.
<ORACLE_HOME>/<hostname_sid>.upgrade
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>.upgrade
On Microsoft platforms, also delete the DB Console service, generally with name
OracleDBConsole<sid>
Starting phase 1 : Dropping AQ related objests, EM jobs and all Oracle Enterprise Manager related schemas; except SYSMAN ...
dropping AQ related objests from SYSMAN ...
saved job_queue_process=1000, set to 0, now removing Oracle EM jobs ...
finding users who needs to be dropped ...
found user name: MGMT_VIEW
found [sql%notfound]: no session found; or already killed.
Dropping user : "MGMT_VIEW"...
Finished phase 1
Starting phase 2 : Dropping SYSMAN schema ...
found [sql%notfound]: SYSMAN related sessions are already killed; no session found
dropping user : MGMT_VIEW...
SYSMAN dropped
Finished phase 3
Starting phase 4 : Dropping Oracle Enterprise Manager related MGMT_USER role ...
Finished phase 4
Starting phase 5 : Dropping Oracle Enterprise Manager related public synonyms ...
Dropping synonym : ECM_UTIL ...
Dropping synonym : EMD_MNTR ...
Dropping synonym : MGMT$ALERT_ANNOTATIONS ...
Dropping synonym : MGMT$ALERT_CURRENT ...
Dropping synonym : MGMT$ALERT_HISTORY ...
Dropping synonym : MGMT$ALERT_NOTIF_LOG ...
……
Dropping synonym : SMP_EMD_STRING_ARRAY_ARRAY ...
Dropping synonym : SMP_EMD_TARGET_OBJ ...
Dropping synonym : SMP_EMD_TARGET_OBJ_ARRAY ...
Finished phase 5
Starting phase 6 : Dropping Oracle Enterprise Manager related other roles ...
Finished phase 6
The Oracle Enterprise Manager related schemas and objects are dropped.
Do the manual steps to studown the DB Control if not done before running this script and then delete the DB Control configuration files
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
主要修改如下：修改完成后，重启DB，让修改生效。
EXECUTE dbms_stats.gather_dictionary_stats; ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE $ORACLE_HOME/olap/admin/catnoamd.sql
另外需要修改参数：
alter system set "_diag_adr_enabled"=true; @/u01/app/oracle/product/12.1.0/db_1/rdbms/admin/catadrvw.sql
否则会遇到bug 13499432：
'ORA-04063: View "SYS.V_$DIAG_VIPS_PACKAGE_MAIN_INT" Has Errors' During Upgrade (文档 ID 1674422.1)
Oracle 建议在升级12c之前手工升级APEX，这样可以减少停机时间。升级参考MOS：1088970.1。 看升级过程，在使用PCIE 闪存卡，16G 内存的情况下APEX耗时18分钟。
6 安装12c 的数据库并执行DBUA
[oracle@dave database]$ cd /u01/app/oracle/product/12.1.0/db_1/bin [oracle@dave bin]$ ./dbua
[oracle@dave trace]$ mkdir -p /u01/app/oracle/12coradata [oracle@dave trace]$ mkdir -p /u01/app/oracle/12cfast_recovery_area
注意：如果需要Move database，那么目录一定要和之前的不同，否则会升级失败。
这里演示的是移动了数据文件，实际上在升级的时候，没必要进行MOVE，因为升级完之后，还是NON CDB，后期还需要plug 到CDB中，到时还有一次copy的过程。
这些错误是安装中跳过的，
7 执行Postupgrade 脚本
执行postupgrade 脚本：
SQL> @/u01/app/oracle/cfgtoollogs/dave/preupgrade/postupgrade_fixups.sql Post Upgrade Fixup Script Generated on 2015-11-05 23:36:57 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. SQL>
8 其他善后和检查工作
8.1 修改环境变量
[oracle@dave dbs]$ echo $ORACLE_HOME /u01/app/oracle/product/12.1.0/db_1 [oracle@dave dbs]$
8.2 将实例切换成CDB
安装完成之后，之前的数据库还是NON CDB 架构的，需要切换成CDB架构。
SQL> select name,cdb from v$database; NAME CDB --------- --- DAVE NO
在操作之前我们先用DBCA创建一个空的CDB,然后把DAVE 实例迁入到CDB中。
具体操作过程参考：
Oracle 12c Non CDB 数据库 切换成 CDB 操作示例
http://www.cndba.cn/dave/article/220
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 DAVE READ WRITE NO
我这里采用的是COPY的方式，所以完成之后，可以把之前的目录全干掉。
8.3 检查/etc/oratab
升级完成之后，会自动修改/etc/oratab 文件，准确点，是先删了，后添加上去的：
[oracle@dave dbs]$ cat /etc/oratab |grep -v "#" |grep -v "^$" dave:/u01/app/oracle/product/12.1.0/db_1:Y
8.4 检查兼容性
SQL> alter session set container=DAVE; 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 SQL>
8.5 检查组件状态
SQL> col comp_name for a50 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 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.6 检查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.
8.7 注意升级的时间
这里的测试环境是16G 内存，存储采用的是PCIE SSD 闪存卡， DBUA的执行时间是40分钟，如果是普通环境，那么可能需要更久的时间， 根据之前几次失败的经验，在普通虚拟机，分配4G内存的情况下，至少需要2个小时，所以想升级的，耐心等吧。
版权声明：本文为博主原创文章，未经博主允许不得转载。
