Patch 30886680: DATABASE APR 2020 RELEASE UPDATE 12.2.0.1.200414 [Linux X86-64]

Below document I will explain how to apply APRIL 2020 PSU Patch

Overall steps:

Export exact oracle home path
Check the opatch version – Oracle recommends to use the latest Patch version
Apply optach
Datapatch verbose need to do
Oracle Patch Download link: https://support.oracle.com/epmos/faces/PatchDetail?requestId=23474251&_afrLoop=108504923966006&patchId=30886680&_afrWindowMode=0&_adf.ctrl-state=4te1a33nq_119
Here we are going to check Opatch lsinventory:

[oracle@oracleagent 30886680]$ opatch lsinventory|grep "Patch description"
Patch description: "Database Apr 2021 Release Update : 12.2.0.1.200414 (30886680)"

[oracle@oracleagent 30886680]$ opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.19
Copyright (c) 2021, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc
OPatch version : 12.2.0.1.19
OUI version : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-18_10-27-13AM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/lsinv/lsinventory2021-01-18_10-27-13AM.txt

Local Machine Information::
Hostname: oracleagent.oracledb
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 12c 12.2.0.1.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch 30886680 : applied on Mon Jan 18 10:21:48 IST 2021
Unique Patch ID: 23474251
Patch description: "Database Apr 2021 Release Update : 12.2.0.1.200414 (30886680)"
Created on 1 Apr 2021, 01:36:23 hrs PST8PDT
Bugs fixed:
30533198, 30312568, 30086166, 28951106, 28435910, 8480838, 8932139
12763598, 13554903, 14221306, 14690846, 15931756, 16010385, 16438495
16727454, 16942578, 17107695, 17533661, 17947871, 18308268, 18521691
18594510, 18774543, 18878420, 19072655, 19211433, 19285105, 19327292
19526548, 19614183, 19647894, 19649997, 19710201, 19721304, 20003668
20087519, 20118035, 20121036, 20318049, 20436508, 20532077, 20549013
20588486, 20591151, 20617383, 20620169, 20736227, 20756305, 20866970
20176443, 21089435, 20215391, 21143725, 21147908, 21159907, 21178363
21186167, 21197018, 21216226, 21320338, 21433452, 21479706, 21517767
21521066, 21547051, 21638600, 21744603, 21788462, 21837606, 21882528
21935698, 21981529, 21985256, 22007318, 22070473, 22070853, 22072543
22087683, 22104866, 22107360, 22174392, 22179537, 22282748, 22310426
22347493, 22363790, 22364044, 22367053, 22379010, 21846455, 21854940
21895673, 22503283, 22503297, 22504793, 22522515, 22530186, 22564336
22568728, 22581771, 22594071, 22599050, 22628825, 22645001, 22645496
22654475, 22700845, 22726044, 22729345, 22826067, 22843979, 22845846
22864303, 22898198, 22921674, 22939829, 22950145, 22970869, 22981722
23018676, 23019710, 23106585, 23035189, 23055900, 23061453, 23065010
23066146, 23080557, 23104033, 23105538, 23101325, 23110523, 23125560
23126545, 23127945, 23143074, 23151677, 23168363, 23169712, 23177923
23179662, 23184263, 23197730, 23234232, 23237011, 23189829, 23271203
23278750, 23300142, 23306590, 23308065, 23310101, 23312077, 23328639
23333567, 23336559, 23342170, 23481673, 23491861, 23499004, 23499160
23521523, 23527363, 23533647, 23548817, 23567857, 23572982, 23581777
23588722, 23599216, 23600861, 23610213, 23645516, 23665623, 23701062
23715460, 23715518, 23730161, 23733981, 23735292, 23738304, 23741944
23743596, 23746128, 23749454, 23761718, 18006569, 18010030, 18289874
18289895, 18294174, 18303148, 18307571, 18308349, 18326444, 18326846
18328857, 18330708, 18332831, 18334708, 18336189, 18337882, 18341675
18343905, 18345420, 18346821, 18348685, 18350620, 18352981, 18355111
18357348, 18368004, 18371491, 18373528, 18373756, 18374976, 18376875
18376878, 18383086, 18385983, 18401351, 18403922, 18401977, 18415926
18416451, 18421668, 18423416, 18425056, 18425998, 18435982, 18437162
18440648, 18443539, 18457597, 18460392, 18461826, 18467122, 18468470
18470606, 18471079, 18471473, 18473736, 18485034, 18485161, 18485174
18486059, 18486237, 18501056, 18516314, 18530364, 18534401, 18554533
18555417, 18556862, 18556967, 18560106, 18563422, 18571014, 18570598
18573817, 18578718, 18578797, 18588377, 18589081, 18589590, 18591506
18593740, 18595699, 18600330, 18601592, 18601996, 18611527, 18616637
18617969, 18623975, 18618166, 18641895, 18654629, 18655717, 18664211
18668398, 18669189, 18674197, 18674955, 18676172, 18677696, 18680159
18687075, 18689376, 18692973, 18693010, 18693290, 18697323, 18699619
18710696, 18713381, 18714016, 18717183, 18717859, 18718260, 18719799
18735430, 18737064, 18737403, 18737581, 18737954, 18739791, 18744383
18744686, 18752618, 18757934, 18759556, 18760407, 18764085, 18766301
18784414, 18786669, 18791883, 18792678, 18793511, 18796012, 18797119
18800423, 18801152, 18810934, 18808504, 18811725, 18812047, 18818566
18827228, 18827654, 18831514, 18835919, 18841671, 18843188, 18844549
18844841, 18845157, 18848746, 18848923, 18850622, 18907917, 18908321
18911701, 18912588, 18920582, 18921478, 18922704, 18923080, 18923215
18923338, 18923790, 18918667, 18926999, 18929210, 18938784, 18940060
18942749, 18953434, 18957555, 18960044, 18960801, 18965426, 18966594
18966788, 18967993, 18968162, 18976007, 18978100, 25102574, 25107852
25108996, 25109102, 25109423, 25031510, 25032818, 25034396, 25036006
25036474, 25042823, 25044977, 25045228, 25050160, 25051465, 25051628
25054064, 25057811, 25058080, 25060506, 25062592, 25063971, 25065563
25072986, 25077278, 25078611, 25086233, 25087436, 25012777, 25013872
25015982, 25018160, 25019339, 25019497, 25019758, 25100063, 25100579
25103996, 25107662, 25111033, 25114561, 25121084, 25120668, 25120742
25121089, 25123585, 25118363, 25129925, 25130312, 25140197, 25145163
25145215, 25150125, 25159176, 25162645, 25164293, 25166187, 25171041
25171084, 25175723, 25176408, 25178032, 25178101, 25178179, 25179774
25182817, 25184453, 25184555, 25186079, 25191872, 25192044, 25192528
25192729, 25195901, 25199585, 25201454, 25210355, 25203656, 25205954
25206864, 25207410, 25201912, 25211068, 25210499, 25210690, 25211628
25219450, 25223839, 25218182, 25225795, 25226665, 25227381, 25230870
25230145, 25237577, 25180188, 25180590, 25181448, 25181625, 25184807
25188384, 25250101, 25251648, 25257085, 25259611, 25262869, 25263960
25265499, 25269133, 25283790, 25287072, 25293659, 25296876, 25299227
25299807, 25300427, 25303284, 25303756, 25305405, 25307368, 25301116
25313154, 25313411, 25316758, 25317989, 25320555, 25323525, 25328013
25328518, 25329664, 25335189, 25335360, 25335790, 25337332, 25337640
25348956, 25353983, 25356118, 25357142, 25360661, 25362958, 25367588
25367721, 25382812, 25383204, 25384462, 25386748, 25388896, 25392535
25393714, 25395696, 25397936, 25398306, 25404210, 25405100, 25405687
25405813, 25410017, 25410180, 25410810, 25410877, 25411036, 25415713
25416731, 25417050, 25417056, 25417958, 25425005, 25425451, 25425760
25427662, 25429959, 25430120, 25433696, 25435038, 25437699, 25440818
25442559, 25444961, 25445168, 25451531, 25455795, 25457401, 25459958
25462714, 25463844, 25472112, 25472885, 25476125, 25476149, 25477657
25478885, 25479164, 25481087, 25482971, 25486384, 25489342, 25489367
25489607, 25492379, 25498930, 25498994, 25516250, 25518955, 25528838
25530080, 25530814, 25535668, 25536819, 25537470, 25539063, 25540738
25546580, 25546608, 25547901, 25551676, 25553616, 25554787, 25555252
25557886, 25558986, 25560487, 25561296, 25569149, 25570129, 25573623
25575348, 25575369, 25575628, 25579458, 25579761, 25591394, 25594901
25597525, 25598473, 25599425, 25600342, 25600421, 25601999, 25601888
25603923, 25606011, 25607726, 25612015, 25614866, 25616268, 25616359
25616417, 25616645, 25631933, 25633101, 25634317, 25634348, 25635149
25638456, 25639019, 25643818, 25643889, 25643931, 25646373, 25647325
25648731, 25653101, 25654459, 25654936, 25655390, 25655966, 25659655
25660847, 25661819, 25662088, 25662021, 25662518, 25663488, 25667973
25669791, 25670786, 25671354, 25672640, 25674386, 25681021, 25685152
25686739, 25687460, 25691904, 25694206, 25695903, 25696520, 25699321
25700654, 25701368, 25710420, 25715167, 25717371, 25722055, 25722608
25722720, 25723017, 25723158, 25728085, 25729507, 25730014, 25734963
25736747, 25739065, 25740844, 25741955, 25743479, 25747569, 25749273
25752755, 25754606, 25756945, 25757697, 25757748, 25760195, 25762221
25764100, 25766822, 25768681, 25772669, 25774077, 25775213, 25775444
25780343, 25783447, 25784010, 25785331, 25785441, 25788879, 25789041
25789277, 25789579, 25790353, 25792911, 25795865, 25797012, 25797118
25797305, 25800464, 25810510, 25803364, 25803545, 25807997, 25801518
25811063, 25810704, 25811650, 25812390, 25813931, 25818707, 25821810
25823532, 25823754, 25825910, 25826740, 25830492, 25832935, 25834581
25835365, 25838361, 25838755, 25852885, 25856821, 25858672, 25861398
25865785, 25870579, 25871177, 25871639, 25871753, 25872127, 25872389
25873336, 25874050, 25874678, 25882264, 25883438, 25885148, 25888073
25888984, 25890056, 25890673, 25890782, 25894239, 25895218, 25897615
25898228, 25904273, 25904490, 25905130, 25906117, 25906886, 25908728
25911718, 25914276, 25919622, 25932518, 25932728, 25933494, 25941836
25943271, 25945130, 25947799, 25951571, 25953857, 25954102, 25954054
25957038, 25963018, 25964954, 25967544, 25967985, 25970731, 25971286
25971817, 25973152, 25975723, 25977310, 25980605, 25980770, 25981498
25982666, 25986062, 25990107, 25995938, 25997810, 26006257, 26007010
26019148, 26018732, 26018784, 26105681, 26109075, 26109777, 26109780
26032573, 26034119, 26036748, 26037215, 26038086, 26039623, 26040483
26045732, 26051656, 26078437, 26078493, 26080410, 26083298, 26087754
26088426, 26088836, 26010767, 26010893, 26011640, 26011786, 26015327
26015405, 26016382, 26108080, 26108337, 26111059, 26110632, 26111842
26112621, 26115103, 26121990, 26118078, 26130486, 26137367, 26137416
26138085, 26145560, 26149904, 26153372, 26153977, 26168933, 26169341
26169345, 26170659, 26170715, 26176010, 26187943, 26189861, 26198757
26198926, 26201113, 26203182, 26223039, 26237338, 26237431, 26237773
26238195, 26182031, 26182677, 26183698, 26184115, 26185237, 26188143
26189718, 26256131, 26257953, 26259265, 26261327, 26263328, 26263721
26268756, 26269790, 26271001, 26274660, 26275103, 26275415, 26277439
26281476, 26285062, 26285933, 26308650, 26301047, 26317991, 26318627
26323308, 26318206, 26318769, 26325856, 26327418, 26327618, 26327775
26330194, 26331743, 26333141, 26336977, 26338953, 26351334, 26351996
26353617, 26358670, 26359011, 26362155, 26362821, 26366517, 26367012
26367460, 26371725, 26373967, 26374791, 26375052, 26375250, 26380017
26385189, 26388538, 26396790, 26398675, 26399626, 26399691, 26405036
26406387, 26407408, 26410180, 26412540, 26418088, 26420561, 26421667
26422277, 26423085, 26426526, 26426967, 26430323, 26430737, 26434436
26434999, 26435073, 26436168, 26438612, 26439748, 26440169, 26440749
26442308, 26444601, 26444887, 26446018, 26452606, 26474662, 26474703
26475419, 26476184, 26478970, 26479173, 26482376, 26486365, 26492866
26493289, 26498354, 26513067, 26513701, 26521043, 26521839, 26523432
26526726, 26526799, 26536320, 26537307, 26542135, 26542236, 26542835
26544823, 26545688, 26546070, 26546664, 26546754, 26548363, 26556014
26558437, 26569225, 26575788, 26580633, 26581860, 26584641, 26588069
26597140, 26599395, 26608137, 26608238, 26601942, 26615291, 26615690
26617804, 26623652, 26626879, 26629381, 26633355, 26633558, 26635897
26637273, 26637818, 26639167, 26641610, 26651026, 26650540, 26654363
26658759, 26659182, 26669550, 26680105, 26712331, 26714486, 26714910
26717528, 26718511, 26725687, 26727397, 26729494, 26729611, 26740700
26744595, 26745010, 26751106, 26751171, 26755171, 26758193, 26764561
26765212, 26768105, 26775610, 26784501, 26790123, 26794786, 26797591
26798411, 26798516, 26810503, 26816582, 26820076, 26822314, 26822620
26818833, 26828994, 26829845, 26833932, 26837569, 26837710, 26840654
26844406, 26844870, 26849779, 26871815, 26875822, 26883456, 26895149
26896659, 26898563, 26907236, 26907327, 26908788, 26901100, 26901504
26910716, 26911000, 26939314, 26943004, 26944190, 26958896, 26963310
26966616, 26966916, 26967713, 26968670, 26969321, 26970175, 26970717
26981910, 26983259, 26985010, 26986173, 26992964, 27000158, 27006120
27006664, 27001164, 27013146, 27108251, 27032785, 27033520, 27033652
27034890, 27036163, 27037839, 27038986, 27039712, 27044169, 27044297
27045634, 27052607, 27056711, 27058530, 27060167, 27060859, 27061736
27072923, 27073314, 27079140, 27084613, 27087426, 27010765, 27012508
27013423, 27017854, 27100800, 27101105, 27105900, 27106179, 27110878
27115422, 27117822, 27119621, 27119861, 27122162, 27118618, 27125872
27133662, 27134734, 27135647, 27135993, 27138325, 27142120, 27142373
27142529, 27144928, 27151826, 27153641, 27160122, 27161071, 27162390
27161805, 27163928, 27165231, 27169796, 27170305, 27181537, 27181897
27185188, 27195935, 27199185, 27200159, 27202115, 27203055, 27207110
27208795, 27213218, 27216046, 27217412, 27223075, 27229389, 27231051
27234962, 27236722, 27182226, 27184337, 27184999, 27188917, 27189531
27250547, 27251690, 27255377, 27256000, 27258578, 27259307, 27262945
27264464, 27266185, 27274456, 27274536, 27275533, 27276231, 27283960
27284499, 27285184, 27288638, 27292213, 27293599, 27310711, 27310730
27303287, 27303938, 27304410, 27304906, 27305039, 27308088, 27314206
27314390, 27314697, 27320576, 27321179, 27329612, 27333106, 27334316
27338912, 27338946, 27339115, 27345231, 27346701, 27348081, 27349393
27351067, 27351628, 27359178, 27364854, 27367194, 27369515, 27370165
27375542, 27381498, 27383281, 27386467, 27393570, 27394703, 27395416
27396618, 27396672, 27396813, 27397048, 27400416, 27400598, 27404573
27404668, 27405645, 27416997, 27423251, 27418405, 27426363, 27432062
27432826, 27433385, 27433870, 27434193, 27439835, 27441326, 27442041
27445727, 27457891, 27459593, 27459948, 27466597, 27468303, 27486805
27487919, 27489107, 27493674, 27494663, 27501373, 27501413, 27501820
27504770, 27505229, 27508985, 27510159, 27525901, 27529661, 27533780
27533819, 27534501, 27540613, 27544973, 27548131, 27554074, 27555481
27558861, 27560610, 27561888, 27565906, 27567477, 27576342, 27576354
27587905, 27588271, 27593501, 27595973, 27601118, 27601441, 27607563
27611612, 27613080, 27613530, 27617978, 27620808, 27623159, 27629756
27632114, 27634676, 27634991, 27645231, 27657712, 27658186, 27666312
27671633, 27680669, 27686599, 27687880, 27688036, 27688019, 27688692
27691920, 27691939, 27693416, 27693713, 27695063, 27698953, 27700466
27704237, 27701046, 27710072, 27719000, 27726780, 27729678, 27739006
27740418, 27748954, 27751755, 27757567, 27757888, 27758544, 27758653
27758972, 27759077, 27769361, 27793533, 27799032, 27801337, 27818389
27819881, 27818540, 27818543, 27825181, 27828794, 27829295, 27833672
27834551, 27834569, 27835925, 27839353, 27846298, 27846499, 27847259
27850112, 27855490, 27861226, 27873412, 27882176, 27886087, 27897759
27898015, 27910561, 27901478, 27927431, 27929287, 27931299, 27935493
27940876, 27945870, 27951817, 27959048, 27959594, 27966472, 27967484
27983174, 27986817, 27994325, 27995215, 27995188, 27997875, 27998003
27999073, 27999638, 28001069, 28102021, 28103081, 28103399, 28103482
28018793, 28105414, 28106866, 28033429, 28040776, 28043157, 28045903
28067846, 28071549, 28072383, 28072567, 28074713, 28079127, 28010453
28018160, 28019662, 28108003, 28111583, 28120036, 28120151, 28118631
28125947, 28129791, 28140658, 28157786, 28164480, 28165439, 28171079
28174827, 28180464, 28181101, 28184554, 28188330, 28190796, 28194173
28199085, 28201419, 28201985, 28215510, 28218832, 28220398, 28223871
28226179, 28229360, 28236305, 28238264, 28182712, 28250129, 28256164
28271119, 28276054, 28279837, 28281014, 28282606, 28290434, 28294563
28310049, 28305001, 28305362, 28301406, 28319114, 28320399, 28330714
28330171, 28354603, 28357401, 28361221, 28365111, 28369012, 28371123
28373960, 28375383, 28378446, 28384353, 28386259, 28388910, 28391073
28391210, 28396445, 28397317, 28401116, 28410823, 28420042, 28420457
28423598, 28432129, 28434108, 28437315, 28439086, 28454215, 28454182
28468312, 28468493, 28481149, 28483184, 28489150, 28501075, 28510343
28503038, 28507318, 28508053, 28508557, 28512336, 28521330, 28521841
28528349, 28530171, 28535127, 28535272, 28537715, 28538439, 28541855
28545134, 28546290, 28547068, 28564479, 28571483, 28571807, 28572834
28578164, 28578945, 28585411, 28587723, 28589501, 28601033, 28608211
28612674, 28617631, 28617959, 28621470, 28622210, 28627255, 28636676
28639299, 28642899, 28678804, 28691965, 28692103, 28692275, 28697806
28708103, 28701063, 28710469, 28714988, 28728272, 28734355, 28740708
28742555, 28749289, 28749718, 28758010, 28758722, 28774416, 28777174
28791725, 28797711, 28803345, 28817449, 28819640, 28820669, 28821847
28830691, 28831971, 28835937, 28836716, 28838066, 28844866, 28849751
28852691, 28855922, 28856060, 28856172, 28863263, 28863487, 28867992
28887305, 28889730, 28891984, 28927452, 28949888, 28951382, 28956908
28959493, 28961011, 28965015, 28965787, 28986231, 28986257, 28987439
28991884, 28993295, 28993590, 29001888, 29006527, 29007321, 29007353
29001513, 29013832, 29018054, 29106582, 29107694, 29032276, 29040739
29048498, 29050886, 29061016, 29061016, 29115857, 29125374, 29158680
29163567, 29171032, 29173817, 29179017, 29182517, 29182901, 29189889
29198012, 29200700, 29203604, 29213320, 29218605, 29237575, 29187712
29189289, 29251030, 29250317, 29260156, 29278684, 29301463, 29339155
29343086, 29343861, 29347943, 29353821, 29372069, 29371860, 29375355
29375984, 29376346, 29378913, 29379978, 29383695, 29388100, 29398488
29399336, 29405462, 29401149, 29401455, 29426181, 29434301, 29436454
29437712, 29450812, 29452251, 29454978, 29464779, 29483626, 29483672
29483723, 29483771, 29501057, 29500163, 29504682, 29511611, 29518985
29530515, 29536342, 29538631, 29541849, 29542580, 29548592, 29549071
29580394, 29614575, 29621961, 29625065, 29626154, 29629430, 29633753
29637526, 29645349, 29651520, 29656843, 29667994, 29676089, 29678163
29685137, 29687220, 29690625, 29703195, 29707896, 29719146, 29718063
29726695, 29767177, 29782211, 29791152, 29794462, 29807964, 29813494
29817278, 29825525, 29836659, 29841687, 29846645, 29853485, 29865188
29869404, 29875459, 29876358, 29881050, 29884958, 29893132, 29910311
29914449, 29944035, 29944660, 29951620, 29961353, 29962927, 29962939
29991257, 30018017, 30031107, 30064268, 30076253, 30078934, 30088912
30018251, 30019310, 30114477, 30120608, 30125995, 30131286, 30147928
30150731, 30163183, 30164714, 30173113, 30177597, 30179644, 30189103
30196358, 31000758, 31015130, 31018044, 31018317, 31023712, 31039480
30181567, 30184787, 30186179, 30187305, 31052018, 31052156, 31053255
31065523, 31072329, 31081591, 31082501, 31083932, 30305880, 30312014
30342878, 30364613, 30365745, 30374739, 30384152, 30410386, 30403763
30408515, 30413137, 30416034, 30431274, 30441687, 30453442, 30458593
30460122, 30475115, 30485255, 30496957, 30497057, 30501574, 30503943
30501277, 30510527, 30582500, 30613937, 30635310, 30654401, 30671813
30741263, 30803210, 30815852, 30168208





OPatch succeeded.

[oracle@oracleagent 30886680]$ export PATH=/u01/app/oracle/product/12.2.0.1/db_1/OPatch:$PATH
[oracle@oracleagent 30886680]$ opatch version
OPatch Version: 12.2.0.1.19
OPatch succeeded.

Here we are going to apply patch with opatch apply.

User need to give Y to proceed Patch.

[oracle@oracleagent 30886680]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.19
Copyright (c) 2021, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc
OPatch version : 12.2.0.1.19
OUI version : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-18_10-03-10AM_1.log

Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 30886680






Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/12.2.0.1/db_1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…
Applying interim patch '30886680' to OH '/u01/app/oracle/product/12.2.0.1/db_1'
ApplySession: Optional component(s) [ oracle.swd.oui, 12.2.0.1.0 ] , [ oracle.oid.client, 12.2.0.1.0 ] , [ oracle.has.crs, 12.2.0.1.0 ] , [ oracle.rdbms.drdaas, 12.2.0.1.0 ] , [ oracle.ons.daemon, 12.2.0.1.0 ] , [ oracle.network.cman, 12.2.0.1.0 ] not present in the Oracle Home or a higher version is found.






Patching component oracle.rdbms.rsf, 12.2.0.1.0…
Patching component oracle.rdbms, 12.2.0.1.0…
Patching component oracle.rdbms.util, 12.2.0.1.0…
Patching component oracle.network.rsf, 12.2.0.1.0…
Patching component oracle.ctx, 12.2.0.1.0…
Patching component oracle.ctx.rsf, 12.2.0.1.0…
Patching component oracle.has.common.cvu, 12.2.0.1.0…
Patching component oracle.ldap.rsf, 12.2.0.1.0…
Patching component oracle.rdbms.dbscripts, 12.2.0.1.0…
Patching component oracle.rdbms.deconfig, 12.2.0.1.0…
Patching component oracle.rdbms.rsf.ic, 12.2.0.1.0…
Patching component oracle.sdo, 12.2.0.1.0…
Patching component oracle.sdo.locator, 12.2.0.1.0…
Patching component oracle.tfa, 12.2.0.1.0…
Patching component oracle.xdk.parser.java, 12.2.0.1.0…
Patching component oracle.rdbms.dv, 12.2.0.1.0…
Patching component oracle.precomp.rsf, 12.2.0.1.0…
Patching component oracle.rdbms.crs, 12.2.0.1.0…
Patching component oracle.rdbms.rman, 12.2.0.1.0…
Patching component oracle.rdbms.install.plugins, 12.2.0.1.0…
Patching component oracle.sqlplus, 12.2.0.1.0…
Patching component oracle.ons, 12.2.0.1.0…
Patching component oracle.oraolap, 12.2.0.1.0…
Patching component oracle.xdk.rsf, 12.2.0.1.0…
Patching component oracle.xdk, 12.2.0.1.0…
Patching component oracle.rdbms.lbac, 12.2.0.1.0…
Patching component oracle.sqlplus.ic, 12.2.0.1.0…
Patching component oracle.assistants.server, 12.2.0.1.0…
Patching component oracle.nlsrtl.rsf, 12.2.0.1.0…
Patching component oracle.assistants.deconfig, 12.2.0.1.0…
Patching component oracle.ldap.rsf.ic, 12.2.0.1.0…
Patching component oracle.ldap.client, 12.2.0.1.0…
Patching component oracle.assistants.acf, 12.2.0.1.0…
Patching component oracle.rdbms.oci, 12.2.0.1.0…
Patching component oracle.install.deinstalltool, 12.2.0.1.0…
Patching component oracle.oracore.rsf, 12.2.0.1.0…
Patching component oracle.precomp.lang, 12.2.0.1.0…
Patching component oracle.precomp.common, 12.2.0.1.0…
Patching component oracle.jdk, 1.8.0.91.0…
Patch 30886680 successfully applied.
Log file location: /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-18_10-03-10AM_1.log

OPatch succeeded.

Bringing up the instance and execute the datapatch verbose.

SQL> startup
ORACLE instance started.
Total System Global Area 1883107968 bytes
Fixed Size 8623736 bytes
Variable Size 671011080 bytes
Database Buffers 1795162112 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@oracleagent 30886680]$ cd $ORACLE_HOME/OPatch
[oracle@oracleagent OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Mon Jan 18 10:33:28 2021
Copyright (c) 2012, 2021, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_30825_2021_01_18_10_33_28/sqlpatch_invocation.log
Connecting to database…OK
Bootstrapping registry and package to current versions…done
Determining current state…done
Current state of SQL patches:
Bundle series DBRU:
ID 200414 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks…
Installation queue:
Nothing to roll back
The following patches will be applied:
30886680 (DATABASE APR 2021 RELEASE UPDATE 12.2.0.1.200414)

Installing patches…
Patch installation complete. Total patches installed: 1

Validating logfiles…
Patch 30886680 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30886680/23474251/30886680_apply_BIJU1_2021Jan18_10_34_01.log (no errors)
SQL Patching tool complete on Mon Jan 18 10:37:30 2021

[oracle@oracleagent OPatch]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 18 10:38:48 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> SELECT owner, object_name, object_type FROM all_objects WHERE status ='INVALID';
no rows selected

SQL> set line 1000
col action form a12
col version form a40
col description form a85
col action_date form a20
select description, action, to_char(action_time,'DD/MM/RR HH18:MI:SS') action_date, ' ' version
from dba_registry_sqlpatch;

DESCRIPTION                           ACTION             ACTION_DATE              VERSION
-----------------------------      ----------         ---------------------   ------------------
DATABASE APR 2021 RELEASE            APPLY             18/01/20 10:37:30          12.2.0.1.0
UPDATE 12.2.0.1.200414

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Find sql history and statistics

SQL History:

All in one:

set lines 1000 pages 9999 COL instance_number FOR 9999 HEA 'Inst'; COL end_time HEA 'End Time'; COL plan_hash_value HEA 'Plan|Hash Value'; COL executions_total FOR 999,999 HEA 'Execs|Total'; COL rows_per_exec HEA 'Rows Per Exec'; COL et_secs_per_exec HEA 'Elap Secs|Per Exec'; COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec'; COL io_secs_per_exec HEA 'IO Secs|Per Exec'; COL cl_secs_per_exec HEA 'Clus Secs|Per Exec'; COL ap_secs_per_exec HEA 'App Secs|Per Exec'; COL cc_secs_per_exec HEA 'Conc Secs|Per Exec'; COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec'; COL ja_secs_per_exec HEA 'Java Secs|Per Exec'; SELECT 'gv$dba_hist_sqlstat' source,h.instance_number, TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time, TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time, h.sql_id, h.plan_hash_value, h.executions_total, TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec, TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec, TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec, TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec, TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec, TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec, TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec, TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec, TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec FROM dba_hist_sqlstat h, dba_hist_snapshot s WHERE h.sql_id = '&sql_id' AND h.executions_total > 0 AND s.snap_id = h.snap_id AND s.dbid = h.dbid AND s.instance_number = h.instance_number UNION ALL SELECT 'gv$sqlarea_plan_hash' source,h.inst_id, TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time, TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time, h.sql_id, h.plan_hash_value, h.executions, TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec, TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec, TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec, TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec, TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec, TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec, TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec, TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec, TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec FROM gv$sqlarea_plan_hash h WHERE h.sql_id = '&&sql_id' AND h.executions > 0 order by source ;

AWR DATA:

AWR-LIO:

col execs for 999,999,999 col avg_etime for 999,999 col avg_lio for 999,999,999 col avg_pio for 999,999,999 col begin_interval_time for a30 col node for 99999 break on plan_hash_value on startup_time skip 1 select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio, ROUND(disk_reads_delta/DECODE(executions_delta,0,1, executions_delta),1) avg_pio, ROUND(rows_processed_delta/DECODE(executions_delta,0, 1, executions_delta), 1) avg_rows, round(px_servers_execs_delta/decode(executions_delta,0,1, executions_delta), 1) avg_px from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = nvl('&sql_id','4dqs2k5tynk61') and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3 /

To find which is using Current memory:

set linesize 999 col avg_et_secs justify right format 9999999.99 col cost justify right format 9999999999 col timestamp justify center format a25 col parsing_schema_name justify center format a30 col inst_id format 999999999 alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss'; select 'gv$sqlarea_plan_hash' source, INST_ID, SQL_ID, PLAN_HASH_VALUE, round(elapsed_time/decode(nvl(executions,0),0,1,executions)/1e6/ decode(px_servers_executions,0,1,px_servers_executions)/decode(nvl(executions,0),0,1,executions),2) avg_et_secs, px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px, optimizer_cost cost, LAST_LOAD_TIME timestamp, parsing_schema_name --FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, SQL_PROFILE from gv$sqlarea_plan_hash where sql_id = nvl(trim('&sql_id'),sql_id) UNION SELECT 'dba_hist_sql_plan' source, null INST_ID, t1.sql_id sql_id, t1.plan_hash_value plan_hash_value, t2.avg_et_secs avg_et_secs, t2.avg_px, t1.cost cost, t1.timestamp timestamp, NULL parsing_schema_name FROM dba_hist_sql_plan t1, ( SELECT sql_id, plan_hash_value, --round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6,2) avg_et_secs round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6/ decode(SUM(px_servers_execs_total),0,1,SUM(px_servers_execs_total))/decode(SUM(executions_total),0,1,SUM(executions_total)),2) avg_et_secs, SUM(px_servers_execs_total)/decode(SUM(executions_total),0,1,SUM(executions_total)) avg_px FROM dba_hist_sqlstat WHERE executions_total > 0 GROUP BY sql_id, plan_hash_value ) t2 WHERE t1.sql_id = nvl(TRIM('&sql_id'), t1.sql_id) AND t1.depth = 0 AND t1.sql_id = t2.sql_id(+) AND t1.plan_hash_value = t2.plan_hash_value(+) order by avg_et_secs, cost /

Identify the Culprit: SQL , SQL TEXT , BIND VARIABLE

col exact_matching_signature for 99999999999999999999999999 col sql_text for a50 select sql_id, exact_matching_signature, SQL_TEXT from v$sqlarea where UPPER(sql_text) like '%DUMMY%' order by UPPER(sql_text);

set long 20000 set lines 750 pages 9999 select sql_text from dba_hist_sqltext where sql_id = '&SQL_ID';

set long 20000 set lines 750 pages 9999 select sql_text from gv$sqlarea where sql_id = '&SQL_ID';

col VALUE_STRING for a50 SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING FROM gv$sql_bind_capture WHERE sql_id='&sql_id';

statistics of objects of a specific sql id:

set lines 300 set pages 300 col table_name for a40 col owner for a30 select distinct owner, table_name, STALE_STATS, last_analyzed, stattype_locked from dba_tab_statistics where (owner, table_name) in (select distinct owner, table_name from dba_tables where ( table_name) in ( select object_name from gv$sql_plan where upper(sql_id) = upper('&sql_id') and object_name is not null)) --and STALE_STATS='YES' /

col index_name for a50 SELECT owner, index_name, table_name,last_analyzed, sample_size, num_rows, partitioned, global_stats FROM dba_indexes WHERE index_name IN ( select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ') from ( SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', null, 'BASIC')) UNION ALL SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null, 'ALL')) ) where plan_table_output like '%INDEX%' ) ORDER BY owner, table_name, index_name /

Sql id current rows, I/O , read/wrire details:

select SQL_ID,PLAN_HASH_VALUE,ELAPSED_TIME,PHYSICAL_READ_BYTES,PHYSICAL_WRITE_BYTES,DISK_READS,DIRECT_WRITES,BUFFER_GETS,ROWS_PROCESSED from v$sqlstats where sql_id='&sql_id';

select sql_text,rows_processed from v$sql where USERS_EXECUTING>0;

select s.sid, s.serial#, p.spid, s.username, s.program, t.xidusn, t.used_ublk, t.used_urec, sa.sql_text from v$process p,v$session s, v$sqlarea sa, v$transaction t where s.paddr=p.addr and s.taddr=t.addr and s.sql_address=sa.address(+) and s.sql_hash_value=sa.hash_value(+) order by s.sid /

exec dbms_stats.flush_database_monitoring_info; select inserts,updates,deletes from user_tab_modifications where table_name = '&table_name';

Check the progress of DML statements:

col sql_text for a60 SELECT rows_processed "Total Rows Processed", ROUND((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60, 1) "Total Time (Min)", TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) "Rows/Min", TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60)) "Rows/Sec" , SUBSTR(sql_text, 1, 60) sql_text FROM gv$sqlarea WHERE SQL_ID='&SQL_ID' AND open_versions > 0 AND rows_processed > 0;

Detailed history of SQL_ID:

select instance_number inst_id,SESSION_ID,USER_ID,PROGRAM,sql_id,SQL_CHILD_NUMBER,sql_plan_hash_value,to_char (sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start from dba_hist_active_sess_history where sql_id='&sql_id';

select sql_id, starting_time, end_time, (EXTRACT(HOUR FROM run_time) * 3600 + EXTRACT(MINUTE FROM run_time) * 60 + EXTRACT(SECOND FROM run_time))/60 run_time_MIN, READ_IO_BYTES, PGA_ALLOCATED PGA_ALLOCATED_BYTES, TEMP_ALLOCATED TEMP_ALLOCATED_BYTES from ( select sql_id, max(sample_time - sql_exec_start) run_time, max(sample_time) end_time, sql_exec_start starting_time, sum(DELTA_READ_IO_BYTES) READ_IO_BYTES, sum(DELTA_PGA) PGA_ALLOCATED, sum(DELTA_TEMP) TEMP_ALLOCATED from ( select sql_id, sample_time, sql_exec_start, DELTA_READ_IO_BYTES, sql_exec_id, greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_PGA, greatest(TEMP_SPACE_ALLOCATED - first_value(TEMP_SPACE_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_TEMP from dba_hist_active_sess_history where sample_time >= trunc(sysdate) --and sample_time < to_date ('2013/04/16 03:10:00','YYYY/MM/DD HH24:MI:SS') and sql_exec_start is not null and IS_SQLID_CURRENT='Y' ) group by sql_id,SQL_EXEC_ID,sql_exec_start order by sql_id ) where sql_id = '&sql_id' order by sql_id, run_time_MIN desc;

To find Sql Hanging or not:

select sess_io.inst_id, sess_io.sid, sesion.sql_id, sess_io.block_gets, sess_io.consistent_gets, sess_io.physical_reads, sess_io.block_changes, sess_io.consistent_changes from gv$sess_io sess_io, gv$session sesion where sesion.sid = sess_io.sid and sess_io.inst_id = sesion.inst_id and sesion.sql_id='&sql_id' and sesion.username is not null ;

select sess_io.inst_id, sess_io.sid, sess_io.block_gets, sess_io.consistent_gets, sess_io.physical_reads, sess_io.block_changes, sess_io.consistent_changes from gv$sess_io sess_io, gv$session sesion where sesion.sid = sess_io.sid and sess_io.inst_id = sesion.inst_id and sesion.username is not null ;

SQL to show the full SQL executing for active sessions:

select sesion.sid,sql_text from v$sqltext sqltext, v$session sesion where sesion.sql_hash_value = sqltext.hash_value and sesion.sql_address = sqltext.address and sesion.username is not null order by sqltext.piece;

select a.sid,b.sql_fulltext from V$Session a, V$SQLAREA b where a.sql_id=b.sql_id and a.status='ACTIVE';

Tag Archives: oracle agent