您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫相關知識

Oracle 資料庫補丁查詢指南：v$version 與 OPatch lsinventory 雙重驗證

編輯：Oracle 資料庫相關知識

確認 Oracle 資料庫當前版本與已安裝補丁，是升級評估、安全合規、問題排查的基礎步驟。本文提供兩大標準查詢方式：SQL 層面的 V$VERSION 與 OS 層面的 OPatch lsinventory，並附上真實輸出範例解析。

一、SQL 查詢：V$VERSION (需資料庫啟動)

SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0       Production
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

快速確認基礎版本 (11.1.0.7.0)、平台 (AIX 64bit)、組件版本一致性
適用於資料庫已啟動、可連線的場景

二、OS 命令：OPatch lsinventory (資料庫可關閉)

[oraprod@db01]$ $ORACLE_HOME/OPatch/opatch lsinventory

Oracle Interim Patch Installer version 11.1.0.6.2
Oracle Home       : /u01/PRODDB/db
Central Inventory : /u01/oraInventory
OPatch version    : 11.1.0.6.2
OUI version       : 11.1.0.7.0

Installed Top-level Products (2):
Oracle Database 11g                   11.1.0.6.0
Oracle Database 11g Patch Set 1       11.1.0.7.0

Interim patches (10):
Patch 7211965 : applied on Fri Dec 11 00:30:57 CST 2009  (Bug 7211965)
Patch 7253531 : applied on Fri Dec 11 00:28:34 CST 2009  (Bug 7253531)
Patch 8639653 : applied on Fri Dec 11 00:18:33 CST 2009  (Bug 8639653)
Patch 8409203 : applied on Fri Dec 11 00:16:08 CST 2009  (Bugs 8409203, 8249087, 7281382)
Patch 8338798 : applied on Fri Dec 11 00:13:22 CST 2009  (Bugs 7327166, 8338798, 6815733, 8303523)
Patch 8834636 : applied on Fri Dec 11 00:10:52 CST 2009  (Bug 8834636)
Patch 8447623 : applied on Fri Dec 11 00:07:23 CST 2009  (Bug 8447623)
Patch 8738008 : applied on Thu Dec 10 23:53:42 CST 2009  (Bug 8738008)
Patch 7486407 : applied on Thu Dec 10 23:30:20 CST 2009  (Bug 7486407)
Patch 8833297 : applied on Thu Dec 10 22:35:06 CST 2009  (Bugs 6870937, 7627743, 7652888, 7299153, 8242410, 6059178, 8563946, 6955744, 7497788, 6840740, 8244217, 8702276, 6981690, 8450529, 7432556, 7719143, 7523787, 8251486, 8367827, 7613481, 8341623, 7515145, 7348847, 8416414, 8250643, 8284633, 8230457, 8563948, 6900214, 7044551, 7318049, 8940197, 7013124, 7432514, 7393258, 7553884, 7639121, 8563944, 8860821, 7606362, 7426959, 7330434, 7708340, 7352414, 6452375, 7356443, 7341598, 8213302, 7196532, 7446163, 8409848, 8236851, 8342506, 7593835, 7340448, 7309458, 8290478, 8391256, 7462112, 7013817, 8499600, 7411865, 7331867, 7527650, 6977167, 8855565, 6501490, 6598432, 7524944, 7706138, 6941717, 8402555, 7494333, 7586451, 8402551, 7499353, 8408887, 7496908, 7511040, 7719148, 7311601, 7497640, 7373196, 7424804, 7452373, 7597354, 6882739, 7366290, 6851669, 8543737, 7318276, 8284438, 8324760, 7420394, 7475055, 8563942, 7122161, 8361398, 7705669, 7676737, 8301559, 8224083, 8658581, 8211920, 8462173, 7206858, 8563945, 8855553, 8402637, 8257122, 8199266, 7454752, 7516536, 7345904, 8352304, 7416901, 7426336, 8352309, 6812439, 7219752, 8534338, 8542307, 8413059, 7572069, 7436280, 7432601, 7311909, 7506785, 7460818, 7276960, 8855559, 7013835, 7378322, 8402548, 7189645, 8563947, 8306933, 7477246, 7263842, 7480809, 8855575, 8433270, 7556778, 8836375, 7330611, 8339352, 7225720, 8563943, 7036453, 7628387, 8419383, 6970731, 7719668, 7203349, 8402562, 7680907, 7438445, 8855577, 8243648, 7630416, 6851110, 6980597, 6618461, 7357609, 8855570, 8369094, 8318050, 8306934, 7434194, 8833297, 7486595, 7716219, 8362693, 6599920, 7628866, 7183523, 7412296, 7135702, 7720494, 7436152, 7175513, 7602341, 6679303, 8339404, 7393804, 8856696, 7650993, 6980601, 7830065, 7462709, 8563941, 8226397, 7515779)

三、關鍵資訊解讀

項目	含義	範例值
Top-level Products	基礎版本 + Patch Set	11.1.0.6.0 + 11.1.0.7.0 (PSU)
Interim patches	一次性補丁 (CPU/PSU/單一修復)	10 個補丁，含 Bug 編號清單
Patch 8833297	大型合併補丁 (典型 PSU)	修復 100+ Bug，含 8833297 自身
Applied date	補丁實際套用時間	2009-12-10/11

四、常見查詢場景

安全稽核：對照 Oracle Critical Patch Update (CPU) 公告，確認是否套用最新安全補丁
升級評估：確認當前 Patch Set 版本，規劃升級路徑 (如 11.1.0.7 → 11.2.0.4 → 19c)
問題排查：MOS 文檔建議套用特定 Patch (如 8833297)，驗證是否已安裝
RAC 環境：每個節點均需執行，確保叢集一致性

五、進階：查詢特定 Bug 是否已修復

-- 方法 1: OPatch 輸出 grep
$ opatch lsinventory | grep -i 8833297

-- 方法 2: SQL 查詢 (需資料庫開啟)
SELECT patch_id, bug_id FROM dba_registry_sqlpatch WHERE bug_id = 8833297;

-- 方法 3: XML 輸出解析 (自動化腳本)
$ opatch lsinventory -xml -invPtrLoc /u01/oraInventory/oraInst.loc

雙重驗證機制：V$VERSION 確認運行時版本，OPatch lsinventory 確認安裝介質層面的補丁清單，兩者一致才代表環境狀態清晰可控。