升级11.2.0.1 到11.2.0.3 并安装PSU4 补丁

ALLSTARS_ORACLE

升级11.2.0.1 到11.2.0.3  并安装PSU4 补丁



准备阶段
将dbupgdiag.sql拷贝至某一目录下并执行，如tmp目录

SQL> @dbupgdiag.sql

1）查看“List of Invalid Database Objects”部分，如果存在系统失效对象，则运行utlrp.sql进行编译。多次执行直至失效对象的数目稳定为止。若有，则记录应用相关的失效对象，以便于升级后进行对比。

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

set linesize132;
set pagesize300;
col object_nameformat a30;
col ownerformat a15;
selectOWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where status<>'VALID'order by 1,2,3;
记录下来升级前的失效对象(用于升版之后的失效对象数量对比)
2）查看“Duplicate Objects Owned by SYS and SYSTEM”部分，确保SYS和SYSTEM表中不包含重复对象。查询结果中如果有以下重复对象可忽略。

column object_name format a30   select object_name, object_type   from dba_objects   where object_name||object_type in      (select  object_name||object_type         from  dba_objects       where  owner = 'SYS')   and owner = 'SYSTEM'      OBJECT_NAME OBJECT_TYPE   ------------------------------ -------------------   AQ$_SCHEDULES TABLE   AQ$_SCHEDULES_PRIMARY INDEX   DBMS_REPCAT_AUTH PACKAGE   DBMS_REPCAT_AUTH PACKAGE BODY

若除上述重复对象以外仍存在其它重复对象，则需要drop掉system用户的对应对象，需要同应用人员确认后删除。若需要删除的对象数量较多，可以采用以下方法批量删除。

Step  1:   set pause off   set heading off   set pagesize 0   set feedback off   set verify off   spool dropsys.sql   select 'DROP ' || object_type || ' SYSTEM.' ||  object_name || ';'   from dba_objects   where object_name||object_type in      (select  object_name||object_type         from  dba_objects       where  owner = 'SYS')   and owner = 'SYSTEM';   spool off   exit   Step  2：   从dropsys.sql中删除无需drop的对象，如上述的AQ$_SCHEDULES对象。   Step  3：   $ sqlplus system/passwd   SQL> @dropsys   Step  4:   部分对象可能由于存在依赖关系无法删除，则需要手动处理。

After validating theinvalid objects, re-run dbupgdiag.sql in the database once again and make surethat everything is fine.
4.  检查数据库国家字符集查询语句如下：
select value from NLS_DATABASE_PARAMETERSwhere parameter = 'NLS_NCHAR_CHARACTERSET';

如果国家字符集既不是 UTF8 也不是 AL16UTF16 则请按下列官方步骤处理：
Note 276914.1 The National Character Set in Oracle 9iand 10g.

5.  记录数据库dblink信息查询数据库中 dblink的语句如下：
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK'||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'"USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;


6.  查询源版本数据库TIMESTAMP WITH TIMEZONE 设置查询语句如下：
SQL> select TZ_VERSION fromregistry$database;


7.  收集数据字典统计信息在升级之前执行数据字典的统计信息收集，可以减少升级时所需的停机时间。执行check_stale_stats.sql，并根据执行结果提供的SQL对特定Schema执行统计信息收集。

--------执行如下存储过程   EXECUTE dbms_stats.gather_dictionary_stats;

8.  检查database vault组件  如果系统有database vault组件，则需要升级之前先disable掉该组件。查询该组件是否安装的语句
SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';
PARAMETER                     VALUE
----------------------------- -----------------------
Oracle Database Vault         FALSE

value为TRUE则表示已安装，为FALSE则表示没有安装。
如果需要disable掉databasevault. 请参阅 :
Note 453903.1 - Enabling and Disabling Oracle Database Vault in UNIX
9.  备份database control配置数据(如果有)  客户环境未配置OEM database control. 该部分略过。如需操作请参阅：
Note 870877.1 How To Save Oracle Enterprise Manager Database Control Data BeforeUpgrading The Single Instance Database To Other Release ?

10.检查数据库Network ACL的使用  检查客户数据库是否使用ACL:
SQL> SELECT acl FROM dba_network_acls
  2 WHERE host = 'host_name' AND lower_port IS NULL AND upper_port IS NULL;
未选定行

  经检查未使用Network ACL.

11.检查数据库没有逻辑坏块的存在创建脚本如下：
Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql

SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';

spool off

创建完成之后将运行检测脚本:

This creates a script called analyze.sql.
Now execute the following steps:
$ sqlplus "/ as sysdba"
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql

确认运行过程中未出现运行错误，则没有逻辑坏块的存在。

12.确认所有物化视图更新已成功完成，复制操作已停止SQL> SELECT DISTINCT(TRUNC(last_refresh))FROM dba_snapshot_refresh_times;
SQL> selects.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#,bitand(s.mflags, 8) from obj$ o, sum$ s
where o.obj# = s.obj# and o.type# = 42 ANDbitand(s.mflags, 8) = 8;


13.确认没有需要恢复的数据文件

$sqlplus  / as sysdba   SQL> SELECT * FROM v$recover_file;

14.确认没有处于备份模式的文件

$sqlplus  / as sysdba   SQL> SELECT * FROM v$backup WHERE status != 'NOT  ACTIVE';

15.确认没有未完成的分布式事务

SQL> select *  from dba_2pc_pending;       如果有则执行如下操作：   SQL> SELECT  local_tran_id  FROM dba_2pc_pending;   SQL> EXECUTE  dbms_transaction.purge_lost_db_entry('');   SQL> COMMIT;

16.确认data guard是否存在检查语句：
SELECTSUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM v$parameter
WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';

如果有data guard配置，则要确保所有主库日志均传递到了备库端。备库启动recover模式应用归档且nodelay方式。
如果有data guard配置，升级data guard过程请查阅Oracle Online Document upgrade部分。
http://docs.oracle.com/cd/E11882_01/server.112/e25608/upgrades.htm
17.停止批量作业和cron中的job

crontab -l

同时确保数据库在升级过程中不能自动执行job任务：
停止所有的批处理作业、定时作业等，参考MOS文档404238.1/1335741.1/ 67695.1。
检查crontab中的定时任务已经注释。
检查JOB_QUEUE_PROCESSES、AQ_TM_PROCESSES参数，设置为0。
检查是否有正在运行的JOB：
show parameter job_queue_processes
show parameter aq_tm_processes

select * from v$lock where type='JQ';
select /*+ rule */ * from dba_jobs_running;


Disable属于dbms_scheduler的作业：
Set lin 160 pages 1000
col JOB_NAME format a20
col PROGRAM_NAME format a20
col JOB_CREATOR format a8
SELECT JOB_NAME, JOB_CREATOR,PROGRAM_NAME, ENABLED,RESTARTABLE,STATE, RUN_COUNT,MAX_RUNS,FAILURE_COUNT,STOP_ON_WINDOW_CLOSE
FROM DBA_SCHEDULER_JOBS;

select'EXEC DBMS_SCHEDULER.DISABLE( '||''''||JOB_NAME||''''||');'
FROM DBA_SCHEDULER_JOBS
where enabled = 'TRUE';

====================SYS==========================================
EXEC DBMS_SCHEDULER.DISABLE( 'PURGE_LOG');
EXEC DBMS_SCHEDULER.DISABLE( 'ORA$AUTOTASK_CLEAN');
EXEC DBMS_SCHEDULER.DISABLE( 'DRA_REEVALUATE_OPEN_FAILURES');
EXEC DBMS_SCHEDULER.DISABLE( 'MGMT_CONFIG_JOB');
EXEC DBMS_SCHEDULER.DISABLE( 'MGMT_STATS_CONFIG_JOB');
EXEC DBMS_SCHEDULER.DISABLE( 'BSLN_MAINTAIN_STATS_JOB');
EXEC DBMS_SCHEDULER.DISABLE( 'RSE$CLEAN_RECOVERABLE_SCRIPT');
EXEC DBMS_SCHEDULER.DISABLE( 'SM$CLEAN_AUTO_SPLIT_MERGE');
EXEC DBMS_SCHEDULER.DISABLE( 'RLM$EVTCLEANUP');
EXEC DBMS_SCHEDULER.DISABLE( 'RLM$SCHDNEGACTION');


18.确认SYS和 SYSTEM用户的默认表空间为system

SQL> SELECT username, default_tablespace        FROM  dba_users        WHERE  username in ('SYS','SYSTEM');

19.确认aud$表位于sys schema和system表空间中

SQL> SELECT owner,tablespace_name        FROM  dba_tables        WHERE  table_name='AUD$';

20.确认部分init参数满足升级需求

Adjust the values of the  initialization parameters to at least the minimum values indicated by the  Pre-Upgrade Information Tool.  For customers with JVM installed, java_pool_size and shared_pool_size must be set to at least  250MB prior to the upgrade otherwise JVM upgrade may fail with the  following error:   ORA-07445: exception encountered: core dump [qmkmgetConfig()+52]  [SIGSEGV] [ADDR:0x18] [PC:0x103FFEC34] [Address not mapped to object] []

show parameterpool;
show parameter sga；
show parameter target；
21.记录数据文件、控制文件、日志文件等文件路径 （可略）

SELECT name FROM v$controlfile;   SELECT file_name FROM dba_data_files;   SELECT group#, member FROM v$logfile;

22.清空回收站为了避免升级过程中的ORA-00600错误以及减少升级时间，建议清空回收站。

SQL> purge DBA_RECYCLEBIN;   SQL> SELECT * FROM DBA_RECYCLEBIN;

23.将应用表空间置为只读模式

$sqlplus  / as sysdba   SQL> alter tablespace users read only;

24.停应用、监听和数据库

$lsnrctl stop      shutdown immediate;

25.备份system/sysaux/undo/redo/controlfile

对于system/sysaux/undo/redo/controlfile等文件进行冷备份      可以使用dd或其他备份软件备份上述raw device文件

26.调整环境变量

1） 确认ORACLE_BASE, ORACLE_HOME, PATH, NLS_10 and LIBRARY_PATH等环境变量已经指向11.2.0.3的相应目录。   ORACLE_BASE= /Oracle/app   ORACLE_HOME= /Oracle/app/product/11.2.0.3/dbhome   PATH= /Oracle/app/product/11.2.0.3/dbhome/bin.   SHLIB_PATH=/Oracle/app/product/11.2.0.3/dbhome/lib32:   /Oracle/app/product/11.2.0.3/dbhome/rdbms/lib32   LD_LIBRRARY_PATH=/Oracle/app/product/11.2.0.3/dbhome/lib:/usr/lib:/lib   CLASSPATH=/Oracle/app/product/11.2.0.3/dbhome/jlib:   /Oracle/app/product/11.2.0.3/dbhome/rdbms/jlib:   /Oracle/app/product/11.2.0.3/dbhome/JRE:   /Oracle/app/product/11.2.0.3/dbhome/network/jlib      2） /etc/oratab文件已经指向11.2.0.3软件的路径   退出oracle环境，再次进入使环境变量生效

27.拷贝原init/spfile 口令文件到11.2.0.3的$ORCLE_HOME/dbs

Copy init.ora/spfile and password file  (orapw<sid>.ora) from 11.2.0.1 $ORACLE_HOME/dbs to 11.2.0.3  $ORACLE_HOME/dbs

28.拷贝网络配置文件到11.2.0.3的$ORACLE_HOME/network/admin

Copy network configuration files (listener.ora,  sqlnet.ora, tnsnames.ora, etc) from 11.2.0.1 $ORACLE_HOME/network/admin ( or  $TNS_ADMIN) location to 11.2.0.3 $ORACLE_HOME/network/admin ( or $TNS_ADMIN)  location   注意需要修改listener.ora 和tnsnames.ora中的$ORACLE_HOME路径

29.如果配置了DB console/DB control建议做如下调整 如果未配置db console和dbcontrol可以忽略此步骤。

If DB Console / DB Control  is configured and used then also copy the  following two directories and their contents from 11.2.0.1 to 11.2.0.3. (if  DB Console / DB Control is not configured these directories MAY not exist).               ORACLE_HOME/<hostname_dbname>               ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_<hostname_dbname>   Specify the actual name for  <hostname_dbname>

30.确保设置了正确的COMPATIBLE参数

Make sure the COMPATIBLE initialization parameter is  properly set for Oracle Database 11g Release 2 (11.2). The Pre-Upgrade  Information Tool displays a warning in the Database section if COMPATIBLE is  not properly set.   例如：   compatible =11.2.0.0.0

Show parametercompatible
Alter system set compatible =11.2.0.0.0
31.执行升级脚本catupgrd.sql   50分钟

cd /Oracle/app/product/11.2.0.3/dbhome/rdbms/admin   sqlplus / as sysdba   SQL>STARTUP UPGRADE   SQL> set echo on   SQL> SPOOL upgrade.log   SQL> @?/rdbms/admin/catupgrd.sql   SQL>spool off   SQL> exit

32.重新启动数据库到normal模式

SQL>shutdown immediate      SQL>startup

33.运行utlu112s.sql检验升级结果

sqlplus / as sysdba   SQL>   SQL> select * from v$version;   SQL>spool checkupgrade.log   SQL> @?/rdbms/admin/utlu112s.sql   SQL>spool off

查看upgrade.log和checkupgrade.log，检查是否有错误出现。如果出现状态为INVALID等错误，则在改正错误后，重新运行catupgrd.sql
34.运行catuppst.sql脚本运行catuppst.sql脚本执行一些非upgrade模式下的升级操作，如升级baseline数据等。

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

35.运行utlrp.sql脚本编译失效对象   15分钟运行utlrp.sql脚本编译失效对象，确认升级后没有新增的失效对象

@?/rdbms/admin/utlrp.sql      set linesize 132;   set pagesize 300;   col object_name format a30;   col owner format a15;   select OWNER,OBJECT_NAME,OBJECT_TYPE from  dba_objects where status<>'VALID' order by 1,2,3;

36.启动数据库监听

$lsnrctl start

37.检查升级后，验证各组件状态

SQL> SELECT COMP_NAME, VERSION, STATUS FROM  SYS.DBA_REGISTRY;   SQL> exit

38.启动Database Vault(如果配置)

如果未使用Database Vault，可忽略该步骤，具体方法请参考：   Note 453903.1 - Enabling and Disabling Oracle  Database Vault in UNIX

39.更新RMAN catalog库（如果有）

$ rman catalog username/password@alias   RMAN> UPGRADE CATALOG;   $ rman catalog  username/password@alias   RMAN> UPGRADE CATALOG;

40.至应用表空间为读写状态

$sqlplus  / as sysdba   SQL> alter tablespace users  read write;

41.查看升级之后的数据库autoTask查询语句如下：
Set linesize 160 pagesize1000
col CLIENT_NAME format a40
col CONSUMER_GROUP formata30
col WINDOW_GROUP formata30

select CLIENT_NAME,STATUS,CONSUMER_GROUP,WINDOW_GROUP
from dba_autotask_client;


42.建议禁用掉数据库DBRM根据其他客户处升级经验，如果不是一台服务器多个数据库实例，配置每个实例的资源使用范围，则建议禁掉DBRM(resource manager)配置：
show parameter resource

set linesize 160 pagesize1000
col WINDOW_NAME format a30
col SCHEDULE_OWNER formata20
col SCHEDULE_NAME formata20
col RESOURCE_PLAN formata35

selectWINDOW_NAME,RESOURCE_PLAN,SCHEDULE_OWNER,SCHEDULE_NAME,SCHEDULE_TYPE
fromdba_scheduler_windows;

alter system setresource_manager_plan='';

executedbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');


43.建议升级数据库TimeZone版本到14升级到11.2.0.2之后，TimeZone版本默认变为14. 才可以执行的步骤。
参考文档：
n  Note 1201253.1 Actions For DSTUpdates When Upgrading To Or Applying The 11.2.0.2 Patchset
n  (recommended) update to DSTv14(standard DST version of 11.2.0.2) by following Note 977512.1 Updating theRDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST from step 3a)onwards, when going to DSTv14 there is no need to apply any DST patch to the11.2.0.2 home

NOTE : If youhave run DBMS_DST package to upgrade the timezone version to a newer one, e.g., version 11 during post-upgrade of OracleDatabase Release 11.2 , before you downgrade to your previous Oracle databaserelease, you must install version 11 timezone files into the directory
of  $ORACLE_HOME/ORACORE/ZONEINFO on your old Oracle database release.


For patches of new timezone version files onOracle Database Release 11.1 or older, please refer to metalink Note412160.1
Updated DST transitions and new Time Zones inOracle Time Zone File patches.

3a) check current RDBMS DST version and"DST UPGRADE STATUS".
SELECTPROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROMDATABASE_PROPERTIES
WHEREPROPERTY_NAME LIKE 'DST_%'
ORDERBY PROPERTY_NAME;

PROPERTY_NAME                  VALUE
------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION         4
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE


SELECTversion FROM v$timezone_file;

   VERSION
----------
         4


3b) Check UPFRONT using DBMS_DST if there isaffected data that cannot be resolved automatically.
-- these steps will NOT update any data yet.
-- this alter session might speed up DBMS_DST on some db's
-- see Bug 10209691


purge dba_recyclebin;
alter session set "_simple_view_merging"=TRUE;
alter session set "_with_subquery" = materialize;
exec DBMS_DST.BEGIN_PREPARE( 14 )


如果以上出现了错误，则参考文档977512.1进行处理。

-- check for prepare status
col property_name format a30
col value format a20

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

-- output should be
-- PROPERTY_NAME VALUE
-- ------------------------------ ------------------------------
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION <the new DST version number>
-- DST_UPGRADE_STATE PREPARE

PROPERTY_NAME                 VALUE
------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION         4
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE             PREPARE


-- truncate logging tables if they exist.
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;


-- log affected data
set serveroutput on

BEGIN
DBMS_DST.FIND_AFFECTED_TABLES(
  affected_tables =>'sys.dst$affected_tables',
  log_errors => TRUE,
  log_errors_table =>'sys.dst$error_table' );
END;
/


-- check what tables have affected data thatcannot be resolved automatically.
-- if this gives no rows then there is noproblem at all
SELECT * FROM sys.dst$affected_tables;


-- IF previous select gives rows then you cansee  what kind of problem there are inthose rows
SELECT * FROM sys.dst$error_table;


说明：
-- error_on_overlap_time is error numberORA-1883
-- error_on_nonexisting_time is error numberORA-1878

-- for an explanation of the reported dataplease see
-- "Error Handling when Upgrading TimeZone File and Timestamp with Time Zone Data"
-- For the "error_on_overlap_time"and "error_on_nonexisting_time" you do not HAVE to
-- take action on this data to upgrade theDST version, but it is advised
-- to at least to check the results AFTER theupdate.

-- end prepare window, the rows above willstay in those tables.
EXEC DBMS_DST.END_PREPARE;


-- check if this is ended
col property_name format a30
col value format a20

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         4
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE             NONE


4) Do the actual RDBMS DST version update ofthe database using DBMS_DST:
Assuming all non-existing time and overlaptimes in previous step are solved or logged, so using for
DBMS_DST.UPGRADE_DATABASEerror_on_overlap_time => FALSE and error_on_nonexisting_time => FALSE);
For RAC the database should be in singleinstance mode , as required by the "startup UPGRADE".

conn / as sysdba
shutdown immediate;
startup upgrade;

set serveroutput on

-- checkif previous prepare window is ended
col property_name format a30
col value format a20

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

-- output should be
-- PROPERTY_NAME VALUE
-- ---------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE


PROPERTY_NAME                 VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         4
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE             NONE


purge dba_recyclebin;


-- clean used tables
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;


-- this alter session mightspeed up DBMS_DST on some db's
-- seeBug 10209691
altersession set "_with_subquery"=materialize;
alter session set"_simple_view_merging"=TRUE;
-- start upgrade window
EXEC DBMS_DST.BEGIN_UPGRADE( 14 );


-- the message -- "An upgrade window hasbeen successfully started." will be seen

-- check if this select
col property_name format a30
col value format a20

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

-- gives this output:
-- PROPERTY_NAME VALUE
-- --------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the new DST version number>
-- DST_SECONDARY_TT_VERSION <the old DST version number>
-- DST_UPGRADE_STATE UPGRADE

PROPERTY_NAME                 VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       4
DST_UPGRADE_STATE             UPGRADE


-- restart the database
shutdown immediate
startup


- this alter session might speed up DBMS_DSTon some db's
-- see Bug 10209691
alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;


-- now upgrade the tables who need action
set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table =>'SYS.DST$ERROR_TABLE',
log_triggers_table =>'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time=> FALSE,
error_on_nonexisting_time=> FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'||:numfail);
END;
/

-- ouput of this will be alist of tables like:
-- Table list:SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
-- Number of failures: 0
-- ....
-- Table list:SYSMAN.MGMT_PROV_ASSIGNMENT
-- Number of failures: 0
-- Table list:SYSMAN.MGMT_CONFIG_ACTIVITIES
-- Number of failures: 0
-- Failures:0

Table list:"SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S"
Number of failures: 0
Table list:"SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_L"
Number of failures: 0
Table list:"SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S"
Number of failures: 0
Table list:"SYSMAN"."AQ$_MGMT_LOADER_QTABLE_L"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_SUITE_INST_MEMBERS"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_STAGING_DIRS"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_RPM_REP"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_OPERATION"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_NET_CONFIG"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_IP_RANGE"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_DEFAULT_IMAGE"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_CLUSTER_NODES"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_BOOTSERVER"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_ASSIGNMENT"
Number of failures: 0
Table list:"SYSMAN"."MGMT_CONFIG_ACTIVITIES"
Number of failures: 0
Failures:0

PL/SQL proceduresuccessfully complet


-- if there where nofailures then end the upgrade.

VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'||:fail);
END;
/

-- output that will beseen:
-- An upgrade window hasbeen successfully ended.
-- Failures:0

An upgrade window has beensuccessfully ended.
Failures:0

PL/SQL proceduresuccessfully completed.


-- last checks
col property_name format a30
col value format a20

SELECT PROPERTY_NAME,SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE'DST_%'
ORDER BY PROPERTY_NAME;

-- needed output:
-- PROPERTY_NAME VALUE
------------------------------ ------------------------------
-- DST_PRIMARY_TT_VERSION<the new DST version number>
-- DST_SECONDARY_TT_VERSION0
-- DST_UPGRADE_STATE NONE

PROPERTY_NAME                  VALUE
------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE


SELECT * FROMv$timezone_file;

-- needed output:
-- FILENAME VERSION
-- ----------------------------
-- timezlrg_<newversion>.dat <new version>

FILENAME                                                       VERSION
----------------------------------------------------------------------
timezlrg_14.dat                                                      14


44.需要留意的11.2.0.3数据库参数设置对于11.2.0.2之后的数据库新特性，以下参数需要留意：

参数名称及其设置值	说明	目的
deferred_segment_creation = false		禁用Deferred Segment Creation特性
parallel_execution_message_size = 32768		禁用跨实例的并行服务器   增大并行服务器所使用的消息缓存
_undo_autotune = false   undo_retention = 3600		禁用Auto Undo
_optimizer_adaptive_cursor_sharing = false   _optimizer_extended_cursor_sharing = none   _optimizer_extended_cursor_sharing_rel =  none		禁用Adaptive Cursor  Sharing特性
_cleanup_rollback_entries = 400		加快事务的回滚/恢复

  注：对上述参数的修改，请务必针对本数据库业务做好测试之后再执行！  

11.2.0.3 回退方案
1.  首选回退方案

--将所有应用数据表空间置为read-only模式   --shutdown immediate 数据库   --拷贝system/undo/sysaux/xdb/drsys/odm/tools表空间的数据文件和controlfiles/redo文件     到备份文件系统   --启动数据库开始upgrade      如果在升级过程中出现问题需要回退，则只需将上述冷备份的数据文件恢复到原来位置即可。   然后重新创建TEMP表空间。      该方案的特点是针对软件升级失败后回退速度快、简单。      注：上述备份方案只针对此次数据库升级过程中出现问题后需要回退使用，不能保证数据库升级期间出现存储故障等原因造成的数据文件损坏，数据丢失的情况。因此建议在条件允许的情况下使用RMAN或专业备份软件在数据库升级前执行一次完整备份。

2.  其它回退方案

-- Online Backup  方式（RMAN 备份/恢复）       --  FLASHBACK  方式   --该方式不允许修改COMPATIBLE参数，且只支持10.2 or later   --打开数据库的flashback功能    --创建GUARANTEED  RESTORE POINT还原点   --升级数据库   --如果遇到问题需要回退，则可以使用flashback闪回到之前创建的RESTORE POINT。   --回退成功后删除还原点。   --命令参考如下    SQL>  CREATE RESTORE POINT upgrd               GUARANTEE FLASHBACK DATABASE;    SQL>  FLASHBACK DATABASE TO RESTORE POINT upgrd;    SQL>  SELECT * FROM V$RESTORE_POINT;    SQL>  DROP RESTORE POINT upgrd;

安装PSU补丁（Patch 14275605 - 11.2.0.3.4 PSU）
1.  备份ORACLE_HOME目录

$tar  cvf /backup/11.2.0.3.tar /Oracle/app/*

2.  下载并安装最新版本的OPatch工具

You  must use the OPatch utility version 11.2.0.3.0 or  later to apply this patch    ./opatch  -versione

3.  升级前环境检查

确认$PATH 能够包含make, ar, ld, and nm等编译命令   which make      例如：   export PATH=$PATH:/usr/ccs/bin

4.  升级前补丁冲突检查

$unzip  p14275605_11203_<platform>.zip    $cd  14275605    $opatch  prereq CheckConflictAgainstOHWithDetail -ph ./

5.  停应用，监听和数据库
6.  开始补丁安装  20分钟

$unzip  p14275605_11203_<platform>.zip   $cd 14275605   $ /Oracle/product/11.2.0.3/dbhome_1/OPatch/opatch  apply

全选y
7.  Patch Post-Installation

cd $ORACLE_HOME/rdbms/admin   sqlplus /nolog   SQL> CONNECT / AS SYSDBA   SQL> STARTUP   SQL> @catbundle.sql psu apply   SQL> QUIT

8.  检查Patch安装日志有无报错

Check the following log files in  $ORACLE_HOME/cfgtoollogs/catbundle or $ORACLE_BASE/cfgtoollogs/catbundle for  any errors:      catbundle_PSU_<database  SID>_APPLY_<TIMESTAMP>.log   catbundle_PSU_<database  SID>_GENERATE_<TIMESTAMP>.log

9.  更新RMAN catalog库（如果有）如果当前数据库未配置catalog库，可以跳过此步骤

$ rman catalog username/password@alias   RMAN> UPGRADE CATALOG;   $ rman catalog  username/password@alias   RMAN> UPGRADE CATALOG;

10.开启监听

$ lsnrctl start

PSU补丁回退方案
1.  确认catbundle_PSU_<databaseSID>_ROLLBACK.sql已存在

1.  Verify that an $ORACLE_HOME/rdbms/admin/catbundle_PSU_<database SID>_ROLLBACK.sql file exists for each database associated with this ORACLE_HOME.  If this is not the case, you must execute the steps in Section  3.3.2, "Loading Modified SQL Files into the Database" against  the database before deinstalling the PSU.

2.  停应用，监听和数据库

lsnrctl stop   shutdown immediate;

3.  使用opatch回退PSU

opatch rollback -id 14275605

4.  Patch Post-Deinstallation

cd $ORACLE_HOME/rdbms/admin   sqlplus /nolog   SQL> CONNECT / AS SYSDBA   SQL> STARTUP   SQL> @catbundle_PSU_<database  SID>_ROLLBACK.sql   SQL> QUIT

5.  检查Patch卸载日志

The log file is found in $ORACLE_BASE/cfgtoollogs/catbundle  and is named catbundle_PSU_<database  SID>_ROLLBACK_<TIMESTAMP>.log

dba_sky

很不错，谢谢了。

djmilo

太全面了！牛逼