Oracle 12c升级指南 / 开普饭

1. 概述升级路线图

从18c开始，如果想要直接升级到Oracle 18c，对于源库版本要求越来越高了。Oracle已经彻底放弃了Oracle 11.2.0.3之前版本直接升级到18c。具体升级路线，请查看下表当前版本号说明12.2.0.1,12.1.0.1, 12.1.0.211.2.0.3, 11.2.0.4支持直接升级到18c11.2.0.1, 11.2.0.211.1.0.6, 11.1.0.710.2.0.2,10.2.0.3, 10.2.0.4 和10.2.0.510.1.0.59.2.0.8及更早版本不支持直接升级到18c.解决方法：1）只能先升级到支持直接升级到18c的中间版本，然后再次升级到18c2）使用数据泵直接将数据迁移到新版本数据库中源库过度版本目标数据库版本11.2.0.1/11.2.0.2-->11.2.0.3/11.2.0.4-->18.x11.1.0.6/11.1.0.7-->11.2.0.3/11.2.0.4-->18.x10.2.0.2, 10.2.0.3, 10.2.0.4, 10.2.0.5-->11.2.0.3/11.2.0.4/12.1.0.1/12.1.0.2-->18.x10.1.0.5-->11.2.0.3/11.2.0.4/12.1.0.1/12.1.0.2-->18.x9.2.0.8 or earlier-->11.2.0.3/11.2.0.4-->18.xOracle版本发布与支持时间

参考文档Oracle 12cR1 Upgrade Companion (文档 ID 1462240.1)Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (文档 ID 1503653.1)Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA (文档 ID 1516557.1)Release Schedule of Current Database Releases (文档 ID 742060.1)How to Upgrade to Oracle Database 12c Release1 (12.1.0) and Known Issues (文档 ID 2085705.1)Master Note For Oracle Database 12c Release 1 (12.1) Database/Client Installation/Upgrade/Migration Standalone Environment (Non-RAC) (文档 ID 1520299.1)Database Server Upgrade/Downgrade Compatibility Matrix (文档 ID 551141.1)2. 10gR2、11gR1或11gR2升级12cR12.1. 操作系统要求Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Solaris and MS Windows Operating Systems Installation and Configuration Requirements Quick Reference (12.1) (文档 ID 1587357.1)Document 1517948.1 Requirements for Installing Oracle Database 12.1 on Solaris 10 SPARCDocument 1525614.1 Requirements for Installing Oracle Database 12.1 on Solaris 11 SPARCDocument 1529433.1 Requirements for Installing Oracle Database 12.1 on RHEL5 or OL5 64-bit (x86-64)Document 1529864.1 Requirements for Installing Oracle Database 12.1 on RHEL6 or OL6 64-bit (x86-64)Document 1961997.1 Requirements for Installing Oracle Database 12.1 on RHEL7 or OL7 64-bit (x86-64)Document 1519770.1 Requirements for Installing Oracle Database 12.1 64-bit (AMD64/EM64T) on SLES 11Document 1961277.1 The Oracle Database 12c Install Options and the Installed Components2.2. 12.1.0.2重要的补丁包为了避免升级过程中出现问题，安装12.1.0.2版本后打上如下补丁包，避免相关问题20369415、215507772.3. 源库要求和建议1)版本要求根据升级要求，在原数据库上升级，对原数据库的版本要求如下：10gR2不能低于10.2.0.511gR1不能低于11.1.0.711gR2不能低于11.2.0.2PSU补丁：Quick Reference to Patch Numbers for Database/GI PSU, SPU(CPU), Bundle Patches and Patchsets (文档 ID 1454618.1)2)升级前建议做个备份3)确保升级前Oracle系统对象和组件全部是VALID状态。针对组件column comp_name format a40set linesize 140 pagesize 999select comp_name, version, status from dba_registry; 针对对象SQL> create table invalid_objects_20170206 as select * from dba_objects where status = 'INVALID'; 如果有invalid的对象，运行utlrp.sql重新编译对象。确保sys和system下没有重复的对象：4)确保在SYS和SYSTEM用户中没有重复对象，以下对象是可允许的重复对象：column object_name format a30column object_type format a30select object_name, object_type from dba_objects where object_name||object_typein (select object_name||object_type from dba_objects where OWNER = 'SYS') and OWNER= 'SYSTEM';OBJECT_NAME OBJECT_TYPE------------------------------ ------------------------------AQ$_SCHEDULES TABLEAQ$_SCHEDULES_PRIMARY INDEXDBMS_REPCAT_AUTH PACKAGEDBMS_REPCAT_AUTH PACKAGE BODY 如果有其它记录返回，则必须根据下面这篇文档把重复记录删除：How to Clean Up Duplicate Objects Owned by SYS and SYSTEM Schema [ID 1030426.6]5)业务定义的触发器在升级前禁用，在升级完成后再启用6)11g里如果创建了ACL，并且ACL有带时区的时间戳的数据类型，升级后可能会报ORA-01830具体涉及的Bug为：Bug 20369415 – UPGRADE TO 12C FAILS – XDB ERROR ORA-1830 ORA-6512: AT “SYS.XS_OBJECT_MIGRATION”，在升级前需要对Oracle 12.1打补丁20369415。参考：Upgrade to 12.1 fails with ORA-01830 date format picture ends before converting entire input string ORA-06512: at “SYS.XS_OBJECT_MIGRATION” (文档 ID 1958876.1)How to find ACL creation information (文档 ID 1475575.1)7)为了避免升级后datapump使用时报ORA-7445错误，安装Oracle 12c版本后建议打补丁：17325413参考：ORA-7445 [qcsIsColInFro] Querying After Upgrade to 12c (文档 ID 2017572.1)Bug 17325413 – Drop column with DEFAULT value and NOT NULL definition ends up with Dropped Column Data still on Disk leading to Corruption (文档 ID 17325413.8)该问题已在12.1.0.2版本中修复。8)在手工升级前，需要检查并行统计信息收集设置，默认为FALSE，如果已经修改为TRUE了，需要置为FALSESELECT dbms_stats.get_prefs('CONCURRENT') from dual;DBMS_STATS.GET_PREFS('CONCURRENT')------------------------------------------FALSE 如果不是为FALSE，在升级前修改如下：BEGINDBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','FALSE');END;/ 参考：Note 2037154.1 DBMS_STATS.GATHER_DICTIONARY_STATS Fails with “ORA-06502: PL/SQL: numeric or value error: character string buffer too small”2.4. 检查原数据库完整性2.4.1. dbupgdiag检查在升级之前，在原始环境下，执行dbupgdiag.sql脚本，检查原库的完整性。dbupgdiag.sql脚本下载地址：Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)cd <location of the script>$ sqlplus / as sysdbasql> alter session set nls_language='American';sql> @dbupgdiag.sqlsql> exit 如果dbupgdiag.sql报告有INVALID对象，使用utlrp.sql脚本编译，utlrp.sql可以多次执行，直接INVALID对象数目不再变化。$ cd $ORACLE_HOME/rdbms/admin$ sqlplus "/ as sysdba"SQL> @utlrp.sql 处理完，再次执行dbupgdiag.sql，确认没有问题2.4.2. 数据字典检查建议在升级前使用hcheck.sql脚本，做一下health check。参考：Note 136697.1 hcheck.sql” script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11gSQL> @hout.sql Package created.No errors.Package body created.SQL> @hcheck2.sqlPackage created.No errors.Package body created.No errors.HCheck Version 8i-11/2.00Problem: SEG$ bad LISTS/GROUPS (==1) - See Tar:2470806.1May be Ok for LOBSEGMENT/SECUREFILE in release 11gR1+Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=26585 TYPE#=8 Lists=2 Groups=1Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=26769 TYPE#=8 Lists=2 Groups=1Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=26841 TYPE#=8 Lists=2 Groups=1Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=28177 TYPE#=8 Lists=2 Groups=1Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=28217 TYPE#=8 Lists=2 Groups=1Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=30985 TYPE#=8 Lists=2 Groups=1Found 6 potential problems and 0 warningsContact Oracle Support with the outputto check if the above needs attention or notPL/SQL procedure successfully completed. 2.5. 升级前的步骤2.5.1. 执行Database Pre-Upgrade Utility下载preupgrade_12.1.0.2.0_15_lf.zip，参考How to Download and Run Oracle’s Database Pre-Upgrade Utility [ID 884522.1]，解压后得到：preupgrd.sql、utluppkg.sql，把这两个文件复制到原库的$ORACLE_HOME/rdbms/admin目录。$ sqlplus "/ as sysdba"SQL> @$ORACLE_HOME/rdbms/admin/preupgrd.sql 在$ORACLE_BASE/cfgtoollogs/$ORACLE_SID/preupgrade/目录下，生成preupgrade.log, preupgrade_fixups.sql和 postupgrade_fixups.sql 这几个文件。内容如下：SQL> @$ORACLE_HOME/rdbms/admin/preupgrd.sqlLoading 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. You MUST resolve the above errors prior to upgrade ************************************************************ ************************************************************ ====>> PRE-UPGRADE RESULTS for TEST <<====ACTIONS REQUIRED:1. Review results of the pre-upgrade checks: /data/oracle/cfgtoollogs/test/preupgrade/preupgrade.log2. Execute in the SOURCE environment BEFORE upgrade: /data/oracle/cfgtoollogs/test/preupgrade/preupgrade_fixups.sql3. Execute in the NEW environment AFTER upgrade: /data/oracle/cfgtoollogs/test/preupgrade/postupgrade_fixups.sql ***************************************************************************************************************************************Pre-Upgrade Checks in TEST Completed.********************************************************************************************************************************************************************************************************************************* 建议关注：/data/oracle/cfgtoollogs/test/preupgrade/preupgrade.log 在11g原始环境中执行：/data/oracle/cfgtoollogs/test/preupgrade/preupgrade_fixups.sql脚本，升级后在12c环境下执行postupgrade_fixups.sql脚本。原始环境preupgrade_fixups.sql脚本的执行结果如下：SQL> @/data/oracle/cfgtoollogs/test/preupgrade/preupgrade_fixups.sqlPre-Upgrade Fixup Script Generated on 2017-02-07 09:19:47 Version: 12.1.0.2 Build: 015Beginning Pre-Upgrade Fixups...Executing in container TEST**********************************************************************Check Tag: DEFAULT_PROCESS_COUNTCheck Summary: Verify min process count is not too lowFix 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_PRESENTCheck Summary: Check if Enterprise Manager is presentFix 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_EXISTSCheck Summary: Check to see if AMD is present in the databaseFix 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: APEX_UPGRADE_MSGCheck 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 toupgrading the database.To gather dictionary statistics execute the following commandwhile connected as SYSDBA: EXECUTE dbms_stats.gather_dictionary_stats;^^^ MANUAL ACTION SUGGESTED ^^^ ************************************************** ************* Fixup Summary ************ 4 fixup routines generated INFORMATIONAL messages that should be reviewed.**************** Pre-Upgrade Fixup Script Complete *********************PL/SQL procedure successfully completed. 提出的建议如下：11g默认150的PROCESSES太小，建议修改为300发现了Enterprise Manager，在升级过程中会删除EM库，为了减少升级时间，可以先删除。 (emremove.sql脚本从12c环境复制过来)发现了OLAP Catalog组件，在12c中OLAP Catalog组件不再支持，可以在升级前或升级后使用catnoamd.sql脚本删除，这就是不删除了，升级后再说。发现了APEX，APEX版本升级从3.2.1.00.12到4.2.5会占用比较多的时间，Oracle建议可以手工升级，参考：1088970.1，这里也不管了，一起升级建议执行EXECUTE dbms_stats.gather_dictionary_stats;收集数据字典统计信息2.5.2. CONNECT角色中权限的回收9i,10gR1非直接升到12c，会先升到中间版本。从10.2开始，CONNECT角色只包含CREATE SESSION权限，其它权限被回收了。9i中CONNECT角色中包含的权限：SQL> SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='CONNECT';GRANTEE PRIVILEGE------------------------------ ----------------------------------------CONNECT CREATE VIEWCONNECT CREATE TABLECONNECT ALTER SESSIONCONNECT CREATE CLUSTERCONNECT CREATE SESSIONCONNECT CREATE SYNONYMCONNECT CREATE SEQUENCECONNECT CREATE DATABASE LINK8 rows selected. 检查有CONNECT角色的用户：SELECT grantee FROM dba_role_privsWHERE granted_role = 'CONNECT' andgrantee NOT IN ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP','LOGSTDBY_ADMINISTRATOR', 'ORDSYS','ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY','WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS','WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA','SI_INFORMTN_SCHEMA', 'XDB', 'ODM'); 如果需要恢复，请参考Predefined Roles Evolution From 8i to 10gR2: CONNECT Role Change in 10gR2 (Doc ID 317258.1)2.5.3. Access Control Lists 和Network Utility Packages包的说明从Oracle 12c开始，访问控制的UTL包(UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR)使用Real Application Security实现，不再需要Oracle XML DB。参考：http://docs.oracle.com/database/121/UPGRD/preup.htm#BABEDAFB2.5.4. Network Utility Packages包的依赖性SQL> SELECT * FROM DBA_DEPENDENCIESWHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP')AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS'); 升级后，引入了DBMS_NETWORK_ACL_ADMIN包2.5.5. 备份DBLINK创建脚本SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING'''||L.HOST||''''||chr(10)||';' TEXTFROM SYS.LINK$ L, SYS.USER$ UWHERE L.OWNER# = U.USER#; 2.5.6. 检查TIMESTAMP WITH TIMEZONE数据类型Oracle 12cR1默认的time zone文件版本是18。当前库time zone版本SQL> SELECT version FROM v$timezone_file; VERSION---------- 14 参考如下文档，检查是否需要进行DST升级Note 1665676.1 Actions For DST Updates When Upgrading To Or Applying The 12.1.0.2 PatchsetNote 1522719.1 Actions For DST Updates When Upgrading To 12.1.0.1 Base Release2.5.7. 数据字典统计信息收集$ sqlplus "/as sysdba"SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; 2.5.8. 使用emdwgrd工具保留db control文件和数据Oracle 12c不支持Enterprise Manager，所以一般可以不处理2.5.9. 确保物化视图已经全部刷新检查是否还有物化视图正在刷新$ sqlplus '/ as sysdba'SQL> select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ swhere o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8; 如果查出来有数据，参考Note 1442457.1 : During 11g Upgrade, Mview refresh warning其它检查脚本：$ sqlplus '/ as sysdba'SQL> select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;或SQL> select distinct owner, name mview, master_owner master_owner, last_refresh from dba_mview_refresh_times; 2.5.10. 检查没有文件需要介质恢复SQL> select * from v$recover_file; 2.5.11. 确认没有数据文件处理备份模式SQL> select * from v$backup where status!='NOT ACTIVE'; 2.5.12. 确认没有分布式未决事务SQL> select * from dba_2pc_pending; 如果有未决事务，处理如下：select local_tran_id from dba_2pc_pending;execute dbms_transaction.purge_lost_db_entry('');commit; 2.5.13. 升级前清理回收站SQL> PURGE DBA_RECYCLEBIN;2.5.14. 如果有DataGuard，升级前先同步好备库SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)FROM v$parameterWHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%'; 如果有返回结果，则在升级之前，要保证Standby和Primary是处于同步的状态。2.5.15. 禁用批处理和JOB参考：Note 404238.1 : How to Disable an Entry from DBMS_SCHEDULERNote 1335741.1 : How To Stop A Running Job Using DBMS_JOBNote 67695.1 : PROCEDURE DBMS_JOB.BROKEN Specification检查crontab是否有批处理，并禁用2.5.16. 确认SYS、SYSTEM用户处于自已的表空间SQL> select username, default_tablespace from dba_users where username in ('SYS','SYSTEM');USERNAME DEFAULT_TABLESPACE------------------------------ ------------------------------SYSTEM SYSTEMSYS SYSTEM 如果不是，修改如下：alter user sys default tablespace SYSTEM;alter user system default tablespace SYSTEM; 2.5.17. 检查是否有外部SSL用户SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = 'GLOBAL'; 2.5.18. 记住相关文件信息SELECT name FROM v$controlfile;SELECT file_name FROM dba_data_files;SELECT group#, member FROM v$logfile; 2.5.19. 删作Enterprise Manager Database Control信息库Enterprise Manager Database Control在12c中被Oracle Enterprise Manager Express替代，所以就不再需要EMDC信息库。手工删除EMDC的过程如下：从12c的$ORACLE_HOME/rdbms/admin目录中复制emremove.sql脚本到原库的$ORACLE_HOME/rdbms/admin，在升级前执行如下：$emctl stop dbcontrolSQL> @ ?/rdbms/admin/emremove.sql如果以上脚本没有删除EM的信息库，那在catuppst.sql脚本执行阶段会自动删除。如果是使用的Cloud Control，那没有EM信息库，这个步骤就不用执行了。其它：Is it Possible to Validate Invalid Objects related to DBControl Configuration in a Database Upgraded to 12c? (文档 ID 2118740.1)2.5.20. 检查Valut是否使用如果了使用了OLS(Lable Security) 或者DV ( Database Vault)，那在升级前需要执行olspreupgrade.sql脚本，该脚本在12c的$ORACLE_HOME/rdbms/admin目录下，把该脚本从12c复制到原环境的$ORACLE_HOME/rdbms/admin目录下SQL> @ ?/rdbms/admin/olspreupgrade.sql参考：http://docs.oracle.com/database/121/UPGRD/preup.htm#UPGRD600152.5.21. 检查用户和角色是否被占用Oracle 12.1使用了新的用户和角色，如果在原库中已经存在，在升级前需要先删除。运行preupgrade工具检查是否用户名和角色有冲突，如果有冲突，升级过程会报：the upgrade will terminate will with "ORA-01722: invalid number"。2.5.22. 删除不必要的隐含参数检查隐含参数的脚本如下：SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;2.5.23. 检查XDB ACL是否有start_date和end_date属性升级前，执行如下脚本检查：SQL> select aclid, start_date, end_date from xds_ace where start_date is not null;如果检查出有数据，参考以下文档处理Upgrade to 12.1 fails with ORA-01830 date format picture ends before converting entire input string ORA-06512: at "SYS.XS_OBJECT_MIGRATION" (文档 ID 1958876.1)2.5.24. 检查是否应用了Mitigation Patch在原ORACLE_HOEM中应用了Mitigation Patch，会禁用JAVA开发环境，需要在原库中启用java开发环境Connect to the database as a SYSDBA userSQL> exec dbms_java_dev.enable;参考：Database Upgrade failed with Errors “ORA-02290: check constraint (SYS.JAVA_DEV_DISABLED) violated” & “ORA-04045: SYS.DBMS_ISCHED” (文档 ID 1985725.1)2.5.25. 修改或选择数据库字符集在Oracle 12c多租户体系中，容器库（CDB）中的所有可插拔库（PDB），必须满足如下条件：PDB的NLS_CHARACTERSET必须与CDB一致或者是CDB的二进制子集。PDB的NLS_NCHAR_CHARACTERSET必须与CDB一致。如果PDB有Unicode字符集，那推荐CDB的字符集为AL32UTF8。注意：不能使用DBU迁移CDB的字符集。参考：Note 1968706.1 12c Multitenant Container Databases (CDB) and Pluggable Databases (PDB) Character set restrictions / ORA-65116/65119: incompatible database/national character set ( Character set mismatch: PDB character set CDB character set )Note ID 225912.1 [Section E] Changing Or Choosing the Database Character Set ( NLS_CHARACTERSET )2.5.26. 从10g升级的注意事项检查如下内容：SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;DBMS_STATS.GET_PARAM('METHOD_OPT')-------------------------------------------------------------FOR COLUMNS ID SIZE 1 如果返回值是："FOR COLUMNS ID SIZE 1"，升级过程可能碰到问题，处理如下：SQL>exec DBMS_STATS.SET_PARAM('METHOD_OPT','FOR ALL COLUMNS SIZE AUTO');参考：Unpublished BUG 22454765 - CARRYING METHOD_OPT = "FOR COLUMNS ID SIZE 1" FROM 10G WILL BREAK UPGRADE2.5.27. 从11.2.0.3升级的注意事项建议在12.1.0.2的ORACLE_HOME中打上patch 21550777，以避免升级过程中物化视图的相关问题。2.6. 目标库的要求和建议2.6.1. 检查操作系统版本是否经过Oracle认证2.6.2. 安装Oracle 12c的软件、PSU等参考：Things to Consider to Avoid Poor Performance or Wrong Results on 12.1.0.2 (文档 ID 2034610.1)2.6.3. 复制原库的配置文件到12c的ORACLE_HOME参数文件（spfile或pfile）口令文件（orapwsid）2.6.4. 删除废弃初始化参数参考：http://docs.oracle.com/database/121/UPGRD/deprecated.htm#UPGRD60057注意：SEC_CASE_SENSITIVE_LOGON在12.1中已废弃DIAGNOSTIC_DEST参数替换了USER_DUMP_DEST、BACKGROUND_DUMP_DEST因为Bug 8937877, CORE_DUMP_DEST仍旧存在参考： Note 454442.1 11g Install : Understanding about Oracle Base, Oracle Home and Oracle Inventory locations2.6.5. 修改CLUSTER_DATABASE=FALSE如果是RAC，升级前设置CLUSTER_DATABASE=FALSE，升级后再恢复2.6.6. 检查参数文件中使用的是全路径，而不是相对路径2.6.7. 停止原库的监听$ lsnrctl stop2.6.8. 在12.1环境下创建新的监听2.6.9. 停止其它服务如：dbconsole、isqlplus等等$ emctl stop dbconsole$ isqlplusctl stop2.6.10. 停止原库$ sqlplus "/as sysdba"SQL> shutdown immediate;2.6.11. 修改环境变量ORACLE_BASEORACLE_HOMEPATH, LD_LIBRARY_PATH and SHLIB_PATH设置以上环境变量，指向Oracle 12.1环境$ export ORACLE_HOME=$ export PATH=$ORACLE_HOME/bin:$PATH$ export ORACLE_BASE=2.6.12. 更新oratab文件，设置新的ORACLE_HOME，并禁止自动启动Sample : cat /etc/oratab #orcl:/opt/oracle/product/11.2/db_1:N orcl:/opt/oracle/product/12.1/db_1:N 更新/etc/oratab文件后，可以指行oraenv（/usr/local/bin/oraenv）设置环境变量[oracle@localhost ~]$ . oraenvORACLE_SID = [orcl] ? orclThe Oracle base for ORACLE_HOME=/opt/oracle/product/12.1/db_1 is /u01/app/oracle[oracle@localhost ~]$ 2.7. 升级12cR1过程2.7.1. 在12cR1新环境启动实例$ cd $ORACLE_HOME/rdbms/admin$ sqlplus "/ as sysdba"SQL> startup UPGRADESQL> exit 2.7.2. 执行升级脚本在12c中，升级脚本catctl.pl代替了catupgrd.sql，升级脚本执行方式也发生了变化在Linux平台执行catctl.pl：Example: Where parallelism is 6 ( n=6)$ cd $ORACLE_HOME/rdbms/admin $ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_HOME/diagnostics catupgrd.sql 在Windows平台执行Example: Where parallelism is 6 ( n=6)cd %ORACLE_HOME%\rdbms\admin%ORACLE_HOME%\perl\bin\perl catctl.pl -n 6 -l %ORACLE_HOME%\diagnostics catupgrd.sql catctl.pl的说明参考：Oracle Database 12c Release 1 (12.1) Upgrade New Features (文档 ID 1515747.1)升级日志，当前使用6并行度升级，Oracle使用了6个会话进行升级$ pwd/data/oracle/product/12.1.0/db_1/diagnostics$ ls -ltotal 55756-rw-r--r--. 1 oracle oinstall 21683527 Feb 7 14:38 catupgrd0.log-rw-r--r--. 1 oracle oinstall 4000201 Feb 7 14:35 catupgrd1.log-rw-r--r--. 1 oracle oinstall 4232471 Feb 7 14:35 catupgrd2.log-rw-r--r--. 1 oracle oinstall 4408497 Feb 7 14:35 catupgrd3.log-rw-r--r--. 1 oracle oinstall 3001116 Feb 7 14:35 catupgrd4.log-rw-r--r--. 1 oracle oinstall 3208239 Feb 7 14:35 catupgrd5.log-rw-r--r--. 1 oracle oinstall 438 Feb 7 14:18 catupgrd_catcon_49000.lst-rw-r--r--. 1 oracle oinstall 42 Feb 7 14:35 catupgrd_catcon_50957.done-rw-r--r--. 1 oracle oinstall 42 Feb 7 14:35 catupgrd_catcon_50958.done-rw-r--r--. 1 oracle oinstall 42 Feb 7 14:35 catupgrd_catcon_50959.done-rw-r--r--. 1 oracle oinstall 42 Feb 7 14:35 catupgrd_catcon_50960.done-rw-r--r--. 1 oracle oinstall 42 Feb 7 14:35 catupgrd_catcon_50961.donedrwxr-xr-x. 3 oracle oinstall 25 Feb 7 10:52 config$ ps -ef | grep LOCALoracle 50962 50956 65 14:35 ? 00:01:11 oracletest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))oracle 50963 50958 0 14:35 ? 00:00:00 oracletest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))oracle 50964 50961 0 14:35 ? 00:00:00 oracletest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))oracle 50965 50960 0 14:35 ? 00:00:00 oracletest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))oracle 50966 50959 0 14:35 ? 00:00:00 oracletest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))oracle 50967 50957 0 14:35 ? 00:00:00 oracletest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) 2.7.3. 执行Post-Upgrade Status Tool升级完成后，可以执行Post-Upgrade Status Tool（$ORACLE_HOME/rdbms/admin/utlu121s.sql），查看升级的概况信息。$ sqlplus "/as sysdba"SQL> STARTUPSQL> @utlu121s.sql 重要：catuppst.sql脚本是作为升级过程一部分执行的，除非升级过程碰到问题。检查日志文件："BEGIN catuppst.sql"以确认在升级过程中catuppst.sql正确运行。如果catuppst.sql没有运行，检查这个位置的信息，处理并运行catuppst.sql，并且在catctl.pl升级过程当中也会有相应的警告信息，表明catuppst.sql没有运行。运行catuppst.sql，脚本位于：$ORACLE_HOME/rdbms/admin目录下，不需要数据库在UPGRADE模式下。SQL> @catuppst.sql执行完后，运行utlrp.sql编译PL/SQL和JAVA代码SQL> @utlrp.sql2.7.4. 检查升级后数据库完整性参考：Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)如果dbupgdiag.sql检查发现有INVALID对象，使用utlrp脚本编译2.8. 完成升级阶段2.8.1. 检查环境变量ORACLE_BASEORACLE_HOMEPATH, LD_LIBRARY_PATH and SHLIB_PATH确认这些环境变量都修改正确确认/etc/oratab和客户端的ORACLE_HOME已修改正确2.8.2. 检查初始化参数文件确认初始化参数文件修改正确，如果是cluster_database，恢复为TRUE2.8.3. 口令文件为了避免在升级过程中出现ORA-28017，可以使用orapwd重建口令文件参考：ORA-28017: The password file is in the legacy format (passwordfile new features in 12c) (文档 ID 2112456.1)2.8.4. COMPATIBLE参数COMPATIBLE参数控制数据库兼容性级别，如果确认不需要再降级到以前的版本，可以设置该参数。建议设置该参数前，对数据库做个备份。ALTER SYSTEM SET COMPATIBLE = '12.1.0' SCOPE=SPFILE;重启数据库实例2.8.5. Oracle新引入的用户在升级Oracle12c过程中，Oracle新建了一些用户，Oracle建议锁定这些用户，除了SYS和SYSTEMSQL> SELECT username, account_status FROM dba_users ORDER BY username;SQL> ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;2.8.6. 升级Recovery Catalog如果当前库上有RMAN Catalog，可以使用UPGRADE CATALOG升级参考：http://docs.oracle.com/database/121/BRADV/rcmcatdb.htm#BRADV1882.8.7. 升级Time Zone 文件版本在升级过程中会自动升级Time Zone File Version，检查如下：SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIESWHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;DST_PRIMARY_TT_VERSION在11.2.0.4中默认为14，在12.1.0.2中为18参考：Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database . (文档 ID 1585343.1)2.8.8. 升级统计信息导出表在旧版本的数据库中使用DBMS_STATS.CREATE_STAT_TABLE创建的统计信息导出表，可以使用如下命令升级：EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS','dictstattab');2.8.9. 升级外部认证SSL用户如果是从9.2.0.x，10.1.0.x升级，并且有外部认证SSL用户，需要用以下命令升级ORACLE_HOME/rdbms/bin/extusrupgrade --dbconnectstring--dbuser --dbuserpassword-a如果从10gR2以上版本升级，不需要执行utluiobj检查INVALID对象当升级前，执行Pre-Upgrade Information Tool脚本时，SYS和SYSTEM用户下的 INVALID对象会记录到registry$sys_inv_objs，其它用户的INVALID对象会记录到registry$nonsys_inv_objs升级后可以执行utluiobj.sql脚本比较差异SQL> @?/rdbms/admin/utluiobj.sql2.8.10. 其它�0�2启用JOB启用Database Vault配置ACL，参考：Note 453786.1 ORA-24247 When Executing UTL_HTTP UTL_INADDR Packages升级APEX配置安装Oracle Text Supplied Knowledge Bases

Oracle 12c升级指南

相关推荐