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.

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.

Duplicate Column Names?

Recently, at a customers site, we stumbled upon an error in one of the databases alert.log. It turned out, that it was caused by the nightly statistics gathering job. There was one table for which statistics could not be generated.

SQL> exec dbms_stats.gather_table_stats('BASIS','VG_XXXXXXXXXXX', method_opt=>'for all columns size 1');

BEGIN dbms_stats.gather_table_stats('BASIS','VG_XXXXXXXXXXX', method_opt=>'for all columns size 1'); END;
 
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated
ORA-06512: at "SYS.DBMS_STATS", line 34850
ORA-06512: at line 1

So we first tried to remove the statistics, but we were unable to.

SQL> exec dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX');
BEGIN dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX'); END;
 
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated
ORA-06512: at "SYS.DBMS_STATS", line 17279
ORA-06512: at line 1
 
SQL> exec dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX',force=>true);
BEGIN dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX',force=>true); END;
 
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated
ORA-06512: at "SYS.DBMS_STATS", line 17279
ORA-06512: at line 1

So we started to dig deeper into that issue. We found out, that there is a duplicate column name when querying the Data Dictionary. But on the other hand, a simple “describe” does not show that.

SQL> desc owner.vg_xxxxxxxxxx

Name                                                  Null?    Type
----------------------------------------------------- -------- -----------------
[...]
XXXXXXXXXXXXXXX_ID                                             NUMBER(19)
XXXXXXXXXXXXXXXXX_XXX                                          NUMBER(19)
XXXXXXXXXXXXXXXXX_ID                                           NUMBER(19)
NAMENSZUSATZ                                                   NVARCHAR2(4000)
ENTITY_TIMESTAMP                                               TIMESTAMP(9)
OPTLOCKVERSION                                        NOT NULL NUMBER(19)
DATENUEBERNAHMETYP                                             NVARCHAR2(4000)
ZUGANGGEBUCHT                                         NOT NULL NUMBER(1)
UEBERSTELLER                                          NOT NULL NUMBER(1)
XXXXXXXXXXXXXX                                        NOT NULL NUMBER(1)
 
 
SQL> select column_id, column_name  from dba_tab_columns where table_name='VG_XXXXXXXXXXX' and column_name like 'Z%'
 
COLUMN_ID  COLUMN_NAME
---------- --------------------
        59 ZUGANGGEBUCHT
        59 ZUGANGGEBUCHT

Next step was to find out, where the data from the dictionary view is comming from.

SQL> select text from dba_views where view_name='DBA_TAB_COLUMNS';

TEXT
--------------------------------------------------------------------------------
select OWNER, TABLE_NAME,
       COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
       DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
       DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
       DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
       CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
       GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
       V80_FMT_IMAGE, DATA_UPGRADED, HISTOGRAM, DEFAULT_ON_NULL,
       IDENTITY_COLUMN, SENSITIVE_COLUMN,
       EVALUATION_EDITION, UNUSABLE_BEFORE, UNUSABLE_BEGINNING
  from DBA_TAB_COLS
where USER_GENERATED = 'YES'


SQL> select text from dba_views where view_name='DBA_TAB_COLS';

TEXT
--------------------------------------------------------------------------------
select
     OWNER, TABLE_NAME,
     COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
     DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
     DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
     DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
     CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
     GLOBAL_STATS,
     USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
     V80_FMT_IMAGE, DATA_UPGRADED, HIDDEN_COLUMN, VIRTUAL_COLUMN,
     SEGMENT_COLUMN_ID, INTERNAL_COLUMN_ID, HISTOGRAM, QUALIFIED_COL_NAME,
     USER_GENERATED, DEFAULT_ON_NULL, IDENTITY_COLUMN, SENSITIVE_COLUMN,
     EVALUATION_EDITION, UNUSABLE_BEFORE, UNUSABLE_BEGINNING
from dba_tab_cols_v$


SQL> select text from dba_views where view_name='DBA_TAB_COLS_V$';

TEXT
--------------------------------------------------------------------------------
select u.name, o.name,
       c.name,

[...]

Ok, DBA_TAB_COLUMNS gets its data from DBA_TAB_COLS which in turn gets its data from DBA_TAB_COLS_V$. That view has the final SQL, that is being used to retrieve the data, I shortened it, you get the idea.
We then used the SQL from DBA_TAB_COLS_V$ to find the root cause of our duplicate column.

SQL> select
  2    , sys."_CURRENT_EDITION_OBJ" o
  3    , sys.hist_head$ h
  4    c.col#, c.obj#, c.name,  c.intcol#
  5    , sys.tab$ t
  6  where o.obj# = c.obj#
  7    , h.obj#, h.intcol#
  8  from sys.col$ c
  9        or
 10        (o.type# = 2     /* tables, excluding iot - overflow and nested tables */
 11          and
 12   , sys."_CURRENT_EDITION_OBJ" o
 13                         and (bitand(t.property, 512) = 512 or
 14                              bitand(t.property, 8192) = 8192))))
 15    , sys.hist_head$ h
 16    , sys.user$ u
 17    , sys.coltype$ ac
 18    , sys.obj$ ot
 19    , sys."_BASE_USER" ut
 20    , sys.tab$ t
 21  where o.obj# = c.obj#
 22    and o.owner# = u.user#
 23    and o.obj# = t.obj#(+)
 24    and c.obj# = h.obj#(+) and c.intcol# = h.intcol#(+)
 25    and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
 26    and ac.toid = ot.oid$(+)
 27    and ot.type#(+) = 13
 28    and ot.owner# = ut.user#(+)
 29    and (o.type# in (3, 4)                                     /* cluster, view */
 30         or
 31         (o.type# = 2     /* tables, excluding iot - overflow and nested tables */
 32          and
 33          not exists (select null
 34                        from sys.tab$ t
 35                       where t.obj# = o.obj#
 36                         and (bitand(t.property, 512) = 512 or
 37                              bitand(t.property, 8192) = 8192))))
 38    and c.name='ZUGANGGEBUCHT';

      COL#       OBJ# NAME                    INTCOL#       OBJ#    INTCOL#
---------- ---------- -------------------- ---------- ---------- ----------
        60      95556 ZUGANGGEBUCHT                60
        68      95523 ZUGANGGEBUCHT                68
        59      94177 ZUGANGGEBUCHT                59      94177         59   
        59      94177 ZUGANGGEBUCHT                59      94177         59   
        74      95522 ZUGANGGEBUCHT                74
        74      95550 ZUGANGGEBUCHT                74

6 rows selected.

Have a look at the highlighted rows, there’s the duplicate column. Obviously the join to HIST_HEAD$ introduced the second row. That means, there must be something wrong inside that table. And that turned out to be true.

SQL> desc hist_head$
Name                        Null?    Type
--------------------------- -------- -------------
OBJ#                        NOT NULL NUMBER
COL#                        NOT NULL NUMBER
BUCKET_CNT                  NOT NULL NUMBER
ROW_CNT                     NOT NULL NUMBER
CACHE_CNT                            NUMBER
NULL_CNT                             NUMBER
TIMESTAMP#                           DATE
SAMPLE_SIZE                          NUMBER
MINIMUM                              NUMBER
MAXIMUM                              NUMBER
DISTCNT                              NUMBER
LOWVAL                               RAW(1000)
HIVAL                                RAW(1000)
DENSITY                              NUMBER
INTCOL#                     NOT NULL NUMBER
SPARE1                               NUMBER
SPARE2                               NUMBER
AVGCLN                               NUMBER
SPARE3                               NUMBER
SPARE4                               NUMBER

SQL> select  COL#, BUCKET_CNT, ROW_CNT, NULL_CNT, TIMESTAMP#, SAMPLE_SIZE from  hist_head$ where obj#=94177 and intcol#=59;

      COL# BUCKET_CNT    ROW_CNT   NULL_CNT TIMESTAMP#          SAMPLE_SIZE
---------- ---------- ---------- ---------- ------------------- -----------
         0          1          0      35656 2017-01-05 22:00:23        5502
         0          1          0      35383 2017-01-05 22:00:23        5775

A quick MOS-search revealed two notes, that describe the issue, Alter Table Drop Column Failing With ORA-00600[16515] (Doc ID 2375301.1) and DBMS_STATS.DELETE_TABLE_STATS Fails With ORA-600 [16515] (Doc ID 1233745.1). The latter one has the final solution for this issue.
We first identified the older of the two rows which we then deleted.

SQL> SELECT rowid,obj#,intcol#,timestamp# FROM hist_head$ WHERE obj#=94177 and intcol#=59 order by timestamp#;

ROWID                    OBJ#    INTCOL# TIMESTAMP#
------------------ ---------- ---------- -------------------
AAAABEAABAAAWh0AAj      94177         59 2017-01-05 22:00:23
AAAABEAABAAAWh0AAi      94177         59 2017-01-05 22:00:23

SQL> DELETE FROM hist_head$ WHERE ROWID='AAAABEAABAAAWh0AAj';

1 row deleted.

SQL>  COMMIT;

Commit complete.

After this, DBA_TAB_COLUMNS is back to distinct column names.

SQL> select column_id, column_name  from dba_tab_columns where table_name='VG_XXXXXXXXXXX' and column_name like 'Z%';

COLUMN_ID COLUMN_NAME
---------- --------------------
        59 ZUGANGGEBUCHT

And of cause, the statistics gathering was and is running fine again.