Oracle database 補丁知識介紹
oracle path和大家傳統理解的補丁的含義都是差不多的，可以快速解決oracle 本身的bug，我們都具備這個宏觀概念，但是Oracle DBA要清楚 oracle 是如何來細分補丁的型別和定義我們還是檢視oracle 官方文件這個不會有錯誤，提醒大家百度出來的是非官方說法，很容易誤導大眾，建議大家多看官方文件，ok，廢話不多說我們開始定位
看到官方文件提示我們確認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]
上面提到的所有補丁都會有一個補丁號，如果已經知道了補丁號，就可以直接使用下面的連結來下載這個補丁：
補丁號.html
輸入MOS的賬號、密碼就可以。
比如：11.2.0.3.0 PATCH SET 下載連結：
作業系統和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]
上面提到的所有補丁都會有一個補丁號，如果已經知道了補丁號，就可以直接使用下面的連結來下載這個補丁：
補丁號.html
輸入MOS的賬號、密碼就可以。
比如：11.2.0.3.0 PATCH SET 下載連結：
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部落格 ” ，連結：http://blog.itpub.net/28211342/viewspace-2131053/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle補丁介紹一Oracle
- 【補丁】Oracle補丁的知識及術語Oracle
- Oracle Proactive補丁集介紹Oracle
- Oracle補丁術語介紹Oracle
- Oracle補丁術語介紹 PSU CPU補丁Oracle
- Oracle 補丁體系 及 opatch 工具 介紹Oracle
- Oracle Database常用補丁集Patch號OracleDatabase
- Oracle介質補丁號Oracle
- Oracle CRS Database安裝10.2.0.5.0補丁OracleDatabase
- oracle 補丁Oracle
- 【opatch】Oracle打補丁工具opatch簡介Oracle
- 關於Oracle Database Vault介紹OracleDatabase
- Oracle的補丁Oracle
- Oracle Database常用補丁集Patch號及各版本PSUOracleDatabase
- Epub基礎知識介紹
- 安卓App熱補丁動態修復技術介紹安卓APP
- Oracle Database 10g/11g補丁(Patchset)下載地址OracleDatabase
- Oracle RAC更新補丁Oracle
- Oracle 安裝補丁Oracle
- 12. Oracle版本、補丁及升級——12.2. 補丁及補丁集Oracle
- Oracle補丁集的補丁號Patch ID/Number速查Oracle
- 應用oracle flashback--Flashback Database介紹OracleDatabase
- oracle安裝介質及補丁集下載地址Oracle
- 資料庫安全知識介紹資料庫
- shiro入門知識介紹
- JDE 的掃盲知識介紹。。。
- oracle最新補丁查詢Oracle
- oracle打補丁回顧Oracle
- Oracle的OPatch補丁更新Oracle
- Oracle補丁集查詢Oracle
- oracle 補丁號查詢Oracle
- Oracle EBS中打補丁Oracle
- 關於Oracle Database PSU/SPU/Bundle Patch的補丁號的變化OracleDatabase
- 資料庫基礎知識介紹！資料庫
- 區塊鏈入門知識介紹！區塊鏈
- [推薦]WebService開發知識介紹Web
- ORL Faces Database介紹Database
- metalink上oracle資料庫推薦補丁列表的document id（Oracle Recommended Patches -- Oracle Database）Oracle資料庫Database