Database Patching? Patch like a King with a Single Click – Database As A Service (DbaaS)

Yenugula Venkata Ravi Kumar (Oracle ACE, OCM & Speaker), Nassyam Basha (Oracle ACE Director, Book Author, OCM, Speaker and OTN Moderator)

Introduction

Database upgrade and patching is a routine task for Oracle DBAs. Whether updating to incorporate new functionality, or the quarterly security patches, DBAs must plan carefully to time the upgrades to minimize downtime and protect data. Add to this the many actions that should be managed manually such as conflicts, OPatch utility version and it's clear patching requires expert timing and execution to be successful. But how about patching and upgrading if you are deploying your database on the Oracle Cloud? This article explains how easy to perform patching on your Database as a Service (DBaaS) using a single click.

Challenges of patching On-Premises

Patching under any circumstances requires a lot of planning. Below are a few important steps:

• What patches have been applied?
• What patches can we apply on our database?
• Downloading patches
• OPatch utility version
• One-off patch conflict detection and resolution
• Installation of patch at Oracle Home level
• Performing post tasks (loading modified SQL)
• Invalid objects compilation

As you can see, these steps represent a lot of planning time and work involved to locate, prepare, download the patch and update the OPatch utility version to actually accomplish the patch operation--for each identified On Premises database in the IT environment.

DBaaS – Cloud patching

Now consider customer databases moved from on-premises to the Oracle cloud then….

**Question:**How easy is it to apply the patch when the database is in the cloud?

Guess: We probably host the database in the cloud, so we can apply the patch like we would on-premises.

Answer: A single click of "Apply Patch" will do everything for you within 30 minutes. For the security patches it depends.

How is it Possible?

Oracle cloud implementations makes it possible to easily integrate all the Oracle sources such as My Oracle Support, Special DBaaS Monitoring, and easy access to the secured Oracle sources. Oracle Cloud has the flexibility to perform all the tasks at one stop, so that manual intervention is no longer required to download, analyze or apply the patches.

Environment Information

Service/Hostname : CKPT-DBaaS
Domain : nassyambasha
Oracle RDBMS : 12.1.0.2
Database : On Cloud
SID : ORC1
OPatch version : 12.1.0.1.10
Patch to apply : April 2016 PSU
Patch 22291127: DATABASE PATCH SET UPDATE 12.1.0.2.160419
Patch 22291127 - Database Patch Set Update 12.1.0.2.160419 (Includes CPUApr2016)

Applying Patch on the Cloud Database

In order to access our database service from the cloud, navigate to Oracle Cloud Dashboard → Select Oracle Database Cloud Service → from My services “click on URL” → from new page under database click on “ x instance(s) and then you can see below screen with the list of databases services.

From the home page of database service, we can see the hardware configuration, connectivity information of database. Now we can jump to the administration section of the left side panel to check the list of the available patches and to know the patch history.

So this section shows us that the database is eligible to apply the PSU update 12.1.0.2.160419 which is April 2016 PSU and we can see other information such as when this patch is released and quick access to readme file.

If once we decided to go to apply the patch then the first step is to perform the “pre-check” of database over the database. Manually we have to run the command using OPatch but here single click can perform and provides the output whether the patch have any conflicts or not.

After initiating pre-check it will start checking with database homes.

It takes very little time to perform the pre-check and after that you will see below status message of the conflicts. This step can be performed as much as many times if in case.

Now we are ready to go for patching, prior to that we will gather the OPatch inventory information so that we can crosscheck after applying the patch.

<p

[oracle@CKPT-DBaaS ~]$ hostname CKPT-DBaaS [oracle@CKPT-DBaaS ~]$ id uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba) [oracle@CKPT-DBaaS ~]$  [oracle@CKPT-DBaaS ~]$ export PATH=$ORACLE_HOME/OPatch:$PATH [oracle@CKPT-DBaaS ~]$ OPatch version OPatch Version: 12.1.0.1.10  OPatch succeeded. [oracle@CKPT-DBaaS ~]$ OPatch lsinventory Oracle Interim Patch Installer version 12.1.0.1.10 Copyright (c) 2016, Oracle Corporation.  All rights reserved.   Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1 Central Inventory : /u01/app/oraInventory    from           : /u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc OPatch version    : 12.1.0.1.10 OUI version       : 12.1.0.2.0 Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/OPatch/OPatch2016-07-16_08-23-00AM_1.log  Lsinventory Output file location :   /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/OPatch/lsinv/lsinventory2016-07-16_08-23-00AM.txt  ----------------------------------------------------------------------Local Machine Information:: Hostname: CKPT-DBaaS.compute-nassyambasha.oraclecloud.internal ARU platform id: 226 ARU platform description:: Linux x86-64  Installed Top-level Products (1):  Oracle Database 12c                                                  12.1.0.2.0 There are 1 products installed in this Oracle Home.  Interim patches (3) :  Patch  22139226     : applied on Thu Jan 21 12:30:53 IST 2016 Unique Patch ID:  19729684 Patch description:  "Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016)"    Created on 4 Jan 2016, 01:41:46 hrs PST8PDT    Bugs fixed:      19699946, 19176885, 19623450, 22139226, 19909862, 21811517, 19223010      21068507, 19895326, 19877336, 22118835, 22118851, 21566993, 19153980      20408829, 21047766, 19231857, 19895362, 19855285, 20415564, 21555660      19245191, 21047803, 20408866, 21566944  Patch  22543975     : applied on Thu Jan 21 12:27:21 IST 2016 Unique Patch ID:  19772638    Created on 20 Jan 2016, 19:09:00 hrs PST8PDT    Bugs fixed:      19770063, 19665921, 21281607, 21154593, 22543975, 21294504    This patch overlays patches:      21948354    This patch needs patches:      21948354    as prerequisites  Patch  21948354     : applied on Thu Jan 21 12:20:58 IST 2016 Unique Patch ID:  19553095 Patch description:  "Database Patch Set Update : 12.1.0.2.160119 (21948354)"    Created on 20 Dec 2015, 23:39:33 hrs PST8PDT Sub-patch  21359755; "Database Patch Set Update : 12.1.0.2.5 (21359755)" Sub-patch  20831110; "Database Patch Set Update : 12.1.0.2.4 (20831110)" Sub-patch  20299023; "Database Patch Set Update : 12.1.0.2.3 (20299023)" Sub-patch  19769480; "Database Patch Set Update : 12.1.0.2.2 (19769480)"    Bugs fixed:      19189525, 19075256, 19141838, 19865345, 19791273, 19280225, 18845653      20951038, 19243521, 19248799, 21756699, 18988834, 19238590, 21281532      20245930, 18921743, 18799063, 19134173, 19571367, 20476175, 20925795      19018206, 20509482, 20387265, 20588502, 19149990, 18849537, 18886413      17551063, 19183343, 19703301, 19001390, 18202441, 19189317, 19644859      19358317, 19390567, 19279273, 19706965, 19068970, 19619732, 20348653      18607546, 18940497, 19670108, 19649152, 18948177, 19315691, 19676905      18964978, 19035573, 20165574, 19176326, 20413820, 20558005, 19176223      19532017, 20134339, 19074147, 18411216, 20361671, 20425790, 18966843      20294666, 19307662, 19371175, 19195895, 19154375, 19468991, 19174521      19520602, 19382851, 21875360, 19326908, 19658708, 20093776, 20618595      21787056, 17835294, 19791377, 19068610, 20048359, 20746251, 19143550      19185876, 19627012, 20281121, 19577410, 22092979, 19001359, 19518079      18610915, 19490948, 18674024, 18306996, 19309466, 19081128, 19915271      20122715, 21188532, 20284155, 18791688, 20890311, 21442094, 18973548      19303936, 19597439, 20235511, 18964939, 19430401, 19044962, 19409212      19879746, 20657441, 19684504, 19024808, 18799993, 20877664, 19028800      19065556, 19723336, 19077215, 19604659, 21421886, 19524384, 19048007      18288842, 19689979, 20446883, 18952989, 16870214, 19928926, 21526048      19180770, 19197175, 19902195, 20318889, 19730508, 19012119, 19067244      20074391, 19512341, 19841800, 14643995, 20331945, 19587324, 19547370      19065677, 19637186, 21225209, 20397490, 18967382, 19174430, 18674047      19054077, 19536415, 19708632, 19289642, 20869721, 19335438, 17365043      18856999, 19869255, 20471920, 19468347, 21620471, 16359751, 18990693      17890099, 19439759, 19769480, 19272708, 19978542, 20101006, 21300341      20402832, 19329654, 19873610, 21668627, 21517440, 19304354, 19052488      20794034, 19291380, 18681056, 19896336, 19076343, 19561643, 18618122      20440930, 18456643, 19699191, 18909599, 19487147, 18250893, 19016730      18743542, 20347562, 16619249, 18354830, 19687159, 19174942, 20424899      19989009, 20688221, 20441797, 19157754, 19032777, 19058490, 19399918      18885870, 19434529, 19018447, 18417036, 20919320, 19022470, 19284031      20474192, 20173897, 22062026, 19385656, 19501299, 17274537, 20899461      19440586, 16887946, 19606174, 18436647, 17655240, 19023822, 19178851      19124589, 19805359, 19597583, 19155797, 19393542, 19050649    ---------------------------------------------------------------------- OPatch succeeded. [oracle@CKPT-DBaaS ~]$

We have gathered the required information and now we will proceed to apply patch directly from the cloud dashboard for the database service.

Before proceeding with patching cloud prompts for your approval once again to ensure, because it requires downtime and hence this step should consider when the downtime is accepted. You can leave a comment so that the job id tagged with comment to view for other users.

Once the comment entered and acknowledged that means we are ensuring to apply patch forcibly then again we can click on the patch button and then the job id will be created and oracle cloud handles the complete patching.

Now the patching is in progress and we can check the status on clicking the “Last Patch applied” section and it shows when the patching is started with the job id and the current status.

We can review the database and listener status while patching in progress from the console using putty ssh terminal. If we see below the listener and also the database are already down and that means of course patching is in progress.

[oracle@CKPT-DBaaS ~]$ ps -ef|grep pmon oracle    6848  1369  0 08:36 pts/1    00:00:00 grep pmon [oracle@CKPT-DBaaS ~]$ ps -ef|grep tns root        20     2  0 Jun26 ?        00:00:00 [netns] oracle    6850  1369  0 08:36 pts/1    00:00:00 grep tns [oracle@CKPT-DBaaS ~]$

Whole patching took for 25 minutes of overall process; the duration depends on each patch and the number of databases.

In span of 25 minutes all the tasks are performed

• Download required patches
• Apply the patch over Oracle Home
• Loading modified SQL
• Recompiling invalid objects

Further more information can be grabbed from the Patch history by clicking like below.

After the patching is completed we can verify the status from database level using the below query

The same information we can also gather from the database alert log and the contents are below.

Sat Jul 16 08:44:45 2016 AQPC started with pid=35, OS id=15902 Database Characterset for PDB$SEED is AL32UTF8 Opening pdb PDB$SEED (2) with no Resource Manager plan active ALTER PLUGGABLE DATABASE ALL OPEN Database Characterset for PSPDB is AL32UTF8 Opening pdb PSPDB (3) with no Resource Manager plan active Pluggable database PSPDB opened read write Completed: ALTER PLUGGABLE DATABASE ALL OPEN Starting background process CJQ0 Completed: ALTER DATABASE OPEN Sat Jul 16 08:44:48 2016 CJQ0 started with pid=36, OS id=16023 Sat Jul 16 08:44:49 2016 =========================================================== Dumping current patch information =========================================================== Patch Id: 19769480 Patch Description: Database Patch Set Update : 12.1.0.2.2 (19769480) Patch Apply Time: 2016-05-06 22:55:14 GMT+05:30 Bugs Fixed: 14643995,16359751,16870214,17835294,18250893,18288842,18354830, 18436647,18456643,18610915,18618122,18674024,18674047,18791688,18845653, 18849537,18885870,18921743,18948177,18952989,18964939,18964978,18967382, 18988834,18990693,19001359,19001390,19016730,19018206,19022470,19024808, 19028800,19044962,19048007,19050649,19052488,19054077,19058490,19065556, 19067244,19068610,19068970,19074147,19075256,19076343,19077215,19124589, 19134173,19143550,19149990,19154375,19155797,19157754,19174430,19174521, 19174942,19176223,19176326,19178851,19180770,19185876,19189317,19189525, 19195895,19197175,19248799,19279273,19280225,19289642,19303936,19304354, 19309466,19329654,19371175,19382851,19390567,19409212,19430401,19434529, 19439759,19440586,19468347,19501299,19518079,19520602,19532017,19561643, 19577410,19597439,19676905,19706965,19708632,19723336,19769480,20074391, 20284155  Patch Id: 20299023 Patch Description: Database Patch Set Update : 12.1.0.2.3 (20299023) Patch Apply Time: 2016-05-06 22:56:42 GMT+05:30 Bugs Fixed: 16619249,17274537,18202441,18306996,18417036,18607546,18681056, 18856999,18909599,18940497,19012119,19018447,19023822,19035573,19065677, 19081128,19183343,19238590,19272708,19291380,19315691,19335438,19358317, 19385656,19393542,19487147,19512341,19524384,19536415,19547370,19597583, 19606174,19619732,19627012,19637186,19644859,19649152,19658708,19670108, 19684504,19687159,19730508,19791377,19805359,19841800,19865345,19873610, 19896336,19915271,19928926,19978542,20235511,20347562,20348653,20425790, 20440930  Patch Id: 20831110 Patch Description: Database Patch Set Update : 12.1.0.2.4 (20831110) Patch Apply Time: 2016-05-06 22:58:23 GMT+05:30 Bugs Fixed: 19284031,19307662,19399918,19699191,19703301,19989009,20093776, 20165574,20294666,20331945,20387265,20402832,20424899,20474192,20558005, 20657441,20746251,20899461,20919320,21225209  Patch Id: 21359755 Patch Description: Database Patch Set Update : 12.1.0.2.5 (21359755) Patch Apply Time: 2016-05-06 23:00:02 GMT+05:30 Bugs Fixed: 17365043,18411216,18743542,18966843,19032777,19243521,19468991, 19571367,19587324,19791273,20048359,20122715,20134339,20245930,20281121, 20361671,20397490,20413820,20441797,20471920,20476175,20688221,20925795, 21281532,21421886,21442094,21620471,22062026  Patch Id: 21948354 Patch Description: Database Patch Set Update : 12.1.0.2.160119 (21948354) Patch Apply Time: 2016-05-06 23:01:39 GMT+05:30 Bugs Fixed: 16887946,17551063,17655240,17890099,18799063,18799993,18886413, 18973548,19141838,19326908,19490948,19604659,19689979,19869255,19879746, 19902195,20101006,20173897,20318889,20446883,20509482,20588502,20618595, 20794034,20869721,20877664,20890311,20951038,21188532,21300341,21517440, 21526048,21668627,21756699,21787056,21875360,22092979  Patch Id: 22291127 Patch Description: Database Patch Set Update : 12.1.0.2.160419 (22291127) Patch Apply Time: 2016-05-06 23:03:25 GMT+05:30 Bugs Fixed: 13542050,16439813,16923858,18499088,18893947,18914624,18990023, 19258504,19333670,19354335,19450314,19591608,19639483,19676012,19721304, 19777862,19835133,19883092,19888853,19990037,20043616,20117253,20124446, 20356733,20373598,20447445,20466628,20596234,20711718,20825533,20831538, 20879889,20904530,20936905,20952966,21091431,21153266,21260431,21273804, 21315084,21329301,21744290,21756661,21756677,21847223,21899588,21915719, 21917884,22046677,22168163,22173980,22353199,22353346,22374754,22528741, 22762046  Patch Id: 22674709 Patch Description: Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016) Patch Apply Time: 2016-05-06 23:04:29 GMT+05:30 Bugs Fixed: 19153980,19176885,19223010,19231857,19245191,19623450,19699946, 19855285,19877336,19895326,19895362,19909862,20408829,20408866,20415564, 21047766,21047803,21068507,21188537,21555660,21566944,21566993,21811517, 22118835,22118851,22139226,22670385,22670413,22674709  Patch Id: 23192060 Patch Description: Patch Apply Time: 2016-05-06 23:05:07 GMT+05:30 Bugs Fixed: 19366375,19665921,19770063,21281607,21470120,21923026,23072137 =========================================================== Sat Jul 16 08:44:49 2016 db_recovery_file_dest_size of 6144 MB is 48.22% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Sat Jul 16 08:45:38 2016 SERVER COMPONENT id=UTLRP_BGN: timestamp=2016-07-16 08:45:38 Container=CDB$ROOT Id=1 diag_adl:SERVER COMPONENT id=UTLRP_END: timestamp=2016-07-16 08:45:41 Container=CDB$ROOT Id=1 diag_adl: diag_adl:XDB installed. diag_adl: diag_adl:XDB initialized. Sat Jul 16 08:54:28 2016 Resize operation completed for file# 1, old size 839680K, new size 849920K 

We have crosschecked the patching and the loading modified sql from alert log and we finally crosscheck the OPatch inventory to know the list of patches are applied.

[oracle@CKPT-DBaaS ~]$ export PATH=$ORACLE_HOME/OPatch:$PATH [oracle@CKPT-DBaaS ~]$ OPatch lsinventory Oracle Interim Patch Installer version 12.1.0.1.10 Copyright (c) 2016, Oracle Corporation.  All rights reserved.   Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1 Central Inventory : /u01/app/oraInventory    from           : /u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc OPatch version    : 12.1.0.1.10 OUI version       : 12.1.0.2.0 Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/OPatch/OPatch2016-07-16_15-37-52PM_1.log  Lsinventory Output file location :   /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/OPatch/lsinv/lsinventory2016-07-16_15-37-52PM.txt  ----------------------------------------------------------------------Local Machine Information:: Hostname: CKPT-DBaaS.compute-nassyambasha.oraclecloud.internal ARU platform id: 226 ARU platform description:: Linux x86-64  Installed Top-level Products (1):  Oracle Database 12c                                                  12.1.0.2.0 There are 1 products installed in this Oracle Home.   Interim patches (3) :  Patch  23192060     : applied on Fri May 06 23:05:07 IST 2016 Unique Patch ID:  20172670    Created on 6 May 2016, 04:54:57 hrs PST8PDT    Bugs fixed:      19770063, 19366375, 21923026, 19665921, 23072137, 21281607, 21470120    This patch overlays patches:      22291127    This patch needs patches:      22291127    as prerequisites  Patch  22674709     : applied on Fri May 06 23:04:29 IST 2016 Unique Patch ID:  20057886 Patch description:  "Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)"    Created on 5 Apr 2016, 08:56:18 hrs PST8PDT    Bugs fixed:      22674709, 19176885, 22670413, 19623450, 21566993, 19153980, 19855285      20415564, 21555660, 21047803, 21188537, 22670385, 19699946, 22139226      19909862, 21811517, 19223010, 21068507, 19895326, 19877336, 22118851      22118835, 20408829, 21047766, 19231857, 19895362, 19245191, 20408866, 21566944  Patch  22291127     : applied on Fri May 06 23:03:25 IST 2016 Unique Patch ID:  19694308 Patch description:  "Database Patch Set Update : 12.1.0.2.160419 (22291127)"    Created on 6 Apr 2016, 03:46:21 hrs PST8PDT Sub-patch  21948354; "Database Patch Set Update : 12.1.0.2.160119 (21948354)" Sub-patch  21359755; "Database Patch Set Update : 12.1.0.2.5 (21359755)" Sub-patch  20831110; "Database Patch Set Update : 12.1.0.2.4 (20831110)" Sub-patch  20299023; "Database Patch Set Update : 12.1.0.2.3 (20299023)" Sub-patch  19769480; "Database Patch Set Update : 12.1.0.2.2 (19769480)"    Bugs fixed:      21847223, 19189525, 19075256, 19141838, 22762046, 20117253, 19865345      19791273, 19280225, 18845653, 19248799, 20951038, 19243521, 21756699      18988834, 21281532, 19238590, 18921743, 20245930, 18799063, 19134173      20373598, 19571367, 20476175, 20925795, 19018206, 20711718, 20387265      20509482, 20588502, 19149990, 18849537, 17551063, 18886413, 19183343      19703301, 21917884, 19001390, 18202441, 19189317, 19644859, 19358317      19390567, 19279273, 19706965, 22528741, 19068970, 20825533, 19619732      18607546, 20348653, 19649152, 19670108, 18940497, 18948177, 19315691      19676905, 18964978, 19035573, 20165574, 19176326, 20413820, 20558005      19176223, 19532017, 20904530, 20134339, 19450314, 22353346, 19074147      18411216, 20361671, 20425790, 18966843, 21329301, 20294666, 19333670      19195895, 19307662, 19371175, 20043616, 19154375, 20124446, 18914624      19468991, 19883092, 19382851, 19520602, 19174521, 21875360, 19676012      19326908, 19658708, 19591608, 20093776, 20618595, 21787056, 17835294      19721304, 19791377, 19068610, 22173980, 20746251, 20048359, 19143550      19185876, 19627012, 20281121, 19577410, 22092979, 19001359, 19518079      18610915, 19490948, 18674024, 18306996, 19309466, 19081128, 19915271      20122715, 21188532, 18791688, 20284155, 20890311, 21442094, 20596234      18973548, 19303936, 19597439, 20936905, 20235511, 19888853, 21756677      18964939, 19354335, 19430401, 19044962, 19639483, 21153266, 22353199      19409212, 20657441, 19879746, 19684504, 19024808, 21260431, 21756661      18799993, 20877664, 19028800, 20879889, 19065556, 19723336, 19077215      19604659, 21421886, 19524384, 18288842, 19048007, 19689979, 20446883      18952989, 16870214, 19928926, 19835133, 21526048, 20466628, 19197175      19180770, 19902195, 20318889, 19730508, 19012119, 19067244, 20074391      20356733, 14643995, 19512341, 19841800, 20331945, 19587324, 19547370      19065677, 21225209, 19637186, 20397490, 18967382, 19174430, 19054077      18674047, 19536415, 19708632, 21091431, 19289642, 22168163, 20869721      19335438, 19258504, 20447445, 17365043, 18856999, 19468347, 20471920      19869255, 21620471, 16359751, 18990693, 17890099, 19769480, 19439759      19272708, 18990023, 19978542, 20402832, 20101006, 21300341, 19329654      19873610, 21744290, 13542050, 21517440, 21668627, 19304354, 19052488      20794034, 19291380, 21915719, 18681056, 20952966, 19896336, 19076343      19561643, 19990037, 18618122, 20440930, 18456643, 19699191, 19487147      18909599, 20831538, 18250893, 19016730, 18743542, 20347562, 16619249      18354830, 19777862, 19687159, 19174942, 20424899, 19989009, 20688221      21899588, 20441797, 19157754, 19032777, 19058490, 19399918, 18885870      19434529, 21273804, 19018447, 18893947, 16923858, 18417036, 20919320      19022470, 19284031, 20474192, 22046677, 20173897, 22062026, 19385656      19501299, 17274537, 20899461, 21315084, 19440586, 22374754, 16887946      19606174, 18436647, 17655240, 19023822, 19178851, 19124589, 16439813      19805359, 19597583, 18499088, 19155797, 19050649, 19393542 ---------------------------------------------------------------------- OPatch succeeded. [oracle@CKPT-DBaaS ~]$

Conclusion

We’ve seen the step by step procedure to apply the patch for database service on cloud and the major challenges of applying patches from on-premises and the advantages of patching of cloud database with just single click.

References

https://docs.oracle.com/cloud/latest/dbcs_dbaas/CSDBI/GUID-C843AC5A-0C1C-4475-8EEC-3A13D1DD92B2.htm#CSDBI-GUID-C843AC5A-0C1C-4475-8EEC-3A13D1DD92B2