Patching Oracle Database Release Updates Revision (RUR) 18.3.2.0.0 to 18.4.1.0.0

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.0
Catch 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

 

 

 

Oracle 18c Data Guard Broker Enhancements

Description:-

These are the changes in Oracle Data Guard Broker in Oracle Database Release 18c

This release provides several new commands:

  • VALIDATE DATABASE SPFILE performs a comparison of server parameter file (SPFILE) entries between the primary database and a specified standby database.
  • VALIDATE NETWORK CONFIGURATION performs network connectivity checks between members of a broker configuration.
  •  VALIDATE STATIC CONNECT IDENTIFIER validates the static connect identifier of a database.
  •  SET ECHO controls whether or not to echo commands that are issued either at the command-line prompt or from a DGMGRL script.
  • SHOW ALL (DGMGRL CLI properties) shows the values of DGMGRL CLI properties.

The V$DATAGUARD_PROCESS view, introduced in Oracle Database 12c Release 2 (12.2.0.1), now shows broker processes. (Oracle Data Guard Monitor (DMON)
Process.)

SQL> select name,TYPE,role from V$DATAGUARD_PROCESS;

NAME  TYP ROLE
----- --- ------------------------
LGWR  KSB log writer
TMON  KSB redo transport monitor
DMON  KSB broker monitor
INSV  KSB broker instance slave
NSV2  KSB broker net slave
TT00  KSV gap manager
TT01  KSV redo transport timer
ARC0  KSB archive local
TT02  KSV async ORL multi
TT03  KSV heartbeat redo informer
ARC1  KSB archive redo
ARC2  KSB archive redo
ARC3  KSB archive redo
RSM0  KSB broker worker
drcx  NET switchover
15 rows selected.

VALIDATE DATABASE SPFILE

The VALIDATE DATABASE SPFILE command performs a comparison of server parameter file (SPFILE) entries between the primary database and a specified standby database.

The validation of the server parameter file detects parameter value discrepancies between the primary and the specified standby database so that they can be rectified before a role change, thus ensuring that after a role change the databases perform at the same level they did prior to the role change. Additionally, using this command frees you from having to restart a database to correct improperly set parameters.

Format
VALIDATE DATABASE {database-name} SPFILE;

If the database to be validated is the primary database, then a message is returned saying the command cannot be issued on a primary database.

DGMGRL> VALIDATE DATABASE dbwrprim SPFILE;
This command cannot be used for the primary database.
DGMGRL> VALIDATE DATABASE dbwrstby SPFILE;
Connecting to "dbwrprim".
Connected to "dbwrprim"
Connecting to "dbwrstby".
Connected to "dbwrstby"
Parameter settings with different values:
audit_file_dest:
dbwrprim (PRIMARY) : /oradb/app/oracle/admin/dbwrprim/adump
dbwrstby : /oradb/app/oracle/admin/dbwrstby/adump
compatible:
dbwrprim (PRIMARY) : 18.0.0
dbwrstby : 18.3.0.0.0
dispatchers:
dbwrprim (PRIMARY) : (PROTOCOL=TCP) (SERVICE=dbwrprimXDB)
dbwrstby : (PROTOCOL=TCP) (SERVICE=dbwrstbyXDB)
fal_client:
dbwrprim (PRIMARY) : dbwrprim
dbwrstby : dbwrstby
sga_target:
dbwrprim (PRIMARY) : 1201668096
dbwrstby : 943718400

VALIDATE NETWORK CONFIGURATION

The VALIDATE NETWORK CONFIGURATION command performs network connectivity checks between members of a configuration.

The connect identifier for each connectivity check is generated based on the
DGConnectIdentifier property of the associated database.

Format
VALIDATE NETWORK CONFIGURATION FOR { ALL | member name };

Validating Network Configuration for a Specific Database

DGMGRL> VALIDATE NETWORK CONFIGURATION FOR dbwrstby;
Connecting to instance "dbwrstby" on database "dbwrstby" ...
Checking connectivity from instance "dbwrstby" on database "dbwrstby" to
instance "dbwrprim" on database "dbwprim"...
Succeeded.
Oracle Clusterware is not configured on database "dbwrstby".

VALIDATE STATIC CONNECT IDENTIFIER

The VALIDATE STATIC CONNECT IDENTIFIER command validates the static connect identifier of a database.
To perform this validation, the broker makes a new connection to the database using a static connect identifier based on the StaticConnectIdentifer property of the database. A new attribute, STATIC_SERVICE=TRUE is added to the connect identifier to ensure that a connection to the database is established using only a static service, not a dynamic service.

Format
VALIDATE STATIC CONNECT IDENTIFIER FOR { ALL | database name };

Command Examples
Validation of Static Connect Identifier For a Database on Which
Oracle Clusterware Is Configured

DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR dbwrstby;
Oracle Clusterware is not configured on database "dbwrstby".

SHOW ALL

The SHOW ALL command shows the values of DGMGRL CLI properties.

Format
SHOW ALL;

Command Example

DGMGRL> SHOW ALL;
debug ON
echo OFF
time OFF
observerconfigfile = observer.ora

 

Catch 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

Patching Oracle Database Release Updates Revision (RUR) 18.3.0.0.0 to 18.3.2.0.0

Description:-

We are going to perform Patching Oracle Database Release Update Revision 18.3.0.0.0 to 18.3.2.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 admin]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Feb 9 01:15:22 2019
Version 18.3.0.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.0.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-09_01-43-56AM_1.log
Lsinventory Output file location : /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2019-02-09_01-43-56AM.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 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
Patch 28090523 : applied on Wed Jul 18 23:09:24 IST 2018
Unique Patch ID: 22329768
Patch description: "Database Release Update : 18.3.0.0.180717 (28090523)"
Created on 14 Jul 2018, 00:03:50 hrs PST8PDT
Bugs fixed:
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, 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, 28184554, 28188330, 25929650, 28264172

-----------------------------------------------------------------------
OPatch succeeded.

Download and Unzip the Database Release Update Patch :

Release Date and Download link :

15-Jan-2019 Database Release Update Revision 18.3.2.0.0 Patch 28790643
[oracle@test u02]$ unzip p28790643_180000_Linux-x86-64.zip
Archive: p28790643_180000_Linux-x86-64.zip
creating: 28790643/
creating: 28790643/files/
[oracle@test u02]$ ls -lrt
total 553308
drwxr-xr-x 4 oracle oinstall 4096 Dec 1 18:43 28790643

Check Patch Conflict Detection and Resolution :

[oracle@test 28790643]$ 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-09_01-53-22AM_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 28790643]$ 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-09_01-59-16AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 28790643

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 '28790643' to OH '/u02/app/oracle/product/18.3.0/db_1'
ApplySession: Optional component(s) [ oracle.network.cman, 18.0.0.0.0 ] , [ oracle.net.cman, 18.0.0.0.0 ] , [ oracle.assistants.asm, 18.0.0.0.0 ] , [ oracle.ons.daemon, 18.0.0.0.0 ] , [ oracle.crs, 18.0.0.0.0 ] , [ oracle.assistants.usm, 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.ctx.rsf, 18.0.0.0.0...
Patching component oracle.ons, 18.0.0.0.0...
Patching component oracle.nlsrtl.rsf, 18.0.0.0.0...
Patching component oracle.dbjava.ucp, 18.0.0.0.0...
Patching component oracle.sdo.locator, 18.0.0.0.0...
Patching component oracle.nlsrtl.rsf.core, 18.0.0.0.0...
Patching component oracle.xdk, 18.0.0.0.0...
Patching component oracle.rdbms.dbscripts, 18.0.0.0.0...
Patching component oracle.rdbms.rsf, 18.0.0.0.0...
Patching component oracle.rdbms.util, 18.0.0.0.0...
Patching component oracle.rdbms.rman, 18.0.0.0.0...
Patching component oracle.xdk.rsf, 18.0.0.0.0...
Patching component oracle.network.client, 18.0.0.0.0...
Patching component oracle.rdbms.rsf.ic, 18.0.0.0.0...
Patching component oracle.assistants.deconfig, 18.0.0.0.0...
Patching component oracle.oraolap.dbscripts, 18.0.0.0.0...
Patching component oracle.xdk.parser.java, 18.0.0.0.0...
Patching component oracle.rdbms.deconfig, 18.0.0.0.0...
Patching component oracle.assistants.server, 18.0.0.0.0...
Patching component oracle.rdbms.install.plugins, 18.0.0.0.0...
Patching component oracle.network.rsf, 18.0.0.0.0...
Patching component oracle.install.deinstalltool, 18.0.0.0.0...
Patching component oracle.sdo.locator.jrf, 18.0.0.0.0...
Patching component oracle.oracore.rsf, 18.0.0.0.0...
Patching component oracle.assistants.acf, 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.rdbms, 18.0.0.0.0...
Patching component oracle.dbjava.ic, 18.0.0.0.0...
Patching component oracle.server, 18.0.0.0.0...
Patching component oracle.rdbms.crs, 18.0.0.0.0...
Patching component oracle.sqlplus, 18.0.0.0.0...
Patching component oracle.sdo, 18.0.0.0.0...
Patching component oracle.network.listener, 18.0.0.0.0...
Patching component oracle.rdbms.oci, 18.0.0.0.0...
Patching component oracle.ctx, 18.0.0.0.0...
Patching component oracle.precomp.rsf, 18.0.0.0.0...
Patching component oracle.sqlplus.ic, 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.precomp.lang, 18.0.0.0.0...
Patching component oracle.precomp.common, 18.0.0.0.0...
Patch 28790643 successfully applied.
Sub-set patch [28090523] has become inactive due to the application of a super-set patch [28790643].
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-09_01-59-16AM_1.log

OPatch succeeded.

Start the listener and database :

[oracle@test ~]$ lsnrctl start

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 09-FEB-2019 02:04:49

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 09-FEB-2019 02:04:51
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 ~]$ lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 09-FEB-2019 02:05:05

Copyright (c) 1991, 2018, Oracle. All rights reserved.

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 09-FEB-2019 02:04:51
Uptime 0 days 0 hr. 0 min. 13 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)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=test.localdomain.com)(PORT=5500))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "dbwr.localdomain.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Service "dbwrXDB.localdomain.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
The command completed successfully.
[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Feb 9 02:04:20 2019
Version 18.3.2.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area 1660943808 bytes
Fixed Size 8658368 bytes
Variable Size 1040187392 bytes
Database Buffers 603979776 bytes
Redo Buffers 8118272 bytes
Database mounted.
Database opened.

Loading Modified SQL Files into the Database : 

[oracle@test OPatch]$ ./datapatch -verbose

SQL Patching tool version 18.0.0.0.0 Production on Sat Feb 9 02:09:25 2019
Copyright (c) 2012, 2018, Oracle. All rights reserved.

Log file for this invocation: /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_7208_2019_02_09_02_09_25/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 09-JAN-19 02.47.25.515405 AM

Current state of release update SQL patches:
Binary registry:
18.3.2.0.0 Release_Update_Revision 1811212344: Installed
SQL registry:
Applied 18.3.0.0.0 Release_Update 1806280943 successfully on 09-JAN-19 02.46.23.143918 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)):
Apply from 18.3.0.0.0 Release_Update 1806280943 to 18.3.2.0.0 Release_Update_Revision 1811212344
No interim patches need to be applied

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

Validating logfiles...done
Patch 28790643 apply: SUCCESS
logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/28790643/22605424/28790643_apply_DBWR_2019Feb09_02_10_00.log (no errors)
SQL Patching tool complete on Sat Feb 9 02:11:05 2019

Patch 28790643 – Database Release Update Revision 18.3.2.0.190115 is applied successfully and Database is upgraded from 18.3.0.0.0 to 18.3.2.0.0.

Reference : Patch README file

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

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.3.2.0.0

 

Catch 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