{"id":5494,"date":"2023-10-22T18:48:00","date_gmt":"2023-10-22T16:48:00","guid":{"rendered":"https:\/\/promatis.com\/ch\/increasing-database-patching-performance\/"},"modified":"2023-10-23T08:58:04","modified_gmt":"2023-10-23T06:58:04","slug":"increasing-database-patching-performance","status":"publish","type":"post","link":"https:\/\/promatis-test.de\/ch\/increasing-database-patching-performance\/","title":{"rendered":"Increasing Database Patching Performance"},"content":{"rendered":"

[et_pb_section fb_built=\"1\" custom_padding_last_edited=\"on|tablet\" disabled_on=\"off|off|off\" admin_label=\"Sektion\" _builder_version=\"4.17.6\" _module_preset=\"default\" custom_padding=\"5vh||5vh||true|false\" custom_padding_tablet=\"5vh||5vh||true|false\" custom_padding_phone=\"5vh||5vh||true|false\" global_module=\"23\" locked=\"off\" global_colors_info=\"{}\" theme_builder_area=\"et_body_layout\"][et_pb_row column_structure=\"1_4,3_4\" _builder_version=\"4.17.6\" _module_preset=\"default\" custom_margin=\"||0px||false|false\" global_colors_info=\"{}\" theme_builder_area=\"et_body_layout\"][et_pb_column type=\"1_4\" _builder_version=\"4.17.6\" _module_preset=\"default\" global_colors_info=\"{}\" theme_builder_area=\"et_body_layout\"][et_pb_image src=\"https:\/\/promatis.com\/wp-content\/uploads\/2022\/07\/johannes-michler.png\" alt=\"Johannes Michler PROMATIS Horus Oracle\" title_text=\"johannes-michler\" _builder_version=\"4.20.2\" _module_preset=\"default\" width=\"90%\" custom_margin=\"0vh||0vh||true|false\" border_radii=\"on|516px|516px|516px|516px\" global_colors_info=\"{}\" theme_builder_area=\"et_body_layout\"][\/et_pb_image][\/et_pb_column][et_pb_column type=\"3_4\" _builder_version=\"4.17.6\" _module_preset=\"default\" global_colors_info=\"{}\" theme_builder_area=\"et_body_layout\"][et_pb_text ul_type=\"square\" _builder_version=\"4.20.2\" _module_preset=\"default\" text_font=\"Open Sans||||||||\" text_font_size=\"17px\" text_line_height=\"1.6em\" link_font=\"Open Sans||||on|||gcid-0becd5ff-19fc-4653-a221-c8c75771a987|\" link_text_color=\"gcid-0becd5ff-19fc-4653-a221-c8c75771a987\" link_font_size=\"22px\" ul_font=\"Open Sans||||||||\" ul_font_size=\"17px\" ul_line_height=\"1.6em\" header_font=\"|600||on|||||\" header_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_font_size=\"47px\" header_line_height=\"1.2em\" header_2_font=\"|600||on|||||\" header_2_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_line_height=\"1.6em\" header_3_font=\"|600|||||||\" header_3_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_3_font_size=\"24px\" header_3_line_height=\"1.4em\" header_4_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_4_line_height=\"1.4em\" header_5_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_5_line_height=\"1.4em\" header_6_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_font_size=\"16px\" header_6_line_height=\"1.4em\" custom_margin=\"2vh||0px||false|false\" custom_padding=\"||||true|false\" text_font_size_tablet=\"20px\" text_font_size_phone=\"17px\" text_font_size_last_edited=\"on|tablet\" header_font_size_tablet=\"\" header_font_size_phone=\"28px\" header_font_size_last_edited=\"on|phone\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22,%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22,%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22%93,%22gcid-0becd5ff-19fc-4653-a221-c8c75771a987%22:%91%22link_text_color%22%93}\" theme_builder_area=\"et_body_layout\"]<\/p>\n

Johannes Michler<\/a><\/div>\n

[\/et_pb_text][et_pb_text ul_type=\"square\" _builder_version=\"4.20.0\" _module_preset=\"default\" text_font=\"Open Sans||||||||\" text_font_size=\"17px\" text_line_height=\"1.6em\" link_font=\"Open Sans||||on||||\" link_text_color=\"#00A9A0\" ul_font=\"Open Sans||||||||\" ul_font_size=\"17px\" ul_line_height=\"1.6em\" header_font=\"|600||on|||||\" header_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_font_size=\"47px\" header_line_height=\"1.2em\" header_2_font=\"|600||on|||||\" header_2_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_line_height=\"1.6em\" header_3_font=\"|600|||||||\" header_3_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_3_font_size=\"24px\" header_3_line_height=\"1.4em\" header_4_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_4_line_height=\"1.4em\" header_5_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_5_line_height=\"1.4em\" header_6_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_font_size=\"16px\" header_6_line_height=\"1.4em\" custom_margin=\"1vh||0px||false|false\" custom_padding=\"||||true|false\" text_font_size_tablet=\"20px\" text_font_size_phone=\"17px\" text_font_size_last_edited=\"on|tablet\" header_font_size_tablet=\"\" header_font_size_phone=\"28px\" header_font_size_last_edited=\"on|phone\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22%93}\" theme_builder_area=\"et_body_layout\"]<\/p>\n


Executive Vice President<\/strong> \u2013\u00a0Head of Platforms\u00a0&\u00a0Development<\/p>\n

[\/et_pb_text][et_pb_text ul_type=\"square\" _builder_version=\"4.20.0\" _module_preset=\"default\" text_font=\"Open Sans||||||||\" text_text_color=\"gcid-0becd5ff-19fc-4653-a221-c8c75771a987\" text_font_size=\"22px\" text_line_height=\"1.6em\" link_font=\"Open Sans||||on||||\" link_text_color=\"#00A9A0\" ul_font=\"Open Sans||||||||\" ul_font_size=\"17px\" ul_line_height=\"1.6em\" header_font=\"|600||on|||||\" header_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_font_size=\"47px\" header_line_height=\"1.2em\" header_2_font=\"|600||on|||||\" header_2_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_line_height=\"1.6em\" header_3_font=\"|600|||||||\" header_3_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_3_font_size=\"24px\" header_3_line_height=\"1.4em\" header_4_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_4_line_height=\"1.4em\" header_5_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_5_line_height=\"1.4em\" header_6_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_font_size=\"16px\" header_6_line_height=\"1.4em\" custom_margin=\"5px||0px||false|false\" custom_padding=\"||||true|false\" text_font_size_tablet=\"20px\" text_font_size_phone=\"17px\" text_font_size_last_edited=\"on|tablet\" header_font_size_tablet=\"\" header_font_size_phone=\"28px\" header_font_size_last_edited=\"on|phone\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22,%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22,%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22%93,%22gcid-0becd5ff-19fc-4653-a221-c8c75771a987%22:%91%22text_text_color%22%93}\" theme_builder_area=\"et_body_layout\"]<\/i><\/a><\/i><\/a><\/i><\/a>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section][et_pb_section fb_built=\"1\" _builder_version=\"4.16\" _module_preset=\"default\" custom_padding=\"0vh||10vh||false|false\" global_colors_info=\"{}\" theme_builder_area=\"et_body_layout\"][et_pb_row use_custom_gutter=\"on\" _builder_version=\"4.17.3\" _module_preset=\"default\" custom_padding=\"0px||0px||true|false\" global_colors_info=\"{}\" theme_builder_area=\"et_body_layout\"][et_pb_column type=\"4_4\" _builder_version=\"4.16\" _module_preset=\"default\" global_colors_info=\"{}\" theme_builder_area=\"et_body_layout\"][et_pb_text _builder_version=\"4.21.0\" _module_preset=\"default\" background_enable_color=\"off\" custom_padding=\"0px||0px||true|false\" hover_enabled=\"0\" inline_fonts=\"Times New Roman\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22%93,%22gcid-0becd5ff-19fc-4653-a221-c8c75771a987%22:%91%22background_color%22%93}\" theme_builder_area=\"et_body_layout\" sticky_enabled=\"0\"]<\/p>\n

\"\"<\/p>\n

While applying the October 2023 Critical Patch Update (CPU) - that contains various critical fixes as can be found over there https:\/\/www.oracle.com\/security-alerts\/cpuoct2023.html<\/a> - I realized that the quarterly database patching takes longer quarter by quarter.<\/p>\n

My impression was that especially the analysis before the actual patching starts takes longer every time. This time when going from 19.20 to 19.21 the patching of DB RU, Grid Infrastructure and OJVM took more than an hour on the first development system where I've applied the patch (for a single instance database).<\/p>\n

Out-of-band patching<\/h2>\n

My first idea was to do out-of-band patching and thus just<\/p>\n

    \n
  1. copy the patched Oracle Home from DEV to TEST and later to PROD. This seemed especially reasonable, since DEV and TEST are regular copies from production anyway OR<\/li>\n
  2. Do a clean installation of a new 19c Oracle Home and patch that one directly to 19.21 (and add all other required one-off-patches).<\/li>\n<\/ol>\n

    While this is a supported approach, when looking into (1) I wondered, why the Oracle Home was so huge (more than 70 Gigabytes).<\/p>\n

    I realized, that large parts of the size came from $ORACLE_HOME\/.patch_storage:<\/p>\n

    [oracle@prod122db ~]$ du -hs $ORACLE_HOME\/.patch_storage\n32G \/u01\/install\/APPS\/19.0.0\/.patch_storage<\/pre>\n
    When looking into cleaning that up I stumbled onto a quite new option of Opatch: \"opatch util deleteinactivepatches\". The feature is described over there: \"OPatch 12.2.0.1.37+ Introduces a New Feature to Delete Inactive Patches in the ORACLE_HOME\/.patch_storage Directory (Doc ID 2942102.1)\"<\/p>\n
    Cleaning up inactive patches<\/h2>\n
    The tool has a \"diagnostic\" command, that just lists the patches that can be removed: \"$ORACLE_HOME\/OPatch\/opatch util listorderedinactivepatches\" The output in my case reveleas:<\/p>\n
    \"\"<\/p>\n
    Output of listorderedinactivepatches before cleanup<\/em><\/p>\n
    When run in \"real\" mode - with \"opatch util DeleteInactivePatches\" - it also lists those patches and then allows to delete them by confirming to do so.<\/p>\n
    The Support Notes suggest that older versions of OPatch require multiple consecutive runs of the cleanup. In my case though, everything got removed with a single shot (even though I have multiple chains with OJVM, DB RU and OCW):<\/p>\n
    [oracle@entw230928db ~]$ du -hs $ORACLE_HOME\/.patch_storage\n5.3G \/u01\/install\/APPS\/19.0.0\/.patch_storage<\/pre>\n
    This can also be confirmed with a second \"listorderedinactivepatches\":<\/p>\n
    [oracle@entw230928db ~]$ $ORACLE_HOME\/OPatch\/opatch util listorderedinactivepatches\nOracle Interim Patch Installer version 12.2.0.1.40\nCopyright (c) 2023, Oracle Corporation. All rights reserved.\n\nOracle Home : \/u01\/install\/APPS\/19.0.0\nCentral Inventory : \/u01\/install\/APPS\/oraInventory\nfrom : \/u01\/install\/APPS\/19.0.0\/oraInst.loc\nOPatch version : 12.2.0.1.40\nOUI version : 12.2.0.7.0\nLog file location : \/u01\/install\/APPS\/19.0.0\/cfgtoollogs\/opatch\/opatch2023-10-22_18-17-06PM_1.log\n\nInvoking utility \"listorderedinactivepatches\"\nList Inactive patches option provided\n\nThe oracle home has the following inactive patch(es) and their respective overlay patches:\n\nThe number of RU chains is 3\n\n***** There are 1 inactive RU patches in chain 1\n-Inactive RU\/BP 35354406:OJVM RELEASE UPDATE: 19.20.0.0.230718 (35354406), installed on: Tue Aug 08 18:18:39 CEST 2023, with no overlays\n-Active RU\/BP 35648110:OJVM RELEASE UPDATE: 19.21.0.0.231017 (35648110), installed on: Thu Oct 19 20:03:50 CEST 2023, with no overlays\n\n***** There are 1 inactive RU patches in chain 2\n-Inactive RU\/BP 35320149:OCW RELEASE UPDATE 19.20.0.0.0 (35320149), installed on: Tue Aug 08 18:10:49 CEST 2023, with no overlays\n-Active RU\/BP 35655527:OCW RELEASE UPDATE 19.21.0.0.0 (35655527), installed on: Thu Oct 19 19:50:29 CEST 2023, with no overlays\n\n***** There are 1 inactive RU patches in chain 3\n-Inactive RU\/BP 35320081:Database Release Update : 19.20.0.0.230718 (35320081), installed on: Tue Aug 08 17:50:00 CEST 2023, with no overlays\n-Active RU\/BP 35643107:Database Release Update : 19.21.0.0.231017 (35643107), installed on: Thu Oct 19 19:20:53 CEST 2023, with no overlays\n\nOPatch succeeded.<\/pre>\n
    Performance impact on opatch apply<\/h2>\n
    After seeing \"Opatch Apply And Rollback Getting Slower After Oneoff Or RU Patch Numbers Increased (Doc ID 2946849.1)\" - where it is explained that the tool may also have a good impact on performance beside \"shrinking\" the Oracle Home I gave an apply another chance on my second testing instance. The instance is similar sized, and the 3 OPatch apply commands came down from almost 1.5 hours before the cleanup to 14 Minutes after the cleanup.<\/p>\n
    That was \"good enough\" for me so I did not pursue the Out-Of-Band Patching approach any further.<\/p>\n
    [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
    Johannes MichlerExecutive Vice President \u2013\u00a0Head of Platforms\u00a0&\u00a0Development While applying the October 2023 Critical Patch Update (CPU) - that contains various critical fixes as can be found over there https:\/\/www.oracle.com\/security-alerts\/cpuoct2023.html - I realized that the quarterly database patching takes longer quarter by quarter. My impression was that especially the analysis before the actual patching starts takes […]<\/p>\n","protected":false},"author":2,"featured_media":1243,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[23],"tags":[],"dipi_cpt_category":[],"class_list":["post-5494","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-techblog"],"_links":{"self":[{"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/posts\/5494"}],"collection":[{"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/comments?post=5494"}],"version-history":[{"count":0,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/posts\/5494\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/media\/1243"}],"wp:attachment":[{"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/media?parent=5494"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/categories?post=5494"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/tags?post=5494"},{"taxonomy":"dipi_cpt_category","embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/dipi_cpt_category?post=5494"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}