Samstag, 7. März 2020

materialized WITH query

Sometimes I have to improve a SQL query where the same (or similar) subquery is used several times within the whole statement. This leads to many times the tables needs to be visited, even for the same rows.
It also makes the query hard to read.

A real life example I had to deal with this week is something like

SELECT *
FROM ( SELECT columns, aggregate functions
       FROM some tables
       INNER JOIN
       ( SELECT IDa, 0 as IDb, col1
         FROM T1
         UNION 
         SELECT 0, IDb, col col1
         FROM T2 )
       ON some joins
       INNER JOIN
       ( SELECT IDa, 0 as IDb, col2
         FROM T1
         UNION 
         SELECT 0, IDb, col col2
         FROM T2 )
       ON some joins
       INNER JOIN
       ( SELECT IDa, 0 as IDb, col3
         FROM T1
         UNION 
         SELECT 0, IDb, col col3
         FROM T2 )
       ON some joins
       INNER JOIN
       ( SELECT IDa, 0 as IDb, col4
         FROM T1
         UNION 
         SELECT 0, IDb, col col4
         FROM T2 )
       ON some joins
       WHERE some filters
       GROUP BY columns)
WHERE more filters

In this case it's quite visible there are 4 INNER JOINs to the same UNION of 2 tables, only the columns differ.

Fig 1 original PLAN - begin


The optimizer tried it's best and the beginning of the plan looked like Fig1. 3 more iterations with SORT - VIEW - SORT - UNION ALL follow. It's amazing the cost is so low, But even with higher cost there is not much the optimizer could do.


So my idea was to put it into a WITH clause and replace the INNER JOIN select with it.

The WITH clause is

WITH my_inner as (
SELECT IDa, 0 as IDb, col1, col2, col3, col4
FROM T1
UNION ALL
SELECT   0, IDb,      col1, col2, col3, col4
FROM T2
)
 

And when replacing the first INNER JOIN
INNER JOIN
       ( SELECT * from my_inner )

Fig 2 changed PLAN - 1 replacement

It seems the optimizer did not like this as the cost increased.


But I continued with the next replacement:
Fig 3 changed PLAN - 2 replacements

The optimizer "understands" it can created a temporary object (the one which begins with SYS_TEMP_) and then re-uses it later.

With all 4 replacements, the Plan is different now:

Fig 4 changed PLAN - 4 replacements


The cost is still higher than in the original plan, but it's very likely the statement is faster than the original one.
In this case, there was no need to add the MATERIALIZED hint - it was done automatically, for very good reasons.

I like this optimization as it both, improves the readability of the query AND it's performance!

Montag, 3. Februar 2020

Gorillas, be aware!

 During the last 2 weeks I attended the Guerrilla Capacity and Performance online class.
It was somehow different from my normal technical activities as the content was totally agnostic of any specific technology and solely focussed on proper thinking and using of methodologies.
We learned sufficient about queueing theory, Littles Law, Amdahl and USL, PDQ and Statistical Forecasting like Multivariate Regression.
 Beside these technical skills the most important aspect was the tactical one: As modern timeframes and project plans does not allow a dedicated, full blown capacity planning step, the required approach is more guerilla like: Use oportunities, don't waste (your) resources, make achievements visible communicate properly.
 I have to thank Dr. Neil J. Gunther for giving this class. Beside the raw facts and figures he shared a great set of wisdom and anectotes.
 It's now on me to practice - the only way for becoming a practicioner. - Let's hope for many interesting projects ahead where I can apply my new skills!


Freitag, 17. Januar 2020

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?

Freitag, 13. Dezember 2019

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.

Dienstag, 3. Dezember 2019

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.

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.