Upgrade 11.2.0.1 rac db to 11.2.0.2 in linux
View more documents from Maclean Liu
Upgrade 11.2.0.1 DB/RDBMS to 11.2.0.2 in Linux
2011/09/07 by 4 Comments
在<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.askmac.cn
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种回退办法：
- 通过restore point还原到11.2.0.1的数据库
- 执行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]>。
11.2.0.2补丁集安装体验
2010/09/15 by 2 Comments
使用了Out-of-place Upgrade方式，安装图形界面沿袭了11.2.0.1的风格:
[Read more…]
