同機上升級oracle10.2.0.5到11.2.0.4

      在同一臺機器上將oracle10.2.0.4升級到11.2.0.5。以下是升級過程
1、在本機上首先安裝oracle11.2.0.4的資料庫環境

安裝過程就不用多說了，建好目錄，上傳相關的包等，正常進行安裝即可！

2、將11g ORACLE_HOME/rdbms/admin/utlu112i.sql複製到oracle10g相應的目錄中

[oracle@scmtest ~]$ cp /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql / u01/app/oracle/product/10.2.0/db1/rdbms/admin/

[oracle@scmtest ~]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db1

[oracle@scmtest ~]$ cd /u01/app/oracle/product/10.2.0/db1/bin

./sqlplus "/as sysdba"

SQL> spool /u02/upgrade.log;

SQL> @/u01/app/oracle/product/10.2.0/db1/rdbms/admin/utlu112i.sql;

得到一個檢查結果：

SQL> @?/rdbms/admin/utlu112i.sql;
Oracle Database 11.2 Pre-Upgrade Information Tool 11-12-2013 08:24:15
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:          YLINDB
--> version:       10.2.0.5.0
--> compatible:    10.2.0.5.0
--> blocksize:     8192
--> platform:      Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 1036 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 1142 MB
--> TEMP1 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:
WARNING: --> "java_pool_size" needs to be increased to at least 64 MB
.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:
WARNING: --> "java_pool_size" needs to be increased to at least 128 MB
.
**********************************************************************
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]
**********************************************************************
--> background_dump_dest         11.1       DEPRECATED   replaced by  "diagnostic_dest"
--> user_dump_dest               11.1       DEPRECATED   replaced by  "diagnostic_dest"
.

**********************************************************************
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
--> Data Mining                  [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> 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 10.2.0.5.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 MDSYS has 18 INVALID objects.
.... USER DMSYS has 2 INVALID objects.
.... USER PUBLIC has 341 INVALID objects.
.... USER CTXSYS has 1 INVALID objects.
.... USER ORACLE_OCM has 2 INVALID objects.
.... USER EXFSYS has 8 INVALID objects.
.... USER DBSNMP has 4 INVALID objects.
.... USER SYSMAN has 3 INVALID objects.
.... USER XDB has 3 INVALID objects.
.... USER SYS has 411 INVALID objects.
.... USER WMSYS has 25 INVALID objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while 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 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' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

**********************************************************************

根據結果調整引數，記憶體資源。
主要調整：java_pool_size,列出來的幾個表空間的大小是否滿足要求

3、建立11g引數檔案

[oracle@scmtest ~]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db1

[oracle@scmtest ~]$ cd /u01/app/oracle/product/10.2.0/db1/bin

[oracle@scmtest ~]$ ./sqlplus "/as sysdba"

SQL> create pfile='/u02/upgrade11g.ora' from spfile;

修改引數檔案:
java_pool_size                >=128m
background_dump_dest          =>diagnostic_dest
user_dump_dest                =>diagnostic_dest

這裡必須修改，否則到後面啟動時報引數錯誤

[oracle@scmtest ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@scmtest ~]$ cd /u01/app/oracle/product/11.2.0/db_1/bin
[oracle@scmtest ~]$ ./sqlplus "/as sysdba"

SQL> create spfile from pfile = ='/u02/upgrade11g.ora' ;

4、以upgrade啟動資料庫（11g ORACLE_HOME）

sqlplus "/as sysdba"

SQL> STARTUP UPGRADE

5、開始升級

SQL> SPOOL /u02/upgrade.log

SQL> @catupgrd.sql

這裡執行時間很長，執行結束後，會自動關閉資料庫，並退出sqlplus

The catupgrd.sql script determines which upgrade scripts must be run, runs them, and then shuts down the database. You must run the script in the Oracle Database 11g Release 2 (11.2) environment.

The upgrade script creates and alters certain data dictionary tables. It also upgrades or installs the following database components in the new Oracle Database 11g Release 2 (11.2) database:

6、重新啟動資料庫，檢視升級結果
SQL> STARTUP

SQL> @?/rdbms/admin/utlu112s.sql

檢查升級結果

7、執行catuppst.sql，執行升級

SQL> @?/rdbms/admin/catuppst.sql

This may generate the following informational messages:
Generating apply and rollback scripts...
Check the following file for errors:
Apply script: .*
Rollback script: .*
Executing script file...
Updating registry...
Check the following log file for errors:

Generating apply and rollback scripts...
Check the following file for errors:
.../cfgtoollogs/catbundle/catbundle_PSU_*.log
Apply script:
.../rdbms/admin/catbundle_PSU_*_APPLY.sql
Rollback script:
.../rdbms/admin/catbundle_PSU_*_ROLLBACK.sql
Executing script file...

Updating registry...
Check the following log file for errors:
.../cfgtoollogs/catbundle/catbundle_PSU_*.log

執行以下指令碼，對失效物件進行再編譯
SQL> @?/rdbms/admin/utlrp.sql

檢查是否還存在失效物件

SQL> SELECT count(*) FROM dba_invalid_objects;

SQL> SELECT distinct object_name FROM dba_invalid_objects;