oracle 資料庫從10.2.0.4升級到11.2.0.3

dingzihan發表於2014-07-22
Oracle資料庫
       環境：linux 5.4  紅帽雙機（RHCS）
              資料庫:  10.2.0.4
     前期準備：在兩臺機器的相同目錄下安裝11.2.0.3的資料庫軟體，別安裝在以前的10G目錄下。
      一、停雙機，掛儲存
            service  rgmanager  stop
            service  cman  stop
            mount /dev/arc_vg01/arc_lv01 /oradata
            mount /dev/oralnx_vg01/ora_lv01 /oradata/oralnx
            mount /dev/oralnx_vg01/ora_lv02 /oradata/lbsdata
            mount /dev/lbsdata_vg01/lbs_lv01 /oradata/lbsdata01
            mount /dev/lbsdata_vg02/lbs_lv02 /oradata/lbsdata02
            mount /dev/lbsdata_vg03/lbs_lv03  /oradata/lbsdata03
            mount /dev/lbsdata_vg04/lbs_lv04 /oradata/lbsdata04
       二、啟動資料庫
            su - oracle
            sqlplus / as sysdba
            startup;
        三、做測試前的準備
     在11G的安裝目錄下，執行utlu112i.sql，進行升級前的檢查。      

SQL> @/home/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 07-21-2014 14:10:21
Script Version: 11.2.0.3.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--&gt name:          ORALNX
--&gt version:       10.2.0.4.0
--&gt compatible:    10.2.0.3.0
--&gt blocksize:     8192
--&gt platform:      Linux x86 64-bit
--&gt timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--&gt SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 9551 MB
--&gt UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--&gt SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 19398 MB
--&gt 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.
**********************************************************************
--&gt If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.

--&gt 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]
**********************************************************************
--&gt background_dump_dest         11.1       DEPRECATED   replaced by  "diagnostic_dest"
--&gt user_dump_dest               11.1       DEPRECATED   replaced by  "diagnostic_dest"
.

**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--&gt Oracle Catalog Views         [upgrade]  VALID
--&gt Oracle Packages and Types    [upgrade]  VALID
--&gt JServer JAVA Virtual Machine [upgrade]  VALID
--&gt Oracle XDK for Java          [upgrade]  VALID
--&gt Oracle Workspace Manager     [upgrade]  VALID
--&gt OLAP Analytic Workspace      [upgrade]  VALID
--&gt OLAP Catalog                 [upgrade]  VALID
--&gt EM Repository                [upgrade]  VALID
--&gt Oracle Text                  [upgrade]  VALID
--&gt Oracle XML Database          [upgrade]  VALID
--&gt Oracle Java Packages         [upgrade]  VALID
--&gt Oracle interMedia            [upgrade]  VALID
--&gt Spatial                      [upgrade]  VALID
--&gt Data Mining                  [upgrade]  VALID
--&gt Expression Filter            [upgrade]  VALID
--&gt Rule Manager                 [upgrade]  VALID
--&gt Oracle OLAP API              [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --&gt 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.4.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --&gt 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 JSHX has 5 INVALID objects.
.... USER LBS has 83 INVALID objects.
.... USER SPOT has 11 INVALID objects.
WARNING: --&gt 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.
WARNING: --&gt Your recycle bin contains 65 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.
.
**********************************************************************
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.

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

四、收集字典統計資訊。
EXECUTE dbms_stats.gather_dictionary_stats;

五、開始升級
在11G ORACLE_HOME/bin下執行DBUA。
下面的操作比較簡單，根據圖形介面的操作提示進行操作。在操作的過程中，我一直在監控日誌，中間有啟停資料庫的操作。由於資料量比較大，有3、4個T，但3個小時總算搞完了。

六、升級完後，進行資料庫檢查，發現一臺ORACLE_HOME下有spfile 檔案、密碼檔案已經TNS檔案，於是將這幾個檔案拷貝到另一臺。手動啟資料，如果正常的話，可以起雙機進行測試。
     具體操作：停止資料庫。
            umount  /oradata/oralnx
            umount   /oradata/lbsdata
            umount  /oradata/lbsdata01
            umount   /oradata/lbsdata02
            umount  /oradata/lbsdata03
            umount  /oradata/lbsdata04
            umount  /oradata
   啟雙機，注意先在一臺上啟，啟完後再在另一臺上啟。
    service  cman   start
    service  rgmanager  start
            
附錄：通過指令碼升級10.2.0.4到11.2.0.3
1、安裝11.2.0.3的patchset(安裝在新的ORACLE_HOME下）
2、升級前的預檢查
    sqlplus / as sysdba
    SQL>spool  upgrade_info.log
    SQL>@$ORACLE_HOME/rdbms/admin/utlu112i.sql
     SQL>spool  off
    檢查upgrade_info.log 日誌資訊，將相關警告及錯誤一一解決
3、手動升級資料庫
    SQL>shutdown  immediate
    SQL>startup  upgrade
    SQL>spool  upgrade.log
    SQL>@catupgrd.sql
    SQL>startup
    SQL>@utlu112s.sql
    SQL>@catuppst.sql
    SQL>@utlrp.sql
    SQL>select count(*)  from dba_invalid_objects;
    SQL>select  distinct  object_name  from dba_invalid_objects;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22969361/viewspace-1224553/，如需轉載，請註明出處，否則將追究法律責任。

相關文章