Freitag, 15. April 2022

qgis with Oracle Cloud Database

qgis is A Free and Open Source Geographic Information System. It can use several spatial backends, and Oracle is just one of them. From what I see it is quite often used with other databases like PostgreSQL or even SpatiaLite. And even Oracle is quite well supported, there sem to be some loose ends, especially for complex situations.

One of these complex situations is the connection to an Always Free Autonomous Database. Oracle tries to secure the database access, so by default it does not allow a simple connection with host, port, service_name and username/password. Instead a wallet is provided to secure the network access before providing the credentials. 

The zip contains several files which can be used for OCI or JDBC connections. In it's tnsnames.ora the aliases are slightly more complex than normally expected:

db1234_medium = 
      (ssl_server_cert_dn=", OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US")
On the other hand, qgis (3.24.1) provides these input fields:
Name, Database, Host and Port define the connection endpoint. But there is no field available for additional details like protocol=tcps or ssl_server_cert_dn=.... Also the specific wallets location can not be provided. 

But it is possible to add all these details. To understand the method, it's required to understand how qgis simply concatenates the given strings to one connection URL which is then handed over to the Oracle client libraries. This client also accepts an Easy Connect String.  
With this information, I managed to generate these values: 
Database", OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US"&wallet_location=C:\tns_admin

Here are 3 important details: in the Database field, you can add information about the certificates DN and the wallet location. In the Host field, the prefix tcps:// defines the specific protocol used. 
This alltogether generates an URL
tcps://", OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US"&wallet_location=C:\tns_admin
which is sufficient to connect to my ATP Database 

Dienstag, 22. Februar 2022

sqltrace - stitch together one EXPERIENCE ID from different tracefiles


Today I stumbled across an interesting question:
(slightly edited)

How can I cut one specific EXPERIENCE ID from several SQL-tracefiles and merge it together in one (artificial) tracefile for further processing.

For those who are familiar with SQL-Trace files, the term EXPERIENCE ID might be dubious. That's for a good reason: The EXPERIENCE ID is developed by Cary Millsap and Jeff Holt to identify one specific experience. It can be implemented by several ways - here an example from The Method R Guide to Mastering Oracle Trace Data:

    dbms_session.session_trace_enable(true, false, 'first_execution');
    dbms_log.ksdwrt(1, '*** EXPERIENCE ID:('||sys_guid()||')'); 
    dbms_application_info.set_module('OE', 'BOOK');

select 'your application goes here' from dual
    dbms_application_info.set_module('', '');
    dbms_log.ksdwrt(1, '*** EXPERIENCE ID:()'); dbms_session.session_trace_disable();

You can imagine, all the tools in the Method R Workbench can deal with this artificial trace entry. 

Now the initial request is still somehow special: Normally one EXPERIENCE ID will be visible only in one tracefile. But in this special implementation, one experience can be spread over several tracefiles due to the applications special handling of its connection pool.

There is no simple tool to grab all the different pieces and glue them together: 

  • oracles trcsess does not know about the EXPERIENCE ID, so it is of no help here to identify ine experience. 
  • Method-Rs mrcrop can create single files for each EXPERIENCE ID, but it doesn't glue them together as required. 
But both can be combined in a quite nice way: 
mrcrop can filter for a specific experience with the parameters experience ‑‑id=$experience
and trcsess can stitch together several tracefiles, when they have one of its filter-criteria in common. 
This leads to a simple pseudo-code which does the trick :

for each experience ID:
  {purge previous mrcrop directories}
  for each XE_ora_*.trc
    mrcrop experience --id=$experience XE_ora_file.trc
  trcsess output=$experience.trc service=xepdb1 XE_ora_*/XE_ora*.trc 

By this we have one tracefile for each experience - goal achieved.

Samstag, 15. Januar 2022

eBPF on ExaCC


Recently I had to answer for a customer a quite simple question: 
Which processes suffer from having their memory in swap?
Basically having some memory pages swapped out and the physical memory used for soemething else is not a problem at all. Only if and when these pages are required by the program, it has to wait. This translates the question into a question easier to answer: 

Which processes need to wait for memory pages read from swap (every second).

The processes does not know if they are waiting for a memory page to be read in. It's not a syscall they do. The process only waits. So tools like strace does not provide any information here. 

Luckily there is a quite clever engine for instrumentation (and other useful extension like networking, security, ...) vailable in the modern linux kernel, called eBPF. Brendan Gregg wrote a suite of linux tracing tools. There you can find a tool called swapin (manpage, examples). 

Unfortunately, Oracle doesn't seem to think, performance tools are of any use in Exadata Cloud at Customers (ExaCC) and so bcc/eBPF isn't part of their installation image. Luckily at the end it's a simple Oracle Linux, and in the public repositories the rpms are available. 
I just had to install 
the packages
bcc  python-bcc  bcc-tools 

the packages 
kernel-uek-devel matching  uname -r

Unfortunately I didn't find swapin script in /usr/share/bcc/tools, so I had to create it there. 

But now, after these steps, we can answer if having memory in swap on a system is bad at all and which processes are affected. 
(of course, after all these preparations, the symptoms were gone and there is nothing to observe. But now everything is prepared!) 

Samstag, 25. Dezember 2021

under DMUs cover

 Oracle Data Migration Assistant for Unicode  (DMU) is a nice tool to convert databases (but no CDB$ROOT, sorry) from any character set to unicode. 

It is a nice java tool which is described already, So I will focus more on the steps what's going on. 

DMU uses an internal package DBMS_DUMA_INTERNAL which just creates an interface to DUMA_LIB library. 

Beside that, in my simple testcase DMU used the undocumented function SYS_OP_CSCONV(<column>, <target characterset> [, <assumend source characterset>] ). Even it is not part of the SQL documentation, it can be found in the DMU documentation

To enambe DMU doing all it's magic, some events are set. In my case they were: 


and at the end, another undocumented command is executed: 


The lastest (21) docu tells: 

You can no longer change the database character set or the national character set using the ALTER DATABASE statement. Refer to Oracle Database Globalization Support Guide for information on database character set migration.

Of course using any of these statements is totally unsupported (even others already used some parts to fix existing problems) it's woth to know SYS_OP_CSCONV() can be used in any case where "wrong" characters were inserted and need to be fixed. 

One last bit I stumbled across: it seems even DMU can not manipulate AUD$INIFIED:

There is one even less documented function: SYS_OP_CSCONVTEST. It also has 3 parameters, I just assume these are equal to those of SYS_OP_CSCONV
It returns a number, in my tests there were 3 possible results:
1needs conversion
0no conversion needed
-1no conversion possible

Let's hope this is of any help.

Freitag, 20. August 2021

ASM lsof with process ID

 Oracle ASM can list files which belong to a (current active) instance. 

The command is lsof. Unfortunately it does not provide the process-ID which holds the file open. lsof gets the data from internal view X$KFFOF, which contains additional information.

Based on all I found (which is close to nothing) I can provide this SQL - might it help, to find the holder of an open AMS file form now on.

     , lsof.NUMBER_KFFOF       as FILE_NUMBER
     , lsof.DBNAME_KFFOF       as DB_NAME
     , lsof.OSID_KFFOF         as OS_PID
     , lsof.PATH_KFFOF         as PATH
     , lsof.FLAGS_KFFOF        as FLAGS 
  AND bitand(flags_kffof, to_number('00004000', 'XXXXXXXX')) = 0  -- kffilf_invalid = 0x00004000

Sonntag, 2. Mai 2021

tracing network ACLs

If there are problems with ACLs (Access Control Lists) in Oracle, the note SRDC - How to Collect Standard Information for Access Control Lists (ACLs) (Doc ID 1905572.1) is an important starting point.
Basically it suggests to enable these events.

alter session set events '10937 trace name context forever, level 7'; 
alter session set events '24247 trace name errorstack level 3';
But the only suggestion is to open a SR at oracle and send the results in. Sometimes, it already helps to understand parts of the trace file to identify an issue - no need to invest precious time on MOS. First the events itself. Their meaning are:

10937, 00000, "trace name context forever"
// * Cause:
// * Action: When set, this event enables tracing of PL/SQL packages.
// * Comment: Setting this event could result in some performance slowdown.

24247, 00000, "network access denied by access control list (ACL)"
// *Cause:    No access control list (ACL) has been assigned to the target
//            host or the privilege necessary to access the target host has not
//            been granted to the user in the access control list.
// *Action:   Ensure that an access control list (ACL) has been assigned to
//            the target host and the privilege necessary to access the target
//            host has been granted to the user.
Unfortunately I did not find a comprehensive documentation of these events levels, maybe this is area for some other posts.

A first try with a newly created user (grants: connect, resource & alter session) fails as expected:

SQL> select utl_http.request(url => '') from dual;

Error starting at line : 1 in command -
select utl_http.request(url => '') from dual
Error report -
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1530
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1470
ORA-06512: at line 1
With no ACLS at all, it's obvious.
According tho the documentation of UTL_HTTP:

This package is an invoker's rights package. The invoking user will need the connect privilege granted in the access control list assigned to the remote network host to which he wants to connect, as well as the use-client-certificates or the use-passwords privilege to authenticate himself with the remote Web server using the credentials stored in an Oracle wallet.

So connect it is: 

  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => '', 
    lower_port => 80,
    upper_port => 443,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'BERX',
                              principal_type => xs_acl.ptype_db)); 
With this privilege, let's see what the ACL trace file says. (I have to set event 10937 to level 6 (not 7) in my lab env - lvl7 did not generate anything). 

SQL> alter session set events '10937 trace name context forever, level 6';

Session altered.

SQL> alter session set events '24247 trace name errorstack level 3';

Session altered.

SQL> select utl_http.request(url => '') from dual;

<html code="" itemscope="" itemtype="" lan...
The select is fine now - and what's in the trace?

Trace file /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_5483.trc
Oracle Database 19c Enterprise Edition Release - Production
Build label:    RDBMS_19.
ORACLE_HOME:    /u01/app/oracle/product/19.0.0/dbhome_1
System name:    Linux
Node name:      localhost.localdomain
Release:        5.4.17-2011.5.3.el8uek.x86_64
Version:        #2 SMP Wed Jul 29 22:09:11 PDT 2020
Machine:        x86_64
Instance name: cdb1
Redo thread mounted by this instance: 1
Oracle process number: 53
Unix process pid: 5483, image: oracle@localhost.localdomain

*** 2021-05-02T07:43:38.309047+00:00 (PDB1(3))
*** SESSION ID:(270.62847) 2021-05-02T07:43:38.309125+00:00
*** CLIENT ID:() 2021-05-02T07:43:38.309135+00:00
*** SERVICE NAME:(pdb1) 2021-05-02T07:43:38.309144+00:00
*** MODULE NAME:(java@localhost.localdomain (TNS V1-V3)) 2021-05-02T07:43:38.309153+00:00
*** ACTION NAME:() 2021-05-02T07:43:38.309162+00:00
*** CLIENT DRIVER:(jdbcoci : 2021-05-02T07:43:38.309170+00:00
*** CONTAINER ID:(3) 2021-05-02T07:43:38.309210+00:00

psdnop: BERX 120 3
psdnopCheckAcl: priv 2147483693 cnt 1
psdnopCheckAcl: aclid 2147493757 status 3
psdnop: denied
psdnop: BERX 120 2
psdnopCheckAcl: priv 2147483690 cnt 1
psdnopCheckAcl: aclid 2147493757 status 1
psdnop: granted
According to the last line, everything is fine - access is granted. But there is more! 
the first line of interest is psdnop: BERX 120 3 The username is obvious (well, not so if I think about PLSQL with PL/SQL AUTHID CURRENT_USER|DEFINER ).
120 is the USER_ID of user BERX. 
I have no idea what the 3rd parameter of psdnop is - any suggestion is very welcome! 
The last entry is the host & port I try to connect to. 
For me it's the summary what will be checked against ACLs. 
The last line of this first block is psdnop: denied. Even in the 2nd block it's granted, I still 

The second line in this block is psdnopCheckAcl: priv 2147483693 cnt 1. It checks if an ACL is granted. But which one? 
It's first parameter tells us it's a priviliege the number is 2147483693. Unfortunately it's not written in the tracefile, so we need to identify this privilege.

  SQL> select name, id  from sys.xs$obj where id = 2147483693 ;
   NAME            ID
_______ _____________
HTTP       2147483693
This means Oracle first checks if the HTTP privilege is present - as I did not grant it, it's not there. 
The last 2 parameters of psdnopCheckAcl are unclear: cnt 1 - I have no explanation. 

In case you don't want to query sys.xs$obj for a given privilege, have a peek in $ORACLE_HOME/rdbms/admin/catts.sql. The 3 important parts there (for privilege HTTP) are 

RESERVED_ID              NUMBER := 2147483648;
PRIV_HTTP                   NUMBER := RESERVED_ID + 45;
-- Create http privilege
insert into xs$obj(name, owner, tenant, id, type, status, flags)

Next line is psdnopCheckAcl: aclid 2147493757 status 3. Again a check, just for an aclid now.  Also this one can be identified, in our case: 
SQL> select * from nacl$_HOST where acl#=2147493757;

             HOST    LOWER_PORT    UPPER_PORT          ACL#
_________________ _____________ _____________ _____________               80           443    2147493757
Now it's clear why the first block is denied: for this host that privilege is just not granted.

To crosscheck on the second (granted) block: 
SQL> select name, id  from sys.xs$obj where id = 2147483690 ;

      NAME            ID
__________ _____________
CONNECT       2147483690

This is by far not a comprehensive introduction into ACL traces, but at least it might help to identify, WHAT is missing when hitting ORA-24247

Sonntag, 7. März 2021

reading PDBs spfile parameters

Last week I was asked by a customer if changes in a PDBs spfile parameter can be monitored easily. 

My first thought was to check if AWR on CDB$ROOT level does this for me already - unfortunately it doesn't. 

So the next idea was to sample PDB_SPFILE$ on my own. 
Unfortunately this table is not documented by Oracle and my colleague Miguel Anjo found records there are not deleted when they are reset on PDB level

To identify such behavior, I like to have a look in all the nice files in $ORACLE_HOME/rdbms/admin.
In e1201000.sql I luckily found this code. Even e1201000.sql is used in case of downgrade, it's quite helpful.

Rem *************************************************************************
Rem BEGIN BUG 23300354 - remove HWM and flags column values in pdb_spfile$
Rem *************************************************************************
update pdb_spfile$ set
db_uniq_name=(select sys_context('userenv', 'DB_UNIQUE_NAME') from dual)
where db_uniq_name='*';
Rem Set HWM value to 0
update pdb_spfile$ set spare1=0;
Rem Delete deleted rows and reset flags to 0
delete from pdb_spfile$ where bitand(nvl(spare2,0),1) = 1;
update pdb_spfile$ set spare2=0;
Another approach is to check what#s Oracle doing at UNPLUGGING a PDB: 

select value$ from pdb_spfile$ where name=:1 and pdb_uid=:2                   and bitand(nvl(spare2,0),1)=0

I tried the probably most obvious method: 

CDB$ROOT> select * from containers(v$spparameter);

But this only show results for CDB$OOT (CON_ID => 1). 

Even a test with x$kspspfile shows only values for CON_ID => 1. 

This is quite sad, especially as x$kspspfile IS used when I query v$spparameter from my PDB1 (which has CON_ID=3 in this test), but there seems to be some additional magic in x$kspspfile which I can not mitigate on SQL layer.