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.