Description:-
We are going to perform Patching Oracle Database Release Update Revision 18.3.2.0.0 to 18.4.1.0.0
About Oracle Database Release Numbers:-
Oracle Database releases are released in version and version_full releases. The version is designated in the form major release numeral.0.0.0.0. The release version is the annual release designation of the database software. For example: 2018 is the release year, and the release version is 18.0.0.0.0. The version_full release is updated using numeric segments that change, based on the annual release designation of the software, the quarterly release update version (RU), and the quarterly release updates revision (RUR).
Oracle Database version_full releases are categorized by three numeric segments, which are divided by periods. The first, second, and third numerals provide information about the Oracle Database releases, starting with Oracle Database release 18c:
First numeral: Oracle Database major release number. For example: Oracle Database 18c; Oracle Database 12c. Starting with Oracle Database 18c, the first numeral indicates the initial year in which an Oracle Database version is released. For example: 2018 is the initial release year for Oracle Database 18c (18.0.0.0.0)
Second numeral: Oracle Database Release Update (Update) in Oracle Database 18c and later releases. For example: 18.3 is Release Update 3. In releases before Oracle Database 18c, the second numeral designates a maintenance release number. For example: Oracle Database 12c release 1 (12.1); Oracle 12c release 2 (12.2).
Third numeral: Oracle Database release updates revision (Revision). For example: 18.1.1.; 18.2.1, 18.3.0
Fourth numeral: Oracle Database increment version. This nomenclature can apply to updates in future releases, and it applies to all releases earlier than 18c. For example: 12.1.0.1, 12.2.0.1,
Fifth numeral: This numeral is reserved for future use.
Caution: Oracle strongly recommends that you apply the most recent release update (Update) or bundle patch or patch set update to your source and target databases before starting an upgrade, and before starting a downgrade.
Check Database version :
[oracle@test ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 – Production on Thu Feb 14 04:26:25 2019
Version 18.3.2.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.3.2.0.0
Check Opatch version and lsinventory :
Check the opatch version and inventory for ORACLE_HOME.
[oracle@test ~]$ export ORACLE_HOME=/u02/app/oracle/product/18.3.0/db_1 [oracle@test ~]$ export PATH=/u02/app/oracle/product/18.3.0/db_1/OPatch:$PATH [oracle@test ~]$ opatch version OPatch Version: 12.2.0.1.14 OPatch succeeded.
Minimum version of OPatch utility version 12.2.0.1.14 or later to apply this patch.
[oracle@test ~]$ opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.14 Copyright (c) 2019, Oracle Corporation. All rights reserved. Oracle Home : /u02/app/oracle/product/18.3.0/db_1 Central Inventory : /u01/app/oraInventory from : /u02/app/oracle/product/18.3.0/db_1/oraInst.loc OPatch version : 12.2.0.1.14 OUI version : 12.2.0.4.0 Log file location : /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2019-02-14_04-27-17AM_1.log Lsinventory Output file location : /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2019-02-14_04-27-17AM.txt -------------------------------------------------------------------------------- Local Machine Information:: Hostname: test.localdomain.com ARU platform id: 226 ARU platform description:: Linux x86-64 Installed Top-level Products (1): Oracle Database 18c 18.0.0.0.0 There are 1 products installed in this Oracle Home. Interim patches (4) :
Patch 28790643 : applied on Sat Feb 09 02:01:58 IST 2019
Unique Patch ID: 22605424
Patch description: “Database Release Update Revision : 18.3.2.0.190115 (28790643)”
Created on 1 Dec 2018, 05:13:21 hrs PST8PDT Bugs fixed: 25929650, 9062315, 13554903, 21547051, 21766220, 21806121, 23003564 23310101, 24489904, 24689376, 24737581, 24925863, 25035594, 25035599 25287072, 25348956, 25634405, 25726981, 25743479, 25824236, 25943740 26226953, 26336101, 26423085, 26427905, 26450454, 26476244, 26598422 26615291, 26646549, 26654411, 26731697, 26785169, 26792891, 26818960 26822620, 26843558, 26843664, 26846077, 26894737, 26898279, 26928317 26933599, 26956033, 26961415, 26966120, 26986173, 26992964, 27005278 27026401, 27028251, 27030974, 27036408, 27038986, 27041253, 27044575 27047831, 27053044, 27058530, 27060167, 27060859, 27061736, 27066451 27066519, 27073066, 27086821, 27090765, 27101527, 27101652, 27110878 27112686, 27119621, 27126666, 27128580, 27135647, 27143756, 27143882 27147979, 27153641, 27155549, 27156355, 27163928, 27169796, 27181521 27181537, 27189611, 27190851, 27193810, 27199245, 27208953, 27210038 27210872, 27214085, 27215007, 27216224, 27221900, 27222121, 27222626 27224987, 27226913, 27232983, 27233563, 27236052, 27236110, 27240246 27240570, 27241221, 27241247, 27244337, 27244785, 27249215, 27250547 27254851, 27258578, 27259386, 27259983, 27262650, 27262945, 27263276 27263996, 27270197, 27274456, 27274536, 27275136, 27275776, 27282707 27283029, 27283960, 27284499, 27285244, 27288230, 27292213, 27294480 27301308, 27301568, 27302594, 27302681, 27302695, 27302711, 27302730 27302777, 27302800, 27302960, 27304410, 27304936, 27305318, 27307868 27310092, 27313687, 27314206, 27314390, 27318869, 27321179, 27321834 27326204, 27329812, 27330158, 27330161, 27333658, 27333664, 27333693 27334316, 27334648, 27335682, 27338912, 27338946, 27339115, 27339396 27339483, 27339495, 27341036, 27345190, 27345231, 27345450, 27345498 27346329, 27346644, 27346709, 27346949, 27347126, 27348081, 27348707 27349393, 27352600, 27354783, 27356373, 27357773, 27358241, 27359178 27359368, 27360126, 27364891, 27364916, 27364947, 27365139, 27365702 27365993, 27367194, 27368850, 27372756, 27375260, 27375542, 27376871 27378103, 27379233, 27381383, 27381656, 27384222, 27389352, 27392187 27395404, 27395416, 27395794, 27396357, 27396365, 27396377, 27396624 27396666, 27396672, 27396813, 27398080, 27398660, 27401637, 27405242 27405696, 27410300, 27410595, 27412805, 27417186, 27420715, 27421101 27422874, 27423251, 27425507, 27425622, 27426363, 27427805, 27430802 27432338, 27432355, 27433870, 27434050, 27434193, 27434486, 27434974 27435537, 27439835, 27441326, 27442041, 27444727, 27445330, 27445462 27445727, 27447452, 27447687, 27448162, 27450355, 27450400, 27450783 27451049, 27451182, 27451187, 27451531, 27452760, 27453225, 27457666 27457891, 27458164, 27459909, 27460675, 27467543, 27469329, 27471876 27472969, 27473800, 27479358, 27483974, 27484556, 27486253, 27487795 27489719, 27496224, 27496308, 27497950, 27498477, 27501327, 27501413 27501465, 27502420, 27504190, 27505603, 27506774, 27508985, 27511196 27512439, 27517818, 27518227, 27518310, 27520070, 27520900, 27522245 27523368, 27523800, 27525909, 27532375, 27533819, 27534509, 27537472 27544030, 27545630, 27547732, 27550341, 27551855, 27558557, 27558559 27558861, 27560702, 27563629, 27563767, 27570318, 27577758, 27579353 27580996, 27585755, 27585800, 27586810, 27586895, 27587672, 27591842 27592466, 27593389, 27595973, 27599689, 27602091, 27602488, 27603841 27604293, 27607805, 27608669, 27610269, 27613080, 27613247, 27615608 27616657, 27617522, 27625274, 27625620, 27631506, 27634676, 27635508 27644757, 27649707, 27652302, 27663370, 27664702, 27679488, 27679664 27679806, 27679961, 27680162, 27680509, 27682151, 27688099, 27688692 27690578, 27691809, 27692215, 27693713, 27697092, 27701795, 27705761 27707544, 27709046, 27718914, 27719187, 27723002, 27726269, 27726780 27732323, 27739006, 27740844, 27744211, 27745220, 27747869, 27748954 27751006, 27753336, 27757567, 27772815, 27773602, 27774320, 27774539 27779886, 27780562, 27782339, 27783289, 27786772, 27791223, 27797290 27803665, 27807441, 27812560, 27812593, 27813267, 27815347, 27818871 27832643, 27833369, 27834984, 27840386, 27847259, 27851757, 27861909 27869339, 27873643, 27882176, 27892488, 27924147, 27926113, 27930478 27934468, 27941896, 27945870, 27950708, 27952762, 27961746, 27964051 27970265, 27971575, 27984028, 27989849, 27993289, 27994333, 27997875 27999597, 28021205, 28022847, 28033429, 28057267, 28059199, 28072130 28098865, 28106402, 28132287, 28169711, 28174827, 28174951, 28184554 28188330, 28238264, 28264172, 28320117, 28321446, 28338399, 28492362 28571483, 28580528, 28713840, 28728272, 28758090, 28785022, 28805612 28805695, 28919145 Patch 27908644 : applied on Wed Jul 18 23:14:11 IST 2018 Unique Patch ID: 22153180 Patch description: "UPDATE 18.3 DATABASE CLIENT JDK IN ORACLE HOME TO JDK8U171" Created on 4 May 2018, 01:21:02 hrs PST8PDT Bugs fixed: 27908644 Patch 27923415 : applied on Wed Jul 18 23:11:38 IST 2018 Unique Patch ID: 22239273 Patch description: "OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)" Created on 15 Jul 2018, 10:33:22 hrs PST8PDT Bugs fixed: 27304131, 27539876, 27952586, 27642235, 27636900, 27461740 Patch 28090553 : applied on Wed Jul 18 23:10:01 IST 2018 Unique Patch ID: 22256940 Patch description: "OCW RELEASE UPDATE 18.3.0.0.0 (28090553)" Created on 11 Jul 2018, 19:20:31 hrs PST8PDT Bugs fixed: 12816839, 18701017, 22734786, 23698980, 23840305, 25709124, 25724089 26299684, 26313403, 26433972, 26527054, 26586174, 26587652, 26647619 26827699, 26860285, 26882126, 26882316, 26943660, 26996813, 27012915 27018734, 27032726, 27034318, 27040560, 27080748, 27086406, 27092991 27098733, 27106915, 27114112, 27121566, 27133637, 27144533, 27153755 27166715, 27174938, 27174948, 27177551, 27177852, 27182006, 27182064 27184253, 27204476, 27212837, 27213140, 27220610, 27222423, 27222938 27238077, 27238258, 27249544, 27252023, 27257509, 27263677, 27265816 27267992, 27271876, 27274143, 27285557, 27299455, 27300007, 27302415 27309182, 27314512, 27315159, 27320985, 27334353, 27338838, 27346984 27358232, 27362190, 27370933, 27377219, 27378959, 27379846, 27379956 27393421, 27398223, 27399499, 27399762, 27399985, 27401618, 27403244 27404599, 27426277, 27428790, 27430219, 27430254, 27433163, 27452897 27458829, 27465480, 27475272, 27481406, 27481765, 27492916, 27496806 27503318, 27503413, 27508936, 27508984, 27513114, 27519708, 27526362 27528204, 27532009, 27534289, 27560562, 27560735, 27573154, 27573408 27574335, 27577122, 27579969, 27581484, 27593587, 27595801, 27600706 27609819, 27625010, 27625050, 27627992, 27654039, 27657467, 27657920 27668379, 27682288, 27691717, 27702244, 27703242, 27708711, 27714373 27725967, 27731346, 27734470, 27735534, 27739957, 27740854, 27747407 27748321, 27757979, 27766679, 27768034, 27778433, 27782464, 27783059 27786669, 27786699, 27801774, 27811439, 27839732, 27850736, 27862636 27864737, 27865439, 27889841, 27896388, 27897639, 27906509, 27931506 27935826, 27941514, 27957892, 27978668, 27984314, 27993298, 28023410 28025398, 28032758, 28039471, 28039953, 28045209, 28099592, 28109698 28174926, 28182503, 28204423, 28240153 ----------------------------------------------------------------------- OPatch succeeded.
Download and Unzip the Database Release Update Patch :
Release Date and Download link :
| 15-Jan-2019 | Database Release Update Revision 18.4.1.0.0 | Patch 28822587 |
[oracle@test u02]$ unzip p28822587_180000_Linux-x86-64.zip Archive: p28822587_180000_Linux-x86-64.zip creating: 28822587/ creating: 28822587/files/ creating: 28822587/files/javavm/ creating: 28822587/files/javavm/ojvmwcu/ creating: 28822587/files/javavm/ojvmwcu/lib/
Check Patch Conflict Detection and Resolution :
[oracle@test 28822587]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 12.2.0.1.14 Copyright (c) 2019, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u02/app/oracle/product/18.3.0/db_1 Central Inventory : /u01/app/oraInventory from : /u02/app/oracle/product/18.3.0/db_1/oraInst.loc OPatch version : 12.2.0.1.14 OUI version : 12.2.0.4.0 Log file location : /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2019-02-14_04-46-45AM_1.log Invoking prereq "checkconflictagainstohwithdetail" Prereq "checkConflictAgainstOHWithDetail" passed. OPatch succeeded.
Shutdown the database and listener :
SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. [oracle@test ~]$ lsnrctl stop LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 09-FEB-2019 02:05:46 Copyright (c) 1991, 2018, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.localdomain.com)(PORT=1521))) The command completed successfully
Apply the database upgrade patch :
[oracle@test 28822587]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.14 Copyright (c) 2019, Oracle Corporation. All rights reserved. Oracle Home : /u02/app/oracle/product/18.3.0/db_1 Central Inventory : /u01/app/oraInventory from : /u02/app/oracle/product/18.3.0/db_1/oraInst.loc OPatch version : 12.2.0.1.14 OUI version : 12.2.0.4.0 Log file location : /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2019-02-14_04-49-20AM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 28822587 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 = '/u02/app/oracle/product/18.3.0/db_1') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files... Applying interim patch '28822587' to OH '/u02/app/oracle/product/18.3.0/db_1' ApplySession: Optional component(s) [ oracle.ons.daemon, 18.0.0.0.0 ] , [ oracle.assistants.asm, 18.0.0.0.0 ] , [ oracle.net.cman, 18.0.0.0.0 ] , [ oracle.network.cman, 18.0.0.0.0 ] , [ oracle.assistants.usm, 18.0.0.0.0 ] , [ oracle.crs, 18.0.0.0.0 ] , [ oracle.tfa, 18.0.0.0.0 ] , [ oracle.assistants.server.oui, 18.0.0.0.0 ] , [ oracle.has.crs, 18.0.0.0.0 ] not present in the Oracle Home or a higher version is found. Patching component oracle.xdk.rsf, 18.0.0.0.0... Patching component oracle.rdbms.rman, 18.0.0.0.0... Patching component oracle.rdbms.util, 18.0.0.0.0... Patching component oracle.rdbms.rsf, 18.0.0.0.0... Patching component oracle.xdk.parser.java, 18.0.0.0.0... Patching component oracle.oraolap.dbscripts, 18.0.0.0.0... Patching component oracle.assistants.deconfig, 18.0.0.0.0... Patching component oracle.rdbms.rsf.ic, 18.0.0.0.0... Patching component oracle.network.client, 18.0.0.0.0... Patching component oracle.nlsrtl.rsf, 18.0.0.0.0... Patching component oracle.ons, 18.0.0.0.0... Patching component oracle.ctx.rsf, 18.0.0.0.0... Patching component oracle.rdbms.dbscripts, 18.0.0.0.0... Patching component oracle.xdk, 18.0.0.0.0... Patching component oracle.nlsrtl.rsf.core, 18.0.0.0.0... Patching component oracle.sdo.locator, 18.0.0.0.0... Patching component oracle.dbjava.ucp, 18.0.0.0.0... Patching component oracle.rdbms.crs, 18.0.0.0.0... Patching component oracle.server, 18.0.0.0.0... Patching component oracle.dbjava.ic, 18.0.0.0.0... Patching component oracle.rdbms, 18.0.0.0.0... Patching component oracle.ctx.atg, 18.0.0.0.0... Patching component oracle.dbjava.jdbc, 18.0.0.0.0... Patching component oracle.sqlplus.ic, 18.0.0.0.0... Patching component oracle.precomp.rsf, 18.0.0.0.0... Patching component oracle.ctx, 18.0.0.0.0... Patching component oracle.rdbms.oci, 18.0.0.0.0... Patching component oracle.network.listener, 18.0.0.0.0... Patching component oracle.sdo, 18.0.0.0.0... Patching component oracle.sqlplus, 18.0.0.0.0... Patching component oracle.assistants.server, 18.0.0.0.0... Patching component oracle.rdbms.deconfig, 18.0.0.0.0... Patching component oracle.assistants.acf, 18.0.0.0.0... Patching component oracle.oracore.rsf, 18.0.0.0.0... Patching component oracle.sdo.locator.jrf, 18.0.0.0.0... Patching component oracle.install.deinstalltool, 18.0.0.0.0... Patching component oracle.network.rsf, 18.0.0.0.0... Patching component oracle.rdbms.install.plugins, 18.0.0.0.0... Patching component oracle.javavm.client, 18.0.0.0.0... Patching component oracle.ldap.owm, 18.0.0.0.0... Patching component oracle.ldap.security.osdt, 18.0.0.0.0... Patching component oracle.rdbms.install.common, 18.0.0.0.0... Patching component oracle.precomp.lang, 18.0.0.0.0... Patching component oracle.precomp.common, 18.0.0.0.0...
Patch 28822587 successfully applied.
Sub-set patch [28790643] has become inactive due to the application of a super-set patch [28822587]. Please refer to Doc ID 2161861.1 for any possible further required actions. Log file location: /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2019-02-14_04-49-20AM_1.log OPatch succeeded.
Start the listener and database :
[oracle@test 28822587]$ lsnrctl start
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 14-FEB-2019 04:54:29 Copyright (c) 1991, 2018, Oracle. All rights reserved. Starting /u02/app/oracle/product/18.3.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 18.0.0.0.0 - Production System parameter file is /u02/app/oracle/product/18.3.0/db_1/network/admin/listener.ora Log messages written to /u02/app/oracle/diag/tnslsnr/test/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.localdomain.com)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.localdomain.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production Start Date 14-FEB-2019 04:54:31 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u02/app/oracle/product/18.3.0/db_1/network/admin/listener.ora Listener Log File /u02/app/oracle/diag/tnslsnr/test/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.localdomain.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully
[oracle@test 28822587]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 – Production on Thu Feb 14 04:54:53 2019
Version 18.4.1.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup ORACLE instance started. Total System Global Area 2516580184 bytes Fixed Size 8660824 bytes Variable Size 671088640 bytes Database Buffers 1828716544 bytes Redo Buffers 8114176 bytes Database mounted. Database opened.
Loading Modified SQL Files into the Database :
[oracle@test 28822587]$ datapatch -verbose SQL Patching tool version 18.0.0.0.0 Production on Thu Feb 14 04:57:16 2019 Copyright (c) 2012, 2018, Oracle. All rights reserved. Log file for this invocation: /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_3312_2019_02_14_04_57_16/sqlpatch_invocation.log Connecting to database...OK Gathering database info...done Bootstrapping registry and package to current versions...done Determining current state...done Current state of interim SQL patches: Interim patch 27923415 (OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)): Binary registry: Installed SQL registry: Applied successfully on 13-FEB-19 01.00.13.654106 AM Current state of release update SQL patches: Binary registry: 18.4.1.0.0 Release_Update_Revision 1811261143: Installed SQL registry: Applied 18.3.2.0.0 Release_Update_Revision 1811212344 successfully on 13-FEB-19 12.56.24.480442 AM Adding patches to installation queue and performing prereq checks...done Installation queue: No interim patches need to be rolled back Patch 28790643 (Database Release Update Revision : 18.3.2.0.190115 (28790643)):
Rollback from 18.3.2.0.0 Release_Update_Revision 1811212344 to 18.3.0.0.0 Release_Update 1806280943
Patch 28822587 (Database Release Update Revision : 18.4.1.0.190115 (28822587)):
Apply from 18.3.0.0.0 Release_Update 1806280943 to 18.4.1.0.0 Release_Update_Revision 1811261143
No interim patches need to be applied Installing patches... Patch installation complete. Total patches installed: 2 Validating logfiles...done
Patch 28790643 rollback: SUCCESS
logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/28790643/22605424/28790643_rollback_TEST18C_2019Feb14_04_57_44.log (no errors)
Patch 28822587 apply: SUCCESS
logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/28822587/22600665/28822587_apply_TEST18C_2019Feb14_04_58_32.log (no errors) SQL Patching tool complete on Thu Feb 14 04:59:23 2019
Patch 28822587 – Database Release Update Revision 18.4.1.0.0 is applied successfully and Database is upgraded from 18.3.2.0.0 to 18.4.1.0.0.
Run utlrp.sql to compile invalid objects :-
SQL> @?/rdbms/admin/utlrp
Session altered. TIMESTAMP ----------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2019-02-09 02:12:45 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC> objects in the database. Recompilation time is proportional to the DOC> number of invalid objects in the database, so this command may take DOC> a long time to execute on a database with a large number of invalid DOC> objects. DOC> DOC> Use the following queries to track recompilation progress: DOC> DOC> 1. Query returning the number of invalid objects remaining. This DOC> number should decrease with time. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC> 2. Query returning the number of objects compiled so far. This number DOC> should increase with time. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC> This script automatically chooses serial or parallel recompilation DOC> based on the number of CPUs available (parameter cpu_count) multiplied DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC> On RAC, this number is added across all RAC nodes. DOC> DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC> recompilation. Jobs are created without instance affinity so that they DOC> can migrate across RAC nodes. Use the following queries to verify DOC> whether UTL_RECOMP jobs are being created and run correctly: DOC> DOC> 1. Query showing jobs created by UTL_RECOMP DOC> SELECT job_name FROM dba_scheduler_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> DOC> 2. Query showing UTL_RECOMP jobs that are running DOC> SELECT job_name FROM dba_scheduler_running_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC># PL/SQL procedure successfully completed. TIMESTAMP ----------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2019-02-09 02:12:47 DOC> The following query reports the number of invalid objects. DOC> DOC> If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS ------------------- 0 DOC> The following query reports the number of exceptions caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC> Note: Typical compilation errors (due to coding errors) are not DOC> logged into this table: they go into DBA_ERRORS instead. DOC># ERRORS DURING RECOMPILATION --------------------------- 0 Function created. PL/SQL procedure successfully completed. Function dropped. PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status='INVALID'; COUNT(*) ---------- 0
dba_registry_sqlpatch :
From Oracle 12c,we are using dba_registry_sqlpatch instead of REGISTRY$HISTORY to check latest applied patches like RU,PSU and CPU.
SQL> select STATUS,DESCRIPTION,SOURCE_VERSION,TARGET_VERSION from dba_registry_sqlpatch; STATUS DESCRIPTION SOURCE_VERSION ------ ---------------------------------------------- -------------- TARGET_VERSION -------------- SUCCESS Database Release Update : 18.3.0.0.180717(28090523) 18.1.0.0.0 18.3.0.0.0 SUCCESS OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415) 18.3.0.0.0 18.3.0.0.0
SUCCESS Database Release Update Revision : 18.3.2.0.190115 18.3.0.0.0
(28790643)
18.3.2.0.0 SUCCESS Database Release Update Revision 18.4.1.0.190115 18.3.0.0.0 (28822587) 18.4.1.0.0
To check database version :
SQL> select BANNER,BANNER_FULL from v$version;
BANNER BANNER FULL
————————– ————————–
Oracle Database 18c Oracle Database 18c
Enterprise Edition Release Enterprise Edition Release
18.0.0.0.0 – Production 18.0.0.0.0 – Production
Version 18.4.1.0.0Catch Me On:- Hariprasath Rajaram
Telegram:
https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg
LinkedIn:
https://www.linkedin.com/in/hariprasathdba
Facebook:
https://www.facebook.com/HariPrasathdba
FB Group:
https://www.facebook.com/groups/894402327369506/
FB Page:
https://www.facebook.com/dbahariprasath/?
Twitter:
https://twitter.com/hariprasathdba
