Oracle Clusterware (19c) and SLES 12 certification

Recently we stumbled upon an issue with Oracle Clusterware on SLES 12 SP5. We checked the certification on MOS and found this statement: “SUSE Linux Enterprise Server 12 SP3: 4.4.162-94.72-default or later”. That “or later” lead us to the assumption, that it is certified. But it is not, I’ll explain why.
If you search MOS for Clusterware certifications, you walk through these steps:



Here is a direct link to that search.
And that is the point, where you have to read very carefully to interpret the result. The title saying “SP3+” might lead to the assumption that also later SPs are supported. But in fact they are not. The note states clearly, that only “SP3” is supported with some minimum kernel version to be used. I highlighted that in the picture above. So that means in turn, only SLES 12 SP3 is certified, nothing else.
If you are using or planning to use ACFS, you should also check the certification/compatibility for that. But there is no product for ACFS, there is just a note in MOS: 1369107.1.

In this note, the certification is stated a bit more cleary. It says “SP3” followed by “comma” and then the minimum required kernel version. So the same here, just SP3 of SLES12 is certified, nothing else. As far as I know, that SP3 is not under SuSE’s support anymore at the time of writing. Only SLES 15 is, but there is no upgrade path for this. If you choose to use SLES 15, you need to install it from scratch. And there are a couple of patches required to make the Clusterware run on SLES 15.
I hope this helps you understand and interpret the certifications a bit better next time and choose the propper OD version.

Change time zone in Oracle’s DBaaS

Recently I assisted in a project that is utilizing Oracle Cloud Infrastructure (OCI) to provide application servers and Oracle databases. The customer is located in New Zealand, so they obviously wanted to work with dates and times in their local time zone. But when it came to the Database Scheduler, we found, that is was using UTC rather than the local time zone. This makes totally sense in the first place, but it’s not what we wanted to have.
Here is, what it looked like initially.

[oracle@ecotes-dbt ~]$ date
Thu Nov 14 07:05:29 UTC 2019

SQL> select sysdate from dual;

SYSDATE
----------------
14.11.2019 07:06

So at first we checked and changed the time zone for the underlying server. This is done by pointing /etc/localtime to another file.

[opc@ecotes-dbt ~]# sudo su - 
[root@ecotes-dbt ~]# ll /etc/localtime
lrwxrwxrwx 1 root root 23 Oct  7 06:51 /etc/localtime -> /usr/share/zoneinfo/UTC
[root@ecotes-dbt ~]#  ll /usr/share/zoneinfo/NZ
-rw-r--r-- 4 root root 2434 Jan 29  2018 /usr/share/zoneinfo/NZ
[root@ecotes-dbt ~]# ll /usr/share/zoneinfo/Pacific/Auckland
-rw-r--r-- 4 root root 2434 Jan 29  2018 /usr/share/zoneinfo/Pacific/Auckland
[root@ecotes-dbt ~]# rm -f /etc/localtime
[root@ecotes-dbt ~]# ln -s /usr/share/zoneinfo/Pacific/Auckland /etc/localtime
[root@ecotes-dbt ~]# ll /etc/localtime
lrwxrwxrwx 1 root root 36 Nov 14 20:10 /etc/localtime -> /usr/share/zoneinfo/Pacific/Auckland

[oracle@ecotes-dbt ~]$ date
Thu Nov 14 20:11:31 NZDT 2019

Now the database is picking up the new settings from the OS.

SQL> select sysdate from dual;

SYSDATE
----------------
14.11.2019 20:11

Unfortunately, the database scheduler has it’s own settings. So we needed to change that too.

SQL> SELECT *
2    FROM   dba_scheduler_global_attribute
3    WHERE  attribute_name = 'DEFAULT_TIMEZONE';   

ATTRIBUTE_NAME         VALUE
---------------------- -----------
DEFAULT_TIMEZONE       Etc/UTC

SQL> select dbms_scheduler.stime from dual;

STIME
---------------------------------------------------------------------------
14-NOV-19 08.26.59.556872000 AM ETC/UTC

But we can simply change this default with a single call to DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE.

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone', 'Pacific/Auckland');

PL/SQL procedure successfully completed.

SQL> SELECT *
2    FROM   dba_scheduler_global_attribute
3    WHERE  attribute_name = 'DEFAULT_TIMEZONE';   

ATTRIBUTE_NAME         VALUE
---------------------- -----------
DEFAULT_TIMEZONE       Pacific/Auckland

SQL> select dbms_scheduler.stime from dual;

STIME
---------------------------------------------------------------------------
14-NOV-19 09.31.26.365519000 PM PACIFIC/AUCKLAND

So that’s it. Now we have our DBaaS running with the time zone specific to New Zealand.


Update 2020-01-06

In the first version of my post, I did not mention the relevance of the server side time zone settings. Because a database session inherits its NLS settings from the listener that forks the process for a session. You can find a complete checklist in MOS Note 1627439.1.
Basically, you set the timezone in /etc/sysconfig/clock. The possible values can be found in /usr/share/zoneinfo.

$ cat /etc/sysconfig/clock
ZONE="Pacific/Auckland"
UTC=true
ARC=false

Before doing so, you should check if the times for UTC and your desired time zone are accurate.

$ export TZ=UTC
$ date
Fri Dec 20 11:53:48 UTC 2019
$ export TZ=Pacific/Auckland
$ date
Sat Dec 21 00:53:52 NZDT 2019

The settings for the Clusterware may also need a change. The neccessary steps can be found in part D) of the note mentioned above. Ideally you set the time zone only in $GRID_HOME/crs/install/s_crsconfig__env.txt:

$ grep ^TZ $GRID_HOME/crs/install/s_crsconfig_$(hostname -s)_env.txt
TZ=Pacific/Auckland

All other ressource specific settings shall be removed using “srvctl unsetenv [database | asm | listener] -envs TZ”, that will make those ressources use the settings defined for the clusterware.
If all those changes have been made, the Clusterware stack should be bounced to bring all the changes into effect.e geänderten Einstellungen zu aktivieren.

Oracle Grid Infrastructure vs. SLES 12

During a new installation of an Oracle Grid Infrastructure 19.4 at a customers site, I experienced a strange behaviour. The databases could be started and stopped via SQL*Plus as usual. Also stopping of databases using “srvctl” was fine. But when it came to starting a database via “srvctl” or rebooting the nodes, I experienced trouble. This looked like this:

oracle@proddb20:~> srvctl start database -db db3p
PRCR-1079 : Failed to start resource ora.db3p.db
CRS-5017: The resource action "ora.db3p.db start" encountered the following error:
ORA-00444: background process "PXMN" failed while starting
ORA-27300: OS system dependent operation:fork failed with status: 11
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/proddb20/crs/trace/crsd_oraagent_oracle.trc".

So I started to investigate and started with the alert.log of the database. It had some more details for me.

oracle@proddb20:~> tail /u01/app/oracle/diag/rdbms/db3p/db3p/trace/alert_db3p.log
2019-10-08T13:20:56.263194+02:00
Errors in file /u01/app/oracle/diag/rdbms/db3p/db3p/trace/db3p_psp0_187365.trc:
ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable
ORA-27302: failure occurred at: skgpspawn3
2019-10-08T13:20:57.237250+02:00
Process LREG died, see its trace file
USER (ospid: ): terminating the instance due to ORA error
2019-10-08T13:20:58.269727+02:00
Instance terminated by USER, pid = 187344

The messages vary, the failing function mentioned in the ORA-27302 is not always the same. A look in the mentioned tracefile revealed the following information.

oracle@proddb20:~> cat /u01/app/oracle/diag/rdbms/db3p/db3p/trace/db3p_psp0_187365.trc
Trace file /u01/app/oracle/diag/rdbms/db3p/db3p/trace/db3p_psp0_187365.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Build label:    RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
ORACLE_HOME:    /u01/app/oracle/product/19.3.0/db_ee_1
System name:    Linux
Node name:      proddb20
Release:        4.4.166-3.g849dcaf-default
Version:        #1 SMP Fri Dec 7 15:18:32 UTC 2018 (849dcaf)
Machine:        x86_64
Instance name: db3p
Redo thread mounted by this instance: 0 

Oracle process number: 4
Unix process pid: 187365, image: oracle@proddb20 (PSP0)
  
*** 2019-10-08T13:20:56.240852+02:00
*** SESSION ID:(61.55718) 2019-10-08T13:20:56.240872+02:00
*** CLIENT ID:() 2019-10-08T13:20:56.240876+02:00
*** SERVICE NAME:() 2019-10-08T13:20:56.240879+02:00
*** MODULE NAME:() 2019-10-08T13:20:56.240882+02:00
*** ACTION NAME:() 2019-10-08T13:20:56.240885+02:00
*** CLIENT DRIVER:() 2019-10-08T13:20:56.240887+02:00
 
Process startup failed, error stack:
ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable
ORA-27302: failure occurred at: skgpspawn3
OS - DIAGNOSTICS
----------------
loadavg : 0.27 0.12 0.11
Memory (Avail / Total) = 118346.89M / 128328.88M
Swap (Avail / Total) = 16386.00M /  16386.00M
Max user processes limits(s / h) =  65536 / 65536
----------------

My first guess was, that some kernel parameters were not set properly. But a quick check showed that everything was fine at that point.
That’s why I went to My Oracle Support were I quickly found this note: SLES 12: Database Startup Error with ORA-27300 ORA-27301 ORA-27303 While Starting using Srvctl (Doc ID 2340986.1). The note talks about a new functionality named “cgroup controller” introduced in SLES 12. This new functionality limits the maximum number of so-called tasks that a single process may start. The default limit for this is 512 tasks per process. For the Grid Infrastructure in it’s very basic setup right after the installation it looks like this.

proddb20:~ # systemctl status ohasd
ohasd.service - LSB: Start and Stop Oracle High Availability Service
   Loaded: loaded (/etc/init.d/ohasd; bad; vendor preset: disabled)
   Active: active (exited) since Fri 2019-10-04 14:30:19 CEST
     Docs: man:systemd-sysv-generator(8)
  Process: 4024 ExecStart=/etc/init.d/ohasd start (code=exited, tatus=0/SUCCESS)
    Tasks: 471 (limit: 512) 

As you can see, it is already near the limit. So if I now start a database instance, I will definitely reach that limit causing the instance startup to fail.
The limit can be increased by modifying/specifying the value for “DefaultTasksMax” in “/etc/systemd/system.conf”.

proddb20:~ # grep DefaultTasksMax /etc/systemd/system.conf 
#DefaultTasksMax=512
proddb20:~ # vi DefaultTasksMax /etc/systemd/system.conf 

proddb20:~ # grep DefaultTasksMax /etc/systemd/system.conf 
DefaultTasksMax=65535

After a reboot the new value is being picked up and the Grid Infrastructure can now start much more tasks. That means, the databases come up right away during the node startup and I am finally able to start the databases using “srvctl”.

proddb20:~ # systemctl status ohasd
ohasd.service - LSB: Start and Stop Oracle High Availability Service
   Loaded: loaded (/etc/init.d/ohasd; bad; vendor preset: disabled)
   Active: active (exited) since Tue 2019-10-08 14:30:19 CEST; 1min 12s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 4024 ExecStart=/etc/init.d/ohasd start (code=exited, tatus=0/SUCCESS)
    Tasks: 463 (limit: 65535)

So it is defnitely a good idea to set/increase that limit before you even start installing a Grid Infrastructure.

ODA – cleanup repository

A couple of days ago, Oracle released version 18.7 for the Oracle Database Appliance. The most useful new feature in my opinion is the “Repository Cleanup”. You can have a look at all the other new features in the Realase Notes.

For now, I’ll outline the usage of the cleanup feature. First, let’s have a look at the options:

[root@odax6m ~]# odacli cleanup-patchrepo -h
Usage: cleanup-patchrepo [options]
  Options:
    --clone, -cl
      Flag for clone directory cleanup.
    --component, -comp
      Components:{DB,GI} default{Both DB and GI}
    --help, -h
      get help
    --json, -j
      json output
    --local, -l
      Cleanup Repository for local node.
    --node, -n
      Cleanup Repository for specific nodes
    --version, -v
      ODA Patch Version.

It states, and the documentation does too, the options are all optional but in fact this isn’t true:

[root@odax6m pkgrepos]# odacli cleanup-patchrepo
DCS-10001:Internal error encountered: Invalid Input Parameter to delete the clones and patch component.

I started with the clone files. But I did not manage to delete a single DB clone. These are the clones I had:

[root@odax6m ~]# cd /opt/oracle/oak/pkgrepos/
[root@odax6m pkgrepos]# ll orapkgs/clones/
total 14615580
-r-xr-xr-x 1 root root 2395927731 Jun 17  2018 db112.180417.tar.gz
-r-xr-xr-x 1 root root 4199611255 Jun 17  2018 db121.180417.tar.gz
-rw-r--r-- 1 root root 3857973981 Jun 17  2018 db122.180417.tar.gz
-r-xr-xr-x 1 root root 4498196424 Dec 18  2018 db18.181016.tar.gz

I tried several different combinations:

[root@odax6m pkgrepos]# odacli cleanup-patchrepo -cl -v 18.3.0.0.0
[root@odax6m pkgrepos]# odacli cleanup-patchrepo -cl -v 18.5.0.0.0
[root@odax6m pkgrepos]# odacli cleanup-patchrepo -cl -comp db -v 18.3.0.0.0
[root@odax6m pkgrepos]# odacli cleanup-patchrepo -cl -v 18.3.0.0.181016
DCS-10200:Supplied version: 18.3.0.0 is invalid.

Except the last one, all cleanup jobs run successfully, but still there are all clones:

[root@odax6m pkgrepos]# ll orapkgs/clones/
total 14615580
-r-xr-xr-x 1 root root 2395927731 Jun 17  2018 db112.180417.tar.gz
-r-xr-xr-x 1 root root 4199611255 Jun 17  2018 db121.180417.tar.gz
-rw-r--r-- 1 root root 3857973981 Jun 17  2018 db122.180417.tar.gz
-r-xr-xr-x 1 root root 4498196424 Dec 18  2018 db18.181016.tar.gz

I’ll investigate this later.
Next, I cleaned the Bundlepatches. Since the ODA was freshly imaged with 18.7.0.0 there where just 18.7.0.0.0 and 18.6.0.0.0 patches installed.

[root@odax6m pkgrepos]# ll db/*
db/11.2.0.4.190416:
total 1200324
-r-xr-xr-x 1 root root        873 Sep 12 18:32 componentmetadata.xml
-r-xr-xr-x 1 root root   89475398 Sep 12 18:32 db112.patch.storage.zip
-r-xr-xr-x 1 root root    4953025 Sep 12 18:32 p28602216_112040_Linux-x86-64.zip
-r-xr-xr-x 1 root root 1133472011 Sep 12 18:32 p29255947_112040_Linux-x86-64.zip

db/11.2.0.4.190716:
total 1250008
-r-xr-xr-x 1 root root        949 Sep 12 18:31 componentmetadata.xml
-r-xr-xr-x 1 root root   89475398 Sep 12 18:31 db112.patch.storage.zip
-r-xr-xr-x 1 root root    4953025 Sep 12 18:31 p28602216_112040_Linux-x86-64.zip
-r-xr-xr-x 1 root root 1184297467 Sep 12 18:31 p29698727_112040_Linux-x86-64.zip

db/12.1.0.2.190416:
total 3285576
-r-xr-xr-x 1 root root       1015 Sep 12 18:32 componentmetadata.xml
-r-xr-xr-x 1 root root    3398176 Sep 12 18:32 p28602202_121020_Linux-x86-64.zip
-r-xr-xr-x 1 root root 3357733895 Sep 12 18:32 p29176139_121020_Linux-x86-64.zip

db/12.1.0.2.190716:
total 3635656
-r-xr-xr-x 1 root root       1091 Sep 12 18:30 componentmetadata.xml
-r-xr-xr-x 1 root root    3398176 Sep 12 18:30 p28602202_121020_Linux-x86-64.zip
-r-xr-xr-x 1 root root 3715867922 Sep 12 18:31 p29698629_121020_Linux-x86-64.zip

db/12.2.0.1.190416:
total 1701944
-r-xr-xr-x 1 root root        925 Sep 12 18:31 componentmetadata.xml
-r-xr-xr-x 1 root root    1547429 Sep 12 18:31 p28602183_122010_Linux-x86-64.zip
-r-xr-xr-x 1 root root 1736326653 Sep 12 18:32 p29301687_122010_Linux-x86-64.zip
-r-xr-xr-x 1 root root    3200431 Sep 12 18:31 p29344646_12201190416DBAPR2019RU_Linux-x86-64.zip

db/12.2.0.1.190716:
total 1934444
-r-xr-xr-x 1 root root       1001 Sep 12 18:30 componentmetadata.xml
-r-xr-xr-x 1 root root    1547429 Sep 12 18:30 p28602183_122010_Linux-x86-64.zip
-r-xr-xr-x 1 root root 1977375813 Sep 12 18:30 p29708720_122010_Linux-x86-64.zip

db/18.6.0.0.190416:
total 3364852
-r-xr-xr-x 1 root root       1010 Sep 12 18:31 componentmetadata.xml
-r-xr-xr-x 1 root root 1024357189 Sep 12 18:31 p29173957_186000ACFSRU_Linux-x86-64.zip
-r-xr-xr-x 1 root root  114529524 Sep 12 18:31 p29249584_180000_Linux-x86-64.zip
-r--r--r-- 1 root root 2303327208 Sep 12 18:31 p29764249_180000_Linux-x86-64.zip

db/18.7.0.0.190716:
total 2489356
-r-xr-xr-x 1 root root       1038 Sep 12 18:30 componentmetadata.xml
-r-xr-xr-x 1 root root 2546599237 Sep 12 18:30 p30097923_180000_Linux-x86-64.zip

The only option is to remove the Bundlepatches that came with 18.7.0.0.0.

[root@odax6m pkgrepos]# odacli cleanup-patchrepo -comp GI,DB -v 18.6.0.0.0

This time it removed the patches:

[root@odax6m pkgrepos]# ll db/*
db/11.2.0.4.190716:
total 1250008
-r-xr-xr-x 1 root root        949 Sep 12 18:31 componentmetadata.xml
-r-xr-xr-x 1 root root   89475398 Sep 12 18:31 db112.patch.storage.zip
-r-xr-xr-x 1 root root    4953025 Sep 12 18:31 p28602216_112040_Linux-x86-64.zip
-r-xr-xr-x 1 root root 1184297467 Sep 12 18:31 p29698727_112040_Linux-x86-64.zip

db/12.1.0.2.190716:
total 3635656
-r-xr-xr-x 1 root root       1091 Sep 12 18:30 componentmetadata.xml
-r-xr-xr-x 1 root root    3398176 Sep 12 18:30 p28602202_121020_Linux-x86-64.zip
-r-xr-xr-x 1 root root 3715867922 Sep 12 18:31 p29698629_121020_Linux-x86-64.zip

db/12.2.0.1.190716:
total 1934444
-r-xr-xr-x 1 root root       1001 Sep 12 18:30 componentmetadata.xml
-r-xr-xr-x 1 root root    1547429 Sep 12 18:30 p28602183_122010_Linux-x86-64.zip
-r-xr-xr-x 1 root root 1977375813 Sep 12 18:30 p29708720_122010_Linux-x86-64.zip

db/18.7.0.0.190716:
total 2489356
-r-xr-xr-x 1 root root       1038 Sep 12 18:30 componentmetadata.xml
-r-xr-xr-x 1 root root 2546599237 Sep 12 18:30 p30097923_180000_Linux-x86-64.zip

Finally, we have an option to clean the repository and provide additional space for new patch files. Hopefully your remaining space allows to patch to 18.7.

Update 2019-10-01

I just learned, that the cleanup of RDBMS clones only removes older clone files of a specific relaease. It keeps just the most current clone per RDBMS relaase. In other words, there is no way to get rid of a complete release.

[root@odax6m ~]# ll /opt/oracle/oak/pkgrepos/orapkgs/clones/
total 16961664
-r-xr-xr-x 1 root root 2395927731 Jun 17  2018 db112.180417.tar.gz
-rwxr-xr-x 1 root root 2400037923 Sep  7  2018 db112.180717.tar.gz
-r-xr-xr-x 1 root root 4199611255 Jun 17  2018 db121.180417.tar.gz
-rw-r--r-- 1 root root 3857973981 Jun 17  2018 db122.180417.tar.gz
-r-xr-xr-x 1 root root 4498196424 Dec 18  2018 db18.181016.tar.gz
[root@odax6m ~]# odacli cleanup-patchrepo -cl
{
  "jobId" : "c02181b9-d8f5-40df-90de-82df4ea82f8d",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "October 01, 2019 09:03:35 AM CEST",
  "resourceList" : [ ],
  "description" : "Cleanup patchrepos",
  "updatedTime" : "October 01, 2019 09:03:35 AM CEST"
}
[root@odax6m ~]# odacli describe-job -i "c02181b9-d8f5-40df-90de-82df4ea82f8d"

Job details
----------------------------------------------------------------
                     ID:  c02181b9-d8f5-40df-90de-82df4ea82f8d
            Description:  Cleanup patchrepos
                 Status:  Success
                Created:  October 1, 2019 9:03:35 AM CEST
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Cleanup Repository                       October 1, 2019 9:03:35 AM CEST     October 1, 2019 9:03:35 AM CEST     Success

[root@odax6m ~]# ll /opt/oracle/oak/pkgrepos/orapkgs/clones/
total 14619596
-rwxr-xr-x 1 root root 2400037923 Sep  7  2018 db112.180717.tar.gz
-r-xr-xr-x 1 root root 4199611255 Jun 17  2018 db121.180417.tar.gz
-rw-r--r-- 1 root root 3857973981 Jun 17  2018 db122.180417.tar.gz
-r-xr-xr-x 1 root root 4498196424 Dec 18  2018 db18.181016.tar.gz

As you can see, only the older clone file of the 11.2 RDBMS release was removed.

Oracle Grid Infrastructure 19c Installation

Lately I tried to install Oracle Grid Infrastructure on a SUSE SLES12 SP3 server. Don’t blame me for this OS, it was not my choice. The setup of the cluster went quite smooth, just some components did not start properly after the setup. We needed to re-issue the start or stop commands in order to make things happen. But this effect was gone one we applied the current (July 2019) Release Update. Unfortunately we saw some other nasty error messages in the crs alert.log:

CRS-8503 [__lll_unlock_elision()+48] [Signal/Exception: 11] [Instruction Addr: 0x7f1ec2deb4a0] [Memory Addr: (nil)] [] [] [] [] [] [] [] []

A quick reseach on MOS revealed note Installation of Oracle Clusterware 12c(12cR1 & 12cR2) on SLES 12 fails with CRS-8503 [Signal / Exception: 11] (Doc ID 2270947.1) with a solution, deconfigure the cluster, change some linking configuration and re-configure the cluster. I did not like the idea of re-creating the cluster, so I simply followed the steps to relink the binaries and in between, did the modifications mentioned in the MOS note.

root # export ORACLE_HOME=/u01/app/grid/19
root # $ORACLE_HOME/crs/install/rootcrs.sh -unlock
root # vi  /etc/ld.so.conf

  ### Add the this at the very top
  /lib64/noelision

root # ln -s /lib64/noelision/libpthread-2.19.so $ORACLE_HOEM/lib/libpthread.so.0

root # su - oracle
oracle $> export ORACLE_HOME=/u01/app/grid/19
oracle $> $ORACLE_HOME/bin/relink
oracle $> exit
root # $ORACLE_HOME/crs/install/rootcrs.sh -lock

Having done this, the error messages were gone.

Privilege Analysis

Todays blog post is about security. When creating database users, the least privilege principle should apply. That means, the user gets only those privileges that are needed for the work it needs to do. But we all know that many applications come with extensive requirements for the database user accounts. In most cases the development was done using DBA privileges or similar so the final installation requires these privileges too. A DBA will not like that at all, but it is difficult to identify all the privileges that are needed to apply the least-privilege-principle. That is where the “Privilege Analysis” feature comes into play. It was introduced with Oracle Database 12.1 but unfortunately it was part of the “Database Vault” extra-cost option. With the Database Release 18 this limitation was lifted and backported to 12.1 so it is now available as a standard feature of Enterprise Edition.
The following small and simple example will outline the basic functionality of the feature. The main parts are the DBMS_PRIVILEGE_CAPTURE package and the DBA_USED_PRIVS view. In the example there is a central schema named “NEXUS” that is being accessed by other users named “NEXUS”. There are roles in place wich contain all required privileges and are granted to those users. But in addition to that the “SELECT ANY TABLE” system privilege is required to make the application work. This requirement needs to be eliminated.
The first step is to create a rule that defines what should be monitored. Since a “like” expression cannot be used, it is done with a “between” that actually does the same.

BEGIN
  DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
    name        => 'COLLECT_NEXUS' ,
    description => 'Ermittlung verwendeter Privilegien von Nexus' ,
    type        => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT ,
    condition   => 'SYS_CONTEXT (''USERENV'',''SESSION_USER'') between ''NEXUS'' and ''NEXUT'''
  );
END;
/

In order to gather some data, the rule needs to be enabled.

BEGIN DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE('COLLECT_NEXUS'); END;
/

Having done this, we run through the questionable part of the application. The database will capture all the privileges we use during that time. So it is essential to deactivate this right after this.

BEGIN DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE('COLLECT_NEXUS'); END;
/

The captured data can now be analyzed using the DBA_USED_PRIVS view. In our case this looked like this:

SQL> SELECT DISTINCT USERNAME, SYS_PRIV, OBJ_PRV, OBJECT_OWNER,OBJECT_NAME,USERNAME FROM DBA_USED_PRIVS WHERE CAPTURE='COLLECT_NEXUS';

USERNAME	SYS_PRIV	     OBJ_PRIV	 USER_PRIV	 OBJECT_OWNER	 OBJECT_NAME
----------- ---------------- ----------- ----------- --------------- ---------------
NEXUS13	    CREATE PROCEDURE				
NEXUS11	    CREATE PROCEDURE				
[...]
NEXUS11	    CREATE TABLE				
NEXUS13	    CREATE VIEW				
NEXUS11	    CREATE VIEW				
NEXUS11	    SELECT ANY TABLE                        NEXUS	        KASSEKAT
NEXUS11	    SELECT ANY TABLE			            NEXUS           SPRACHEN
NEXUS13		                  SELECT		        SYS             ALL_TAB_COLUMNS
NEXUS13		                  SELECT		        SYS             ALL_CONSTRAINTS
NEXUS11		                  SELECT		        NEXUS	        BEREICHSZAHLEN
NEXUS13		                  SELECT		        SYS	            ALL_OBJECTS
NEXUS		                  EXECUTE		        SYS	            DBMS_OUTPUT
NEXUS13		                  SELECT		        NEXUS	        KAPITEL
[...]

As we can see, the application used the SELECT ANY TABLE privilge to access two tables in the main schema. The users already have SELECT privileges granted on those tables, but through a role. Since the application is using PL/SQL as well, this grant cannot be used. In this case we need to do a direct grant for these two tables. And as soon as we did that, we were able to revoke the SELECT ANY TABLE privilege with no negative impact to the application.
This example outlines the possibilities of the feature. Since it is now free-of-charge for Enterprise Edition users, it becomes quite useful. Only Standard Edition users are not allowed to make use of it at all.

Purging ADR home of upgraded Grid Infrastructure might fail

The second blog post today is about housekeeping. I typically use the “purgeLogs” script from MOS Doc-ID 2081655.1. It was intially designed for engineered systems but it works well on classic systems too. But today I found an error in the output:

2019-06-21 00:00:06: I adrci GI purging diagnostic destination diag/crs/odax6m/crs
2019-06-21 00:00:06: I ... purging ALERT older than 8 days
2019-06-21 00:00:06: W Not able to purge ALERT, due to error: DIA-49803: Purge not possible due to incompatible schema version.

Typically this happens, when you use an “adrci” that does not match the ADR home you are trying to purge. That’s maybe the reason, why “purgeLogs” tries different adrci’s to purge the RDBMS homes.
But what is wrong here? Let’s try the manual way.

oracle@odax6m ~> adrci

ADRCI: Release 18.0.0.0.0 - Production on Fri Jun 21 13:53:17 2019

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> set home crs
adrci> purge
DIA-49803: Purge not possible due to incompatible schema version.

When I put that message in MOS, I quickly found this bug: Bug 28375106 – ADRCI Purge of Grid Home Fails With “DIA-49803: Purge not possible due to incompatible schema version” After Upgrade from 12.1 (Doc ID 28375106.8). So I crosschecked that.

adrci> show version schema
Schema version (on-disk): 107
Schema version (library): 110

And indeed, the versions are different. Obviously the issue is related to
upgrades. In my case this is an Oracle Database Appliance, that has been patched from 18.3 to 18.5. Unfortunately MOS has no workaround for this. But let’s check what “adrci” may can do for me.

adrci> help extended

 HELP [topic]
   Available Topics:
        BEGIN BACKUP
        CD
        CREATE STAGING XMLSCHEMA
        CREATE VIEW
        DDE
        DEFINE
        DELETE
        DESCRIBE
        DROP VIEW
        END BACKUP
        INSERT
        LIST DEFINE
        MERGE ALERT
        MERGE FILE
        MERGE LOG
        MIGRATE
        QUERY
        REPAIR
        SET COLUMN
        SHOW CATALOG
        SHOW DUMP
        SHOW SECTION
        SHOW TRACE
        SHOW TRACEMAP
        SWEEP
        UNDEFINE
        UPDATE
        VIEW

The “MIGRATE” sounds interresting.

adrci> help migrate

  Usage: MIGRATE [RELATION  | SCHEMA] [-DOWNGRADE | -RECOVER]

  Purpose: Upgrades the relation or schema to the latest version

  Options:
    [-downgrade] Downgrade schema to previous version
    [-recover]   Recover schema from failed migrate

  Examples:
    migrate schema
    migrate relation incident

Sounds like I do want to do exactly this. Give it a try.

adrci> migrate schema
Schema migrated.
adrci> show version schema
Schema version (on-disk): 110
Schema version (library): 110

And voilà, now I can purge the Grid Inftrastructure ADR home again.

adrci> purge
adrci>

Since the bug is about the behaviour after upgrading from 12.1, this might apply to other upgrade or update paths too. Be aware of that and monitor your housekeeping scripts. And check your ADR schema version after you upgraded or patched your Grid Infrastructure installation.