Oracle database 补丁知识介绍
oracle path和大家传统理解的补丁的含义都是差不多的，可以快速解决oracle 本身的bug，我们都具备这个宏观概念，但是Oracle DBA要清楚 oracle 是如何来细分补丁的类型和定义我们还是查看oracle 官方文档这个不会有错误，提醒大家百度出来的是非官方说法，很容易误导大众，建议大家多看官方文档，ok，废话不多说我们开始定位http://docs.oracle.com/cd/E11882_01/server.112/e25494/dba.htm#ADMIN11032
看到官方文档提示我们确认oracle 数据库的版本，我们从oracle官网下载的版本是属于一个基础版本，没有帮你直接升级到最新版，所以我们要升级到最新版本。我们先来介绍
软件版本的命名，还是看文档喽，版本命名和书写方式是“11.2.0.1.0”我们来看这几个数字都代表什么意思！！根据示例图
看到官方文档提示我们确认oracle 数据库的版本，我们从oracle官网下载的版本是属于一个基础版本，没有帮你直接升级到最新版，所以我们要升级到最新版本。我们先来介绍
软件版本的命名，还是看文档喽，版本命名和书写方式是“11.2.0.1.0”我们来看这几个数字都代表什么意思！！根据示例图
- 主数据库发布号 – Major Database Release Number
- 数据库维护发布号 – Database Maintanence Release Number
- 应用服务器发布号 – Application Server Release Number
- 组建相关发布号 – Component Specific Release Number
- 平台相关发布号 – Platform Specific Release Number
我们用oracle补丁包升级后，第4位和第5位数字会有所改变，其中第4位是补丁集（path set）号，第5位是补丁集更新（path set update，PSU）。我可以通过查看oracle的数据字典
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;
PRODUCT VERSION STATUS
---------------------------------------- --------------- ---------------
NLSRTL 11.2.0.1.0 Production
Oracle Database 11g Enterprise Edition 11.2.0.1.0 64bit Productio
n
PL/SQL 11.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: 11.2.0.1.0 Production
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;
PRODUCT VERSION STATUS
---------------------------------------- --------------- ---------------
NLSRTL 11.2.0.1.0 Production
Oracle Database 11g Enterprise Edition 11.2.0.1.0 64bit Productio
n
PL/SQL 11.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: 11.2.0.1.0 Production
我们升级后，第4为才会发生变化，目前我们还是基础版本，也是bug最多的版本，升级补丁需要从mos上去下载，也已经有网友把补丁集
放在了自己空间上，大家可以自己想办法下载了！最新的补丁集是11.2.0.4，在mos上可以查到相关的文档,包括安装方法等。
在打补丁之前我们先来看看oracle补丁的相关术语的介绍，我们才知道oracle 的补丁的作用和补丁的类别是如何区分的??
PSU 就是一种Composite Patch，以PSU为例来说明。
第一次安装的composite patch为PSU 11.2.0.3.2：
Interim patches (1) :
Patch 13545268: applied on Fri Dec 09 10:59:28 EST 2011
Patch Description: "Database Patch Set Update : 11.2.0.3.2 (13545268)"
Created on 2 July 2012, 07:52:00 hrs PST8PDT
Sub-patch 13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"<==Sub-patch
Bugs fixed:
9275072, 9352237 ...
安装下一个composite patch PSU 11.2.0.3.3时，只需要安装有变化的部分和新增加的patches，不需要再安装之前已经安装的11.2.0.3.1 和11.2.0.3.2，之前已经安装的这部分就称为sub-patch：
Patch 13795248 : applied on Fri Dec 10 10:00:28 EST 2011
Patch Description: "Database Patch Set Update : 11.2.0.3.3 (13795248)" <==变化和新增的部分
Created on 2 July 2012, 07:52:00 hrs PST8PDT
Sub-patch 13545268; "Database Patch Set Update : 11.2.0.3.2 (13545268)" <==Sub-patch
Sub-patch 13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)" <==Sub-patch
Bugs fixed:
9275072, 9352237 ...
Composite Patches 的好处是减少补丁的安装量，减少与已经安装上补丁的冲突。
在MOS文档中有针对Composite Patches详细的说明：
Note 1376691.1 Composite Patches for Oracle Products [Video]
上面提到的所有补丁都会有一个补丁号，如果已经知道了补丁号，就可以直接使用下面的链接来下载这个补丁：
https://updates.oracle.com/download/补丁号.html
输入MOS的账号、密码就可以。
比如：11.2.0.3.0 PATCH SET 下载链接：
https://updates.oracle.com/download/10404530.html
操作系统和database 11.2.0.1.0相信大家都很好弄到手，我们就不说了，果断从mos上下载path set 和OPatch，psu我们也下载了。
p6880880这个包是OPatch的最新版本，其余的7个是我们11.2.3.0补丁集，总大小7.1个G，分别的作用如下图所示
我们这次是单实例打patch set 补丁我们就只用的上前两个压缩包喽！！打开压缩包里面有pathset的文档截图如下
我看到文档给出了两种升级方式，第一种In-Place Upgrade 意思是说将升级文件安装到原来的oracle home上，这样能够节省空间，但需要的时间很久，不推荐啊
第二中Out-Place Upgrade 将oracle database 安装到新的 oracle home目录，安装完成后，可以把原来的database目录迁移到新的oracle home 目录中即可。
这个是oracle 推荐的方法，它可以降低停机时间，也相对安全，只是这种方法需要2倍的硬盘空间。这里做过升级的同学应该清楚11g和10g的升级方式还是变化很大的，10g现在是extended support阶段，oracle公司马上就要让它退役了
我们以后要以11g为准了！！！
我们现在开始正式的数据库升级操作，我这里演示的是测试环境，请大家不要在生产系统上面去搞啊，这里只是教方法，具体的升级要有一套完整的升级方案，并反复测试才能正式启用。
测试环境我就不备份了，我们首先把数据库shutdown掉，再次关闭监听器，再确认以下目录是否存在
来到此界面我选择最后一项Skip software updates 继续下一步
这里选第三项，到这个界面我们应该清楚为什么11g和10g升级不一样了！其实11g的patch set包就是个oracle soft。接续下一步
这个没什么可说的大家都懂得，保持和原来的数据库语言环境一致，我们继续
选择企业版 继续下一步
这里需要注意了默认的路径可不是11.2.0.3，我们需要手工改一下即可，这样就符合我们的安装升级要求了，oracle推荐的Out-Place Upgrade升级方法
继续下一步，我们到如下界面
点击“install”我们开是安装新版本的oracle，哈哈！！！
安装完成后会提示你root用户下运行一个root.sh的脚本，运行完后，自动弹出窗口让你配置listener
继续就行了
还是继续啊！！！
继续中。。。。。
点击下一步，我们配置完毕
配置完毕后，还会提示你升级oracle数据库，默认启动DBUA程序，这个是我们不启动DBUA，我们关掉窗口，如下图所示
ok后，我们继续下一步，安装完成退出窗口
我们在配置完监听后，没有启动DBUA程序，我们这里手工升级数据库吧！！！DBUA大家也可以练习我这里就不用了。
其实我们这里已经升级完oracle软件了，去更新一下oracle用户的.profile环境变量，这样可以把旧的oracle_home路径改成新的让用户识别到。
$ vi .profile
".profile" 16 lines, 434 characters
PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:.
export PATH
if [ -s "$MAIL" ] # This is at Shell startup. In normal
then echo "$MAILMSG" # operation, the Shell checks
fi # periodically.
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/db_1 改这里
export ORACLE_SID=xupeng11g
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
".profile" 16 lines, 436 characters
$ . ./.profile 执行生效
修改oracle 随机自启动的开关，如果你用的上的话
$ vi /etc/oratab
"/etc/oratab" 23 lines, 726 characters
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed
xupeng11g:/u01/app/oracle/product/11.2.0.3/db_1:N 修改这里的版本号，改成新的
我们还要把spfile或者pfile拷贝到新的oracle_home 目录下即可,不要启动listener和db
开始升级实例，用SYSDBA用户登录 SQL*PLUS
SQL> startup upgrade 我去看一下alert日志就行，看有没有报错，如果安装都问题这不肯定也没有问题
SQL> SPOOL upgrade.log 输出一个当前日志
SQL> @?/rdbms/admin/utlu112i.sql 运行升级工具
SQL> @?/rdbms/admin/catupgrd.sql 运行升级脚本，这个时间比较久大概30-40分钟吧！！！
运行完成后catupgrd.sql里执行了shutdown 命令了，我们把数据库再次启动
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql 生成数据字典和pl/sql等
最后我们需要查看dba_invalid_objects 有没有失效对象，再查看一下各个组件的版本
SQL> SELECT COMP_NAME, VERSION, STATUSFROM SYS.DBA_REGISTRY;
COMP_NAME VERSION STATUS
———————————————————————- ———————-
到这里我们的升级path set 就完成了，我们下一步来看PSU的安装，其实PSU的安装很简单，先升级你的OPatch Version的版本
先查看一下我的OPatch的版本是多少，默认的基础版本是11.2.0.1的。解压p6880880这个包，把内容释放到ORACLE_HOME/OPatch，
之前的OPatch可以先备份一份，以防出错，然后在查询OPatch version 如下命令
# opatch version
OPatch Version: 11.2.0.3.5
OPatch succeeded.
我们在解压缩你的PSU补丁包，我们拿到11.2.0.3.9的包，首先读一下包里的readme文档，这个很关键啊，基本上是step by step的操作
$ unzip p17540582_112030_LINUX.zip -d patch 解压缩到patch目录下
我们手动打patch
$ opatch napply -oh $ORACLE_HOME -local /u01/app/oracle/patch/17540582
Oracle 中间补丁程序安装程序版本 11.2.0.3.5
版权所有 (c) 2013, Oracle Corporation。保留所有权利。
Oracle Home : /u01/app/oracle/product/11.2.0.3/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.3/db_1/oraInst.loc
OPatch version : 11.2.0.3.5
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2014-02-20_11-32-35上午_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 17540582
是否继续? [y|n]
y
User Responded with: Y
All checks passed.
请关闭本地系统上在此 ORACLE_HOME 之外运行的 Oracle 实例。
(Oracle 主目录 = '/u01/app/oracle/product/11.2.0.3/db_1')
本地系统是否已准备打补丁? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '17540582' to OH '/u01/app/oracle/product/11.2.0.3/db_1'
ApplySession: Oracle 主目录中不存在可选组件 [ oracle.precomp.lang, 11.2.0.3.0 ] , 或找到更高版本。
正在为组件 oracle.rdbms, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.rsf, 11.2.0.3.0 打补丁...
正在为组件 oracle.sdo, 11.2.0.3.0 打补丁...
正在为组件 oracle.ldap.rsf, 11.2.0.3.0 打补丁...
正在为组件 oracle.precomp.common, 11.2.0.3.0 打补丁...
正在为组件 oracle.ordim.client, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.util, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.dbscripts, 11.2.0.3.0 打补丁...
正在为组件 oracle.sdo.locator, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.rman, 11.2.0.3.0 打补丁...
正在为组件 oracle.ordim.jai, 11.2.0.3.0 打补丁...
Verifying the update...
Composite patch 17540582 successfully applie
查看更新的结果
$ opatch lspatches
17540582;Database Patch Set Update : 11.2.0.3.9 (17540582)
查看更新补丁的内容
$ opatch lsinventory
Oracle 中间补丁程序安装程序版本 11.2.0.3.5
版权所有 (c) 2013, Oracle Corporation。保留所有权利。
Oracle Home : /u01/app/oracle/product/11.2.0.3/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.3/db_1/oraInst.loc
OPatch version : 11.2.0.3.5
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2014-02-20_11-36-48上午_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-02-20_11-36-48上午.txt
--------------------------------------------------------------------------------
已安装的顶级产品 (1):
Oracle Database 11g 11.2.0.3.0
此 Oracle 主目录中已安装 1 个产品。
中间补丁程序 (1) :
Patch 17540582 : applied on Thu Feb 20 11:33:06 CST 2014
Unique Patch ID: 16985511
Patch description: "Database Patch Set Update : 11.2.0.3.9 (17540582)"
Created on 7 Jan 2014, 03:01:22 hrs PST8PDT
Sub-patch 16902043; "Database Patch Set Update : 11.2.0.3.8 (16902043)"
Sub-patch 16619892; "Database Patch Set Update : 11.2.0.3.7 (16619892)"
Sub-patch 16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Sub-patch 14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch 14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch 13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch 13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch 13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
Bugs fixed:
13593999, 10350832, 14138130, 12919564, 13561951, 14198511, 13588248
13080778, 13804294, 16710324, 12873183, 14472647, 12880299, 13369579
14409183, 13492735, 12857027, 13496884, 14263036, 14263073, 13015379
16038929, 17748833, 16563678, 13732226, 13866822, 13742434, 13944971
12950644, 17748831, 12899768, 13063120, 13958038, 14613900, 13972394
11877623, 17088068, 13072654, 12395918, 13814739, 17343514, 13649031
13981051, 12797765, 17333200, 12923168, 16761566, 16279401, 13384182
13466801, 15996344, 14207163, 13724193, 13642044, 11063191, 13945708
12797420, 12865902, 15869211, 13041324, 14003090, 16314468, 16019955
11708510, 14637368, 13026410, 13737746, 13742438, 15841373, 16347904
15910002, 16362358, 14398795, 13579992, 16344871, 10400244, 14275605
13742436, 9858539, 14841812, 16338983, 9703627, 13483354, 14207317
14393728, 12764337, 16902043, 14459552, 14191508, 12964067, 12780983
12583611, 14383007, 14546575, 15862016, 13476583, 13489024, 17748830
14088346, 13448206, 16314466, 13419660, 14110275, 13430938, 13467683
14548763, 12834027, 13632809, 13377816, 13036331, 14727310, 16175381
13584130, 12829021, 15862019, 12794305, 14546673, 12791981, 13787482
13503598, 10133521, 12744759, 13399435, 13553883, 14023636, 14762511
9095696, 14343501, 13860201, 13257247, 14176879, 16014985, 12312133
14480675, 16306019, 13559697, 9706792, 12974860, 12940620, 13098318
13773133, 15883525, 16794244, 13340388, 13366202, 13528551, 12894807
12747437, 13454210, 12748240, 13385346, 15987992, 13923995, 13582702
14571027, 12784406, 13907462, 13493847, 13857111, 13035804, 16710363
13544396, 14128555, 8547978, 14226599, 17478415, 17333197, 9397635
14007968, 12925089, 12693626, 14189694, 12815057, 17761775, 16721594
13332439, 14038787, 11071989, 14207902, 14062796, 12913474, 14390252
16314470, 13370330, 14062794, 13358781, 17333202, 12960925, 9659614
14546638, 13699124, 13936424, 9797851, 14301592, 16794240, 13338048
12938841, 12620823, 12656535, 12678920, 14488943, 16850197, 14791477
14062792, 13807411, 16794238, 15862022, 12594032, 13250244, 9761357
12612118, 14053457, 13527323, 10625145, 15862020, 13910420, 12780098
13696216, 10263668, 14841558, 16794242, 16944698, 15862023, 16056266
13834065, 14351566, 13723052, 13011409, 14063280, 13566938, 13737888
13624984, 16024441, 17333199, 13914613, 17540582, 14258925, 14222403
14755945, 13645875, 12571991, 14664355, 12998795, 13719081, 14469008
14188650, 17019974, 13742433, 16368108, 16314469, 12905058, 6690853
16212405, 12849688, 13742435, 13464002, 13534412, 12879027, 12585543
13790109, 12535346, 16382448, 12588744, 13916549, 13786142, 12847466
13855490, 13551402, 12582664, 14262913, 17332800, 14695377, 12912137
13612575, 13484963, 14163397, 17437634, 13772618, 16694777, 13070939
14369664, 12391034, 13605839, 16314467, 16279211, 12976376, 12755231
13680405, 14589750, 13742437, 14318397, 11868640, 14644185, 13326736
13596521, 13001379, 12898558, 17752121, 13099577, 9873405, 16372203
16344758, 11715084, 16231699, 9547706, 14040433, 12662040, 12617123
17748832, 16530565, 12845115, 16844086, 17748834, 13354082, 13397104
13913630, 16462834, 12983611, 13550185, 13810393, 14121009, 13065099
11840910, 13903046, 15862017, 13572659, 16294378, 13718279, 13657605
14480676, 13632717, 14668670, 14063281, 13420224, 13812031, 16299830
12646784, 14512189, 12755116, 13616375, 17230530, 14035825, 13427062
12861463, 13092220, 15862021, 13043012, 16619892, 13685544, 15862018
13499128, 13561750, 12718090, 13848402, 13725395, 12401111, 12796518
13362079, 12917230, 13042639, 13923374, 14220725, 12621588, 13524899
14751895, 14480674, 13916709, 14076523, 15905421, 12731940, 13343438
14205448, 17748835, 14127231, 17082364, 15853081, 14273397, 16844448
14467061, 12971775, 16864562, 14497307, 12748538, 10242202, 14230270
16382353, 13686047, 14095982, 17333203, 13591624, 14523004, 13440516
16794241, 14062795, 13035360, 13040943, 13843646, 16794243, 14841409
13059165, 14062797, 12959852, 12345082, 16703112, 13890080, 17333198
16450169, 12658411, 13780035, 14062793, 13038684, 16742095, 13742464
14052474, 13060271, 13911821, 13457582, 7509451, 13791364, 12821418
13502183, 13705338, 16794239, 15862024, 13554409, 13645917, 13103913, 12772404
--------------------------------------------------------------------------------
OPatch succeeded.
PL/SQL procedure successfully completed.
Function created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_XUPENG_GENERATE_2014Feb20_11_42_25.log
Apply script: /u01/app/oracle/product/11.2.0.3/db_1/rdbms/admin/catbundle_PSU_XUPENG_APPLY.sql
Rollback script: /u01/app/oracle/product/11.2.0.3/db_1/rdbms/admin/catbundle_PSU_XUPENG_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...
sys@XUPENG> COLUMN spool_file NEW_VALUE spool_file NOPRINT
sys@XUPENG> SELECT '/u01/app/oracle/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
sys@XUPENG> SPOOL &spool_file
sys@XUPENG> exec sys.dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
sys@XUPENG> PROMPT Skipping EM Repository because it is not installed or versions mismatch...
Skipping EM Repository because it is not installed or versions mismatch...
sys@XUPENG> PROMPT Processing Oracle Database Packages and Types...
Processing Oracle Database Packages and Types...
sys@XUPENG> ALTER SESSION SET current_schema = sys;
Session altered.
sys@XUPENG> @?/rdbms/admin/dbmsscnc.sql
sys@XUPENG> Rem
sys@XUPENG> Rem $Header: rdbms/admin/dbmsscnc.sql /st_rdbms_11.2.0.3.0dbpsu/1 2013/11/06 04:17:31 mtiwary Exp $
sys@XUPENG> Rem
sys@XUPENG> Rem dbmsscn.sql
sys@XUPENG> Rem
sys@XUPENG> Rem Copyright (c) 2012, 2013, Oracle and/or its affiliates.
sys@XUPENG> Rem All rights reserved.
sys@XUPENG> Rem
sys@XUPENG> Rem NAME
sys@XUPENG> Rem dbmsscnc.sql - dbms_scn package definition
sys@XUPENG> Rem
sys@XUPENG> Rem DESCRIPTION
sys@XUPENG> Rem <short description of component this file declares/defines>
sys@XUPENG> Rem
sys@XUPENG> Rem NOTES
sys@XUPENG> Rem <other useful comments, qualifications, etc.>
sys@XUPENG> Rem
sys@XUPENG> Rem MODIFIED (MM/DD/YY)
sys@XUPENG> Rem mtiwary 05/26/12 - Declarations and definitions related to DBMS_SCN
sys@XUPENG> Rem package.
sys@XUPENG> Rem mtiwary 05/26/12 - Created
sys@XUPENG> Rem
sys@XUPENG>
sys@XUPENG> Rem
sys@XUPENG> Rem BEGIN SQL_FILE_METADATA
sys@XUPENG> Rem SQL_SOURCE_FILE: rdbms/admin/dbmsscn.sql
sys@XUPENG> Rem SQL_SHIPPED_FILE:
sys@XUPENG> Rem SQL_PHASE:
sys@XUPENG> Rem SQL_STARTUP_MODE: NORMAL
sys@XUPENG> Rem SQL_IGNORABLE_ERRORS: NONE
sys@XUPENG> Rem SQL_CALLING_FILE:
sys@XUPENG> Rem END SQL_FILE_METADATA
sys@XUPENG>
sys@XUPENG> SET ECHO ON
sys@XUPENG> SET FEEDBACK 1
sys@XUPENG> SET NUMWIDTH 10
sys@XUPENG> SET LINESIZE 80
sys@XUPENG> SET TRIMSPOOL ON
sys@XUPENG> SET TAB OFF
sys@XUPENG> SET PAGESIZE 100
sys@XUPENG>
sys@XUPENG> CREATE OR REPLACE LIBRARY DBMS_SCN_LIB TRUSTED AS STATIC;
2 /
Library created.
sys@XUPENG>
sys@XUPENG> CREATE OR REPLACE PACKAGE DBMS_SCN AUTHID CURRENT_USER IS
2
3 DBMS_SCN_API_MAJOR_VERSION CONSTANT NUMBER := 1;
4 DBMS_SCN_API_MINOR_VERSION CONSTANT NUMBER := 0;
5
6 PROCEDURE GetCurrentSCNParams(
7 rsl OUT number,
8 headroom_in_scn OUT number,
9 headroom_in_sec OUT number,
10 cur_scn_compat OUT number,
11 max_scn_compat OUT number);
12
13 -- Currently no exceptions are thrown.
14 -- rsl - Reasonable SCN Limit as of 'now'
15 -- headroom_in_scn - Difference between current SCN and RSL
16 -- headroom_in_sec - number of seconds it would take to reach RSL
17 -- assuming a constant SCN consumption rate associated
18 -- with current SCN compatibility level
19 -- cur_scn_compat - current value of SCN compatibility
20 -- max_scn_compat - max value of SCN compatibility this database
21 -- understands
22
23 FUNCTION GetSCNParamsByCompat(
24 compat IN number,
25 rsl OUT number,
26 headroom_in_scn OUT number,
27 headroom_in_sec OUT number
28 ) RETURN boolean;
29
30 -- compat -- SCN compatibility value
31 -- rsl -- Reasonable SCN Limit
32 -- headroom_in_scn -- Difference between current SCN and RSL
33 -- headroom_in_sec -- number of seconds it would take to reach RSL
34 -- assuming a constant SCN consumption rate associated
35 -- with specified database SCN compatibility
36 --
37 -- Returns False if 'compat' parameter value is invalid, and OUT parameters
38 -- are not updated.
39
40 PROCEDURE GetSCNAutoRolloverParams(
41 effective_auto_rollover_ts OUT DATE,
42 target_compat OUT number,
43 is_enabled OUT boolean);
44
45 -- effective_auto_rollover_ts - timestamp at which rollover becomes
46 -- effective
47 -- target_compat - SCN compatibility value this database
48 -- will move to, as a result of
49 -- auto-rollover
50 -- is_enabled - TRUE if auto-rollover feature is
51 -- currently enabled
52
53 PROCEDURE EnableAutoRollover;
54
55 PROCEDURE DisableAutoRollover;
56
57 END DBMS_SCN;
58 /
Package created.
sys@XUPENG>
sys@XUPENG> @?/rdbms/admin/prvtscnc.plb
sys@XUPENG> SET ECHO ON
sys@XUPENG> SET FEEDBACK 1
sys@XUPENG> SET NUMWIDTH 10
sys@XUPENG> SET LINESIZE 80
sys@XUPENG> SET TRIMSPOOL ON
sys@XUPENG> SET TAB OFF
sys@XUPENG> SET PAGESIZE 100
sys@XUPENG> CREATE OR REPLACE PACKAGE BODY DBMS_SCN wrapped
2 a000000
3 1
4 abcd
5 abcd
6 abcd
7 abcd
8 abcd
9 abcd
10 abcd
11 abcd
12 abcd
13 abcd
14 abcd
15 abcd
16 abcd
17 abcd
18 abcd
19 b
20 6c0 243
21 QlmAiY1dAl0ShRRHlX+HGNAfF7Mwgw23ACAVfC9A2k7VVhtmMilHXbSA4+y0szHoAcIlGGvF
22 LFznjZK7HsiO4405ad7otP6DvBJPmF/CgKv7vWxPthzol8UbWtg5Rsh0bB1IL1o27IiiL4Pp
23 ghghXIzy7qpN8ZKAqy5GoYTd+NFVjhaAPl79bXMSsYU3kLeYwwq6YrfeYIGtMvJPmD01eYTm
24 6ZHFbXW65+zhiLyd4n6gFjHiFm8ewsIUlps9n1Qmhi8+HDugSGp5JJUj8nWOq0ENurliNrJN
25 hU0xgcfHK5K6QfbtOHA/U80YLHmYL19b0SJ/rClUGJ61NxJXZGyQ5KEL4FaSdiRh+mztwHkD
26 0vUMuhwvNnlpUxmcvWlSy/43x86V3wrQNDQ+u0hWeLus6JG2IndfBYS5uYxgDImhZhepALfL
27 t71Ti3U3O8u0T7YrCu/D3Cr1ZiWOVQsf/xfYVuerG93+lzkruPtiRdV4U5PReE9tBiwb0r+Z
28 zwEKhyQwCZo3l/PypHsCJbpX2E6cQwagpSSNihdqCzJce+R5Ek7PZ6VqrwhVeOL4icI=
29
30 /
Package body created.
sys@XUPENG> CREATE OR REPLACE PUBLIC SYNONYM dbms_scn FOR sys.dbms_scn;
Synonym created.
sys@XUPENG> /
Synonym created.
sys@XUPENG> GRANT EXECUTE ON dbms_scn TO PUBLIC;
Grant succeeded.
sys@XUPENG> /
Grant succeeded.
sys@XUPENG> PROMPT Skipping Oracle Workspace Manager because it is not installed or versions mismatch...
Skipping Oracle Workspace Manager because it is not installed or versions mismatch...
sys@XUPENG> PROMPT Skipping Oracle interMedia because it is not installed or versions mismatch...
Skipping Oracle interMedia because it is not installed or versions mismatch...
sys@XUPENG> PROMPT Skipping Spatial because it is not installed or versions mismatch...
Skipping Spatial because it is not installed or versions mismatch...
sys@XUPENG> ALTER SESSION SET current_schema = SYS;
Session altered.
sys@XUPENG> PROMPT Updating registry...
Updating registry...
sys@XUPENG> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '11.2.0.3',
9 9,
10 'PSU',
11 'PSU 11.2.0.3.9');
1 row created.
sys@XUPENG> COMMIT;
Commit complete.
sys@XUPENG> SPOOL off
sys@XUPENG> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_XUPENG_APPLY_2014Feb20_11_42_26.log
最后我们查看一下PSU的更新信息，查数据字点典
sys@XUPENG> set line 150
sys@XUPENG> col ACTION_TIME for a30
sys@XUPENG> col ACTION for a8
sys@XUPENG> col NAMESPACE for a8
sys@XUPENG> col VERSION for a10
sys@XUPENG> col BUNDLE_SERIES for a5
sys@XUPENG> col COMMENTS for a20
sys@XUPENG> select * from dba_registry_history;
ACTION_TIME ACTION NAMESPAC VERSION ID BUNDL COMMENTS
------------------------------ -------- -------- ---------- ---------- ----- --------------------
21-NOV-13 04.10.49.499847 PM VIEW INV 8289601 view invalidation
ALIDATE
21-NOV-13 04.10.49.851435 PM UPGRADE SERVER 11.2.0.3.0 Upgraded from 11.2.0
.1.0
21-NOV-13 04.18.05.730260 PM APPLY SERVER 11.2.0.3 0 PSU Patchset 11.2.0.2.0
22-NOV-13 12.58.39.026261 PM APPLY SERVER 11.2.0.3 8 PSU PSU 11.2.0.3.8
20-FEB-14 11.42.26.429093 AM APPLY SERVER 11.2.0.3 9 PSU PSU 11.2.0.3.9
5 rows selected.
我的PSU也安装完成了，我们可以定期的通过此方法对你的数据进行升级，前提你是support用户哦！！ 还要提示大家在生产系统上
升级一定要有完善的方案和对新的PATCH SET 和 PSU的bug修复以及适用度进行评估，不一定最新的就是最好的，适合才是最重要的
最后给大家附上最新的PATCH SET 和 PSU的更新进度表，供大家参考
补丁集/PSU 补丁号码 描述
11.2.0.4.0 13390677 11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER
11.2.0.3.8 16902043 DATABASE PATCH SET UPDATE 11.2.0.3.8 (INCLUDES CPUOCT2013)
11.2.0.3.7 16619892 DATABASE PATCH SET UPDATE 11.2.0.3.7 (INCLUDES CPUJUL2013)
11.2.0.3.6 16056266 DATABASE PATCH SET UPDATE 11.2.0.3.6 (INCLUDES CPUAPR2013)
11.2.0.3.5 14727310 DATABASE PATCH SET UPDATE 11.2.0.3.5 (INCLUDES CPUJAN2013):
11.2.0.3.4 14275605 DATABASE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES CPUOCT2012)
11.2.0.3.3 13923374 DATABASE PATCH SET UPDATE 11.2.0.3.3 (INCLUDES CPU JUL2012):
11.2.0.3.2 13696216 DATABASE PATCH SET UPDATE 11.2.0.3.2 (INCLUDES CPU APR2012)
11.2.0.3.1 13343438 DATABASE PATCH SET UPDATE 11.2.0.3.1 (INCLUDES CPU JAN2012)
11.2.0.3 10404530 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER
11.2.0.2.11 16619893 DATABASE PATCH SET UPDATE 11.2.0.2.11 (INCLUDES CPUJUL2013)
11.2.0.2.10 16056267 DATABASE PATCH SET UPDATE 11.2.0.2.10 (INCLUDES CPUAPR2013)
11.2.0.2.9 14727315 DATABASE PATCH SET UPDATE 11.2.0.2.9 (INCLUDES CPUJAN2013):
11.2.0.2.8 14275621 DATABASE PATCH SET UPDATE 11.2.0.2.8 (INCLUDES CPUOCT2012)
11.2.0.2.7 13923804 DATABASE PATCH SET UPDATE 11.2.0.2.7 (INCLUDES CPU JUL2012)
11.2.0.2.6 13696224 DATABASE PATCH SET UPDATE 11.2.0.2.6 (INCLUDES CPU APR2012)
11.2.0.2.5 13343424 DATABASE PATCH SET UPDATE 11.2.0.2.5 (INCLUDES CPU JAN2012)
11.2.0.2.4 12827726 DATABASE PSU 11.2.0.2.4 (INCLUDES CPUOCT2011)
11.2.0.2.3 12419331 DATABASE PSU 11.2.0.2.3 (INCLUDES CPUJUL2011)
11.2.0.2.2 11724916 DATABASE PSU 11.2.0.2.2 (INCLUDES CPUAPR2011)
11.2.0.2.1 10248523 DATABASE PSU 11.2.0.2.1
11.2.0.2 10098816 11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER
11.2.0.1.6 12419378 DATABASE PSU 11.2.0.1.6 (INCLUDES CPUJUL2011)
11.2.0.1.5 11724930 DATABASE PSU 11.2.0.1.5 (INCLUDES CPUAPR2011)
11.2.0.1.4 10248516 DATABASE PSU 11.2.0.1.4 (INCLUDES CPUJAN2011)
11.2.0.1.3 9952216 DATABASE PSU 11.2.0.1.3 (INCLUDES CPUOCT2010)
11.2.0.1.2 9654983 DATABASE PSU 11.2.0.1.2 (INCLUDES CPUJUL2010)
11.2.0.1.1 9352237 DATABASE PSU 11.2.0.1.1
11.1.0.7.17 17082366 [*] DATABASE PATCH SET UPDATE 11.1.0.7.17 (INCLUDES CPUOCT2013)
11.1.0.7.16 16619896 [*] DATABASE PATCH SET UPDATE 11.1.0.7.16 (INCLUDES CPUJUL2013)
11.1.0.7.15 16056268 [*] DATABASE PATCH SET UPDATE 11.1.0.7.15 (INCLUDES CPUAPR2013)
11.1.0.7.14 14739378 [*] DATABASE PATCH SET UPDATE 11.1.0.7.14 (INCLUDES CPUJAN2013)
11.1.0.7.13 14275623 [*] DATABASE PATCH SET UPDATE 11.1.0.7.13 (INCLUDES CPUOCT2012)
11.1.0.7.12 13923474 DATABASE PATCH SET UPDATE 11.1.0.7.12 (INCLUDES CPU JUL2012)
11.1.0.7.11 13621679 DATABASE PATCH SET UPDATE 11.1.0.7.11 (INCLUDES CPU APR2012)
11.1.0.7.10 13343461 DATABASE PATCH SET UPDATE 11.1.0.7.10 (INCLUDES CPU JAN2012)
11.1.0.7.9 12827740 DATABASE PSU 11.1.0.7.9 (INCLUDES CPUOCT2011)
11.1.0.7.8 12419384 DATABASE PSU 11.1.0.7.8 (INCLUDES CPUJUL2011)
11.1.0.7.7 11724936 DATABASE PSU 11.1.0.7.7 (INCLUDES CPUAPR2011)
11.1.0.7.6 10248531 DATABASE PSU 11.1.0.7.6 (INCLUDES CPUJAN2011)
11.1.0.7.5 9952228 DATABASE PSU 11.1.0.7.5 (INCLUDES CPUOCT2010)
11.1.0.7.4 9654987 DATABASE PSU 11.1.0.7.4 (INCLUDES CPUJUL2010)
11.1.0.7.3 9352179 DATABASE PSU 11.1.0.7.3 (INCLUDES CPUAPR2010)
11.1.0.7.2 9209238 DATABASE PSU 11.1.0.7.2 (INCLUDES CPUJAN2010)
11.1.0.7.1 8833297 DATABASE PSU 11.1.0.7.1 (INCLUDES CPUOCT2009)
11.1.0.7 6890831 11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.5.12 16619894 [*] DATABASE PATCH SET UPDATE 10.2.0.5.12 (INCLUDES CPUJUL2013):
10.2.0.5.11 16056270 [*] DATABASE PATCH SET UPDATE 10.2.0.5.11 (INCLUDES CPUAPR2013)
10.2.0.5.10 14727319 [*] DATABASE PATCH SET UPDATE 10.2.0.5.10 (INCLUDES CPUJAN2013):
10.2.0.5.9 14275629 [*] DATABASE PATCH SET UPDATE 10.2.0.5.9 (INCLUDES CPUOCT2012)
10.2.0.5.8 13923855 [*] DATABASE PATCH SET UPDATE 10.2.0.5.8 (INCLUDES CPU JUL2012)
10.2.0.5.7 13632743 [*] DATABASE PATCH SET UPDATE 10.2.0.5.7 (INCLUDES CPU APR2012)
10.2.0.5.6 13343471 [*] DATABASE PATCH SET UPDATE 10.2.0.5.6 (INCLUDES CPU JAN2012)
10.2.0.5.5 12827745 [*] DATABASE PSU 10.2.0.5.5 (INCLUDES CPUOCT2011)
10.2.0.5.4 12419392 DATABASE PSU 10.2.0.5.4 (INCLUDES CPUJUL2011)
10.2.0.5.3 11724962 DATABASE PSU 10.2.0.5.3 (INCLUDES CPUAPR2011)
10.2.0.5.2 10248542 DATABASE PSU 10.2.0.5.2 (INCLUDES CPUJAN2011)
10.2.0.5.1 9952230 DATABASE PSU 10.2.0.5.1 (INCLUDES CPUOCT2010)
10.2.0.5 8202632 10.2.0.5.0 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.4.17 16619897 [*] DATABASE PSU 10.2.0.4.17 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2013):
10.2.0.4.16 16056269 [*] DATABASE PSU 10.2.0.4.16 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2013)
10.2.0.4.15 14736542 [*] DATABASE PSU 10.2.0.4.15 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJAN2013):
10.2.0.4.14 14275630 [**] DATABASE PSU 10.2.0.4.14 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2012)
0.2.0.4.13 13923851 [*] DATABASE PSU 10.2.0.4.13 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2012)
10.2.0.4.12 12879933 [*]
DATABASE PSU 10.2.0.4.12 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2012)
10.2.0.4.11 12879929 [*] DATABASE PATCH SET UPDATE 10.2.0.4.11 (PRE-REQ 10.2.0.4.4 | INCLUDES CPUJAN2012)
10.2.0.4.10 12827778 DATABASE PSU 10.2.0.4.10 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2011)
10.2.0.4.9 12419397 DATABASE PSU 10.2.0.4.9 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2011)
10.2.0.4.8 11724977 DATABASE PSU 10.2.0.4.8 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2011)
10.2.0.4.7 10248636 DATABASE PSU 10.2.0.4.7 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJAN2011)
10.2.0.4.6 9952234 DATABASE PSU 10.2.0.4.6 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2010)
10.2.0.4.5 9654991 DATABASE PSU 10.2.0.4.5 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2010) [overlay PSU]
10.2.0.4.4 9352164 DATABASE PSU 10.2.0.4.4 (INCLUDES CPUAPR2010)
10.2.0.4.3 9119284 DATABASE PSU 10.2.0.4.3 (INCLUDES CPUJAN2010)
10.2.0.4.2 8833280 DATABASE PSU 10.2.0.4.2 (INCLUDES CPUOCT2009)
10.2.0.4.1 8576156 DATABASE PSU 10.2.0.4.1 (INCLUDES CPUJUL2009)
10.2.0.4 6810189 10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.3 5337014 10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.2 4547817 10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.5 4505133 10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.4 4163362 10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.3 3761843 10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.8 4547809 9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.7 4163445 9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.6 3948480 9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.5 3501955 ORACLE 9I DATABASE SERVER RELEASE 2 - PATCH SET 4 VERSION 9.2.0.5.0
9.2.0.4 3095277 9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.3 2761332 9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.2 2632931 9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
9.0.1.5 3301544 9.0.1.5 PATCHSET
9.0.1.4 2517300 9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER
9.0.1.3 2271678 9.0.1.3. PATCH SET FOR ORACLE DATA SERVER
8.1.7.4 2376472 8.1.7.4 PATCH SET FOR ORACLE DATA SERVER
8.1.7.3 2189751 8.1.7.3 PATCH SET FOR ORACLE DATA SERVER
8.1.7.2 1909158 8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER
提示：
[*] 10.2.0.4 和 10.2.0.5目前是extended support阶段，2011年8月1日之后发布的PSU需要ES License下载。
[**] 只在有限的平台提供
放在了自己空间上，大家可以自己想办法下载了！最新的补丁集是11.2.0.4，在mos上可以查到相关的文档,包括安装方法等。
在打补丁之前我们先来看看oracle补丁的相关术语的介绍，我们才知道oracle 的补丁的作用和补丁的类别是如何区分的??
现在对这些术语做些解释说明：
Interim patch/One-off patch：是我们常说的小补丁，为了修复某(几)个Bug而发布的补丁。这种补丁推荐在测试库上测试无误后再安装在生产库上。
Merged patch： 合并的补丁。当几个小补丁之间有冲突，不能同时安装的时候，需要提供这种Merged Patch。补丁冲突主要是由于2个或者多个补丁修改同一个文件，但是修改的内容是不同的。
Bundle patch：补丁集，修复多个Bug。在Windows平台上的Oracle没有小补丁，只有这种Bundle Patch。 这种补丁集会周期性的发布（至少每季一次）。这种补丁集是累积型的（cumulative），也就是每个Bundle patch 会包含之前的所有的Bundle Patch。 比如Windows Bundle Patch 16，它会包含之前所有15个Bundle Patch，所以我们总是推荐安装最新的Bundle Patch。Oracle的集群软件和数据库软件的Window Bundle Patch是同一个，比如Windows Bundle Patch 16(补丁号16167942，既可以打在集群上，也可以打在数据库上) 。
要了解Windows Bundle Patch的补丁号，可以参考MOS文档：
Note 161549.1 Oracle Database, Networking and Grid Agent Patches for Microsoft Platforms
Critical Patch Update (CPU)：每季度发布一次，用来修复安全方面的一些补丁，是累积型的。目前已经更名为Security Patch Update (SPU)。
点击下面的链接查看各个CPU所修复的具体问题：
http://www.oracle.com/technetwork/topics/security/alerts-086861.html
Patch Set Update (PSU)：每季度发布一次，修复比较严重的一些问题，包含每季的CPU，是累积型的。虽然在描述PSU的时候会用到数据库版本第5位，比如Database PSU 11.2.0.3.5，但实际上打完PSU后并不会真正改变数据库的版本，从v$version中看到的版本还是4位的(11.2.0.3.0)，第5位仍然是0。注意，Windows上没有CPU和PSU，Oracle的集群软件和数据库软件使用不同的PSU。
可以参考下面的MOS文档了解每个季度的CPU、PSU、Windows Bundle Patch的具体补丁号：
Note 1454618.1 Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets
Patch set： 是在大版本上发布的补丁集，修复了较多的Bug，可能会包含一些增强功能（Enhancement）。比如11.2是一个大版本，那么11.2.0.2就是一个Patch set。这种补丁集经过了严格的集成测试，也是累积型的。所以我们总是推荐安装最新的Patch Set。
Diagnostic patch：诊断补丁。有的时候诊断一个问题的时候，为了获得更多的诊断信息，Oracle的开发部门会提供一个Diagnostic Patch，这种补丁不是为了修复问题，而是诊断问题。
Composite Patch：从2012年4月份的Database PSU 11.2.0.3.2开始，推出一种新的概念叫Composite Patches。 这是一种新型的补丁包，它不同于其他的累积型补丁包。如果是第一次安装Composite Patches，那么该Composite Patches所包括的全部补丁都会被安装，后续安装的Composite Patches，只会安装对比前一次Composite Patches有变化的部分和新增加的补丁。
OPatch： 安装管理补丁而设计的工具，从Oracle 9.2版开始提供使用。通过Opatch工具，DBA可以方便安装、卸载补丁，也可以检测冲突等，从mos上可以
找到一个老外做的视频专门来介绍OPatch工具的使用，有账号的同学可以去看看，这里给没有账号的同学来一个截图过个眼瘾
我把每个oracle server 所对应的OPatch版本列表，大家可以参考一下,注意：这个表OPatch 版本列的内容会有所变化这个oracle内部会自行发布。
Oracle Database Server 产品（包括 CRS/ASM/RAC）
| Oracle 产品版本 | 下载 Opatch 版本 |
OPATCH 版本 (截至 2013年11月25日) |
|---|---|---|
| 9.2.0.x or 10.1.0.x | "10.1.0.0.0" (description "OPatch 9i, 10.1") | 1.0.0.0.64 |
| 10.2.0.x | "10.2.0.0.0" (description "OPatch 10.2") | 10.2.0.5.1 |
| 11.1.0.x | "11.1.0.0.0" (description "OPatch 11.1") | 11.1.0.10.2 |
| 11.2.0.x | "11.2.0.0.0" (description "OPatch 11.2") | 11.2.0.3.5 |
| 12.1.0.x | "12.1.0.0.0" (description "OPatch 12.1") | 12.1.0.1.2 |
第一次安装的composite patch为PSU 11.2.0.3.2：
Interim patches (1) :
Patch 13545268: applied on Fri Dec 09 10:59:28 EST 2011
Patch Description: "Database Patch Set Update : 11.2.0.3.2 (13545268)"
Created on 2 July 2012, 07:52:00 hrs PST8PDT
Sub-patch 13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"<==Sub-patch
Bugs fixed:
9275072, 9352237 ...
安装下一个composite patch PSU 11.2.0.3.3时，只需要安装有变化的部分和新增加的patches，不需要再安装之前已经安装的11.2.0.3.1 和11.2.0.3.2，之前已经安装的这部分就称为sub-patch：
Patch 13795248 : applied on Fri Dec 10 10:00:28 EST 2011
Patch Description: "Database Patch Set Update : 11.2.0.3.3 (13795248)" <==变化和新增的部分
Created on 2 July 2012, 07:52:00 hrs PST8PDT
Sub-patch 13545268; "Database Patch Set Update : 11.2.0.3.2 (13545268)" <==Sub-patch
Sub-patch 13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)" <==Sub-patch
Bugs fixed:
9275072, 9352237 ...
Composite Patches 的好处是减少补丁的安装量，减少与已经安装上补丁的冲突。
在MOS文档中有针对Composite Patches详细的说明：
Note 1376691.1 Composite Patches for Oracle Products [Video]
上面提到的所有补丁都会有一个补丁号，如果已经知道了补丁号，就可以直接使用下面的链接来下载这个补丁：
https://updates.oracle.com/download/补丁号.html
输入MOS的账号、密码就可以。
比如：11.2.0.3.0 PATCH SET 下载链接：
https://updates.oracle.com/download/10404530.html
Oracle推荐打的补丁，可以参考MOS文档：
Note 756671.1 Oracle Recommended Patches -- Oracle Database
（推荐打最新版本的Patch set、PSU/CPU及 Windows Bundle Patch）
操作系统和database 11.2.0.1.0相信大家都很好弄到手，我们就不说了，果断从mos上下载path set 和OPatch，psu我们也下载了。
p6880880这个包是OPatch的最新版本，其余的7个是我们11.2.3.0补丁集，总大小7.1个G，分别的作用如下图所示
我们这次是单实例打patch set 补丁我们就只用的上前两个压缩包喽！！打开压缩包里面有pathset的文档截图如下
我看到文档给出了两种升级方式，第一种In-Place Upgrade 意思是说将升级文件安装到原来的oracle home上，这样能够节省空间，但需要的时间很久，不推荐啊
第二中Out-Place Upgrade 将oracle database 安装到新的 oracle home目录，安装完成后，可以把原来的database目录迁移到新的oracle home 目录中即可。
这个是oracle 推荐的方法，它可以降低停机时间，也相对安全，只是这种方法需要2倍的硬盘空间。这里做过升级的同学应该清楚11g和10g的升级方式还是变化很大的，10g现在是extended support阶段，oracle公司马上就要让它退役了
我们以后要以11g为准了！！！
我们现在开始正式的数据库升级操作，我这里演示的是测试环境，请大家不要在生产系统上面去搞啊，这里只是教方法，具体的升级要有一套完整的升级方案，并反复测试才能正式启用。
测试环境我就不备份了，我们首先把数据库shutdown掉，再次关闭监听器，再确认以下目录是否存在
ORACLE_HOME/dbs
ORACLE_HOME/network/admin
ORACLE_HOME/hostname_dbname
ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_dbname
还要在这里强调一下升级步骤，先升级oracle 软件，就是把软件先升级了，再升级数据库，升级数据库也分两个方法一个是DBUA图形升级，一个是手工升级。
把已经上传的包解压缩，到安装包目录下运行
./runInstaller
来到此界面我选择最后一项Skip software updates 继续下一步
这里选第三项，到这个界面我们应该清楚为什么11g和10g升级不一样了！其实11g的patch set包就是个oracle soft。接续下一步
这个没什么可说的大家都懂得，保持和原来的数据库语言环境一致，我们继续
选择企业版 继续下一步
这里需要注意了默认的路径可不是11.2.0.3，我们需要手工改一下即可，这样就符合我们的安装升级要求了，oracle推荐的Out-Place Upgrade升级方法
继续下一步，我们到如下界面
点击“install”我们开是安装新版本的oracle，哈哈！！！
安装完成后会提示你root用户下运行一个root.sh的脚本，运行完后，自动弹出窗口让你配置listener
继续就行了
还是继续啊！！！
继续中。。。。。
点击下一步，我们配置完毕
配置完毕后，还会提示你升级oracle数据库，默认启动DBUA程序，这个是我们不启动DBUA，我们关掉窗口，如下图所示
ok后，我们继续下一步，安装完成退出窗口
我们在配置完监听后，没有启动DBUA程序，我们这里手工升级数据库吧！！！DBUA大家也可以练习我这里就不用了。
其实我们这里已经升级完oracle软件了，去更新一下oracle用户的.profile环境变量，这样可以把旧的oracle_home路径改成新的让用户识别到。
$ vi .profile
".profile" 16 lines, 434 characters
PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:.
export PATH
if [ -s "$MAIL" ] # This is at Shell startup. In normal
then echo "$MAILMSG" # operation, the Shell checks
fi # periodically.
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/db_1 改这里
export ORACLE_SID=xupeng11g
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
".profile" 16 lines, 436 characters
$ . ./.profile 执行生效
修改oracle 随机自启动的开关，如果你用的上的话
$ vi /etc/oratab
"/etc/oratab" 23 lines, 726 characters
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed
xupeng11g:/u01/app/oracle/product/11.2.0.3/db_1:N 修改这里的版本号，改成新的
我们还要把spfile或者pfile拷贝到新的oracle_home 目录下即可,不要启动listener和db
开始升级实例，用SYSDBA用户登录 SQL*PLUS
SQL> startup upgrade 我去看一下alert日志就行，看有没有报错，如果安装都问题这不肯定也没有问题
SQL> SPOOL upgrade.log 输出一个当前日志
SQL> @?/rdbms/admin/utlu112i.sql 运行升级工具
SQL> @?/rdbms/admin/catupgrd.sql 运行升级脚本，这个时间比较久大概30-40分钟吧！！！
运行完成后catupgrd.sql里执行了shutdown 命令了，我们把数据库再次启动
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql 生成数据字典和pl/sql等
最后我们需要查看dba_invalid_objects 有没有失效对象，再查看一下各个组件的版本
SQL> SELECT COMP_NAME, VERSION, STATUSFROM SYS.DBA_REGISTRY;
COMP_NAME VERSION STATUS
———————————————————————- ———————-
OWB 11.2.0.1.0 VALID
Oracle Application Express 3.2.1.00.10 VALID
Oracle Enterprise Manager 11.2.0.3.0 VALID
OLAP Catalog 11.2.0.3.0 VALID
Spatial 11.2.0.3.0 VALID
Oracle Multimedia 11.2.0.3.0 VALID
Oracle XML Database 11.2.0.3.0 VALID
Oracle Text 11.2.0.3.0 VALID
Oracle Expression Filter 11.2.0.3.0 VALID
Oracle Rules Manager 11.2.0.3.0 VALID
Oracle Workspace Manager 11.2.0.3.0 VALID
COMP_NAME VERSION STATUS
———————————————————————- ———————-
Oracle Database Catalog Views 11.2.0.3.0 VALID
Oracle Database Packages and Types 11.2.0.3.0 VALID
JServer JAVA Virtual Machine 11.2.0.3.0 VALID
Oracle XDK r 11.2.0.3.0 VALID
Oracle Database Java Packages 11.2.0.3.0 VALID
OLAP Analytic Workspace 11.2.0.3.0 VALID
Oracle OLAP API 11.2.0.3.0 VALID
18 rows selected.
到这里我们的升级path set 就完成了，我们下一步来看PSU的安装，其实PSU的安装很简单，先升级你的OPatch Version的版本
先查看一下我的OPatch的版本是多少，默认的基础版本是11.2.0.1的。解压p6880880这个包，把内容释放到ORACLE_HOME/OPatch，
之前的OPatch可以先备份一份，以防出错，然后在查询OPatch version 如下命令
# opatch version
OPatch Version: 11.2.0.3.5
OPatch succeeded.
我们在解压缩你的PSU补丁包，我们拿到11.2.0.3.9的包，首先读一下包里的readme文档，这个很关键啊，基本上是step by step的操作
$ unzip p17540582_112030_LINUX.zip -d patch 解压缩到patch目录下
我们手动打patch
$ opatch napply -oh $ORACLE_HOME -local /u01/app/oracle/patch/17540582
Oracle 中间补丁程序安装程序版本 11.2.0.3.5
版权所有 (c) 2013, Oracle Corporation。保留所有权利。
Oracle Home : /u01/app/oracle/product/11.2.0.3/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.3/db_1/oraInst.loc
OPatch version : 11.2.0.3.5
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2014-02-20_11-32-35上午_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 17540582
是否继续? [y|n]
y
User Responded with: Y
All checks passed.
请关闭本地系统上在此 ORACLE_HOME 之外运行的 Oracle 实例。
(Oracle 主目录 = '/u01/app/oracle/product/11.2.0.3/db_1')
本地系统是否已准备打补丁? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '17540582' to OH '/u01/app/oracle/product/11.2.0.3/db_1'
ApplySession: Oracle 主目录中不存在可选组件 [ oracle.precomp.lang, 11.2.0.3.0 ] , 或找到更高版本。
正在为组件 oracle.rdbms, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.rsf, 11.2.0.3.0 打补丁...
正在为组件 oracle.sdo, 11.2.0.3.0 打补丁...
正在为组件 oracle.ldap.rsf, 11.2.0.3.0 打补丁...
正在为组件 oracle.precomp.common, 11.2.0.3.0 打补丁...
正在为组件 oracle.ordim.client, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.util, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.dbscripts, 11.2.0.3.0 打补丁...
正在为组件 oracle.sdo.locator, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.rman, 11.2.0.3.0 打补丁...
正在为组件 oracle.ordim.jai, 11.2.0.3.0 打补丁...
Verifying the update...
Composite patch 17540582 successfully applie
查看更新的结果
$ opatch lspatches
17540582;Database Patch Set Update : 11.2.0.3.9 (17540582)
查看更新补丁的内容
$ opatch lsinventory
Oracle 中间补丁程序安装程序版本 11.2.0.3.5
版权所有 (c) 2013, Oracle Corporation。保留所有权利。
Oracle Home : /u01/app/oracle/product/11.2.0.3/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.3/db_1/oraInst.loc
OPatch version : 11.2.0.3.5
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2014-02-20_11-36-48上午_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-02-20_11-36-48上午.txt
--------------------------------------------------------------------------------
已安装的顶级产品 (1):
Oracle Database 11g 11.2.0.3.0
此 Oracle 主目录中已安装 1 个产品。
中间补丁程序 (1) :
Patch 17540582 : applied on Thu Feb 20 11:33:06 CST 2014
Unique Patch ID: 16985511
Patch description: "Database Patch Set Update : 11.2.0.3.9 (17540582)"
Created on 7 Jan 2014, 03:01:22 hrs PST8PDT
Sub-patch 16902043; "Database Patch Set Update : 11.2.0.3.8 (16902043)"
Sub-patch 16619892; "Database Patch Set Update : 11.2.0.3.7 (16619892)"
Sub-patch 16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Sub-patch 14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch 14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch 13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch 13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch 13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
Bugs fixed:
13593999, 10350832, 14138130, 12919564, 13561951, 14198511, 13588248
13080778, 13804294, 16710324, 12873183, 14472647, 12880299, 13369579
14409183, 13492735, 12857027, 13496884, 14263036, 14263073, 13015379
16038929, 17748833, 16563678, 13732226, 13866822, 13742434, 13944971
12950644, 17748831, 12899768, 13063120, 13958038, 14613900, 13972394
11877623, 17088068, 13072654, 12395918, 13814739, 17343514, 13649031
13981051, 12797765, 17333200, 12923168, 16761566, 16279401, 13384182
13466801, 15996344, 14207163, 13724193, 13642044, 11063191, 13945708
12797420, 12865902, 15869211, 13041324, 14003090, 16314468, 16019955
11708510, 14637368, 13026410, 13737746, 13742438, 15841373, 16347904
15910002, 16362358, 14398795, 13579992, 16344871, 10400244, 14275605
13742436, 9858539, 14841812, 16338983, 9703627, 13483354, 14207317
14393728, 12764337, 16902043, 14459552, 14191508, 12964067, 12780983
12583611, 14383007, 14546575, 15862016, 13476583, 13489024, 17748830
14088346, 13448206, 16314466, 13419660, 14110275, 13430938, 13467683
14548763, 12834027, 13632809, 13377816, 13036331, 14727310, 16175381
13584130, 12829021, 15862019, 12794305, 14546673, 12791981, 13787482
13503598, 10133521, 12744759, 13399435, 13553883, 14023636, 14762511
9095696, 14343501, 13860201, 13257247, 14176879, 16014985, 12312133
14480675, 16306019, 13559697, 9706792, 12974860, 12940620, 13098318
13773133, 15883525, 16794244, 13340388, 13366202, 13528551, 12894807
12747437, 13454210, 12748240, 13385346, 15987992, 13923995, 13582702
14571027, 12784406, 13907462, 13493847, 13857111, 13035804, 16710363
13544396, 14128555, 8547978, 14226599, 17478415, 17333197, 9397635
14007968, 12925089, 12693626, 14189694, 12815057, 17761775, 16721594
13332439, 14038787, 11071989, 14207902, 14062796, 12913474, 14390252
16314470, 13370330, 14062794, 13358781, 17333202, 12960925, 9659614
14546638, 13699124, 13936424, 9797851, 14301592, 16794240, 13338048
12938841, 12620823, 12656535, 12678920, 14488943, 16850197, 14791477
14062792, 13807411, 16794238, 15862022, 12594032, 13250244, 9761357
12612118, 14053457, 13527323, 10625145, 15862020, 13910420, 12780098
13696216, 10263668, 14841558, 16794242, 16944698, 15862023, 16056266
13834065, 14351566, 13723052, 13011409, 14063280, 13566938, 13737888
13624984, 16024441, 17333199, 13914613, 17540582, 14258925, 14222403
14755945, 13645875, 12571991, 14664355, 12998795, 13719081, 14469008
14188650, 17019974, 13742433, 16368108, 16314469, 12905058, 6690853
16212405, 12849688, 13742435, 13464002, 13534412, 12879027, 12585543
13790109, 12535346, 16382448, 12588744, 13916549, 13786142, 12847466
13855490, 13551402, 12582664, 14262913, 17332800, 14695377, 12912137
13612575, 13484963, 14163397, 17437634, 13772618, 16694777, 13070939
14369664, 12391034, 13605839, 16314467, 16279211, 12976376, 12755231
13680405, 14589750, 13742437, 14318397, 11868640, 14644185, 13326736
13596521, 13001379, 12898558, 17752121, 13099577, 9873405, 16372203
16344758, 11715084, 16231699, 9547706, 14040433, 12662040, 12617123
17748832, 16530565, 12845115, 16844086, 17748834, 13354082, 13397104
13913630, 16462834, 12983611, 13550185, 13810393, 14121009, 13065099
11840910, 13903046, 15862017, 13572659, 16294378, 13718279, 13657605
14480676, 13632717, 14668670, 14063281, 13420224, 13812031, 16299830
12646784, 14512189, 12755116, 13616375, 17230530, 14035825, 13427062
12861463, 13092220, 15862021, 13043012, 16619892, 13685544, 15862018
13499128, 13561750, 12718090, 13848402, 13725395, 12401111, 12796518
13362079, 12917230, 13042639, 13923374, 14220725, 12621588, 13524899
14751895, 14480674, 13916709, 14076523, 15905421, 12731940, 13343438
14205448, 17748835, 14127231, 17082364, 15853081, 14273397, 16844448
14467061, 12971775, 16864562, 14497307, 12748538, 10242202, 14230270
16382353, 13686047, 14095982, 17333203, 13591624, 14523004, 13440516
16794241, 14062795, 13035360, 13040943, 13843646, 16794243, 14841409
13059165, 14062797, 12959852, 12345082, 16703112, 13890080, 17333198
16450169, 12658411, 13780035, 14062793, 13038684, 16742095, 13742464
14052474, 13060271, 13911821, 13457582, 7509451, 13791364, 12821418
13502183, 13705338, 16794239, 15862024, 13554409, 13645917, 13103913, 12772404
--------------------------------------------------------------------------------
OPatch succeeded.
数据库启动，并加载修改SQL Files到数据库
sys@XUPENG> @?/rdbms/admin/catbundle.sql psu applyPL/SQL procedure successfully completed.
Function created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_XUPENG_GENERATE_2014Feb20_11_42_25.log
Apply script: /u01/app/oracle/product/11.2.0.3/db_1/rdbms/admin/catbundle_PSU_XUPENG_APPLY.sql
Rollback script: /u01/app/oracle/product/11.2.0.3/db_1/rdbms/admin/catbundle_PSU_XUPENG_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...
sys@XUPENG> COLUMN spool_file NEW_VALUE spool_file NOPRINT
sys@XUPENG> SELECT '/u01/app/oracle/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
sys@XUPENG> SPOOL &spool_file
sys@XUPENG> exec sys.dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
sys@XUPENG> PROMPT Skipping EM Repository because it is not installed or versions mismatch...
Skipping EM Repository because it is not installed or versions mismatch...
sys@XUPENG> PROMPT Processing Oracle Database Packages and Types...
Processing Oracle Database Packages and Types...
sys@XUPENG> ALTER SESSION SET current_schema = sys;
Session altered.
sys@XUPENG> @?/rdbms/admin/dbmsscnc.sql
sys@XUPENG> Rem
sys@XUPENG> Rem $Header: rdbms/admin/dbmsscnc.sql /st_rdbms_11.2.0.3.0dbpsu/1 2013/11/06 04:17:31 mtiwary Exp $
sys@XUPENG> Rem
sys@XUPENG> Rem dbmsscn.sql
sys@XUPENG> Rem
sys@XUPENG> Rem Copyright (c) 2012, 2013, Oracle and/or its affiliates.
sys@XUPENG> Rem All rights reserved.
sys@XUPENG> Rem
sys@XUPENG> Rem NAME
sys@XUPENG> Rem dbmsscnc.sql - dbms_scn package definition
sys@XUPENG> Rem
sys@XUPENG> Rem DESCRIPTION
sys@XUPENG> Rem <short description of component this file declares/defines>
sys@XUPENG> Rem
sys@XUPENG> Rem NOTES
sys@XUPENG> Rem <other useful comments, qualifications, etc.>
sys@XUPENG> Rem
sys@XUPENG> Rem MODIFIED (MM/DD/YY)
sys@XUPENG> Rem mtiwary 05/26/12 - Declarations and definitions related to DBMS_SCN
sys@XUPENG> Rem package.
sys@XUPENG> Rem mtiwary 05/26/12 - Created
sys@XUPENG> Rem
sys@XUPENG>
sys@XUPENG> Rem
sys@XUPENG> Rem BEGIN SQL_FILE_METADATA
sys@XUPENG> Rem SQL_SOURCE_FILE: rdbms/admin/dbmsscn.sql
sys@XUPENG> Rem SQL_SHIPPED_FILE:
sys@XUPENG> Rem SQL_PHASE:
sys@XUPENG> Rem SQL_STARTUP_MODE: NORMAL
sys@XUPENG> Rem SQL_IGNORABLE_ERRORS: NONE
sys@XUPENG> Rem SQL_CALLING_FILE:
sys@XUPENG> Rem END SQL_FILE_METADATA
sys@XUPENG>
sys@XUPENG> SET ECHO ON
sys@XUPENG> SET FEEDBACK 1
sys@XUPENG> SET NUMWIDTH 10
sys@XUPENG> SET LINESIZE 80
sys@XUPENG> SET TRIMSPOOL ON
sys@XUPENG> SET TAB OFF
sys@XUPENG> SET PAGESIZE 100
sys@XUPENG>
sys@XUPENG> CREATE OR REPLACE LIBRARY DBMS_SCN_LIB TRUSTED AS STATIC;
2 /
Library created.
sys@XUPENG>
sys@XUPENG> CREATE OR REPLACE PACKAGE DBMS_SCN AUTHID CURRENT_USER IS
2
3 DBMS_SCN_API_MAJOR_VERSION CONSTANT NUMBER := 1;
4 DBMS_SCN_API_MINOR_VERSION CONSTANT NUMBER := 0;
5
6 PROCEDURE GetCurrentSCNParams(
7 rsl OUT number,
8 headroom_in_scn OUT number,
9 headroom_in_sec OUT number,
10 cur_scn_compat OUT number,
11 max_scn_compat OUT number);
12
13 -- Currently no exceptions are thrown.
14 -- rsl - Reasonable SCN Limit as of 'now'
15 -- headroom_in_scn - Difference between current SCN and RSL
16 -- headroom_in_sec - number of seconds it would take to reach RSL
17 -- assuming a constant SCN consumption rate associated
18 -- with current SCN compatibility level
19 -- cur_scn_compat - current value of SCN compatibility
20 -- max_scn_compat - max value of SCN compatibility this database
21 -- understands
22
23 FUNCTION GetSCNParamsByCompat(
24 compat IN number,
25 rsl OUT number,
26 headroom_in_scn OUT number,
27 headroom_in_sec OUT number
28 ) RETURN boolean;
29
30 -- compat -- SCN compatibility value
31 -- rsl -- Reasonable SCN Limit
32 -- headroom_in_scn -- Difference between current SCN and RSL
33 -- headroom_in_sec -- number of seconds it would take to reach RSL
34 -- assuming a constant SCN consumption rate associated
35 -- with specified database SCN compatibility
36 --
37 -- Returns False if 'compat' parameter value is invalid, and OUT parameters
38 -- are not updated.
39
40 PROCEDURE GetSCNAutoRolloverParams(
41 effective_auto_rollover_ts OUT DATE,
42 target_compat OUT number,
43 is_enabled OUT boolean);
44
45 -- effective_auto_rollover_ts - timestamp at which rollover becomes
46 -- effective
47 -- target_compat - SCN compatibility value this database
48 -- will move to, as a result of
49 -- auto-rollover
50 -- is_enabled - TRUE if auto-rollover feature is
51 -- currently enabled
52
53 PROCEDURE EnableAutoRollover;
54
55 PROCEDURE DisableAutoRollover;
56
57 END DBMS_SCN;
58 /
Package created.
sys@XUPENG>
sys@XUPENG> @?/rdbms/admin/prvtscnc.plb
sys@XUPENG> SET ECHO ON
sys@XUPENG> SET FEEDBACK 1
sys@XUPENG> SET NUMWIDTH 10
sys@XUPENG> SET LINESIZE 80
sys@XUPENG> SET TRIMSPOOL ON
sys@XUPENG> SET TAB OFF
sys@XUPENG> SET PAGESIZE 100
sys@XUPENG> CREATE OR REPLACE PACKAGE BODY DBMS_SCN wrapped
2 a000000
3 1
4 abcd
5 abcd
6 abcd
7 abcd
8 abcd
9 abcd
10 abcd
11 abcd
12 abcd
13 abcd
14 abcd
15 abcd
16 abcd
17 abcd
18 abcd
19 b
20 6c0 243
21 QlmAiY1dAl0ShRRHlX+HGNAfF7Mwgw23ACAVfC9A2k7VVhtmMilHXbSA4+y0szHoAcIlGGvF
22 LFznjZK7HsiO4405ad7otP6DvBJPmF/CgKv7vWxPthzol8UbWtg5Rsh0bB1IL1o27IiiL4Pp
23 ghghXIzy7qpN8ZKAqy5GoYTd+NFVjhaAPl79bXMSsYU3kLeYwwq6YrfeYIGtMvJPmD01eYTm
24 6ZHFbXW65+zhiLyd4n6gFjHiFm8ewsIUlps9n1Qmhi8+HDugSGp5JJUj8nWOq0ENurliNrJN
25 hU0xgcfHK5K6QfbtOHA/U80YLHmYL19b0SJ/rClUGJ61NxJXZGyQ5KEL4FaSdiRh+mztwHkD
26 0vUMuhwvNnlpUxmcvWlSy/43x86V3wrQNDQ+u0hWeLus6JG2IndfBYS5uYxgDImhZhepALfL
27 t71Ti3U3O8u0T7YrCu/D3Cr1ZiWOVQsf/xfYVuerG93+lzkruPtiRdV4U5PReE9tBiwb0r+Z
28 zwEKhyQwCZo3l/PypHsCJbpX2E6cQwagpSSNihdqCzJce+R5Ek7PZ6VqrwhVeOL4icI=
29
30 /
Package body created.
sys@XUPENG> CREATE OR REPLACE PUBLIC SYNONYM dbms_scn FOR sys.dbms_scn;
Synonym created.
sys@XUPENG> /
Synonym created.
sys@XUPENG> GRANT EXECUTE ON dbms_scn TO PUBLIC;
Grant succeeded.
sys@XUPENG> /
Grant succeeded.
sys@XUPENG> PROMPT Skipping Oracle Workspace Manager because it is not installed or versions mismatch...
Skipping Oracle Workspace Manager because it is not installed or versions mismatch...
sys@XUPENG> PROMPT Skipping Oracle interMedia because it is not installed or versions mismatch...
Skipping Oracle interMedia because it is not installed or versions mismatch...
sys@XUPENG> PROMPT Skipping Spatial because it is not installed or versions mismatch...
Skipping Spatial because it is not installed or versions mismatch...
sys@XUPENG> ALTER SESSION SET current_schema = SYS;
Session altered.
sys@XUPENG> PROMPT Updating registry...
Updating registry...
sys@XUPENG> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '11.2.0.3',
9 9,
10 'PSU',
11 'PSU 11.2.0.3.9');
1 row created.
sys@XUPENG> COMMIT;
Commit complete.
sys@XUPENG> SPOOL off
sys@XUPENG> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_XUPENG_APPLY_2014Feb20_11_42_26.log
sys@XUPENG> set line 150
sys@XUPENG> col ACTION_TIME for a30
sys@XUPENG> col ACTION for a8
sys@XUPENG> col NAMESPACE for a8
sys@XUPENG> col VERSION for a10
sys@XUPENG> col BUNDLE_SERIES for a5
sys@XUPENG> col COMMENTS for a20
sys@XUPENG> select * from dba_registry_history;
ACTION_TIME ACTION NAMESPAC VERSION ID BUNDL COMMENTS
------------------------------ -------- -------- ---------- ---------- ----- --------------------
21-NOV-13 04.10.49.499847 PM VIEW INV 8289601 view invalidation
ALIDATE
21-NOV-13 04.10.49.851435 PM UPGRADE SERVER 11.2.0.3.0 Upgraded from 11.2.0
.1.0
21-NOV-13 04.18.05.730260 PM APPLY SERVER 11.2.0.3 0 PSU Patchset 11.2.0.2.0
22-NOV-13 12.58.39.026261 PM APPLY SERVER 11.2.0.3 8 PSU PSU 11.2.0.3.8
20-FEB-14 11.42.26.429093 AM APPLY SERVER 11.2.0.3 9 PSU PSU 11.2.0.3.9
5 rows selected.
我的PSU也安装完成了，我们可以定期的通过此方法对你的数据进行升级，前提你是support用户哦！！ 还要提示大家在生产系统上
升级一定要有完善的方案和对新的PATCH SET 和 PSU的bug修复以及适用度进行评估，不一定最新的就是最好的，适合才是最重要的
最后给大家附上最新的PATCH SET 和 PSU的更新进度表，供大家参考
补丁集/PSU 补丁号码 描述
11.2.0.4.0 13390677 11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER
11.2.0.3.8 16902043 DATABASE PATCH SET UPDATE 11.2.0.3.8 (INCLUDES CPUOCT2013)
11.2.0.3.7 16619892 DATABASE PATCH SET UPDATE 11.2.0.3.7 (INCLUDES CPUJUL2013)
11.2.0.3.6 16056266 DATABASE PATCH SET UPDATE 11.2.0.3.6 (INCLUDES CPUAPR2013)
11.2.0.3.5 14727310 DATABASE PATCH SET UPDATE 11.2.0.3.5 (INCLUDES CPUJAN2013):
11.2.0.3.4 14275605 DATABASE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES CPUOCT2012)
11.2.0.3.3 13923374 DATABASE PATCH SET UPDATE 11.2.0.3.3 (INCLUDES CPU JUL2012):
11.2.0.3.2 13696216 DATABASE PATCH SET UPDATE 11.2.0.3.2 (INCLUDES CPU APR2012)
11.2.0.3.1 13343438 DATABASE PATCH SET UPDATE 11.2.0.3.1 (INCLUDES CPU JAN2012)
11.2.0.3 10404530 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER
11.2.0.2.11 16619893 DATABASE PATCH SET UPDATE 11.2.0.2.11 (INCLUDES CPUJUL2013)
11.2.0.2.10 16056267 DATABASE PATCH SET UPDATE 11.2.0.2.10 (INCLUDES CPUAPR2013)
11.2.0.2.9 14727315 DATABASE PATCH SET UPDATE 11.2.0.2.9 (INCLUDES CPUJAN2013):
11.2.0.2.8 14275621 DATABASE PATCH SET UPDATE 11.2.0.2.8 (INCLUDES CPUOCT2012)
11.2.0.2.7 13923804 DATABASE PATCH SET UPDATE 11.2.0.2.7 (INCLUDES CPU JUL2012)
11.2.0.2.6 13696224 DATABASE PATCH SET UPDATE 11.2.0.2.6 (INCLUDES CPU APR2012)
11.2.0.2.5 13343424 DATABASE PATCH SET UPDATE 11.2.0.2.5 (INCLUDES CPU JAN2012)
11.2.0.2.4 12827726 DATABASE PSU 11.2.0.2.4 (INCLUDES CPUOCT2011)
11.2.0.2.3 12419331 DATABASE PSU 11.2.0.2.3 (INCLUDES CPUJUL2011)
11.2.0.2.2 11724916 DATABASE PSU 11.2.0.2.2 (INCLUDES CPUAPR2011)
11.2.0.2.1 10248523 DATABASE PSU 11.2.0.2.1
11.2.0.2 10098816 11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER
11.2.0.1.6 12419378 DATABASE PSU 11.2.0.1.6 (INCLUDES CPUJUL2011)
11.2.0.1.5 11724930 DATABASE PSU 11.2.0.1.5 (INCLUDES CPUAPR2011)
11.2.0.1.4 10248516 DATABASE PSU 11.2.0.1.4 (INCLUDES CPUJAN2011)
11.2.0.1.3 9952216 DATABASE PSU 11.2.0.1.3 (INCLUDES CPUOCT2010)
11.2.0.1.2 9654983 DATABASE PSU 11.2.0.1.2 (INCLUDES CPUJUL2010)
11.2.0.1.1 9352237 DATABASE PSU 11.2.0.1.1
11.1.0.7.17 17082366 [*] DATABASE PATCH SET UPDATE 11.1.0.7.17 (INCLUDES CPUOCT2013)
11.1.0.7.16 16619896 [*] DATABASE PATCH SET UPDATE 11.1.0.7.16 (INCLUDES CPUJUL2013)
11.1.0.7.15 16056268 [*] DATABASE PATCH SET UPDATE 11.1.0.7.15 (INCLUDES CPUAPR2013)
11.1.0.7.14 14739378 [*] DATABASE PATCH SET UPDATE 11.1.0.7.14 (INCLUDES CPUJAN2013)
11.1.0.7.13 14275623 [*] DATABASE PATCH SET UPDATE 11.1.0.7.13 (INCLUDES CPUOCT2012)
11.1.0.7.12 13923474 DATABASE PATCH SET UPDATE 11.1.0.7.12 (INCLUDES CPU JUL2012)
11.1.0.7.11 13621679 DATABASE PATCH SET UPDATE 11.1.0.7.11 (INCLUDES CPU APR2012)
11.1.0.7.10 13343461 DATABASE PATCH SET UPDATE 11.1.0.7.10 (INCLUDES CPU JAN2012)
11.1.0.7.9 12827740 DATABASE PSU 11.1.0.7.9 (INCLUDES CPUOCT2011)
11.1.0.7.8 12419384 DATABASE PSU 11.1.0.7.8 (INCLUDES CPUJUL2011)
11.1.0.7.7 11724936 DATABASE PSU 11.1.0.7.7 (INCLUDES CPUAPR2011)
11.1.0.7.6 10248531 DATABASE PSU 11.1.0.7.6 (INCLUDES CPUJAN2011)
11.1.0.7.5 9952228 DATABASE PSU 11.1.0.7.5 (INCLUDES CPUOCT2010)
11.1.0.7.4 9654987 DATABASE PSU 11.1.0.7.4 (INCLUDES CPUJUL2010)
11.1.0.7.3 9352179 DATABASE PSU 11.1.0.7.3 (INCLUDES CPUAPR2010)
11.1.0.7.2 9209238 DATABASE PSU 11.1.0.7.2 (INCLUDES CPUJAN2010)
11.1.0.7.1 8833297 DATABASE PSU 11.1.0.7.1 (INCLUDES CPUOCT2009)
11.1.0.7 6890831 11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.5.12 16619894 [*] DATABASE PATCH SET UPDATE 10.2.0.5.12 (INCLUDES CPUJUL2013):
10.2.0.5.11 16056270 [*] DATABASE PATCH SET UPDATE 10.2.0.5.11 (INCLUDES CPUAPR2013)
10.2.0.5.10 14727319 [*] DATABASE PATCH SET UPDATE 10.2.0.5.10 (INCLUDES CPUJAN2013):
10.2.0.5.9 14275629 [*] DATABASE PATCH SET UPDATE 10.2.0.5.9 (INCLUDES CPUOCT2012)
10.2.0.5.8 13923855 [*] DATABASE PATCH SET UPDATE 10.2.0.5.8 (INCLUDES CPU JUL2012)
10.2.0.5.7 13632743 [*] DATABASE PATCH SET UPDATE 10.2.0.5.7 (INCLUDES CPU APR2012)
10.2.0.5.6 13343471 [*] DATABASE PATCH SET UPDATE 10.2.0.5.6 (INCLUDES CPU JAN2012)
10.2.0.5.5 12827745 [*] DATABASE PSU 10.2.0.5.5 (INCLUDES CPUOCT2011)
10.2.0.5.4 12419392 DATABASE PSU 10.2.0.5.4 (INCLUDES CPUJUL2011)
10.2.0.5.3 11724962 DATABASE PSU 10.2.0.5.3 (INCLUDES CPUAPR2011)
10.2.0.5.2 10248542 DATABASE PSU 10.2.0.5.2 (INCLUDES CPUJAN2011)
10.2.0.5.1 9952230 DATABASE PSU 10.2.0.5.1 (INCLUDES CPUOCT2010)
10.2.0.5 8202632 10.2.0.5.0 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.4.17 16619897 [*] DATABASE PSU 10.2.0.4.17 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2013):
10.2.0.4.16 16056269 [*] DATABASE PSU 10.2.0.4.16 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2013)
10.2.0.4.15 14736542 [*] DATABASE PSU 10.2.0.4.15 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJAN2013):
10.2.0.4.14 14275630 [**] DATABASE PSU 10.2.0.4.14 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2012)
0.2.0.4.13 13923851 [*] DATABASE PSU 10.2.0.4.13 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2012)
10.2.0.4.12 12879933 [*]
DATABASE PSU 10.2.0.4.12 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2012)
10.2.0.4.11 12879929 [*] DATABASE PATCH SET UPDATE 10.2.0.4.11 (PRE-REQ 10.2.0.4.4 | INCLUDES CPUJAN2012)
10.2.0.4.10 12827778 DATABASE PSU 10.2.0.4.10 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2011)
10.2.0.4.9 12419397 DATABASE PSU 10.2.0.4.9 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2011)
10.2.0.4.8 11724977 DATABASE PSU 10.2.0.4.8 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2011)
10.2.0.4.7 10248636 DATABASE PSU 10.2.0.4.7 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJAN2011)
10.2.0.4.6 9952234 DATABASE PSU 10.2.0.4.6 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2010)
10.2.0.4.5 9654991 DATABASE PSU 10.2.0.4.5 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2010) [overlay PSU]
10.2.0.4.4 9352164 DATABASE PSU 10.2.0.4.4 (INCLUDES CPUAPR2010)
10.2.0.4.3 9119284 DATABASE PSU 10.2.0.4.3 (INCLUDES CPUJAN2010)
10.2.0.4.2 8833280 DATABASE PSU 10.2.0.4.2 (INCLUDES CPUOCT2009)
10.2.0.4.1 8576156 DATABASE PSU 10.2.0.4.1 (INCLUDES CPUJUL2009)
10.2.0.4 6810189 10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.3 5337014 10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.2 4547817 10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.5 4505133 10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.4 4163362 10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.3 3761843 10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.8 4547809 9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.7 4163445 9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.6 3948480 9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.5 3501955 ORACLE 9I DATABASE SERVER RELEASE 2 - PATCH SET 4 VERSION 9.2.0.5.0
9.2.0.4 3095277 9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.3 2761332 9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.2 2632931 9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
9.0.1.5 3301544 9.0.1.5 PATCHSET
9.0.1.4 2517300 9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER
9.0.1.3 2271678 9.0.1.3. PATCH SET FOR ORACLE DATA SERVER
8.1.7.4 2376472 8.1.7.4 PATCH SET FOR ORACLE DATA SERVER
8.1.7.3 2189751 8.1.7.3 PATCH SET FOR ORACLE DATA SERVER
8.1.7.2 1909158 8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER
提示：
[*] 10.2.0.4 和 10.2.0.5目前是extended support阶段，2011年8月1日之后发布的PSU需要ES License下载。
[**] 只在有限的平台提供
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28211342/viewspace-2131053/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
