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 = 
  (description= 
    (retry_count=20)(retry_delay=3)
    (address=
      (protocol=tcps)
      (port=1522)
      (host=adb.eu-frankfurt-1.oraclecloud.com)
    )
    (connect_data=
      (service_name=pxyz_db1234_medium.adb.oraclecloud.com)
    )
    (security=
      (ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, 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 pxyz_db1234_medium.adb.oraclecloud.com?ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US"&wallet_location=C:\tns_admin
Hosttcps://adb.eu-frankfurt-1.oraclecloud.com
Port1522

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://adb.eu-frankfurt-1.oraclecloud.com:1522/pxyz_db1234_medium.adb.oraclecloud.com?ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, 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:


begin 
    dbms_session.session_trace_enable(true, false, 'first_execution');
	
    dbms_log.ksdwrt(1, '*** EXPERIENCE ID:('||sys_guid()||')'); 
    dbms_session.set_identifier(sys_context('userenv','session_user')); 
    dbms_application_info.set_module('OE', 'BOOK');
end;
/

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

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
llvm-private 
python-netaddr 
bcc  python-bcc  bcc-tools 

the packages 
libdtrace-ctf 
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!)