Archive

Tag Archives: oracle

This blogpost takes a look at the technical differences between Oracle database 11.2.0.4 PSU 200714 (july 2020) and PSU 201020 (october 2020). This gives technical specialists an idea of the differences, and gives them the ability to assess if the PSU impacts anything.

Functions

code symbol names unique in version 11.2.0.4.200714 versus 11.2.0.4.201020

NAME                                                         RESOLVE                                            ANNOTATION                                                                                 ARCHIVE_OBJECT
------------------------------------------------------------ -------------------------------------------------- ------------------------------------------------------------------------------------------ ------------------------------------------------------------
kglrfSetNiv                                                  (kgl)rfSetNiv                                      kernel generic library cache management ??                                                 libgeneric11.a:kgl2.o

code symbol names unique in version 11.2.0.4.201020 versus 11.2.0.4.200714

NAME                                                         RESOLVE                                            ANNOTATION                                                                                 ARCHIVE_OBJECT
------------------------------------------------------------ -------------------------------------------------- ------------------------------------------------------------------------------------------ ------------------------------------------------------------
__init_0.0                                                   __init_0.0                                         ??
jox_get_cbrls_lock                                           (jox)_get_cbrls_lock                               java jit compiler ??
kglNivHTComp                                                 (kgl)NivHTComp                                     kernel generic library cache management ??                                                 libgeneric11.a:kgl2.o
kglNivHTHash                                                 (kgl)NivHTHash                                     kernel generic library cache management ??                                                 libgeneric11.a:kgl2.o
kglrfAddNivHT                                                (kgl)rfAddNivHT                                    kernel generic library cache management ??                                                 libgeneric11.a:kgl2.o
kglrfDestroyNivHT                                            (kgl)rfDestroyNivHT                                kernel generic library cache management ??                                                 libgeneric11.a:kgl2.o
kglrfSetNivHT                                                (kgl)rfSetNivHT                                    kernel generic library cache management ??                                                 libgeneric11.a:kgl2.o
next_marker                                                  (n)ext_marker                                      network ??                                                                                 libjavavm11.a:jdmarker.o
slrac_end_lock                                               (slrac)_end_lock                                   operating system (dependent) library verify (memory) read access ??                        libgeneric11.a:slrac.o

This is a poor, yet one of the only ways, to understand what is going on inside the oracle executable.
There aren’t that many things removed or added. Of course this is version 11.2.0.4, for which this PSU might be the last one because of 11.2.0.4’s expiring extended support state.

Quite striking here too is the absence of all the changed libjava related functions, for which I don’t know if these function additions have anything to do with security. However, please mind I am not implying that this is not secure, I simply can’t tell.

For the added and removed functions for this version: the only function that was added in version 12.1.0.2 is slrac_end_lock, all the other changes to functions seem to be completely unique to version 11.2.0.4.
If you did read all the functions closely, you might have seen that actually there was one libjava related function changed, and that my name resolving showed its limitations, because it marked it as ‘network’. Well, it seems that certain things, like the java functions do not follow the oracle database layered naming by letters approach.
It also seems that for adding and removing functions, the functions are mainly library cache (kgl) related.

In the data dictionary there weren’t any changes spotted. Of course this overview focusses on availability of parameters and metadata (tables and their columns), for a look into the data dictionary you should look at the work of my colleague Rodrigo Jorge (dissecting the 201020 patches)

This blogpost takes a look at the technical differences between Oracle database 12.1.0.2 PSU 200714 (july 2020) and PSU 201020 (october 2020). This gives technical specialists an idea of the differences, and gives them the ability to assess if the PSU impacts anything.

Functions

code symbol names unique in version 12.1.0.2.200714 versus 12.1.0.2.201020

NAME                                                         RESOLVE                                            ANNOTATION                                                                                 ARCHIVE_OBJECT
------------------------------------------------------------ -------------------------------------------------- ------------------------------------------------------------------------------------------ ------------------------------------------------------------
defCtx_GetRngCtx                                             defCtx_GetRngCtx                                   ??                                                                                         libnnzst12.a:ext_ztrsaadapter.o,libnnz12.a:ztrsaadapter.o
kgiflpop                                                     (kgi)flpop                                         kernel generic instantiation manager ??                                                    libgeneric12.a:kgi.o
kkfdGetQueReason                                             (kkfd)GetQueReason                                 kernel compile fast dataflow (PQ DFO) ??                                                   libserver12.a:kkfd.o
kkodpCheckPrefetch                                           (kkodp)CheckPrefetch                               kernel compile optimizer AQP ??                                                            libserver12.a:kkodp.o
kkodpHJLegal                                                 (kkodp)HJLegal                                     kernel compile optimizer AQP ??                                                            libserver12.a:kkodp.o
kkogimpCheckIMCValidity                                      (kko)gimpCheckIMCValidity                          kernel compile optimizer ??                                                                libserver12.a:kkoop.o
knasnblp2                                                    (knas)nblp2                                        kernel replication apply server ??                                                         libserver12.a:knasn.o
krvxuc                                                       (krvx)uc                                           kernel redo recovery extract ??                                                            libserver12.a:krvx.o
ksfd_estimate_fobpools                                       (ksfd)_estimate_fobpools                           kernel service functions disk IO ??                                                        libserver12.a:ksfd.o
ktcnq_get_typesize                                           (ktc)nq_get_typesize                               kernel transaction control component ??                                                    libserver12.a:ktcnq.o
ktuTempAddExtent_swcbk                                       (ktu)TempAddExtent_swcbk                           kernel transaction undo ??                                                                 libserver12.a:ktu.o
ztchmd5m                                                     (zt)chmd5m                                         security encryption ??                                                                     libnnzst12.a:ext_ztchmd5.o,libnnz12.a:ztchmd5.o
ztcriv                                                       (zt)criv                                           security encryption ??                                                                     libnnzst12.a:ext_ztcr.o,libnnz12.a:ztcr.o
ztcrsg                                                       (zt)crsg                                           security encryption ??                                                                     libnnzst12.a:ext_ztcr.o,libnnz12.a:ztcr.o

code symbol names unique in version 12.1.0.2.201020 versus 12.1.0.2.200714

NAME                                                         RESOLVE                                            ANNOTATION                                                                                 ARCHIVE_OBJECT
------------------------------------------------------------ -------------------------------------------------- ------------------------------------------------------------------------------------------ ------------------------------------------------------------
kcbo_exam_buf                                                (kcbo)_exam_buf                                    kernel cache buffers object queue ??                                                       libserver12.a:kcbo.o
kgnfs_isstaterenewal                                         (kgnfs)_isstaterenewal                             kernel generic network file system ??                                                      libgeneric12.a:kgnfs.o
kjbrnd2dummy                                                 (kjbr)nd2dummy                                     kernel lock management global cache service resource table ??                              libserver12.a:kjbr.o
kkfd_par_all                                                 (kkfd)_par_all                                     kernel compile fast dataflow (PQ DFO) ??                                                   libserver12.a:kkfd.o
kkqjpdClearFroCtx                                            (kkqjpd)ClearFroCtx                                kernel compile query  join analysis predicate push down ??                                 libserver12.a:kkqjpd.o
kksResetPMONSessionState                                     (kks)ResetPMONSessionState                         kernel compile shared objects (cursor) ??                                                  libserver12.a:kks1.o
knasnblp2l                                                   (knas)nblp2l                                       kernel replication apply server ??                                                         libserver12.a:knasn.o
knasnblp2lr                                                  (knas)nblp2lr                                      kernel replication apply server ??                                                         libserver12.a:knasn.o
kokaugi_iobs                                                 (koka)ugi_iobs                                     kernel objects kernel side access ??                                                       libserver12.a:koka.o
krvxpcls                                                     (krvx)pcls                                         kernel redo recovery extract ??                                                            libserver12.a:krvx.o
ktuAddTrsp                                                   (ktu)AddTrsp                                       kernel transaction undo ??                                                                 libserver12.a:ktu.o
ktuFindTrsp                                                  (ktu)FindTrsp                                      kernel transaction undo ??                                                                 libserver12.a:ktu.o
ktuGetTseg                                                   (ktu)GetTseg                                       kernel transaction undo ??                                                                 libserver12.a:ktu.o
ktuTempsoClnUp_pdcbk                                         (ktu)TempsoClnUp_pdcbk                             kernel transaction undo ??                                                                 libserver12.a:ktu.o
ktuTempsoClnUp_single                                        (ktu)TempsoClnUp_single                            kernel transaction undo ??                                                                 libserver12.a:ktu.o
nzosGetCipherDetails                                         (nz)osGetCipherDetails                             network security ??                                                                        libnnz12.a:ext_nzos.o,libnnzst12.a:ext_nzos.o
nzosGetCipherDetails2                                        (nz)osGetCipherDetails2                            network security ??                                                                        libnnz12.a:ext_nzos.o,libnnzst12.a:ext_nzos.o
nzosGetCipherList                                            (nz)osGetCipherList                                network security ??                                                                        libnnz12.a:ext_nzos.o,libnnzst12.a:ext_nzos.o
nzosGetDefaultCipherlist                                     (nz)osGetDefaultCipherlist                         network security ??                                                                        libnnz12.a:ext_nzos.o,libnnzst12.a:ext_nzos.o
qksceCEContainerMismatch                                     (qksce)CEContainerMismatch                         query kernel sql cursor evaluation ??                                                      libserver12.a:qksce.o
slrac_end_lock                                               (slrac)_end_lock                                   operating system (dependent) library verify (memory) read access ??                        libgeneric12.a:slrac.o

This is a poor, yet one of the only ways, to understand what is going on inside the oracle executable.
There aren’t that many things removed or added. Of course this is version 12.1, which is not what I believe to be the 12.2 releases of 12.2.0.1, 18 and 19, so a truly different version/release.

Also quite striking is the absence of all the changed libjava related functions, for which I don’t know if these function additions have anything to do with security. However, please mind I am not implying that this is not secure, I simply can’t tell.

Also, the functions that are removed and added do not seem to have any relationship with the added and removed functions in 12.2 and up. That might indicate that the PSU purely focussed on security updates to existing functions and issues that were logged against this version.

In the data dictionary there weren’t any changes spotted. Of course this overview focusses on availability of parameters and metadata (tables and their columns), for a look into the data dictionary you should look at the work of my colleague Rodrigo Jorge (dissecting the 201020 patches)

This blogpost is about how the oracle database executable created or changed during installation and patching. I take linux for the examples, because that is the version that I am almost uniquely working with. I think the linux operating is where the vast majority of linux installations are installed on, and therefore an explanation with linux is helpful to most of the people.

The first thing to understand is the oracle executable is a dynamically linked executable. This is easy to see when you execute the ‘ldd’ utility against the oracle executable:

$ ldd oracle
	linux-vdso.so.1 (0x00007ffd3f5b0000)
	libodm19.so => /u01/app/oracle/product/19/dbhome_1/lib/libodm19.so (0x00007fa693084000)
	libofs.so => /u01/app/oracle/product/19/dbhome_1/lib/libofs.so (0x00007fa692e82000)
	libcell19.so => /u01/app/oracle/product/19/dbhome_1/lib/libcell19.so (0x00007fa692b69000)
	libskgxp19.so => /u01/app/oracle/product/19/dbhome_1/lib/libskgxp19.so (0x00007fa69284d000)
	libskjcx19.so => /u01/app/oracle/product/19/dbhome_1/lib/libskjcx19.so (0x00007fa692604000)
	librt.so.1 => /lib64/librt.so.1 (0x00007fa6923fb000)
	libclsra19.so => /u01/app/oracle/product/19/dbhome_1/lib/libclsra19.so (0x00007fa6921c0000)
	libdbcfg19.so => /u01/app/oracle/product/19/dbhome_1/lib/libdbcfg19.so (0x00007fa691f93000)
	libhasgen19.so => /u01/app/oracle/product/19/dbhome_1/lib/libhasgen19.so (0x00007fa691270000)
	libskgxn2.so => /u01/app/oracle/product/19/dbhome_1/lib/libskgxn2.so (0x00007fa69106d000)
	libocr19.so => /u01/app/oracle/product/19/dbhome_1/lib/libocr19.so (0x00007fa690d49000)
	libocrb19.so => /u01/app/oracle/product/19/dbhome_1/lib/libocrb19.so (0x00007fa690a49000)
	libocrutl19.so => /u01/app/oracle/product/19/dbhome_1/lib/libocrutl19.so (0x00007fa690828000)
	libaio.so.1 => /lib64/libaio.so.1 (0x00007fa690625000)
	libons.so => /u01/app/oracle/product/19/dbhome_1/lib/libons.so (0x00007fa6903d1000)
	libmql1.so => /u01/app/oracle/product/19/dbhome_1/lib/libmql1.so (0x00007fa69016f000)
	libipc1.so => /u01/app/oracle/product/19/dbhome_1/lib/libipc1.so (0x00007fa68fcf5000)
	libdl.so.2 => /lib64/libdl.so.2 (0x00007fa68faf1000)
	libm.so.6 => /lib64/libm.so.6 (0x00007fa68f76f000)
	libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fa68f54f000)
	libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fa68f336000)
	libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fa68f11f000)
	libc.so.6 => /lib64/libc.so.6 (0x00007fa68ed5d000)
	/lib64/ld-linux-x86-64.so.2 (0x00007fa693287000)

The way this works, is that a library is defined in the ELF (the executable format of linux executables) header of the oracle executable. This can be seen using the ‘readelf’ utility:

$ readelf -d oracle

Dynamic section at offset 0x16f03640 contains 45 entries:
  Tag        Type                         Name/Value
 0x0000000000000001 (NEEDED)             Shared library: [libodm19.so]
 0x0000000000000001 (NEEDED)             Shared library: [libofs.so]
 0x0000000000000001 (NEEDED)             Shared library: [libcell19.so]
 0x0000000000000001 (NEEDED)             Shared library: [libskgxp19.so]
 0x0000000000000001 (NEEDED)             Shared library: [libskjcx19.so]
 0x0000000000000001 (NEEDED)             Shared library: [librt.so.1]
 0x0000000000000001 (NEEDED)             Shared library: [libclsra19.so]
 0x0000000000000001 (NEEDED)             Shared library: [libdbcfg19.so]
 0x0000000000000001 (NEEDED)             Shared library: [libhasgen19.so]
 0x0000000000000001 (NEEDED)             Shared library: [libskgxn2.so]
 0x0000000000000001 (NEEDED)             Shared library: [libocr19.so]
 0x0000000000000001 (NEEDED)             Shared library: [libocrb19.so]
 0x0000000000000001 (NEEDED)             Shared library: [libocrutl19.so]
 0x0000000000000001 (NEEDED)             Shared library: [libaio.so.1]
 0x0000000000000001 (NEEDED)             Shared library: [libons.so]
 0x0000000000000001 (NEEDED)             Shared library: [libmql1.so]
 0x0000000000000001 (NEEDED)             Shared library: [libipc1.so]
 0x0000000000000001 (NEEDED)             Shared library: [libdl.so.2]
 0x0000000000000001 (NEEDED)             Shared library: [libm.so.6]
 0x0000000000000001 (NEEDED)             Shared library: [libpthread.so.0]
 0x0000000000000001 (NEEDED)             Shared library: [libnsl.so.1]
 0x0000000000000001 (NEEDED)             Shared library: [libresolv.so.2]
 0x0000000000000001 (NEEDED)             Shared library: [libc.so.6]
 0x000000000000000f (RPATH)              Library rpath: [/u01/app/oracle/product/19/dbhome_1/lib]
 0x000000000000000c (INIT)               0xdb0d80
 0x000000000000000d (FINI)               0x12b54b90
 0x0000000000000019 (INIT_ARRAY)         0x17398c20
 0x000000000000001b (INIT_ARRAYSZ)       8 (bytes)
 0x000000006ffffef5 (GNU_HASH)           0x4002d0
 0x0000000000000005 (STRTAB)             0x9ddb10
 0x0000000000000006 (SYMTAB)             0x528128
 0x000000000000000a (STRSZ)              3567352 (bytes)
 0x000000000000000b (SYMENT)             24 (bytes)
 0x0000000000000015 (DEBUG)              0x0
 0x0000000000000003 (PLTGOT)             0x17504000
 0x0000000000000002 (PLTRELSZ)           26136 (bytes)
 0x0000000000000014 (PLTREL)             RELA
 0x0000000000000017 (JMPREL)             0xdaa768
 0x0000000000000007 (RELA)               0xda9328
 0x0000000000000008 (RELASZ)             5184 (bytes)
 0x0000000000000009 (RELAENT)            24 (bytes)
 0x000000006ffffffe (VERNEED)            0xda9188
 0x000000006fffffff (VERNEEDNUM)         7
 0x000000006ffffff0 (VERSYM)             0xd44a08
 0x0000000000000000 (NULL)               0x0

This shows the names of the needed shared libraries ‘(NEEDED)’. Some of the needed shared libraries are oracle shared libraries, such as libodm19.so, libofs.so, libcell19.so, libskgxp19.so and so on. Other libraries are operating system libraries, such as libc.so.6, libresolv.so.2, libnsl.so.1, libpthread.so.0, libm.so.6 and so on. The oracle libraries are found because an RPATH (runpath) is included in the header, in my case /u01/app/oracle/product/19/dbhome_1/lib. The operating system libraries are not included with the oracle installation, they are dynamically obtained from the operating system, for which the selection lies with the operating system.

So, we got the oracle executable, and we found out it’s a dynamically linked executable, which means that it’s using shared libraries for some of its functionality.

Now let’s take one step further. Whenever the oracle database software is installed or patched, it must be linked in order to build the executable with the current state of the software.
You might wonder the what I mean with the phrase ‘is installed’: you probably don’t execute a relink all. And that is sensible, because the installer does that for you, you can validate it in $ORACLE_HOME/install/make.log.
I’ll get to the manual linking in a bit.

The oracle database executable and compilation

The first thing to discuss now is compilation. Compilation is the process of turning text based code into a compiled form, for which a lot of compilers do not create an executable form, but an intermediary form, which is called an object. Turning an object or objects into an executable form is called linking. Compiling on linux is done using a compiler, and the default C compiler with Oracle and RedHat linux is gcc. Since Oracle 12.2, the compiler is not a requirement for installation anymore. It is documented, but I believe many may have missed this.

But isn’t there the $ORACLE_HOME/rdbms/lib/config.c file, which is still there, and still used, and isn’t there the make target config.o (make -f ins_rdbms.mk config.o)? Yes, both of them are still there. And still gcc is not a requirement anymore. If you have a pressing need for changing the config.c file (which lists the dba,oper,asm,backup,dataguard,keymanagement and RAC group names), you can still change it, and when you remove the config.o file which USED to be generated with gcc, will now be generated by the ‘as’ executable (portable GNU assembler). This is visible in the oracle database executable make target (ioracle):

$ mv config.o config.O
$ make --dry-run -f ins_rdbms.mk ioracle
chmod 755 /u01/app/oracle/product/19/dbhome_1/bin
cd /u01/app/oracle/product/19/dbhome_1/rdbms/lib/; \
/usr/bin/as -o config.o `[ -f config.c ] && echo config.c || echo config.s`; \
/usr/bin/ar r /u01/app/oracle/product/19/dbhome_1/lib/libserver19.a /u01/app/oracle/product/19/dbhome_1/rdbms/lib/config.o
echo
echo " - Linking Oracle "
rm -f /u01/app/oracle/product/19/dbhome_1/rdbms/lib/oracle
/u01/app/oracle/product/19/dbhome_1/bin/orald  -o /u01/app/oracle/product/19/dbhome_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/app/oracle/product/19/dbhome_1/rdbms/lib/ -L/u01/app/oracle/product/19/dbhome_1/lib/ -L/u01/app/oracle/product/19/dbhome_1/lib/stubs/   -Wl,-E /u01/app/oracle/product/19/dbhome_1/rdbms/lib/opimai.o /u01/app/oracle/product/19/dbhome_1/rdbms/lib/ssoraed.o /u01/app/oracle/product/19/dbhome_1/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv19 -Wl,--no-whole-archive /u01/app/oracle/product/19/dbhome_1/lib/nautab.o /u01/app/oracle/product/19/dbhome_1/lib/naeet.o /u01/app/oracle/product/19/dbhome_1/lib/naect.o /u01/app/oracle/product/19/dbhome_1/lib/naedhs.o /u01/app/oracle/product/19/dbhome_1/rdbms/lib/config.o  -ldmext -lserver19 -lodm19 -lofs -lcell19 -lnnet19 -lskgxp19 -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lclient19  -lvsnst19 -lcommon19 -lgeneric19 -lknlopt -loraolap19 -lskjcx19 -lslax19 -lpls19  -lrt -lplp19 -ldmext -lserver19 -lclient19  -lvsnst19 -lcommon19 -lgeneric19 `if [ -f /u01/app/oracle/product/19/dbhome_1/lib/libavserver19.a ] ; then echo "-lavserver19" ; else echo "-lavstub19"; fi` `if [ -f /u01/app/oracle/product/19/dbhome_1/lib/libavclient19.a ] ; then echo "-lavclient19" ; fi` -lknlopt -lslax19 -lpls19  -lrt -lplp19 -ljavavm19 -lserver19  -lwwg  `cat /u01/app/oracle/product/19/dbhome_1/lib/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/app/oracle/product/19/dbhome_1/lib/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19 -lmm -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lztkg19 `cat /u01/app/oracle/product/19/dbhome_1/lib/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/app/oracle/product/19/dbhome_1/lib/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19   -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 `if /usr/bin/ar tv /u01/app/oracle/product/19/dbhome_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo19 -lserver19"; fi` -L/u01/app/oracle/product/19/dbhome_1/ctx/lib/ -lctxc19 -lctx19 -lzx19 -lgx19 -lctx19 -lzx19 -lgx19 -lclscest19 -loevm -lclsra19 -ldbcfg19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19 -locr19 -locrb19 -locrutl19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19  -lgeneric19 -lorazip -loraz -llzopro5 -lorabz2 -lorazstd -loralz4 -lipp_z -lipp_bz2 -lippdc -lipps -lippcore  -lippcp -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lsnls19 -lunls19  -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lasmclnt19 -lcommon19 -lcore19  -ledtn19 -laio -lons  -lmql1 -lipc1 -lfthread19    `cat /u01/app/oracle/product/19/dbhome_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/19/dbhome_1/lib -lm    `cat /u01/app/oracle/product/19/dbhome_1/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/19/dbhome_1/lib `test -x /usr/bin/hugeedit -a -r /usr/lib64/libhugetlbfs.so && test -r /u01/app/oracle/product/19/dbhome_1/rdbms/lib/shugetlbfs.o && echo -Wl,-zcommon-page-size=2097152 -Wl,-zmax-page-size=2097152 -lhugetlbfs`
rm -f /u01/app/oracle/product/19/dbhome_1/bin/oracle
mv /u01/app/oracle/product/19/dbhome_1/rdbms/lib/oracle /u01/app/oracle/product/19/dbhome_1/bin/oracle
chmod 6751 /u01/app/oracle/product/19/dbhome_1/bin/oracle
(if [ ! -f /u01/app/oracle/product/19/dbhome_1/bin/crsd.bin ]; then \
    getcrshome="/u01/app/oracle/product/19/dbhome_1/srvm/admin/getcrshome" ; \
    if [ -f "$getcrshome" ]; then \
        crshome="`$getcrshome`"; \
        if [ -n "$crshome" ]; then \
            if [ $crshome != /u01/app/oracle/product/19/dbhome_1 ]; then \
                oracle="/u01/app/oracle/product/19/dbhome_1/bin/oracle"; \
                $crshome/bin/setasmgidwrap oracle_binary_path=$oracle; \
            fi \
        fi \
    fi \
fi\
);
$ mv config.O config.o

First of all, I am in the $ORACLE_HOME/rdbms/lib directory already. I moved the config.o file to a different name, config.O (uppercase O). This will trigger the config.o file to be generated during linking via the the makefile, because the make macro for generating the oracle executable checks for the existence of config.o in $ORACLE_HOME/rdbms/lib, and the generation of config.o is triggered by it not existing.
I used make with the ‘–dry-run’ option, which means it will list what it WOULD do, it doesn’t actually do it.
Now that the make macro doesn’t find the $ORACLE_HOME/rdbms/lib/config.o file, it generates it, using ‘as’, the GNU assembler.
After the run, I move the config.O file back to config.o.
Please mind the make target config.o (make -f ins_rdbms.mk config.o) still exists, and this follows the traditional way, so using gcc, to create the object file config.o.

For anything else than config.c, Oracle provides objects (the compiled, intermediary form of C) in object files. This has several advantages. First of all, the server to install oracle on doesn’t require a compiler. That also means that there is no discussion about compiler versions, Oracle knows for a fact which version of a compiler is used. Second, Oracle can use a different compiler than the GNU compiler, as long as it does provide objects in Linux X86_64 (ELF) format. In fact, that is what oracle does: for Oracle 19.9, Oracle used a compiler from intel: Intel(R) C Intel(R) 64 Compiler for applications running on Intel(R) 64, Version 17.0.2.174 Build 20170213
You can obtain that information from the oracle executable using:

$ readelf -p .comment oracle | egrep 'Intel.*Build\ [0-9]*'

I hope that at this point I made it clear that no compiler is needed anymore for oracle installation and making changes to the oracle installation, like patching.

The oracle database executable and objects and object files

It’s probably a good idea to show how the oracle executable is build. The way this happens is using the make target ‘ioracle’, which is visible above (make -f ins_rdbms.mk ioracle).
The macro calls ‘orald’, which actually is a script in $ORACLE_HOME/bin, which calls the operating-system ‘ld’ executable, which is the GNU linker.
The arguments to ‘orald’ are arguments that mostly are put through to ‘ld’. ‘-o’ is the output flag, and that shows the executable to be build by the linker, and besides options being set, what you mainly see is -L (library path) and -l (library) switches adding libraries (=object files and archive files) to build the oracle executable.
There’s a couple of places that are used to get objects to build the oracle executable:
– $ORACLE_HOME/rdbms/lib — oracle database rdbms specific libraries
– $ORACLE_HOME/lib — oracle database general libraries (objects are used by multiple “products” in the $ORACLE_HOME)
– $ORACLE_HOME/lib/stubs — this is a directory with ‘stub objects’, which are versions of operating system libraries that contain no code, but allow the oracle executable to be build, even if a operating system library is missing (see: https://docs.oracle.com/cd/E23824_01/html/819-0690/chapter2-22.html)
– $ORACLE_HOME/ctx/lib — oracle text (not sure why oracle text requires an explicit lookup to $ORACLE_HOME/ctx/lib, while other options are all in $ORACLE_HOME/lib
– /lib64 — operating system libraries

At this point it’s important to realise that the object files for linking oracle are visible in two forms: as plain object files (.o) and as archive files (.a). Archive files are exactly what the name suggests: these are archives of object files. You can look and manipulate an archive file using the ‘ar’ (archiver utility), for which the working strongly resembles how tar and jar work: t=list, x=extract, c=create.
If you take a look at one of the main archives, libserver19.a, you see that it contains 2852 object files:

$ ar -t $ORACLE_HOME/lib/libserver19.a | wc -l
2852

If you do wonder what’s inside, ‘ar -tv’ would be a good way to have an idea:

$ ar -tv $ORACLE_HOME/lib/libserver19.a
rw-r--r-- 54321/54321  11136 Oct 19 21:12 2020 kdr.o
rw-rw-r-- 94110/42424   8376 Apr 17 04:58 2019 upd.o
rw-rw-r-- 94110/42424  41968 Apr 17 04:58 2019 kd.o
...
rw-r--r-- 54321/54321  13248 Oct 19 21:11 2020 qjsntrans.o
rw-r--r-- 54321/54321  20296 Oct 19 21:11 2020 kubsd.o
rw-r--r-- 54321/54321  16720 Oct 19 21:12 2020 kqro.o

The conclusion here is that archive files are logical and sensible, otherwise the library directories would have been swamped with huge numbers of object files.

When object files are linked to an executable, it requires object files, and these are in ‘.o’ files, or grouped in ‘.a’ files. A third type of file is needed for linking linking an executable that is going to be a dynamically linked executable: the libraries (the ‘.so’ files) the executable is dynamically going to use. The linker will validate the libraries, which means it inspects the libraries to find the symbols that the objects that form the executable is calling. A library (‘.so’ file) is an already compiled form, in facts it’s pretty much similar to an executable, only the way it’s invoked is when it’s called by a dynamically linked executable that uses it, instead of directly.

The object files itself

This above text pretty much describe how executables, libraries, object files and archives sit together, and how the linking creates the oracle executable via the makefile. This description describes how this is configured by oracle for creating the oracle executable. However, this is really flexible, and can be done differently, so this is not how it always is or should be, this is how oracle chosen it to do.

We can look one level deeper into how this works. An object file in fact is already an archive, containing one or more compiled versions of functions:

$ nm -A opimai.o
opimai.o:                 U dbkc_free_bs_context
opimai.o:                 U dbkc_init
opimai.o:                 U dbktFlush
opimai.o:                 U __intel_new_feature_proc_init
opimai.o:                 U kgeasnmierr
opimai.o:                 U kge_pop_guard_fr
opimai.o:                 U kge_push_guard_fr
opimai.o:                 U kge_report_17099
opimai.o:                 U kgeresl
opimai.o:                 U kge_reuse_guard_fr
opimai.o:                 U ksdwrf
opimai.o:                 U kseini
opimai.o:                 U ksmdsg
opimai.o:                 U ksmgpg_
opimai.o:                 U ksmlsge_phaseone
opimai.o:                 U ksmsgaattached_
opimai.o:                 U kso_save_arg
opimai.o:                 U kso_spawn_ts_save
opimai.o:                 U ksosp_parse
opimai.o:                 U ksuginpr
opimai.o:                 U lfvinit
opimai.o:0000000000000010 T main
opimai.o:                 U opiinit
opimai.o:0000000000000300 t opimai_init
opimai.o:0000000000000140 T opimai_real
opimai.o:                 U opiterm
opimai.o:                 U sdterm
opimai.o:                 U _setjmp
opimai.o:                 U skge_sign_fr
opimai.o:                 U skgmstack
opimai.o:                 U skgp_done_args
opimai.o:                 U skgp_retrieve_args
opimai.o:                 U slgtds
opimai.o:                 U slgts
opimai.o:                 U slkbpi
opimai.o:                 U slkfpi
opimai.o:                 U sou2o
opimai.o:                 U spargs
opimai.o:                 U ssthrdmain

This example takes the object file $ORACLE_HOME/rdbms/lib/opimai.o, and this object file contains 3 actual functions (shown by an address and the symbol type ‘T’ or ‘t’), and a whole bunch of functions without an address and symbol type ‘U’. The functions with symbol type ‘U’ are undefined functions, which means that these functions are not in this archive, but defined somewhere else.
The important thing to consider is that a single symbol can contain multiple functions.

I chosen this object file, because this is in fact the actual object file where the main function, the starting function, for the oracle executable is in. If you obtain a stack trace of an oracle database process, the first function (called ‘first frame’) in at least recent linux versions (some other operating systems or versions might show earlier functions) will show main as the first function. This is also what the linker uses to build the executable, it follows the symbol information together with the command line switches to resolve and obtain all the functions via the symbol information. The linker will generate an error and not build the executable if it can’t find or resolve the symbols and get all the information it needs.

Patching

At this point you should have an understanding what a dynamically linked executable, libraries, object files and archives are, and that the oracle executable is build using a makefile using the linker.

It might be handy and interesting to look at patching. This information, the information about archives and objects, should give you more background about the specifics of patching. Oracle patching has many forms, and actually can do and change a lot of things in a lot of ways. It is retraceable what a patch does by looking at the contents of the patch. But that is now what this post is about.

Especially with one-off patches, in the case of a patch to fix or change one or more functions in the oracle executable, what the patch provides is the fixed and thus changed versions of these functions. However, oracle does not provide sourcecode. In general what oracle provides, is the object or objects containing the changed functions. In order to get the changed function or functions into the oracle executable, what generally happens is that the current/old versions of the object file are removed from the archive they are in, and saved in $ORACLE_HOME/.patch_storage, and the patched versions of the object file are inserted into the archive.

But, we saw an object file generally contains more or much more functions. Well, this is why patches can be incompatible with other patches: if multiple patches change different (or the same) functions in the same objects, the patch applied latest will undo the changes of the previous patch(es). This is also why you must request merge patches if patches are incompatible.

Dealing with individual object files, extracting them from an archive and saving them in order to be able to restore it into the archive is tedious. Also, the archive itself doesn’t mind whatever you remove from it or insert to it, even if it will break linking the oracle executable. Therefore, oracle has created opatch to perform a great deal of validations and checks, and take the work of dependency checks from you and fully automate it. In fact, in general, you can take a (one-off) patch and try to apply it, if it does, it will allow oracle to be relinked, if there is a conflicting patch it will tell you. Also, if you want to revert your applied patch, you can simply rollback and get opatch to load the previous version into the archive. This is way better than letting us humans deal with it directly.

After the patching changed the archives to contain the updated versions of the objects which contain updated functions, these must make it into the oracle executable. This must be done by relinking the executable, which will take the objects including the changed objects from all the object files and archives, and create a new executable. The oracle executable is never directly touched on linux with recent versions, to my knowledge.

I hope this explanation made sense and made a lot of these things which we are dealing with as oracle DBAs more understandable. Any comments or updates are welcome!

This blogpost takes a look at the technical differences between Oracle database 12.2.0.1 PSU 200714 (july 2020) and PSU 201020 (october 2020). This gives technical specialists an idea of the differences, and gives them the ability to assess if the PSU impacts anything.

Functions


code symbol names unique in version 12.2.0.1.200714 versus 12.2.0.1.201020

NAME                                                         RESOLVE                                                      ANNOTATION                                                                                 ARCHIVE_OBJECT
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------
NAME                                               RESOLVE                                            ANNOTATION                                                                       ARCHIVE_OBJECT                                                 COUNT(*)
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------ ----------
kcbo                                               kcbo                                               kernel cache buffers object queue                                                                                                                      1
kkdl                                               kkdl                                               kernel compile dictionary lookup                                                                                                                       1
kmgs                                               kmgs                                               kernel multi threaded/mman manage (sga) space (?)                                                                                                      1
kzpR                                               (kzp)R                                             kernel security privileges ??                                                                                                                          1
qcso                                               qcso                                               query compile semantic analysis (parser) OBJECTS                                                                                                       1
qeae                                               qeae                                               query execute aggregate (order by) elimination (?)                                                                                                     1
qkaI                                               (qka)I                                             query kernel allocation ??                                                                                                                             1
qkex                                               (qke)x                                             query kernel expressions ??                                                                                                                            1
qksh                                               (qks)h                                             query kernel sql ??                                                                                                                                    1
qksp                                               (qks)p                                             query kernel sql ??                                                                                                                                    1
krvx                                               krvx                                               kernel redo recovery extract                                                                                                                           2
kxtt                                               kxtt                                               kernel execution temporary table operations                                                                                                            2
Java                                               Java                                               java classes                                                                                                                                           6

code symbol names unique in version 12.2.0.1.201020 versus 12.2.0.1.200714

NAME                                               RESOLVE                                            ANNOTATION                                                                       ARCHIVE_OBJECT                                                 COUNT(*)
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------ ----------
ECGr                                               (EC)Gr                                             elleptic curve cryptography ??                                                   libjavavm12.a:ecl.o                                                   7
Java                                               Java                                               java classes                                                                     libjavavm12.a:ShapeSpanIterator.o                                     7
ec_G                                               (ec_)G                                             elleptic curve cryptography ??                                                   libjavavm12.a:ecp_jac.o                                               7
mp_c                                               (mp_)c                                             multiple precision cryptography ??                                               libjavavm12.a:mpi.o                                                   7
mp_i                                               (mp_)i                                             multiple precision cryptography ??                                               libjavavm12.a:mpi.o                                                   7
mp_t                                               (mp_)t                                             multiple precision cryptography ??                                               libjavavm12.a:mpi.o                                                   7
mp_b                                               (mp_)b                                             multiple precision cryptography ??                                               libjavavm12.a:mp_gf2m.o                                               8
mp_s                                               (mp_)s                                             multiple precision cryptography ??                                               libjavavm12.a:mpi.o                                                  11
ec_G                                               (ec_)G                                             elleptic curve cryptography ??                                                   libjavavm12.a:ecl_gf.o                                               21
s_mp                                               s_mp                                               cryptography multiple precision                                                  libjavavm12.a:mpi.o                                                  49

This is a poor, yet one of the only ways to understand what is going on inside the oracle executable.
There aren’t that many thing removed, which is understandable, this is an update. 20 functions have been removed in total. The archive:object annotation is not there, I started doing that for the latest PSU.
For the functions that were added, the vast majority comes from libjavavm18, and seem to be cryptographic related functions.

It should be noted that if you compare the added functions and the changed functions with the Oracle 18 ones, a fair share of them are equal, but there are also functions unique to each version. Of course the equal functions probably are related to huge issues that must be fixed in all versions. The functions that are unique to this version are likely to be (minor-)version specific issues.

This blogpost takes a look at the technical differences between Oracle database 18 RU 11 (july 2020) and RU 12 (october 2020). This gives technical specialists an idea of the differences, and gives them the ability to assess if the RU impacts anything.

Functions


code symbol names unique in version 18.11 versus 18.12 -- so removed functions in RU 12.

NAME                                                         RESOLVE                                                      ANNOTATION                                                                                 ARCHIVE_OBJECT
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------
Java_sun_java2d_pipe_ShapeSpanIterator_appendCubic           (Java_sun_java2d_pipe_ShapeSpanIterator_)appendCubic         java classes sun. java2d. pipe. ShapeSpanIterator. ??
Java_sun_java2d_pipe_ShapeSpanIterator_appendLine            (Java_sun_java2d_pipe_ShapeSpanIterator_)appendLine          java classes sun. java2d. pipe. ShapeSpanIterator. ??
Java_sun_java2d_pipe_ShapeSpanIterator_appendQuadratic       (Java_sun_java2d_pipe_ShapeSpanIterator_)appendQuadratic     java classes sun. java2d. pipe. ShapeSpanIterator. ??
Java_sun_java2d_pipe_ShapeSpanIterator_beginSubpath          (Java_sun_java2d_pipe_ShapeSpanIterator_)beginSubpath        java classes sun. java2d. pipe. ShapeSpanIterator. ??
Java_sun_java2d_pipe_ShapeSpanIterator_endPath               (Java_sun_java2d_pipe_ShapeSpanIterator_)endPath             java classes sun. java2d. pipe. ShapeSpanIterator. ??
Java_sun_java2d_pipe_ShapeSpanIterator_getCPathConsumer      (Java_sun_java2d_pipe_ShapeSpanIterator_)getCPathConsumer    java classes sun. java2d. pipe. ShapeSpanIterator. ??
kkdlpGetObjn                                                 (kkdl)pGetObjn                                               kernel compile dictionary lookup ??
kxttSCNGet                                                   (kxtt)SCNGet                                                 kernel execution temporary table operations ??
kxttSCNGetBoth                                               (kxtt)SCNGetBoth                                             kernel execution temporary table operations ??
kzpRoleEnabled                                               (kzp)RoleEnabled                                             kernel security privileges ??
qkspmTravInit                                                (qkspm)TravInit                                              query kernel sql plan management ??
skgpgprocstat                                                (skg)pgprocstat                                              operating system dependent kernel generic ??
skgpgthrstat                                                 (skg)pgthrstat                                               operating system dependent kernel generic ??

code symbol names unique in version 18.11 versus 18.12 (top 11) -- so added in the RU 12.

NAME                                               RESOLVE                                            ANNOTATION                                                                       ARCHIVE_OBJECT                                                 COUNT(*)
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------ ----------
ECGr                                               (EC)Gr                                             elleptic curve cryptography ??                                                   libjavavm18.a:ecl.o                                                   7
Java                                               Java                                               java classes                                                                     libjavavm18.a:ShapeSpanIterator.o                                     7
ec_G                                               (ec_)G                                             elleptic curve cryptography ??                                                   libjavavm18.a:ecp_jac.o                                               7
mp_c                                               (mp_)c                                             multiple precision cryptography ??                                               libjavavm18.a:mpi.o                                                   7
mp_i                                               (mp_)i                                             multiple precision cryptography ??                                               libjavavm18.a:mpi.o                                                   7
mp_t                                               (mp_)t                                             multiple precision cryptography ??                                               libjavavm18.a:mpi.o                                                   7
mp_b                                               (mp_)b                                             multiple precision cryptography ??                                               libjavavm18.a:mp_gf2m.o                                               8
mp_s                                               (mp_)s                                             multiple precision cryptography ??                                               libjavavm18.a:mpi.o                                                  11
kewr                                               kewr                                               kernel event AWR repository                                                      libserver18.a:kewr.o                                                 14
ec_G                                               (ec_)G                                             elleptic curve cryptography ??                                                   libjavavm18.a:ecl_gf.o                                               21
s_mp                                               s_mp                                               cryptography multiple precision                                                  libjavavm18.a:mpi.o                                                  49

This is a poor, yet one of the only ways to understand what is going on inside the oracle executable.
There aren’t that many thing removed, which is understandable. Therefore I listed all the functions that were removed, not a as a grouped overview. The archive:object annotation is not there, I started doing that for the latest RU.
For the functions that were added, the vast majority comes from libjavavm18, and seem to be cryptographic related functions.

The only group that might show something is kewr (AWR), however I cannot see something in general that is changed, the names seem random:

NAME                                                         RESOLVE                                                      ANNOTATION                                                                                 ARCHIVE_OBJECT
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------
kewramcs_app_map_condbid_str                                 (kewr)amcs_app_map_condbid_str                               kernel event AWR repository ??                                                             libserver18.a:kewr.o
kewramvn_append_mdb_vvwname                                  (kewr)amvn_append_mdb_vvwname                                kernel event AWR repository ??                                                             libserver18.a:kewr.o
kewraxc_attribute_xcb                                        (kewr)axc_attribute_xcb                                      kernel event AWR repository ??                                                             libserver18.a:kewr.o
kewrbtsg_build_topseg                                        (kewr)btsg_build_topseg                                      kernel event AWR repository ??                                                             libserver18.a:kewr.o
kewrccsq_collect_csql                                        (kewr)ccsq_collect_csql                                      kernel event AWR repository ??                                                             libserver18.a:kewr.o
kewrgcfes_get_cacheid_from_enum_str                          (kewr)gcfes_get_cacheid_from_enum_str                        kernel event AWR repository ??                                                             libserver18.a:kewr.o
kewrmplvl_map_snap_level                                     (kewr)mplvl_map_snap_level                                   kernel event AWR repository ??                                                             libserver18.a:kewr.o
kewrpfbue_pdb_from_buffer_entry                              (kewr)pfbue_pdb_from_buffer_entry                            kernel event AWR repository ??                                                             libserver18.a:kewr.o
kewrptsq_prep_topsql                                         (kewr)ptsq_prep_topsql                                       kernel event AWR repository ??                                                             libserver18.a:kewr.o
kewrrdsi_rank_dstat_item                                     (kewr)rdsi_rank_dstat_item                                   kernel event AWR repository ??                                                             libserver18.a:kewr.o
kewrrtsq_rank_topsql                                         (kewr)rtsq_rank_topsql                                       kernel event AWR repository ??                                                             libserver18.a:kewr.o
kewrsaobn_set_all_objnames                                   (kewr)saobn_set_all_objnames                                 kernel event AWR repository ??                                                             libserver18.a:kewr.o
kewrsonie_set_object_names_in_entry                          (kewr)sonie_set_object_names_in_entry                        kernel event AWR repository ??                                                             libserver18.a:kewr.o
kewrsqlc_sql_iscolored_cb                                    (kewr)sqlc_sql_iscolored_cb                                  kernel event AWR repository ??                                                             libserver18.a:kewr.o

Outside of code changes, there isn’t anything changed in the data dictionary.

This blogpost takes a look at the technical differences between Oracle database 19 RU 8 (july 2020) and RU 9 (october 2020). This gives technical specialists an idea of the differences, and gives them the ability to assess if the RU impacts anything.

Functions


code symbol names unique in version 19.8 versus 19.9 (top 5) -- so removed in the 19.9 RU

NAME                                               RESOLVE                                            ANNOTATION                                                                       ARCHIVE_OBJECT                                                 COUNT(*)
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------ ----------
qsod                                               (qs)od                                             query system ??                                                                                                                                        3
ZSTD                                               ZSTD                                               Zstandard. fast lossless compression algorithm                                                                                                         4
qjsn                                               qjsn                                               query json                                                                                                                                             5
Java                                               Java                                               java classes                                                                                                                                           6
qcpi                                               qcpi                                               query compile parse interim                                                                                                                            6

code symbol names unique in version 19.9 versus 19.8 (top 10) -- so added in the 19.9 RU

NAME                                               RESOLVE                                            ANNOTATION                                                                       ARCHIVE_OBJECT                                                 COUNT(*)
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------ ----------
qsod                                               (qs)od                                             query system ??                                                                  libclntst19.a:generic19_qsodax.o,libgeneric19.a:qsodax.o              8
mp_s                                               (mp_)s                                             multiple precision cryptography ??                                               libjavavm19.a:mpi.o                                                  11
qcpi                                               qcpi                                               query compile parse interim                                                      libclntst19.a:generic19_qcpi7.o,libgeneric19.a:qcpi7.o               11
ksac                                               (ksa)c                                             kernel service asynchronous messages ??                                          libserver19.a:ksacl.o                                                13
jznO                                               (jzn)O                                             json ??                                                                          libxml19.a:jznoct.o,libclntst19.a:xml19_jznoct.o                     15
qjsn                                               qjsn                                               query json                                                                       libserver19.a:qjsn.o                                                 15
jznt                                               (jzn)t                                             json ??                                                                          libxml19.a:jzntrans.o,libclntst19.a:xml19_jzntrans.o                 19
jzno                                               (jzn)o                                             json ??                                                                          libxml19.a:jznoct.o,libclntst19.a:xml19_jznoct.o                     20
ec_G                                               (ec_)G                                             elleptic curve cryptography ??                                                   libjavavm19.a:ecl_gf.o                                               21
s_mp                                               s_mp                                               cryptography multiple precision                                                  libjavavm19.a:mpi.o                                                  49

This is a poor, yet one of the only ways to understand what is going on inside the oracle executable.

There aren’t that many thing removed, which is understandable.
I show the the top 11 of function groups that are added, because their numbers are significant (more than 10).
In general, what I see is most functions are related to cryptography, which is understandable and logical in today’s world.
Additionally, there’s a lot of movement with JSON related libraries, this is also understandable, and probably a lot of the functions are related to DBMS_SODA.
Obviously, there is more changed as we can see below from the parameters (which are switches for bug fixes), and other changes, but the function changes doe not indicate huge groupings of functions to be added or removed outside of the groups indicated above.

Parameters

parameters unique in version 19.8 versus 19.9

NAME
--------------------------------------------------
_eleventh_spare_parameter
_nineteenth_spare_parameter
_one-hundred-and-seventy-fourth_spare_parameter
_one-hundred-and-sixty-eighth_spare_parameter
_thirteenth_spare_parameter
_twentieth_spare_parameter
_twenty-eighth_spare_parameter
_twenty-fifth_spare_parameter
_twenty-first_spare_parameter
_twenty-fourth_spare_parameter
_twenty-seventh_spare_parameter
_twenty-sixth_spare_parameter
_twenty-third_spare_parameter

parameters unique in version 19.9 versus 19.8

NAME
--------------------------------------------------
_bug28482048_fb_log_max_rereads
_bug29504103_runtime_index_key_length_check
_bug30159581_cputime_limit_parallel_fptr
_bug30159581_runtime_limit_parallel_fptr
_bug30165506_enable_unified_memory
_bug30165506_unified_pga_gran_leak_timeout
_bug30165506_unified_pga_list_count
_bug30165506_unified_pga_max_pool_size
_bug31747989_cputime_limit_parallel_fptr
_bug31747989_runtime_limit_parallel_fptr
_ipacl_feature_control_flags
_ldap_use_all_direct_groups_only
_pdb_transition_clean_bg_delay

Once again, this is quite consistent, ‘spare’ parameters being swapped for lots of ‘_bugnnnnnnn_description’ parameters, and a few other underscore parameters.
There are no new documented parameters.

Waitevents

waitevents unique in version 19.8 versus 19.9

NAME
----------------------------------------------------------------------------------------------------
second spare wait event

waitevents unique in version 19.9 versus 19.8

NAME
----------------------------------------------------------------------------------------------------
unified memory allocation

A new wait event was added with PSU 19.9! A ‘spare’ wait event was changed to ‘unified memory allocation’. I do not have more information about this, and have not heard about ‘unified memory’ before.

Sysstat

sysstat statistics unique to 19.8 versus 19.9

NAME
----------------------------------------------------------------------------------------------------
cell RDMA reads
cell pmem cache read hits
spare statistic 1
spare statistic 2

sysstat statistics unique to 19.9 versus 19.8

NAME
----------------------------------------------------------------------------------------------------
cell RDMA reads
cell RDMA reads unused
cell pmem cache read hits
cell pmem cache read hits unused

A few statistics have been added! Quite logically this is related to RDMA and pmem (persistent memory). It looks like existing statistics have been moved to a new statistics number, and the ‘old’ ones have gotten an addition saying ‘unused’. It’s understandable and known that oracle is putting efforts in the area’s of RDMA, where it seems the usage of RDMA is expanded all the time to increase performance of RAC cross instance communication, and pmem is new and currently officially implemented for Exadata. I do not know if the above statistic is the exadata related pmem cache, or if this is a local machine pmem implementation.

DBA/CDB views

dba tables unique to 19.8 versus 19.9

dba tables unique to 19.9 versus 19.8

NAME
----------------------------------------------------------------------------------------------------
DBA_SUBSCR_DUR_REGISTRATIONS

cdb tables unique to 19.8 versus 19.9

cdb tables unique to 19.9 versus 19.8

NAME
----------------------------------------------------------------------------------------------------
CDB_SUBSCR_DUR_REGISTRATIONS

It seems there is a DBA and CDB scope view added.

I posted a message on twitter saying that DBMS_PIPE is an excellent mechanism to make a session run and halt in PLSQL. One response I gotten was asking for an example of that. That is what this post is about.

DBMS_PIPE is an implementation of a pipe inside the Oracle database. A pipe is a mechanism that is not limited to the Oracle database, in fact I assume the implementation is inspired by an operating system level pipe, which can be created using the ‘mknod /path/pipename p’ on unix and unix-like systems. A quick search shows windows has got them too, but not really, or does it? Another implementation is the pipe to redirect output from one command to the next using ‘|’. The essence of a pipe that input to and output from the pipe are separated from each other, and that information is flowing from the inputting process of the pipe to the outputting process.

For the specific purpose this blogpost is about, DBMS_PIPE, I am not using the information that is sent through the pipe, I am mainly using the fact that a receiving session waits for information to be sent, and therefore halts execution.

When the Oracle database executes in PL/SQL mode, there is no simple way to make it pause and request for user input, like you would do with the ‘pause’ or ‘accept’ commands in sqlplus in SQL mode. PL/SQL runs and provides its output when the session quits PL/SQL mode, in other words: there is no interaction with the terminal that executes the PL/SQL procedure or anonymous block. So if you want to make it stop and continue, you must use something else, for which I use DBMS_PIPE. Of course there are other ways, I would encourage people to come up with other mechanisms as a comment.

The use the technique of using DBMS_PIPE, you need two sessions, one which runs the PL/SQL block that you want to pause and continue (using a database pipe), and one to make it continue by sending a message to that pipe. Another thing that you might notice is the lack of explicit setup of the pipe; I just declare waiting for the pipe, and it’ll get created implicitly.

This is how it’s done:

1. Simple example anonymous PL/SQL block:

set serverout on;
declare
 v_result number;
 v_time1 date;
 v_time2 date;
begin
  select sysdate into v_time1 from dual;
  v_result := dbms_pipe.receive_message ( 'test' );
  select sysdate into v_time2 from dual;
  dbms_output.put_line('you waited: '||(24*60*60)*(v_time2-v_time1)||' seconds');
end;
/

If you execute this, it will provide no output, and in fact be waiting for the pipe that I named ‘test’.

In order to make anonymous PL/SQL block continue, I need another session to send a message to the pipe:
2. Send empty message to the pipe:

var v_result number;
exec v_result := dbms_pipe.send_message ( 'test' );

Once you executed that, the PL/SQL block will continue, and then finish and therefore display the output:


you waited: 3 seconds

PL/SQL procedure successfully completed.

There are a couple of other caveats you need to be aware of:
– a pipe is visible to the instance, in the case of a cluster database it’s NOT visible on the other instances in the cluster.
– there can be multiple sessions waiting on the same pipe, but a single message is consumed/received by a single session, based on time.

All Oracle database professionals know the current versions of the Oracle database (12.2, 18, 19, 20 at the moment of writing), and we also know the pace Oracle corporation keeps is so high that a lot of companies are having a hard time keeping up with the current versions. A prominent one is Oracle corporation itself for their E-Business suite software, where Oracle extended the support for the database for version 12.1.0.2 and 11.2.0.4 for E-Business suite licenses only. But this blog isn’t about bitching about the pace of Oracle support and versions getting desupported.

What I do regularly encounter is that for all kinds of reasons a database version is not updated. Most of the time the versions that are encountered are 12.1.0.2 (the long term supported version of the 12.1 version of the database), 11.2.0.4 (the long term supported version of the 11.2 version of the database), and more and more seldom 11.2.0.3. If things truly have been left without updating you might encounter 11.2.0.2, and god forbid if you still have 11.2.0.1, that version had a lot if issues.

Now what if you encounter even older versions? Probably younger Oracle database consultants might never even have seen older versions than 11.2.0.1. But what if you need to work with a truly old version? Or are just interested in such an old version to see how it looks like, and what the state of the database was at that version?

For that, I created an automatic installation script to install either:
– Release 10.2 versions: 10.2.0.1, 10.2.0.2, 10.2.0.3, 10.2.0.4, 10.2.0.5.
– Release 9.2 versions: 9.2.0.4, 9.2.0.5*, 9.2.0.6*, 9.2.0.7, 9.2.0.8.
(*=the patch install is fully scripted, but linking oracle throws an error)

Yes, this is extremely old, and if you must work with it, there has been neglect and somebody not paying attention at a quite massive scale. There are also licensing implications that do not work in your favour there.

There is a huge caveat too: the installation media for these Oracle database versions is not available for download anywhere as far as I know, and some of the patches are restricted downloads on My Oracle Support too. Since it’s Oracle proprietary software, the only way to obtain it is via Oracle.

Outside of these, if you must or want to use these ancient versions, and you got the required files, you can use:
https://gitlab.com/FritsHoogland/ol48_oracle92 for installing Oracle 9.2 on Oracle Linux 4.8 or
https://gitlab.com/FritsHoogland/ol511_oracle102 for installing Oracle 10.2 on oracle Linux 5.11

Clone the repository, put the required files in the files directory, edit the Vagrantfile to your liking and then build the database server by typing ‘vagrant up’.

In case you’re wondering how the operating system images are build, this is done using ‘packer’, I have a repository where you can see how these are build too: https://gitlab.com/FritsHoogland/packer-oracle-linux-museum

In case you’re wondering: there are even older versions; the first public version of the Oracle database on linux, as far as I know, is Oracle 8.0.5. However, the linux version to use with versions like 8.1.7, RHEL/Centos 2.1, is so old that it doesn’t play nicely with VirtualBox and packer, so in al reasonability, Oracle 9.2/oracle linux 4.8 is the earliest version that can be used without severe issues.

This post is about library cache SQL cursors, and how these are managed by the database instance.

Whenever an Oracle database parses a SQL, it follows the following sequence of events (events which are of importance to the topic of cursor children):
1. The SQL text is hashed to generate a hash value.
2. The hash value is used to inspect the session cursor cache of the session to find a cached child.
If a cached entry is found, the child is inspected for compatibility, and then executed.
3. The hash value is used to calculate the library cache hash bucket.
4. The hash bucket is inspected, and the pointer is followed to find the parent handle.
5. In the parent handle a pointer is followed to the parent’s heap 0.
6. In the parent heap 0 a pointer to the hash table with the cursor children followed.
7. The hash table is read from the most recent child down to the oldest child.
If a compatible child is found, that child is executed.
8. If no compatible child is found, a new child is created.

I guess this is reasonably well known.

However, there are some nuances that are interesting to understand.

The first one is that with pluggable databases, a child has the container-id in which the child is generated as a property. During the inspection of the child list, the container-id of the child must match the container-id of the executing session, otherwise the child is skipped. This prevents from getting blocked from sessions across containers. Please mind the library cache hash table and the parent handle are shared between containers, although waiting for these should be negligible. See Oracle multi-tenant and library cache isolation.

Children are inspected from most recently created to the oldest. This is logical, more recent children have the highest probability to have been optimised with the most current state of the data.

Whenever there is no compatible child found, a new child must be created.

Incomplete list insertion.
The first thing that is done to create a new child, is an entry is allocated into the child list for the new child. This is done using the function kkshinins(), and a cursor trace will show this as:

kkshinins insert child into incomplete list bi=0x7ea05290 cld=21 flg=25

This is noteworthy, because to do so, the hash table (child list) mutex must be taken in exclusive mode. If a session wants to inspect the child list, it will briefly take the hash table mutex in shared mode. If the session creating the new child does find such a session, it will wait for ‘cursor: mutex X’. The other way around: if a session wants to inspect the child list, but find it taken by a session in exclusive mode, it will wait for ‘cursor: mutex S’.

I am not sure how to read ‘incomplete list’, and whether this means that the current child list is containing a child not yet complete, or hinting at a separate list of incomplete cursors. What I do know, is that the function kkshinins() inserts the new at this point not yet created (“incomplete”) child as the very last entry of the child list.

This means that at this point, if another session needs to find a compatible child, it will scan the child list and if no children are found compatible, it will find the incomplete child as the last one. At this point once the session finds the incomplete child, it has to wait for it, and will wait for ‘kksfbc child completion’.

Child load.
The session that is creating a new child will continue to create the new child, for which the next step is to allocate the child’s heap 0. This is done in the function kksLoadChild(). After the child’s heap 0 is allocated, it will pin the child in exclusive mode. Sessions that were waiting for ‘kksfbc child completion’ now will wait for ‘cursor: pin S wait on X’. Any other sessions that scanned the child list and did not find any compatible child, will run into this (last) child and wait for ‘cursor: pin S wait on X’ too.

There is a good reason the sessions wait for the child creation. If a session would not wait for the child creation, it would not have found any suitable child, because the incomplete child is the last one on the list, and therefore also would start creating a child. The reason this doesn’t happen is that in such case, there is a fair chance that both sessions would create identical children.

At this point, after the child cursor is pinned in exclusive mode, the session will continue to create the child and perform all the steps it needs to do during parsing. In fact, child generation is the actual thing that is considered ‘hard parse’. This includes syntax checking, semantic checking, execution path selection, optimization, creation of the executable version of the cursor (in heap 6) etc.

Incomplete list removal.
When the child creation is finished, the just created child is taken off the incomplete list (function kkshindel()), and inserted into child list as first entry (function kkshhcins()). This requires exclusive access to the child list once again, so when there is high concurrency, this might yield ‘cursor: mutex X’ for the session creating the child, and/or ‘cursor: mutex S’ for the sessions willing to scan the child list. A cursor trace shows:

kkshindel remove child from incomplete list bi=0x7ea05290 cld=21 flg=30
kkshhcins insert child into hash table bi=0x7ea05290 cld=21 flg=38

I am not sure if it’s a change in the child’s status (perhaps the ‘flg’ field shown in the trace?) being taken off the incomplete list, but what does happen is that the child after being taken off the incomplete list is inserted as first entry on the list of children. The next step is that the exclusive pin of this child is changed to shared. Any sessions that were waiting for child creation in ‘cursor: pin S wait on X’ now will continue and can inspect the child to check compatibility, and if found compatible execute the newly created child. If the child it was waiting for was not compatible, it has run out of children to check and the session will start creating a new child.

This post is the result of a question that I got after presenting a session about Oracle database mutexes organised by ITOUG, as a response to the conference cancellations because of COVID-19. Thank Gianni Ceresa for asking me!

The library cache provides shared cursors and execution plans. Because they are shared, sessions can take advantage of the work of previous sessions of creating these. However, by having these shared, access needs to be regulated not to have sessions overwrite each other’s work. This is done by mutexes.

The question I got was (this is a paraphrased from my memory): ‘when using pluggable databases, could a session in one pluggable database influence performance of a session in another pluggable database’?

The answer I gave was that I didn’t test a multi-tenant scenario, but because the library cache is shared between the pluggable databases, it should be possible for a session in one pluggable database to block another session in another pluggable database.

So let’s test it!

I used an Oracle version 20.2 database, which automatically gives you a pluggable database. In fact, even when you don’t specify you want a multi-tenant database, it will create one. This is as expected and documented.

I created an instance called o202, and two pluggable databases, PDB1 and PDB2. Yes, I am a creative genius.

I logged in to PDB1 and executed a ‘select 8 from dual’.

SQL> show con_id

CON_ID
------------------------------
3

SQL> select 8 from dual;

         8
----------
         8

Now using another session in the root container, I dumped the library cache at level 16 to see how that looks like for the ‘select 8 from dual’ cursor:

SQL> show con_id

CON_ID
------------------------------
1

SQL> alter session set events 'immediate trace name library_cache level 16';

Session altered.

(don’t do this on a live environment!)
These are snippets of certain parts of the dump:

Bucket: #=100089 Mutex=0x71f7f8d8(627065225216, 6, 0, 6)
  LibraryHandle:  Address=0x67bcca90 Hash=372d86f9 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=select 8 from dual
      FullHashValue=f18bf11763dd069341c61ce6372d86f9 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=925730553 OwnerIdn=9
...
    Concurrency:  DependencyMutex=0x67bccb40(0, 1, 0, 0) Mutex=0x67bccbe0(146, 22, 0, 6)
...
        Child:  childNum='0'
          LibraryHandle:  Address=0x6e6b3fd8 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
            Name:  Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=3
...
            Concurrency:  DependencyMutex=0x6e6b4088(0, 0, 0, 0) Mutex=0x67bccbe0(146, 22, 0, 6)

This is bucket 100089, with its mutex. The counter for this mutex is the second argument, which is 6.
The library cache handle on the next line is the parent handle. The sql text and the hash value sit with the parent handle.
I am not sure how to interpret the “ContainerId” here, it says 1 (indicating the root container), while this SQL is only executed in container 3, which is shown above.
After the first ‘…’, the two mutexes in the parent handle can be seen: the dependency mutex, and the parent handle mutex.
After the second ‘…’, child number 0 is visible. Here the container is specified from which this child was actually executed.
After the third ‘…’, the concurrency information for the child is shown. The important bit is the dependency mutex is independent/unique, whilst the regular mutex is the same/shared from the parent.

Now what would happen if I execute the exact same SQL (select 8 from dual) in another container; PDB2 alias container id 4?

SQL> show con_id

CON_ID
------------------------------
4

SQL> select 8 from dual;

         8
----------
         8

And look at bucket 100089 again (using the dump of the library cache at level 16):

Bucket: #=100089 Mutex=0x73f81e58(8589934592, 4, 0, 6)
  LibraryHandle:  Address=0x656e2638 Hash=372d86f9 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=select 8 from dual
      FullHashValue=f18bf11763dd069341c61ce6372d86f9 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=925730553 OwnerIdn=9
...
    Concurrency:  DependencyMutex=0x656e26e8(0, 2, 0, 0) Mutex=0x656e2788(2, 31, 0, 6)
...
        Child:  childNum='0'
          LibraryHandle:  Address=0x656e0ed8 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
            Name:  Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=3
...
            Concurrency:  DependencyMutex=0x656e0f88(0, 0, 0, 0) Mutex=0x656e2788(2, 31, 0, 6)
...
        Child:  childNum='1'
          LibraryHandle:  Address=0x655fba58 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
            Name:  Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=4
...
            Concurrency:  DependencyMutex=0x655fbb08(0, 0, 0, 0) Mutex=0x656e2788(2, 31, 0, 6)

Executing an identical SQL statement (which is a cursor object in the library cache) in another PDB means the same library cache hash bucket is used, and container id is one of the sharing criteria for a child. Because the statement in both cases has a different container id, the cursors can not be shared. This is very logical if you think about it: both containers essentially are logically isolated databases, and therefore there is no way to tell if the tables, access rights, data, really anything is the same, so the SQL is parsed again for the other container.

This has some implications. Because of identical SQL, it hashes to the same value, which mean both containers need access to the same bucket to find the pointer to the parent handle. Both containers also need access to the same parent and the same heap 0 to obtain the child list to find their child. Both the bucket and the handle have a single mutex that serializes access.

Access to the parent heap 0 is done shared, and remains shared if a compatible child can be found.

However, as soon as a new child needs to be created for the same SQL, it will create the new child handle pinned in exclusive mode and insert it into the hash table/child list in the parent’s heap 0. The new child is pinned in exclusive mode, because it still needs to allocate the child heaps and create the child information. This new child is the newest entry, and therefore is the first one any session would find if it scans for a compatible child.

At this point multi-tenancy makes a difference:
– If a session in the same container starts parsing and follows the procedure of hashing the SQL text, obtaining the bucket, finding the parent handle and then scan the child list for a compatible child, it will find the child in exclusive mode for this container, and waits for the child creation to finish, waiting on the event: ‘cursor: pin S wait on X’.
– If a session in ANOTHER container starts parsing and follows the procedure of hashing the SQL text, obtaining the bucket, finding the parent handle and then scan the child list for a compatible child, it will find the child in exclusive mode, but because it has a different container id, it will skip the child, and continue to scan the child list for compatible children. The scanning means pinning each child that potentially can be compatible, which means has the same container id, in shared mode, and by doing that either finds a compatible child, or if it doesn’t, create one itself.

So what does that mean; the conclusion:
The library cache hash table, the parent cursor and the child list/hash table are shared between all containers. These require unique (library cache hash table/handle) or shared (child list/hash table) access for each database process for usage, but the actual time it is held in these modes is so short that it is negligible.
The main reason for waiting for a cursor/library cache entry is a child pinned in exclusive mode during creation (‘cursor: pin S wait on X’).
When an identical statement is executed in another container, it will skip a child that is created or in the process of being created in another container, even when it’s pinned in exclusive mode.

This means that the multi-tenant option in my opinion does not significantly increase the risk of waiting because of library cache concurrency, specifically because it can skip child cursors pinned exclusively in another container.

Update april 4: I tested having a child from one container being pinned in X mode and running the same SQL in another container in 12.1.0.2.0 (base release of 12.1.0.2) and it works exactly the same as described above. So whilst the library cache hash table and parent handles are shared, the cursor children are specific to a container and do not lock each other out.

%d bloggers like this: