Category Archives: Oracle

Oracle (Database and Middleware) related posts

Do I really have to set resource limits for Oracle in systemd unit files?

TL;DR: it is very much required to set resource limits in systemd unit files. If you’d like to learn more about potential directives to put into a unit file, please have a look at the earlier post I wrote for a more thorough explanation. I also wrote a short post about a potential unit file used to start an Oracle single-instance database (without Oracle Restart) with a couple more details.

Failing to set resource limits in systemd unit files can lead to outages.

Large pages are a good example

Without the Limit-directives added to the unit file the database(s) might refuse to start. Using large pages is a good example. Switching a database to enforce the use of large pages is easy:

SQL> ALTER SYSTEM SET use_large_pages='ONLY' SCOPE=SPFILE;

System altered.

When implementing this directive you tell Oracle not to start at all unless the entire Shared Global Area (SGA) fits into large pages. It also means the database won’t start if your configuration is wrong. Better get it right :)

How many large pages do I need?

On my host with a single Oracle database it couldn’t be easier to work out the number of large pages required. The alert.log tells me I need to allocate 2050 large pages if I want the SGA to fit completely. If there are multiple Oracle instances per host, you need to allocate the sum of all required large pages without over-allocating them (more on that later).

On my system 2050 large pages is comfortably below the mark of 70% of available memory. As a rule of thumb, don’t allocate more memory in large pages than 70% of your total available memory. The O/S, most processes and the PGA cannot use large pages. Allocating too many large pages is a sure way to create instability and it’s not easy to fix so please don’t do it.

Again, as there are no other Oracle instances on the host, defining the appropriate number of large pages is simple. To make the change permanent I need to either change /etc/sysctl.conf (not a good idea) or create a new file /etc/sysctl.d/99-oracle-large-pages.conf (preferred) containing the following directive:

vm.nr_hugepages = 2052

I was lucky and managed to allocate the necessary number of large pages at runtime saving the time of a reboot. Either way, the output of cat /proc/sys/vm/nr_hugepages needs to show the correct number of large pages available:

$ cat /proc/sys/vm/nr_hugepages
2052

Next I’m shutting the database down before removing LimitMEMLOCK from the unit file mentioned in my earlier post, and reloading the systemd daemon. As you might have imagined, the database doesn’t come up when the system reboots:

$ ps -ef | egrep "tns|smon"
root          37       2  0 09:30 ?        00:00:00 [netns]
oracle      1011       1  0 19:30 ?        00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr LISTENER -inherit
vagrant     7829    7805  0 19:31 pts/0    00:00:00 grep -E --color=auto tns|smon

This shouldn’t be a surprise. The default memlock setting is 64kb, not quite enough for my 4 GB SGA:

$ whoami
vagrant
$ egrep "^Limit|Max locked memory" /proc/$$/limits  
Limit                     Soft Limit           Hard Limit           Units     
Max locked memory         65536                65536                bytes   

And sure enough, ${ORACLE_HOME}/rdbms/log/startup.log confirm the suspicion:

/u01/app/oracle/product/19.0.0/dbhome_1/bin/dbstart: Starting up database "ORCL"
Thu Jun 17 19:30:14 UTC 2021


SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 17 19:30:15 2021
Version 19.11.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

SQL> Connected to an idle instance.
SQL> ORA-27125: unable to create shared memory segment
Linux-x86_64 Error: 1: Operation not permitted
Additional information: 4641
Additional information: 10485760
SQL> Disconnected

/u01/app/oracle/product/19.0.0/dbhome_1/bin/dbstart: Database instance "ORCL" warm started.

Well it wasn’t really warm started, was it ;) I had 2052 large pages available for the SGA, the problem is the shell limit: oracle isn’t allowed to lock more than the default 64k of memory in large pages since systemd doesn’t use pam_limits(8).

Restoring the LimitMEMLOCK directive fixes that problem. After changing the unit file I rebooted once more and, voila:

$ ps -ef | egrep "tns|smon"
root          37       2  0 19:37 ?        00:00:00 [netns]
oracle      1016       1  0 19:37 ?        00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle      2761       1  0 19:37 ?        00:00:00 ora_smon_ORCL
vagrant     4438    4412  0 19:39 pts/0    00:00:00 grep -E --color=auto tns|smon

The database is up and running, and it uses large pages:

$ grep -i ^hugepages_ /proc/meminfo
HugePages_Total:    2052
HugePages_Free:        5
HugePages_Rsvd:        3
HugePages_Surp:        0

Summary

You can use systemd on Oracle Linux 8 to start your databases along with the operating system. This is great in situations when you don’t want to/can’t use Oracle Restart and/or Oracle Real Application Clusters. Making use of systemd.directives(7) allows the database administrator to set appropriate limits for processes created by systemd as the latter doesn’t read settings in /etc/limits.d/* and /etc/limits.conf.

Using systemd to start an Oracle single instance database when booting

I don’t work with systemd frequently enough to remember its syntax and other intricacies so I thought I’d just write down how to start an Oracle 19c single instance database via systemd on Oracle Linux 8.4. Primarily so I can look it up later when I need it, but isn’t that the reason many bloggers write posts?

A little bit of background to this post

There are quite a few blog articles out there describing how to start an Oracle database when a server comes up, however some of them still use the old SysV init model, or upstart. Or a combination of both.

Since RedHat/Oracle Linux 7, systemd took over as the main component controlling the boot process (and a lot more …), so using systemd‘s native syntax and tooling sounds good to me. Please note that the technique you are about to read does not apply to Oracle Restart or Oracle Real Application Clusters. Furthermore, I haven’t had time to test any other combination than my lab VM running Oracle Linux 8.4 and Oracle database 19c (19.11.0 to be precise).

If you’d like to have some additional background information please have a look at Shell limits, Oracle Single Instance, and systemd in Oracle Linux 8.4. It explains my choice of location for the systemd unit file and some of the configuration directives.

As always, just because something works for me doesn’t mean it works for you as well, so test, test, and test some more if you like to make use of (parts of) the article.

Creating the systemd unit file

Based on the sources cited in the earlier post (such as the Red Hat 8 documentation and the always-excellent oracle-base.com) I put a unit file together. It lives in /etc/systemd/system/oracle-db.service.

[Unit]
Description=a service to start databases and listener automatically
After=syslog.target network.target

[Service]
LimitNOFILE=1024:65536
LimitNPROC=2047:16384
LimitSTACK=10485760:33554432
LimitMEMLOCK=infinity

Type=forking

User=oracle
Group=oinstall

ExecStart=/u01/app/oracle/product/19.0.0/dbhome_1/bin/dbstart /u01/app/oracle/product/19.0.0/dbhome_1
ExecStop=/u01/app/oracle/product/19.0.0/dbhome_1/bin/dbshut /u01/app/oracle/product/19.0.0/dbhome_1

RemainAfterExit=True
Restart=no

[Install]
WantedBy=multi-user.target

The unit file defines the properties of the oracle-db service. Most notably:

  • It sets the necessary process limits in the Service section
  • The service type indicates a process is forked by this unit file
  • Since dbstart and dbsthut exit once their work is completed I need to tell systemd to consider the status of the service to be active via the RemainAfterExit directive
  • I also don’t want the service to be restarted automatically
  • The unit file relies on Oracle’s dbstart and dbshut scripts to start and stop the databases on this host

The unit file assumes locally attached storage. Have a look at the article on oracle-base.com if you need NFS for an example on how to define dependencies for NFSv3. Similarly, you might want to add dependencies on iSCSI or other network storage.

The next step is to modify /etc/oratab and change the start flag for my database (there is only one, ORCL):

$ grep $ORACLE_SID /etc/oratab
ORCL:/u01/app/oracle/product/19.0.0/dbhome_1:Y

Only those entries in oratab with a Y as their start flag will be considered by dbstart and dbstop.

Hint: the above is super easy to automate with Ansible and jinja2 templates :)

Next you should execute sudo systemctl daemon-reload to make systemd aware of the new unit file. Once systemd knows about it, it should be enabled via systemctl enable oracle-db.service.

Starting and stopping the database

After a reboot I found the following entry for oracle-db.service in systemd‘s journal:

Jun 17 18:34:47 server1 systemd[1]: Starting a service to start databases and listener automatically...
Jun 17 18:34:48 server1 dbstart[973]: Processing Database instance "ORCL": log file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/startup.log
Jun 17 18:35:04 server1 systemd[1]: Started a service to start databases and listener automatically.

That seemed to have worked. The unit file also took care of the database when the VM was shut down:

Jun 17 18:39:50 server1 systemd[1]: Stopping a service to start databases and listener automatically...
Jun 17 18:39:50 server1 dbshut[4486]: Processing Database instance "ORCL": log file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/shutdown.log
Jun 17 18:40:12 server1 systemd[1]: oracle-db.service: Succeeded.
Jun 17 18:40:12 server1 systemd[1]: Stopped a service to start databases and listener automatically.

It appears as if my unit file does what I want it to do, hurray.

Geeking out on Linux: file descriptors and their open modes & flags

While preparing my talk for the upcoming POUG 2021 conference I wanted to know which flags Oracle passes to open(2) when accessing a data file. A file needs to be opened before its contents can be accessed (no surprises here :). There are quite a few ways of opening a file, depending on whether you want to use the file system cache or not, and whether you want to perform synchronous or asynchronous I/O. There are more alternatives as well but I haven’t come across them with the Oracle database so let’s stick with the arguments/flags/modes available with open(2).

Based on what I understand of how Linux works, you can use the following methods to get the flags passed to open(2):

  1. Use a tracer to catch the call to open(2) when it happens
  2. Find a way to look up flags passed to open(2) after the call completed

This posts shows you both. I should point out though that tracing is an intrusive process and all steps you see in this post have been performed in my Oracle-on-vagrant build running on my laptop. The post is for educational purposes only, especially since you don’t need a tracer to get the answer.

WARNING: don’t use tracers in production environments! They can crash systems. If you are curious about Linux tracing and profiling, always experiment on lab VMs where you are on your own, and crashes don’t cause problems to others. Or worse.

Background

Consider the following example. I have created a session with sqlplus (any other client will do, too) and am about to issue my first query. This is an Oracle 19c database although I’m pretty sure this has been the standard behaviour for quite some time. Please note that the following steps don’t apply when you are using Automatic Storage Management (ASM). I also used filesystemio_options set to none. You see, this is really a post for educational purposes.

Tracing

At this stage, I haven’t touched a user-created segment. Oracle will have to open the data file containing my table next. Let’s see what happens when I run my select statement. Opening a second session on the host I can create a bpftrace probe and attach it to sys_enter_openat() on my server process ID (SPID, 21554). As soon as I start my query in session 1, Oracle opens the user tablespace’s only data file and my probe in session 2 fires:

[root@server1 ~]# bpftrace -e 'tracepoint:syscalls:sys_enter_openat /pid == 21554/ { 
    printf("%s opened file %s with flags %x mode %x\n", comm, str(args->filename), args->flags, args->mode); 
}'
Attaching 1 probe...
oracle_21554_or opened file /u02/oradata/ORCL/datafile/o1_mf_users_jdoz9om2_.dbf with flags 1002 mode 0

If you aren’t intimately familiar with Linux you might wonder how I knew which arguments to pass to printf()? That’s easy enough, the format of sys_enter_openat is available in /sys/kernel/debug/tracing/events/syscalls/sys_enter_openat/format

# cat /sys/kernel/debug/tracing/events/syscalls/sys_enter_openat/format
name: sys_enter_openat
ID: 624
format:
	field:unsigned short common_type;	offset:0;	size:2;	signed:0;
	field:unsigned char common_flags;	offset:2;	size:1;	signed:0;
	field:unsigned char common_preempt_count;	offset:3;	size:1;	signed:0;
	field:int common_pid;	offset:4;	size:4;	signed:1;

	field:int __syscall_nr;	offset:8;	size:4;	signed:1;
	field:int dfd;	offset:16;	size:8;	signed:0;
	field:const char * filename;	offset:24;	size:8;	signed:0;
	field:int flags;	offset:32;	size:8;	signed:0;
	field:umode_t mode;	offset:40;	size:8;	signed:0;

print fmt: "dfd: 0x%08lx, filename: 0x%08lx, flags: 0x%08lx, mode: 0x%08lx", ((unsigned long)(REC->dfd)), ((unsigned long)(REC->filename)), ((unsigned long)(REC->flags)), ((unsigned long)(REC->mode))

Back to the output generated by bpftrace. Unfortunately this isn’t quite as straight-forward as I thought. First of all, I’m returning the arguments to openat() as a hexadecimal number. Actually the flags passed to openat() are in octal notation, which requires translating them. The obvious use of %o in printf() isn’t permitted. 1002 in base 16 equals 10002 in base 8.

But what does the flag really mean?

But that’s only half the job done. What does the flag actually mean? Researching a little bit I found a few header files with more details. Long story short I found what I needed in /usr/include/asm-generic/fcntl.h. But … Linux x86-64 doesn’t implement all of these. You can check whether a given flag is valid or not in /usr/src/kernels/$(uname -r)/include/linux/fcntl.h.

With all that research done I now can tell you that a flag of 10002 means the file was opened using O_RDWR and O_DSYNC.

[oracle@server1 ~]$ egrep 'define O.*[0-9]+' /usr/include/asm-generic/fcntl.h
...
#define O_RDONLY	00000000
#define O_WRONLY	00000001
#define O_RDWR		00000002
...
#define O_DSYNC		00010000	/* used to be O_SYNC, see below */
#define O_DIRECT	00040000	/* direct disk access hint */
#define O_LARGEFILE	00100000
#define O_DIRECTORY	00200000	/* must be a directory */
#define O_NOFOLLOW	00400000	/* don't follow links */
#define O_NOATIME	01000000
#define O_CLOEXEC	02000000	/* set close_on_exec */
...
[oracle@server1 ~]$ 

You test whether a flag is set by using a bitwise and operation, for O_RDWR that’s easy enough in bash:

$ echo $(( 10002 & 2 ))
2

If the result of the bitand operations matches the flag’s value (in this case 2 equals 2) the flag is set.

And what does it mean? Oracle opened the data file read/write for synchronous I/O. Which was to be expected given I’m using a file system with filesystemio_options set to none. Oracle will neither use direct I/O bypassing the file system cache, nor asynchronous I/O either.

Let’s recap for a minute. So far I have used a tracer to read the arguments to openat(2) and decoded the flag after translating its hexadecimal representation to an octal value. That’s quite a lot of work and unless you know the flags in open(2) by heart not very enjoyable. Furthermore, you won’t catch the arguments to open(2) if the process has issued the statement before you traced it.

Determining the flags retrospectively

There are many ways to do so, the easiest is to check /proc/pid/fdinfo/fd. The first step is to identify the file descriptor of the data file opened by the Oracle process. In this case the user tablespace’s data file was opened with a file descriptor of 259

[oracle@server1 ~]$ ls -l /proc/21554/fd
total 0
lr-x------. 1 oracle oinstall 64 Jul  9 19:49 0 -> /dev/null
l-wx------. 1 oracle oinstall 64 Jul  9 19:49 1 -> /dev/null
lr-x------. 1 oracle oinstall 64 Jul  9 19:49 10 -> 'pipe:[127168]'
l-wx------. 1 oracle oinstall 64 Jul  9 19:49 13 -> 'pipe:[127169]'
l-wx------. 1 oracle oinstall 64 Jul  9 19:49 2 -> /dev/null
lrwx------. 1 oracle oinstall 64 Jul  9 19:50 256 -> /u02/oradata/ORCL/datafile/o1_mf_system_jdoz8275_.dbf
lrwx------. 1 oracle oinstall 64 Jul  9 19:50 257 -> /u02/oradata/ORCL/datafile/o1_mf_sysaux_jdoz95dk_.dbf
lrwx------. 1 oracle oinstall 64 Jul  9 19:50 258 -> /u02/oradata/ORCL/datafile/o1_mf_undotbs1_jdoz9nj3_.dbf
lrwx------. 1 oracle oinstall 64 Jul  9 19:50 259 -> /u02/oradata/ORCL/datafile/o1_mf_users_jdoz9om2_.dbf
lr-x------. 1 oracle oinstall 64 Jul  9 19:49 3 -> /dev/null
lr-x------. 1 oracle oinstall 64 Jul  9 19:49 4 -> /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/mesg/oraus.msb
lr-x------. 1 oracle oinstall 64 Jul  9 19:49 5 -> /proc/21554/fd
lr-x------. 1 oracle oinstall 64 Jul  9 19:49 6 -> /var/lib/sss/mc/passwd
lrwx------. 1 oracle oinstall 64 Jul  9 19:49 7 -> 'anon_inode:[eventpoll]'

I can grab the flags from fdinfo:

[oracle@server1 ~]$ grep flags /proc/21554/fdinfo/259
flags:	02110002

Huh? That’s 8 bit though, not 5 as in the previous example. Decoding the flag was a lot easier this time as I knew the flags and their bitmasks.

02110002
not setO_CLOEXECO_LARGEFILEO_DSYNCnot set not setnot setO_RDWR
Bitmask set in fdinfo decoded

Still very much the same picture as before with the addition of the O_LARGEFILE and O_CLOEXEC flags. Check man open(2) If you want to know what they imply.

Using lsof is cheating

If you are lucky enough to have lsof installed on your system you can use it as well. Although it’s not much of a challenge since lsof decodes the flags for you ;) Well most of them at least. The following output was generated on a different system hence the PID and fd don’t match the earlier outptut:

[oracle@server1 ~]$ lsof -p 10009 +fg | egrep 'COMMAND|users'
COMMAND     PID   USER   FD      TYPE         FILE-FLAG DEVICE   SIZE/OFF      NODE NAME
oracle_10 10009 oracle  257u      REG RW,SYN,LG,0x80000   8,33   15736832  16777348 /u02/oradata/ORCL/datafile/o1_mf_users_jdoz9om2_.dbf
[oracle@server1 ~]$ 

And you don’t even need to be root to get these details. Unfortunately lsof as packaged in Linux 8.4 (4.93.2) doesn’t recognise O_CLOEXEC as per a bugzilla entry I found.

Summary

I love Linux since there are so many ways to do things. My talk at POUG 2021 will contain lots of references to BCC and bpftrace so this was a good starting point to learn about these tools. On the other hand it turned out I didn’t need to concern myself with tracing tools at all as Linux often keeps information in many places. You just need to know what to do with it.

Shell limits, Oracle Single Instance, and systemd in Oracle Linux 8.4

The more blog posts I write the harder it becomes to come up with a good title. So what is this post all about? In short, it’s about the implications of using systemd to start a single instance Oracle database in Oracle Linux and associated shell limits. In theory this should apply to Single Instance Oracle only – Oracle Restart/Real Application Clusters use different means to control the start of databases via their respective registries.

A short introduction to systemd

As of Oracle Linux 7 systemd replaced upstart in the same way upstart replaced SysV init earlier. systemd is a system and service manager for Linux Operating systems and does a lot more than either of its predecessors. It’s also extremely well documented in man pages.

An Oracle administrator might use systemd to start a database automatically when a system boots up. This is done via a so-called unit file, containing the necessary instructions to start the Oracle database.

Which leads me back to the topic of this post. You can read at oracle-base.com and a few other places that systemd does not respect shell limits set by pam_limits(8). Which is considered a feature; feel free to check the bugzilla discussion for more details. Remember that pam_limits(8) and its associated limits.conf file is used to configure shell limits for the oracle user, as per the Database Installation Guide.

To demonstrate the effect I’m going to use my most recent Oracle Linux 8.4 lab VM. The oracle account has been created and configured via the 19c preinstall RPM. I’m using my Vagrant box, updated to Oracle Linux 8.4 including all patches up to June 14th.

NOTE: the settings you’ll see later in the unit files are for Oracle Linux 8.4. Although Oracle Linux 7 uses systemd as well it might not support the same directives and/or syntax.

Let’s have a look at systemd unit files.

Creating a oneshot systemd unit file

I picked /etc/systemd/system as the location for my new unit file. At least in Oracle Linux 8.4 that seems to be the preferred location for custom unit files.

WARNING: This is totally lab VM/playground territory, you should never do this in a real (live) environment!

If you find the examples confusing because you aren’t familiar with systemd please head over to the documentation for more information.

# cat /etc/systemd/system/limitstest.service 
[Unit]
Description=A oneshot service to test whether systemd respects PAM limits

[Service]
User=oracle
Group=oinstall
Type=oneshot
ExecStart="/home/oracle/test.sh"

This unit file merely fires off /home/oracle/test.sh (once) as oracle:oinstall. The little shell script in /home/oracle/test.sh couldn’t be simpler:

$ cat /home/oracle/test.sh 
#!/usr/bin/env bash

echo "-----------------------------------------------------------"
echo "test run starts at $(/usr/bin/date):"
echo "I am $(/usr/bin/whoami)"

echo ""
echo "my own PID is: $$"
echo ""
/usr/bin/ps -ef| /usr/bin/grep $$ | /usr/bin/grep -v grep

echo ""
echo "hard limits according to ulimit:"
echo ""
ulimit -Ha

echo ""
echo "soft limits according to ulimit:"
echo ""
ulimit -Sa

echo ""
echo "actual limits as per /proc":
echo ""
/usr/bin/cat /proc/$$/limits

echo -----------------------------------------------------------

It prints a few PIDs and shell limits (both hard and soft limits). Finally, it looks into the /proc file system to get the shell limits of itself (${$}).

Unless you restart your (virtual) server, systemd won’t know about the new unit file. Alternatively you can run systemctl daemon-reload to reload the daemon.

Running the unit file

So let’s go ahead and see what the output of the unit file is. I started it using systemctl start limitstest.service.

# journalctl -u limitstest.service

...

Jun 15 19:44:07 server1 systemd[1]: Starting A oneshot service to test whether systemd respects PAM limits...
Jun 15 19:44:07 server1 test.sh[13046]: -----------------------------------------------------------
Jun 15 19:44:07 server1 test.sh[13046]: test run starts at Tue Jun 15 19:44:07 UTC 2021:
Jun 15 19:44:07 server1 test.sh[13046]: I am oracle
Jun 15 19:44:07 server1 test.sh[13046]: my own PID is: 13046
Jun 15 19:44:07 server1 test.sh[13046]: oracle     13046       1  0 19:44 ?        00:00:00 bash /home/oracle/test.sh
Jun 15 19:44:07 server1 test.sh[13046]: oracle     13049   13046  0 19:44 ?        00:00:00 /usr/bin/ps -ef
Jun 15 19:44:07 server1 test.sh[13046]: hard limits according to ulimit:
Jun 15 19:44:07 server1 test.sh[13046]: core file size          (blocks, -c) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: data seg size           (kbytes, -d) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: scheduling priority             (-e) 0
Jun 15 19:44:07 server1 test.sh[13046]: file size               (blocks, -f) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: pending signals                 (-i) 30554
Jun 15 19:44:07 server1 test.sh[13046]: max locked memory       (kbytes, -l) 64
Jun 15 19:44:07 server1 test.sh[13046]: max memory size         (kbytes, -m) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: open files                      (-n) 262144
Jun 15 19:44:07 server1 test.sh[13046]: pipe size            (512 bytes, -p) 8
Jun 15 19:44:07 server1 test.sh[13046]: POSIX message queues     (bytes, -q) 819200
Jun 15 19:44:07 server1 test.sh[13046]: real-time priority              (-r) 0
Jun 15 19:44:07 server1 test.sh[13046]: stack size              (kbytes, -s) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: cpu time               (seconds, -t) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: max user processes              (-u) 30554
Jun 15 19:44:07 server1 test.sh[13046]: virtual memory          (kbytes, -v) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: file locks                      (-x) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: soft limits according to ulimit:
Jun 15 19:44:07 server1 test.sh[13046]: core file size          (blocks, -c) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: data seg size           (kbytes, -d) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: scheduling priority             (-e) 0
Jun 15 19:44:07 server1 test.sh[13046]: file size               (blocks, -f) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: pending signals                 (-i) 30554
Jun 15 19:44:07 server1 test.sh[13046]: max locked memory       (kbytes, -l) 64
Jun 15 19:44:07 server1 test.sh[13046]: max memory size         (kbytes, -m) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: open files                      (-n) 1024
Jun 15 19:44:07 server1 test.sh[13046]: pipe size            (512 bytes, -p) 8
Jun 15 19:44:07 server1 test.sh[13046]: POSIX message queues     (bytes, -q) 819200
Jun 15 19:44:07 server1 test.sh[13046]: real-time priority              (-r) 0
Jun 15 19:44:07 server1 test.sh[13046]: stack size              (kbytes, -s) 8192
Jun 15 19:44:07 server1 test.sh[13046]: cpu time               (seconds, -t) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: max user processes              (-u) 30554
Jun 15 19:44:07 server1 test.sh[13046]: virtual memory          (kbytes, -v) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: file locks                      (-x) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: actual limits as per /proc:
Jun 15 19:44:07 server1 test.sh[13046]: Limit                     Soft Limit           Hard Limit           Units
Jun 15 19:44:07 server1 test.sh[13046]: Max cpu time              unlimited            unlimited            seconds
Jun 15 19:44:07 server1 test.sh[13046]: Max file size             unlimited            unlimited            bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max data size             unlimited            unlimited            bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max stack size            8388608              unlimited            bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max core file size        unlimited            unlimited            bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max resident set          unlimited            unlimited            bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max processes             30554                30554                processes
Jun 15 19:44:07 server1 test.sh[13046]: Max open files            1024                 262144               files
Jun 15 19:44:07 server1 test.sh[13046]: Max locked memory         65536                65536                bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max address space         unlimited            unlimited            bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max file locks            unlimited            unlimited            locks
Jun 15 19:44:07 server1 test.sh[13046]: Max pending signals       30554                30554                signals
Jun 15 19:44:07 server1 test.sh[13046]: Max msgqueue size         819200               819200               bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max nice priority         0                    0
Jun 15 19:44:07 server1 test.sh[13046]: Max realtime priority     0                    0
Jun 15 19:44:07 server1 test.sh[13046]: Max realtime timeout      unlimited            unlimited            us
Jun 15 19:44:07 server1 test.sh[13046]: -----------------------------------------------------------
Jun 15 19:44:07 server1 systemd[1]: limitstest.service: Succeeded.
Jun 15 19:44:07 server1 systemd[1]: Started A oneshot service to test whether systemd respects PAM limits.

...

All right, that worked and we are off to a good start. Those of us who installed Oracle a few times might already spot a few details in the above output. It’s not quite what I had in mind.

Executing test.sh in an interactive shell

For comparison, this is the output when logged in as oracle (in an interactive shell)

$ ./test.sh 
-----------------------------------------------------------
test run starts at Tue Jun 15 20:13:41 UTC 2021:
I am oracle

my own PID is: 13173

oracle     13173   13150  0 20:13 pts/0    00:00:00 bash ./test.sh
oracle     13176   13173  0 20:13 pts/0    00:00:00 /usr/bin/ps -ef

hard limits according to ulimit:

core file size          (blocks, -c) unlimited
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 30554
max locked memory       (kbytes, -l) 134217728
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65536
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 32768
cpu time               (seconds, -t) unlimited
max user processes              (-u) 16384
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

soft limits according to ulimit:

core file size          (blocks, -c) unlimited
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 30554
max locked memory       (kbytes, -l) 134217728
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 16384
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

actual limits as per /proc:

Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            10485760             33554432             bytes     
Max core file size        unlimited            unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             16384                16384                processes 
Max open files            1024                 65536                files     
Max locked memory         137438953472         137438953472         bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       30554                30554                signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us        
-----------------------------------------------------------

As you can see, there are quite some differences making it necessary to set limits via systemd-specific syntax in the unit file itself. I can only conclude that systemd – as documented – does not pay attention to the configuration set by pam_limits(8).

OK, so shell limits aren’t respected, what next?

So if systemd doesn’t make use of pam_limits(8) there needs to be a different solution. There is a whole raft of options documented in systemd.directives(7) for Oracle Linux 8. Again, this might be different for Oracle Linux 7. The database needs the following shell limits to be set:

  • open file descriptors (“nofile”)
  • number of processes available to a single user (“nproc”)
  • Size of the stack segment per process (“stack”)
  • Maximum locked memory limit (“memlock”)

These map to the following systemd.directives(7):

  • LimitNOFILE
  • LimitNPROC
  • LimitSTACK
  • LimitMEMLOCK

You should really go ahead and read the man pages for systemd, they are great!

Amending the unit file

The next step is to add the above directives to the unit file, reload systemd, start the service and see what happens. Note that most sources I found only set LimitMEMLOCK and LimitNOFILE for Oracle. I am using the values from the Oracle documentation – your system might require different settings.

[Unit]
Description=A oneshot service to test whether systemd respects PAM limits

[Service]
LimitNOFILE=1024:65536
LimitNPROC=2047:16384
LimitSTACK=10485760:33554432
LimitMEMLOCK=infinity
User=oracle
Group=oinstall
Type=oneshot
ExecStart="/home/oracle/test.sh"

Apart from the additional Limit.* directives, it’s the same file. The syntax in Oracle Linux 8 is LimitDIRECTIVE=soft:hard limit.

With the unit file changed, the following limits have been recorded:

Jun 16 20:30:57 server1 systemd[1]: Starting A oneshot service to test whether systemd respects PAM limits...
Jun 16 20:30:57 server1 test.sh[13821]: -----------------------------------------------------------
Jun 16 20:30:57 server1 test.sh[13821]: test run starts at Wed Jun 16 20:30:57 UTC 2021:
Jun 16 20:30:57 server1 test.sh[13821]: I am oracle
Jun 16 20:30:57 server1 test.sh[13821]: my own PID is: 13821
Jun 16 20:30:57 server1 test.sh[13821]: oracle     13821       1  0 20:30 ?        00:00:00 bash /home/oracle/test.sh
Jun 16 20:30:57 server1 test.sh[13821]: oracle     13824   13821  0 20:30 ?        00:00:00 /usr/bin/ps -ef
Jun 16 20:30:57 server1 test.sh[13821]: hard limits according to ulimit:
Jun 16 20:30:57 server1 test.sh[13821]: core file size          (blocks, -c) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: data seg size           (kbytes, -d) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: scheduling priority             (-e) 0
Jun 16 20:30:57 server1 test.sh[13821]: file size               (blocks, -f) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: pending signals                 (-i) 30554
Jun 16 20:30:57 server1 test.sh[13821]: max locked memory       (kbytes, -l) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: max memory size         (kbytes, -m) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: open files                      (-n) 65536
Jun 16 20:30:57 server1 test.sh[13821]: pipe size            (512 bytes, -p) 8
Jun 16 20:30:57 server1 test.sh[13821]: POSIX message queues     (bytes, -q) 819200
Jun 16 20:30:57 server1 test.sh[13821]: real-time priority              (-r) 0
Jun 16 20:30:57 server1 test.sh[13821]: stack size              (kbytes, -s) 32768
Jun 16 20:30:57 server1 test.sh[13821]: cpu time               (seconds, -t) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: max user processes              (-u) 16384
Jun 16 20:30:57 server1 test.sh[13821]: virtual memory          (kbytes, -v) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: file locks                      (-x) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: soft limits according to ulimit:
Jun 16 20:30:57 server1 test.sh[13821]: core file size          (blocks, -c) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: data seg size           (kbytes, -d) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: scheduling priority             (-e) 0
Jun 16 20:30:57 server1 test.sh[13821]: file size               (blocks, -f) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: pending signals                 (-i) 30554
Jun 16 20:30:57 server1 test.sh[13821]: max locked memory       (kbytes, -l) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: max memory size         (kbytes, -m) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: open files                      (-n) 1024
Jun 16 20:30:57 server1 test.sh[13821]: pipe size            (512 bytes, -p) 8
Jun 16 20:30:57 server1 test.sh[13821]: POSIX message queues     (bytes, -q) 819200
Jun 16 20:30:57 server1 test.sh[13821]: real-time priority              (-r) 0
Jun 16 20:30:57 server1 test.sh[13821]: stack size              (kbytes, -s) 10240
Jun 16 20:30:57 server1 test.sh[13821]: cpu time               (seconds, -t) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: max user processes              (-u) 2047
Jun 16 20:30:57 server1 test.sh[13821]: virtual memory          (kbytes, -v) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: file locks                      (-x) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: actual limits as per /proc:
Jun 16 20:30:57 server1 test.sh[13821]: Limit                     Soft Limit           Hard Limit           Units
Jun 16 20:30:57 server1 test.sh[13821]: Max cpu time              unlimited            unlimited            seconds
Jun 16 20:30:57 server1 test.sh[13821]: Max file size             unlimited            unlimited            bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max data size             unlimited            unlimited            bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max stack size            10485760             33554432             bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max core file size        unlimited            unlimited            bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max resident set          unlimited            unlimited            bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max processes             2047                 16384                processes
Jun 16 20:30:57 server1 test.sh[13821]: Max open files            1024                 65536                files
Jun 16 20:30:57 server1 test.sh[13821]: Max locked memory         unlimited            unlimited            bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max address space         unlimited            unlimited            bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max file locks            unlimited            unlimited            locks
Jun 16 20:30:57 server1 test.sh[13821]: Max pending signals       30554                30554                signals
Jun 16 20:30:57 server1 test.sh[13821]: Max msgqueue size         819200               819200               bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max nice priority         0                    0
Jun 16 20:30:57 server1 test.sh[13821]: Max realtime priority     0                    0
Jun 16 20:30:57 server1 test.sh[13821]: Max realtime timeout      unlimited            unlimited            us
Jun 16 20:30:57 server1 systemd[1]: limitstest.service: Succeeded.
Jun 16 20:30:57 server1 test.sh[13821]: -----------------------------------------------------------
Jun 16 20:30:57 server1 systemd[1]: Started A oneshot service to test whether systemd respects PAM limits.

Thankfully this works, and the settings as documented by Oracle are implemented.

Summary

I always found it hard to come up with a working systemd unit file to start a single instance Oracle database. There is a wide range of articles covering SysV init, upstart, and systemd. It certainly can be confusing for a DBA to pick the right one. I have never been a great fan of using /etc/init.d for startup scripts in modern Linux distributions, even though Oracle Linux has a compatibility wrapper to deal with legacy startup scripts. After spending time with systemd I’m now comfortable writing my own start/stop script for RDBMS and listener. Maybe I’ll publish the results in a different post.

I hope you found the article useful for writing your own systemd unit files in Oracle Linux 7 and 8.

SQLcl autotrace is way more than “just” (SQL*Plus) autotrace

As part of the research I did for the 2nd edition of our Exadata book I used session statistics quite heavily. Session statistics can provide additional insights in situations where the wait interface on its own doesn’t get you any further.

On the command line, my tools of choice are either Tanel Poder’s excellent Session Snapper or Adrian Billington’s mystats utility. There are of course others, it just so happened that I’ve been using the previous two quite heavily. If you prefer a graphical front-end such as SQL Developer, you can grab the change in session statistics easily as described in an earlier post.

I’m not sure why it never occurred to me to check if SQL Developer’s best friend, sqlcl, has the same functionality. Well it does, and that’s great news. The other great news is that you can download sqlcl directly from Oracle’s website now.

UPDATE when I first wrote about sqlcl‘s ability to display session statistics it had a problem getting the actual execution plan from the database (it used explain plan for <sql> internally. This is fixed with the current release. I updated the article accordingly.

Downloading SQLcl the new way

The first step to complete is to pull the latest version of sqlcl. Many thanks to Connor McDonald who told me there is a super-easy way to always get the latest version. Just use the following URL:

https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip

Great! I don’t even need to worry about the version:

$ wget https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip
--2021-07-29 17:11:23--  https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip
Resolving download.oracle.com (download.oracle.com)... 104.76.200.85
Connecting to download.oracle.com (download.oracle.com)|104.76.200.85|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 37581054 (36M) [application/zip]
Saving to: ‘sqlcl-latest.zip’

sqlcl-latest.zip           100%[=====================================>]  35.84M  2.43MB/s    in 18s     

2021-07-29 17:11:42 (1.95 MB/s) - ‘sqlcl-latest.zip’ saved [37581054/37581054]

Note that I’m downloading sqlcl-latest.zip, which translates to 21.2.1.195.1252 at the time of writing:

$ cd sqlcl/bin && ./sql -V
SQLcl: Release 21.2.1.0 Production Build: 21.2.1.195.1252

With the latest sqlcl release unzipped it’s time to grab some session statistics.

Session Statistics, please!

To keep matters reasonably simple I’m using a non-partitioned table for my demonstration. As always, my examples use the excellent Swingbench (Order Entry) benchmark schema. Once connected to the database, I can start investigating. By the way I’m running this query on Oracle 19c in a VM on my laptop.

The first step is to enable auto-trace as shown here:

SQL> show version
Oracle SQLDeveloper Command-Line (SQLcl) version: 21.2.1.0 build: 21.2.1.195.1252
SQL> help set autotrace
SET AUTOTRACE
  SET AUTOT[RACE] {OFF|ON|TRACE[ONLY]}
SQL> set autotrace on 
Autotrace Enabled
Shows the execution plan as well as statistics of the statement.

Let’s run a query against the orders table:

SQL> var oid number
SQL> exec :oid := 1

PL/SQL procedure successfully completed.

SQL> select /*+ gather_plan_statistics */ /* sqlcl is cool */ 
  2* order_id, order_date, order_mode from soe.orders o where o.order_id = :oid;

   ORDER_ID                         ORDER_DATE    ORDER_MODE 
___________ __________________________________ _____________ 
          1 07-MAY-11 06.00.00.000000000 AM    direct        

PLAN_TABLE_OUTPUT 
______________________________________________________________________________________________________________ 
SQL_ID  f746puksv24jv, child number 0                                                                          
-------------------------------------                                                                          
select /*+ gather_plan_statistics */ /* sqlcl is cool */  order_id,                                            
order_date, order_mode from soe.orders o where o.order_id = :oid                                               
                                                                                                               
Plan hash value: 4043159647                                                                                    
                                                                                                               
-----------------------------------------------------------------------------------------------------------    
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |    
-----------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |00:00:00.01 |       4 |      3 |    
|   1 |  TABLE ACCESS BY INDEX ROWID| ORDERS   |      1 |      1 |      1 |00:00:00.01 |       4 |      3 |    
|*  2 |   INDEX UNIQUE SCAN         | ORDER_PK |      1 |      1 |      1 |00:00:00.01 |       3 |      2 |    
-----------------------------------------------------------------------------------------------------------    
                                                                                                               
Predicate Information (identified by operation id):                                                            
---------------------------------------------------                                                            
                                                                                                               
   2 - access("O"."ORDER_ID"=:OID)                                                                             
                                                                                                               

Statistics
-----------------------------------------------------------
               1  CPU used by this session
               1  CPU used when call started
               1  DB time
              44  Requests to/from client
              44  SQL*Net roundtrips to/from client
               3  buffer is not pinned count
            1299  bytes received via SQL*Net from client
           84726  bytes sent via SQL*Net to client
               4  calls to get snapshot scn: kcmgss
               4  calls to kcmgcs
           24576  cell physical IO interconnect bytes
               4  consistent gets
               4  consistent gets examination
               1  consistent gets examination (fastpath)
               4  consistent gets from cache
               1  enqueue releases
               1  enqueue requests
               4  execute count
             195  file io wait time
               3  free buffer requested
               1  index fetch by key
           32768  logical read bytes from cache
              56  non-idle wait count
               4  opened cursors cumulative
               1  opened cursors current
               4  parse count (total)
               3  physical read IO requests
           24576  physical read bytes
               3  physical read total IO requests
           24576  physical read total bytes
               3  physical reads
               3  physical reads cache
               1  rows fetched via callback
               4  session logical reads
               3  shared hash latch upgrades - no wait
               1  sorts (memory)
            2010  sorts (rows)
               1  table fetch by rowid
              46  user calls 

This is pretty nifty in my opinion. There’s an execution plan and a whole raft of relevant session statistics.

Unlike in earlier sqlcl releases the execution plan is now fetched from the database. I ran a sql trace to confirm although the presence of a SQL ID and a child number in the above output should indicate this is the “real” plan.

Happy Troubleshooting!

Installation of Oracle Restful Data Services 20.4 without using SYSDBA

It’s really hard to come up with good, concise, and short twitter-compatible blog titles, so let’s try with a few more words. What I’d like to share is how to install Oracle Restful Data Services (ORDS) v20.4 without having to connect to the database as SYSDBA. There are good reasons not to connect as SYSDBA, and I’m glad Oracle made it possible to grant the necessary privileges for an ORDS installation to a regular database account. It’s not a new feature at all, the option has been around for a while but I didn’t have time to write about it yet.

Some background before getting started

The installation of Oracle’s Restful Data Services (ORDS) consists of 2 parts:

  1. Deploying the ORDS distribution to a Docker container or virtual machine
  2. Connecting to and configuring its database

Initially it was necessary to connect to the highly privileged SYSDBA account to complete step 2. Thankfully this isn’t necessary anymore. My post describes how to install and configure ORDS 20.4 against an Oracle 19c (non-container) database.

Deploying ORDS

The first step consists of downloading ORDS from Oracle’s website. I am planning on deploying it in one of my Oracle Linux 8 vagrant boxes. There is more to the deployment of ORDS than configuration, which I’m leaving to a later post. More specifically I’ll not concern myself integrating ORDS into a Docker container or even Tomcat 9.x to keep the post simple.

The Ansible playbook I use creates an Oracle account out of habit. It also creates the location I want it to use – /opt/ords – and changes the directory’s ownership to oracle:oinstall. Finally, it unzips ORDS.

Configuring ORDS

ORDS stores its configuration in a directory of your choice. Since I’m lazy I use /opt/ords/config for this purpose. Once the directory is created on the file system you tell ORDS where to find its configuration:

[oracle@ords ords]$ java -jar /opt/ords/ords.war configdir /opt/ords/config
2021-04-21T18:48:23.156Z INFO        Set config.dir to /opt/ords/config in: /opt/ords/ords.war

Once this step is complete it’s time to configure ORDS and its database connection. This step is #2 in the above list and referred to as “installation” in the documentation.

Creating the less-privileged user in the database

As per the Install Guide you need to run a script to grant a non-privileged user the rights to configure ORDS. The script doesn’t create the user so you have to ensure it exists. The user didn’t seem to require any elevated privileges. I went creative and created the installation account:

SQL> create user ordsinstall identified by values '...';

User created.

SQL> grant create session to ordsinstall;

Grant succeeded.

With the user created I could start the minimum privilege script:

SQL> @installer/ords_installer_privileges ordsinstall

The script takes one argument: the account you intend to use for the installation (ordsinstall).

Installing ORDS in the database

The final step is to install ORDS in the database. This can be done in many ways. Trying to keep it simple I went with the interactive installation.

Have a look at the screen output, it should be self-explanatory for the most part. When prompted for the administrator username you provide the account just created (ordsinstall in my case). Since I wanted to try SQL*Developer Web, I chose that option. Your mileage may vary.

[oracle@ords ords]$ java -jar ords.war install advanced
Specify the database connection type to use.
Enter number for [1] Basic  [2] TNS  [3] Custom URL [1]:1
Enter the name of the database server [localhost]:server3
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:1
Enter the database service name:ORCL
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Requires to login with administrator privileges to verify Oracle REST Data Services schema.

Enter the administrator username:ordsinstall
Enter the database password for ordsinstall:
Confirm password:
Connecting to database user: ordsinstall url: jdbc:oracle:thin:@//server3:1521/ORCL

Retrieving information.
Enter the default tablespace for ORDS_METADATA [SYSAUX]:
Enter the temporary tablespace for ORDS_METADATA [TEMP]:
Enter the default tablespace for ORDS_PUBLIC_USER [SYSAUX]:
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Enter a number to select a feature to enable:
   [1] SQL Developer Web  (Enables all features)
   [2] REST Enabled SQL
   [3] Database API
   [4] REST Enabled SQL and Database API
   [5] None
Choose [1]:1
2021-04-21T19:38:18.012Z INFO        reloaded pools: []
Installing Oracle REST Data Services version 20.4.3.r0501904
... Log file written to /home/oracle/ords_install_core_2021-04-21_193818_00414.log
... Verified database prerequisites
... Created Oracle REST Data Services proxy user
Warning: Nashorn engine is planned to be removed from a future JDK release
... Created Oracle REST Data Services schema
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /home/oracle/ords_install_datamodel_2021-04-21_193842_00226.log
... Log file written to /home/oracle/ords_install_apex_2021-04-21_193846_00491.log
Completed installation for Oracle REST Data Services version 20.4.3.r0501904. Elapsed time: 00:00:32.177 

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2
[oracle@ords ords]$

That’s it! ORDS has been configured in the database, and I didn’t have to connect as SYSDBA. I think that’s a big step ahead, and I have meant to write about this topic for a while.

Have fun!

Connecting to a database using SQLcl, a wallet and the thin driver

I have previously written about the use of SQLcl and using an Oracle wallet (aka secure external password store). In my previous article I used the JDBC oci driver. This is perfectly fine, but as it’s based on Oracle’s Call Interface, it requires client libraries to be present. This time I wanted to use the thin driver. A cursory search didn’t reveal any recent information about the topic so I decided to write this short post.

Creating the Wallet

The wallet is created exactly the same way as described in an earlier article of mine. Scroll down to “Preparing the External Password Store” and have a look at the steps required. You probably don’t have a client installation on the sqlcl host, so you need to prepare the wallet on a machine with one present. When providing input to mkstore and when creating tnsnames.ora it still seems to be best to ensure all identifiers are in lower case. Even if you db_name is in upper case like mine (“ORCL”).

Once the wallet is created and tested, transfer it to the sqlcl-host in a secure manner.

Deploying SQLcl

SQLcl – among other things – allows you to connect to an Oracle database without a client installation. This is quite useful in many cases. I downloaded SQLcl 21c (build 21.1.0.104.1544) from Oracle’s website and deployed it in ~/sql.

Testing the Wallet

I dropped the wallet and associated configuration files created earlier into ~/tns on my sqlcl-host. There is no need to set any environment variables at all. For reference, the following files were present on this vagrant box:

[vagrant@ords ~]$ ls -l ~/tns
total 20
-rw-------. 1 vagrant vagrant 581 Apr 20 19:29 cwallet.sso
-rw-------. 1 vagrant vagrant   0 Apr 20 19:28 cwallet.sso.lck
-rw-------. 1 vagrant vagrant 536 Apr 20 19:29 ewallet.p12
-rw-------. 1 vagrant vagrant   0 Apr 20 19:28 ewallet.p12.lck
-rw-r--r--. 1 vagrant vagrant  89 Apr 20 19:30 ojdbc.properties
-rw-r--r--. 1 vagrant vagrant 120 Apr 20 19:30 tnsnames.ora
[vagrant@ords ~]$  

With the files in place you connect to the database as follows:

[vagrant@ords bin]$ ./sql /@jdbc:oracle:thin:/@orcl?TNS_ADMIN=/home/vagrant/tns

SQLcl: Release 21.1 Production on Tue Apr 20 20:17:12 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Tue Apr 20 2021 20:17:15 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0


SQL> select host_name from v$instance;

HOST_NAME 
-------------
server3      

SQL>   

Note the leading forward slashes in the connection string, they are absolutely mandatory. The beauty of EZConnect Plus syntax is that I don’t need to specify TNS_ADMIN as an environment variable anymore. EZConnect Plus can do many more things, feel free to browse the white paper I linked to.

Have fun!

New initialisation parameters for Transparent Data Encryption beginning in 19c

This blog post is a short summary of my attempt to configure Transparent Data Encryption (TDE) in a single-instance Oracle 19c Container Database (CDB). Reading the documentation I noticed that the way I used to configure TDE changed, there are two new initialisation parameters to be used since SQLNET.ENCRYPTION_WALLET_LOCATION has been deprecated. They are:

  • wallet_root
  • tde_configuration

This article assumes you have prior knowledge of TDE, if not I suggest you head over to the Advanced Security Guide for Oracle 19c. I won’t be so bold as to make the claim my configuration is perfect or even 100% correct; it does seem to work though. Please make sure that you get sign-off from your security team once you completed your TDE configuration.

If you are interested in Transparent Data Encryption please ensure your database is appropriately licensed to use the feature!

Reference

I used the following chapters in the Oracle Advanced Security Guide as reference:

  • Chapter 3: Configuring Transparent Data Encryption
  • Chapter 5: Managing Keystores and TDE Master Encryption Keys in United Mode

Current Environment

Key properties of the environment I’m creating in the lab:

  • I’ll use a software keystore
  • Oracle Restart 19c Enterprise Edition
  • Separation of duties in place in an Oracle Restart configuration
  • I use a Container Database with a single PDB (pdb1). The database has just been created by dbca
  • Configuration of “united mode” where all PDBs share the same keystore with the CDB$ROOT
  • I would like to use an auto-login keystore, trading security for convenience

I would have liked to use a local auto login keystore, however I’m planning on writing another article discussing Data Guard, and I don’t think a local auto-login would have worked as – per the docs – you can only open it on the node it was created.

In united mode, you create the keystore and TDE master encryption key for CDB and PDBs that reside in the same keystore. This might not be a good choice for you, make sure your implementation is consistent with your security team’s requirements.

Configure location and keystore type

This step is significantly different from previous releases, and the main new thing in 19c from what I understand.

[oracle@dgnode1 ~]$ mkdir /u01/app/oracle/admin/SITEA/wallets || echo "the directory exists, check your configuration"

Connect to CDB$ROOT and set wallet_location and tde_configuration

[oracle@dgnode1 ~]$ sq

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 16 09:14:05 2021
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> show parameter wallet_root

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
wallet_root                          string

SQL> alter system set wallet_root='/u01/app/oracle/admin/SITEA/wallets' scope=spfile;

System altered.

Unfortunately it is necessary to bounce the instance as wallet_root is a static parameter. You have to set wallet_root before you can change tde_configuration. So it’s time to bounce the database now. After it’s up, the finishing touches can be applied.

SQL> alter system set tde_configuration="keystore_configuration=file" scope=both;

System altered.

SQL> select name, value from v$parameter
  2  where name in ('wallet_root', 'tde_configuration');

NAME                 VALUE
-------------------- ------------------------------------------------------
wallet_root          /u01/app/oracle/admin/SITEA/wallets
tde_configuration    keystore_configuration=file

To avoid bouncing the instance you could have set the parameters via dbca when creating the database.

Create the software keystore

I was surprised when I noticed that Oracle appended “/tde” to the location specified by wallet_root. According to the documentation this is correct.

SQL> select wrl_parameter, status, wallet_type, keystore_mode, con_id from v$encryption_wallet
  2  /

WRL_PARAMETER                                      STATUS                         WALLET_TYPE          KEYSTORE     CON_ID
-------------------------------------------------- ------------------------------ -------------------- -------- ----------
/u01/app/oracle/admin/SITEA/wallets/tde/           NOT_AVAILABLE                  UNKNOWN              NONE              1
                                                   NOT_AVAILABLE                  UNKNOWN              UNITED            2
                                                   NOT_AVAILABLE                  UNKNOWN              UNITED            3

The value of NONE in KEYSTORE_MODE for CON_ID 1 is expected, as is the absence of wallet locations for the PDBs. CDB1 (the root) owns the keystore for all PDBs.

Let’s create a local auto-login software keystore. This might not be the right keystore type for you, make sure to read the documentation about keystore types and their pros and cons.

Create the necessary password-protected keystore

Before creating the auto login keystore, a password protected keystore has to be created. This is what I did:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show parameter wallet_root

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------------
wallet_root                          string      /u01/app/oracle/admin/SITEA/wallets

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

SQL> set verify off

SQL> administer key management create keystore identified by &password;

keystore altered.

SQL> select con_id, wrl_parameter, status, wallet_type, keystore_mode from v$encryption_wallet;

    CON_ID WRL_PARAMETER                                      STATUS                         WALLET_TYPE          KEYSTORE
---------- -------------------------------------------------- ------------------------------ -------------------- --------
         1 /u01/app/oracle/admin/SITEA/wallets/tde/           CLOSED                         UNKNOWN              NONE
         2                                                    CLOSED                         UNKNOWN              UNITED
         3                                                    CLOSED                         UNKNOWN              UNITED

The keystore is created, but it’s closed. Here I had to make a choice whether to keep the keystore password protected or transforming it to an auto login keystore. The higher degree of security in the first case is offset by the necessity to enter the keystore password when starting the database. In my lab environment I wanted to be able to start the database without manual intervention, so an auto login password it is. As you read earlier I would have preferred a local auto login keystore but since I will create a physical standby for the database. The Advanced Security Guide doesn’t provide specifics about the keystore type to be used in Data Guard.

Deciding between an auto login keystore and a password protected keystore isn’t a decision for the DBA to make. This is one for the security team!

Convert to auto-login keystore

Straight forward, but the keystore location cannot be ommitted this time. The docs instruct us to use wrl_location as shown in v$encryption_wallet. And yes, please use $WALLET_ROOT/tde for this.

SQL> administer key management create auto_login keystore
  2  from keystore '/u01/app/oracle/admin/SITEA/wallets/tde'
  3  identified by &password;

keystore altered.

SQL> select con_id, wrl_parameter, status, wallet_type, keystore_mode from v$encryption_wallet;

    CON_ID WRL_PARAMETER                                      STATUS                         WALLET_TYPE          KEYSTORE
---------- -------------------------------------------------- ------------------------------ -------------------- --------
         1 /u01/app/oracle/admin/SITEA/wallets/tde/           OPEN_NO_MASTER_KEY             AUTOLOGIN            NONE
         2                                                    OPEN_NO_MASTER_KEY             AUTOLOGIN            UNITED
         3                                                    OPEN_NO_MASTER_KEY             AUTOLOGIN            UNITED

This command created the following files:

SQL> !ls -laR /u01/app/oracle/admin/SITEA/wallets
/u01/app/oracle/admin/SITEA/wallets:
total 0
drwxr-xr-x. 3 oracle oinstall 17 Feb 17 13:16 .
drwxr-x---. 7 oracle oinstall 79 Feb 17 13:12 ..
drwxr-x---. 2 oracle asmadmin 44 Feb 17 13:16 tde

/u01/app/oracle/admin/SITEA/wallets/tde:
total 8
drwxr-x---. 2 oracle asmadmin   44 Feb 17 13:16 .
drwxr-xr-x. 3 oracle oinstall   17 Feb 17 13:16 ..
-rw-------. 1 oracle asmadmin 2600 Feb 17 13:16 cwallet.sso
-rw-------. 1 oracle asmadmin 2555 Feb 17 13:16 ewallet.p12

Since this is an auto-login password store it is not necessary to open it. A master key is missing though, it needs to be created.

Create the TDE master encryption key

Important: before running this command ensure all the PDBs in your CDB are open read-write!

SQL> administer key management set key force keystore identified by &password with backup container=all;

keystore altered.

It was necessary to use the “force keystore” option due to this error:

SQL> administer key management set key identified by &password with backup container = all;
administer key management set key identified by ... with backup
*
ERROR at line 1:
ORA-28417: password-based keystore is not open

When clearly it was open! The keystore status column read OPEN_NO_MASTER_KEY, see above.

It has also been necessary to specify container = all or otherwise the PDB wouldn’t have been assigned the key. This was the output of my first attempt, before I specified container=all:

SQL> administer key management set key force keystore identified by &password with backup;

keystore altered.

SQL> select con_id, wrl_parameter, status, wallet_type, keystore_mode from v$encryption_wallet;

    CON_ID WRL_PARAMETER                                 STATUS                         WALLET_TYPE          KEYSTORE
---------- --------------------------------------------- ------------------------------ -------------------- --------
         1 /u01/app/oracle/admin/SITEA/wallets/tde/      OPEN                           LOCAL_AUTOLOGIN      NONE
         2                                               OPEN                           LOCAL_AUTOLOGIN      UNITED
         3                                               OPEN_NO_MASTER_KEY             LOCAL_AUTOLOGIN      UNITED 

Note how CON_ID 3 (my PDB) doesn’t have a master key. According to the documentation it shouldn’t be necessary to specify container = all when using unified mode, but I guess it is.

Validating the configuration

The documentation provides a query to check if the master key is enabled:

SQL> select con_id, masterkey_activated from v$database_key_info;

    CON_ID MAS
---------- ---
         1 YES
         2 NO
         3 YES

So both my CDB$ROOT and PDB1 have the master key activated. I found the query against v$encryption_wallet useful, too.

SQL> select con_id, wrl_parameter, status, wallet_type, keystore_mode from v$encryption_wallet;

    CON_ID WRL_PARAMETER                                      STATUS                         WALLET_TYPE          KEYSTORE
---------- -------------------------------------------------- ------------------------------ -------------------- --------
         1 /u01/app/oracle/admin/SITEA/wallets/tde/           OPEN                           AUTOLOGIN            NONE
         2                                                    OPEN                           AUTOLOGIN            UNITED
         3                                                    OPEN                           AUTOLOGIN            UNITED

The autologin wallet is now open and ready for use.

Encrypt data

Connect to the PDB and create an encrypted tablespace using the default encryption algorithm. Be advised you can specify which encryption algorithm to use in the create tablespace command. I’m just mucking around with the feature and thus am perfectly happy with the default.

SQL> alter session set container = pdb1;

Session altered.

SQL> create tablespace soe_tbs datafile size 2g autoextend on next 1g maxsize 5g 
  2  encryption encrypt;

Tablespace created.

SQL> select ets.ts#, ets.encryptionalg, ets.key_version, ets.status, ts.name
  2  from v$encrypted_tablespaces ets join v$tablespace ts on (ets.ts# = ts.ts#);

       TS# ENCRYPT KEY_VERSION STATUS     NAME
---------- ------- ----------- ---------- ------------------------------
         6 AES128            0 NORMAL     SOE_TBS

Now let’s load some data… As always, I use Swingbench for this purpose. I’m pointing oewizard to the existing (TDE) tablespace; that’s an important aspect!

martin@client:~/java/swingbench/bin$ 
>./oewizard -cl -create ... -scale 1 -tc 4 -ts SOE_TBS -u soe  

While the data creation job is running you can see the number of blocks encrypted increase on my encrypted tablespace:

SQL> select ets.ts#, ets.encryptionalg, ets.key_version, ets.status, 
  2     ets.blocks_encrypted, ets.blocks_decrypted, ts.name
  3    from v$encrypted_tablespaces ets 
  4     join v$tablespace ts on (ets.ts# = ts.ts#);

       TS# ENCRYPT KEY_VERSION STATUS     BLOCKS_ENCRYPTED BLOCKS_DECRYPTED NAME
---------- ------- ----------- ---------- ---------------- ---------------- ------------------------------
         6 AES128            0 NORMAL                28587                0 SOE_TBS

Similarly, you can see the number of blocks decrypted increase when you query data from the SOE schema.

13:26:06 SQL> select ets.ts#, ets.encryptionalg, ets.key_version, 
  2     ets.status, ets.blocks_encrypted, ets.blocks_decrypted, ts.name
  3      from v$encrypted_tablespaces ets 
  4      join v$tablespace ts on (ets.ts# = ts.ts#);

       TS# ENCRYPT KEY_VERSION STATUS     BLOCKS_ENCRYPTED BLOCKS_DECRYPTED NAME
---------- ------- ----------- ---------- ---------------- ---------------- ------------------------------
         6 AES128            0 NORMAL                69901                0 SOE_TBS

13:26:07 SQL> /

       TS# ENCRYPT KEY_VERSION STATUS     BLOCKS_ENCRYPTED BLOCKS_DECRYPTED NAME
---------- ------- ----------- ---------- ---------------- ---------------- ------------------------------
         6 AES128            0 NORMAL               155725           191459 SOE_TBS

13:26:57 SQL> 

You can also notice blocks decrypted. As I haven’t run charbench yet I assume this is caused by the table scans when building the indexes.

Summary

This concludes the article on implementing TDE for an Oracle single-instance database. So far a lot of complexities have been omitted: no RAC, no Data Guard, no additional features requiring wallets. I am planning on extending this article to Data Guard setups, and eventually discuss a RAC setup.

Create a custom service in single instance Oracle 19c

This post demonstrates how to create a custom service in a single instance Oracle 19c database. As per the Oracle 19c PL/SQL Packages and Types guide, this is the only Oracle database deployment option where you are allowed to use this technique. Anything to do with high availability rules this approach out straight away. The same applies for a database managed by Clusterware (both Oracle Real Application Clusters and Oracle Restart) and Global Data Services (GDS).

Furthermore, the service_name parameter to DBMS_SERVICE.CREATE_SERVICE() is deprecated, I wouldn’t recommend running the code in this blog post on anything newer than Oracle 19c.

If you have a 19c single-instance database where the use of DBMS_SERVICE.CREATE_SERVICE is permitted, you might find this post useful. As always, my example deals with Swingbench. I created a CDB containing a PDB named swingbench1. In addition to the default service I would like to start a custom service, swingbench1_svc. This little SQL*Plus script should do the trick.

To add a little bit of security I added a short prompt to remind you that you can’t use DBMS_SERVICE.START_SERVICE in combination with RAC/Oracle Restart/Global Data Service. It requires you to be logged in a SYS, but that’s easy enough to change if you don’t want to do so.

whenever sqlerror exit

set verify off

define v_service=swingbench1_svc

-- let's try to prevent problems with RAC/Oracle Restart and GDS before we start

prompt This script cannot be run if your database is managed by Clusterware (RAC/Oracle Restart)
prompt or Global Data Services (GDS). 
prompt
accept ok_to_run prompt 'Is this environment a single-instance database [y/n] '

BEGIN
    IF upper('&ok_to_run') != 'Y' THEN
        raise_application_error(-20001, 'you must not use this script with RAC/Oracle Restart/GDS');
    END IF;
END;
/


DECLARE
    v_parameters dbms_service.svc_parameter_array;
    service_exists EXCEPTION;
    service_running EXCEPTION;
    PRAGMA exception_init ( service_exists, -44303 );
    PRAGMA exception_init ( service_running, -44305);
    v_version VARCHAR2(100);
    v_compatibility VARCHAR2(100);
    
BEGIN
    -- must be connected as SYS to a non-CDB or PDB
    IF
        sys_context('userenv', 'cdb_name') IS NOT NULL
        AND sys_context('userenv', 'con_id') <= 2
    THEN
        raise_application_error(-20002, 'you must be connected to a PDB');
    END IF;

    IF sys_context('userenv', 'session_user') != 'SYS' THEN
        raise_application_error(-20002, 'you must by logged in as SYS to run this code');
    END IF;

    -- make sure this is 19c
    dbms_utility.db_version(v_version, v_compatibility);
    if v_version != '19.0.0.0.0' then
        raise_application_error(-20003, 'you must run this script in a 19c database');
    end if;    

    -- create the service, there is no need to provide any parameters
    -- for a single instance database. Ignore the error should the service exist
    BEGIN
        dbms_service.create_service(
            service_name => '&v_service', 
            network_name => '&v_service',
            parameter_array => v_parameters);
    EXCEPTION
        WHEN service_exists THEN
            NULL;
        WHEN others THEN
            raise;
    END;
            
    -- and start it. Ignore an error in case it's running
    BEGIN
        dbms_service.start_service('&v_service');
    EXCEPTION
        WHEN service_running THEN
            NULL;
        WHEN others THEN
            raise;
    END;
END;
/

-- make sure the service starts when the database opens
CREATE OR REPLACE TRIGGER SYS.SERVICES_TRIG
AFTER STARTUP ON DATABASE 
BEGIN
    IF sys_context('userenv','database_role') = 'PRIMARY' THEN
        dbms_service.start_service('&v_service');
    END IF;
END;
/

Once the code is deployed, the service will start with the PDB:

SQL> select name, open_mode, con_id from v$pdbs;

NAME                           OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           2
SWINGBENCH1                    MOUNTED             4

SQL> select con_id, name from v$active_services order by 1,2;

    CON_ID NAME
---------- ------------------------------
         1 CDB
         1 CDBXDB
         1 SYS$BACKGROUND
         1 SYS$USERS
         4 swingbench1

SQL> alter pluggable database SWINGBENCH1 open;

Pluggable database altered.

SQL> select con_id, name from v$active_services order by 1,2;

    CON_ID NAME
---------- ------------------------------
         1 CDB
         1 CDBXDB
         1 SYS$BACKGROUND
         1 SYS$USERS
         4 swingbench1
         4 swingbench1_svc

6 rows selected.

That should do it: if I can’t rely on Clusterware or Global Data Services to manage services for my database, I can use this approach to create an “application” or custom service in my single instance database. And it works:

SQL> conn martin@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server1.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = swingbench1_svc)))
Enter password: 
Connected.
SQL> select sys_context('userenv','service_name') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')
----------------------------------------------------------
swingbench1_svc

I like it when a plan comes together.

Swingbench: creating benchmark schemas with strong passwords

This post describes my experience creating the SOE user account in an Oracle 21 database provided by Oracle’s Database Cloud Service (DBCS). The same principle should apply for earlier releases as well, at the end of the day it boils down to the use of Oracle’s password verify functions. To avoid any ambiguity: the use of password verification functions isn’t a cloud-only feature ;)

Any security conscious administrator should enforce strong passwords and good password hygiene. There is a lot to be said about the topic, too much even for me :) Please refer to the Database Security Guide for your release for an introduction to the topic in the context of the Oracle database and take it from there.

Password Verify Functions

Quite recently I wanted to install Swingbench in my Oracle 21 system, hosted in Oracle’s Cloud Infrastructure (OCI). Unsurprisingly the database enforces strong passwords. Connected to my PDB (“demo_pdb”) I noticed the following in dba_profiles:

SQL> select profile, resource_name, limit from dba_profiles 
  2  where resource_name = 'PASSWORD_VERIFY_FUNCTION';

PROFILE 		       RESOURCE_NAME			LIMIT
------------------------------ -------------------------------- --------------------------------------------------
DEFAULT 		       PASSWORD_VERIFY_FUNCTION 	ORA12C_STRONG_VERIFY_FUNCTION
ORA_CIS_PROFILE 	       PASSWORD_VERIFY_FUNCTION 	ORA12C_VERIFY_FUNCTION
ORA_STIG_PROFILE	       PASSWORD_VERIFY_FUNCTION 	ORA12C_STIG_VERIFY_FUNCTION

SQL> 

A weak password unsurprisingly will not work in these circumstances:

SQL> create user martin identified by superWeakPassword;
create user martin identified by superWeakPassword
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20000: password must contain 2 or more digits

In other words, you need to provide a strong password for Swingbench’s schema creation wizards as well.

Calling OEWizard

I commonly use the command line to create Swingbench’s benchmark schemas. This time around I wanted to create the Swingbench Order Entry schema. To adhere to the password-complexity rule I have to provide Oracle with a password containing special characters. As per the create user SQL command, passwords containing those special characters need to be enclosed in double-quotes. Now the trick is to read man(1) bash correctly, namely the section on quoting.

The keep it short you need to wrap the actual password into a single quote/a double quote combination, like so:

./oewizard -allindexes ... -u soe -p '"superSafeTempPassword"' ...

This way the passwords will make it all the way to oewizard, allowing it to create the user successfully.

Change those passwords immediately

Unfortunately there is no other way than providing passwords to oewizard on the command line. They will be visible to other users on the system, so make sure to change them immediately as soon as the wizard finished with the schema creation.

Password complexity rules

By the way, the Oracle-provided password verification functions and their password complexity rules are explained in chapter 3 of the Database Security Guide. If you get ORA-28003/ORA-20000 in OEWizard or later in SQL*Plus when changing the password your new password doesn’t adhere to the complexity rules.

Happy benchmarking!