Mittwoch, 6. November 2019

Oracle Datapatch - out of place patches simplified

datapatch was introduced with Oracle 12.1 to simplify management of post-patching steps when a patch (or patchset) is applied.
As most software, it was written with best intentions but little knowledge how customers would use it.
One of these unforseen used case was parallel patching of multiple DBs in the same ORACLE_HOME at the same time.
Another problem was missing rollback files due to out of place patching.

Oracle addressed this in Bug 23170620.
The solution there is interesting:
the table sys.registry$sqlpatch is extended by a column patch_directory. It is a BLOB which contains the Zipped contents of patch directory.
By this patch, each database contains the sql files required to rollback a change done in the database. If datapatch does not find the proper rollback files in ORACLE_HOME/sqlpatch, it unzips the content of registry$sqlpatch.patch_directory into ORACLE_HGOME/sqlpatch and executes the scripts. For every new patch, a zip file is loaded into the table by datapatch.
With 18c an equal mechanism was introduced in sys.registry$sqlpatch_ru_info respectively DBA_REGISTRY_SQLPATCH_RU_INFO.

By it's own this feature is very nice. But it shows the importance of serious security settings.
Only SYS can write to these tables by default, and that should be limited for some good reasons:
If anyone can write to this table with evil intentions, he can create a zipfile with a content like
.
├── 1234.xml
├── 1234_apply.sql
├── 1234_rollback.sql
└── rollback_files
    └── rdbms
        └── admin
            ├── berx_bug1234_apply.sql
            └── berx_bug1234_rollback.sql

The XML contains some basic content
<?xml version="1.0" encoding="utf-8"?>
<sqlPatch ID="1234" uniquePatchID="5678" applicationPatch="NO" bundle="NO" jvm="NO" bundleSeries="NONE" bundleID="0"   startupMode="normal" estimatedTime="5">
  <patchDescription>berx' datapatch-escape</patchDescription>
  <component>CATALOG</component>
  <component>CATPROC</component>
  <sqlFiles>
    <file mode="rollback" new="yes" estimatedTime="5" component="CATALOG">rdbms/admin/berx_bug1234_rollback.sql</file>
    <file mode="apply" new="yes" estimatedTime="5" component="CATALOG">rdbms/admin/berx_bug1234_apply.sql</file>
  </sqlFiles>
</sqlPatch>

and the 1234_rollback.sql file can contain:
SET SERVEROUTPUT ON

REM Apply script for patch 1234/5678
DEFINE description = &1
DEFINE logfiledir = &2
DEFINE flags = &3
DEFINE bundle_series = &4

COLUMN sqlpatch_logfile NEW_VALUE full_logfile


SELECT '&logfiledir' || '1234_apply_' ||
       CASE WHEN (sys_context('userenv', 'cdb_name') IS NULL) 
            THEN name
            ELSE name || '_' || replace(sys_context('userenv', 'con_name'), '$')
             END || TO_CHAR(systimestamp, '_YYYYMonDD_HH24_MI_SS') ||
                    '.log' AS sqlpatch_logfile
  FROM v$database;

SPOOL &full_logfile

SET PAGESIZE 0
SELECT 'Starting rollback for patch 1234/5678 on ' ||
       SYSTIMESTAMP FROM dual;
SET PAGESIZE 10

BEGIN
    dbms_sqlpatch.patch_initialize(p_patch_id      => 1234,
                                   p_patch_uid     => 5678,
                                   p_flags         => '&flags',
                                   p_description   => '&description',
                                   p_action        => 'APPLY',
                                   p_logfile       => '&full_logfile',
                                   p_bundle_series => '&bundle_series');
END;
/

COLUMN install_file NEW_VALUE sql_script


ALTER SESSION SET CURRENT_SCHEMA = SYS;

SET PAGESIZE 0
SELECT 'Calling rdbms/admin/berx_bug1234_apply.sql on ' || SYSTIMESTAMP FROM dual;
SET PAGESIZE 10

PROMPT IGNORABLE ERRORS: NONE
select "berx_was_here!" from dual;
create table berx_was_here as select  * from dual;

! touch /tmp/berx_was_here


BEGIN dbms_sqlpatch.patch_finalize; END;
/

SET PAGESIZE 0
SELECT 'Finished apply for patch 1234/5678 on' ||
       SYSTIMESTAMP FROM dual;
SET PAGESIZE 10

SPOOL off

This specific script creates only one table and a file in /tmp but it could do worse.

At the next run of datapatch (might it be due to a regular patch session or an one-off patch with post-patching steps), will execute this script. And as the script then is located in ORACLE_HOME/sqlpatch, all other databases which instances use this ORACLE_HOME and get patched will be affected also (and get the zip-file added to their sys.registry$sqlpatch).

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!

Montag, 30. September 2019

Simple getTracefile

Tracefiles are a very valuable source of information in Oracle databases.
In Versions prior to 12.2 quite complicated objects (with java dependency) were required.
Fortunately, in 12.2 Oracle introduced (& documented) the View V$DIAG_TRACE_FILE_CONTENTS. Unfortunately for some (only good from an internal technical perspective) reasons it does not contain the file itself, or a LOB locator, but several chunks as VARCHAR2. So it's still worth to write some code for a simple interface to access the tracefiles.

Again it's a SQLDeveloper report. But the structure is so simple, it can be implemented in any preferred tool.



There is a simple list of tracefiles and their MODIFY_TIME as FILEDATE - based on V$DIAG_TRACE_FILE.



When a specific tracefile name is selected, the file is presented as text and BLOB. The content is identical, just BLOBs can be downloaded which is useful for huge tracefiles, whereas the CLOB representation is simple to directly read small tracefiles.


Fortunately no objects are required in the database, only proper grants on V$DIAG_TRACE_FILE and V$DIAG_TRACE_FILE_CONTENTS. These can be roles also.

As I have limited access to databases right now, I could not test a lot of settings, e.g. RAC or different character sets.

Just import this Report as an User Defined Report:

<?xml version="1.0" encoding="UTF-8" ?>
<displays>
<display id="04e5e018-014a-1000-8001-ac193119805c" type="" style="Table" enable="true">
<name><![CDATA[get tracefiles]]></name>
<description><![CDATA[create a list of tracefiles as listed in V$DIAG_TRACE_FILE and makes specific files available]]></description>
<tooltip><![CDATA[create a list of tracefiles in DIAG directory and makes specific files available ]]></tooltip>
<drillclass><![CDATA[null]]></drillclass>
<CustomValues>
<TYPE>horizontal</TYPE>
</CustomValues>
<query>
<sql><![CDATA[select TRACE_FILENAME as TRACE_NAME ,
 MODIFY_TIME as FILEDATE
from V$DIAG_TRACE_FILE
Order By MODIFY_TIME desc]]></sql>
</query>
<pdf version="VERSION_1_7" compression="CONTENT">
<docproperty title="-" author="berx" subject="-" keywords="-" />
<cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
<column>
<heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
<footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
<blob blob="EMBED" zip="false" />
</column>
<table font="Agency FB" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
<header enable="false" generatedate="false">
<data>
 
null                                                               </data>
</header>
<footer enable="false" generatedate="false">
<data value="null" />
</footer>
<security enable="false" useopenpassword="false" openpassword="null" encryption="EXCLUDE_METADATA">
<permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="true" />
</security>
<pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
</pdf>
<display id="null" type="" style="Table" enable="true">
<name><![CDATA[get tracefile]]></name>
<description><![CDATA[access the specific tracefile ]]></description>
<tooltip><![CDATA[Specific tracefile from list above ]]></tooltip>
<drillclass><![CDATA[null]]></drillclass>
<CustomValues>
<TYPE>horizontal</TYPE>
</CustomValues>
<query>
<sql><![CDATA[with function get_tracefile (filename varchar2) return CLOB IS
-- PRAGMA AUTONOMOUS_TRANSACTION;
-- to avoid ORA-14553: cannot perform a lob write operation inside a query 
-- only required in dedicated function
--
-- in a dedicated function, this grant is required:
-- grant select on sys.V_$DIAG_TRACE_FILE_CONTENTS to xxx;
  l_out CLOB := EMPTY_CLOB;
  CURSOR all_payload (fn varchar2)
  is 
  Select Payload
  from V$DIAG_TRACE_FILE_CONTENTS 
  where trace_filename=fn
    and payload is not null
  order by line_number asc;
begin
  NULL;
   
   DBMS_LOB.CREATETEMPORARY(l_out, FALSE, DBMS_LOB.CALL);

  for r_payload in all_payload(filename)
  loop
    DBMS_lob.append(l_out, r_payload.payload);
  end loop;
  return l_out;
  DBMS_LOB.FREETEMPORARY(l_out);
end get_tracefile;
function get_tracefile_blob (filename varchar2) return BLOB IS
-- PRAGMA AUTONOMOUS_TRANSACTION;
-- to avoid ORA-14553: cannot perform a lob write operation inside a query 
-- only required in dedicated function
--
-- in a dedicated function, this grant is required:
-- grant select on sys.V_$DIAG_TRACE_FILE_CONTENTS to xxx;
  l_out BLOB := EMPTY_BLOB;
  CURSOR all_payload (fn varchar2)
  is 
  Select Payload
  from V$DIAG_TRACE_FILE_CONTENTS 
  where trace_filename=fn
    and payload is not null
  order by line_number asc;
begin
  NULL;
   DBMS_LOB.CREATETEMPORARY(l_out, FALSE, DBMS_LOB.CALL);
  for r_payload in all_payload(filename)
  loop
    DBMS_lob.append(l_out, utl_raw.cast_to_raw(r_payload.payload));
  end loop;
  return l_out;
  DBMS_LOB.FREETEMPORARY(l_out);
end get_tracefile_blob ;
select get_tracefile(:TRACE_NAME) as " Tracefile", get_tracefile_blob(:TRACE_NAME) as " Binary Tracefile " from dual]]></sql>
<binds>
<bind id="TRACE_NAME">
<prompt><![CDATA[TRACE_NAME]]></prompt>
<tooltip><![CDATA[TRACE_NAME]]></tooltip>
<value><![CDATA[NULL_VALUE]]></value>
<bracket><![CDATA[null]]></bracket>
</bind>
</binds>
</query>
<pdf version="VERSION_1_7" compression="CONTENT">
<docproperty title="-" author="-" subject="-" keywords="-" />
<cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
<column>
<heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
<footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
<blob blob="EMBED" zip="false" />
</column>
<table font="null" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
<header enable="false" generatedate="false">
<data>
 
null          </data>
</header>
<footer enable="false" generatedate="false">
<data value="null" />
</footer>
<security enable="false" useopenpassword="false" openpassword="null" encryption="EXCLUDE_METADATA">
<permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="false" />
</security>
<pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
</pdf>
</display>
</display>
</displays>

Any comments, hints or recommendations are highly welcome!

Donnerstag, 18. Juli 2019

possible changes in Oracle 20

As this post is written in July 2019, it's highly speculative writing about changes in Oracle version 20.
But at least it's based on some information available from Oracle.

A new autoupgrade.jar version is available at AutoUpgrade Tool (Doc ID 2485457.1). In this Note, there is also a BUGS_20190715.txt which contains (among others):
AUPG-955 Disable NONCDB validation when upgrading a DB to 20c
AUPG-975 upgrade from 12.1.0.2 to 20 is not supported

Based on that I assume, noncdb will not be officially supported anymore, but maybe it's not supported in the autoupgrade.jar, but manual steps are still allowed & supported - we will se.
Also a direct upgrade from 12.1 to 20 is out of scope of this tool.

But there is more information available.
in the autoupgrade.jar in directory resources/preupgrade/config/ there is a file parameters20.properties. It contains information about deprecated/obsolete/removed parameters.

The changes in comparison to 19 I found:

from deprecated to removed:
unified_audit_sga_queue_size

from deprecated to obsolete:
cluster_database_instances

new parameters and their status:

PARAMETER STATUS
_arch_sim_mode removed
_gc_async_receive removed
_hcs_disable_sp_jback_opt removed
_autonomous_performance_features_index removed
_cgs_os_level_connection_reqno deprecated
_autonomous_performance_features_stats removed
_px_monitor_load obsolete
_hcs_disable_level_prune_vis_lvs removed
_hcs_disable_level_prune_gby removed
_asm_enable_parity_datafile_creation removed
_part_access_version_by_number obsolete
_tablespace_encryption_default_algorithm removed
_hcs_disable_cell_qry_meas_prune_opt removed
_unified_audit_flush_threshold removed
_optimizer_stats_on_conventional_dml_config removed
_reset_maxcap_history removed
_ldr_io_size2 obsolete
_hang_long_wait_time_threshold removed
_read_mostly_instance deprecated
_cgs_os_level_connection_pthreadno removed
_max_cr_rollbacks deprecated
_hcs_enable_multi_parent_gen removed
_disable_thread_snapshot removed
_hcs_disable_tgt_depths_opt removed
_enable_imc_mira removed
_log_archive_avoid_memcpy removed
_gcs_min_slaves deprecated
_hcs_enable_dynamic_cache removed
_unified_audit_flush_interval removed
_hcs_disable_hord_in_oby_prune removed
_ta_lns_wait_for_arch_log removed
_hcs_disable_jback_opt_for_hord_in_oby removed
_show_mgd_recovery_state removed
_hcs_disable_pushed_preds_in_gen_sql removed
_hcs_disable_cell_qry_tmpls removed
_test_param_5 deprecated
_autonomous_performance_features removed
_session_percent_threshold removed
_ash_compression_enable deprecated
_kebm_max_parallel_intensive_autotasks removed


let's see if this will change in next iterations.

The biggest value of such sources (for me) is the reliability. It's not something a vendor wants the customers to know/act on (like a "what changed" document would be); it's the information required by tools, so no political filters are there.

Freitag, 7. Juni 2019

ORA-21700 in data dictionary


This whole story started with a nice little ticket:

When I try to edit a scheduler job in <DB>  with user <USER> I am getting error:
ORA-21700: object does not exist or is marked for delete
even I can see the object and the object is not deleted or marked as delete.

At first I could not reproduce the error, so I asked for something more specific, and I got 2 screenshots. (one shown here)
Unfortunately (for me) the screenshots were from Toad and PL/SQL Developer - 2 tools I don't have install or licensed or know anything about.






So, to give it a try I connected to the DB with SQL Developer and randomly clicked around several scheduler related tabs. I was lucky to generate ORA-21700 there also. And the big value of SQL Developer in this case is the log it has for all SQLs it's sending to the DB.




It shows the statement which failed - together with it's arguments.


select job_name, argument_name, argument_position, argument_type, value, out_argument 
from SYS.Dba_SCHEDULER_JOB_ARGS  
WHERE "JOB_NAME" = :OBJECT_NAME  AND "OWNER" = :OBJECT_OWNER
order by argument_position

With this query it's much easier to analyse the problem. Executing the quera ys the specific user really created an ORA-21700. But where does it come from? DBMS_UTILITY.EXPAND_SQL_TEXT is very handy to get the "real" sql for a query with views. Of course it's possible to see the views text, but if it's using views again, this can be real work (which I try to avoid).

Trying all the tables in the expanded query the culprit was found easily: sys.SCHEDULER$_JOB_ARGUMENT
The table is not complicated:
NAME            DATA TYPE            NULL  DEFAULT    COMMENTS
*OID            NUMBER               No               
*POSITION       NUMBER               No               
 NAME           VARCHAR2(128 BYTE)   Yes              
 TYPE_NUMBER    NUMBER               Yes              
 USER_TYPE_NUM  NUMBER               Yes              
 VALUE          ANYDATA              Yes              
 FLAGS          NUMBER               Yes              

And with some more trying the problem was tracked down to 1 column in 1 row:
select VALUE from sys.SCHEDULER$_JOB_ARGUMENT where oid=2736824 

This shows a little misunderstanding in the first request, the error code and the problem. The full error description is
ORA-21700: object does not exist or is marked for delete
21700. 00000 -  "object does not exist or is marked for delete"
*Cause:    User attempted to perform an inappropriate operation to
           an object that is non-existent or marked for delete.
           Operations such as pinning, deleting and updating cannot be
           applied to an object that is non-existent or marked for delete.
*Action:   User needs to re-initialize the reference to reference an
           existent object or the user needs to unmark the object.

The objects it is referring to are things like a TYPE, whereas in the original content it was interpreted as a SCHEDULER JOB.

But why is there a problem with an anydata column at all? couldn't it hold simply ANY data?
there is a Note at MOS which describes the situation quite good: Used Type Can Be Dropped Causing ORA-21700 When Querying A Table With SYS.ANYDATA Column ( Doc ID 1503628.1 )
When selecting an anydata column which contains atype, the given TYPE must be known. If the TYPE was deleted in the meantime, the column (and related functions like anydata.gettypename) will fail.
The block or row itself is not corrupted and it can be queried with dump().
Typ=58 Len=630: 0,1,0,0,0,0,0,1,0,0,5,189,61,90,2,96,9,0,0,0,0,0,2,80,0,0,0,0,0,...
Unfortunately I don't know the internal structure of this Type. Datatype 58 is opaque (DTYOPQ) which can hold ANYDATA, ANYTYPE, XMLTYPE, ANYDATASET (and maybe others). But how to distinguish which exactly, or how to interpred the bytes I got from dump() I couldn't find somewhere.

The problem in this particular database is this one row in sys.SCHEDULER$_JOB_ARGUMENT. To be visible in Dba_SCHEDULER_JOB_ARGS, there is a join of SCHEDULER$_JOB_ARGUMENT and SCHEDULER$_JOB on OID = OBJ#.
In my particular case there is no row anymore on SCHEDULER$_JOB. So no row is visible and there is nothing which can be dropped with DBMS_SCHEDULER.DROP_JOB. I have no idea how this happened. But it's there.

That's the situation to create a SR at MOS and ask for assistance.

Even a testcase is simply done:
CREATE OR REPLACE TYPE random_type AS OBJECT (
    n NUMBER
);
/

CREATE OR REPLACE PROCEDURE do_nothing (
    param1 IN random_type
) AS
BEGIN
    NULL;
END do_nothing;
/

DECLARE
    t random_type;
BEGIN
    t := random_type(NULL);
    t.n := 1;
    do_nothing(t);
END;
/

BEGIN
    dbms_scheduler.create_job(job_name => 'TEST_NOTHING', job_type => 'STORED_PROCEDURE', job_action => 'DO_NOTHING', number_of_arguments
    => 1, start_date => SYSDATE + 1, repeat_interval => NULL, end_date => NULL, enabled => false, 
auto_drop => true, 
comments => 'job to test parameter of ANYDATA'
    );
END;
/

DECLARE
    t random_type;
BEGIN
    t := random_type(NULL);
    t.n := 1;
    dbms_scheduler.set_job_anydata_value(job_name => 'TEST_NOTHING', argument_position => 1, argument_value => SYS.anydata.convertobject
    (t) );

END;
/

SELECT
    sys.anydata.gettypename(value) d
FROM
    sys.scheduler$_job_argument;

DROP TYPE random_type;

SELECT
    sys.anydata.gettypename(value) d
FROM
    sys.scheduler$_job_argument;

That's exactly where the ORA-21700 occurs.
In the testcase, a simple cleanup can be done by (trying to) execute this job, so it's cleared:

PAUSE "let's initiate some cleanup" 

begin
    dbms_scheduler.set_attribute(name => '"BERX"."TEST_NOTHING"', attribute => 'logging_level', value => dbms_scheduler.logging_full);
    dbms_scheduler.set_attribute_null(name => '"BERX"."TEST_NOTHING"', attribute => 'start_date');
    dbms_scheduler.enable(name => '"BERX"."TEST_NOTHING"');
end;
/

PAUSE "sleep for some time until the job is executed"

SELECT
    sys.anydata.gettypename(value) d
FROM
    sys.scheduler$_job_argument;

SELECT
    *
FROM
    dba_scheduler_job_log
WHERE
    owner = user;

All I can do now is to convince an MOS to approve me deleting the problematic row.
Update: 2019-06-13
Oracle Support approved to fix this situation by running
delete from SCHEDULER$_JOB_ARGUMENT WHERE OID=2736824; 
commit
So my issue is fixed.

I do NOT recommend anyone to delete rows in data dictionary without asking Oracle Support for approval first!