探索Oracle之数据库升级二 11.2.0.3升级到11.2.0.4完整步骤

探索Oracle之数据库升级二

 11.2.0.3升级到11.2.0.4完整步骤

 

说明：

        这篇文章主要是记录下单实例环境下Oracle 11.2.0.1升级到11.2.0.3的过程，当然RAC的升级是会有所不同。但是他们每个版本之间升级步骤都是差不多的，先升级Database Software，再升级Oracle Instance。

 Oracle 11.2.0.4的Patchset No:19852360下载需要有Oracle Support才可以。

 Patchset包含有7个文件，关于这七个文件的作用，详见如下链接：

 我们升级Database，只需要其中的第一个和第二文件即可。将2个文件解压缩后就可以执行升级操作了。

 

升级前准备：

1、查看数据库和操作系统相关信息:

[root@db01 ~]# uname -aLinux db01 2.6.18-308.el5 #1SMP Fri Jan 27 17:17:51 EST 2012 x86_64 x86_64 x86_64 GNU/Linux[root@db01 ~]# lsb_release-aLSB Version:    :core-4.0-amd64:core-4.0-ia32:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-ia32:printing-4.0-noarchDistributor ID:RedHatEnterpriseServerDescription:    Red Hat Enterprise Linux Server release 5.8(Tikanga)Release:        5.8Codename:       Tikanga[root@db01 ~]# su - oracle-c "sqlplus / as sysdba";SQL*Plus: Release 11.2.0.3.0Production on Fri Oct 3 21:32:02 2014Copyright (c) 1982, 2011,Oracle.  All rights reserved. Connected to:Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP,Data Mining and Real Application Testing optionsSQL> select name fromv$database; NAME---------WOOSQL> 

 

2、备份数据库

[oracle@db01 ~]$ rman target/ Recovery Manager: Release11.2.0.3.0 - Production on Fri Oct 3 21:43:04 2014Copyright (c) 1982, 2011,Oracle and/or its affiliates.  All rightsreserved.connected to targetdatabase: WOO (DBID=4199461782)RMAN> backup databaseplus archivelog delete input format '/DBBackup/Phycal/full_%U.bak'; Starting backup at 03-OCT-14current log archivedusing target databasecontrol file instead of recovery catalogallocated channel:ORA_DISK_1channel ORA_DISK_1: SID=149device type=DISKchannel ORA_DISK_1: startingarchived log backup setchannel ORA_DISK_1:specifying archived log(s) in backup setinput archived log thread=1sequence=15 RECID=1 STAMP=860017183input archived log thread=1sequence=16 RECID=2 STAMP=860017184input archived log thread=1sequence=17 RECID=3 STAMP=860017186input archived log thread=1sequence=18 RECID=4 STAMP=860017186input archived log thread=1sequence=19 RECID=5 STAMP=860017188input archived log thread=1sequence=20 RECID=6 STAMP=860017387channel ORA_DISK_1: startingpiece 1 at 03-OCT-14channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14piecehandle=/DBBackup/Phycal/full_01pk5knb_1_1.bak tag=TAG20141003T214307comment=NONEchannel ORA_DISK_1: backupset complete, elapsed time: 00:00:02channel ORA_DISK_1: deletingarchived log(s)archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_15_b2x9rz0z_.arcRECID=1 STAMP=860017183archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_16_b2x9s05l_.arcRECID=2 STAMP=860017184archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_17_b2x9s2nx_.arcRECID=3 STAMP=860017186archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_18_b2x9s2od_.arcRECID=4 STAMP=860017186archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_19_b2x9s4dr_.arcRECID=5 STAMP=860017188archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_20_b2x9zbz2_.arcRECID=6 STAMP=860017387Finished backup at 03-OCT-14Starting backup at 03-OCT-14using channel ORA_DISK_1channel ORA_DISK_1: startingfull datafile backup setchannel ORA_DISK_1:specifying datafile(s) in backup setinput datafile filenumber=00001 name=/DBData/woo/system01.dbfinput datafile filenumber=00002 name=/DBData/woo/sysaux01.dbfinput datafile filenumber=00005 name=/DBData/woo/example01.dbfinput datafile filenumber=00003 name=/DBData/woo/undotbs01.dbfinput datafile filenumber=00004 name=/DBData/woo/users01.dbfchannel ORA_DISK_1: startingpiece 1 at 03-OCT-14channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14piecehandle=/DBSoft/fast_recovery_area/WOO/backupset/2014_10_03/o1_mf_nnndf_TAG20141003T214309_b2x9zfm5_.bkptag=TAG20141003T214309 comment=NONEchannel ORA_DISK_1: backupset complete, elapsed time: 00:01:45channel ORA_DISK_1: startingfull datafile backup setchannel ORA_DISK_1:specifying datafile(s) in backup setincluding current controlfile in backup setincluding current SPFILE inbackup setchannel ORA_DISK_1: startingpiece 1 at 03-OCT-14channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14piecehandle=/DBSoft/fast_recovery_area/WOO/backupset/2014_10_03/o1_mf_ncsnf_TAG20141003T214309_b2xb2qlm_.bkptag=TAG20141003T214309 comment=NONEchannel ORA_DISK_1: backupset complete, elapsed time: 00:00:01Finished backup at 03-OCT-14 Starting backup at 03-OCT-14current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: startingarchived log backup setchannel ORA_DISK_1:specifying archived log(s) in backup setinput archived log thread=1sequence=21 RECID=7 STAMP=860017496channel ORA_DISK_1: startingpiece 1 at 03-OCT-14channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14piecehandle=/DBBackup/Phycal/full_04pk5kqo_1_1.bak tag=TAG20141003T214456comment=NONEchannel ORA_DISK_1: backupset complete, elapsed time: 00:00:01channel ORA_DISK_1: deletingarchived log(s)archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_21_b2xb2rsf_.arcRECID=7 STAMP=860017496Finished backup at 03-OCT-14 RMAN>

 

3、停止数据库

###停止数据库SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.###停止监听SQL> host lsnrctl stop LSNRCTL for Linux: Version11.2.0.3.0 - Production on 04-OCT-2014 01:39:26 Copyright (c) 1991, 2011,Oracle.  All rights reserved.Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))The command completedsuccessfully###停止EMSQL> host emctl stopdbconsoleOracle Enterprise Manager11g Database Control Release 11.2.0.3.0 Copyright (c) 1996, 2011Oracle Corporation.  All rights reserved.<a target=_blank href="https://db01:1158/em/console/aboutApplication">https://db01:1158/em/console/aboutApplication</a>Stopping Oracle EnterpriseManager 11g Database Control ...###查看oracle进程，检查是否已经停止完毕[root@db01 ~]# ps -ef|greporaroot      4971 4944  0 01:46 pts/0    00:00:00 grep ora

 

4、备份老的ORACLE_HOME和oraInventory

[root@db01 ~]#tar –cvfproduct.zip /DBSoft/product/[root@db01 ~]#tar –cvforaInventory.zip /DBSoft/product/oraInventory/

 

5、上传并解压缩Oracle Database 11.2.0.4安装介质

[oracle@db01 ~]$ lltotal 2489644drwxr-xr-x 2 oracle oinstall       4096 Oct 3 04:06 Desktop-rw-r--r-- 1 oracle oinstall1395582860 Oct  3 06:55p13390677_112040_Linux-x86-64_1of7.zip-rw-r--r-- 1 oracle oinstall1151304589 Oct  3 06:54p13390677_112040_Linux-x86-64_2of7.zip[oracle@db01 ~]$ unzipp13390677_112040_Linux-x86-64_1of7.zip[oracle@db01 ~]$ unzipp13390677_112040_Linux-x86-64_2of7.zip

 

6、 开始安装

6.1 将文件解压缩后进入安装目录执行./runInstall

 
  6.2 取消Oracle支持选项，点击Next

 
  6.3 选择最后一个选项"skip software update" 点击Next


   6.4 选择最后一个选项"Upgrade an existing database" 后执行Next


   6.5 选择所有语言，后点击Next


  6.6 选择要升级的数据库版本，后点击Next


   6.7 选择新版本的数据库软件安装目录，后点击Next


   6.8 选择数据库所属用户组，后点击Next


  6.9 察看数据库配置信息后，点击Install开始进行新版本软件安装


   6.10 安装进度，这个过程将会持续15分钟左右


  6.11 弹出对话框要求执行/DBSoft/Product/11.2.4/db_1/root.sh 脚本

7、 执行root.sh脚本

[root@db01 ~]#/DBSoft/Product/11.2.4/db_1/root.sh Performing root useroperation for Oracle 11g  The following environmentvariables are set as:    ORACLE_OWNER= oracle    ORACLE_HOME=  /DBSoft/Product/11.2.4/db_1 Enter the full pathname ofthe local bin directory: [/usr/local/bin]: The contents of"dbhome" have not changed. No need to overwrite.The contents of"oraenv" have not changed. No need to overwrite.The contents of"coraenv" have not changed. No need to overwrite. Entries will be added to the/etc/oratab file as needed byDatabase ConfigurationAssistant when a database is createdFinished running genericpart of root script.Now product-specific rootactions will be performed.Finished product-specificroot actions.[root@db01 ~]#

   6.12 执行完脚本之后继续运行，提示配置监听，选择Cancel，稍后复制即可


  6.13 提示监听配置失败，点击ok即可


  6.14 有报错不用管，我们直接Next即可


   6.15 至此软件安装完成，点击Close关闭安装界面

至此软件安装完成，但是并不代表数据库就已经升级完成了。

 

8、至此11.2.0.4的软件就已经装完了，修改Oracle环境变量

[root@db01 ~]# su - oracle[oracle@db01 ~]$ vi.bash_profile                   -------修改如下行,将11.2.3改成11.2.4即可export ORACLE_HOME=$ORACLE_BASE/Product/11.2.4/db_1[oracle@db01 ~]$ vi /etc/oratab                     -------修改如下行11.2.3为11.2.4woo:/DBSoft/Product/11.2.4/db_1:N[oracle@db01 admin]$ cp/DBSoft/Product/11.2.3/db_1/dbs/* /DBSoft/Product/11.2.4/db_1/dbs/

 

9、 拷贝监听配置文件

[oracle@db01 admin]$ source~/.bash_profile [oracle@db01 admin]$ pwd/DBSoft/Product/11.2.3/db_1/network/admin[oracle@db01 admin]$ cd$ORACLE_HOME/network/admin[oracle@db01 admin]$ lssamples  shrept.lst[oracle@db01 admin]$ cp -r/DBSoft/Product/11.2.3/db_1/network/admin/* $ORACLE_HOME/network/admin[oracle@db01 admin]$ lslistener.ora  samples shrept.lst  sqlnet.ora  tnsnames.ora

 

10、执行预升级脚本检查

[oracle@db01 dbs]$ sqlplus /as sysdba SQL*Plus: Release 11.2.0.4.0Production on Sat Oct 4 02:26:01 2014Copyright (c) 1982, 2013,Oracle.  All rights reserved.Connected to an idleinstance. SQL> startup upgrade;ORACLE instance started.Total System Global Area2037673984 bytesFixed Size                  2254704 bytesVariable Size            1811941520 bytesDatabase Buffers          218103808 bytesRedo Buffers                5373952 bytesDatabase mounted.Database opened.SQL>@?/rdbms/admin/utlu112i.sql    ---执行升级前检查Oracle Database 11.2Pre-Upgrade Information Tool 10-04-2014 02:27:38Script Version: 11.2.0.4.0Build: 001.**********************************************************************Database:**********************************************************************--> name:          WOO--> version:       11.2.0.3.0--> compatible:    11.2.0.0.0--> blocksize:     8192--> platform:      Linux x86 64-bit--> timezone file: V14.**********************************************************************Tablespaces: [makeadjustments in the current environment]**********************************************************************--> SYSTEM tablespace isadequate for the upgrade..... minimum required size:917 MB--> SYSAUX tablespace isadequate for the upgrade..... minimum required size:646 MB--> UNDOTBS1 tablespaceis adequate for the upgrade..... minimum required size:400 MB--> TEMP tablespace isadequate for the upgrade..... minimum required size:60 MB.**********************************************************************Flashback: OFF********************************************************************************************************************************************Update Parameters: [UpdateOracle Database 11.2 init.ora or spfile]Note: Pre-upgrade tool wasrun on a lower version 64-bit database.**********************************************************************--> If Target Oracle is32-Bit, refer here for Update Parameters:WARNING: -->"shared_pool_size" needs to be increased to at least 236 MB. --> If Target Oracle is64-Bit, refer here for Update Parameters:WARNING: -->"shared_pool_size" needs to be increased to at least 472 MB.**********************************************************************Renamed Parameters: [UpdateOracle Database 11.2 init.ora or spfile]**********************************************************************-- No renamed parametersfound. No changes are required..**********************************************************************Obsolete/DeprecatedParameters: [Update Oracle Database 11.2 init.ora or spfile]**********************************************************************-- No obsolete parametersfound. No changes are required. **********************************************************************Components: [The followingdatabase components will be upgraded or installed]**********************************************************************--> Oracle CatalogViews         [upgrade]  VALID--> Oracle Packages andTypes    [upgrade]  VALID--> JServer JAVA VirtualMachine [upgrade]  VALID--> Oracle XDK forJava          [upgrade]  VALID--> Oracle WorkspaceManager     [upgrade]  VALID--> OLAP Analytic Workspace      [upgrade] VALID--> OLAP Catalog                 [upgrade]  VALID--> EM Repository                [upgrade]  VALID--> Oracle Text                  [upgrade]  VALID--> Oracle XMLDatabase          [upgrade]  VALID--> Oracle Java Packages         [upgrade]  VALID--> OracleinterMedia            [upgrade]  VALID--> Spatial                      [upgrade]  VALID--> ExpressionFilter            [upgrade]  VALID--> Rule Manager                 [upgrade]  VALID--> Oracle ApplicationExpress   [upgrade]  VALID... APEX will only beupgraded if the version of APEX in... the target Oracle homeis higher than the current one.--> Oracle OLAP API              [upgrade]  VALID.**********************************************************************Miscellaneous Warnings**********************************************************************WARNING: --> Your recyclebin is turned on and currently contains no objects..... Because it is REQUIREDthat the recycle bin be empty prior to upgrading.... and your recycle bin isturned on, you may need to execute the command:        PURGE DBA_RECYCLEBIN.... prior to executing yourupgrade to confirm the recycle bin is empty.WARNING: --> Databasecontains schemas with objects dependent on DBMS_LDAP package..... Refer to the 11gUpgrade Guide for instructions to configure Network ACLs..... USER APEX_030200 hasdependent objects..**********************************************************************Recommendations**********************************************************************Oracle recommends gatheringdictionary statistics prior toupgrading the database.To gather dictionarystatistics execute the following commandwhile connected as SYSDBA:     EXECUTE dbms_stats.gather_dictionary_stats; **********************************************************************Oracle recommends removingall hidden parameters prior to upgrading.To view existing hiddenparameters execute the following commandwhile connected AS SYSDBA:     SELECT name,description fromSYS.V$PARAMETER WHERE name        LIKE '\_%' ESCAPE '\'Changes will need to be madein the init.ora or spfile.**********************************************************************Oracle recommends reviewingany defined events prior to upgrading.To view existing non-defaultevents execute the following commandswhile connected AS SYSDBA:  Events:    SELECT (translate(value,chr(13)||chr(10),'')) FROM sys.v$parameter2      WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'   Trace Events:    SELECT (translate(value,chr(13)||chr(10),'')) from sys.v$parameter2      WHERE UPPER(name) = '_TRACE_EVENTS' ANDisdefault='FALSE' Changes will need to be madein the init.ora or spfile.

 

11、     修改不满足项

SQL> show parametershared NAME                                 TYPE        VALUE----------------------------------------------- ------------------------------hi_shared_memory_address             integer     0max_shared_servers                   integershared_memory_address                integer     0shared_pool_reserved_size            big integer 8Mshared_pool_size                     big integer 160Mshared_server_sessions               integershared_servers                       integer     1SQL> alter system setshared_pool_size=800m scope=spfile; System altered. SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountlSP2-0714: invalidcombination of STARTUP optionsSQL> startup mount;ORACLE instance started.Total System Global Area2872786944 bytesFixed Size                  2256712 bytesVariable Size            2634023096 bytesDatabase Buffers          218103808 bytesRedo Buffers               18403328 bytesDatabase mounted.SQL> alter databaseflashback on; ###创建一个回滚点SQL> create restore pointmaclean_rollback guarantee flashback database; Restore point created. SQL> select * fromv$restore_point;        SCN DATABASE_INCARNATION# GUASTORAGE_SIZE------------------------------- --- ------------TIME---------------------------------------------------------------------------RESTORE_POINT_TIME                                                         PRE------------------------------------------------------------------------------NAME--------------------------------------------------------------------------------   1187867                     2 YES     5242880004-OCT-14 02.41.43.000000000AM                                                                           YESMACLEAN_ROLLBACK 再次运行检查：SQL>@?/rdbms/admin/utlu112i.sqlOracle Database 11.2Pre-Upgrade Information Tool 10-04-2014 03:26:20Script Version: 11.2.0.4.0Build: 001.**********************************************************************Database:**********************************************************************--> name:          WOO--> version:       11.2.0.3.0--> compatible:    11.2.0.0.0--> blocksize:     8192--> platform:      Linux x86 64-bit--> timezone file: V14.**********************************************************************Tablespaces: [makeadjustments in the current environment]**********************************************************************--> SYSTEM tablespace isadequate for the upgrade..... minimum required size:917 MB--> SYSAUX tablespace isadequate for the upgrade..... minimum required size:646 MB--> UNDOTBS1 tablespaceis adequate for the upgrade..... minimum required size:400 MB--> TEMP tablespace isadequate for the upgrade..... minimum required size:60 MB.**********************************************************************Flashback: ON**********************************************************************FlashbackInfo:--> name:          /DBSoft/fast_recovery_area--> limit:         4122 MB--> used:          1490 MB--> size:          4122 MB--> reclaim:       1097.734375 MB--> files:         11WARNING: --> FlashbackRecovery Area Set.  Please ensureadequate disk space              inrecovery areas before performing anupgrade..**********************************************************************Update Parameters: [UpdateOracle Database 11.2 init.ora or spfile]Note: Pre-upgrade tool wasrun on a lower version 64-bit database.**********************************************************************--> If Target Oracle is32-Bit, refer here for Update Parameters:-- No update parameterchanges are required.. --> If Target Oracle is64-Bit, refer here for Update Parameters:-- No update parameterchanges are required..**********************************************************************Renamed Parameters: [UpdateOracle Database 11.2 init.ora or spfile]**********************************************************************-- No renamed parametersfound. No changes are required..**********************************************************************Obsolete/DeprecatedParameters: [Update Oracle Database 11.2 init.ora or spfile]**********************************************************************-- No obsolete parametersfound. No changes are required. **********************************************************************Components: [The followingdatabase components will be upgraded or installed]**********************************************************************--> Oracle CatalogViews          [upgrade]  VALID--> Oracle Packages andTypes     [upgrade]  VALID--> JServer JAVA VirtualMachine  [upgrade]  VALID--> Oracle XDK forJava           [upgrade]  VALID--> Oracle WorkspaceManager      [upgrade]  VALID--> OLAP AnalyticWorkspace       [upgrade]  VALID--> OLAP Catalog                 [upgrade]  VALID--> EM Repository                [upgrade]  VALID--> Oracle Text                  [upgrade]  VALID--> Oracle XMLDatabase           [upgrade]  VALID--> Oracle JavaPackages          [upgrade]  VALID--> OracleinterMedia             [upgrade]  VALID--> Spatial                      [upgrade]  VALID--> ExpressionFilter             [upgrade]  VALID--> Rule Manager                 [upgrade]  VALID--> Oracle ApplicationExpress    [upgrade]  VALID... APEX will only beupgraded if the version of APEX in... the target Oracle homeis higher than the current one.--> Oracle OLAP API              [upgrade]  VALID.**********************************************************************Miscellaneous Warnings**********************************************************************WARNING: --> Your recyclebin is turned on and currently contains no objects..... Because it is REQUIREDthat the recycle bin be empty prior to upgrading.... and your recycle bin isturned on, you may need to execute the command:        PURGE DBA_RECYCLEBIN.... prior to executing yourupgrade to confirm the recycle bin is empty.WARNING: --> Databasecontains schemas with objects dependent on DBMS_LDAP package..... Refer to the 11gUpgrade Guide for instructions to configure Network ACLs..... USER APEX_030200 hasdependent objects..**********************************************************************Recommendations**********************************************************************Oracle recommends gatheringdictionary statistics prior toupgrading the database.To gather dictionarystatistics execute the following commandwhile connected as SYSDBA:     EXECUTE dbms_stats.gather_dictionary_stats;**********************************************************************Oracle recommends removingall hidden parameters prior to upgrading.To view existing hiddenparameters execute the following commandwhile connected AS SYSDBA:     SELECT name,description fromSYS.V$PARAMETER WHERE name        LIKE '\_%' ESCAPE '\'Changes will need to be madein the init.ora or spfile.**********************************************************************Oracle recommends reviewingany defined events prior to upgrading.To view existing non-defaultevents execute the following commandswhile connected AS SYSDBA:  Events:    SELECT (translate(value,chr(13)||chr(10),'')) FROM sys.v$parameter2      WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'  Trace Events:    SELECT (translate(value,chr(13)||chr(10),'')) from sys.v$parameter2      WHERE UPPER(name) = '_TRACE_EVENTS' ANDisdefault='FALSE' Changes will need to be madein the init.ora or spfile.

 

12、执行升级操作 

SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup upgrade;ORACLE instance started.Total System Global Area2872786944 bytesFixed Size                  2256712 bytesVariable Size            2634023096 bytesDatabase Buffers          218103808 bytesRedo Buffers               18403328 bytesDatabase mounted.Database opened.SQL> set echo onSQL> spool/home/oracle/upgrade.logSQL> set time on;03:28:37 SQL>@?/rdbms/admin/catupgrd.sql  ---该脚本会运行十分钟左右……..Oracle Database 11.2Post-Upgrade Status Tool          10-04-2014 04:01:36.Component                               Current      Version     Elapsed TimeName                                    Status       Number      HH:MM:SS.Oracle Server.                                        VALID      11.2.0.4.0  00:08:38JServer JAVA Virtual Machine.                                        VALID      11.2.0.4.0  00:04:26Oracle Workspace Manager.                                        VALID      11.2.0.4.0  00:00:25OLAP Analytic Workspace.                                        VALID      11.2.0.4.0  00:01:27OLAP Catalog.                                        VALID      11.2.0.4.0  00:00:32Oracle OLAP API.                                        VALID      11.2.0.4.0  00:00:19Oracle Enterprise Manager.                                        VALID      11.2.0.4.0  00:06:25Oracle XDK.                                         VALID      11.2.0.4.0  00:00:26Oracle Text.                                        VALID      11.2.0.4.0  00:00:24Oracle XML Database.                                        VALID      11.2.0.4.0  00:01:46Oracle Database JavaPackages.                                         VALID      11.2.0.4.0  00:00:08Oracle Multimedia.                                        VALID      11.2.0.4.0  00:01:43Spatial.                                        VALID      11.2.0.4.0  00:05:34Oracle Expression Filter.                                        VALID      11.2.0.4.0  00:00:06Oracle Rules Manager.                                        VALID      11.2.0.4.0  00:00:07Oracle Application Express.                                        VALID     3.2.1.00.12Final Actions.                                                               00:00:00Total Upgrade Time: 00:32:35 PL/SQL proceduresuccessfully completed. 04:01:36 SQL> 04:01:36 SQL> SETSERVEROUTPUT OFF04:01:36 SQL> SET VERIFYON04:01:36 SQL> commit; Commit complete. 04:01:36 SQL> 04:01:36 SQL> shutdownimmediate;Database closed.Database dismounted.ORACLE instance shut down.04:01:54 SQL> 04:01:54 SQL> 04:01:54 SQL> DOC04:01:54 DOC>#######################################################################04:01:54DOC>#######################################################################04:01:54 DOC>04:01:54 DOC>   The above sql script is the final step ofthe upgrade. Please04:01:54 DOC>   review any errors in the spool log file. Ifthere are any errors in04:01:54 DOC>   the spool file, consult the Oracle DatabaseUpgrade Guide for04:01:54 DOC>   troubleshooting recommendations.04:01:54 DOC>04:01:54 DOC>   Next restart for normal operation, and thenrun utlrp.sql to04:01:54 DOC>   recompile any invalid application objects.04:01:54 DOC>04:01:54 DOC>   If the source database had an older timezone version prior to04:01:54 DOC>   upgrade, then please run the DBMS_DSTpackage.  DBMS_DST will upgrade04:01:54 DOC>   TIMESTAMP WITH TIME ZONE data to use thelatest time zone file shipped04:01:54 DOC>   with Oracle.04:01:54 DOC>04:01:54DOC>#######################################################################04:01:54 DOC>#######################################################################04:01:54 DOC>#04:01:54 SQL> 04:01:54 SQL> Rem Seterrorlogging off04:01:54 SQL> SETERRORLOGGING OFF;04:01:54 SQL> 04:01:54 SQL> REM END OFCATUPGRD.SQL04:01:54 SQL> 04:01:54 SQL> REM bug12337546 - Exit current sqlplus session at end of catupgrd.sql.04:01:54 SQL> REM                This forces user to start a newsqlplus session in order04:01:54 SQL> REM                to connect to the upgraded db.04:01:54 SQL> exitDisconnected from OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP,Data Mining and Real Application Testing options

 

以上catupgrd.sql脚本运行了50分钟左右，执行完之后会shutdown immediate数据库。这个时候我们将要重启数据库运行utlrp.sql脚本编译失效对象：

 

13、运行utlrp.sql编译失效对象

[oracle@db01 dbs]$ sqlplus /as sysdba SQL*Plus: Release 11.2.0.4.0Production on Sat Oct 4 04:11:22 2014Copyright (c) 1982, 2013,Oracle.  All rights reserved. Connected to an idleinstance. SQL> startupORACLE instance started. Total System Global Area 2872786944bytesFixed Size                  2256712 bytesVariable Size            2634023096 bytesDatabase Buffers          218103808 bytesRedo Buffers               18403328 bytesDatabase mounted.Database opened.SQL> @?/rdbms/admin/utlrp TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMPUTLRP_BGN  2014-10-04 04:14:57DOC>   The following PL/SQL block invokesUTL_RECOMP to recompile invalidDOC>   objects in the database. Recompilation timeis proportional to theDOC>   number of invalid objects in the database,so this command may takeDOC>   a long time to execute on a database with alarge number of invalidDOC>   objects.DOC>DOC>   Use the following queries to trackrecompilation progress:DOC>DOC>   1. Query returning the number of invalidobjects remaining. ThisDOC>      number should decrease with time.DOC>         SELECT COUNT(*) FROM obj$ WHERE statusIN (4, 5, 6);DOC>DOC>   2. Query returning the number of objectscompiled so far. This numberDOC>      should increase with time.DOC>         SELECT COUNT(*) FROMUTL_RECOMP_COMPILED;DOC>DOC>   This script automatically chooses serial orparallel recompilationDOC>   based on the number of CPUs available(parameter cpu_count) multipliedDOC>   by the number of threads per CPU (parameterparallel_threads_per_cpu).DOC>   On RAC, this number is added across all RACnodes.DOC>DOC>   UTL_RECOMP uses DBMS_SCHEDULER to createjobs for parallelDOC>   recompilation. Jobs are created withoutinstance affinity so that theyDOC>   can migrate across RAC nodes. Use thefollowing queries to verifyDOC>   whether UTL_RECOMP jobs are being createdand run correctly:DOC>DOC>   1. Query showing jobs created by UTL_RECOMPDOC>         SELECT job_name FROM dba_scheduler_jobsDOC>            WHERE job_name like'UTL_RECOMP_SLAVE_%';DOC>DOC>   2. Query showing UTL_RECOMP jobs that arerunningDOC>         SELECT job_name FROMdba_scheduler_running_jobsDOC>            WHERE job_name like'UTL_RECOMP_SLAVE_%';DOC># PL/SQL proceduresuccessfully completed.  TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMPUTLRP_END  2014-10-04 04:15:34 DOC> The following queryreports the number of objects that have compiledDOC> with errors.DOC>DOC> If the number ishigher than expected, please examine the errorDOC> messages reportedwith each object (using SHOW ERRORS) to see if theyDOC> point to systemmisconfiguration or resource constraints that must beDOC> fixed beforeattempting to recompile these objects.DOC># OBJECTS WITH ERRORS-------------------                  0 DOC> The following queryreports the number of errors caught duringDOC> recompilation. Ifthis number is non-zero, please query the errorDOC> messages in thetable UTL_RECOMP_ERRORS to see if any of these errorsDOC> are due tomisconfiguration or resource constraints that must beDOC> fixed before objectscan compile successfully.DOC># ERRORS DURING RECOMPILATION---------------------------                          0  Function created. PL/SQL proceduresuccessfully completed. Function dropped. PL/SQL proceduresuccessfully completed.SQL>

 

该脚本耗时约为3分钟左右。

 

14、至此数据库已经升级完成，查看各组件版本号：

 

SQL> select comp_name,status,version fromdba_server_registry COMP_NAME                                STATUS         VERSION------------------------------------------------------ ------------------------------OWB                                      VALID          11.2.0.3.0Oracle ApplicationExpress                VALID          3.2.1.00.12Oracle EnterpriseManager                 VALID          11.2.0.4.0OLAP Catalog                             VALID          11.2.0.4.0Spatial                                  VALID          11.2.0.4.0Oracle Multimedia                        VALID          11.2.0.4.0Oracle XML Database                      VALID          11.2.0.4.0Oracle Text                              VALID          11.2.0.4.0Oracle ExpressionFilter                  VALID          11.2.0.4.0Oracle Rules Manager                     VALID          11.2.0.4.0Oracle WorkspaceManager                  VALID          11.2.0.4.0Oracle Database CatalogViews             VALID          11.2.0.4.0Oracle Database Packages andTypes        VALID          11.2.0.4.0JServer JAVA VirtualMachine              VALID          11.2.0.4.0Oracle XDK                               VALID          11.2.0.4.0Oracle Database JavaPackages             VALID          11.2.0.4.0OLAP Analytic Workspace                  VALID          11.2.0.4.0Oracle OLAP API                          VALID          11.2.0.4.0 18 rows selected.

 

15、检查无效对象：

SQL> select * fromdba_objects where status !='VALID'; no rows selected

 

16、升级成功后删除原来的目录，通过EMCA重建EM

[oracle@db01 /]$ rm -rf/DBSoft/Product/11.2.3/手工创建EM资料库:####emca -repos drop [oracle@db01 /]$ emca -reposdropSTARTED EMCA at Oct 4, 20146:11:41 AMEM Configuration Assistant,Version 11.2.0.3.0 ProductionCopyright (c) 2003, 2011,Oracle.  All rights reserved. Enter the followinginformation:Database SID: wooListener port number: 1521Password for SYS user:  Password for SYSMANuser:   ----------------------------------------------------------------------WARNING : While repositoryis dropped the database will be put in quiesce mode.----------------------------------------------------------------------Do you wish to continue?[yes(Y)/no(N)]: yOct 4, 2014 6:11:53 AMoracle.sysman.emcp.EMConfig performINFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca_2014_10_04_06_11_41.log.Oct 4, 2014 6:11:53 AMoracle.sysman.emcp.EMReposConfig invokeINFO: Dropping the EMrepository (this may take a while) ...Oct 4, 2014 6:13:37 AMoracle.sysman.emcp.EMReposConfig invokeINFO: Repositorysuccessfully droppedEnterprise Managerconfiguration completed successfullyFINISHED EMCA at Oct 4, 20146:13:37 AM####emca -repos create [oracle@db01 /]$ emca -reposcreate STARTED EMCA at Oct 4, 20146:14:07 AMEM Configuration Assistant,Version 11.2.0.3.0 ProductionCopyright (c) 2003, 2011,Oracle.  All rights reserved. Enter the followinginformation:Database SID: wooListener port number: 1521Password for SYS user:  Password for SYSMANuser:  Do you wish to continue?[yes(Y)/no(N)]: yOct 4, 2014 6:14:20 AM oracle.sysman.emcp.EMConfigperformINFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca_2014_10_04_06_14_07.log.Oct 4, 2014 6:14:21 AMoracle.sysman.emcp.EMReposConfig createRepositoryINFO: Creating the EMrepository (this may take a while) ...Oct 4, 2014 6:17:57 AMoracle.sysman.emcp.EMReposConfig invokeINFO: Repositorysuccessfully createdEnterprise Managerconfiguration completed successfullyFINISHED EMCA at Oct 4, 20146:17:57 AM ###emca -config dbcontrol db[oracle@db01 /]$ emca-config dbcontrol db STARTED EMCA at Oct 4, 20146:24:04 AMEM Configuration Assistant,Version 11.2.0.3.0 ProductionCopyright (c) 2003, 2011,Oracle.  All rights reserved. Enter the followinginformation:Database SID: wooDatabase Control is alreadyconfigured for the database wooYou have chosen to configureDatabase Control for managing the database wooThis will remove theexisting configuration and the default settings and perform a freshconfigurationDo you wish to continue?[yes(Y)/no(N)]: yListener ORACLE_HOME [/DBSoft/Product/11.2.4/db_1 ]:Password for SYS user:  Password for DBSNMPuser:  Password for SYSMANuser:  Email address fornotifications (optional):Outgoing Mail (SMTP) serverfor notifications (optional):----------------------------------------------------------------- You have specified thefollowing settingsDatabase ORACLE_HOME................ /DBSoft/Product/11.2.4/db_1Local hostname................ db01Listener ORACLE_HOME................ /DBSoft/Product/11.2.4/db_1Listener port number................ 1521Database SID................ wooEmail address fornotifications ...............Outgoing Mail (SMTP) serverfor notifications ...............-----------------------------------------------------------------Do you wish to continue?[yes(Y)/no(N)]: yOct 4, 2014 6:24:49 AMoracle.sysman.emcp.EMConfig performINFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca_2014_10_04_06_24_04.log.Oct 4, 2014 6:24:50 AMoracle.sysman.emcp.util.DBControlUtil stopOMSINFO: Stopping DatabaseControl (this may take a while) ...Oct 4, 2014 6:24:54 AMoracle.sysman.emcp.EMReposConfig uploadConfigDataToRepositoryINFO: Uploadingconfiguration data to EM repository (this may take a while) ...Oct 4, 2014 6:25:53 AMoracle.sysman.emcp.EMReposConfig invokeINFO: Uploaded configurationdata successfullyOct 4, 2014 6:25:57 AMoracle.sysman.emcp.util.DBControlUtil secureDBConsoleINFO: Securing DatabaseControl (this may take a while) ...Oct 4, 2014 6:26:03 AMoracle.sysman.emcp.util.DBControlUtil secureDBConsoleINFO: Database Controlsecured successfully.Oct 4, 2014 6:26:03 AMoracle.sysman.emcp.util.DBControlUtil startOMSINFO: Starting DatabaseControl (this may take a while) ...Oct 4, 2014 6:26:23 AMoracle.sysman.emcp.EMDBPostConfig performConfigurationINFO: Database Controlstarted successfullyOct 4, 2014 6:26:23 AMoracle.sysman.emcp.EMDBPostConfig performConfigurationINFO:>>>>>>>>>>> The Database Control URL ishttps://db01:5500/em <<<<<<<<<<<Oct 4, 2014 6:26:25 AMoracle.sysman.emcp.EMDBPostConfig invokeWARNING: ************************  WARNING ************************Management Repository hasbeen placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in thefile: /DBSoft/Product/11.2.4/db_1/db01_woo/sysman/config/emkey.ora. Ensure thisfile is backed up as the encrypted data will become unusable if this file islost. ***********************************************************Enterprise Managerconfiguration completed successfullyFINISHED EMCA at Oct 4, 20146:26:25 AM[oracle@db01 /]$

 

17、至此，升级已经全部完成。