Upgrade 11.2.0.1 DB/RDBMS to 11.2.0.2 in Linux 文章轉載自: Oracle Clinic – Maclean Liu的個人技術博客 [http://www.oracledatabase12g.com/]

Upgrade 11.2.0.1 DB/RDBMS to 11.2.0.2 in Linux

作者: Maclean Liu , post on September 7th, 2011 , English Version 
【本站文章除注明轉載外，均為本站原創編譯】
轉載請注明：文章轉載自: Oracle Clinic – Maclean Liu的個人技術博客 [http://www.oracledatabase12g.com/]
本文標題: Upgrade 11.2.0.1 DB/RDBMS to 11.2.0.2 in Linux 
本文永久地址: http://www.oracledatabase12g.com/archives/upgrade-11-2-0-1-db-rdbms-to-11-2-0-2-in-linux.html 

在<Upgrade 11.2.0.1 GI/CRS to 11.2.0.2 in Linux>一文中我們介紹了升級11.2.0.1 GI/CRS到11.2.0.2的詳細步驟，因為GI/CRS的版本總是要求大於DB/RDBMS，所以這是我們升級RDBMS資料庫軟體的前提條件。

接下來我們將具體介紹升級11.2.0.1 DB/RDBMS到 11.2.0.2的詳細步驟:

一、 下載補丁介質

11.2.0.2的patchset目前沒有公開的下載地址，因為updates.oracle.com目前已經不再提供ftp下載模式，所以我們只能通過登錄My Oracle Support後進入Patch欄目搜索Patchid並獲得加密的下載鏈結。

11.2.0.2補丁集的全稱是11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER (Patchset)(patchid:10098816)，可以通過10098816這個id到Patch欄目搜索，並找出對應平臺的介質zip包。如在Linux x86-64平臺上：

以上p10098816_112020_Linux-x86-64_1of7.zip和p10098816_112020_Linux-x86-64_2of7.zip ，這2個zip包對應為Database/RDBMS軟體的介質，我們不需要下載所有的7個zip包，有這2個升級資料庫軟體就已經足夠了。

完成以上2個軟體的下載後，分別解壓zip包:

unzip p10098816_112020_Linux-x86-64_1of7.zip -d  $PATCHHOME

unzip p10098816_112020_Linux-x86-64_2of7.zip -d  $PATCHHOME

二、以out of place方式安裝11.2.0.2 DB資料庫軟體

因為11.2.0.2的Patchset以後都是out of place的，所以我們可以不用像在11gr2以前那樣必須在原有安裝低版本軟體的基礎上才能升級軟體，而可以選擇在別的位置完全新安裝。

注意該步驟不需要停止資料庫實例，可以在前期工作中完成。

以DB/RDBMS資料庫軟體的擁有者身份(oracle用戶)啟動方才解壓目錄下的oui安裝介面:

su - oracle

(oracle)$ unset ORACLE_HOME ORACLE_BASE ORACLE_SID

(oracle)$ export DISPLAY=:0

(oracle)$ cd $PATCHHOME

(oracle)$ ./runInstaller

在Oracle Universal Installer介面下的Select Installation Options Screen選擇install database only.

在Grid Installation Options下若是RAC 資料庫則選擇Oracle Real Application cluster database installation，注意如果在該螢幕下出現[FATAL] [INS-35354] The system on which you are attempting to install Oracle RAC is not part of a valid cluster則可能是在之前的安裝Gird的過程中沒有正確的Update Inventory更新資訊庫資訊，見<11gr2 RAC安裝INS-35354問題一例>。

若是單節點資料庫則選擇Single instance database installation

在Specify Installation Location Screen上一般OUI會幫你自動匹配一個$ORACLE_BASE變數下不同於原有資料庫軟體安裝目錄的新目錄，確認這些目錄下有足夠的磁碟空間，保險起見空間應大於10GB。注意這是out of place安裝，所以千萬不要填入原有的安裝路徑。

以上安裝完成後OUI會提示要在所有節點上以root身份執行root.sh腳本:

su - root

(root #) /s01/orabase/product/11.2.0/dbhome_2/root.sh

Running Oracle 11g root script...

The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME=  /s01/orabase/product/11.2.0/dbhome_2

Enter the full pathname of the 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 Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

三、升級前的準備工作
以上我們完成了11.2.0.2 資料庫軟體的安裝工作，但是還沒有升級實例和資料字典。
在正式升級之前，極有必要完成一系列的備份和準備工作，這些準備工作可以詳見拙作<Oracle資料庫升級前必要的準備工作>

1.清理資料字典中的無用資料，包括審計和回收站，它們可能拉慢資料字典升級的速度:

TRUNCATE TABLE SYS.AUD$;

purge DBA_RECYCLEBIN;

2.如果條件允許的話，建議使用RMAN全量備份資料庫，前提是資料庫沒有達到TB級別。

rman target / catalog rman/rman@cata

backup as compressed backupset incremental level 0 database ;

3. 收集資料字典的統計資訊，若dictionary的統計資訊不準備可能導致catupgrd.sql字典升級腳本運行過久:

SQL> set timing on;

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

Elapsed: 00:00:27.81

4.運行dbupgdiag.sql升級資訊收集腳本， 該腳本可以提供資料庫的一些版本資訊和組建資訊，以下為該腳本的示例輸出內容:

cat db_upg_diag_VPROD_07-Sep-2011_0737.log

                          *** Start of LogFile ***

  Oracle Database Upgrade Diagnostic Utility       09-07-2011 19:37:23

===============

Database Uptime

===============

19:32 07-SEP-11

=================

Database Wordsize

=================

This is a 64-bit database

================

Software Version

================

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

=============

Compatibility

=============

Compatibility is set as 11.2.0.0.0

================

Component Status

================

Comp ID Component                          Status    Version        Org_Version    Prv_Version

------- ---------------------------------- --------- -------------- -------------- --------------

CATALOG Oracle Database Catalog Views      VALID     11.2.0.1.0

CATPROC Oracle Database Packages and Types VALID     11.2.0.1.0

OWM     Oracle Workspace Manager           VALID     11.2.0.1.0

RAC     Oracle Real Application Clusters   VALID     11.2.0.1.0

======================================================

List of Invalid Database Objects Owned by SYS / SYSTEM

======================================================

Number of Invalid Objects

------------------------------------------------------------------

There are no Invalid Objects

DOC>################################################################

DOC>

DOC> If there are no Invalid objects below will result in zero rows.

DOC>

DOC>################################################################

DOC>#

no rows selected

================================

List of Invalid Database Objects

================================

Number of Invalid Objects

------------------------------------------------------------------

There are no Invalid Objects

DOC>################################################################

DOC>

DOC> If there are no Invalid objects below will result in zero rows.

DOC>

DOC>################################################################

DOC>#

no rows selected

==============================================================

Identifying whether a database was created as 32-bit or 64-bit

==============================================================

DOC>###########################################################################

DOC>

DOC> Result referencing the string 'B023' ==> Database was created as 32-bit

DOC> Result referencing the string 'B047' ==> Database was created as 64-bit

DOC> When String results in 'B023' and when upgrading database to 10.2.0.3.0

DOC> (64-bit) , For known issue refer below articles

DOC>

DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While

DOC> Upgrading Or Patching Databases To 10.2.0.3

DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and

DOC> OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6

DOC>

DOC>###########################################################################

DOC>#

Metadata Initial DB Creation Info

-------- -----------------------------------

B047     Database was created as 64-bit

===================================================

Number of Duplicate Objects Owned by SYS and SYSTEM

===================================================

Counting duplicate objects ....

  COUNT(1)

----------

         4

=========================================

Duplicate Objects Owned by SYS and SYSTEM

=========================================

Querying duplicate objects ....

OBJECT_NAME                              OBJECT_TYPE

---------------------------------------- ----------------------------------------

AQ$_SCHEDULES                            TABLE

AQ$_SCHEDULES_PRIMARY                    INDEX

DBMS_REPCAT_AUTH                         PACKAGE BODY

DBMS_REPCAT_AUTH                         PACKAGE

DOC>

DOC>################################################################################

DOC>

DOC> If any objects found please follow below article.

DOC> Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema

DOC> Read the Exceptions carefully before taking actions.

DOC>

DOC>################################################################################

DOC>#

================

JVM Verification

================

JAVAVM - NOT Installed. Below results can be ignored

================================================

Checking Existence of Java-Based Users and Roles

================================================

DOC>

DOC>################################################################################

DOC>

DOC> There should not be any Java Based users for database version 9.0.1 and above.

DOC> If any users found, it is faulty JVM.

DOC>

DOC>################################################################################

DOC>#

User Existence

---------------------------

No Java Based Users

DOC>

DOC>###############################################################

DOC>

DOC> Healthy JVM Should contain Six Roles.

DOC> If there are more or less than six role, JVM is inconsistent.

DOC>

DOC>###############################################################

DOC>#

Role

------------------------------

No JAVA related Roles

Roles

=========================================

List of Invalid Java Objects owned by SYS

=========================================

There are no SYS owned invalid JAVA objects

DOC>

DOC>#################################################################

DOC>

DOC> Check the status of the main JVM interface packages DBMS_JAVA

DOC> and INITJVMAUX and make sure it is VALID.

DOC> If there are no Invalid objects below will result in zero rows.

DOC>

DOC>#################################################################

DOC>#

no rows selected

INFO: Below query should succeed with 'foo' as result.

select dbms_java.longname('foo') "JAVAVM TESTING" from dual

       *

ERROR at line 1:

ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier

                            *** End of LogFile ***

以上spool內容顯示所要升級的資料庫現有CATALOG、CATPROC、OWM和RAC組件，且沒有安裝JVM，升級JVM組建的資料字典將消耗較長的時間。

另外一個建議運行的腳本是utlu112i.sql，它位於新安裝的$ORACLE_HOME/rdbms/admin目錄下。

該腳本會給出一些升級前地建議，包括建議保證系統表空間和閃回區域有足夠的空間，以及收集資料字典的統計資訊，如以下輸出:

SQL> @/s01/orabase/product/11.2.0/dbhome_2/rdbms/admin/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 09-07-2011 20:02:30

Script Version: 11.2.0.2.0 Build: 001

.

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

Database:

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

--> name:          VPROD

--> version:       11.2.0.1.0

--> compatible:    11.2.0.0.0

--> blocksize:     8192

--> platform:      Linux x86 64-bit

--> timezone file: V11

.

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

Tablespaces: [make adjustments in the current environment]

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

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 267 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 150 MB

--> UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 253 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 61 MB

.

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

Flashback: ON

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

FlashbackInfo:

--> name:          +SYSTEMDG

--> limit:         4977 MB

--> used:          264 MB

--> size:          4977 MB

--> reclaim:       0 MB

--> files:         7

WARNING: --> Flashback Recovery Area Set.  Please ensure adequate disk space              in recover

y areas before performing an upgrade.

.

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

Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]

Note: Pre-upgrade tool was run on a lower version 64-bit database.

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

--> If Target Oracle is 32-Bit, refer here for Update Parameters:

-- No update parameter changes are required.

.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:

-- No update parameter changes are required.

.

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

Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]

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

-- No renamed parameters found. No changes are required.

.

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

Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]

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

-- No obsolete parameters found. No changes are required

.

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

Components: [The following database components will be upgraded or installed]

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

--> Oracle Catalog Views         [upgrade]  VALID

--> Oracle Packages and Types    [upgrade]  VALID

--> Real Application Clusters    [upgrade]  VALID

--> Oracle Workspace Manager     [upgrade]  VALID

.

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

Miscellaneous Warnings

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

WARNING: --> The "cluster_database" parameter is currently "TRUE"

.... and must be set to "FALSE" prior to running a manual upgrade.

WARNING: --> Database is using a timezone file older than version 14.

.... After the release migration, it is recommended that DBMS_DST package

.... be used to upgrade the 11.2.0.1.0 database timezone version

.... to the latest version which comes with the new release.

WARNING: --> Your recycle bin is turned on and currently contains no objects.

.... Because it is REQUIRED that the recycle bin be empty prior to upgrading

.... and your recycle bin is turned on, you may need to execute the command:

        PURGE DBA_RECYCLEBIN

.... prior to executing your upgrade to confirm the recycle bin is empty.

.

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

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 removing all hidden parameters prior to upgrading.

To view existing hidden parameters execute the following command

while connected AS SYSDBA:

    SELECT name,description from SYS.V$PARAMETER WHERE name

        LIKE '\_%' ESCAPE '\'

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

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

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.

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

5.如果資料庫很大那麼建議打開閃回資料庫flashback database，並創建還原點，這樣可以極大地縮短回退時間。

可以通過以下查詢判斷資料庫是或否啟用了flashback database功能:

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

------------------

NO

若顯示NO則說明之前沒有啟用資料庫閃回功能，若希望啟用資料庫閃回功能需要資料庫短時間停機:

關閉所有的資料庫實例

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

啟動某一套實例到mount 狀態

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size                  2212936 bytes

Variable Size             603982776 bytes

Database Buffers          637534208 bytes

Redo Buffers                8933376 bytes

Database mounted.

SQL> alter database flashback on;

Database altered.

在本節點打開資料庫，並啟動所有節點

SQL> alter database open;

Database altered.

以上在資料庫級別啟用了閃回flashback功能。

接著我們需要停止應用程式，注意在這一步之前的準備工作都可以線上完成，但是本步驟將要求停止一切應用程式的鏈結，關閉資料庫，並啟動到restrict限制模式，以便創建restore point，方便可能的升級回退。，strict模式避免了普通用戶的鏈結。

在所有節點上關閉資料庫實例，並在唯一節點上啟動資料庫到restrict模式。

startup restrict;

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size 2212936 bytes

Variable Size 603982776 bytes

Database Buffers 637534208 bytes

Redo Buffers 8933376 bytes

Database mounted.

Database opened.

SQL> conn maclean/maclean

ERROR:

ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Warning: You are no longer connected to ORACLE.

conn / as sysdba

SQL> create restore point maclean_rollback guarantee flashback database;

Restore point created.

SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE

---------- --------------------- --- ------------

TIME

---------------------------------------------------------------------------

RESTORE_POINT_TIME                                                          PRE

--------------------------------------------------------------------------- ---

NAME

--------------------------------------------------------------------------------

    601958                     1 YES     15941632

07-SEP-11 07.52.59.000000000 PM

                                                                            YES

MACLEAN_ROLLBACK

四、正式升級資料庫實例和資料字典

1. 關閉所有資料庫實例

2. 複製相關的pfile或spfile形式的參數到新的ORACLE_HOME下，這�我們假設使用ASM存儲共用的spfile，那麼只需要在所有節點上將init$SID.ora形式的檔拷貝即可:

(oracle $) cat $ORACLE_HOME/dbs/initVPROD1.ora

SPFILE='+SYSTEMDG/VPROD/spfileVPROD.ora'

(oracle $) cp $ORACLE_HOME/dbs/initVPROD1.ora /s01/orabase/product/11.2.0/dbhome_2/dbs

設置ORACLE_HOME和PATH變數指向新的11.2.0.2資料庫軟體

(oracle $) export ORACLE_HOME=/s01/orabase/product/11.2.0/dbhome_2

(oracle $) export PATH=/s01/orabase/product/11.2.0/dbhome_2/bin:$PATH

設置正確的ORACLE_SID

(oracle $) export ORACLE_SID=VPROD1

(oracle $) unset LD_LIBRARY_PATH

3. 啟動實例到nomount狀態，並修改cluster_database參數到spfile:

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size                  2226072 bytes

Variable Size             402655336 bytes

Database Buffers          838860800 bytes

Redo Buffers                8921088 bytes

SQL> alter system set cluster_database=false scope=spfile;

System altered.

4. 重啟實例到upgrade模式，升級資料字典，運行$ORACLE_HOME/rdbms/admin/catupgrd.sql腳本:

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size                  2226072 bytes

Variable Size             402655336 bytes

Database Buffers          838860800 bytes

Redo Buffers                8921088 bytes

Database mounted.

Database opened.

SQL> set echo on  

SQL> SPOOL /tmp/upgrade.log

SQL> set time on;

20:40:40 SQL> @/s01/orabase/product/11.2.0/dbhome_2/rdbms/admin/catupgrd.sql

在以上catupgrd.sql腳本運行過程中可以通過DBA_SERVER_REGISTRY視圖瞭解元件字典升級的進度

SQL> select * from DBA_SERVER_REGISTRY;

select * from DBA_SERVER_REGISTRY

              *

ERROR at line 1:

ORA-04063: view "SYS.DBA_SERVER_REGISTRY" has errors

or

ERROR at line 1:

ORA-04063: package body "SYS.DBMS_REGISTRY" has errors

在一開始會提示該視圖有錯誤，這不要緊，稍等一會。

SQL> select comp_name,status,version from dba_server_registry;

COMP_NAME                                          STATUS                           VERSION

-------------------------------------------------- --------------------------       ------------------------------

Oracle Workspace Manager                           UPGRADING                        11.2.0.1.0

Oracle Database Catalog Views                      VALID                            11.2.0.2.0

Oracle Database Packages and Types                 VALID                            11.2.0.2.0

Oracle Real Application Clusters                   VALID                            11.2.0.2.0

20:50:40 SQL>

20:50:40 SQL> Rem *********************************************************************

20:50:40 SQL> Rem END catupgrd.sql

20:50:40 SQL> Rem *********************************************************************

20:50:40 SQL>

以上catupgrd.sql腳本運行了10分鐘左右

重啟實例，運行utlrp.sql腳本編譯失效對象

sqlplus  / as sysdba

startup;

@?/rdbms/admin/utlrp

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN  2011-09-07 20:53:38

該腳本會自動根據cpu數目選擇並行度

DOC>   This script automatically chooses serial or parallel recompilation

DOC>   based on the number of CPUs available (parameter cpu_count) multiplied

DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC>   On RAC, this number is added across all RAC nodes.

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END  2011-09-07 20:55:09

該腳本耗時約2分鐘

修改cluster_database參數為true，並重啟所有節點實例

SQL> alter system set cluster_database=true scope=spfile;

System altered.

可以看到以上在資料庫僅安裝了CATALOG、CATPROC、OWM和RAC Cluster View 4種元件的情況下，catupgrd.sql字典升級腳本僅耗時10分鐘左右。 而實際的生產庫可能安裝了更多的元件，如JVM等元件將耗時較多。

以下總結了各Oracle元件升級字典的平均耗時，是一張十分有用的升級時間參考表:

DB Sample Upgrade Time

較少元件情況下

Component	HH:MM:SS
Oracle Server	00:16:17
JServer JAVA Virtual Machine	00:05:19
Oracle XDK	00:00:48
Oracle Text	00:00:58
Oracle XML Database	00:04:09
Oracle Database Java Packages	00:00:33
Gathering Statistics	00:02:43
Total Upgrade Time:	00:30:47

較多元件情況下

Component	HH:MM:SS
Oracle Server	00:16:17
JServer JAVA Virtual Machine	00:05:19
Oracle Workspace Manager	00:01:01
Oracle Enterprise Manager	00:10:13
Oracle XDK	00:00:48
Oracle Text	00:00:58
Oracle XML Database	00:04:09
Oracle Database Java Packages	00:00:33
Oracle Multimedia	00:07:43
Oracle Expression Filter	00:00:18
Oracle Rule Manager	00:00:12
Gathering Statistics	00:04:53
Total Upgrade Time:	00:52:31

5.使用srvctl命令更新ocr中DBHOME相關資訊：

su  - oracle

srvctl upgrade database -d VPROD -o $NEW_ORACLE_HOME

srvctl upgrade database -d VPROD -o /s01/orabase/product/11.2.0/dbhome_2

[oracle@vrh1 ~]$ srvctl config database -d VPROD

Database unique name: VPROD

Database name: VPROD

Oracle home: /s01/orabase/product/11.2.0/dbhome_2

Oracle user: oracle

Spfile: +SYSTEMDG/VPROD/spfileVPROD.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: VPROD

Database instances: VPROD1,VPROD2

Disk Groups: SYSTEMDG

Mount point paths:

Services:

Type: RAC

Database is administrator managed

[oracle@vrh1 ~]$ srvctl stop database -d VPROD

PRCC-1016 : VPROD was already stopped

[oracle@vrh1 ~]$ srvctl start database -d VPROD 

[oracle@vrh1 ~]$ srvctl status  database -d VPROD

Instance VPROD1 is running on node vrh1

Instance VPROD2 is running on node vrh2

6.修改oracle用戶的profile配置檔指中的變數：

cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

# User specific environment and startup programs

ORACLE_HOME=/s01/orabase/product/11.2.0/dbhome_2

ORACLE_SID=VPROD1

ORACLE_BASE=/s01/orabase

PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:$HOME/bin

export PATH ORACLE_HOME ORACLE_SID ORACLE_BASE

SQL> select * from global_name;

GLOBAL_NAME

--------------------------------------------------------------------------------

www.oracledatabase12g.com

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

7. 資料庫升級完成後進入一個pending area，建議在至少2個禮拜內，不要升級compatible參數和刪除restore point。

在確認沒有回退的必要後，修改compatible參數並刪除restore point:

alter system set compatible='11.2.0.2.0′ scope=spfile;

drop restore point  MACLEAN_ROLLBACK;

srvctl stop database -d VPROD

srvctl start database -d VPROD

以上成功地將11.2.0.1的RAC資料庫升級到了11.2.0.2。

五、回退升級操作(Database Downgrade)

我們可以選擇2種回退辦法：

1. 通過restore point還原到11.2.0.1的資料庫
2. 執行catdwgrd.sql降級資料字典

針對第一種方法：

關閉所有節點實例

srvctl stop database -d VPROD

export ORACLE_HOME=$OLD_ORACLE_HOME

export PATH=$OLD_ORACLE_HOME/bin:$PATH

unset LD_LIBRARY_PATH

sqlplus  / as sysdba

SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE

---------- --------------------- --- ------------

TIME

---------------------------------------------------------------------------

RESTORE_POINT_TIME                                                          PRE

--------------------------------------------------------------------------- ---

NAME

--------------------------------------------------------------------------------

    601958                     1 YES    462307328

07-SEP-11 07.52.59.000000000 PM

                                                                            YES

MACLEAN_ROLLBACK

SQL> flashback database to restore point MACLEAN_ROLLBACK;

Flashback complete.

flashback database的速度 視乎flashback log多少而定，一般是很快的，在1分鐘之內。

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

以上通過restore point的方法是我所推薦的，這種方法簡單、省時省力、高效且問題少少，是一種綠色方案。同時不要忘記使用srvctl upgrade命令還原ocr中的DBHOME資訊，以及還原profile檔。

針對第二種方法：
catdwgrd.sql的運行有諸多限制，其所消耗的時間可能要略長於catupgrd.sql。而且該腳本在運行過程中可能遇到各種錯誤，不推薦使用這種方法。

關於使用catdwgrd.sql腳本降級資料庫11.2.0.2到11.2.0.1，可以參考MOS note <How To Downgrade From Database 11.2 To Previous Release (includes 11.2.0.2-11.2.0.1) [ID 883335.1]>。