Donnerstag, 24. Oktober 2019

Oracle patches - a patchfiles anatomy

In my previous post I explained some basics about IDs of platforms, products and similar and where these patches can be obtained.

Today my focus is on the content of some patchfiles.

As I'm basically a DBA, my patchfiles are regarding database binaries. Patchfiles for other software products might differ, but I hope their main structure is similar.

A very simple patch is p26007010_12102180417ProactiveBP_Linux-x86-64.zip
it contains these files:
.
├── 26007010
│   ├── etc
│   │   └── config
│   │       ├── actions.xml
│   │       └── inventory.xml
│   ├── files
│   │   └── lib
│   │       └── libserver12.a
│   │           ├── ktsla.o
│   │           └── ktsld.o
│   └── README.txt
└── PatchSearch.xml

Let's start with PatchSearch.xml. This file is outside of the directory with patch number.

<!-- This file contain patch Metadata -->
<results md5_sum="911913c83e833a5b6cd893365066141c">
  <generated_date in_epoch_ms="1538413221000">2018-10-01 17:00:21</generated_date>
  <patch has_prereqs="n" has_postreqs="n" is_system_patch="n"> 
    <bug> 
      <number>26007010</number>
      <abstract><![CDATA[DATABASE GREW EVEN THOUGH RESOURCES DELETED]]></abstract>
    </bug> 
    <name>26007010</name>
    <type>Patch</type>
    <status>Available</status>
    <access id="m">Open access</access> 
    <url> 
      <patch_readme host="https://updates.oracle.com"><![CDATA[/Orion/Services/download?type=readme&aru=22425588]]></patch_readme> 
      <patch_details><![CDATA[/download/26007010.html]]></patch_details>
    </url> 
    <request_id>22425588</request_id>
    <product id="9480" bugdb_id="5"><![CDATA[Oracle Database Family]]></product> 
    <release id="6000000000093018041701" name="12.1.0.2.180417ProactiveBP" platform_patch_not_required="Y" psu="Y" cc="Y"><![CDATA[Oracle 12.1.0.2.180417 Proactive BP]]></release> 
    <platform id="226" bugdb_id="226"><![CDATA[Linux x86-64]]></platform> 
    <language id="0" iso_code="EN"><![CDATA[American English]]></language> 
    <translations_available>No</translations_available> 
    <classification id="174">General</classification> 
    <patch_classification id="174">General</patch_classification> 
    <support_level id="G">General Support</support_level> 
    <entitlements> 
      <entitlement code="SW"/> 
    </entitlements> 
    <patch_relationships> 
      <related_patch> 
        <bug> 
          <number>27338029</number>
          <abstract><![CDATA[DATABASE BUNDLE PATCH 12.1.0.2.180417]]></abstract>
        </bug> 
        <relation_types> 
          <relation_type>Prereq</relation_type> 
        </relation_types> 
        <name>27338029</name>
        <type>Patch</type>
        <patch_details><![CDATA[/Orion/Services/search?bug=27338029&release=600000000009300]]></patch_details>
      </related_patch> 
    </patch_relationships> 
    <fixes_bugs truncated="no"> 
      <bug> 
        <number>26007010</number>
        <abstract><![CDATA[DATABASE GREW EVEN THOUGH RESOURCES DELETED]]></abstract>
      </bug> 
    </fixes_bugs> 
    <size>210076</size>
    <files> 
      <file> 
        <name>p26007010_12102180417ProactiveBP_Linux-x86-64.zip</name>
        <size>210076</size>
        <download_url host="https://updates.oracle.com"><![CDATA[/Orion/Services/download/p26007010_12102180417ProactiveBP_Linux-x86-64.zip?aru=22425588&patch_file=p26007010_12102180417ProactiveBP_Linux-x86-64.zip]]></download_url> 
        <digest type="SHA-256">ED9AE319DD9A6B149BFA66842C8AF03F853908B16B694781F78D0BC1D0E8071E</digest> 
        <digest type="SHA-1">F91E6F7525A09E8AEAC93FED6DC39DDA4382648B</digest> 
      </file> 
    </files> 
    <downloads_count>0</downloads_count>
    <updated_date in_epoch_ms="1538413182000">2018-10-01 16:59:42</updated_date> 
    <released_date in_epoch_ms="1521586681000">2018-03-20 22:58:01</released_date> 
  </patch> 
</results>

At the beginning this xml shows some generic information about the patch like it's product, release, platform. It also contains the bug it fixes and download filename. With previous post most of it's content should be easily readable.


The README.txt (and sometimes README.html) is this particular file admins should read, understand and follow. It contains directives how to check, if the patch is applicable, and how to apply it.


The next part bottom up is a directory structure files - lib - libserver12.a - ktsl*.o
As a rule of thumb these files will be somehow merged into the respective directories in the binaries home directory (ORACLE_HOME in the case of RDBMS).

More details can be found in etc - config directory:

the inventory.xml again contains some additional details about the patch:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<oneoff_inventory>
    <opack_version version="14.1.0.1.9"/>
    <patch_id number="26007010"/>
    <minimum_opatch_version>12.1.0.1.4</minimum_opatch_version>
    <unique_patch_id>22425588</unique_patch_id>
    <cannot_autorollback>false</cannot_autorollback>
    <date_of_patch day="1" month="Oct" time="09:59:37 hrs" year="2018" zone="PST8PDT"/>
    <base_bugs>
        <bug description="DATABASE GREW EVEN THOUGH RESOURCES DELETED" number="26007010"/>
    </base_bugs>
    <required_components>
        <component internal_name="oracle.rdbms" opt_req="O" version="12.1.0.2.0"/>
    </required_components>
    <os_platforms>
        <platform id="226" name="Linux x86-64"/>
    </os_platforms>
    <executables>
        <executable path="%ORACLE_HOME%/bin/oracle"/>
    </executables>
    <instance_shutdown>true</instance_shutdown>
    <instance_shutdown_message/>
    <online_rac_installable>true</online_rac_installable>
    <run_as_root>false</run_as_root>
    <sql_migrate>false</sql_migrate>
    <wls_prereq_oneoffs/>
    <prereq_oneoffs>
        <prereq oneoff_id="27338029"/>
    </prereq_oneoffs>
    <overlay_oneoffs>
        <overlay oneoff_id="27338029"/>
    </overlay_oneoffs>
    <patch_type sub_type="update" value="singleton"/>
    <patch_language value="en"/>
    <product_family value="db"/>
    <patching_model value="one-off"/>
    <auto>false</auto>
    <translatable>false</translatable>
    <application_dirs/>
    <patch_characteristics>
        <content_type value="generic"/>
    </patch_characteristics>
</oneoff_inventory>

whereas actions.xml describes what to do with the content of the patch:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<oneoff_actions>
    <oracle.rdbms opt_req="O" version="12.1.0.2.0">
        <archive backup_in_zip="false" name="libserver12.a" object_name="lib/libserver12.a/ktsla.o" path="%ORACLE_HOME%/lib" shaolue="6D6BA5355DB887694E210E53856F2CE8F779BB9E"/>
        <archive backup_in_zip="false" name="libserver12.a" object_name="lib/libserver12.a/ktsld.o" path="%ORACLE_HOME%/lib" shaolue="842C83B27C0517C0D938865C819F2090163A926F"/>
        <make change_dir="%ORACLE_HOME%/rdbms/lib" make_file="ins_rdbms.mk" make_target="ioracle"/>
    </oracle.rdbms>
</oneoff_actions>

In this case the 2 files in lib/libserver12.a  are copied to %ORACLE_HOME%/lib and a new libserver12.a (and the oracle binary) is newly generated by make -f ins_rdbms.mk ioracle.

Of course other patches can contain other actions. Their basic meaning should be guessable with some common sense.



When comparing previous patch to p26439748_12102180116ProactiveBP_Linux-x86-64.zip , we can see some important details:
.
├── 26439748
│   ├── etc
│   │   └── config
│   │       ├── actions.xml
│   │       └── inventory.xml
│   ├── files
│   │   └── lib
│   │       └── libserver12.a
│   │           └── ktsla.o
│   └── README.txt
└── PatchSearch.xml

actions.xml:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<oneoff_actions>
    <oracle.rdbms opt_req="O" version="12.1.0.2.0">
        <archive name="libserver12.a" object_name="lib/libserver12.a/ktsla.o" path="%ORACLE_HOME%/lib"/>
        <make change_dir="%ORACLE_HOME%/rdbms/lib" make_file="ins_rdbms.mk" make_target="ioracle"/>
    </oracle.rdbms>
</oneoff_actions>

We can easily see both patches touch ktsla.o. That's where they conflict and
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
shows
Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
...


To fix this conflict, let's have a look at p27860850_12102180417ProactiveBP_Linux-x86-64.zip:
.
├── 27860850
│   ├── etc
│   │   └── config
│   │       ├── actions.xml
│   │       └── inventory.xml
│   ├── files
│   │   └── lib
│   │       └── libserver12.a
│   │           ├── ktsla.o
│   │           └── ktsld.o
│   └── README.txt
└── PatchSearch.xml

PatchSearch.xml now contains
...
    <bug> 
      <number>27860850</number>
      <abstract><![CDATA[MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.180417 FOR BUGS 26007010 26439748]]></abstract>
    </bug> 
    <name>27860850</name>
    <type>Patch</type>
...
    <fixes_bugs truncated="no"> 
      <bug> 
        <number>26007010</number>
        <abstract><![CDATA[DATABASE GREW EVEN THOUGH RESOURCES DELETED]]></abstract>
      </bug> 
      <bug> 
        <number>26439748</number>
        <abstract><![CDATA[ENQ: TX - CONTENTION AND GC BUFFER BUSY ACQUIRE WAITS ON SECUREFILES IN 12.1]]></abstract>
      </bug> 
      <bug> 
        <number>26513067</number>
        <abstract><![CDATA[ASSIGNMENT OF INSTANCE AFFINITY FLAWED IN KTSLA_HBB_UPDATE_CACHE()]]></abstract>
      </bug> 
      <bug> 
        <number>27255377</number>
        <abstract><![CDATA[ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [KTEUPROPAGATETIME:CLSVIOL_KCBGCUR_9]]]></abstract>
      </bug> 
    </fixes_bugs> 
...

This particular merge patch contains fixes for 26007010 and 26439748 (and some others).


There are also bigger patches like p27486326_121020_Linux-x86-64.zip:
.
├── 27486326
│   ├── 26983807
│   │   ├── README.txt
│   │   ├── etc
│   │   │   ├── config
│   │   │   │   ├── actions.xml
│   │   │   │   └── inventory.xml
│   │   │   └── xml
│   │   │       ├── GenericActions.xml
│   │   │       └── ShiphomeDirectoryStructure.xml
│   │   └── files
│   │       ├── oc4j
│   │       │   └── ...
│   │       └── wlm
│   │           └── ...
│   ├── 27338013
│   │   ├── README.html
│   │   ├── README.txt
│   │   ├── automation
│   │   │   ├── ...
│   │   ├── custom
│   │   │   └── scripts
│   │   │       ├── post.txt
│   │   │       ├── pre.txt
│   │   │       └── prepatchverify.sh
│   │   ├── etc
│   │   │   ├── config
│   │   │   │   ├── actions.xml
│   │   │   │   └── inventory.xml
│   │   │   └── xml
│   │   │       ├── GenericActions.xml
│   │   │       └── ShiphomeDirectoryStructure.xml
│   │   └── files
│   │       ├── ...
│   ├── 27338020
│   │   ├── README.html
│   │   ├── README.txt
│   │   ├── automation
│   │   │   ├── bp1-auto-inplace-non-rolling-automation.xml
│   │   │   ├── bp1-auto-inplace-rolling-automation.xml
│   │   │   ├── bp1-auto-rollback-inplace-automation.xml
│   │   │   ├── bp1-auto-rollback-inplace-non-rolling-automation.xml
│   │   │   ├── bp1-inplace-automation.xml
│   │   │   ├── bp1-inplace-non-rolling-automation.xml
│   │   │   ├── bp1-out-of-place-automation.xml
│   │   │   ├── bp1-out-of-place-non-rolling-automation.xml
│   │   │   ├── bp1-out-of-place-non-rolling-switchback.xml
│   │   │   ├── bp1-out-of-place-switchback.xml
│   │   │   ├── bp1-rollback-inplace-automation.xml
│   │   │   ├── bp1-rollback-inplace-non-rolling-automation.xml
│   │   │   └── messages.properties
│   │   ├── custom
│   │   │   └── scripts
│   │   │       ├── postpatch.sh
│   │   │       └── prepatch.sh
│   │   ├── etc
│   │   │   ├── config
│   │   │   │   ├── actions.xml
│   │   │   │   └── inventory.xml
│   │   │   └── xml
│   │   │       ├── GenericActions.xml
│   │   │       └── ShiphomeDirectoryStructure.xml
│   │   └── files
│   │       ├── ...
│   ├── 27338029
│   │   ├── 20243804
│   │   │   ├── etc
│   │   │   │   └── config
│   │   │   │       ├── actions.xml
│   │   │   │       └── inventory.xml
│   │   │   └── files
│   │   │       ├── ...
│   │   ├── 20415006
│   │   │   ├── etc
│   │   │   │   └── config
│   │   │   │       ├── actions.xml
│   │   │   │       └── inventory.xml
│   │   │   └── files
│   │           ├── ...
│   ├── 20594149
│   │   │   ├── etc
│   │   │   │   └── config
│   │   │   │       ├── actions.xml
│   │   │   │       └── inventory.xml
│   │   │   └── files
│   │   │       ├── ...
│   │   ├── README.html
│   │   └── README.txt
│   │   ├── 27338029
│   │   │   ├── etc
│   │   │   │   └── config
│   │   │   │       ├── actions.xml
│   │   │   │       ├── composite.xml
│   │   │   │       └── inventory.xml
│   │   │   └── files
│   │   │       ├── ...
│   ├── README.html
│   ├── README.txt
│   ├── automation
│   │   ├── bp1-auto-inplace-non-rolling-automation.xml
│   │   ├── ...
│   │   └── messages.properties
│   └── bundle.xml
└── PatchSearch.xml

979 directories, 7578 files

The first obvious diference are the patch numbers inside of 27486326, and these patch number contain their own well known directory structure. And 27338029 again has it's sub patches.
But let's start with known PatchSearch.xml:

<!-- This file contain patch Metadata -->
<results md5_sum="0e8f74b3cdd19ff9af42650d53fbfafd">
  <generated_date in_epoch_ms="1523969284000">2018-04-17 12:48:04</generated_date>
  <patch has_prereqs="n" has_postreqs="n" is_system_patch="y"> 
    <bug> 
      <number>27486326</number>
      <abstract><![CDATA[DATABASE PROACTIVE BUNDLE PATCH 12.1.0.2.180417]]></abstract>
    </bug> 
    <name>27486326</name>
    <type>Patch</type>
    <status>Available</status>
    <access id="m">Open access</access> 
    <url> 
      <patch_readme host="https://updates.oracle.com"><![CDATA[/Orion/Services/download?type=readme&aru=22118389]]></patch_readme> 
      <patch_details><![CDATA[/download/27486326.html]]></patch_details>
    </url> 
    <request_id>22118389</request_id>
    <product id="9480" bugdb_id="5"><![CDATA[Oracle Database Family]]></product> 
    <release id="600000000009300" name="12.1.0.2.0" platform_patch_not_required="Y" cc="Y"><![CDATA[Oracle 12.1.0.2.0]]></release> 
    <platform id="226" bugdb_id="226"><![CDATA[Linux x86-64]]></platform> 
    <language id="0" iso_code="EN"><![CDATA[American English]]></language> 
    <translations_available>No</translations_available> 
    <classification id="174">General</classification> 
    <patch_classification id="174">General</patch_classification> 
    <support_level id="G">General Support</support_level> 
    <entitlements> 
      <entitlement code="SW"/> 
    </entitlements> 
    <fixes_bugs truncated="no"> 
      <bug> 
        <number>13498243</number>
        <abstract><![CDATA[CALCULATE AVAILABLE SCN HEADROOM]]></abstract>
      </bug> 
      <bug> 
        <number>13542050</number>
        <abstract><![CDATA[USE OF KGL MUTEXES MIGHT BLOCK ON BOGUS MUTEX HOLDER]]></abstract>
      </bug> 
      <bug> 
        <number>13640676</number>
        <abstract><![CDATA[CANNOT EXCHANGE TABLE PARTITION WITH TEXT INDEX CREATED BEFORE 11.2.0.2 UPGRADE]]></abstract>
      </bug> 
...
    </fixes_bugs> 
    <size>2531696443</size>
    <files> 
      <file> 
        <name>p27486326_121020_Linux-x86-64.zip</name>
        <size>2531696443</size>
        <download_url host="https://updates.oracle.com"><![CDATA[/Orion/Services/download/p27486326_121020_Linux-x86-64.zip?aru=22118389&patch_file=p27486326_121020_Linux-x86-64.zip]]></download_url> 
        <digest type="MD5">86424FF991F46750CBFA9DBBDC27E208</digest> 
        <digest type="SHA-256">F16D2BEF1B02E0132C75ADB3EF176D3DD30C32FBC8D40F35EA68457E918648B7</digest> 
        <digest type="SHA-1">7DD310BA943EED216E905D44FF49F5FD9C952DBF</digest> 
      </file> 
    </files> 
    <downloads_count>0</downloads_count>
    <updated_date in_epoch_ms="1523969071000">2018-04-17 12:44:31</updated_date> 
    <released_date in_epoch_ms="1518611278000">2018-02-14 12:27:58</released_date> 
  </patch> 
</results>

So it looks exactly the same and shows no evidence of the more complex structure inside of 27486326.

The next file is 27486326/bundle.xml:
<system_patch_bundle_xml type_version="2.0" bundle_type="ENGSYSTEM" patch_abstract="sample EsysPatch description" patch_id="27486326" unique_patch_id="22118389">
    <system urm_id="9999" type="oracle_exadata_dbsys">
        <product qpart_id="5" aru_id="9480">Oracle Database</product>
        <release urm_id="18073" aru_id="600000000009300">Oracle 12.1.0.2.0</release>
    </system>
    <subpatches>
        <subpatch patch_type="SINGLETON" patching_tool="OPATCH" location="26983807" platform="226" patch_id="26983807" unique_patch_id="21704746">
            <target_types>
                <target_type version="12.1.0.2.0" type="has">
                    <product qpart_id="5" aru_id="9480">Oracle Database</product>
                    <release urm_id="18086" aru_id="600000000009300">Oracle 12.1.0.2.0</release>
                </target_type>
                <target_type version="12.1.0.2.0" type="cluster">
                    <product qpart_id="5" aru_id="9480">Oracle Database</product>
                    <release urm_id="21227" aru_id="600000000009300">Oracle 12.1.0.2.0</release>
                </target_type>
            </target_types>
        </subpatch>
...
        <subpatch patch_type="SINGLETON" patching_tool="OPATCH" location="27338020" platform="226" patch_id="27338020" unique_patch_id="21965858">
            <target_types>
                <target_type version="12.1.0.2.0" type="has">
                    <product qpart_id="5" aru_id="9480">Oracle Database</product>
                    <release urm_id="18086" aru_id="600000000009300">Oracle 12.1.0.2.0</release>
                </target_type>
                <target_type version="12.1.0.2.0" type="oracle_database">
                    <product qpart_id="5" aru_id="9480">Oracle Database</product>
                    <release urm_id="18073" aru_id="600000000009300">Oracle 12.1.0.2.0</release>
                </target_type>
                <target_type version="12.1.0.2.0" type="rac_database">
                    <product qpart_id="5" aru_id="9480">Oracle Database</product>
                    <release urm_id="18083" aru_id="600000000009300">Oracle 12.1.0.2.0</release>
                </target_type>
                <target_type version="12.1.0.2.0" type="cluster">
                    <product qpart_id="5" aru_id="9480">Oracle Database</product>
                    <release urm_id="21227" aru_id="600000000009300">Oracle 12.1.0.2.0</release>
                </target_type>
            </target_types>
        </subpatch>
...  
    </subpatches>
    <automation>
        <hash>dd55bb3aef5c9dbe72052b3109725ebac5db922c</hash>
        <oplan_version>11.2.0.2.5</oplan_version>
    </automation>
    <fvalue>
        <min_opatch_version>12.1.0.1.4</min_opatch_version>
        <value>a93be317becb06265c84897fa024a3481f9c3bd5</value>
        <function>3</function>
    </fvalue>
</system_patch_bundle_xml>

It explains the subpatches and their targets.

While 26983807 has only 2 targets has and cluster, 27338020 also has targets oracle_database and rac_database. In comparison the screenshot from corresponding README.html.

The directory 27486326/27338029 is slightly different from others: It contains additional patches, but the directory structure etc/config is missing.
Instead there is a sub-patch with the same name and this one has a little gem in 27338029.

composite.xml:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<composite>
    <sequence>
        <patch id="20243804"/>
        <patch id="20415006"/>
        <patch id="20594149"/>
        <patch id="20788771"/>
        <patch id="20950328"/>
        <patch id="21125181"/>
        <patch id="21359749"/>
        <patch id="21527488"/>
        <patch id="21694919"/>
        <patch id="21949015"/>
        <patch id="22806133"/>
        <patch id="23144544"/>
        <patch id="24340679"/>
        <patch id="24732088"/>
        <patch id="25397136"/>
        <patch id="25869760"/>
        <patch id="26609798"/>
        <patch id="26717470"/>
        <patch id="26925263"/>
        <patch id="27338029"/>
    </sequence>
    <replaces_upis>
        <upi>18350083</upi>
        <upi>18703022</upi>
        <upi>18977826</upi>
        <upi>19194568</upi>
        <upi>19553095</upi>
        <upi>19694308</upi>
        <upi>20464632</upi>
        <upi>20683584</upi>
        <upi>20904347</upi>
        <upi>21099266</upi>
        <upi>21385018</upi>
        <upi>21481899</upi>
        <upi>21602269</upi>
        <upi>21850549</upi>
        <upi>22036385</upi>
    </replaces_upis>
    <bundle series="DBBP" id="180417"/>
    <replaces_bundle series="PSU" id="180417"/>
</composite>

The Patch IDs mentioned here are all the other directories in 27486326/27338029, with their own well known structure. The upi (Unique Patch ID) is identical to aru from previous post and
by using one of this arus to get the corresponding readme ( https://updates.oracle.com/Orion/Services/download?type=readme&aru=18350083 ) this uip/aru leads to Patch 19769480 - Database Patch Set Update 12.1.0.2.2 (Includes CPUJan2015).
As so often, when the details are clear, most things are obvious: this Bundle Patch replaces all previous BPs.


That should be sufficient information for one post, for some additional information please read Frits Hooglands opatch investigations.

Montag, 21. Oktober 2019

Oracle patches - some basics and good-to-know information

Oracle Patches can occur very complicated and confusing. Most of the time they are zip files with strange and sometimes inconsistent naming convention.
As these patches are processed by a program called opatch (which also can be obtained as a patch - but it does not follow the same structure as regular patches).

Back to basics: In patches words like product, platform or language are used. They are all IDs and their "data dictionary" can easily be obtained. There are these links:
products https://updates.oracle.com/Orion/Services/metadata?table=aru_products
releases https://updates.oracle.com/Orion/Services/metadata?table=aru_releases
platforms https://updates.oracle.com/Orion/Services/metadata?table=aru_platforms
languages https://updates.oracle.com/Orion/Services/metadata?table=aru_languages
product_groups https://updates.oracle.com/Orion/Services/metadata?table=aru_product_groups
product_releases https://updates.oracle.com/Orion/Services/metadata?table=aru_product_releases
component_releases https://updates.oracle.com/Orion/Services/metadata?table=aru_component_releases
aru targets https://updates.oracle.com/Orion/Services/metadata?table=aru_targets
components https://updates.oracle.com/Orion/Services/components?ctype=product,release,platform,platform_version,wildcard-release,config-group,release-component,code_list
certifications https://updates.oracle.com/Orion/Services/components?ctype=certification
patch recommendationshttps://updates.oracle.com/Orion/Services/recommended_patches
ARU stands for Automated Release Update.

All these files can also obtained in a single zip file: https://updates.oracle.com/download/em_catalog.zip
The name of the zip file also shows it's origin purpose: provide Enterprise Managers recommended patches, download etc. with sufficient information. Some parts like platforms and languages are quite stable, others like releases and  recommendations, change regularly.

recommendations brings uns closer to real patches. it starts with
<results md5_sum="212e2bc3336d1aa3e41074e796201aa9">
  <generated_date in_epoch_ms="1571568490000">2019-10-20 10:48:10</generated_date>
  <patches md5_sum="d58c2ef61a596dd7bdef85b61cd06861">

followed by entries for each patch like
<patch uid="3d4a1929407b7e05430e2fd5bbc18481" has_prereqs="n" has_postreqs="n" is_system_patch="n">
<bug>
        <number>30049606</number>
        <abstract><![CDATA[WINDOWS DB BUNDLE PATCH 12.1.0.2.191015]]></abstract>
      </bug>
      <name>30049606</name>
      <type>Patch</type>
      <psu_bundle>Oracle 12.1.0.2.191015 for Windows</psu_bundle>
      <status>Available</status>
      <access id="m">Open access</access>
      <url>
        <patch_readme host="https://updates.oracle.com"><![CDATA[/Orion/Services/download?type=readme&aru=23166562]]></patch_readme>
        <patch_details><![CDATA[/download/30049606.html]]></patch_details>
      </url>
      <request_id>23166562</request_id>
      <urm_components>
        <qparts ctype_id="201">
          <qpart cid="32615" version="Q5"><![CDATA[Oracle Database - Enterprise Edition]]></qpart>
          <qpart cid="42353" version="Q790"><![CDATA[Oracle Server - Standard Edition]]></qpart>
        </qparts>
        <urm_releases ctype_id="5">
          <urm_release cid="86176" version="12.1.0.2.0"><![CDATA[Oracle Database]]></urm_release>
        </urm_releases>
      </urm_components>
      <product id="9480" bugdb_id="5"><![CDATA[Oracle Database Family]]></product>
      <release id="600000000009300" name="12.1.0.2.0" platform_patch_not_required="Y" cc="Y"><![CDATA[Oracle 12.1.0.2.0]]></release>
      <platform id="912" bugdb_id="912"><![CDATA[Microsoft Windows (32-bit)]]></platform>
      <language id="0" iso_code="EN"><![CDATA[American English]]></language>
      <translations_available>No</translations_available>
      <classification id="185">Security</classification>
      <patch_classification id="185">Security</patch_classification>
      <life_cycle id="175">Recommended</life_cycle>
      <support_level id="E">Extended Support</support_level>
      <entitlements>
        <entitlement code="EXS"/>
      </entitlements>
      <target_types>
        <target_type>
          <aru_target><![CDATA[All Database]]></aru_target>
          <em_tag></em_tag>
        </target_type>
      </target_types>
      <fixes_bugs truncated="no">
        <bug>
          <number>12681464</number>
          <abstract><![CDATA[UPDATE STATEMENT  FAILED - ORA7445 [KGGSLDESTROY] ERROR]]></abstract>
        </bug>
        <bug>
          <number>12799826</number>
          <abstract><![CDATA[LOCK TABLE FOR INDEX FAILS WITH ORA-600 [KKBKAUXBLL0]]]></abstract>
        </bug>
... many more
      </fixes_bugs>
      <size>317964815</size>
      <files>
        <file>
          <name>p30049606_121020_WINNT.zip</name>
          <size>317964815</size>
          <download_url host="https://updates.oracle.com"><![CDATA[/Orion/Services/download/p30049606_121020_WINNT.zip?aru=23166562&patch_file=p30049606_121020_WINNT.zip]]></download_url>
          <digest type="SHA-1">784196EE41085E633F1C10C5F12B47717232C3C5</digest>
          <digest type="SHA-256">849D03DCBB4EDCD2F08474371309470E9D3AE172E4BD7296453F31AEAE40E9D7</digest>
        </file>
      </files>
      <updated_date in_epoch_ms="1571299461000">2019-10-17 08:04:21</updated_date>
      <released_date in_epoch_ms="1570628127000">2019-10-09 13:35:27</released_date>
    </patch>        

Many entries in this XML can be interpreted by the information provided in em_catalog.zip. For each patch there is a download link: https://updates.oracle.com/Orion/Services/download/p30049606_121020_WINNT.zip?aru=23166562&patch_file=p30049606_121020_WINNT.zip


As there are many patches which are not mentioned in recommendations, and also for them there is a link to get an equal XML regarding this patch:
https://updates.oracle.com/Orion/Services/search?bug=<patch_number>

Even the URL suggests it is a search for a BUG, it's a search for a PATCH (as a PATCH can contain fixes for multiple BUGs).

If the same information is required in a HTML form, the URL is
https://updates.oracle.com/Orion/PatchDetails/process_form?patch_num=<patch_number>

Donnerstag, 10. Oktober 2019

OGB Appreciation Day : manipulating execution plans

Tims call for OGB Appreciation Day 2019 (#ThanksOGB) comes in time this year.

Even unemployed at the moment, I had a chance to help a friend with some problematic SQL statements.

The statements are not that important at all but he possibilities we had to address the problems were great!


First of all an existing execution plan, together with session stats, ASH/AWR and in one case SQLTRACE. None of these sources of information shows all details we required, but all together provided sufficient information to understand the problem.
The probably most comprehensive way to get all these information (except tracing) is sqld360.

Next of course is the system of hints.
There are many discussions if hints are a good and bad thing. It's not me to argue it here.
I appreciate hints exist in Oracle RDBMS as they can be used if arguments in their favor are strong.

Third the possibilities to apply hints to a SQL are huge. Even the statement itself can not be changed, there are stored outlines, SQL Profile, SQL Plan Baselines in SQL Plan Management, SQL Patch, and if some really dirty tricks are required, SQL Translation Framework and other mean tools can be used.

So there are sufficient tools available to improve a statements execution.


The sheer amount of possibilities might be overwhelming or even deterrent. In fact it's not that complicated and results can be achieved in short time.
Writing this, it's always easy to deliver shiny fancy solutions when standing on giants shoulders.
To give some examples, here is a random selection of articles I used during the latest investigations:

Occurence - Jonathan Lewis
Fixing SQL Plans: The hard way – Part 1 - Advait Deo
SQL Profiles - Kerry Osborne
buffer sorts - Jonathan Lewis
Oracle’s OPT_ESTIMATE Hint: Usage Guide - Christo Kutrovsky
Visual SQL Tuning (VST) - Kyle Hailey

I'm grateful for the possibilities we have to fix SQL executions, and I'm also grateful for all those people who share their knowledge, so I can learn from them!