oracle升级操作流程

数据库版本升级操作手册目录备份数据库 (3)升级Oracle软件 (3)修改ORACLE_HOME (12)升级实例 (13)手工使用脚本升级实例 (14)升级遇到问题的解决办法 (15)检查升级后的版本信息和无效对象 (16)附录 (17)本文用于指导oracle数据库由版本11.2.0.1.0升级到版本11.2.0.3.0升级步骤如下：➢备份数据库➢升级oracle 软件➢修改ORACLE_HOME➢升级实例➢检查升级后的版本信息和无效对象备份数据库由于oracle升级存在一定风险，所以在升级前一定要对已有数据进行备份。

1、停掉所有使用数据库的相关程序，以及数据库。

2、对数据库文件进行冷备份。

备份后的文件可转存到异机或在本地存放。

升级Oracle软件1、获取oracle升级软件p1*******_112030_Linux-x86-64_1of7.zip 及p1*******_112030_Linux-x86-64_2of7.zip。

2、将其上传到数据库服务器的/tmp/目录下并解压。

unzip p1*******_112030_Linux-x86-64_1of7.zipunzip p1*******_112030_Linux-x86-64_1of7.zip3、解压后，会在当前目录下生成一个database目录。

4、切换到oracle用户，执行oracle升级脚本。

5、s u - oracle/tmp/database/runInstaller6、不需要进行任何更改，直接点击下一步。

7、选择跳过软件更新，点击下一步。

8、选择升级现有数据库，点击下一步。

9、语言选择简体中文和英语，点击下一步。

10、选择企业版，并点击下一步。

11、Oracle基目录无需更改，软件位置需要更改为新的位置，这样可以减少宕机时间，是oracle所推荐的方法。

如下图我们将软件安装位置选择为/home/oracle/oracle11g3，修改后，点击下一步。

12、数据库管理员选择dba，数据库操作者选择oinstall，点击下一步。

13、检查先决条件，通过后点击“安装”按钮14、用root用户执行root.sh脚本，执行后，点击“确定”按钮。

15、配置监控，因为我们之前已经配置过，直接选择取消即可。

16、由于选择跳过配置监听，所以此处会报错，不过没有关系。

点击下一步。

17、此处会提示一些配置失败，是否继续。

18、选择“是”，结束Oracle database 软件的安装。

修改ORACLE_HOME注：此文中，数据库升级前软件的安装目录为/home/oracle/oracle11g2，升级后的安装目录为/home/oracle/oracle11g3，若升级前的安装目录与文中提到的不符，请按实际内容进行修改。

如下所有内容均使用oracle用户来进行修改。

1、拷贝dbs目录内容[oracle@dave dbs]$ cd /home/oracle/oracle11g2/dbs[oracle@dave dbs]$ cp * /home/oracle/oracle11g3/dbs2、拷贝admin目录中的内容[oracle@dave admin]$ cd /home/oracle/oracle11g2/network/admin [oracle@dave admin]$ cp -R * /home/oracle/oracle11g3/network/admin3、修改.bashrc文件中的ORACLE_HOME。

[oracle@dave admin]$ vi /home/oracle/.bashrcexport ORACLE_BASE=/home/oracleexport ORACLE_HOME=$ORACLE_BASE/oracle11g3/export ORACLE_SID=orclexport PATH=$ORACLE_HOME/bin:$PATH修改后保存退出。

4、修改/etc/oratab中的ORACLE_HOME[oracle@dave db_1]$ vi /etc/orataborcl:/home/oracle/oracle11g3:N升级实例升级Instance 有两种方法，第一种是使用dbua工具，第二种是手工执行脚本。

此文中，我们使用脚本来升级实例。

升级前验证由于之前，我们修改了oracle用户的.bashrc，因此一定要重新连接oracle用户以获得最新的ORACLE_HOME。

查看ORACLE_HOME的方法：su - oracleexport找到ORACLE_HOME那一条，查看是否为最新。

确认已获取到最新的ORACLE_HOME后则可执行后续操作；否则应退出当前用户，重新连接。

手工使用脚本升级实例1、切换oracle用户，以dba角色进入数据库。

su - oraclesqlplus / as sysdba2、以升级方式启动数据库。

SQL> STARTUP UPGRADE3、设置log输出。

:SQL> SPOOL upgrade.log4、打印预升级信息。

SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql5、执行升级脚本。

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql6、由于上一步会关闭数据库。

所以此处要重启数据库。

SQL> STARTUP7、运行状态工具显示升级结果。

该脚本会显示升级各个组件所花的时间。

SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql8、运行升级后操作。

SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql9、重编译并重验证任何剩余的应用程序对象。

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql10、检查包和类的正确性。

SQL> SELECT count(*) FROM dba_invalid_objects;COUNT(*)----------SQL> SELECT distinct object_name FROM dba_invalid_objects;no rows selected11、Exit SQL*Plus.至此，通过脚本升级操作结束。

升级遇到问题的解决办法如果在升级中遇到问题，可以重新进行升级。

具体的步骤如下：1、停止数据库。

SQL> SHUTDOWN IMMEDIATE2、以升级模式启动数据库。

SQL> STARTUP UPGRADE3、设置log输出。

SQL> SPOOL upgrade.log4、重新执行catupgrd.sqlSQL>@$ORACLE_HOME/rdbms/admin/catupgrd.sql注意：第一次执行此脚本不会报错，再次执行时，会报ORA-00001的错误，可以直接忽略。

5.执行utlu112s.sql:SQL>@$ORACLE_HOME/rdbms/admin/utlu112s.sql检查升级后的版本信息和无效对象SQL> SELECT COMP_NAME, VERSION,STATUS FROMSYS.DBA_REGISTRY;COMP_NAME VERSION STATUS---------------------------------------------------------------------- ---------------------- OWB 11.2.0.1.0 VALIDOracle Application Express 3.2.1.00.10 VALIDOracle Enterprise Manager 11.2.0.3.0 VALIDOLAP Catalog 11.2.0.3.0 VALIDSpatial 11.2.0.3.0 VALIDOracle Multimedia 11.2.0.3.0 VALIDOracle XML Database 11.2.0.3.0 VALIDOracle Text 11.2.0.3.0 VALIDOracle Expression Filter 11.2.0.3.0 VALIDOracle Rules Manager 11.2.0.3.0 VALIDOracle Workspace Manager 11.2.0.3.0 VALIDCOMP_NAME VERSION STATUS---------------------------------------------------------------------- ---------------------- Oracle Database Catalog Views 11.2.0.3.0 VALIDOracle Database Packages and Types 11.2.0.3.0 VALIDJServer JAVA Virtual Machine 11.2.0.3.0 VALIDOracle XDK 11.2.0.3.0 VALIDOracle Database Java Packages 11.2.0.3.0 VALIDOLAP Analytic Workspace 11.2.0.3.0 VALIDOracle OLAP API 11.2.0.3.0 VALID18 rows selected.附录各脚本执行结果，供参考：utlu112i.sqlSQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sqlOracle Database 11.2 Pre-Upgrade Information Tool 06-20-2013 16:01:00 Script Version: 11.2.0.3.0 Build: 001.********************************************************************** Database:**********************************************************************--> name: SOC--> version: 11.2.0.1.0--> compatible: 11.2.0.0.0--> blocksize: 8192--> platform: Linux x86 64-bit--> timezone file: V11.********************************************************************** Tablespaces: [make adjustments in the current environment]**********************************************************************--> SYSTEM tablespace is adequate for the upgrade..... minimum required size: 695 MB--> SYSAUX tablespace is adequate for the upgrade..... minimum required size: 483 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.********************************************************************** Flashback: OFF******************************************************************************************************************************************** Update Parameters: [Update Oracle Database 11.2 init.ora or spfile] Note: Pre-upgrade tool was run on a lower version 64-bit database. **********************************************************************--> If Target Oracle is 32-Bit, refer here for Update Parameters:-- No update parameter changes are required..--> If Target Oracle is 64-Bit, refer here for Update Parameters:-- No update parameter changes are required..**********************************************************************Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]**********************************************************************-- No renamed parameters found. No changes are required..**********************************************************************Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile] **********************************************************************-- No obsolete parameters found. No changes are required.**********************************************************************Components: [The following database components will be upgraded or installed] **********************************************************************--> 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--> OLAP Catalog [upgrade] VALID--> EM Repository [upgrade] VALID--> Oracle Text [upgrade] VALID--> Oracle XML Database [upgrade] VALID--> Oracle Java Packages [upgrade] VALID--> Oracle interMedia [upgrade] VALID--> Spatial [upgrade] VALID--> Expression Filter [upgrade] VALID--> Rule Manager [upgrade] VALID--> Oracle Application Express [upgrade] VALID... APEX will only be upgraded if the version of APEX in... the target Oracle home is higher than the current one.--> Oracle OLAP API [upgrade] VALID.********************************************************************** Miscellaneous Warnings********************************************************************** WARNING: --> Database is using a timezone file older than version 14. .... After the release migration, it is recommended that DBMS_DST package .... be used to upgrade the 11.2.0.1.0 database timezone version.... to the latest version which comes with the new release.WARNING: --> Database contains INVALID objects prior to upgrade. .... The list of invalid SYS/SYSTEM objects was written to.... registry$sys_inv_objs..... The list of non-SYS/SYSTEM objects was written to.... registry$nonsys_inv_objs..... Use utluiobj.sql after the upgrade to identify any new invalid.... objects due to the upgrade..... USER PUBLIC has 27 INVALID objects.WARNING: --> Your recycle bin contains 12 object(s)..... It is REQUIRED that the recycle bin is empty prior to upgrading.... your database. The command:PURGE DBA_RECYCLEBIN.... must be executed immediately prior to executing your upgrade. WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package..... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs. .... USER APEX_030200 has dependent objects..********************************************************************** Recommendations**********************************************************************Oracle recommends gathering dictionary statistics prior toupgrading the database.To gather dictionary statistics execute the following commandwhile connected as SYSDBA:EXECUTE dbms_stats.gather_dictionary_stats;**********************************************************************Oracle recommends reviewing any defined events prior to upgrading.To view existing non-default events execute the following commandswhile connected AS SYSDBA:Events:SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'Trace Events:SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE' Changes will need to be made in the init.ora or spfile.**********************************************************************Utlu112s.sqlSQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql.Oracle Database 11.2 Post-Upgrade Status Tool 06-20-2013 16:54:31 .Component Current Version Elapsed TimeName Status NumberHH:MM:SS.Oracle Server. VALID 11.2.0.3.000:07:41JServer JAVA Virtual Machine. VALID 11.2.0.3.000:02:12Oracle Workspace Manager. VALID 11.2.0.3.000:00:22OLAP Analytic Workspace. VALID 11.2.0.3.000:00:51OLAP Catalog. VALID 11.2.0.3.000:00:40Oracle OLAP API. VALID 11.2.0.3.0Oracle Enterprise Manager. VALID 11.2.0.3.0 00:02:53Oracle XDK. VALID 11.2.0.3.0 00:00:22Oracle Text. VALID 11.2.0.3.0 00:00:21Oracle XML Database. VALID 11.2.0.3.0 00:01:27Oracle Database Java Packages. VALID 11.2.0.3.0 00:00:09Oracle Multimedia. VALID 11.2.0.3.0 00:01:57Spatial. VALID 11.2.0.3.0 00:02:19Oracle Expression Filter. VALID 11.2.0.3.0Oracle Rules Manager. VALID 11.2.0.3.0 00:00:05Oracle Application Express. VALID 3.2.1.00.10 Gathering Statistics.00:01:59Total Upgrade Time: 00:23:47PL/SQL procedure successfully completed.catuppst.sqlSQL> @$ORACLE_HOME/rdbms/admin/catuppst.sqlTIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMP POSTUP_BGN 2013-06-20 16:57:19PL/SQL procedure successfully completed.This script will migrate the Baseline data on a pre-11g database to the 11g database.... ...... Completed Moving the Baseline Data ...... ...... If there are no Move BL Data messages ...... above, then there are no renamed ...... baseline tables in the system. ...... ...... ...... Completed the Dropping of the ...... Renamed Baseline Tables ...... ...... If there are no Drop Table messages ...... above, then there are no renamed ...... baseline tables in the system. ...... ... PL/SQL procedure successfully completed.0 rows created.Commit complete.Table created.2 rows created.1 row updated.2 rows updated.0 rows updated. Table dropped. Commit complete.0 rows updated. Commit complete.0 rows updated. Commit complete.0 rows updated.Commit complete.0 rows created.Commit complete.0 rows created.Commit complete.PL/SQL procedure successfully completed.TIMESTAMP-------------------------------------------------------------------------------- COMP_TIMESTAMP POSTUP_END 2013-06-20 16:57:20PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.Generating apply and rollback scripts...Check the following file for errors:/home/oracle/cfgtoollogs/catbundle/catbundle_PSU_SOC_GENERATE_2013Jun20_1 6_57_21.logApply script:/home/oracle/oracle11g3//rdbms/admin/catbundle_PSU_SOC_APPLY.sqlRollback script:/home/oracle/oracle11g3//rdbms/admin/catbundle_PSU_SOC_ROLLBACK.sqlPL/SQL procedure successfully completed.Executing script file...SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINTSQL> SELECT '/home/oracle/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss','NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;SQL> SPOOL &spool_fileSQL> exec dbms_registry.set_session_namespace('SERVER') PL/SQL procedure successfully completed.SQL> ALTER SESSION SET current_schema = SYS;Session altered.SQL> PROMPT Updating registry...Updating registry...SQL> INSERT INTO registry$history2 (action_time, action,3 namespace, version, id,4 bundle_series, comments)5 VALUES6 (SYSTIMESTAMP, 'APPLY',7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),8 '11.2.0.3',9 0,10 'PSU',11 'Patchset 11.2.0.2.0');1 row created.SQL> COMMIT;Commit complete.SQL> SPOOL offSQL> SET echo offCheck the following log file for errors:/home/oracle/cfgtoollogs/catbundle/catbundle_PSU_SOC_APPLY_2013Jun20_16_57 _22.log。