fixing a failed APEX installation

Today I tried to install APEX 19.2.0.00.18 into a 19.5 database, but apexins.sql failed with

Errors found. Drop APEX_190200 before re-starting the installation.
#
# Actions in Phase 1:
#
    ok 1 - BEGIN                                                        |   0.00
    ok 2 - Creating FLOWS_FILES                                         |   0.00
    ok 3 - Creating APEX_PUBLIC_USER                                    |   0.02
    ok 4 - Creating APEX_GRANTS_FOR_NEW_USERS_ROLE                      |   0.00
    ok 5 - Creating SYS Objects                                         |   0.00
    ok 6 - Creating APEX Tables                                         |   0.13
    ok 7 - Installing Package Specs (Runtime)                           |   0.48
    not ok 8 - Installing Package Bodies (Runtime)                      |   0.20
    # Message: ORA-00933: SQL command not properly ended
    # Statement: comment on column apex_team_feedback.logged_as_issue_id      is
 'If feedback resulted in an issue, the unique identifier of the resulting issue
.'
    #
    # comment on column apex_team_feedback.LABEL_01 is 'Identifies the label for
 corresponding attribute.'
    ok 9 - Dev Grants                                                   |   0.00
    ok 10 - Creating Dev-Only Tables and Triggers                       |   0.00
    ok 11 - Installing Package Specs (Dev)                              |   0.20
    ok 12 - Installing Views (Dev)                                      |   0.02
    ok 13 - Installing Package Bodies (Dev)                             |   0.07
    ok 14 - Recompiling APEX_190200                                     |   0.33
    ok 15 - Creating APEX$ objects in APEX_190200                       |   0.02
    ok 16 - Creating Spatial Support                                    |   0.00
    ok 17 - Creating Instance Parameter Defaults                        |   0.00
    ok 18 - Installing Page Designer Metadata                           |   0.03
    ok 19 - Inherit Privileges                                          |   0.00
    ok 20 - Creating APEX_INSTANCE_ADMIN_USER                           |   0.00
not ok 1 - 19 actions passed, 1 actions failed                          |   1.50
begin
*
ERROR at line 1:
ORA-20001: Install errors found in phase 1, see "not ok" messages above for
details.
ORA-06512: at "APEX_190200.WWV_INSTALL_API", line 440
ORA-06512: at line 5

Some lines above in the logfile I found

...apex_team_feedback
comment on column apex_team_feedback.LABEL_01 is 'Identifies the label for corresponding attribute.'
*
ERROR at line 3:
ORA-00933: SQL command not properly ended

This lead me to the file core/apex_views.sql

There a simple ; was missing:

17374c17374
< comment on column apex_team_feedback.logged_as_issue_id      is 'If feedback resulted in an issue, the unique identifier of the resulting issue.';
---
> comment on column apex_team_feedback.logged_as_issue_id      is 'If feedback resulted in an issue, the unique identifier of the resulting issue.'

I have no idea why this file is missing that character.
The zip file seems not to be corrupted:

md5sum apex_19.2_en.zip
dbf813eb9ec65ff91ef1f90e8c9b0148  apex_19.2_en.zip

Maybe this helps anyone, Or I managed to find a problem in this env which causes unzip to drop a single ; - who knows?

maximum size of ORALCE_SID

The maximum length of ORACLE_SID environment variable seems to be a mystery in many places.
Recently I reviews a document where the max length was defined as 8 characters. I was quite sure this was not right, but I didn't know the correct value. A short ressearch did not find anythign useful in the docs - the Best available there was RAC installation guide for Linux & UNIX:

The Oracle Service Identifier (SID) prefix is the first 8 characters of the database name. The SID prefix can contain only the characters a-z, A-Z, and 0-9. The SID prefix cannot contain operating system special characters, so if you use special characters in the first 8 characters of the database name, then these special characters are omitted in the SID prefix. There is a single SID prefix for every database. The SID prefix for a database must be unique within the cluster.
For an Oracle RAC database, each instance has a unique identifier, ORACLE_SID, which consists of the SID prefix and an instance number. The ORACLE_SID prefix can contain up to 12 characters. 

It always refers to a ORACLE_SID prefix - but never explains the total length. As it's the RAC documentation, I assume it's more about RAC capabilities (some internals in srvctl and supporting binaries / structures).

Some other trustworthy sources at Oracle are very vague in this area:

Tom Kyte (2002):

its really 4 characters as the convention (ORCL for example).
The reason is in support of 8.3 filenames. ALRTORCL.LOG, INITORCL.ORA, etc. We use 4, you use 4 and we can get 8 character filenames.

and later there (2007):

I would stick with 8 or less - better safe than sorry.

Connor McDonald (2017):

So even if you find a platform that does more than 8, I would never go more than that.

Another approach can be to identify Data Dictionary views which report the Oracle System ID.
One is v$instance.INSTANCE_NAME with Datatype VARCHAR2(16).
Another is v$thread.INSTANCE with Datatype VARCHAR2(80).
Oracle is really consequent in it's ambiguity!


So it's worth to do some tests!

My lab is 18c & 19c on Linux. Maybe other OS might show other limits!

The longest ORACLE_SID I could set and use to start an instance was

30 characters 

long!
(In my case I used QWERTZUIOP1234567890ASDFGHJKLY)

With this ORACLE_SID set, a proper value is returned by v$thread

SQL> select instance from v$thread;

INSTANCE
--------------------------------------------------------------------------------
QWERTZUIOP1234567890ASDFGHJKLY

but v$instance does not show anything:

select  instance_name  from v$instance;

INSTANCE_NAME
----------------


SQL>

With an ORACLE_SID of 16 characters (QWERTZUIOP123456) v$instance is fine:

select  instance_name  from v$instance;

INSTANCE_NAME
----------------
QWERTZUIOP123456

SQL>

With any ORACLE_SID of 31 characters or more I got

ORA-00600: internal error code, arguments: [OSDEP_INTERNAL], [], [], [], [], [], [], [], [], [], [], []
ORA-27302: failure occurred at: slsid1
ORA-27303: additional information: Unable to get environment variable ORACLE_SID

Now I at least tested a possible max. length of ORACE_SID (18c & 19c on Linux) of 30 characters.
Whenever I use the System ID in scripts, I should query v$thread, NOT v$instance.
I recommend to use not more than 16 characters so it's still visible in v$instance also.

glitches on runInstaller attachHome

Cloning ORACLE_HOMEs should be quite well known by now.

Still sometimes there are some glitchs to take care of.
In this case I cloned an existing ORACLE_HOME (dbhome_1) to a new one (dbhome_2) so I can apply a patch and reduce the downtime to <switching ORACLE_HOME> plus <running datapatch>.

Running the runInstaller was straight forward:

./runInstaller -silent -attachHome -invPtrLoc /var/opt/oracle/oraInst.loc \
 ORACLE_HOME="/path_to/oracle/product/18.0.0/dbhome_2" \
 ORACLE_HOME_NAME="OraDB18Home2" CLUSTER_NODES="{MY_NODE}" -local

But when I tried to start the instance (in an Oracle Restart configuration) a strange error occured:

srvctl start database -db MY_DATABASE

PRCR-1079 : Failed to start resource ora.MY_DATABASE.db
CRS-5017: The resource action "ora.MY_DATABASE.db start" encountered the following error:
ORA-12777: A non-continuable error encountered.  Check the error stack for additional information [ksm_check_ob_paths:1], [ORACLE_BASE], [], [].
ORA-08275: Environment variable unset
. For details refer to "(:CLSN00107:)" in "/ORACLE_BASE/diag/crs/MY_NODE/crs/trace/ohasd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.MY_DATABASE.db' on 'rznjvh175' failed

and the tracefile only contains lines of limited help:

2019-12-03 10:37:17.014 : USRTHRD:748464: {0:6:2} Thread:ASM DedicatedThread AsmCommonAgent::DedicatedThread::run  220 isRemoteListenSet=0
2019-12-03 10:37:17.651 :CLSDYNAM:51523: [ora.MYDATABASE.db]{0:0:36611} [start] ORA-12777: A non-continuable error encountered.  Check the error stack for additional information [ksm_check_ob_paths:1], [ORACLE_BASE], [], [].
ORA-08275: Environment variable unset

With no real luck at googling or searching MOS I had a lucky punch on
orabase command returns no value instead of ORACLE_BASE value. (Doc ID 2225573.1)

So I decided to edit the file /path_to/oracle/product/18.0.0/dbhome_2/install/orabasetab
to

/path_to/oracle/product/18.0.0/dbhome_2:/path_to/oracle:OraDB18Home2:N:

That made the trick and HAS can start the instance with new ORACLE_HOME.

I would assume runInstaller should to this task, and I have no idea why it was not done.
Just one more line on my list of post-activities.

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

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.

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 recommendations	https://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>

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!