Chúng ta có thể sử dụng tiện ích Oracle OPatch để kiểm tra Oracle Patches được apply trong Oracle database. opatch utility trong $ORACLE_HOME/OPatch directory. Có thể download các patch từ Oracle Support. Sau khi download patch hãy đọc hướng dẫn cài đặt patch. OPatch sẽ liệt kê các bug sẽ được fix trong quá trình chúng ta patch. Trong trường hợp xung đột khi đã apply patch cần rollback lại ban đầu.

Kiểm tra patch đã đưuơcj apply cho Database

1. Liệt kê các patch đã apply

[oracle@dbaviet admin]$ $ORACLE_HOME/OPatch/opatch lspatches
30503372;OJVM PATCH SET UPDATE 11.2.0.4.200114
29938455;OCW Patch Set Update : 11.2.0.4.191015 (29938455)
30310975;DATABASE PATCH FOR EXADATA (Jan 2020 - 11.2.0.4.200114) : (30310975)

2. opatch lsinventory

 [oracle@dbaviet  ~]$  $ORACLE_HOME/OPatch/opatch lsinventory|grep "Patch description"
 Patch description:  "Database PSU 12.1.0.2.190716, Oracle JavaVM Component (JUL2019)"
 Patch description:  "Database Patch Set Update : 12.1.0.2.190716 (29494060)"

3. Lấy chi tiết thông tin opatch lsinventory

[oracle@dbaviet ~]$ $ORACLE_HOME/OPatch/opatch lsinventory

Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2019, Oracle Corporation. All rights reserved.
Oracle Home : /scratch/u01/app/oracle/product/12.1.0/dbhome_1
Central Inventory : /scratch/u01/app/oraInventory
from : /scratch/u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.17
OUI version : 12.1.0.2.0
Log file location : /scratch/u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2019-10-12_11-28-58AM_1.log
Lsinventory Output file location : /scratch/u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2019-10-12_11-28-58AM.txt

Local Machine Information::
Hostname: orahowdb
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 12c 12.1.0.2.0
There are 1 products installed in this Oracle Home.
Interim patches (2) :
Patch 29774383 : applied on Wed Sep 04 03:44:12 PDT 2019
Unique Patch ID: 22961858
Patch description: "Database PSU 12.1.0.2.190716, Oracle JavaVM Component (JUL2019)"
Created on 7 Jun 2019, 10:02:47 hrs PST8PDT
Bugs fixed:
29774383, 29774367, 29251241, 29254615, 19165673, 28790654, 28915933
28440711, 28502128, 28502113, 27923320, 27952584, 27952577, 27642235
27475603, 27461789, 27461842, 25649873, 27001733, 27000663, 27000690
26635845, 26637592, 26570171, 26027162, 26023002, 26023025, 25437695
25494413, 25494379, 24917972, 25067795, 24534298, 25076732, 25076756
24315824, 21659726, 24448240, 24448282, 23177536, 22675136, 23265914
23265965, 23727148, 22674709, 22670413, 22670385, 21188537, 22139226
22118835, 22118851, 21555660, 21811517, 19623450, 21566993, 21566944
19176885, 21068507, 21047803, 21047766, 20415564, 20408829, 20408866
19877336, 19855285, 19909862, 19895362, 19895326, 19153980, 19231857
19223010, 19245191, 19699946

Patch 29494060 : applied on Wed Sep 04 02:28:08 PDT 2019
Unique Patch ID: 22993235
Patch description: "Database Patch Set Update : 12.1.0.2.190716 (29494060)"
Created on 28 Jun 2019, 07:47:40 hrs PST8PDT

Bugs fixed:
19309466, 19902195, 18250893, 25437699, 19383839, 19781326, 16756406
18456643, 26546664, 22364044, 18845653, 19915271, 20172151, 18417036
19516448, 23713236, 24907917, 24796092, 23140259, 19243521, 19658708
18272672, 21153266, 19174430, 22243719, 19548064, 26556014, 20493163
20688221, 21387964, 13542050, 22250006, 22734547, 22243983, 21623164
19012119, 19932634, 19869255, 22232606, 18681056, 23324000, 25427662
22068305, 24589081, 19439759, 19303936, 22916353, 24835538, 22353346
19790243, 21106027, 26444887, 23088803, 22529728, 26256131, 19134173
24303148, 20447445, 21101873, 21188584, 19390567, 26513709, 25780343
19769480, 21097043, 21225209, 26245237, 20677396, 19284031, 19450314
19016730, 22517782, 20919320, 22205263, 22075064, 22551446, 22721409
18440095, 22496904, 16439813, 18354830, 20596234, 22022760, 20936905
23197103, 22515353, 21514877, 19809171, 21186167, 26111842, 18990023
25654936, 21492036, 18705806, 28420042, 19578247, 22024071, 22238921
22809871, 21184223, 19995869, 23089357, 19404068, 18921743, 19065677
19018447, 19018206, 18308268, 19777862, 29027694, 22223463, 19304354
22519146, 19445860, 26654363, 27199245, 22977256, 20890311, 27445727
21142837, 20869721, 24555417, 22179537, 21756699, 20217801, 18819908
22760595, 25483815, 23628685, 23007241, 19593445, 21080143, 27351628
20582405, 24966594, 20031873, 25489367, 18618122, 24737581, 21698350
26784509, 24739928, 18966843, 19077215, 20704450, 19068970, 20543011
19023822, 24713381, 20432873, 21756677, 22836801, 20328248, 18674047
18849537, 20087383, 25459958, 20315311, 22897344, 27534509, 25178179
19308965, 18948177, 19468991, 20868862, 21780146, 23315153, 20466628
21756661, 20397490, 19706965, 20302006, 24831514, 23240358, 22178855
19032777, 20862087, 19329654, 18974476, 20603378, 20859910, 19307662
26203182, 21847223, 20281121, 22568797, 19075256, 19076343, 18866977
28026866, 29511611, 22808310, 25635149, 20844426, 20904530, 20441797
21442094, 25079710, 24674955, 18840932, 18740837, 20294666, 25602488
21517440, 22062517, 19180394, 27337759, 19174942, 20671094, 21889720
19450116, 18411216, 20117253, 24386767, 20641666, 19931367, 25264559
19930276, 22092979, 25616268, 21625179, 20879709, 23003979, 20165574
28578164, 19272708, 19547370, 22624709, 23084507, 23184263, 20228093
21281532, 25093872, 19805359, 26324206, 19461270, 19434529, 18799063
20354900, 20378086, 29388020, 17008068, 21246723, 20831538, 20424899
24929210, 24560906, 20144308, 21620471, 19670108, 19068610, 20267166
25123585, 20476175, 18549238, 19297917, 22950945, 19385656, 23528412
19684504, 19330795, 21174504, 28357401, 20899461, 20557786, 21911701
19143550, 20118035, 19024808, 25760195, 20009833, 19604659, 16359751
26039623, 22820579, 19928926, 23314180, 20212067, 24737403, 20480209
18904062, 29030780, 26430737, 20856766, 17258582, 27169796, 21668627
20877664, 23272045, 19487147, 23149541, 24577566, 19430401, 19676905
20925795, 26482376, 21296029, 21629064, 23229229, 22865673, 20708701
25353983, 19280225, 21315084, 20613079, 19375649, 19213447, 19989009
18191823, 27314390, 26336977, 25775213, 24393981, 25639019, 17319928
14705949, 19703301, 28390273, 21626377, 20122715, 6418158, 23105538
26198926, 19258504, 21188532, 23151677, 24792678, 17890099, 21649497
26446098, 16887946, 26024732, 18791688, 19721304, 27012701, 19490948
19619732, 21164318, 23148260, 18090142, 21641760, 19818513, 23002524
20139391, 24693382, 19978542, 23543183, 22165897, 19373893, 22359063
19409212, 18373438, 23035249, 20677974, 18990693, 20470877, 21422580
21632821, 22351572, 20235511, 23220453, 18742258, 18604493, 23008056
22901797, 18610915, 20978259, 20832516, 24801152, 26089440, 20907061
19523462, 20505778, 19183343, 21787056, 21273804, 22782647, 25093739
17835294, 28708023, 24413809, 27846298, 18371441, 26714910, 24385983
20413820, 24421668, 28986231, 25897615, 25643931, 23195445, 21281607
20513399, 20558005, 20093776, 18909599, 20618595, 23572982, 19211433
20331945, 19512341, 22256431, 19637186, 19022470, 18607546, 26875822
24573817, 23115139, 19649152, 19201867, 21294938, 20898997, 18510194
21842740, 22454326, 24683149, 19534363, 25489607, 23061702

OPatch succeeded.

3: Chi tiết thông tin patch đã apply

[oracle@dbaviet ~]$ $ORACLE_HOME/OPatch/opatch lsinventory -details

4: Dùn sys As sysdba, bạn thực hiện lệnh sau dể lấy thông tin chi tiết patch

SQL> set serverout on;
 SQL> exec dbms_qopatch.get_sqlpatch_status;
 Patch Id : 27338041
         Action : APPLY
         Action Time : 18-APR-2018 15:48:43
         Description : DATABASE PATCH SET UPDATE 12.1.0.2.180417
         Logfile :
 /scratch/u01/app/oracle/cfgtoollogs/sqlpatch/27338041/22036385/27338041_apply_D0
 2NAQCD_CDBROOT_2018Apr18_15_46_27.log
         Status : SUCCESS
 Patch Id : 28259833
         Action : APPLY
         Action Time : 24-OCT-2018 03:29:23
         Description : DATABASE PATCH SET UPDATE 12.1.0.2.181016
         Logfile :
 /scratch/u01/app/oracle/cfgtoollogs/sqlpatch/28259833/22488632/28259833_apply_D0
 2NAQCD_CDBROOT_2018Oct24_03_28_14.log
         Status : SUCCESS
 Patch Id : 28440711
         Action : APPLY
         Action Time : 24-OCT-2018 03:38:12
         Description : Database PSU 12.1.0.2.181016, Oracle JavaVM Component (OCT2018)
         Logfile :
 /scratch/u01/app/oracle/cfgtoollogs/sqlpatch/28440711/22440644/28440711_apply_D0
 2NAQCD_CDBROOT_2018Oct24_03_34_30.log
         Status : SUCCESS
 Patch Id : 28440711
         Action : ROLLBACK
         Action Time : 05-MAR-2019 03:12:23
         Description : Database PSU 12.1.0.2.181016, Oracle JavaVM Component (OCT2018)
         Logfile :
 /scratch/u01/app/oracle/cfgtoollogs/sqlpatch/28440711/22440644/28440711_rollback
 _D02NAQCD_CDBROOT_2019Mar05_03_09_19.log
         Status : SUCCESS
 Patch Id : 28790654
         Action : APPLY
         Action Time : 05-MAR-2019 03:12:23
         Description : Database PSU 12.1.0.2.190115, Oracle JavaVM Component (JAN2019)
         Logfile :
 /scratch/u01/app/oracle/cfgtoollogs/sqlpatch/28790654/22620251/28790654_apply_D0
 2NAQCD_CDBROOT_2019Mar05_03_10_18.log
         Status : SUCCESS
 Patch Id : 28729169
         Action : APPLY
         Action Time : 05-MAR-2019 03:12:23
         Description : DATABASE PATCH SET UPDATE 12.1.0.2.190115
         Logfile :
 /scratch/u01/app/oracle/cfgtoollogs/sqlpatch/28729169/22626239/28729169_apply_D0
 2NAQCD_CDBROOT_2019Mar05_03_10_18.log
         Status : SUCCESS
 Patch Id : 28790654
         Action : ROLLBACK
         Action Time : 30-APR-2019 04:50:11
         Description : Database PSU 12.1.0.2.190115, Oracle JavaVM Component (JAN2019)
         Logfile :
 /scratch/u01/app/oracle/cfgtoollogs/sqlpatch/28790654/22620251/28790654_rollback
 _D02NAQCD_CDBROOT_2019Apr30_04_44_47.log
         Status : SUCCESS
 Patch Id : 29251241
         Action : APPLY
         Action Time : 30-APR-2019 04:50:11
         Description : Database PSU 12.1.0.2.190416, Oracle JavaVM Component (APR2019)
         Logfile :
 /scratch/u01/app/oracle/cfgtoollogs/sqlpatch/29251241/22839506/29251241_apply_D0
 2NAQCD_CDBROOT_2019Apr30_04_46_08.log
         Status : SUCCESS
 Patch Id : 29141015
         Action : APPLY
         Action Time : 30-APR-2019 04:50:11
         Description : DATABASE PATCH SET UPDATE 12.1.0.2.190416
         Logfile :
 /scratch/u01/app/oracle/cfgtoollogs/sqlpatch/29141015/22785785/29141015_apply_D0
 2NAQCD_CDBROOT_2019Apr30_04_46_08.log
         Status : SUCCESS
 Patch Id : 29251241
         Action : ROLLBACK
         Action Time : 04-SEP-2019 04:09:43
         Description : Database PSU 12.1.0.2.190416, Oracle JavaVM Component (APR2019)
         Logfile :
 /scratch/u01/app/oracle/cfgtoollogs/sqlpatch/29251241/22839506/29251241_rollback
 _D02NAQCD_CDBROOT_2019Sep04_04_06_10.log
         Status : SUCCESS
 Patch Id : 29774383
         Action : APPLY
         Action Time : 04-SEP-2019 04:09:43
         Description : Database PSU 12.1.0.2.190716, Oracle JavaVM Component (JUL2019)
         Logfile :
 /scratch/u01/app/oracle/cfgtoollogs/sqlpatch/29774383/22961858/29774383_apply_D0
 2NAQCD_CDBROOT_2019Sep04_04_07_20.log
         Status : SUCCESS
 Patch Id : 29494060
         Action : APPLY
         Action Time : 04-SEP-2019 04:09:43
         Description : DATABASE PATCH SET UPDATE 12.1.0.2.190716
         Logfile :
 /scratch/u01/app/oracle/cfgtoollogs/sqlpatch/29494060/22993235/29494060_apply_D0
 2NAQCD_CDBROOT_2019Sep04_04_07_20.log
         Status : SUCCESS
 PL/SQL procedure successfully completed.

5: Để lấy thông tin chi tiết patch ID.

SQL> select xmltransform(dbms_qopatch.is_patch_installed('29494060'),dbms_qopatch.get_opatch_xslt) "Patch installed?" from dual;
 Patch installed?
 Patch Information:
          29494060:   applied on 2019-09-04T02:28:08-07:00

6: Chi tiết Patch detail từ registry

SQL>  select * from sys.registry$history;
 SQL> select * from sys.dba_registry_sqlpatch;

=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,khóa học pl/sql, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, middleware, hoc solaris, hoc linux, hoc aix, unix, securecrt, xshell, mobaxterm, putty

Thứ Ba, 19 tháng 4, 2022

Hướng dẫn kiểm tra Patches được Apply trong Oracle Database

Kiểm tra patch đã đưuơcj apply cho Database

ĐỌC NHIỀU