OR Expansion and Virtual Columns

The following test case can be used to demonstrate a little peculiarity of the OR Expansion transformation, that was introduced in 12.2.
It is important to note that the issue is not present in 19c - I have reproduced it only on 12.2.0.1.190416 and 18c.
The listings that I am using in this article are taken from 18c.

SQL> @or_expand_setup
SQL> drop table t1;

Table dropped.

SQL>
SQL> create table t1 (
  2    part_key varchar2(8),
  3    status   varchar2(12),
  4    pad1     char(500)
  5  )
  6  partition by list(part_key) (
  7    partition values ('P1'),
  8    partition values (default)
  9  )
 10  ;

Table created.

SQL>
SQL>
SQL> insert into t1
  2  select 'P1', status, 'X'
  3    from (select 90 pct, 'PROCESSED' status from dual union all
  4          select 1, 'UNPROCESSED' from dual union all
  5          select 9, 'PENDING' from dual) params,
  6         lateral(
  7           select level
  8             from dual
  9             connect by level <= params.pct * 1000
 10         ) duplicator;

100000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index t1_status_i on t1(status) local;

Index created.

SQL>
SQL> select status,
  2         count(*),
  3         round(ratio_to_report(count(*)) over () * 100, 2) pct
  4    from t1
  5   group by status
  6   order by 1;

STATUS         COUNT(*)        PCT
------------ ---------- ----------
PENDING            9000          9
PROCESSED         90000         90
UNPROCESSED        1000          1

In a nutshell, it is a list-partitioned table with a local index. 90% of the rows are in the PROCESSED status, whereas PENDING and UNPROCESSED statuses account only for 9% and 1% correspondingly.
The script itself: or_expand.sql

Next, I am going to run a simple query and check its plan with and without a virtual column on the STATUS column:

 select --+ gather_plan_statistics or_expand(@sel$1 (1) (2))
        count(pad1)
   from t1
  where part_key = :part_key
    and (:param = 'WAITING' and status = 'UNPROCESSED'
         or
         :param = 'ALL' and status <> 'PENDING');

The variables PARAM and PART_KEY are set as follows:

var part_key varchar2(10)='P1'
var param    varchar2(12)='WAITING'

Thus, I am going to query a single partition to get the rows in the UNPROCESSED status from it.

Here is the result of the script without the virtual column first:

SQL> @or_expand_test_no_vcol
SQL> set def on lin 124 pages 100
SQL>
SQL> -- alter table t1 add lower_status varchar2(128) generated always as (lower(status)) virtual;
SQL>
SQL> exec dbms_stats.gather_table_stats( '', 't1', method_opt => 'for all columns size 254')

PL/SQL procedure successfully completed.

SQL>
SQL> col tfi old_v tfi nopri
SQL>
SQL> select to_char(sysdate, 'yyyymmdd_hh24miss') tfi from dual;




SQL>
SQL> alter session set tracefile_identifier='&tfi.';
old   1: alter session set tracefile_identifier='&tfi.'
new   1: alter session set tracefile_identifier='20200520_181304'

Session altered.

SQL>
SQL> alter session set events 'trace[sql_optimizer.*]';

Session altered.

SQL>
SQL>
SQL> var part_key varchar2(10)='P1'
SQL> var param    varchar2(12)='WAITING'
SQL>
SQL> select --+ gather_plan_statistics or_expand(@sel$1 (1) (2))
  2         count(pad1)
  3    from t1
  4   where part_key = :part_key
  5     and (:param = 'WAITING' and status = 'UNPROCESSED'
  6          or
  7          :param = 'ALL' and status <> 'PENDING');

COUNT(PAD1)
-----------
       1000

SQL>
SQL> select *
  2    from dbms_xplan.display_cursor(format=> 'allstats last outline');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  fq8b3jumk131t, child number 0
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

Plan hash value: 1293629841

-----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |      1 |00:00:00.02 |    7831 |
|   1 |  SORT AGGREGATE        |      |      1 |      1 |      1 |00:00:00.02 |    7831 |
|   2 |   PARTITION LIST SINGLE|      |      1 |    920 |   1000 |00:00:00.01 |    7831 |
|*  3 |    TABLE ACCESS FULL   | T1   |      1 |    920 |   1000 |00:00:00.01 |    7831 |
-----------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      DB_VERSION('18.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter((((:PARAM='ALL' AND "STATUS"<>'PENDING') OR
              ("STATUS"='UNPROCESSED' AND :PARAM='WAITING')) AND "PART_KEY"=:PART_KEY))


35 rows selected.

SQL>
SQL> alter session set events 'trace[sql_optimizer.*] off';

Session altered.

SQL>
SQL> col value for a80
SQL>
SQL> select value
  2    from v$diag_info
  3   where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2137_20200520_181304.trc

I highlighted the commented ALTER TABLE ADD COLUMN command. I am going to refer to it below.

It is worth noting that the OR Expansion transformation has not been performed and the relevant trace file contains a clue:

***********************************
Cost-Based OR Expansion
***********************************
ORE: Trying CBQT OR expansion before unnesting
ORE: Checking validity of OR Expansion for query block SEL$1 (#1)
ORE: Predicate chain before QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')

ORE: Predicate chain after QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')
OR Expansion on query block SEL$1 (#1)
ORE: Checking validity of OR Expansion for query block SEL$1 (#1)
ORE: Predicate chain before QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')

ORE: Predicate chain after QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')
ORE: Using search type: linear
ORE: Checking validity of OR Expansion for query block SEL$1 (#1)
ORE: Predicate chain before QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')

ORE: Predicate chain after QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')
ORE: Checking validity of disjunct chain 
ORE: Bypassed for disjunct chain: No Index or Partition driver found.
ORE: # conjunction chain - 1
ORE: No state generated.

OFF: I came across that ORE: Bypassed for disjunct chain: No Index or Partition driver found. message in one of my previous posts: Concatenation and filter subqueries oddity.

What can go wrong if we now uncomment the aforementioned ALTER TABLE ADD COLUMN command?
The script or_expand_test_vcol.sql:

SQL> @or_expand_test_vcol
SQL> set def on lin 124 pages 100
SQL>
SQL> alter table t1 add lower_status varchar2(128) generated always as (lower(status)) virtual;

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats( '', 't1', method_opt => 'for all columns size 254')

PL/SQL procedure successfully completed.

SQL>
SQL> col tfi old_v tfi nopri
SQL>
SQL> select to_char(sysdate, 'yyyymmdd_hh24miss') tfi from dual;




SQL>
SQL> alter session set tracefile_identifier='&tfi.';
old   1: alter session set tracefile_identifier='&tfi.'
new   1: alter session set tracefile_identifier='20200520_181319'

Session altered.

SQL>
SQL> alter session set events 'trace[sql_optimizer.*]';

Session altered.

SQL>
SQL>
SQL> var part_key varchar2(10)='P1'
SQL> var param    varchar2(12)='WAITING'
SQL>
SQL> select --+ gather_plan_statistics or_expand(@sel$1 (1) (2))
  2         count(pad1)
  3    from t1
  4   where part_key = :part_key
  5     and (:param = 'WAITING' and status = 'UNPROCESSED'
  6          or
  7          :param = 'ALL' and status <> 'PENDING');

COUNT(PAD1)
-----------
       1000

SQL>
SQL> select *
  2    from dbms_xplan.display_cursor(format=> 'allstats last outline');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  fq8b3jumk131t, child number 0
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

Plan hash value: 3973059565

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                 |      1 |        |      1 |00:00:00.01 |      83 |
|   1 |  SORT AGGREGATE                                |                 |      1 |      1 |      1 |00:00:00.01 |      83 |
|   2 |   VIEW                                         | VW_ORE_BA8ECEFB |      1 |  91982 |   1000 |00:00:00.01 |      83 |
|   3 |    UNION-ALL                                   |                 |      1 |        |   1000 |00:00:00.01 |      83 |
|*  4 |     FILTER                                     |                 |      1 |        |   1000 |00:00:00.01 |      83 |
|   5 |      PARTITION LIST SINGLE                     |                 |      1 |    999 |   1000 |00:00:00.01 |      83 |
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1              |      1 |    999 |   1000 |00:00:00.01 |      83 |
|*  7 |        INDEX RANGE SCAN                        | T1_STATUS_I     |      1 |   1000 |   1000 |00:00:00.01 |       6 |
|*  8 |     FILTER                                     |                 |      1 |        |      0 |00:00:00.01 |       0 |
|   9 |      PARTITION LIST SINGLE                     |                 |      0 |  90983 |      0 |00:00:00.01 |       0 |
|* 10 |       TABLE ACCESS FULL                        | T1              |      0 |  90983 |      0 |00:00:00.01 |       0 |
----------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      DB_VERSION('18.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SET$9162BF3C_2")
      OUTLINE_LEAF(@"SET$9162BF3C_1")
      OUTLINE_LEAF(@"SET$9162BF3C")
      OR_EXPAND(@"SEL$1" (1) (2))
      OUTLINE_LEAF(@"SEL$BA8ECEFB")
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$BA8ECEFB" "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB")
      INDEX_RS_ASC(@"SET$9162BF3C_1" "T1"@"SET$9162BF3C_1" ("T1"."STATUS"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$9162BF3C_1" "T1"@"SET$9162BF3C_1")
      FULL(@"SET$9162BF3C_2" "T1"@"SET$9162BF3C_2")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(:PARAM='WAITING')
   6 - filter("PART_KEY"=:PART_KEY)
   7 - access("STATUS"='UNPROCESSED')
   8 - filter(:PARAM='ALL')
  10 - filter(("STATUS"<>'PENDING' AND "PART_KEY"=:PART_KEY AND (LNNVL(:PARAM='WAITING') OR
              LNNVL("STATUS"='UNPROCESSED'))))


54 rows selected.

SQL>
SQL> alter session set events 'trace[sql_optimizer.*] off';

Session altered.

SQL>
SQL> col value for a80
SQL>
SQL> select value
  2    from v$diag_info
  3   where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2137_20200520_181319.trc

The relevant section of the trace file is as follows:

***********************************
Cost-Based OR Expansion
***********************************
ORE: Trying CBQT OR expansion before unnesting
ORE: Checking validity of OR Expansion for query block SEL$1 (#1)
ORE: Predicate chain before QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')

ORE: Predicate chain after QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')
OR Expansion on query block SEL$1 (#1)
ORE: Checking validity of OR Expansion for query block SEL$1 (#1)
ORE: Predicate chain before QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')

ORE: Predicate chain after QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')
ORE: Using search type: linear
ORE: Checking validity of OR Expansion for query block SEL$1 (#1)
ORE: Predicate chain before QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')

ORE: Predicate chain after QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')
ORE: Checking validity of disjunct chain 

ORE: # conjunction chain - 2
ORE: Checking validity of disjunct chain 

ORE:  Predicate list
P1 : "T1"."PART_KEY"=:B1
P2 : :B1='WAITING'
P3 : "T1"."STATUS"='UNPROCESSED'
P4 : :B1='ALL'
P5 : "T1"."STATUS"<>'PENDING'

 DNF Matrix (Before sorting OR branches)
            P1  P2  P3  P4  P5 
CNJ (#1) :   1   1   1   0   0 

CNJ (#2) :   1   0   0   1   1 

I have made the following observations regarding this issue:

• The virtual column should be on the STATUS column. Neither PART_KEY, nor PAD1 works - they both result in the absence of OR Expansion.
• The OR Expansion transformation kicks in if there are extended statistics on the STATUS column, such as: SYS_OP_COMBINED_HASH(STATUS, PART_KEY).
• Unsurprisingly, there is OR Expansion when there is an FBI, such as:

  create index t1_lower_status_i on t1(lower(status)) local;

• The full outline of the query with OR Expansion does not lead to the OR Expansion transformation in the examples from this article where there was no OR Expansion.
• Once again, 19c behaves differently - there is no OR Expansion in the given examples when 12.2 and 18c performs it. I am going to talk about in the next blog post.

Although I do expect OR Expansion in the queries from this article, I am not able to make a connection between OR Expansion and virtual columns/extended statistics/FBIs in the provided examples.
Hence, this issue is a bit mixed bag for me: I would love to have OR Expansion - the examples were designed after real application queries after all (they benefit from OR Expansion) - it might as well be a bug that was fixed in 19c.
Time will tell. I have raised an SR to follow up on this issue: SR 3-22449907431 : OR-Expansion does not work following 19.5 upgrade. Hopefully, Oracle Support will explain it one day - we have not made any progress during last 2 months which has become customary while working with Oracle Support.

Secure Use of Runtime.exec Functionality in Oracle Database

Developers wanted to call external programs from the database using a Java class. By default, those commands are run under the database user account which is anything but secure.
Following Oracle security guidelines, I created a separate low-privileged OS account and configured the database user access to it.
It turns out that this functionality has some limitations and does not work especially well with Grid Infrastructure.

Here are the details of my setup:
Oracle Grid Infrastructure 19.7 standalone that uses separate OS groups: $ORACLE_HOME/rdbms/lib/config.c:

#define SS_DBA_GRP "asmdba"
#define SS_OPER_GRP "asmoper"
#define SS_ASM_GRP "asmadmin"
#define SS_BKP_GRP "asmadmin"
#define SS_DGD_GRP "asmadmin"
#define SS_KMT_GRP "asmadmin"
#define SS_RAC_GRP "asmadmin"

Oracle Database 19.7 uses the following groups:

#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "oper"
#define SS_ASM_GRP ""
#define SS_BKP_GRP "backupdba"
#define SS_DGD_GRP "dgdba"
#define SS_KMT_GRP "kmdba"
#define SS_RAC_GRP "racdba"

The oracle user is the software owner; its group is oinstall. The database listener runs from the GI ORACLE_HOME (it does not matter for the purpose of the test case, though, as the issue can be reproduced even with bequeath connections).

Here is an SQL script that I use to demonstrate the issue:

def user_connect_string=tc/tc@//localhost/pdb
def sys_connect_string="sys/oracle@//localhost/pdb as sysdba"

set echo on

spool ojvm_test_case

connect &sys_connect_string

doc
  CREATING THE TEST CASE USER
#

drop user tc cascade;
grant create procedure, create session to tc identified by tc;

exec dbms_java.grant_permission('TC', 'SYS:java.io.FilePermission', '/usr/bin/who', 'read,execute')

connect &user_connect_string

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "OSCommand" AS 
import java.io.*; 
public class OSCommand {
    public static String Run(String command) {
        final String crlf = "" + (char) 10 + (char) 13;
        final int max_length = 4000;
        String str_out = "";

        try {
            final Process pr = Runtime.getRuntime().exec(command);
            pr.waitFor();

            BufferedReader br_in = null;
            try {
                br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
                String buff = null;
                while (((buff = br_in.readLine()) != null) & (str_out.length() < max_length)) {
                    System.out.println("Process out :" + buff);
                    str_out += buff + crlf;
                }
                br_in.close();
            } catch (IOException ioe) {
                System.out.println("Exception caught printing process output.");
                ioe.printStackTrace();
            } finally {
                try {
                    br_in.close();
                } catch (Exception ex) {}
            }

            BufferedReader br_err = null;
            try {
                br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
                String buff = null;
                while (((buff = br_err.readLine()) != null) & (str_out.length() < max_length)) {
                    str_out += buff + crlf;
                }
                br_err.close();
            } catch (IOException ioe) {
                System.out.println("Exception caught printing process error.");
                ioe.printStackTrace();
            } finally {
                try {
                    br_err.close();
                } catch (Exception ex) {}
            }

        } catch (Exception e) {
            e.printStackTrace();
            return (e.getMessage());
        }
        if (str_out.length() > 4000) str_out = str_out.substring(0, max_length); //limit output
        return str_out;
    }
}
/

CREATE OR REPLACE FUNCTION OSCommand_Run(Command IN STRING) 
RETURN VARCHAR2 IS 
LANGUAGE JAVA 
NAME 'OSCommand.Run(java.lang.String) return int';
/

doc
  Test #1: call OS command
#

select oscommand_run('/usr/bin/who') from dual; 


doc
  ENABLING OJVM SECURITY
#

conn &sys_connect_string

exec dbms_java.set_runtime_exec_credentials('TC', 'oraexec', 'test');
commit;


connect &user_connect_string

doc
  Test #2: call OS command after Java credentials were set
#

select oscommand_run('/usr/bin/who') from dual; 

doc
  Test #3: getting additional output
#
set serverout on
exec dbms_java.set_output(100000)
select oscommand_run('/usr/bin/who') from dual; 


spo off

In a nutshell, the script creates a separate user and grants it necessary privileges to run the external command /usr/bin/who using a Java class (that might be copied from somewhere over the Internet judging by how it handles exceptions).
When I call that command for the first time, I get the following output:

SQL> select oscommand_run('/usr/bin/who') from dual;

OSCOMMAND_RUN('/USR/BIN/WHO')                                                   
--------------------------------------------------------------------------------
oracle   pts/0        2020-04-29 09:06 (:pts/2:S.0)
ec2-user pts/2        2020-04-29 09:06 (172.17.1.27)
ec2-user pts/3        2020-04-29 09:06 (172.17.1.27)

There is an OS account oraexec with a password test that is created on the database server host, and I associate that account to run any external commands initiated by the TC database user (Java Developer's Guide):

exec dbms_java.set_runtime_exec_credentials('TC', 'oraexec', 'test');

The subsequent calls of the previously working function, now starts failing:

SQL> select oscommand_run('/usr/bin/who') from dual;

OSCOMMAND_RUN('/USR/BIN/WHO')                                                   
--------------------------------------------------------------------------------
Cannot run program "/usr/bin/who": cannot execute /u01/app/oracle/product/db_19.
7.0/bin/jssu                                                                    

OJVM can produce additional output, which in this case is not particularly helpful:

SQL> set serverout on
SQL> exec dbms_java.set_output(100000)

PL/SQL procedure successfully completed.

SQL> select oscommand_run('/usr/bin/who') from dual;

OSCOMMAND_RUN('/USR/BIN/WHO')                                                   
--------------------------------------------------------------------------------
Cannot run program "/usr/bin/who": cannot execute /u01/app/oracle/product/db_19.
7.0/bin/jssu                                                                    
                                                                                

java.io.IOException: Cannot run program "/usr/bin/who": cannot execute          
/u01/app/oracle/product/db_19.7.0/bin/jssu                                      
 at java.lang.ProcessBuilder.start(ProcessBuilder.java:1057)                    
 at java.lang.Runtime.exec(Runtime.java:620)                                    
 at java.lang.Runtime.exec(Runtime.java:450)                                    
 at java.lang.Runtime.exec(Runtime.java:347)                                    
 at OSCommand.Run(OSCommand:11)                                                 
Caused by: java.io.IOException: cannot execute                                  
/u01/app/oracle/product/db_19.7.0/bin/jssu                                      
 at java.lang.OracleProcess.create(Native Method)                               
 at java.lang.OracleProcess.construct(OracleProcess.java:112)                   
 at java.lang.OracleProcess.<init>(OracleProcess.java:42)                       
 at java.lang.OracleProcess.start(OracleProcess.java:383)                       
 at java.lang.ProcessBuilder.start(ProcessBuilder.java:1038)                    
 ... 4 more                                                                     

jssu is an Oracle's way to switch to another user per my knowledge. I know that the database uses it when it runs external jobs through DBMS_SCHEDULER. The jssu name itself was likely inspired by the Linux 'su' command (Switch User).
The first thing that I do is to check its permissions since I am quite familiar with certain issues when those permissions can go sideways due to patching actions:

[oracle@ip-172-17-31-160 lib]$ ls -la $ORACLE_HOME/bin/jssu
-rwsr-x---. 1 root oinstall 2327920 Apr 28 15:14 /u01/app/oracle/product/db_19.7.0/bin/jssu

The permissions are fine - its owned by root and has the setuid bit set.
In order to further diagnose this issue, I run strace against the server process and find this:

stat("/u01/app/oracle/product/db_19.7.0/bin/jssu", {st_mode=S_IFREG|S_ISUID|0750, st_size=2327920, ...}) = 0
geteuid()                         = 54321
getegid()                         = 54331

The uid 54321 is the oracle user, however, the gid 54331 is asmadmin (the OSASM group):

[oracle@ip-172-17-31-160 lib]$ grep 54321 /etc/passwd
oracle:x:54321:54321::/home/oracle:/bin/bash
[oracle@ip-172-17-31-160 lib]$ grep 54331 /etc/group
asmadmin:x:54331:oracle

When there is a role-separation in place, the Oracle binary is owned by the OSASM group:

[oracle@ip-172-17-31-160 lib]$ ls -ls /u01/app/oracle/product/db_19.7.0/bin/oracle
432852 -rwsr-s--x. 1 oracle asmadmin 443232296 Apr 28 15:15 /u01/app/oracle/product/db_19.7.0/bin/oracle

Oracle processes have the following groups:

# ps -o rgid,egid,cmd -p 27280
 RGID  EGID CMD
54321 54331 oracleorcl (LOCAL=NO)

Where RGID=oinstall, EGID=asmadmin (OSASM):

# grep 543[23]1 /etc/group
oinstall:x:54321:oracle
asmadmin:x:54331:oracle

The OJVM code expects that jssu is owned by the OSASM group in this case, so that a possible workaround for this issue is to amend the jssu permissions as follows:

[oracle@ip-172-17-31-160 lib]$ ls -ls /u01/app/oracle/product/db_19.7.0/bin/jssu
2276 -rwsr-x---. 1 root asmadmin 2327920 Apr 28 15:14 /u01/app/oracle/product/db_19.7.0/bin/jssu

Obviously it cannot be used as a long-term solution as I recall that jssu permissions might be changed when relink is run, or when some patches are applied.
There permissions also work fine with external jobs that use credentials.
I opened an SR with Oracle Support a few months ago and it has not gone anywhere yet.

Using LogMiner to Identify Redo Tag for Transactions

I was asked if there is a way to identify what redo tag was set for a specific transaction.

There is a document that answers the question if any tag was set without clarifying what tag was set specifically:
Using Logminer to Identify if a Non-NULL Tag Has Been Set Using DBMS_STREAMS.SET_TAG (Doc ID 740574.1)
Besides, LogDump can be used to display the tag information when GoldenGate is in use: GGSTOKEN.

My experiments revealed that the required information can be obtained using X$LOGMNR_CONTENTS.DUMP_INFO as the example below demonstrates.

SYS@PDB> grant create session, create table, select_catalog_role to tc identified by tc;

Grant succeeded.

SYS@PDB>
SYS@PDB> grant execute on dbms_streams_adm to tc;

Grant succeeded.

SYS@PDB>
SYS@PDB> alter user tc quota 100m on users;

User altered.

SYS@PDB>
SYS@PDB> conn tc/tc@pdb
Connected.
TC@PDB>
TC@PDB>
TC@PDB> select current_scn from v$database;

CURRENT_SCN
-----------
    2221571

TC@PDB>
TC@PDB> create table t(id int, s varchar2(30))
  2    segment creation immediate;

Table created.

TC@PDB>
TC@PDB> exec dbms_streams_adm.set_tag(hextoraw('112233'))

PL/SQL procedure successfully completed.

TC@PDB>
TC@PDB> insert into t values (1, 'tag_1');

1 row created.

TC@PDB> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
3.29.732

TC@PDB> commit;

Commit complete.

TC@PDB>
TC@PDB> exec dbms_streams_adm.set_tag(hextoraw('deadbeef'))

PL/SQL procedure successfully completed.

TC@PDB>
TC@PDB> insert into t values (11, 'tag_1');

1 row created.

TC@PDB> insert into t values (12, 'tag_1');

1 row created.

TC@PDB> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
8.26.689

TC@PDB> commit;

Commit complete.

TC@PDB>
TC@PDB> select current_scn from v$database;

CURRENT_SCN
-----------
    2221592

Here is summary information for the transactions above:

TRANSACTION	TAG
3.29.732	112233
8.26.689	DEADBEEF

The following query shows only the redo opcode 5.20 (Transaction Audit) rows for these transactions:

SQL> col log_file old_v log_file
SQL>
SQL> select member log_file 
  2    from v$log l, 
  3         v$logfile f
  4   where l.status = 'CURRENT' 
  5     and f.group# = l.group#;

LOG_FILE
--------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/redo02.log

SQL>
SQL> exec dbms_logmnr.add_logfile('&log_file.')

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog)

PL/SQL procedure successfully completed.

SQL> select xidusn, xidslt, xidsqn, 
  2        dump_info 
  3   from x$logmnr_contents
  4  where (xidusn, xidslt, xidsqn) in ((3,29,732),(8,26,689)) 
  5    and scn between 2221571 and 2221592 
  6    and component_id = 5
  7    and opcode = 20;

    XIDUSN     XIDSLT     XIDSQN DUMP_INFO                                                                                           
---------- ---------- ---------- ----------------------------------------------------------------------------------------------------
         3         29        732 CHANGE #3 MEDIA RECOVERY MARKER CON_ID:3 SCN:0x0000000000000000 SEQ:0 OP:5.20 ENC:0 FLG:0x0000      
                                 session number   = 264                                                                              
                                 serial  number   = 29041                                                                            
                                 transaction name =                                                                                  
                                 version 318767104                                                                                   
                                 audit sessionid 24                                                                                  
                                 Client Id =                                                                                         
                                 login   username = TC                                                                               
                                 REPL MARKER:                                                                                        
                                  06 00 09 00 00 00 01 00 03 11 22 33                                                                


    XIDUSN     XIDSLT     XIDSQN DUMP_INFO                                                                                           
---------- ---------- ---------- ----------------------------------------------------------------------------------------------------
         8         26        689 CHANGE #3 MEDIA RECOVERY MARKER CON_ID:3 SCN:0x0000000000000000 SEQ:0 OP:5.20 ENC:0 FLG:0x0000      
                                 session number   = 264                                                                              
                                 serial  number   = 29041                                                                            
                                 transaction name =                                                                                  
                                 version 318767104                                                                                   
                                 audit sessionid 24                                                                                  
                                 Client Id =                                                                                         
                                 login   username = TC                                                                               
                                 REPL MARKER:                                                                                        
                                  06 00 0a 00 00 00 01 00 04 de ad be ef     

It appears that the 9th byte of a REPL MARKER value stores the length of the tag. Therefore, it is possible to extract a tag using the following query:

SQL> select xidusn, xidslt, xidsqn, repl_marker, 
  2         utl_raw.substr(repl_marker, 10, to_number(utl_raw.substr(repl_marker, 9, 1), 'xxxxxxxxxx')) tag,
  3         dump_info
  4    from (
  5          select xidusn, xidslt, xidsqn, 
  6                 hextoraw(replace(regexp_substr(dump_info, 'REPL MARKER:.(.*).', 1, 1, 'n', 1), ' ')) repl_marker, 
  7                 dump_info 
  8            from x$logmnr_contents
  9           where (xidusn, xidslt, xidsqn) in ((3,29,732),(8,26,689)) 
 10             and scn between 2221571 and 2221592 
 11             and component_id = 5
 12             and opcode = 20);

    XIDUSN     XIDSLT     XIDSQN REPL_MARKER                TAG        DUMP_INFO                                                                                           
---------- ---------- ---------- -------------------------- ---------- ----------------------------------------------------------------------------------------------------
         3         29        732 060009000000010003112233   112233     CHANGE #3 MEDIA RECOVERY MARKER CON_ID:3 SCN:0x0000000000000000 SEQ:0 OP:5.20 ENC:0 FLG:0x0000      
                                                                       session number   = 264                                                                              
                                                                       serial  number   = 29041                                                                            
                                                                       transaction name =                                                                                  
                                                                       version 318767104                                                                                   
                                                                       audit sessionid 24                                                                                  
                                                                       Client Id =                                                                                         
                                                                       login   username = TC                                                                               
                                                                       REPL MARKER:                                                                                        
                                                                        06 00 09 00 00 00 01 00 03 11 22 33                                                                


    XIDUSN     XIDSLT     XIDSQN REPL_MARKER                TAG        DUMP_INFO                                                                                           
---------- ---------- ---------- -------------------------- ---------- ----------------------------------------------------------------------------------------------------
         8         26        689 06000A000000010004DEADBEEF DEADBEEF   CHANGE #3 MEDIA RECOVERY MARKER CON_ID:3 SCN:0x0000000000000000 SEQ:0 OP:5.20 ENC:0 FLG:0x0000      
                                                                       session number   = 264                                                                              
                                                                       serial  number   = 29041                                                                            
                                                                       transaction name =                                                                                  
                                                                       version 318767104                                                                                   
                                                                       audit sessionid 24                                                                                  
                                                                       Client Id =                                                                                         
                                                                       login   username = TC                                                                               
                                                                       REPL MARKER:                                                                                        
                                                                        06 00 0a 00 00 00 01 00 04 de ad be ef  

It is quite handy that X$LOGMNR_CONTENTS.DUMP_INFO contains the same or, at least, the subset of the information present in the redo log dump, that allows to use it for sophisticated analysis in SQL*Plus without resorting to low-level dumps or the LogDump utility of Oracle GoldenGate.
I was using a vanilla 19.3 database on Linux for these experiments.

Tracing SQL Macro Expansion

I decided to take a look at what events Oracle provides to diagnose or troubleshoot SQL Macro Expansions.
For that, I took almost the same code that I used before: SQL Macro script

drop table t;
drop table t1;
drop table t2;

CREATE OR REPLACE FUNCTION sample(t DBMS_TF.Table_t, how_many number DEFAULT 5)
RETURN VARCHAR2 SQL_MACRO
AS 
BEGIN 
  RETURN q'[SELECT * 
              FROM t
      WHERE rownum <= how_many]';
END sample;
/

create table t
as
select n
  from xmltable('1 to 10'
  columns
    n int path '.');

create table t1
as
select n,
       'val_'||to_char(n, 'fm00') val
  from xmltable('1 to 10'
  columns
    n int path '.');

create table t2
as
select 'val_'||to_char(n, 'fm00') s
  from xmltable('1 to 10'
  columns
    n int path '.');

select * from t1;
select * from t2;

select *
  from sample(t1, 3);

select *
  from sample(t1);

select *
  from sample(t2);

A query failed when I was trying to supply the table T - which is the same that is used in the SQL macro's definition:

SQL> select *
  2    from sample(t);
  from sample(t)
               *
ERROR at line 2:
ORA-32039: recursive WITH clause must have column alias list

The errorstack dump for ORA-32039 (level=3) did not provide much information about the underlying cause:

----- Error Stack Dump -----
<error barrier> at 0x7ffd69f7aaf0 placed dbkda.c@296
ORA-32039: recursive WITH clause must have column alias list
----- Current SQL Statement for this session (sql_id=d3ktrfsd4s0sr) -----
select * from sample(t)

SQL Trace was referring to the same statement:

PARSE ERROR #140320112869816:len=24 dep=0 uid=119 oct=3 lid=119 tim=1054524149653 err=32039
select * from sample(t)

It showed the following statement, though:

PARSING IN CURSOR #140320116348624 len=446 dep=1 uid=119 oct=47 lid=119 tim=1054472624364 hv=3547957408 ad='7cda6688' sqlid='9awjhv79rm250'

declare
t0 DBMS_TF.Table_t := DBMS_TF.Table_t(column => :0);

begin
:macro_text := "SAMPLE"(t0);
end;

While studying those qksptfSQM functions, namely: qksptfSQM_GetTxt and qksptfSQM_Template, I found that they are calling to the PTF_Comp UTS component (Polymorphic Table Functions Compilation (qksptf)):

   0x000000000f75c87a <+4202>:  mov    $0x6000,%ecx
   0x000000000f75c87f <+4207>:  mov    $0x205018b,%esi
   0x000000000f75c884 <+4212>:  mov    $0x2,%edx
   0x000000000f75c889 <+4217>:  mov    -0x200(%rbp),%r8
   0x000000000f75c890 <+4224>:  mov    -0xcb58(%rbx),%rdi
   0x000000000f75c897 <+4231>:  callq  0x485cef0 <dbgtCtrl_intEvalCtrlEvent>

Therefore, I enabled PTF_Comp tracing:

alter session set events 'trace[ptf_comp]' tracefile_identifier=ptf_comp;

Here is what I got in the trace file:

qksptfSQM_GetTxt(): Anonymous Block
===================================

declare
t0 DBMS_TF.Table_t := DBMS_TF.Table_t(column => :0);

begin
:macro_text := "SAMPLE"(t0);
end;
qksptfSQM_GetTxt(): Macro Text
==============================

SELECT *
              FROM t
             WHERE rownum <= how_many
qksptfSQM_Template(): Template Text
===================================

with  T as (select  /*+ INLINE */ * from "MY_USER"."T")
select "SYS__$".*
from (select 5 "HOW_MANY" from SYS.DUAL) "SAMPLE",
     lateral(SELECT *
              FROM t
             WHERE rownum <= how_many) "SYS__$"

Obviously, the ORA-32039 error is absolutely legit in this case and the PTF_Comp tracing clearly shows where the issue is. It highlights one of the current limitations of that functionality in Oracle 19.6. Beside it demonstrates how literals (HOW_MANY in the example above) are passed through a subquery and joined to a lateral view - that approach has certain restrictions. Apparently there seem to be more restrictions than that according to $ORACLE_HOME/plsql/mesg/pcmus.msg:

776, 0, "SQL macro can only return character return types"
// INDEX: "SQL macro"
// RELEASE: 20.1
// CAUSE: A SQL macro function returned a type that was not a character type like VARCHAR2.
// ACTION:  Change the return type to one of the character types.
777, 0, "scalar SQL macro cannot have argument of type DBMS_TF.TABLE_T"
// INDEX: "SQL macro"
// RELEASE: 20.1
// CAUSE: DBMS_TF.TABLE_T argument was used for scalar macros.
// ACTION:  Change the argument type to a type other than
//          DBMS_TF.TABLE_T.
778, 0, "PARTITION BY, ORDER BY, CLUSTER BY, or PARALLEL_ENABLE are not allowed for SQL macros"
// MANUAL: partition by, order by, cluster by, deterministic, authid or parallel_enable
//         not allowed for SQL macros
// INDEX: "SQL macro"
// RELEASE: 20.1
// CAUSE: An attempt was made to use the PARTITION BY, ORDER BY, CLUSTER BY,
//        DETERMINISTIC, AUTHID, or PARALLEL_ENABLE clause in a
//        SQL macro.
// ACTION:  Do not specify the PARTITION BY, CLUSTER BY, ORDER BY, DETERMINISTIC, AUTHID, or
//          PARALLEL_ENABLE clause with a SQL macro.
779, 0, "Formal parameters of type TABLE cannot have a default value."
// MANUAL: A default value cannot be specified for parameter of type TABLE.
// INDEX:  "SQL macros"
// RELEASE: 20.1
// CAUSE: A default value was specified for a formal parameter of type TABLE
//        in a SQL macro declaration.
// ACTION: Remove the default value from the parameter of type TABLE in
//         the SQL macro declaration.
//
780, 0, "Formal parameters of type COLUMNS may only have a NULL default value."
// MANUAL:  Parameters of type COLUMNS can only have a NULL default value.
// INDEX:  "SQL macros"
// RELEASE: 20.1
// CAUSE:  A non-NULL default value was specified for a formal parameter of type COLUMNS
//        in a polymorphic table function.
// ACTION: Change the default value for the parameter of type COLUMNS to
//         have a NULL default value in the polymorphic table function
//         specification.
//
//
781, 0, "SQL macro cannot be a type method"
// INDEX:  "SQL macros"
// RELEASE: 20.1
// CAUSE: The SQL macro could not be declared as a method in a type specification.
// ACTION: Use the SQL macro as a package function or a top level function.
//
782, 0, "A package containing SQL macro cannot be an invoker's rights package."
// INDEX:  "SQL macros"
// RELEASE: 20.1
// CAUSE: A package could not be declared as AUTHID CURRENT_USER if it had a SQL
//        macro function declared inside.
// ACTION: Declare the package as AUTHID DEFINER if it contains one or more
//         SQL macro functions.

Keep in mind that this functionality is not officially released and only a limited subset of it is available in 19.6: Bug 30324180 SQL Macro should be backported to 19.5. Hence, the final version of SQL Macro in Oracle 20 might be different from what we have now in Oracle 19.6.

Bug 30324180 SQL Macro should be backported to 19.5

When Oracle released 19.6 release update, I went to the usual My Oracle Support (MOS) note: Database 19 Release Updates and Revisions Bugs Fixed Lists (Doc ID 2523220.1) to find out what exactly was included there that might be of interest for me. Surprisingly, the note was not updated in several days, so that I decided to download the release update and take a look at it myself.
Oracle patches follow a well-defined structure that was described by Martin Berger: Oracle patches - a patchfiles anatomy.

One bug drew my eye:

<bug description="SQM MACRO SHOULD BE BACKPORTED TO 19.5" number="30324180"/>

The relevant note contains some important information, albeit without any examples:
Bug 30324180 - SQL Macro should be backported to 19.5 (Doc ID 30324180.8)
Thankfully, there is a clue that the table semantics macro should be supported, so that I decided to skim through an excellent Keith Laker's presentation on that subject: A New Approach to Simplifying Complex SQL-Using SQL Macros in Database 20c.

After a bit of toing and froing, I constructed the following example that demonstrates this new functionality (as it seems).

SQL> create table t1
  2  as
  3  select n,
  4         'val_'||to_char(n, 'fm00') val
  5    from xmltable('1 to 10'
  6           columns
  7             n int path '.');

Table created.

SQL>
SQL> create table t2
  2  as
  3  select 'val_'||to_char(n, 'fm00') s
  4    from xmltable('1 to 10'
  5           columns
  6             n int path '.');

Table created.

SQL>
SQL> select * from t1;

         N VAL
---------- -------
         1 val_01
         2 val_02
         3 val_03
         4 val_04
         5 val_05
         6 val_06
         7 val_07
         8 val_08
         9 val_09
        10 val_10

SQL> select * from t2;

S
-------
val_01
val_02
val_03
val_04
val_05
val_06
val_07
val_08
val_09
val_10

I created two tables with a different structure. The MOS note says about the table semantics macro, which is supposedly demonstrated on 22nd slide of Keith's presentation.
I was not able to get SQL_MACRO(TABLE) work, so that I ended up with the following example:

SQL> CREATE OR REPLACE FUNCTION sample(t DBMS_TF.Table_t, how_many number DEFAULT 5)
  2  RETURN VARCHAR2 SQL_MACRO
  3  AS
  4  BEGIN
  5    RETURN q'[SELECT *
  6                FROM t
  7               WHERE rownum <= how_many]';
  8  END sample;
  9  /

Function created.

SQL> select *
  2    from sample(t1, 3);

         N VAL
---------- -------
         1 val_01
         2 val_02
         3 val_03

SQL>
SQL> select *
  2    from sample(t1);

         N VAL
---------- -------
         1 val_01
         2 val_02
         3 val_03
         4 val_04
         5 val_05

SQL>
SQL> select *
  2    from sample(t2);

S
-------
val_01
val_02
val_03
val_04
val_05

I traced the last two statements through Intel Pin debugtrace and grepped the resulted file for the SQM string (which are presumably Oracle functions corresponding to the SQL Macro functionality):

<> qksptfSQM_Describe(0x7ff400bc6050, 0x6b76cbd8, ...)
| > qksptfSQM_Init(0x7ff400bc6050, 0x6b76cbd8, ...)
| | > qksptfSQM_Check_Errors(0x6b76cdf8, 0x7ff400b2c7a8, ...)
| | < qksptfSQM_Check_Errors+0x000000000090 returns: 0x7ff400b2c770
| < qksptfSQM_Init+0x0000000005d5 returns: 0x7ff400b2c7a8
| > qksptfSQM_GetTxt(0x7ff400b2c7a8, 0x1, ...)
| | | | | | | | | | | < qksptfSQM_GetTxt+0x000000001d51 returns: 0x6b76cdf8
| | | | | | | | | | | > qksptfSQM_Template(0x7ff400b2c7a8, 0, ...)
| | | | | | | | | | | < qksptfSQM_Template+0x0000000009ca returns: 0x6b76cdf8
| | | | | | | | | | | > qksptfSQM_Parse(0x7ff400b2c7a8, 0, ...)
| | | | | | | | | | | | | | | | | | > qksptfSQM_Parse_errors(0x7ff400b2c448, 0xc0, ...)
| | | | | | | | | | | | | | | | | | | | | | | | < qksptfSQM_Parse_errors+0x00000000069a returns: 0
| | | | | | | | | | | | | | | | | | | | | | | | | > qksptfSQM_Model_Check(0x7ff400bba928, 0, ...)
| | | | | | | | | | | | | | | | | | | | | | | | | < qksptfSQM_Model_Check+0x000000000040 returns: 0x1
| | | | | | | | | | | | | | | | | | | | | | | | | | > qksptfSQM_Model_Check(0x7ff405adf108, 0, ...)
| | | | | | | | | | | | | | | | | | | | | | | | | | < qksptfSQM_Model_Check+0x000000000040 returns: 0x1
| | | | | | | | | | | | | | | | | | | | | | | | | | | > qksptfSQM_Model_Check(0x7ff405adde48, 0, ...)
| | | | | | | | | | | | | | | | | | | | | | | | | | | < qksptfSQM_Model_Check+0x000000000040 returns: 0x1
| | | | | | | | | | | | | | | | | | | | | | | | | | | > qksptfSQM_Model_Check(0x7ff405ade8a8, 0, ...)
| | | | | | | | | | | | | | | | | | | | | | | | | | | < qksptfSQM_Model_Check+0x000000000040 returns: 0x1
| | | | | | | | | | | | | | | | | | | | | | | | | | > qksptfSQM_Model_Check(0x7ff400bc4260, 0, ...)
| | | | | | | | | | | | | | | | | | | | | | | | | | < qksptfSQM_Model_Check+0x000000000040 returns: 0x1
| | | | | | | | | | | | | | | | | | | | | | | | | > qksptfSQM_rm_vqb(0x7ff400bc4260, 0x7ff400bba928, ...)
| | | | | | | | | | | | | | | | | | | | | | | | | < qksptfSQM_rm_vqb+0x00000000003d returns: 0x1
| | | | | | | | | | | | | | | | | | | | | | | | | > qksptfSQM_SetSQM_(0x7ff405adf108, 0x7ff400b2c7a8, ...)
| | | | | | | | | | | | | | | | | | | | | | | | | | | > qksptfSQM_SetSQM_(0x7ff400b2c110, 0x7ff400b2c7a8, ...)
| | | | | | | | | | | | | | | | | | | | | | | | | | | < qksptfSQM_SetSQM_+0x00000000007e returns: 0x1
| | | | | | | | | | | | | | | | | | | | | | | | | < qksptfSQM_SetSQM_+0x00000000007e returns: 0x1
| | | | | | | | | | | | | | | | | | | | | | | | | | > qksptfSQM_SetSQM_(0x7ff405adde48, 0x7ff400b2c7a8, ...)
| | | | | | | | | | | | | | | | | | | | | | | | | | < qksptfSQM_SetSQM_+0x00000000007e returns: 0x1
| | | | | | | | | | | | | | | | | | | | | | | | | | > qksptfSQM_SetSQM_(0x7ff405ade8a8, 0x7ff400b2c7a8, ...)
| | | | | | | | | | | | | | | | | | | | | | | | | | < qksptfSQM_SetSQM_+0x00000000007e returns: 0x1
| | | | | | | | | | | | | | | | | | | | | | | < qksptfSQM_Parse+0x000000000851 returns: 0x1
| | | | | | | | | | | | | | | | | | | | | | | > qksptfSQM_CleanupTDOs(0x3, 0x872027e0, ...)
| | | | | | | | | | | | | | | | | | | | | | | < qksptfSQM_CleanupTDOs+0x0000000001cd returns: 0x7ff400a5d73c
| | | | | | | | | | | | > qksptfSQM_PstPrc(0x7ff400bba928, 0x98b7af08, ...)
| | | | | | | | | | | | | > qksptfSQM_PstPrc1_(0x7ff400bba928, 0x7ff400bba928, ...)
| | | | | | | | | | | | | < qksptfSQM_PstPrc1_+0x000000000032 returns: 0x1
| | | | | | | | | | | | | | > qksptfSQM_PstPrc1_(0x7ff405adf108, 0x7ff400bba928, ...)
| | | | | | | | | | | | | | | | > qksptfSQM_QbcRelExp_(0x7ff405adde48, 0x7ff405aded30, ...)
| | | | | | | | | | | | | | | | | | | > qksptfSQM_ExpRepl_(0x7ffef8fc52e0, 0x7ff405ad8818, ...)
| | | | | | | | | | | | | | | | | | | < qksptfSQM_ExpRepl_+0x00000000005a returns: 0
| | | | | | | | | | | | | | | | | | | > qksptfSQM_ExpRepl_(0x7ffef8fc52e0, 0x7ff405addf10, ...)
| | | | | | | | | | | | | | | | | | | < qksptfSQM_ExpRepl_+0x00000000005a returns: 0
| | | | | | | | | | | | | | | | | | | | > qksptfSQM_ExpRepl_(0x7ffef8fc52e0, 0x7ff405add890, ...)
| | | | | | | | | | | | | | | | | | | | < qksptfSQM_ExpRepl_+0x00000000005a returns: 0
| | | | | | | | | | | | | | | | | | | | | > qksptfSQM_ExpRepl_(0x7ffef8fc52e0, 0x7ff405add850, ...)
| | | | | | | | | | | | | | | | | | | | | < qksptfSQM_ExpRepl_+0x00000000005a returns: 0
| | | | | | | | | | | | | | | | | | | | | | > qksptfSQM_ExpRepl_(0x7ffef8fc52e0, 0x7ff405adda28, ...)
| | | | | | | | | | | | | | | | | | | | | | < qksptfSQM_ExpRepl_+0x00000000005a returns: 0
| | | | | | | | | | | | | | | | | | | | | > qksptfSQM_ExpRepl_(0x7ffef8fc52e0, 0x7ff405add858, ...)
| | | | | | | | | | | | | | | | | | | | | < qksptfSQM_ExpRepl_+0x00000000005a returns: 0
| | | | | | | | | | | | | | | | < qksptfSQM_QbcRelExp_+0x000000000079 returns: 0x1
| | | | | | | | | | | | | | | | | > qksptfSQM_QbcRelExp_(0x7ff400b2c110, 0x7ff405aded30, ...)
| | | | | | | | | | | | | | | | | | | | > qksptfSQM_ExpRepl_(0x7ffef8fc5270, 0x7ff405add1c8, ...)
| | | | | | | | | | | | | | | | | | | | < qksptfSQM_ExpRepl_+0x00000000005a returns: 0
| | | | | | | | | | | | | | | | | < qksptfSQM_QbcRelExp_+0x000000000079 returns: 0x1
| | | | | | | | | | | | | | < qksptfSQM_PstPrc1_+0x000000000181 returns: 0x1
| | | | | | | | | | | | | | | > qksptfSQM_PstPrc1_(0x7ff405adde48, 0x7ff400bba928, ...)
| | | | | | | | | | | | | | | < qksptfSQM_PstPrc1_+0x000000000032 returns: 0x1
| | | | | | | | | | | | | | | | > qksptfSQM_PstPrc1_(0x7ff400b2c110, 0x7ff400bba928, ...)
| | | | | | | | | | | | | | | | < qksptfSQM_PstPrc1_+0x000000000032 returns: 0x1

As Fritz Hoogland identified them, those are new functions introduced in Oracle 19.6: What’s new with Oracle database 19.6 versus 19.5. I will wait till Oracle officially announces SQL Macros. For those who cannot wait, they can play around with them even in 19.6. Judging by the bug description, the relevant functionality should be available for 19.5 (introduced without much fuss), but I have not found a one-off patch. It might have been set unpublished.

Patching Oracle Grid Infrastructure Standalone Environment Using opatchauto Out of Place (OOP) Patching

The MOS article Grid Infrastructure Out of Place ( OOP ) Patching using opatchauto (Doc ID 2419319.1) describes how to perform opatchauto Out of Place Patching of a Grid Infrastructure environment. The article assumes that commands are to be entered interactively. Although it is possible to automate the patching process using the Linux expect command, an alternative approach utilizing property files can be adapted to perform unattended patching.

Initial setup

I have a Grid Infrastructure Oracle Home /u01/app/oracle/product/19.4.0/grid on a Linux server. That is Grid Infrastructure Release Update 19.4.0.0.190716.
I would like to install Grid Infrastructure Release Update 19.5.0.0.191015 to a new location /u01/app/oracle/product/19.5.0/grid.
There are also a few 12.2 databases running on the same server.
I will not be using the 2 Step Method from the aforementioned MOS article, so that I will not be running the prepare-clone and switch-clone commands.
The patching will be performed by running a single command that can be easily scripted and used from any IT automation tool, such as Ansible.

Configuring Oracle Home mapping file

Here is an excerpt from the OPatch User's Guide describing how to provide original and cloned home details:

To create a property file, add all the required original and cloned home details and save the file with the .properties extension.
For example: clone.properties.

Following is an example of the property file:

/scratch/product/12.2/<original_home>=/scratch/product/12.2/<cloned_home>
/scratch/product/db/12.2/<original_home>=/scratch/product/db/12.2/<cloned_home>

Where,

<original_home> is the folder name of the original home.

<cloned_home> is the folder name of the clone home to which the original home would be cloned to.

Hence, I create a property file with the following contents:

# cat clone.properties
/u01/app/oracle/product/19.4.0/grid=/u01/app/oracle/product/19.5.0/grid

Analyzing the patch applicability

[root@emrep 30116789]# opatchauto apply -outofplace -phBaseDir /u01/app/oracle/stage/p30116789_190000_Linux-x86-64_gi_ru_19.5.0.0.191015 \
> -oh /u01/app/oracle/product/19.4.0/grid \
> -silent /u01/app/oracle/stage/p30116789_190000_Linux-x86-64_gi_ru_19.5.0.0.191015/30116789/clone.properties \
> -analyze

OPatchauto session is initiated at Thu Dec 12 12:09:32 2019

System initialization log file is /u01/app/oracle/product/19.4.0/grid/cfgtoollogs/opatchautodb/systemconfig2019-12-12_12-09-36PM.log.

Session log file is /u01/app/oracle/product/19.4.0/grid/cfgtoollogs/opatchauto/opatchauto2019-12-12_12-09-41PM.log
The id for this session is ATZ9

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.4.0/grid
Patch applicability verified successfully on home /u01/app/oracle/product/19.4.0/grid

OPatchAuto successful.

--------------------------------Summary--------------------------------
Out of place patching clone home(s) summary
____________________________________________
Host : emrep
Actual Home : /u01/app/oracle/product/19.4.0/grid
Version:19.0.0.0.0
Clone Home Path : /u01/app/oracle/product/19.5.0/grid


Analysis for applying patches has completed successfully:

Host:emrep
SIHA Home:/u01/app/oracle/product/19.4.0/grid
Version:19.0.0.0.0


==Following patches were SKIPPED:

Patch: /u01/app/oracle/stage/p30116789_190000_Linux-x86-64_gi_ru_19.5.0.0.191015/30116789/29401763
Reason: This patch is already been applied, so not going to apply again.


==Following patches were SUCCESSFULLY analyzed to be applied:

Patch: /u01/app/oracle/stage/p30116789_190000_Linux-x86-64_gi_ru_19.5.0.0.191015/30116789/30122149
Log: /u01/app/oracle/product/19.4.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-12-12_12-10-00PM_1.log

Patch: /u01/app/oracle/stage/p30116789_190000_Linux-x86-64_gi_ru_19.5.0.0.191015/30116789/30122167
Log: /u01/app/oracle/product/19.4.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-12-12_12-10-00PM_1.log

Patch: /u01/app/oracle/stage/p30116789_190000_Linux-x86-64_gi_ru_19.5.0.0.191015/30116789/30125133
Log: /u01/app/oracle/product/19.4.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-12-12_12-10-00PM_1.log



OPatchauto session completed at Thu Dec 12 12:10:14 2019
Time taken to complete the session 0 minute, 42 seconds

Stopping Oracle Databases that use Oracle ASM

Since I would like to patch only the Grid Infrastructure Oracle Home, which implies its unavailability in my setup, I can stop all Oracle databases beforehand. Otherwise, they will be shutting down with the abort option. The following lines from the alert log confirm that the database was brought down using the abort option on one server where I was testing the Out of Place patching:

Thu Dec 12 12:26:00 2019
Shutting down instance (abort) (OS id: 13679)
License high water mark = 97
Thu Dec 12 12:26:00 2019
USER (ospid: 13679): terminating the instance
Thu Dec 12 12:26:01 2019
Instance terminated by USER, pid = 13679

Applying the Release Update

[root@emrep 30116789]# opatchauto apply -outofplace -phBaseDir /u01/app/oracle/stage/p30116789_190000_Linux-x86-64_gi_ru_19.5.0.0.191015 \
> -oh /u01/app/oracle/product/19.4.0/grid \
> -silent /u01/app/oracle/stage/p30116789_190000_Linux-x86-64_gi_ru_19.5.0.0.191015/30116789/clone.properties

OPatchauto session is initiated at Thu Dec 12 12:12:57 2019

System initialization log file is /u01/app/oracle/product/19.4.0/grid/cfgtoollogs/opatchautodb/systemconfig2019-12-12_12-13-01PM.log.

Session log file is /u01/app/oracle/product/19.4.0/grid/cfgtoollogs/opatchauto/opatchauto2019-12-12_12-13-06PM.log
The id for this session is IMWY

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.4.0/grid
Patch applicability verified successfully on home /u01/app/oracle/product/19.4.0/grid


Copying the files from the existing oracle home /u01/app/oracle/product/19.4.0/grid to a new location. Please wait...
Clone of oracle home /u01/app/oracle/product/19.4.0/grid is /u01/app/oracle/product/19.5.0/grid on host emrep
Copying the files from the existing oracle home /u01/app/oracle/product/19.4.0/grid to a new location is successful.


Unlocking CRS clone home for home /u01/app/oracle/product/19.4.0/grid.
Prepatch operation log file location: /u01/app/oracle/crsdata/emrep/crsconfig/hapatch_2019-12-12_12-16-50AM.log
Unlocked CRS clone home successfully for home /u01/app/oracle/product/19.4.0/grid.


Creating clone for oracle home /u01/app/oracle/product/19.4.0/grid.
Clone operation successful for oracle home /u01/app/oracle/product/19.4.0/grid.


Performing post clone operation for oracle home /u01/app/oracle/product/19.4.0/grid.
Performing post clone operation was successful for oracle home /u01/app/oracle/product/19.5.0/grid.


Start applying binary patch on home /u01/app/oracle/product/19.5.0/grid
Binary patch applied successfully on home /u01/app/oracle/product/19.5.0/grid


Update nodelist in the inventory for oracle home /u01/app/oracle/product/19.5.0/grid.
Update nodelist in the inventory is completed for oracle home /u01/app/oracle/product/19.5.0/grid.


Starting CRS service on home /u01/app/oracle/product/19.5.0/grid
Postpatch operation log file location: /u01/app/oracle/crsdata/emrep/crsconfig/hapatch_2019-12-12_12-25-58AM.log
CRS service started successfully on home /u01/app/oracle/product/19.5.0/grid


Confirm that all resources have been started from home /u01/app/oracle/product/19.5.0/grid.
All resources have been started successfully from home /u01/app/oracle/product/19.5.0/grid.



OPatchAuto successful.

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:emrep
SIHA Home:/u01/app/oracle/product/19.4.0/grid
Version:19.0.0.0.0
Summary:

==Following patches were SKIPPED:

Patch: /u01/app/oracle/stage/p30116789_190000_Linux-x86-64_gi_ru_19.5.0.0.191015/30116789/29401763
Reason: This patch is already been applied, so not going to apply again.


==Following patches were SUCCESSFULLY applied:

Patch: /u01/app/oracle/stage/p30116789_190000_Linux-x86-64_gi_ru_19.5.0.0.191015/30116789/30122149
Log: /u01/app/oracle/product/19.5.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-12-12_12-18-01PM_1.log

Patch: /u01/app/oracle/stage/p30116789_190000_Linux-x86-64_gi_ru_19.5.0.0.191015/30116789/30122167
Log: /u01/app/oracle/product/19.5.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-12-12_12-18-01PM_1.log

Patch: /u01/app/oracle/stage/p30116789_190000_Linux-x86-64_gi_ru_19.5.0.0.191015/30116789/30125133
Log: /u01/app/oracle/product/19.5.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-12-12_12-18-01PM_1.log


Patching session reported following warning(s):
_________________________________________________

[Note]: Please verify the database is running from the desired Oracle home, if not then manually execute
 $ORACLE_HOME/bin/srvctl modify database command to fix the problem


Out of place patching clone home(s) summary
____________________________________________
Host : emrep
Actual Home : /u01/app/oracle/product/19.4.0/grid
Version:19.0.0.0.0
Clone Home Path : /u01/app/oracle/product/19.5.0/grid


OPatchauto session completed at Thu Dec 12 12:37:27 2019
Time taken to complete the session 24 minutes, 30 seconds
[root@emrep 30116789]#

Thus, it took just 24 minutes to perform the patching on that server; it is an AWS t3.large instance.
The time can be even shortened more by preparing the cloned Oracle Home in advance.
A rough estimate on the log above can be made that the patching time may be shortened twice using the 2 Step Method.

SQL92_SECURITY and ORA-01031

SQL92_SECURITY has changed its default value to TRUE starting from 12.2.0.1: Upgrade Guide.
That might lead to ORA-01031: insufficient privileges errors being thrown in runtime as the following script demonstrates.

SQL> grant create session, create table, unlimited tablespace to tc_data identified by tc_data;

Grant succeeded.

SQL>
SQL> create table tc_data.t
  2  as
  3  select *
  4    from dual;

Table created.

SQL>
SQL> grant create session, create procedure to tc_app identified by tc_app;

Grant succeeded.

SQL>
SQL> grant delete on tc_data.t to tc_app;

Grant succeeded.

SQL>
SQL> create or replace procedure tc_app.p
  2  is
  3  begin
  4    delete tc_data.t
  5     where dummy = 'X';
  6  end;
  7  /

Procedure created.

SQL>
SQL> exec tc_app.p
BEGIN tc_app.p; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TC_APP.P", line 4
ORA-06512: at line 1

Although the procedure is valid, it throws an ORA-01031 error as soon as the statement refers to any table columns including pseudo-columns, such as ROWID (I have tested it only for ROWID).

I have even seen a case similar to the one below, when the code throws an error depending on its input parameters:

SQL> create or replace procedure tc_app.p_collection(p_tbl sys.odcivarchar2list)
  2  is
  3  begin
  4    forall i in 1..p_tbl.count
  5      delete tc_data.t
  6       where dummy = p_tbl(i);
  7  end;
  8  /

Procedure created.

SQL>
SQL> exec tc_app.p_collection(sys.odcivarchar2list())

PL/SQL procedure successfully completed.

SQL> exec tc_app.p_collection(sys.odcivarchar2list('y'))
BEGIN tc_app.p_collection(sys.odcivarchar2list('y')); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TC_APP.P_COLLECTION", line 4
ORA-06512: at line 1

I can imagine that Oracle has not implemented the behavior when the code does not compile if it is known that there is a missing privilege due to SQL92_SECURITY=TRUE - it is just an instance parameter and it can be changed back and forth. Thus, it would make the status of the objects misleading. For instance, a valid PL/SQL unit with SQL92_SECURITY=FALSE, should either become invalid or throw a runtime ORA-01031 error when SQL92_SECURITY=TRUE.
However, it might come in handy to have a PL/SQL warning at least to identify possible missing privileges.
It can also be a good idea to change the scope of the SQL92_SECURITY parameter and make it one of the PL/SQL compiler settings of the stored objects shown in DBA_PLSQL_OBJECT_SETTINGS.