patch the unpatchable

Oracle provides several VM for demo and training purpose. One of them is the Database Virtual Box Appliance / Virtual Machine. 

It's very clear these VMs are only for testing purpose. 

But they are limited even more.

(at least) this specific VM can not even be patched. 

For some reason I wanted to apply Patch:29649694. 

First of all, there is no OPatch in the VM. This can be mitigated :-) 

But a first try with opatch lsinventory shows a strange result:

Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/version/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/version/db_1/oraInst.loc
OPatch version    : 12.2.0.1.21
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/version/db_1/cfgtoollogs/opatch/opatch2020-06-04_08-24-42AM_1.log

Lsinventory Output file location : /u01/app/oracle/product/version/db_1/cfgtoollogs/opatch/lsinv/lsinventory2020-06-04_08-24-42AM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: localhost
ARU platform id: 0
ARU platform description:: 

There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.
[oracle@localhost OPatch]$ 

The most important part here is the missing of ARU platform id and description.

With some comparison of a good sandbox, I identified the missing directory $ORACLE_HOME/inventory

The first file of interest there is $ORACLE_HOME/inventory/invDetails.properties: 

#invDetails.properties
#Wed May 13 17:46:16 UTC 2020
XML_VER=4.1
CompsXML=comps.xml
LibsXML=libs.xml
configXML=config.xml
ReleaseNotesXML=releaseNotes.xml
OHPropsXML=oraclehomeproperties.xml

This now shows even more required files.
$ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml contains the ARU id and description:

<?xml version = '1.0' encoding = 'UTF-8'?>
<!-- Copyright (c) 1999, 2019, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<ORACLEHOME_INFO>
   <GUID>685938240#.1069264794</GUID>
   <HOME/>
   <ARU_PLATFORM_INFO>
      <ARU_ID>226</ARU_ID>
      <ARU_ID_DESCRIPTION>Linux x86-64</ARU_ID_DESCRIPTION>
   </ARU_PLATFORM_INFO>
   <PROPERTY_LIST>
     <PROPERTY NAME="ARCHITECTURE" VAL="64"/>
     <PROPERTY NAME="ORACLE_BASE" VAL="/u01/app/oracle"/>
   </PROPERTY_LIST>
</ORACLEHOME_INFO>

This is only the first step. Especially as the real patches applied are not known (DBA_REGISTRY_HISTORY  is of limited help, and DBA_REGISTRY_SQLPATCH relies on OPatch) it's to complicated for me. 

My approach here is to mimic OPatch and just do it on my own. 

I'm lucky: the patch only contains 1 file to be applied:

files/lib/libserver19.a/kpdbc.o 

and etc/config/actions.xml only says

<oneoff_actions>
    <oracle .rdbms="" opt_req="O" patch_level="3" version="19.0.0.0.0">
        <archive backup_in_zip="false" name="libserver19.a" object_name="lib/libserver19.a/kpdbc.o" path="%ORACLE_HOME%/lib" shaolue="E2CB4E2A994EB8B59E24471B719EA8F2A0079E66">
        <make change_dir="%ORACLE_HOME%/rdbms/lib" make_file="ins_rdbms.mk" make_target="ioracle">
    </make></archive></oracle>
</oneoff_actions>

Doesn't look difficult.

First of course, make sure no binaries are running. Then copy kpdbc.o to $ORACLE_HOME/lib.
There (after backup of libserver19.a) in this archive, kpbdc.o is replaced:
ar -r libserver19.a kpdbc.o
It should be possible to complete with
relink all
But it fails with

  Can't open perl script "/u01/app/oracle/product/version/db_1/install/modmakedeps.pl": (null)

This file also mist be provided from another installation. To cut it short, also $ORACLE_HOME/inventory/make/makeorder.xml is required.
With the help of these files, relink is happy. 

---

It can be done without the help of these 2 files.

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ioracle

also does the trick.

Oracle WINDOW FUNCTION XOR SDO_GEOMETRY

Recently a friend asked me how to write a query for a SQL query which was slightly above simple SQL demo cases you can find all around. For me it was a good reason to brush my SQL (and in the process I had to reach out to Kim Berg Hansen for a beautiful match_return solution, but that's not this posts content). As this friend has his data in a postgres database, I managed to get a free DB at Alwaysdata and play with this. 

But as I was asking Kim for some help, it was obvious to work in Oracle world. I supposed most solutions should be able to transfer to Posgres easily. How wrong I was ...

A striped down testcase uses a table like 

create table gps (
   train          integer
 , gpstimestamp   timestamp with time zone
 , geom           sdo_geometry
);

Train describes a series of measurements: every second a GPS point is measured. On part of the problematic question is to find the distance between 2 consecutive points. As I didn't stop in 1992, the LAG function came to my mind. 

But in Kims testcase, LAG did not work at all: 

select
train, gpstimestamp, geom
  , nvl(sdo_geom.sdo_distance(geom, LAG(geom) 
                                      OVER (PARTITION BY train 
                                            ORDER BY  gpstimestamp), 0.005)
      , 0) as prev_dist
from gps
;

throws 

ORA-22901: cannot compare VARRAY or LOB attributes of an object type 

This error just does not makes any sense, as the geomertry does not need to be sorted at all. gpstimestamp is used for sorting, and oracle is quite capable of sorting timestamps of any kind. 

Maybe train needs to be sorted internally - PARTITION BY in theory just needs a comparison for equality, but Oracle often sorts in such cases. Even here train is of type integer - nothing easier to sort than this. 

But Kim knows SQL, and if one solution fails for any obscure reason, there is another one: 

   select
      train, gpstimestamp, geom, prev_dist
   from gps
   match_recognize (
      partition by train
      order by gpstimestamp
      measures
         nvl(sdo_geom.sdo_distance(geom, prev(geom), 0.005), 0) as prev_dist
      all rows per match
      pattern (any_row)
      define
         any_row as 1=1
   )

A beautiful workaround. - Funnily in livesql, even this workaround fails, this time with ORA-932:

Of course there are other solutions also: scalar subqueries, correlating inline views and many others. 

I also opened SR 3-23171402921 at Oracle - with the result it works as designed / expected. 

---

OK, back to my initial task: helping my friend with his query.

First I tried to port Kims MATCH_RECOGNIZE solution, but Postgres does not understand MATCH_RECOGNIZE yet. 

So I gave the LAG syntax a chance - and it worked: 

                lag(geom) over
   ( PARTITION BY train
     ORDER BY gpstimestamp ) AS prev_geom ,

is flawless accepted by Postgres!

There are several lessons learned: 

• It's not as easy as I hoped to transfer SQL between DB-engines.
• If my SQL seems to be over complicated, for sure it is - ask Kim ;-) 
• If you want to do analytical functions with spatial data, use Postgres, not Oracle. 

Oracle EUS authentication with LSA activated on AD

Are there to many abbrevations in a posts title possible? If you think so, you might see this post a good example.


The setting I'm talking about is an Oracle Universal Directory (OUD) which works as a proxy between Oracle databases and Active Directory (AD) where the users are managed. Unfortunately it stopped working. Even when a user changed the password in AD, it could not log in with this password to the database, but always got

 ORA-01017: invalid username/password; logon denied  

This can have many reasons.
A check in Middleware/instances/euist_inst/OUD/logs/access(.log)  shows

[03/Mar/2020:13:09:00 +0100] MODIFY PROXY_REQ conn=1654 op=5 msgID=6 s_credmode=use-specific-identity dn="cn=Username,ou=...,dc=..." s_conn=1002 s_msgid=7983
[03/Mar/2020:13:09:00 +0100] MODIFY PROXY_RES conn=1654 op=5 msgID=6 result=53 Message="00000057: LdapErr: DSID-0C090F64, comment: Error in attribute conversion operation, data 0, v3839^@" etime=1 s_authdn=CN=Oracle 
...
[03/Mar/2020:13:09:02 +0100] DISCONNECT conn=1654 reason="Client Disconnect"

there is a MOs Note which describes the situation EUS Login Failure of AD Users Proxied by OUD: LdapErr: DSID-0C090CE0, comment: Error in attribute conversion operation (Doc ID 2612535.1) But neither the cause

They are not OUD-native. They have an AD format and indicate some problem with the passwords in AD.

nor the solution

Contact your AD administrator to determine the meaning of the AD portion of the error to fix this problem.

helps a lot.
Still asking the AD admin is a good idea. After some back & forth this line in eventlog on AD Server is an important step:

Code Integrity determined that a process (\...4\Windows\System32\lsass.exe) attempted to load \...\Windows\System32\oidpwdcn.dll that did not meet the Microsoft signing level requirements.

And it can be proved with signtool.exe - there is no certificate on the ddl:

"c:\Program Files (x86)\Windows Kits\10\bin\10.0.18362.0\x64\signtool.exe" verify oidpwdcn.dll 


File: oidpwdcn.dll 
Index  Algorithm  Timestamp

========================================

SignTool Error: No signature found.

 

Number of errors: 1

Of course Oracle Support has a solution for this situation in UD 11g - OIDPWDCN.DLL Plug-in Fails On AD 2012 R2 With Error "The password notification DLL oidpwdcn failed to load with error 577" (Doc ID 2616566.1) - but disabeling any security feature is not an acceptable solution in 2020. Sorry guys [NOT] !

The first attempt was to replace (quite old) oidpwdcn.dll with orapwdfltr.dll from modern opwdintg.exe

But a first check with signtool.exe didn't show any signature, and LSA also refused it. 

At that point, a SR at MOS was required. It went quite fast and Oracle confirmed, there is no signed version at that time. To get the latest orapwdfltr.dll, Bug 31134430 : NEED TO HAVE ORAPWDFLTR.DLL SIGNED BY MICROSOFT was opened and after reasonable time a signed ddl was provided. (I can not confirm nor decline, if additional contacts to Oracle were involved)

"C:\Program Files (x86)\Windows Kits\10\bin\10.0.18362.0\x64\signtool.exe" verify /v  orapwdfltr.dll
Verifying: orapwdfltr.dll
Signature Index: 0 (Primary Signature)
Hash of file (sha256): 2A14712107D424FF5577EF5C3D111CF66DB40F6226047ADC4F31389D69F437EB
Signing Certificate Chain:
    Issued to: VeriSign Class 3 Public Primary Certification Authority - G5
    Issued by: VeriSign Class 3 Public Primary Certification Authority - G5
    Expires:   Thu Jul 17 01:59:59 2036
    SHA1 hash: 4EB6D578499B1CCF5F581EAD56BE3D9B6744A5E5
        Issued to: Symantec Class 3 Extended Validation Code Signing CA - G2
        Issued by: VeriSign Class 3 Public Primary Certification Authority - G5
        Expires:   Mon Mar 04 01:59:59 2024
        SHA1 hash: 5B8F88C80A73D35F76CD412A9E74E916594DFA67
            Issued to: Oracle America Inc.
            Issued by: Symantec Class 3 Extended Validation Code Signing CA - G2
            Expires:   Thu Jan 28 01:59:59 2021
            SHA1 hash: 1CB08E9B70B917E64407A4F2665799D58B171F89
The signature is timestamped: Thu Apr 23 03:33:05 2020
Timestamp Verified by:
    Issued to: DigiCert Assured ID Root CA
    Issued by: DigiCert Assured ID Root CA
    Expires:   Mon Nov 10 02:00:00 2031
    SHA1 hash: 0563B8630D62D75ABBC8AB1E4BDFB5A899B24D43
        Issued to: DigiCert SHA2 Assured ID Timestamping CA
        Issued by: DigiCert Assured ID Root CA
        Expires:   Tue Jan 07 14:00:00 2031
        SHA1 hash: 3BA63A6E4841355772DEBEF9CDCF4D5AF353A297
            Issued to: TIMESTAMP-SHA256-2019-10-15
            Issued by: DigiCert SHA2 Assured ID Timestamping CA
            Expires:   Thu Oct 17 02:00:00 2030
            SHA1 hash: 0325BD505EDA96302DC22F4FA01E4C28BE2834C5
SignTool Error: A certificate chain processed, but terminated in a root
        certificate which is not trusted by the trust provider.
Number of files successfully Verified: 0
Number of warnings: 0
Number of errors: 1

Also the AD accepted the dll.

Still a proper login was not possible as orclCommonAttribute was not populated after a password change. It's important to read the documentation to opwdintg.exe as this installation program not only applies the ddl (instpflt.bat), but also extends the schema (etadschm.bat). (beside other changes) 3 groups are added: ORA_VFR_11G, ORA_VFR_12C and ORA_VFR_MD5. Only if users belong to the proper group, it's matching password algorithm is used to populate orclCommonAttribute.

After the test user was added to the first group (and it's password was changed again) login on th etest-DB was possible again.   

If you want to use this new, signed ddl, at the time of this post, no regular source is available (afaik). I recommend to open a SR at MOS and ask for a signed version of orapwdfltr.dll. Maybe it helps to drop a comment about Bug 31134430 😉




A bit THANK YOU to Stefa Oehrli who answered uncountable number of questions.

Agent has been blocked manually. Unblock the Agent.

As it's easter weekend (at the time I write this blog) and it's a nice tradition here to hide some small items (often colored eggs or sweets) so other can find them, this story perfectly matches.

In Enterprise Manager 13c I had an agent with status

Agent has been blocked manually. Unblock the Agent.

Unfortunately In the Agents drop down menu, there is no (sub-) entry to unblock the agent. A well hidden entry!
After several attempts I was hinted where to look. It's in the "Setup" => "Manage Cloud Control" => "Agents" area:

There all agents are listed, and when selected the blocked one, it can be unblocked in the top action list.

I did not find a useful entry in the documentation (but that's probably my lack of search-foo). At least there is a Note EM 12c: How to Block or Unblock an Enterprise Manager 12c Cloud Control Agent if Agent Status is Shown as Blocked in EM Console or Emctl Status Agent Command Shows Heartbeat Status : Agent Is Blocked ? (Doc ID 1392601.1)

Γνῶθι σεαυτόν

Sometimes it might be interesting to understand, what's the SQL statement which is currently executed.
In an Oracle instance, for any other session it's quite simple by accessing some v$ views. But from "within" the statement, it's not straight forward. Nevertheless it's possible, by a creative combination of some features.

Here is the example.

First let's create a proper user:

CREATE USER know IDENTIFIED BY "thyself";

GRANT connect, resource    TO know;

GRANT UNLIMITED TABLESPACE TO know;

GRANT    CREATE VIEW       TO know;

GRANT imp_full_database    TO know;

That's slightly more than really needed - so if you evern require this funcitonality, please be careful with required permissions!
In this example, we do not even need a table.
But a Package to keep some variables:

CREATE OR REPLACE PACKAGE other_color_injector AS
-- package OCI
    text_keeper   VARCHAR2(4000 CHAR) := '--';
    current_color VARCHAR2(100 CHAR)  := 'white';
    FUNCTION rls_hook (
        p_schema  IN  VARCHAR2,
        p_object  IN  VARCHAR2
    ) RETURN VARCHAR2;

END other_color_injector;
/

CREATE OR REPLACE PACKAGE BODY other_color_injector AS

    FUNCTION rls_hook (
        p_schema  IN  VARCHAR2,
        p_object  IN  VARCHAR2
    ) RETURN VARCHAR2 AS
        PRAGMA autonomous_transaction;
        chk_color VARCHAR2(100 CHAR);
    BEGIN
        text_keeper := sys_context('userenv', 'CURRENT_SQL');

        -- first word in /* comment 
        chk_color := regexp_substr(regexp_substr(text_keeper, '/\*.*\*'), '[[:alpha:]]+');
        dbms_output.put_line(' SQL Text:' || text_keeper || '---');
        IF chk_color IS NOT NULL THEN
            current_color := chk_color;
        END IF;
        RETURN NULL;
    END;

END other_color_injector;
/

And a function which gives us some feedback about the SQL in which it's called:

CREATE OR REPLACE TYPE rain_tab IS    TABLE OF VARCHAR2(100 CHAR);

create or replace FUNCTION rain_bow 
-- RETURN t_tf_tab 
return rain_tab
PIPELINED AS
BEGIN
    PIPE ROW ( other_color_injector.current_color );   

  RETURN;
END;
/

create or replace view cloud as select column_value as color from rain_bow();

The view is somehow important for the next step whihc puts all the pieces together:

BEGIN
    dbms_rls.add_policy(object_schema =>   'KNOW', 
                        object_name =>     'CLOUD', 
                        policy_name =>     'RAIN', 
                        function_schema => 'KNOW', 
                        policy_function => 'OTHER_COLOR_INJECTOR.RLS_HOOK',
                        statement_types => 'select', 
                        update_check => false);
END;
/

With this objects in place, a beautiful, but maybe confusing result can be created:

Enter user-name: know/thyself@pdb1

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select * from cloud;

COLOR
-------------------------------------------
white

SQL> select /* red */  * from cloud;

COLOR
-------------------------------------------
red

SQL> select * from cloud;

COLOR
-------------------------------------------
red

SQL> select /* green */  * from cloud;

COLOR
-------------------------------------------
green

SQL> select * from cloud;

COLOR
-------------------------------------------
green

SQL>

The important part is this line in OTHER_COLOR_INJECTOR.RLS_HOOK:

text_keeper := sys_context('userenv', 'CURRENT_SQL');

SYS_CONTEXT has access to CURRENT_SQL (and some other useful parameters) -  but only in fine grained audot / RLS events. So all the other objects are required to make this information available in my view rain_bow.

---

I also created a small twitter quiz where I asked for methods to identify the current SQL and create values based on the SQL.
Rene Jeruschkat suggested a solution based on latest v$sqlstats:

exploiting the excessive privileges you granted know and keeping track of the color in v$sqlstats. pic.twitter.com/2tCu8eQyoH

— Rene jeruschkat (@rene_jeruschkat) April 4, 2020

Mathias Rogel suggested to get the latest cursor from v$open_cursor which belongs to the current session:

pic.twitter.com/OpFWHm8epc

— Matthias Rogel 🏃🚴🏊‍♂️🏋️ (@MatthiasRogel) April 5, 2020

Both are great solutions and show: there is quite often more than one way to solve a problem!

---

If you are curious about the greek letters in the title: Γνῶθι σεαυτόν they can be translated to know thyself - something which is often hard - not only for SQL statements.

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!

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!