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

ewr212

说明：

        这篇文章主要是记录下单实例环境下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、查看数据库和操作系统相关信息:

    [iyunv@db01 ~]# uname -a  
    Linux db01 2.6.18-308.el5 #1SMP Fri Jan 27 17:17:51 EST 2012 x86_64 x86_64 x86_64 GNU/Linux  
      
    [iyunv@db01 ~]# lsb_release-a  
    LSB 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-noarch  
    Distributor ID:RedHatEnterpriseServer  
    Description:    Red Hat Enterprise Linux Server release 5.8(Tikanga)  
    Release:        5.8  
    Codename:       Tikanga  
      
    [iyunv@db01 ~]# su - oracle-c "sqlplus / as sysdba";  
    SQL*Plus: Release 11.2.0.3.0Production on Fri Oct 3 21:32:02 2014  
    Copyright (c) 1982, 2011,Oracle.  All rights reserved.  
      
    Connected to:  
    Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production  
    With the Partitioning, OLAP,Data Mining and Real Application Testing options  
      
    SQL> select name fromv$database;  
      
      
    NAME  
    ---------  
    WOO  
      
    SQL>   



2、备份数据库


    [oracle@db01 ~]$ rman target/  
      
      
    Recovery Manager: Release11.2.0.3.0 - Production on Fri Oct 3 21:43:04 2014  
    Copyright (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-14  
    current log archived  
    using target databasecontrol file instead of recovery catalog  
    allocated channel:ORA_DISK_1  
    channel ORA_DISK_1: SID=149device type=DISK  
    channel ORA_DISK_1: startingarchived log backup set  
    channel ORA_DISK_1:specifying archived log(s) in backup set  
    input archived log thread=1sequence=15 RECID=1 STAMP=860017183  
    input archived log thread=1sequence=16 RECID=2 STAMP=860017184  
    input archived log thread=1sequence=17 RECID=3 STAMP=860017186  
    input archived log thread=1sequence=18 RECID=4 STAMP=860017186  
    input archived log thread=1sequence=19 RECID=5 STAMP=860017188  
    input archived log thread=1sequence=20 RECID=6 STAMP=860017387  
    channel ORA_DISK_1: startingpiece 1 at 03-OCT-14  
    channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14  
    piecehandle=/DBBackup/Phycal/full_01pk5knb_1_1.bak tag=TAG20141003T214307comment=NONE  
    channel ORA_DISK_1: backupset complete, elapsed time: 00:00:02  
    channel 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=860017183  
    archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_16_b2x9s05l_.arcRECID=2 STAMP=860017184  
    archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_17_b2x9s2nx_.arcRECID=3 STAMP=860017186  
    archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_18_b2x9s2od_.arcRECID=4 STAMP=860017186  
    archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_19_b2x9s4dr_.arcRECID=5 STAMP=860017188  
    archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_20_b2x9zbz2_.arcRECID=6 STAMP=860017387  
    Finished backup at 03-OCT-14  
      
    Starting backup at 03-OCT-14  
    using channel ORA_DISK_1  
    channel ORA_DISK_1: startingfull datafile backup set  
    channel ORA_DISK_1:specifying datafile(s) in backup set  
    input datafile filenumber=00001 name=/DBData/woo/system01.dbf  
    input datafile filenumber=00002 name=/DBData/woo/sysaux01.dbf  
    input datafile filenumber=00005 name=/DBData/woo/example01.dbf  
    input datafile filenumber=00003 name=/DBData/woo/undotbs01.dbf  
    input datafile filenumber=00004 name=/DBData/woo/users01.dbf  
    channel ORA_DISK_1: startingpiece 1 at 03-OCT-14  
    channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14  
    piecehandle=/DBSoft/fast_recovery_area/WOO/backupset/2014_10_03/o1_mf_nnndf_TAG20141003T214309_b2x9zfm5_.bkptag=TAG20141003T214309 comment=NONE  
    channel ORA_DISK_1: backupset complete, elapsed time: 00:01:45  
    channel ORA_DISK_1: startingfull datafile backup set  
    channel ORA_DISK_1:specifying datafile(s) in backup set  
    including current controlfile in backup set  
    including current SPFILE inbackup set  
    channel ORA_DISK_1: startingpiece 1 at 03-OCT-14  
    channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14  
    piecehandle=/DBSoft/fast_recovery_area/WOO/backupset/2014_10_03/o1_mf_ncsnf_TAG20141003T214309_b2xb2qlm_.bkptag=TAG20141003T214309 comment=NONE  
    channel ORA_DISK_1: backupset complete, elapsed time: 00:00:01  
    Finished backup at 03-OCT-14  
      
      
    Starting backup at 03-OCT-14  
    current log archived  
    using channel ORA_DISK_1  
    channel ORA_DISK_1: startingarchived log backup set  
    channel ORA_DISK_1:specifying archived log(s) in backup set  
    input archived log thread=1sequence=21 RECID=7 STAMP=860017496  
    channel ORA_DISK_1: startingpiece 1 at 03-OCT-14  
    channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14  
    piecehandle=/DBBackup/Phycal/full_04pk5kqo_1_1.bak tag=TAG20141003T214456comment=NONE  
    channel ORA_DISK_1: backupset complete, elapsed time: 00:00:01  
    channel 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=860017496  
    Finished backup at 03-OCT-14  
      
      
    RMAN>  



3、停止数据库


    ###停止数据库  
      
    SQL> shutdown immediate  
    Database 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  
      
    ###停止EM  
      
    SQL> host emctl stopdbconsole  
      
    Oracle 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进程，检查是否已经停止完毕  
      
    [iyunv@db01 ~]# ps -ef|grepora  
    root      4971 4944  0 01:46 pts/0    00:00:00 grep ora  



4、备份老的ORACLE_HOME和oraInventory


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



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


    [oracle@db01 ~]$ ll  
      
    total 2489644  
    drwxr-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脚本

    [iyunv@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 by  
    Database ConfigurationAssistant when a database is created  
    Finished running genericpart of root script.  
    Now product-specific rootactions will be performed.  
    Finished product-specificroot actions.  
      
    [iyunv@db01 ~]#  

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


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


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


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

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

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


    [iyunv@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.4  
    woo:/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]$ ls  
      
    samples  shrept.lst  
      
    [oracle@db01 admin]$ cp -r/DBSoft/Product/11.2.3/db_1/network/admin/* $ORACLE_HOME/network/admin  
    [oracle@db01 admin]$ ls  
    listener.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 2014  
    Copyright (c) 1982, 2013,Oracle.  All rights reserved.  
    Connected to an idleinstance.  
      
      
    SQL> startup upgrade;  
      
    ORACLE instance started.  
    Total System Global Area2037673984 bytes  
    Fixed Size                  2254704 bytes  
    Variable Size            1811941520 bytes  
    Database Buffers          218103808 bytes  
    Redo Buffers                5373952 bytes  
    Database mounted.  
    Database opened.  
      
    SQL>@?/rdbms/admin/utlu112i.sql    ---执行升级前检查  
      
    Oracle Database 11.2Pre-Upgrade Information Tool 10-04-2014 02:27:38  
    Script 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 to  
    upgrading the database.  
    To gather dictionarystatistics execute the following command  
    while connected as SYSDBA:  
      
      
        EXECUTE dbms_stats.gather_dictionary_stats;  
      
      
    **********************************************************************  
    Oracle recommends removingall hidden parameters prior to upgrading.  
    To view existing hiddenparameters execute the following command  
    while 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 commands  
    while 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     0  
    max_shared_servers                   integer  
    shared_memory_address                integer     0  
    shared_pool_reserved_size            big integer 8M  
    shared_pool_size                     big integer 160M  
    shared_server_sessions               integer  
    shared_servers                       integer     1  
      
    SQL> alter system setshared_pool_size=800m scope=spfile;  
      
      
    System altered.  
      
      
      
    SQL> shutdown immediate  
      
    Database closed.  
    Database dismounted.  
    ORACLE instance shut down.  
      
    SQL> startup mountl  
      
    SP2-0714: invalidcombination of STARTUP options  
      
    SQL> startup mount;  
      
    ORACLE instance started.  
    Total System Global Area2872786944 bytes  
    Fixed Size                  2256712 bytes  
    Variable Size            2634023096 bytes  
    Database Buffers          218103808 bytes  
    Redo Buffers               18403328 bytes  
      
    Database 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     52428800  
      
    04-OCT-14 02.41.43.000000000AM  
      
                                                                               YES  
    MACLEAN_ROLLBACK  
      
      
      
    再次运行检查：  
      
    SQL>@?/rdbms/admin/utlu112i.sql  
      
    Oracle Database 11.2Pre-Upgrade Information Tool 10-04-2014 03:26:20  
    Script 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:         11  
      
    WARNING: --> FlashbackRecovery Area Set.  Please ensureadequate disk space              inrecover  
    y 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 to  
    upgrading the database.  
    To gather dictionarystatistics execute the following command  
    while connected as SYSDBA:  
      
      
        EXECUTE dbms_stats.gather_dictionary_stats;  
      
    **********************************************************************  
    Oracle recommends removingall hidden parameters prior to upgrading.  
    To view existing hiddenparameters execute the following command  
    while 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 commands  
    while 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 immediate  
      
    Database closed.  
    Database dismounted.  
    ORACLE instance shut down.  
      
    SQL> startup upgrade;  
      
    ORACLE instance started.  
    Total System Global Area2872786944 bytes  
    Fixed Size                  2256712 bytes  
    Variable Size            2634023096 bytes  
    Database Buffers          218103808 bytes  
    Redo Buffers               18403328 bytes  
    Database mounted.  
      
    Database opened.  
      
    SQL> set echo on  
    SQL> spool/home/oracle/upgrade.log  
    SQL> 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 Time  
    Name                                    Status       Number      HH:MM:SS  
    .  
    Oracle Server  
    .                                        VALID      11.2.0.4.0  00:08:38  
    JServer JAVA Virtual Machine  
    .                                        VALID      11.2.0.4.0  00:04:26  
    Oracle Workspace Manager  
    .                                        VALID      11.2.0.4.0  00:00:25  
    OLAP Analytic Workspace  
    .                                        VALID      11.2.0.4.0  00:01:27  
    OLAP Catalog  
    .                                        VALID      11.2.0.4.0  00:00:32  
    Oracle OLAP API  
    .                                        VALID      11.2.0.4.0  00:00:19  
    Oracle Enterprise Manager  
    .                                        VALID      11.2.0.4.0  00:06:25  
    Oracle XDK  
    .                                         VALID      11.2.0.4.0  00:00:26  
    Oracle Text  
    .                                        VALID      11.2.0.4.0  00:00:24  
    Oracle XML Database  
    .                                        VALID      11.2.0.4.0  00:01:46  
    Oracle Database JavaPackages  
    .                                         VALID      11.2.0.4.0  00:00:08  
    Oracle Multimedia  
    .                                        VALID      11.2.0.4.0  00:01:43  
    Spatial  
    .                                        VALID      11.2.0.4.0  00:05:34  
    Oracle Expression Filter  
    .                                        VALID      11.2.0.4.0  00:00:06  
    Oracle Rules Manager  
    .                                        VALID      11.2.0.4.0  00:00:07  
    Oracle Application Express  
    .                                        VALID     3.2.1.00.12  
    Final Actions  
    .                                                               00:00:00  
    Total Upgrade Time: 00:32:35  
      
      
    PL/SQL proceduresuccessfully completed.  
      
      
    04:01:36 SQL>   
    04:01:36 SQL> SETSERVEROUTPUT OFF  
    04:01:36 SQL> SET VERIFYON  
    04: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> DOC  
    04:01:54 DOC>#######################################################################  
    04:01:54DOC>#######################################################################  
    04:01:54 DOC>  
    04:01:54 DOC>   The above sql script is the final step ofthe upgrade. Please  
    04:01:54 DOC>   review any errors in the spool log file. Ifthere are any errors in  
    04:01:54 DOC>   the spool file, consult the Oracle DatabaseUpgrade Guide for  
    04:01:54 DOC>   troubleshooting recommendations.  
    04:01:54 DOC>  
    04:01:54 DOC>   Next restart for normal operation, and thenrun utlrp.sql to  
    04: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 to  
    04:01:54 DOC>   upgrade, then please run the DBMS_DSTpackage.  DBMS_DST will upgrade  
    04:01:54 DOC>   TIMESTAMP WITH TIME ZONE data to use thelatest time zone file shipped  
    04: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 off  
    04:01:54 SQL> SETERRORLOGGING OFF;  
    04:01:54 SQL>   
    04:01:54 SQL> REM END OFCATUPGRD.SQL  
    04: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 order  
    04:01:54 SQL> REM                to connect to the upgraded db.  
    04:01:54 SQL> exit  
      
    Disconnected from OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production  
    With 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 2014  
    Copyright (c) 1982, 2013,Oracle.  All rights reserved.  
      
      
    Connected to an idleinstance.  
      
      
    SQL> startup  
      
    ORACLE instance started.  
      
      
    Total System Global Area 2872786944bytes  
    Fixed Size                  2256712 bytes  
    Variable Size            2634023096 bytes  
    Database Buffers          218103808 bytes  
    Redo Buffers               18403328 bytes  
    Database mounted.  
      
    Database opened.  
      
    SQL> @?/rdbms/admin/utlrp  
      
      
    TIMESTAMP  
    --------------------------------------------------------------------------------  
      
    COMP_TIMESTAMPUTLRP_BGN  2014-10-04 04:14:57  
    DOC>   The following PL/SQL block invokesUTL_RECOMP to recompile invalid  
    DOC>   objects in the database. Recompilation timeis proportional to the  
    DOC>   number of invalid objects in the database,so this command may take  
    DOC>   a long time to execute on a database with alarge number of invalid  
    DOC>   objects.  
    DOC>  
    DOC>   Use the following queries to trackrecompilation progress:  
    DOC>  
    DOC>   1. Query returning the number of invalidobjects remaining. This  
    DOC>      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 number  
    DOC>      should increase with time.  
    DOC>         SELECT COUNT(*) FROMUTL_RECOMP_COMPILED;  
    DOC>  
    DOC>   This script automatically chooses serial orparallel recompilation  
    DOC>   based on the number of CPUs available(parameter cpu_count) multiplied  
    DOC>   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 parallel  
    DOC>   recompilation. Jobs are created withoutinstance affinity so that they  
    DOC>   can migrate across RAC nodes. Use thefollowing queries to verify  
    DOC>   whether UTL_RECOMP jobs are being createdand run correctly:  
    DOC>  
    DOC>   1. Query showing jobs created by UTL_RECOMP  
    DOC>         SELECT job_name FROM dba_scheduler_jobs  
    DOC>            WHERE job_name like'UTL_RECOMP_SLAVE_%';  
    DOC>  
    DOC>   2. Query showing UTL_RECOMP jobs that arerunning  
    DOC>         SELECT job_name FROMdba_scheduler_running_jobs  
    DOC>            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 compiled  
    DOC> with errors.  
    DOC>  
    DOC> If the number ishigher than expected, please examine the error  
    DOC> messages reportedwith each object (using SHOW ERRORS) to see if they  
    DOC> point to systemmisconfiguration or resource constraints that must be  
    DOC> fixed beforeattempting to recompile these objects.  
    DOC>#  
      
      
    OBJECTS WITH ERRORS  
    -------------------  
                      0  
      
      
      
    DOC> The following queryreports the number of errors caught during  
    DOC> recompilation. Ifthis number is non-zero, please query the error  
    DOC> messages in thetable UTL_RECOMP_ERRORS to see if any of these errors  
    DOC> are due tomisconfiguration or resource constraints that must be  
    DOC> 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.0  
    Oracle ApplicationExpress                VALID          3.2.1.00.12  
    Oracle EnterpriseManager                 VALID          11.2.0.4.0  
    OLAP Catalog                             VALID          11.2.0.4.0  
    Spatial                                  VALID          11.2.0.4.0  
    Oracle Multimedia                        VALID          11.2.0.4.0  
    Oracle XML Database                      VALID          11.2.0.4.0  
    Oracle Text                              VALID          11.2.0.4.0  
    Oracle ExpressionFilter                  VALID          11.2.0.4.0  
    Oracle Rules Manager                     VALID          11.2.0.4.0  
    Oracle WorkspaceManager                  VALID          11.2.0.4.0  
    Oracle Database CatalogViews             VALID          11.2.0.4.0  
    Oracle Database Packages andTypes        VALID          11.2.0.4.0  
    JServer JAVA VirtualMachine              VALID          11.2.0.4.0  
    Oracle XDK                               VALID          11.2.0.4.0  
    Oracle Database JavaPackages             VALID          11.2.0.4.0  
    OLAP Analytic Workspace                  VALID          11.2.0.4.0  
    Oracle 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 -reposdrop  
      
    STARTED EMCA at Oct 4, 20146:11:41 AM  
    EM Configuration Assistant,Version 11.2.0.3.0 Production  
      
    Copyright (c) 2003, 2011,Oracle.  All rights reserved.  
      
      
    Enter the followinginformation:  
    Database SID: woo  
    Listener port number: 1521  
    Password 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)]: y  
    Oct 4, 2014 6:11:53 AMoracle.sysman.emcp.EMConfig perform  
    INFO: 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 invoke  
    INFO: Dropping the EMrepository (this may take a while) ...  
    Oct 4, 2014 6:13:37 AMoracle.sysman.emcp.EMReposConfig invoke  
    INFO: Repositorysuccessfully dropped  
    Enterprise Managerconfiguration completed successfully  
    FINISHED EMCA at Oct 4, 20146:13:37 AM  
      
    ####emca -repos create  
      
      
    [oracle@db01 /]$ emca -reposcreate  
      
      
    STARTED EMCA at Oct 4, 20146:14:07 AM  
    EM Configuration Assistant,Version 11.2.0.3.0 Production  
    Copyright (c) 2003, 2011,Oracle.  All rights reserved.  
      
    Enter the followinginformation:  
    Database SID: woo  
    Listener port number: 1521  
    Password for SYS user:   
    Password for SYSMANuser:   
      
    Do you wish to continue?[yes(Y)/no(N)]: y  
    Oct 4, 2014 6:14:20 AM oracle.sysman.emcp.EMConfigperform  
    INFO: 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 createRepository  
    INFO: Creating the EMrepository (this may take a while) ...  
    Oct 4, 2014 6:17:57 AMoracle.sysman.emcp.EMReposConfig invoke  
    INFO: Repositorysuccessfully created  
    Enterprise Managerconfiguration completed successfully  
    FINISHED 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 AM  
    EM Configuration Assistant,Version 11.2.0.3.0 Production  
    Copyright (c) 2003, 2011,Oracle.  All rights reserved.  
      
      
    Enter the followinginformation:  
    Database SID: woo  
    Database Control is alreadyconfigured for the database woo  
    You have chosen to configureDatabase Control for managing the database woo  
    This will remove theexisting configuration and the default settings and perform a freshconfiguration  
    Do you wish to continue?[yes(Y)/no(N)]: y  
    Listener 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 settings  
      
    Database ORACLE_HOME................ /DBSoft/Product/11.2.4/db_1  
    Local hostname................ db01  
    Listener ORACLE_HOME................ /DBSoft/Product/11.2.4/db_1  
    Listener port number................ 1521  
    Database SID................ woo  
    Email address fornotifications ...............  
    Outgoing Mail (SMTP) serverfor notifications ...............  
    -----------------------------------------------------------------  
      
    Do you wish to continue?[yes(Y)/no(N)]: y  
    Oct 4, 2014 6:24:49 AMoracle.sysman.emcp.EMConfig perform  
    INFO: 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 stopOMS  
    INFO: Stopping DatabaseControl (this may take a while) ...  
    Oct 4, 2014 6:24:54 AMoracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository  
    INFO: Uploadingconfiguration data to EM repository (this may take a while) ...  
    Oct 4, 2014 6:25:53 AMoracle.sysman.emcp.EMReposConfig invoke  
    INFO: Uploaded configurationdata successfully  
    Oct 4, 2014 6:25:57 AMoracle.sysman.emcp.util.DBControlUtil secureDBConsole  
    INFO: Securing DatabaseControl (this may take a while) ...  
    Oct 4, 2014 6:26:03 AMoracle.sysman.emcp.util.DBControlUtil secureDBConsole  
    INFO: Database Controlsecured successfully.  
    Oct 4, 2014 6:26:03 AMoracle.sysman.emcp.util.DBControlUtil startOMS  
    INFO: Starting DatabaseControl (this may take a while) ...  
    Oct 4, 2014 6:26:23 AMoracle.sysman.emcp.EMDBPostConfig performConfiguration  
    INFO: Database Controlstarted successfully  
    Oct 4, 2014 6:26:23 AMoracle.sysman.emcp.EMDBPostConfig performConfiguration  
    INFO:>>>>>>>>>>> The Database Control URL ishttps://db01:5500/em <<<<<<<<<<<  
    Oct 4, 2014 6:26:25 AMoracle.sysman.emcp.EMDBPostConfig invoke  
    WARNING:   
      
    ************************  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 successfully  
    FINISHED EMCA at Oct 4, 20146:26:25 AM  
      
    [oracle@db01 /]$  



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