Category Archives: Linux

Installing Podman on Oracle Linux 8

Rather than having to use a search engine to read up on how to install podman on Oracle Linux 8, I thought I’d write the procedure down. Hopefully this saves you (and me) a few minutes next time the task comes up. I probably should write a short Ansible Playbook at some point, but that’s for another post.

The easiest way to install podman on Oracle Linux 8 is to install the entire podman module. If you haven’t used modules and application streams yet in Oracle Linux 8 yet, you can find more details in the Oracle Linux 8 documentation. Quoting from chapter 5, section “Use DNF Modules and Application Streams” in Managing Software in Oracle Linux Guide:

DNF introduces the concepts of modules, streams and profiles to allow for the management of different versions of software applications within a single operating system release. Modules can be used to group together many packages that comprise a single application and its dependencies.

This sounds exactly like what I want.

Installing the Podman Module

So what do the concepts of stream and module mean in practice? Podman is shipped as a module in the Application Stream (AppStream):

[root@ol8podman ~]# dnf module list container-tools:ol8
Last metadata expiration check: 0:12:47 ago on Fri 15 Jul 2022 10:13:15 BST.
Oracle Linux 8 Application Stream (x86_64)
Name            Stream  Profiles Summary                                                                 
container-tools ol8 [d] common [ Most recent (rolling) versions of podman, buildah, skopeo, runc, conmon,
                        d]        runc, conmon, CRIU, Udica, etc as well as dependencies such as containe
                                 r-selinux built and tested together, and updated as frequently as every 
                                 12 weeks.

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled

Rather than installing the podman RPM on its own and figure out which other packages I need I went with the installation of the entire module:

[root@ol8podman ~]# dnf module install container-tools:ol8 
Last metadata expiration check: 0:15:25 ago on Fri 15 Jul 2022 10:13:15 BST.
Dependencies resolved.
=========================================================================================================
 Package                      Arch   Version                                     Repository         Size
=========================================================================================================
Installing group/module packages:
 buildah                      x86_64 1:1.24.2-4.module+el8.6.0+20665+a3b29bef    ol8_appstream     8.1 M
 cockpit-podman               noarch 43-1.module+el8.6.0+20665+a3b29bef          ol8_appstream     493 k
 conmon                       x86_64 2:2.1.0-1.module+el8.6.0+20665+a3b29bef     ol8_appstream      55 k
 container-selinux            noarch 2:2.179.1-1.module+el8.6.0+20665+a3b29bef   ol8_appstream      58 k
 containernetworking-plugins  x86_64 1:1.0.1-2.module+el8.6.0+20665+a3b29bef     ol8_appstream      18 M
 containers-common            x86_64 2:1-27.0.1.module+el8.6.0+20665+a3b29bef    ol8_appstream      67 k
 criu                         x86_64 3.15-3.module+el8.6.0+20665+a3b29bef        ol8_appstream     518 k
 crun                         x86_64 1.4.4-1.module+el8.6.0+20665+a3b29bef       ol8_appstream     209 k
 fuse-overlayfs               x86_64 1.8.2-1.module+el8.6.0+20665+a3b29bef       ol8_appstream      73 k
 libslirp                     x86_64 4.4.0-1.module+el8.6.0+20665+a3b29bef       ol8_appstream      70 k
 podman                       x86_64 2:4.0.2-6.module+el8.6.0+20665+a3b29bef     ol8_appstream      13 M
 python3-podman               noarch 4.0.0-1.module+el8.6.0+20665+a3b29bef       ol8_appstream     149 k
 runc                         x86_64 1:1.0.3-2.module+el8.6.0+20665+a3b29bef     ol8_appstream     3.0 M
 skopeo                       x86_64 2:1.6.1-2.module+el8.6.0+20665+a3b29bef     ol8_appstream     6.7 M
 slirp4netns                  x86_64 1.1.8-2.module+el8.6.0+20665+a3b29bef       ol8_appstream      51 k
 udica                        noarch 0.2.6-3.module+el8.6.0+20665+a3b29bef       ol8_appstream      49 k
Installing dependencies:
 checkpolicy                  x86_64 2.9-1.el8                                   ol8_baseos_latest 346 k
 cockpit-bridge               x86_64 264.1-1.0.1.el8                             ol8_baseos_latest 535 k
 fuse-common                  x86_64 3.3.0-15.0.2.el8                            ol8_baseos_latest  22 k
 fuse3                        x86_64 3.3.0-15.0.2.el8                            ol8_baseos_latest  55 k
 fuse3-libs                   x86_64 3.3.0-15.0.2.el8                            ol8_baseos_latest  95 k
 glib-networking              x86_64 2.56.1-1.1.el8                              ol8_baseos_latest 155 k
 gsettings-desktop-schemas    x86_64 3.32.0-6.el8                                ol8_baseos_latest 633 k
 json-glib                    x86_64 1.4.4-1.el8                                 ol8_baseos_latest 144 k
 libmodman                    x86_64 2.0.1-17.el8                                ol8_baseos_latest  36 k
 libnet                       x86_64 1.1.6-15.el8                                ol8_appstream      67 k
 libproxy                     x86_64 0.4.15-5.2.el8                              ol8_baseos_latest  75 k
 podman-catatonit             x86_64 2:4.0.2-6.module+el8.6.0+20665+a3b29bef     ol8_appstream     354 k
 policycoreutils-python-utils noarch 2.9-19.0.1.el8                              ol8_baseos_latest 253 k
 protobuf-c                   x86_64 1.3.0-6.el8                                 ol8_appstream      37 k
 python3-audit                x86_64 3.0.7-2.el8.2                               ol8_baseos_latest  87 k
 python3-chardet              noarch 3.0.4-7.el8                                 ol8_baseos_latest 195 k
 python3-idna                 noarch 2.5-5.el8                                   ol8_baseos_latest  97 k
 python3-libsemanage          x86_64 2.9-8.el8                                   ol8_baseos_latest 128 k
 python3-pip                  noarch 9.0.3-22.el8                                ol8_appstream      20 k
 python3-policycoreutils      noarch 2.9-19.0.1.el8                              ol8_baseos_latest 2.2 M
 python3-pysocks              noarch 1.6.8-3.el8                                 ol8_baseos_latest  34 k
 python3-pytoml               noarch 0.1.14-5.git7dea353.el8                     ol8_appstream      25 k
 python3-pyxdg                noarch 0.25-16.el8                                 ol8_appstream      94 k
 python3-requests             noarch 2.20.0-2.1.el8_1                            ol8_baseos_latest 123 k
 python3-setools              x86_64 4.3.0-3.el8                                 ol8_baseos_latest 624 k
 python3-setuptools           noarch 39.2.0-6.el8                                ol8_baseos_latest 163 k
 python3-urllib3              noarch 1.24.2-5.0.1.el8                            ol8_baseos_latest 177 k
 python36                     x86_64 3.6.8-38.module+el8.5.0+20329+5c5719bc      ol8_appstream      19 k
 shadow-utils-subid           x86_64 2:4.6-16.el8                                ol8_baseos_latest 112 k
 yajl                         x86_64 2.1.0-10.el8                                ol8_appstream      41 k
Installing module profiles:
 container-tools/common                                                                                 
Enabling module streams:
 container-tools                     ol8                                                                
 python36                            3.6                                                                

Transaction Summary
=========================================================================================================
Install  46 Packages

Total download size: 58 M
Installed size: 200 M

...

This is great! The current stable podman release at the time of writing is 4.1.1, so getting 4.0.2 doesn’t look too bad to me :)

Summary

Installing podman on Oracle Linux 8 is quite simple provided you are happy to install the entire podman module. The module provides a very convenient way to install recent releases for podman and its related tools (buildah, skopeo, …). Podman is important enough to get its own User Guide in the Oracle Linux 8 documentation set, the installation instructions I used when putting this post together can be found in chapter 2.

Generating Table DDL in Oracle Database

Generating table DDL is a common requirement. Unfortunately it’s not quite common enough for me to remember the syntax by heart, so this post serves as a reference to myself how to do this. Hopefully it saves you a few minutes, too.

I used Oracle SQLDeveloper Command-Line (SQLcl) version: 22.1.1.0 build: 22.1.1.131.0820 for this post, connecting to an Oracle 19c database running on Linux. The DDL command you are reading about later is not new to version 22.1, that one just so happens to be the most current version at the time of writing.

Here are the links for downloading SQLcl:

SQLcl 22.1 comes with a lot more cool stuff, Jeff Smith wrote about the details here.

DBMS_METADATA

Table DDL (and a lot of other DDL for that matter) can be generated using DBMS_METADATA. This package has been part of the database for quite some time and is documented in the PL/SQL Packages and Types Guide. In cases where SQLcl is unavailable it’s still a more than viable option to combine calls to DBMS_METADATA.set_transform_param() with DBMS_METADATA.get_ddl(). If you are in this position you might find the following sections in the PL/SQL Packages and Types Guide useful:

Tables 107-23 and 107-25 respectively (referenced above) are key to when it comes to understanding the options SQLcl offers. More on that later.

SQLcl is a LOT easier to use than DBMS_METADATA

SQLcl provides a shortcut to using the package: rather than calling DBMS_METADATA.GET_DDL() you can make use of the ddl command instead:

SQL> help ddl
DDL
---

DDL generates the code to reconstruct the object listed.  Use the type option
for materialized views. Use the save options to save the DDL to a file.

DDL [<object_name> [<type>] [SAVE <filename>]]

Let’s use the command with the ORDERS table Swingbench provides:

SQL> show user
USER is "SOE"
SQL> ddl orders

  CREATE TABLE "SOE"."ORDERS" 
   (	"ORDER_ID" NUMBER(12,0) CONSTRAINT "ORDER_ORDER_ID_NN" NOT NULL ENABLE, 
	"ORDER_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE, 
	"ORDER_MODE" VARCHAR2(8), 
	"CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE, 
	"ORDER_STATUS" NUMBER(2,0), 
	"ORDER_TOTAL" NUMBER(8,2), 
	"SALES_REP_ID" NUMBER(6,0), 
	"PROMOTION_ID" NUMBER(6,0), 
	"WAREHOUSE_ID" NUMBER(6,0), 
	"DELIVERY_TYPE" VARCHAR2(15), 
	"COST_OF_DELIVERY" NUMBER(6,0), 
	"WAIT_TILL_ALL_AVAILABLE" VARCHAR2(15), 
	"DELIVERY_ADDRESS_ID" NUMBER(12,0), 
	"CUSTOMER_CLASS" VARCHAR2(30), 
	"CARD_ID" NUMBER(12,0), 
	"INVOICE_ADDRESS_ID" NUMBER(12,0), 
	 CONSTRAINT "ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDER_TOTAL_MIN" CHECK (order_total >= 0) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
	  REFERENCES "SOE"."CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL ENABLE NOVALIDATE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 16 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;
  CREATE UNIQUE INDEX "SOE"."ORDER_PK" ON "SOE"."ORDERS" ("ORDER_ID") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;
ALTER TABLE "SOE"."ORDERS" ADD CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID")
  USING INDEX "SOE"."ORDER_PK"  ENABLE NOVALIDATE;

  CREATE INDEX "SOE"."ORD_CUSTOMER_IX" ON "SOE"."ORDERS" ("CUSTOMER_ID") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;

  CREATE INDEX "SOE"."ORD_ORDER_DATE_IX" ON "SOE"."ORDERS" ("ORDER_DATE") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;

  CREATE INDEX "SOE"."ORD_SALES_REP_IX" ON "SOE"."ORDERS" ("SALES_REP_ID") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;

  CREATE INDEX "SOE"."ORD_WAREHOUSE_IX" ON "SOE"."ORDERS" ("WAREHOUSE_ID", "ORDER_STATUS") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ; 

Not only does the ddl command print the table DDL, it also includes the primary key as well as the table’s indexes! If you just want to recreate the table in a different (pluggable) database/same schema, then you’re done here. Optionally store the DDL in a file and put it into version control.

Customising Output

Sometimes however you might want to customise the output. That’s possible with SQLcl, too. The set ddl command can be used to that effect:

SQL> help set ddl
SET DDL
  SET DDL [[ PRETTY | SQLTERMINATOR | CONSTRAINTS | REF_CONSTRAINTS |
          CONSTRAINTS_AS_ALTER|OID | SIZE_BYTE_KEYWORD | PARTITIONING |
          SEGMENT_ATTRIBUTES | STORAGE | TABLESPACE | SPECIFICATION |
          BODY | FORCE | INSERT | |INHERIT | RESET] {on|off}
         ] | ON | OFF ]

Table 107-23 in the DBMS_METADATA package documentation describe the meaning of each of these. SQLTERMINATOR for example defines whether a SQL terminator such as the semi-colon or slash should be added after each statement. This defaults to FALSE in DBMS_METADATA, and TRUE in SQLcl. The current settings can be viewed using the show ddl command:

SQL> show ddl
STORAGE : ON
INHERIT : ON
EMIT_SCHEMA : ON
SQLTERMINATOR : ON
OID : ON
SPECIFICATION : ON
TABLESPACE : ON
SIZE_BYTE_KEYWORD : ON
PRETTY : ON
REF_CONSTRAINTS : ON
FORCE : ON
PARTITIONING : ON
CONSTRAINTS : ON
INSERT : ON
BODY : ON
CONSTRAINTS_AS_ALTER : ON
SEGMENT_ATTRIBUTES : ON

I don’t need the storage attributes, don’t want SQLcl to emit the schema and neither do I need the segment attributes. Although turning off SEGMENT_ATTRIBUTES disables STORAGE, too, I set this flag explicitly as a note to self.

SQL> set ddl STORAGE off
DDL Option STORAGE off
SQL> set ddl EMIT_SCHEMA off
DDL Option EMIT_SCHEMA off
SQL> set ddl SEGMENT_ATTRIBUTES off
DDL Option SEGMENT_ATTRIBUTES off

With the defaults changed to my use case the output is reduced quite a bit:

SQL> ddl orders

  CREATE TABLE "ORDERS" 
   (	"ORDER_ID" NUMBER(12,0) CONSTRAINT "ORDER_ORDER_ID_NN" NOT NULL ENABLE, 
	"ORDER_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE, 
	"ORDER_MODE" VARCHAR2(8), 
	"CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE, 
	"ORDER_STATUS" NUMBER(2,0), 
	"ORDER_TOTAL" NUMBER(8,2), 
	"SALES_REP_ID" NUMBER(6,0), 
	"PROMOTION_ID" NUMBER(6,0), 
	"WAREHOUSE_ID" NUMBER(6,0), 
	"DELIVERY_TYPE" VARCHAR2(15), 
	"COST_OF_DELIVERY" NUMBER(6,0), 
	"WAIT_TILL_ALL_AVAILABLE" VARCHAR2(15), 
	"DELIVERY_ADDRESS_ID" NUMBER(12,0), 
	"CUSTOMER_CLASS" VARCHAR2(30), 
	"CARD_ID" NUMBER(12,0), 
	"INVOICE_ADDRESS_ID" NUMBER(12,0), 
	 CONSTRAINT "ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDER_TOTAL_MIN" CHECK (order_total >= 0) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
	  REFERENCES "CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL ENABLE NOVALIDATE
   ) ;
  CREATE UNIQUE INDEX "ORDER_PK" ON "ORDERS" ("ORDER_ID") REVERSE 
  ;
ALTER TABLE "ORDERS" ADD CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID")
  USING INDEX "ORDER_PK"  ENABLE NOVALIDATE;

  CREATE INDEX "ORD_CUSTOMER_IX" ON "ORDERS" ("CUSTOMER_ID") REVERSE 
  ;

  CREATE INDEX "ORD_ORDER_DATE_IX" ON "ORDERS" ("ORDER_DATE") REVERSE 
  ;

  CREATE INDEX "ORD_SALES_REP_IX" ON "ORDERS" ("SALES_REP_ID") REVERSE 
  ;

  CREATE INDEX "ORD_WAREHOUSE_IX" ON "ORDERS" ("WAREHOUSE_ID", "ORDER_STATUS") 
  ;
SQL> 

And this is exactly what I wanted ;) Instead of listing all the specifics in the storage clause and segment management I can run this script on a different system with different tablespaces and other settings. The downside is that I’m now responsible for providing useful defaults.

Summary

SQLcl provides as super convenient shortcut to using DBMS_METADATA. There are many use cases for this function, from creating DDL for use with version control systems to data migrations the possibilities are almost endless.

Linking Containers with Podman

Users of the Docker engine might find that their container runtime isn’t featured prominently in Oracle Linux 8. In fact, unless you change the default confifguration a dnf search does not reveal the engine at all. For better or for worse, it appears the industry has been gradually switching from Docker to Podman and its related ecosystem.

Whilst most Docker commands can be translated 1:1 to the Podman world, some differences exist. Instead of highlighting all the changes here please have a look at the Podman User Guide.

Overview

This article explains how to create a network link between 2 containers:

  1. Oracle XE 21c
  2. SQLcl client

These containers are going to be run "rootless", which has a few implications. By default Podman will allocate storage for containers in ~/.local/share/containers/ so please ensure you have sufficient space in your home directory.

The article refers to Gerald Venzl’s Oracle-XE images and you will create another image for SQLcl.

Installation

If you haven’t already installed Podman you can do so by installing the container-tools:ol8 module:

[opc@podman ~]$ $ sudo dnf module install container-tools:ol8
Last metadata expiration check: 0:06:04 ago on Mon 21 Mar 2022 13:19:40 GMT.
Dependencies resolved.
========================================================================================================================
 Package                         Arch      Version                                           Repository            Size
========================================================================================================================
Installing group/module packages:
 buildah                         x86_64    1:1.23.1-2.0.1.module+el8.5.0+20494+0311868c      ol8_appstream        7.9 M
 cockpit-podman                  noarch    39-1.module+el8.5.0+20494+0311868c                ol8_appstream        483 k
 conmon                          x86_64    2:2.0.32-1.module+el8.5.0+20494+0311868c          ol8_appstream         55 k
 container-selinux               noarch    2:2.173.0-1.module+el8.5.0+20494+0311868c         ol8_appstream         57 k
 containernetworking-plugins     x86_64    1.0.1-1.module+el8.5.0+20494+0311868c             ol8_appstream         19 M
 containers-common               noarch    2:1-8.0.1.module+el8.5.0+20494+0311868c           ol8_appstream         62 k
 criu                            x86_64    3.15-3.module+el8.5.0+20416+d687fed7              ol8_appstream        518 k
 crun                            x86_64    1.4.1-1.module+el8.5.0+20494+0311868c             ol8_appstream        205 k
 fuse-overlayfs                  x86_64    1.8-1.module+el8.5.0+20494+0311868c               ol8_appstream         73 k
 libslirp                        x86_64    4.4.0-1.module+el8.5.0+20416+d687fed7             ol8_appstream         70 k
 podman                          x86_64    1:3.4.2-9.0.1.module+el8.5.0+20494+0311868c       ol8_appstream         12 M
 python3-podman                  noarch    3.2.1-1.module+el8.5.0+20494+0311868c             ol8_appstream        148 k
 runc                            x86_64    1.0.3-1.module+el8.5.0+20494+0311868c             ol8_appstream        3.1 M
 skopeo                          x86_64    2:1.5.2-1.0.1.module+el8.5.0+20494+0311868c       ol8_appstream        6.7 M
 slirp4netns                     x86_64    1.1.8-1.module+el8.5.0+20416+d687fed7             ol8_appstream         51 k
 udica                           noarch    0.2.6-1.module+el8.5.0+20494+0311868c             ol8_appstream         48 k
Installing dependencies:
 fuse-common                     x86_64    3.2.1-12.0.3.el8                                  ol8_baseos_latest     22 k
 fuse3                           x86_64    3.2.1-12.0.3.el8                                  ol8_baseos_latest     51 k
 fuse3-libs                      x86_64    3.2.1-12.0.3.el8                                  ol8_baseos_latest     95 k
 libnet                          x86_64    1.1.6-15.el8                                      ol8_appstream         67 k
 podman-catatonit                x86_64    1:3.4.2-9.0.1.module+el8.5.0+20494+0311868c       ol8_appstream        345 k
 policycoreutils-python-utils    noarch    2.9-16.0.1.el8                                    ol8_baseos_latest    252 k
 python3-pytoml                  noarch    0.1.14-5.git7dea353.el8                           ol8_appstream         25 k
 python3-pyxdg                   noarch    0.25-16.el8                                       ol8_appstream         94 k
 yajl                            x86_64    2.1.0-10.el8                                      ol8_appstream         41 k
Installing module profiles:
 container-tools/common                                                                                                
Enabling module streams:
 container-tools                           ol8                                                                         

Transaction Summary
========================================================================================================================
Install  25 Packages

If you like DNS on your container network, install podman-plugins and dnsmasq. This article assumes you do so. The latter of the 2 services needs to be enabled and started:

[opc@podman ~]$ for task in enable start is-active; do sudo systemctl ${task} dnsmasq; done
active

If you see active in the output as in the example dnsmasq is working. If your system is part of a more elaborate setup, the use of dnsmasq is discouraged and you should ask your friendly network admin for advice.

Virtual Network Configuration

This section describes setting up a virtual network. That way you are emulating the way you’d previously have worked with Docker. If I should find the time for it I’ll write a second article and introduce you to Podman’s PODs, an elegant concept similar to Kubernetes that is not available with the Docker engine.

Network creation

Before containers can communicate with one another, they need to be told which network to use. The easiest way to do so is by creating a new, custom network as shown in this example:

[opc@podman ~]$ podman network create oranet
/home/opc/.config/cni/net.d/oranet.conflist
[opc@podman ~]$ podman network ls
NETWORK ID    NAME        VERSION     PLUGINS
2f259bab93aa  podman      0.4.0       bridge,portmap,firewall,tuning
4f4bfc6d2c15  oranet      0.4.0       bridge,portmap,firewall,tuning,dnsname
[opc@podman ~]$ 

As you can see the new network – oranet – has been created and it’s capable of using DNS thanks for the dnsname extension. If you opted not to install podman-plugins and dnsmasq this feature won’t be availble. Testing showed that availability of DNS on the container network made life a lot easier.

Storage Volumes

Containers are transient by nature, things you store in them are ephemeral by design. Since that’s not ideal for databases, a persistence layer should be used instead. The industry’s best known method to do so is by employing (Podman) volumes. Volumes are crated using the podman volume create command, for example:

[opc@podman ~]$ podman volume create oradata
oradata

As it is the case with the Container images, by default alll the volume’s data will reside in ~/.local/share/containers.

Database Secrets

The final step while preparing for running a database in Podman is to create a secret. Secrets are a relatively new feature in Podman and relieve you from having to consider workarounds passing sensitive data to containers. The Oracle XE containers to be used need to be initialised with a DBA password and it is prudent not to pass this in clear text on the command line.

For this example the necessary database password has been created as a secret and stored as oracle-password using podman secret create ...

[opc@podman ~]$ podman secret create oracle-password ~/.passwordFileToBeDeletedAfterUse
0c5d6d9eff16c4d30d36c6133
[opc@podman ~]$ podman secret ls
ID                         NAME             DRIVER      CREATED        UPDATED        
0c5d6d9eff16c4d30d36c6133  oracle-password  file        2 minutes ago  2 minutes ago 

This concludes the necessary preparations.

Let there be Containers

With all the setup completed the next step is to start an Oracle 21c XE instance and build the SQLcl container.

Oracle XE

Using the instructions by Gerald Venzl’s GitHub repository, adapted for this use case, a call to podman run might look like this:

[opc@podman ~]$ podman run --name oracle21xe --secret oracle-password \
-e ORACLE_PASSWORD_FILE=/run/secrets/oracle-password -d \
--net oranet -v oradata:/opt/oracle/oradata \
docker.io/gvenzl/oracle-xe:21-slim
5d94c0c3620f811bbe522273f73cbcb7c5210fecc0f88b0ecacc1f5474c0855a

The necessary flags are as follows:

  • --name assigns a name to the container so you can reference it later
  • --secret passes a named secret to the container, accessible in /run/secrets/oracle-password
  • -d tells the container to run in the background
  • --net defines the network the container should be attached to
  • -v maps the newly created volume to a directory in the container

You can check whether the container is up an running by executing podman ps:

[opc@podman ~]$ podman ps
CONTAINER ID  IMAGE                               COMMAND     CREATED         STATUS             PORTS       NAMES
5d94c0c3620f  docker.io/gvenzl/oracle-xe:21-slim              53 seconds ago  Up 54 seconds ago              oracle21xe

Creating a small SQLcl container:

Creating a container to run sqlcl is really quite straight forward. A suitable Dockerfile is shown here, please ensure you update the ZIPFILE with the current SQLcl release.

FROM docker.io/openjdk:11

RUN useradd --comment "sqlcl owner" --home-dir /home/sqlcl --uid 1000 --create-home --shell $(which bash) sqlcl 

USER sqlcl
WORKDIR /home/sqlcl

ENV ZIPFILE=sqlcl-21.4.1.17.1458.zip

RUN curl -LO "https://download.oracle.com/otn_software/java/sqldeveloper/${ZIPFILE}" && \
        /usr/local/openjdk-11/bin/jar -xf ${ZIPFILE} && \
        rm ${ZIPFILE}

ENTRYPOINT ["bash", "/home/sqlcl/sqlcl/bin/sql", "/nolog"]

You could of course pull the latest sqlcl ZIP from https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip. Using a named release should simplify the non-trivial task of naming ("tagging") your container image.

The image can be build using podman much in the same way Docker images were built:

[opc@podman ~]$ podman build . -t tools/sqlcl:21.4.1.17.1458

As you can see from the ENTRYPOINT the image cannot be sent to the backround (-d) by podman, it needs to be run interactively as you will see in the next section.

Linking Containers

The last step is to start the sqlcl container and connect to the database.

podman run --rm -it --name sqlcl --net oranet localhost/tools/sqlcl:21.4.1.17.1458

Here is an example how this works in my container:

[opc@podman ~]$ podman run --rm -it --name sqlcl --net oranet localhost/tools/sqlcl:21.4.1.17.1458


SQLcl: Release 21.4 Production on Mon Mar 21 13:35:05 2022

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

SQL> connect system@oracle21xe/xepdb1
Password? (**********?) ***************
Connected.
SQL> show con_name
CON_NAME 
------------------------------
XEPDB1

The connection string consists of a username (system) and the container name assigned as part of the call to podman run ... --name. Thanks to the dnsname extension and linking the container to the oranet network it is possible to address systems by name. XEPDB1 is the default name of the XE instance’s Pluggable Database.

Instead of connecting to a Pluggable Database it is of course possible to connect to the Container Database’s Root (CDB$ROOT).

Summary

Podman is very compatible to Docker, easing the transition. In this part of the mini-series you could read how to use Podman functionality with Oracle Linux 8 to link a container running Oracle XE and SQLcl.

Vagrant: mapping a Virtualbox VM to a Vagrant environment

This is a small post hopefully saving you a few minutes mapping Vagrant and VirtualBox environments.

I typically have lots of Vagrant environments defined. I love Vagrant as a technology, it makes it super easy to spin up Virtual Machines (VMs) and learn about new technologies.

Said Vagrant environments obviously show up as VMs in VirtualBox. To make it more interesting I have a few more VirtualBox VMs that don’t map to a Vagrant environment. Adding in a naming convention that’s been growing organically over time I occasionally find myself at a loss as to which VirtualBox VM maps to a Vagrant environment. Can this be done? Yep, and creating a mapping is quite simple actually. Here is what I found useful.

Directory structure

My Vagrant directory structure is quite simple: I defined ${HOME}/vagrant as top-level directory with a sub-directory containing all my (custom) boxes. Apart from ~/vagrant/boxes I create further sub-directories for each project. For example:

[martin@ryzen: vagrant]$ ls -ld *oracle* boxes
drwxrwxr-x 2 martin martin 4096 Nov 23 16:52 boxes
drwxrwxr-x 3 martin martin   41 Feb 16  2021 oracle_19c_dg
drwxrwxr-x 3 martin martin   41 Nov 19  2020 oracle_19c_ol7
drwxrwxr-x 3 martin martin   41 Jan  6  2021 oracle_19c_ol8
drwxrwxr-x 3 martin martin   41 Nov 25 12:54 oracle_xe

But … which of my VirtualBox VMs belongs to the oracle_xe environment?

Mapping a Vagrant environment to a VirtualBox VM

Vagrant keeps a lot of metadata in the project’s .vagrant directory. Continuing with the oracle_xe example, here is what it stores:

[martin@buildhost: oracle_xe]$ tree .vagrant/
.vagrant/
├── machines
│   └── oraclexe
│       └── virtualbox
│           ├── action_provision
│           ├── action_set_name
│           ├── box_meta
│           ├── creator_uid
│           ├── id
│           ├── index_uuid
│           ├── synced_folders
│           └── vagrant_cwd
├── provisioners
│   └── ansible
│       └── inventory
│           └── vagrant_ansible_inventory
└── rgloader
    └── loader.rb

7 directories, 10 files

Looking at the above output I guess I should look at .vagrant/machines/

The machine name (oraclexe) is derived from the Vagrantfile. I create a config.vm.define section per VM out of habit (even when I create just 1 VM), as you can see here in my shortened Vagrantfile:

# -*- mode: ruby -*-
# vi: set ft=ruby :

Vagrant.configure("2") do |config|
  
  config.vm.define "oraclexe" do |xe|
    xe.vm.box = "ol7"
    xe.vm.box_url = "file:///home/martin/vagrant/boxes/ol7.json"

    ...

    xe.vm.provision "ansible" do |ansible|
      ansible.playbook = "setup.yml"
    end
  end
end

In case you don’t give your VMs a name you should find a directory named default instead.

As I’m using Vagrant together with VirtualBox I’m not surprised to find a sub-directory named virtualbox.

Finally! You see the VM’s metadata in that directory. The VM’s ID can be found in .vagrant/machines/oraclexe/virtualbox/id. The file contains the internal ID VirtualBox uses to identify VMs. Using that knowledge to my advantage I can create the lookup as shown here:

[martin@buildhost: oracle_xe]$ vboxmanage list vms | grep $(cat .vagrant/machines/oraclexe/virtualbox/id)
"oraclexe" {67031773-bad9-4325-937b-e471d02a56a3}

Voila! This wasn’t particularly hard since the VM name is oracelxe as well. Nevertheless I found this technique works well regardless of how you curated your Vagrantfile.

Happy Automating!

The performance story that didn’t turn out as one: a case of or-expansion and how the database mitigated the problem on its own

Recently I had a bit of time to study the effect of an optimizer query transformation, the so-called or-expansion in Oracle 19c. I thought there might be performance implications with statements using bind variables transformed in this way. My limited testing suggests that isn’t necessarily the case as the optimiser is remarkably resilient.

Still I thought my investigation was worth writing down, I hope you might agree and better still, find the read enjoyable.

Excuse me, what exactly is an Or-Expansion?

Quoting from the SQL Tuning Guide, during an “or expansion the optimiser transforms a query block containing top-level disjunctions into the form of a UNION ALL query the contains 2 or more branches”.

The remainder of this article sheds some light on the query transformation. By the way, there are many, many other blog articles out there covering or-expansion. Some even include an analysis of a 10053 trace! The subject is also covered in Troubleshooting Oracle Performance by Christian Antognini, a great read.

Example setup

This is one of the few times I deviated from my trusted Swingbench environment. Instead I created the following segments in my 19.12.0 database running on Oracle Linux 7.9/UEK 6.

CREATE TABLE skewed_data_distribution
    AS
        WITH generator AS (
            SELECT
                ROWNUM id
            FROM
                dual
            CONNECT BY
                ROWNUM <= 4000
        )
        SELECT
            ROWNUM                    AS id,
            CASE
                WHEN mod(ROWNUM, 100000) = 0     THEN 1
                WHEN mod(ROWNUM, 1000) = 0       THEN 2
                WHEN mod(ROWNUM, 100) = 0        THEN 3
                ELSE 4
            END                       AS skew,
            lpad('*', 150, '*')       AS pad,
            sysdate + dbms_random.value(-1000,0) as datecol
        FROM
            generator,
            generator
        WHERE
            ROWNUM <= 10e6;

CREATE INDEX i_skew_1 ON
    skewed_data_distribution (
        id
    );

CREATE INDEX i_skew_2 ON
    skewed_data_distribution (
        skew
    );

BEGIN
 dbms_stats.gather_table_stats(
  ownname => 'MARTIN', 
  tabname => 'SKEWED_DATA_DISTRIBUTION',
  method_opt => 'for all columns size auto, for columns size 254 skew',
  degree => 4
 );
END;
/

Thanks to Jonathan Lewis for elaborating on how to create sample data safely.

By the way I didn’t enable any of the fix_controls that come with the 19.12 Release Update (RU). If you just raised an eyebrow, please have look at Mike Dietrich’s blog for details about a potential call to DBMS_OPTIM_BUNDLE after applying a RU.

The query I’ll use for this article is this (it’s designed to trigger an or-expansion).

var the_id number
var the_skew number

WITH q AS (
    SELECT id,
        skew
    FROM
        skewed_data_distribution
    WHERE
        id = :the_id
        OR skew = :the_skew
)
SELECT /*+ gather_plan_statistics opt_param('_b_tree_bitmap_plans','false') */
    COUNT(*)
FROM
    q;

Please ignore the fact that it is a rather useless SQL statement on its own, but it helped me create a test case. All I needed was a table, some indexes and a suitable data distribution as well as a histogram on SKEW, otherwise the optimiser probably wouldn’t have considered the use of the index, but I’m getting ahead of myself.

When I first tested the query I didn’t get the or-expansion I wanted, but rather this:

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3063879156

---------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |          |     1 |     9 |  4872   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE                  |          |     1 |     9 |            |          |
|   2 |   BITMAP CONVERSION COUNT        |          |  2500K|    21M|  4872   (1)| 00:00:01 |
|   3 |    BITMAP OR                     |          |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|          |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | I_SKEW_2 |       |       |  4860   (1)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|          |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | I_SKEW_1 |       |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("SKEW"=TO_NUMBER(:THE_SKEW))
   7 - access("ID"=TO_NUMBER(:THE_ID))

20 rows selected.

That bitmap conversion wasn’t quite what I had in mind, hence the opt_param hint in the query text.

Data distribution

The following detail about data distribution might help understand the article better:

REM data distribution: SKEW

SELECT /*+ parallel */
    COUNT(*),
    skew
FROM
    skewed_data_distribution
GROUP BY
    skew;

  COUNT(*)       SKEW
---------- ----------
       100          1
      9900          2
     90000          3
   9900000          4

4 rows selected.

REM Histograms  

SELECT
    column_name,
    histogram,
    num_buckets,
    column_id
FROM
    user_tab_columns
WHERE
    table_name = 'SKEWED_DATA_DISTRIBUTION';

COLUMN_NAM HISTOGRAM           NUM_BUCKETS       COLUMN_ID
---------- --------------- --------------- ---------------
ID         NONE                          1               1
SKEW       FREQUENCY                     4               2
PAD        NONE                          1               3
DATECOL    NONE                          1               4

4 rows selected.

REM Histogram on SKEW

SELECT
    endpoint_number,
    endpoint_value
FROM
    user_tab_histograms
WHERE
        table_name = 'SKEWED_DATA_DISTRIBUTION'
    AND column_name = 'SKEW';

ENDPOINT_NUMBER  ENDPOINT_VALUE
--------------- ---------------
            100               1
          10000               2
         100000               3
       10000000               4

4 rows selected.

Workload

I am going to run 2 scripts a few times to simulate a query workload. The scripts differ in their bind variable values:

$ diff -y skew_unselective.sql skew_selective.sql
var the_id number						var the_id number
var the_skew number						var the_skew number

exec :the_id := 100						exec :the_id := 100
exec :the_skew := 4					      |	exec :the_skew := 1

WITH q AS (							WITH q AS (
    SELECT id,							    SELECT id,
        skew							        skew
    FROM							    FROM
        skewed_data_distribution				        skewed_data_distribution
    WHERE							    WHERE
        id = :the_id						        id = :the_id
        OR skew = :the_skew					        OR skew = :the_skew
)								)
SELECT /*+ gather_plan_statistics opt_param('_b_tree_bitmap_p	SELECT /*+ gather_plan_statistics opt_param('_b_tree_bitmap_p
    COUNT(*)							    COUNT(*)
FROM								FROM
    q;								    q;

Selective bind variables

Using my runMany.sh script I launched 20 instances of the more selective query first. The expected execution plan is as follows:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID  8tjz2tqn9gtck, child number 0
-------------------------------------
WITH q AS (     SELECT id,         skew     FROM
skewed_data_distribution     WHERE         id = :the_id         OR skew
= :the_skew ) SELECT /*+ gather_plan_statistics
opt_param('_b_tree_bitmap_plans','false') */     COUNT(*) FROM     q

Plan hash value: 414689775

---------------------------------------------------------------------------...
| Id  | Operation                              | Name                     |...
---------------------------------------------------------------------------...
|   0 | SELECT STATEMENT                       |                          |...
|   1 |  SORT AGGREGATE                        |                          |...
|   2 |   VIEW                                 | VW_ORE_9774CF0C          |...
|   3 |    UNION-ALL                           |                          |...
|*  4 |     INDEX RANGE SCAN                   | I_SKEW_1                 |...
|*  5 |     TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA_DISTRIBUTION |...
|*  6 |      INDEX RANGE SCAN                  | I_SKEW_2                 |...
---------------------------------------------------------------------------...

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (NUMBER): 100
   2 - :2 (NUMBER): 1

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ID"=:THE_ID)
   5 - filter(LNNVL("ID"=:THE_ID))
   6 - access("SKEW"=:THE_SKEW)


34 rows selected.

In other words, you can witness the result of the query transformation. Also note the VIEW VW_ORE%: it doesn’t exist in the database, it only exists thanks to the query transformation. I think this is a prime use case for the or-expansion: by splitting the or condition in the where clause into separate statements Oracle can make use of the indexes.

Really unselective predicate

So here I thought the trouble might arise: what if instead of a small-ish subset of the rows Oracle has to return the majority of the table instead? It can be as simple as replacing the value for SKEW from a selective-ish predicate to a highly unselective one. Which doesn’t trigger a hard parse due to a change the query text.

Let’s recap. So far, I have 1 execution plan for my statement in the shared pool, covering all executions up to now (29 to be precise):

SELECT
    sql_id,
    plan_hash_value,
    child_number,
    executions,
    is_bind_aware,
    is_bind_sensitive
FROM
    v$sql
WHERE
    sql_id = '8tjz2tqn9gtck';

SQL_ID        PLAN_HASH_VALUE    CHILD_NUMBER      EXECUTIONS I I
------------- --------------- --------------- --------------- - -
8tjz2tqn9gtck       414689775               0              29 N Y

Let’s try the second query. Using runMany.sh I launched skew_unselective.sql 10 times against the database. After they completed, I checked the shared pool again:

SELECT
    sql_id,
    plan_hash_value,
    child_number,
    executions,
    is_bind_aware,
    is_bind_sensitive
FROM
    v$sql
WHERE
    sql_id = '8tjz2tqn9gtck';

SQL_ID        PLAN_HASH_VALUE    CHILD_NUMBER      EXECUTIONS I I
------------- --------------- --------------- --------------- - -
8tjz2tqn9gtck       414689775               0              39 N Y

So here is a problem: The next batch of my queries used the “wrong”, or rather suboptimal plan. This eventually results and longer elapsed time/query. However, during my tests-and I appreciate it’s a bit limited in scope-I noticed that the next time I ran the un-selective query, another child cursor appeared:

SELECT
    sql_id,
    plan_hash_value,
    child_number,
    executions,
    is_bind_aware,
    is_bind_sensitive
FROM
    v$sql
WHERE
    sql_id = '8tjz2tqn9gtck';

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS I I
------------- --------------- ------------ ---------- - -
8tjz2tqn9gtck       414689775            0         39 N Y
8tjz2tqn9gtck      1662074091            1          1 N Y

SQL> select * from dbms_xplan.display_cursor('8tjz2tqn9gtck',1);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  8tjz2tqn9gtck, child number 1
-------------------------------------
WITH q AS (     SELECT id,         skew     FROM
skewed_data_distribution     WHERE         id = :the_id         OR skew
= :the_skew ) SELECT /*+ gather_plan_statistics
opt_param('_b_tree_bitmap_plans','false') */     COUNT(*) FROM     q

Plan hash value: 1662074091

-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                          |       |       | 66204 (100)|          |
|   1 |  SORT AGGREGATE    |                          |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEWED_DATA_DISTRIBUTION |  9900K|    84M| 66204   (1)| 00:00:03 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("SKEW"=:THE_SKEW OR "ID"=:THE_ID))


22 rows selected.

Now that’s better! With the totally un-selective predicate it doesn’t make sense to use the index. The full scan is a far better choice. What happens when I run the selective query again?

SQL> @skew_selective

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

  COUNT(*)
----------
       101

Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  8tjz2tqn9gtck, child number 2
-------------------------------------
WITH q AS (     SELECT id,         skew     FROM
skewed_data_distribution     WHERE         id = :the_id         OR skew
= :the_skew ) SELECT /*+ gather_plan_statistics
opt_param('_b_tree_bitmap_plans','false') */     COUNT(*) FROM     q

Plan hash value: 414689775

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                     | Starts | E-Rows |E-Bytes| Cost (%CPU)|
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                          |      1 |        |       |    10 (100)|
|   1 |  SORT AGGREGATE                        |                          |      1 |      1 |       |            |
|   2 |   VIEW                                 | VW_ORE_9774CF0C          |      1 |    101 |       |    10   (0)|
|   3 |    UNION-ALL                           |                          |      1 |        |       |            |
|*  4 |     INDEX RANGE SCAN                   | I_SKEW_1                 |      1 |      1 |     6 |     3   (0)|
|*  5 |     TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA_DISTRIBUTION |      1 |    100 |   900 |     7   (0)|
|*  6 |      INDEX RANGE SCAN                  | I_SKEW_2                 |      1 |    100 |       |     3   (0)|
------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (NUMBER): 100
   2 - :2 (NUMBER): 1

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ID"=:THE_ID)
   5 - filter(LNNVL("ID"=:THE_ID))
   6 - access("SKEW"=:THE_SKEW)

SELECT
    sql_id,
    plan_hash_value,
    child_number,
    executions,
    is_bind_aware,
    is_bind_sensitive
FROM
    v$sql
WHERE
    sql_id = '8tjz2tqn9gtck';

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS I I
------------- --------------- ------------ ---------- - -
8tjz2tqn9gtck       414689775            0         39 N Y
8tjz2tqn9gtck      1662074091            1          1 N Y
8tjz2tqn9gtck       414689775            2          1 Y Y

After a few more tries the number of child cursors settled down at 4:

SQL> SELECT
    sql_id,
    plan_hash_value,
    child_number,
    executions,
    is_bind_aware,
    is_bind_sensitive
FROM
    v$sql
WHERE
    sql_id = '8tjz2tqn9gtck';

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS I I
------------- --------------- ------------ ---------- - -
8tjz2tqn9gtck       414689775            0         39 N Y
8tjz2tqn9gtck      1662074091            1          1 N Y
8tjz2tqn9gtck       414689775            2          5 Y Y
8tjz2tqn9gtck      1662074091            3          7 Y Y

Child cursors 0 and 1 haven’t seen further executions while 2 and 3 do.

Summary

My-admittedly limited-amount of testing suggests that it is indeed possible to use or-expansion successfully even with huge data skew and bind variables in 19.12.0. All of my database’s parameters remained at their default with the exception of _b_tree_bitmap_plans to prevent these from appearing.

And many thanks, as always, to Tanel Poder for sharing his scripts with the community. They have been taken to good use writing this post.

Deploying I/O intensive workloads in the cloud: Oracle Automatic Storage Management (ASM)

Over the past month I wrote a few posts about deploying I/O intensive workloads in the cloud. Using standard Linux tools, mainly Logical Volume Manager (LVM) I tried to prevent certain pitfalls from occurring. Although I’m a great fan of LVM and RAID (and their combination), there are situations where LVM/Software RAID aren’t part the best solution. This is especially true when it comes to extending a VM’s storage configuration for an Oracle Database.

Striping, Mirroring and Risk

With LVM RAID (or LVM on top of Software RAID) it is possible to stripe an Oracle database-or any other I/O intensive workload-across multiple disks. At the risk of losing the RAID device (remember that RAID 0 offers exactly zero protection from disk failure) you can gain a performance advantage. The risk can be partially mitigated by using a proven, tested, and most importantly, rehearsed technique to still meet the RTO and RPO of the database.

The trouble with LVM RAID can potentially start as soon as you add more storage to the VM. I hope I managed to demonstrate the risk of I/O hotspots in my earlier posts.

Oracle’s ASM is different from stock-Linux tools, and it’s much less of a general purpose solution. Being an Oracle product it is also subject to a different license model. Which rules it out for most generic use cases, or at least that’s my experience. If, however, you want to deploy an Oracle database in the cloud, it is well worth considering ASM. I don’t want to say it’s free of drawbacks (no piece of software is) but in my opinion its benefits outweigh the disadvantages deploying a database.

For the sake of argument I’ll treat Oracle Restart and Grid Infrastructure as synonyms in this article. Oracle Restart is made up of ASM as well as a trimmed version of Oracle’s Clusterware as used in Real Application Clusters. Oracle Restart is installed into a separate Oracle Home, you usually install one database software home in addition. More on that later.

ASM vs LVM: a Question of Concepts

ASM has been around for quite some time and I like to think of it as a mature technology. In a way it is similar to LVM as you aggregate block devices (Physical Volumes in LVM) into Disk Groups (Volume Groups in LVM). Rather than creating another layer of abstraction on top of the ASM Disk Group as you do with LVM you simply point the database at a couple of Disk Groups and you are done. There is no need to maintain an equivalent of a Logical Volume or file system. A shorter code path to traverse tends to be less work. And it’s common knowledge that the fastest way to do something is not to do it in the first place. I should also point out that ASM does not perform I/O. It’s always the database session that does; otherwise ASM would never scale.

But what about protection from failure? Put very simply, in ASM you have a choice between striping and striping + mirroring. There are multiple so-called redundancy levels each with their own implications. If you are interested you can find the relevant details in Oracle’s Automatic Storage Management Administration Guide.

My Test Environment’s Setup

To keep things consistent with my previous posts I am installing Oracle Restart on my VM.Standard.E4.Flex VM in Oracle Cloud Infrastructure. Both Grid Infrastructure and database software are patched to 19.12.0, the current release at the time of writing. The underlying Linux version is 8.4 with kernel 5.4.17-2102.203.6.el8uek.x86_64. I decided to use UDEV rules for device name persistence and setting permissions rather than ASMLib or ASM Filter Driver. To keep things simple and also to follow the path I chose with my previous LVM/RAID posts I’m going to create the +DATA and +RECO Disk Groups with EXTERNAL redundancy. With external redundancy failure of a single block device in an ASM Disk Group will bring the entire Disk Group down, taking the database with it: game over. This is the same as with a RAID 0 configuration.

Again, and in line with the other posts about the topic, this article doesn’t concern itself with the durability of block devices in the cloud. External Redundancy should only be considered if approved in your organisation. You are most likely also required to put additional means in place to guarantee the database’s RTO and RPO. See my earlier comments and posts for details.

My +DATA disk group is currently made up of 2 block devices, +RECO consists of just 1 device. The database lives in +DATA with the Fast Recovery Area (FRA) located on +RECO.

SQL> select dg.name dg_name, dg.type, d.name disk_name, d.os_mb, d.path
  2   from v$asm_disk d join v$asm_diskgroup dg on (d.group_number = dg.group_number);

DG_NAME    TYPE   DISK_NAME       OS_MB PATH
---------- ------ ---------- ---------- ------------------------------
RECO       EXTERN RECO_0000      511998 /dev/oracleoci/oraclevde1
DATA       EXTERN DATA_0001      511998 /dev/oracleoci/oraclevdd1
DATA       EXTERN DATA_0000      511998 /dev/oracleoci/oraclevdc1

You can see from the volume sizes this is a lab/playground environment. The concepts however are independent of disk size. Just make sure the disks you use are of the same size and performance characteristics. Terraform is the most convenient way in the cloud to ensure they are.

Performance

Just as before I’ll start the familiar Swingbench workload. It isn’t meant to benchmark the system but to see which disks are in use. As in the previous examples I gave, Online Redo Logs aren’t multiplexed. This really is acceptable only in this scenario and shouldn’t be done with any serious deployments of the database. It helps me isolate I/O though, hence it’s why I did it.

Before getting detailed I/O performance figures I need to check the current device mapping:

SQL> !ls -l /dev/oracleoci/oraclevd{c,d}1
lrwxrwxrwx. 1 root root 7 Sep  1 15:21 /dev/oracleoci/oraclevdc1 -> ../sdc1
lrwxrwxrwx. 1 root root 7 Sep  1 15:21 /dev/oracleoci/oraclevdd1 -> ../sdd1

Looking at the iostat output I can see both /dev/sdc and /dev/sdd actively used:

[oracle@oracle-19c-asm ~]$ iostat -xmz 5 3
Linux 5.4.17-2102.203.6.el8uek.x86_64 (oracle-19c-asm)  09/01/2021      _x86_64_        (16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.19    0.00    0.26    0.12    0.01   98.43

Device            r/s     w/s     rMB/s     wMB/s   rrqm/s   wrqm/s  ... %util
sda              1.12    1.03      0.04      0.03     0.01     0.54  ...  0.10
dm-0             1.03    0.95      0.03      0.03     0.00     0.00  ...  0.08
dm-1             0.02    0.60      0.00      0.01     0.00     0.00  ...  0.01
sdb              0.87    0.51      0.04      0.00     0.00     0.12  ...  0.09
dm-2             0.86    0.63      0.04      0.00     0.00     0.00  ...  0.09
sdc            291.58    4.87     54.15      0.05     3.51     0.01  ... 22.92
sdd            289.95    4.05     53.63      0.04     3.37     0.01  ... 19.01
sde              0.13    0.00      0.00      0.00     0.00     0.00  ...  0.01
sdf              0.10    0.72      0.00      0.01     0.00     0.00  ...  0.13

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           4.23    0.00    7.77   23.90    0.33   63.78

Device            r/s     w/s     rMB/s     wMB/s   rrqm/s   wrqm/s  ... %util
sda              0.00    2.40      0.00      0.05     0.00     1.20  ...  0.12
dm-0             0.00    0.60      0.00      0.00     0.00     0.00  ...  0.08
dm-1             0.00    3.00      0.00      0.05     0.00     0.00  ...  0.04
sdb              0.00    0.40      0.00      0.00     0.00     0.00  ...  0.02
dm-2             0.00    0.40      0.00      0.00     0.00     0.00  ...  0.02
sdc           24786.60   67.40    211.80      0.57  2319.60     0.00 ... 100.00
sdd           24575.40   72.00    210.01      0.55  2302.80     0.00 ...  97.70
sdf              0.00    0.40      0.00      0.00     0.00     0.00  ...  0.06

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           4.74    0.00    7.65   24.38    0.31   62.93

Device            r/s     w/s     rMB/s     wMB/s   rrqm/s   wrqm/s  ... %util
sda              0.00    1.80      0.00      0.02     0.00     0.20  ...  0.04
dm-0             0.00    1.20      0.00      0.02     0.00     0.00  ...  0.02
dm-1             0.00    0.80      0.00      0.01     0.00     0.00  ...  0.02
sdc           24684.20   61.60    215.14      0.50  2844.40     0.40 ... 100.00
sdd           24399.80   68.40    212.41      0.55  2787.20     0.60 ...  95.74
sdf              0.00    0.80      0.00      0.01     0.00     0.00  ...  0.10

This should demonstrate the fact ASM stripes data across disks. Up to this point there isn’t any visible difference in the iostat output compared to my previous posts.

Extending Storage

The main difference between LVM/RAID and ASM is yet to come: what happens if I have to add storage to the +DATA disk group? Remember that with LVM you had to add as many additional devices as you had in use. In other words, if you used a RAID 0 consisting of 2 block devices, you need to add another 2. With ASM you don’t have the same restriction as you can see in a minute.

I have added another block device to the VM, named /dev/oracleoci/oraclevdf with the exact same size and performance characteristics as the existing 2 devices. After partitioning it and checking for device permissions I can add the device to the Disk Group. There are many ways to do so, I’m showing you the SQL interface.

[grid@oracle-19c-asm ~]$ sqlplus / as sysasm

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 2 06:21:08 2021
Version 19.12.0.0.0

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


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

SQL> alter diskgroup data add disk '/dev/oracleoci/oraclevdf1' ; 

Diskgroup altered.

SQL>

The prompt returns immediately, however there is an asynchronous operation started in the background, a so-called re-balance task:

SQL> select dg.name, o.operation, o.state,o.sofar,o.est_work,o.est_minutes, o.error_code
  2   from v$asm_diskgroup dg join v$asm_operation o using (group_number)
  3  /

NAME                           OPERA STAT      SOFAR   EST_WORK EST_MINUTES ERROR_CODE
------------------------------ ----- ---- ---------- ---------- ----------- --------------------------------------------
DATA                           REBAL RUN       14608          0           0
DATA                           REBAL DONE          0          0           0
DATA                           REBAL DONE      33308      33308           0

Once completed, another disk has been added to the +DATA disk group:

SQL> select dg.name dg_name, dg.type, d.name disk_name, d.os_mb, d.path
  2   from v$asm_disk d join v$asm_diskgroup dg on (d.group_number = dg.group_number)
  3  where dg.name = 'DATA'
  4  /

DG_NAME    TYPE   DISK_NAME	  OS_MB PATH
---------- ------ ---------- ---------- ------------------------------
DATA	   EXTERN DATA_0002	 511998 /dev/oracleoci/oraclevdf1
DATA	   EXTERN DATA_0000	 511998 /dev/oracleoci/oraclevdc1
DATA	   EXTERN DATA_0001	 511998 /dev/oracleoci/oraclevdd1

SQL> 

The disk rebalance operation is an online operation by the way with a few tunables such as the so-called power limit: you can trade off completion time vs effect it has on ongoing I/O operations. For some time the maximum value of ASM’s power limit was 11 ;)

What does that mean for our Swingbench workload? Let’s have a look at iostat while the same workload is running. Please remember that /dev/oracleoci/oraclevd[cdf]1 are part of the ASM +DATA Disk Group:

[grid@oracle-19c-asm ~]$ ls -l /dev/oracleoci/oraclevd[cdf]1
lrwxrwxrwx. 1 root root 7 Sep  2 06:30 /dev/oracleoci/oraclevdc1 -> ../sdd1
lrwxrwxrwx. 1 root root 7 Sep  2 06:30 /dev/oracleoci/oraclevdd1 -> ../sdb1
lrwxrwxrwx. 1 root root 7 Sep  2 06:35 /dev/oracleoci/oraclevdf1 -> ../sdf1

Please bear this in mind when looking at the iostat output:

[grid@oracle-19c-asm ~]$ iostat -xmz 5 3
Linux 5.4.17-2102.203.6.el8uek.x86_64 (oracle-19c-asm) 	09/02/2021 	_x86_64_	(16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.27    0.03    0.37    0.40    0.03   98.90

Device            r/s     w/s     rMB/s     wMB/s   rrqm/s   ...  %util
sda              4.92    1.21      0.14      0.08     0.03   ...   0.26
dm-0             4.53    0.68      0.13      0.07     0.00   ...   0.23
dm-1             0.12    0.75      0.00      0.01     0.00   ...   0.02
sdb            391.83    7.36     12.15      3.60    27.41   ...   6.90
sdc              0.15    0.71      0.00      0.01     0.00   ...   0.14
sdd            396.92    8.48     12.20      3.61    28.23   ...   6.85
sdf            383.58   13.97      3.22     10.71    27.53   ...   5.92
sde              3.74    0.85      0.19      0.01     0.00   ...   0.28
dm-2             3.75    1.02      0.19      0.01     0.00   ...   0.28

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           7.60    0.00   12.18   26.38    1.61   52.24

Device            r/s     w/s     rMB/s     wMB/s   rrqm/s   ...  %util
sda              0.00    0.40      0.00      0.00     0.00   ...   0.06
dm-0             0.00    0.40      0.00      0.00     0.00   ...   0.06
sdb           24375.60  176.80    203.25      1.39  1635.40  ...   97.62
sdc              0.00    0.80      0.00      0.01     0.00   ...   0.14
sdd           24654.60  172.40    205.89      1.45  1689.80  ...   99.96
sdf           24807.40  201.20    207.31      1.51  1718.20  ...   97.86
sde              0.00    1.00      0.00      0.01     0.00   ...   0.04
dm-2             0.00    1.20      0.00      0.01     0.00   ...   0.04

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           7.22    0.00   13.05   23.61    1.55   54.57

Device            r/s     w/s     rMB/s     wMB/s   rrqm/s   ...  %util
sda              0.00    0.60      0.00      0.00     0.00   ...   0.10
dm-0             0.00    0.40      0.00      0.00     0.00   ...   0.04
dm-1             0.00    0.20      0.00      0.00     0.00   ...   0.06
sdb           24783.40  145.40    212.17      1.15  2363.20  ...   97.48
sdc              0.00    0.60      0.00      0.00     0.00   ...   0.14
sdd           24795.40  113.60    213.19      1.00  2470.80  ...   99.90
sdf           24871.00  106.00    213.34      0.97  2426.00  ...   97.00
sde              0.00    2.40      0.00      0.02     0.00   ...   0.08
dm-2             0.00    2.60      0.00      0.02     0.00   ...   0.08

You can see that all 3 disks are more or less evenly used. This is the main difference to the use of LVM RAID. Thanks to the rebalance operation all data on the disk group is redistributed across the disks in the group.

Summary

When it comes to deploying an Oracle database in an Infrastructure as a Service (IaaS) scenario Oracle’s ASM offers lots of advantages over stock Linux tools. For example, it is possible to add storage to an ASM Disk Group as and when it’s needed without over-provisioning. ASM furthermore rebalances all data in the Disk Group across all disks as part of a configuration change as you just saw. That way it is much harder to create I/O hotspots I often see when ASM is not in use.

In addition to ASM you also get other amenities as a side effect. For example, Oracle Restart allows you to start databases and database services automatically when the system boots up. There is no need to write systemd unit files as it’s all done behind the covers. Should your database crash for some reason, provided it can, Oracle Restart automatically brings it up again without your intervention. It also works beautifully in conjunction with Oracle’s Universal Connection Pool (UCP) and Data Guard.

The use of ASM implies direct I/O. I said earlier that ASM doesn’t maintain a file system layer when used for the Oracle database (that’s not entirely correct but true for all the databases I saw) and as a result Linux can’t cache I/O. This is considered a good thing in the community by most. Oracle has its own buffer cache after all, as long as it’s sized appropriately for your workload, double-buffering isn’t the best use of precious DRAM.

So much for the plus side, but what about the implications of using Oracle Restart? First of all, it’s another Oracle software home you need to maintain. Given the high degree of automation possible these days that shouldn’t be an issue. An Ansible playbook is easy enough to write, patching all Oracle Restart components.

If your organisation mandates a separation of duties between database and storage/Linux administration your respective administrator might need to learn a new technology.

I’m sure you can think of additional downsides to using ASM, and I admit I won’t delve into the subject deeper as I’m quite biased. ASM has been one of the truly outstanding innovations for running Oracle in my opinion. The human aspect of introducing a new technology however isn’t to be under-estimated and the best technology doesn’t always win the race.

Resolving slight niggles of Enterprise Manager Express 19c

This page, should I remember I wrote it, hopefully addresses the slight niggles I have with Oracle Enterprise Manager Express. I always forget how to solve these and it takes me a minute to remember. I hope this page helps me jump start my memory. If you have any additional niggles to report please do and I’ll add them here.

OEM Express not working in Grid Infrastructure when separation of duties is enabled

In case you installed Oracle Restart (and I presume the same applies for Real Application Clusters as well) with a different account than the database you won’t be able to access OEM Express straight away. The most common issue I had was this

[oracle@server3 ~]$ curl --verbose --insecure https://server3:5510/em
* About to connect() to server3 port 5510 (#0)
*   Trying 192.168.100.13...
* Connected to server3 (192.168.100.13) port 5510 (#0)
* Initializing NSS with certpath: sql:/etc/pki/nssdb
*   CAfile: /etc/pki/tls/certs/ca-bundle.crt
  CApath: none
* NSS error -5938 (PR_END_OF_FILE_ERROR)
* Encountered end of file
* Closing connection 0
curl: (35) Encountered end of file

I have also seen this one (with port-forwarding in use)

$ curl --insecure --verbose https://localhost:5510/em
* Uses proxy env variable no_proxy == 'localhost,127.0.0.0/8,::1'
*   Trying 127.0.0.1:5510...
* TCP_NODELAY set
* Connected to localhost (127.0.0.1) port 5510 (#0)
* ALPN, offering h2
* ALPN, offering http/1.1
* successfully set certificate verify locations:
*   CAfile: /etc/ssl/certs/ca-certificates.crt
  CApath: /etc/ssl/certs
* TLSv1.3 (OUT), TLS handshake, Client hello (1):
* OpenSSL SSL_connect: SSL_ERROR_SYSCALL in connection to localhost:5510 
* Closing connection 0
curl: (35) OpenSSL SSL_connect: SSL_ERROR_SYSCALL in connection to localhost:5510 

In a browser you get something along the lines of “this site can’t be reached … ERR_CONNECTION_CLOSED”.

This issue is addressed in My Oracle Support (MOS) Doc ID 1604062.1 “Troubleshooting why EM Express is not working”. Search for item 10 in the table of context for the resolution.

Invalid Container Name when trying to connect to a PDB

When enabling OEM Express by setting the HTTPS port in CDB$ROOT you explicitly enabled it for the root container only. Connecting to OEM Express using this port (and omitting the container name) provides you with information about CDB$ROOT as well as all other Pluggable Databases (PDBs).

But what if you want to connect to a specific PDB? In this regard the login screen presented by OEM Express can be a little misleading as you can’t enter a container name without some further work. Unless that work is completed you get an error (“Invalid Container Name”) even though both credentials and container name are correct.

This can be changed though. Since Oracle 12.2 it is possible to define a single, global OEM Express port for the CDB and all it’s PDBs as documented in the 2 Day DBA manual. After implementing the change it is possible to log in to a specific PDB by supplying its name in the login screen. I couldn’t find the OEM equivalent drop-down menu allowing me to switch back and forth between CDB$ROOT and the other containers so it seems to be log-off/log-on.

By the way, in 12.1 you had to switch to each PDB for which you wanted to enable OEM Express and execute a separate call to dbms_xdb_config.sethttpsport().

More to come

This is a living document and I’ll update it with further niggles as and when I hit them.

Deploying I/O intensive workloads in the cloud: mdadm (aka Software) RAID

The final part of my “avoiding pitfalls with Linux Logical Volume Manager” (LVM) series considers software RAID on Oracle Linux 8 as the basis for your LVM’s Physical Volume (PV). It’s still the very same VM.Standard.E4.Flex running Oracle 19.12.0 on top of Oracle Linux 8.4 with UEK6 (5.4.17-2102.203.6.el8uek.x86_64) I used for creating the earlier posts.

Previous articles in this series can be found here:

Storage Configuration

Rather than using LVM-RAID as in the previous article, the plan this time is to create a software RAID (pseudo-device) and use it as a Physical Volume. This is exactly what I have done before I learned about LVM RAID. Strictly speaking, it isn’t necessary to create a Volume Group on top of a RAID device as you can absolutely use such a device on its own. Having said that, growing a RAID 0 device doesn’t seem possible after my limited time studying the documentation. Speaking of which: you can read more about software RAID in Red Hat Linux 8 here.

In this post I’ll demonstrate how you could use a RAID 0 device for striping data across multiple disks. Please don’t implement the steps in this article unless software RAID is an approved solution in your organisation and you are aware of the implications. Kindly note this article does not concern itself with the durability of block devices in the cloud. In the cloud, you have a lot less control over the block devices you get, so make sure you have appropriate protection methods in place to guarantee your databases’ RTO and RPO. RAID 0 offers 0 protection from disk failure (it’s in the name ;), so as soon as you lose a disk from your software RAID, it’s game over.

Creating the RAID Device

The first step is to create the RAID device. For nostalgic reasons I named it /dev/md127, other sources name their devices /dev/md0. Not that it matters too much.

[opc@oracle-19c-fs ~]$ sudo mdadm --create /dev/md127 --level=0 \
> --raid-devices=2 /dev/oracleoci/oraclevdc1 /dev/oracleoci/oraclevdd1
mdadm: Defaulting to version 1.2 metadata
mdadm: array /dev/md127 started.
[opc@oracle-19c-fs ~]$ 

As you can see from the output above mdadm created the device for me. If you wondered what the funny device names imply, have a look at an earlier post I wrote about device name persistence in OCI.

You can always use mdadm --detail to get all the interesting details from a RAID device:

[opc@oracle-19c-fs ~]$ sudo mdadm --detail /dev/md127
/dev/md127:
           Version : 1.2
     Creation Time : Fri Aug  6 14:15:12 2021
        Raid Level : raid0
        Array Size : 524019712 (499.74 GiB 536.60 GB)
      Raid Devices : 2
     Total Devices : 2
       Persistence : Superblock is persistent

       Update Time : Fri Aug  6 14:15:12 2021
             State : clean 
    Active Devices : 2
   Working Devices : 2
    Failed Devices : 0
     Spare Devices : 0

            Layout : -unknown-
        Chunk Size : 512K

Consistency Policy : none

              Name : oracle-19c-fs:127  (local to host oracle-19c-fs)
              UUID : 30dc8f99...
            Events : 0

    Number   Major   Minor   RaidDevice State
       0       8       33        0      active sync   /dev/sdc1
       1       8       49        1      active sync   /dev/sdd1
[opc@oracle-19c-fs ~]$  

This is looking good – both devices are available and no errors have occurred.

Creating oradata_vg

With the future PV available it’s time to create the Volume Group and the Logical Volumes (LV) for the database and Fast Recovery Area. I’m listing the steps here for later reference, although they are the same as in part 1 of this article.

[opc@oracle-19c-fs ~]$ #
[opc@oracle-19c-fs ~]$ # step 1) create the PV
[opc@oracle-19c-fs ~]$ sudo pvcreate /dev/md127
  Physical volume "/dev/md127" successfully created.

[opc@oracle-19c-fs ~]$ #
[opc@oracle-19c-fs ~]$ # step 2) create the VG
[opc@oracle-19c-fs ~]$ sudo vgcreate oradata_vg /dev/md127
  Volume group "oradata_vg" successfully created

[opc@oracle-19c-fs ~]$ #
[opc@oracle-19c-fs ~]$ # step 3) create the first LV
[opc@oracle-19c-fs ~]$ sudo lvcreate --extents 80%FREE --name oradata_lv oradata_vg 
  Logical Volume "oradata_lv" created

[opc@oracle-19c-fs ~]$ #
[opc@oracle-19c-fs ~]$ # step 4) create the second LV
[opc@oracle-19c-fs ~]$ sudo lvcreate --extents 100%FREE --name orareco_lv oradata_vg 
  Logical volume "orareco_lv" created.

The end result are 2 LVs in oradata_vg:

[opc@oracle-19c-fs ~]$ sudo lvs oradata_vg
  LV         VG         Attr       LSize   Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  oradata_lv oradata_vg -wi-a----- 399.79g                                                    
  orareco_lv oradata_vg -wi-a----- <99.95g   

That’s it! The LVs require file systems before they can be mounted (not shown here).

Trying it out

After the final touches have been applied I restored the database and started the familiar Swingbench workload to see which disks are in use. Right before I did that I ensured I’m not multiplexing control files/online redo logs in the FRA for test purposes only. NOT multiplexing control files/online redo log members is probably a Bad Idea for serious Oracle deployments but ok for this scenario.

I am expecting to see both block devices making up /dev/md127 used. And sure enough, they are:

[opc@oracle-19c-fs ~]$ iostat -xmz 5 3
Linux 5.4.17-2102.203.6.el8uek.x86_64 (oracle-19c-fs)   13/08/21        _x86_64_        (16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.23    0.01    0.35    0.57    0.01   98.83

Device            r/s     w/s     rMB/s     wMB/s   rrqm/s   wrqm/s  ...  %util
sda              2.99    0.96      0.08      0.04     0.03     0.26  ...   0.21
dm-0             2.78    0.62      0.07      0.03     0.00     0.00  ...   0.20
dm-1             0.06    0.58      0.00      0.01     0.00     0.00  ...   0.02
sdb              1.28    0.22      0.06      0.00     0.00     0.02  ...   0.13
dm-2             1.26    0.24      0.06      0.00     0.00     0.00  ...   0.13
sdc            753.52   26.38      8.37      5.64    30.91     0.29  ...   7.36
md127         1573.79   53.30     17.44     12.01     0.00     0.00  ...   0.00
sdd            758.09   26.57      8.42      5.64    31.29     0.05  ...   9.34
sde             20.53    0.00      5.11      0.00     0.00     0.00  ...   1.79
dm-3            20.51    0.00      5.11      0.00     0.00     0.00  ...   1.79
dm-4          1558.54   28.25     12.20      5.97     0.00     0.00  ...   6.56
dm-5             4.69    2.61      4.58      5.26     0.00     0.00  ...   4.15

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           4.08    0.00    5.32    9.48    0.13   80.99

Device            r/s     w/s     rMB/s     wMB/s   rrqm/s   wrqm/s  ... %util
sda              0.00    3.40      0.00      0.03     0.00     0.60  ...  0.08
dm-0             0.00    2.60      0.00      0.02     0.00     0.00  ...  0.08
dm-1             0.00    1.40      0.00      0.01     0.00     0.00  ...  0.04
sdc           16865.80  284.60    140.04      2.39  1059.60     0.20 ...  92.60
md127         36008.00  564.20    281.33      4.76     0.00     0.00 ...   0.00
sdd           16978.80  279.40    141.11      2.34  1081.40     0.00 ...  99.96
dm-4          36007.80  563.00    281.33      4.73     0.00     0.00 ... 100.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           4.07    0.00    5.51   10.52    0.16   79.74

Device            r/s     w/s     rMB/s     wMB/s   rrqm/s   wrqm/s  ... %util
sdb              0.00    0.80      0.00      0.01     0.00     0.20  ...  0.04
dm-2             0.00    1.00      0.00      0.01     0.00     0.00  ...  0.04
sdc           17709.80  317.80    142.87      2.51   577.40     0.00 ...  93.90
md127         36657.80  661.60    286.41      5.31     0.00     0.00 ...   0.00
sdd           17790.00  343.40    143.69      2.77   599.00     0.00 ...  99.94
dm-4          36657.80  660.20    286.41      5.28     0.00     0.00 ... 100.00

[opc@oracle-19c-fs ~]$ 

No surprises here! Except maybe that /dev/md127 was somewhat underutilised ;) I guess that’s an instrumentation bug/feature. /dev/dm-4 – showing 100% utilisation – belongs to oradata_lv:

[opc@oracle-19c-fs ~]$ ls -l /dev/mapper | egrep dm-4
lrwxrwxrwx. 1 root root       7 Aug 13 09:37 oradata_vg-oradata_lv -> ../dm-4

Extending oradata_vg

Just as with each previous example I’d like to see what happens when I run out of space and have to extend oradata_vg. For this to happen I need a couple more block devices. These have to match the existing ones in size and performance characteristics for the best result. No difference to LVM-RAID I covered in the earlier article.

I created /dev/md128 in the same way as I did for the original RAID device and created a Physical Volume from it. oradata_vg looked like this prior to its extension:

[opc@oracle-19c-fs ~]$ sudo vgs oradata_vg
  VG         #PV #LV #SN Attr   VSize   VFree
  oradata_vg   1   2   0 wz--n- 499.74g    0 

In the next step I extended the Volume Group but only after I ensured I have a proven, working backup of everything. Don’t ever make changes to the storage layer without a backup and a known, tested, proven way to recover from unforeseen issues!

[opc@oracle-19c-fs ~]$ sudo vgextend oradata_vg /dev/md128
  Volume group "oradata_vg" successfully extended
[opc@oracle-19c-fs ~]$ sudo vgs oradata_vg
  VG         #PV #LV #SN Attr   VSize   VFree  
  oradata_vg   2   2   0 wz--n- 999.48g 499.74g

The VG now shows 2 PVs and plenty of free space. So let’s add 80% of the free space to oradata_lv.

[opc@oracle-19c-fs ~]$ sudo lvresize --extents +80%FREE --resizefs /dev/mapper/oradata_vg-oradata_lv
  Size of logical volume oradata_vg/oradata_lv changed from 399.79 GiB (102347 extents) to <799.59 GiB (204695 extents).
  Logical volume oradata_vg/oradata_lv successfully resized.
meta-data=/dev/mapper/oradata_vg-oradata_lv isize=512    agcount=16, agsize=6550144 blks
         =                       sectsz=4096  attr=2, projid32bit=1
         =                       crc=1        finobt=1, sparse=1, rmapbt=0
         =                       reflink=1
data     =                       bsize=4096   blocks=104802304, imaxpct=25
         =                       sunit=128    swidth=256 blks
naming   =version 2              bsize=4096   ascii-ci=0, ftype=1
log      =internal log           bsize=4096   blocks=51173, version=2
         =                       sectsz=4096  sunit=1 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
data blocks changed from 104802304 to 209607680

The LV changes from its original size …

[opc@oracle-19c-fs ~]$ sudo lvs /dev/mapper/oradata_vg-oradata_lv
  LV         VG         Attr       LSize   Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert                                                         
  oradata_lv oradata_vg -wi-ao---- 399.79g

to its new size:

[opc@oracle-19c-fs ~]$ sudo lvs /dev/mapper/oradata_vg-oradata_lv
  LV         VG         Attr       LSize    Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  oradata_lv oradata_vg -wi-ao---- <799.59g                                                    

The same applies to the file system as well:

[opc@oracle-19c-fs ~]$ df -h /u01/oradata
Filesystem                         Size  Used Avail Use% Mounted on
/dev/mapper/oradata_vg-oradata_lv  800G   38G  762G   5% /u01/oradata

Does that change performance?

Based on my experience with LVM-RAID I did not expect a change in performance as my database wasn’t yet at a stage where it required the extra space yet. My assumption was confirmed by iostat:

[opc@oracle-19c-fs ~]$ iostat -xmz 5 3
Linux 5.4.17-2102.203.6.el8uek.x86_64 (oracle-19c-fs)   13/08/21        _x86_64_        (16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.98    0.01    1.44    2.35    0.03   95.18

Device            r/s     w/s     rMB/s     wMB/s   rrqm/s   wrqm/s  ... %util
sda              2.32    0.99      0.06      0.03     0.02     0.27  ...  0.17
dm-0             2.16    0.61      0.06      0.03     0.00     0.00  ...  0.16
dm-1             0.05    0.62      0.00      0.01     0.00     0.00  ...  0.02
sdb              0.99    0.20      0.05      0.00     0.00     0.02  ...  0.11
dm-2             0.98    0.22      0.04      0.00     0.00     0.00  ...  0.11
sdc           4538.44   73.12     38.69      4.78   190.85     0.23  ... 26.27
md127         9485.50  147.14     78.09     10.13     0.00     0.00  ...  0.00
sdd           4562.89   73.73     38.90      4.79   193.25     0.04  ... 29.88
sde             15.87    0.00      3.95      0.00     0.00     0.00  ...  1.39
dm-3            15.86    0.00      3.95      0.00     0.00     0.00  ...  1.39
dm-4          9473.71  127.63     74.04      5.46     0.00     0.00  ... 27.74
dm-5             3.63    2.02      3.54      4.07     0.00     0.00  ...  3.21
sdf              0.07    0.00      0.00      0.00     0.00     0.01  ...  0.01
sdg              0.08    0.00      0.00      0.00     0.00     0.01  ...  0.00
md128            0.06    0.02      0.00      0.00     0.00     0.00  ...  0.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           3.96    0.00    5.44    8.52    0.08   82.00

Device            r/s     w/s     rMB/s     wMB/s   rrqm/s   wrqm/s  ... %util
sdc           17652.60  306.80    141.15      2.52   414.40     0.00 ...  88.78
md127         36265.40  608.00    283.35      5.01     0.00     0.00 ...   0.00
sdd           17783.60  301.20    142.17      2.43   411.60     0.00 ... 100.00
dm-4          36267.40  607.00    283.37      4.95     0.00     0.00 ... 100.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           4.20    0.00    5.45    8.82    0.14   81.38

Device            r/s     w/s     rMB/s     wMB/s   rrqm/s   wrqm/s  ... %util
sda              0.00    1.20      0.00      0.01     0.00     0.00  ...  0.04
dm-0             0.00    1.00      0.00      0.01     0.00     0.00  ...  0.04
dm-1             0.00    0.20      0.00      0.00     0.00     0.00  ...  0.02
sdc           18145.40  332.20    143.99      2.55   284.40     0.00 ...  92.22
md127         36865.20  650.20    288.04      5.00     0.00     0.00 ...   0.00
sdd           18161.20  318.00    144.14      2.45   285.20     0.00 ...  99.98
dm-4          36863.20  649.00    288.02      4.99     0.00     0.00 ...  99.98

[opc@oracle-19c-fs ~]$ 

As long as there aren’t any database files in the “extended” part of the LV, there won’t be a change in performance. As soon as your database spills over to the “new” disks, you should see a benefit from the newly added /dev/dm128.

Summary

Just as LVM-RAID does, using software RAID allows you to benefit from striping data across multiple devices. The iostat output is quite clear about the benefit, just look at the figures for /dev/sdc, /dev/sdd and how they accumulate in /dev/md127.

Using software RAID doesn’t come without a risk, it’s entirely possible to lose a block device and thus the RAID device. It’s imperative you protect against this scenario in a way that matches your database’s RTO and RPO.

My main problem with the solution as detailed in this post is the lack of a re-balance feature you get with Oracle’s Automatic Storage Management (ASM). It’s still possible to have I/O hotspots after a storage space expansion.

Building a Debian 11 Vagrant Box using Packer and Ansible

Sometimes it might be necessary to create one’s own Vagrant base box for reasons too numerous to mention here. Let’s assume you want to build a new base box for Debian 11 (bullseye) to run on Virtualbox. Previously I would have run through the installation process followed by customising the VM’s installed packages and installing Guest Additions before creating the base box. As it turns out, this repetitive (and boring) process isn’t required as pretty much the whole thing can be automated using Packer.

Debian 11 is still quite new and a few things related to the Guest Additions don’t work yet but it can’t hurt to be prepared.

As I’m notoriously poor at keeping my code in sync between my various computers I created a new repository on Github for sharing my Packer builds. If you are interested head over to https://github.com/martincarstenbach/packer-blogposts. As with every piece of code you find online, it’s always a good idea to vet it first before even considering using it. Kindly take the time to read the license as well as the README associated with the repository in addition to this post.

Please note this is code I wrote for myself, a little more generic than it might have to be but ultimately you’ll have to read the code and adjust it for your own purposes. The preseed and kickstart files are specifically single-purpose only and shouldn’t be used for anything other than what is covered in this post. My Debian 11 base box is true to the word: it’s really basic, apart from SSH and the standard utilities (+ Virtualbox Guest Additions) I decided not to include anything else.

Software Releases

I should have added that I used Packer’s Virtualbox ISO builder. It is documented in great detail at the Packer website. Further software used:

  • Ubuntu 20.04 LTS
  • Ansible 2.9
  • Packer 1.7.4
  • Virtualbox 6.1.26

All of these were current at the time of writing.

Preparing the Packer build JSON and Debian Preseed file

I have missed the opportunity of creating all my computer systems with the same directory structure, hence there are small, subtle differences. To accommodate all of these I created a small shell script, prepare-debian11.sh. This script prompts me for the most important pieces of information and creates both the preseed file as well as the JSON build-file required by Packer.

martin@ubuntu:~/packer-blogposts$ bash prepare-debian11.sh 

INFO: preparing your packer environment for the creation of a Debian 11 Vagrant base box

Enter your local Debian mirror (http://ftp2.de.debian.org): 
Enter the mirror directory (/debian): 

/home/martin/.ssh/id_rsa.pub

Enter the full path to your public SSH key (/home/martin/.ssh/id_rsa.pub): 
Identity added: /home/martin/.ssh/id_rsa (/home/martin/.ssh/id_rsa)
Enter the location of the Debian 11 network installation media (/m/stage/debian-11.0.0-amd64-netinst.iso):
Enter the full path to store the new vagrant box (/home/martin/vagrant/boxes/debian-11-01.box):/home/martin/vagrant/boxes/blogpost.box    

INFO: preparation complete, next run packer validate vagrant-debian-11.json && packer build vagrant-debian-11.json

One of the particularities of my Packer builds is the use of agent authentication. My number 1 rule when coding is to never store authentication details in files if it can be avoided at all. Relying on the SSH agent to connect to the Virtualbox VM while it’s created allows me to do that, at least for Packer. Since I tend to forget adding my Vagrant SSH key to the agent, the prepare-script does that for me.

Sadly I have to store the vagrant user’s password in the preseed file. I can live with that this time as the password should be “vagrant” by convention and I didn’t break with it. Out of habit I encrypted the password anyway, it’s one of these industry best-known-methods worth applying every time.

Building the Vagrant Base Box

Once the build file and its corresponding preseed file are created by the prepare-script, I suggest you review them first before taking any further action. Make any changes you like, then proceed by running a packer validate followed by the packer build command once you understood/agree with what’s happening next. The latter of the 2 commands kicks the build off, and you’ll see the magic of automation for yourself ;)

Here is a sample of one of my sessions:

martin@ubuntu:~/packer-blogposts$ packer build vagrant-debian-11.json
virtualbox-iso: output will be in this color.

==> virtualbox-iso: Retrieving Guest additions
==> virtualbox-iso: Trying /usr/share/virtualbox/VBoxGuestAdditions.iso
==> virtualbox-iso: Trying /usr/share/virtualbox/VBoxGuestAdditions.iso
==> virtualbox-iso: /usr/share/virtualbox/VBoxGuestAdditions.iso => /usr/share/virtualbox/VBoxGuestAdditions.iso
==> virtualbox-iso: Retrieving ISO
==> virtualbox-iso: Trying file:///m/stage/debian-11.0.0-amd64-netinst.iso
==> virtualbox-iso: Trying file:///m/stage/debian-11.0.0-amd64-netinst.iso?checksum=sha256%3Aae6d563d2444665316901fe7091059ac34b8f67ba30f9159f7cef7d2fdc5bf8a
==> virtualbox-iso: file:///m/stage/debian-11.0.0-amd64-netinst.iso?checksum=sha256%3Aae6d563d2444665316901fe7091059ac34b8f67ba30f9159f7cef7d2fdc5bf8a => /m/stage/debian-11.0.0-amd64-netinst.iso
==> virtualbox-iso: Starting HTTP server on port 8765
==> virtualbox-iso: Using local SSH Agent to authenticate connections for the communicator...
==> virtualbox-iso: Creating virtual machine...
==> virtualbox-iso: Creating hard drive output-virtualbox-iso-debian11base/debian11base.vdi with size 20480 MiB...
==> virtualbox-iso: Mounting ISOs...
    virtualbox-iso: Mounting boot ISO...
==> virtualbox-iso: Creating forwarded port mapping for communicator (SSH, WinRM, etc) (host port 2302)
==> virtualbox-iso: Executing custom VBoxManage commands...
    virtualbox-iso: Executing: modifyvm debian11base --memory 2048
    virtualbox-iso: Executing: modifyvm debian11base --cpus 2
==> virtualbox-iso: Starting the virtual machine...
==> virtualbox-iso: Waiting 10s for boot...
==> virtualbox-iso: Typing the boot command...
==> virtualbox-iso: Using SSH communicator to connect: 127.0.0.1
==> virtualbox-iso: Waiting for SSH to become available...
==> virtualbox-iso: Connected to SSH!
==> virtualbox-iso: Uploading VirtualBox version info (6.1.26)
==> virtualbox-iso: Uploading VirtualBox guest additions ISO...
==> virtualbox-iso: Provisioning with Ansible...
    virtualbox-iso: Setting up proxy adapter for Ansible....
==> virtualbox-iso: Executing Ansible: ansible-playbook -e packer_build_name="virtualbox-iso" -e packer_builder_type=virtualbox-iso -e packer_http_addr=10.0.2.2:8765 --ssh-extra-args '-o IdentitiesOnly=yes' -e ansible_ssh_private_key_file=/tmp/ansible-key610730318 -i /tmp/packer-provisioner-ansible461216853 /home/martin/devel/packer-blogposts/ansible/vagrant-debian-11-guest-additions.yml
    virtualbox-iso:
    virtualbox-iso: PLAY [all] *********************************************************************
    virtualbox-iso:
    virtualbox-iso: TASK [Gathering Facts] *********************************************************
    virtualbox-iso: ok: [default]
    virtualbox-iso: [WARNING]: Platform linux on host default is using the discovered Python
    virtualbox-iso: interpreter at /usr/bin/python3, but future installation of another Python
    virtualbox-iso: interpreter could change this. See https://docs.ansible.com/ansible/2.9/referen
    virtualbox-iso: ce_appendices/interpreter_discovery.html for more information.
    virtualbox-iso:
    virtualbox-iso: TASK [install additional useful packages] **************************************
    virtualbox-iso: changed: [default]
    virtualbox-iso:
    virtualbox-iso: TASK [create a temporary mount point for vbox guest additions] *****************
    virtualbox-iso: changed: [default]
    virtualbox-iso:
    virtualbox-iso: TASK [mount guest additions ISO read-only] *************************************
    virtualbox-iso: changed: [default]
    virtualbox-iso:
    virtualbox-iso: TASK [execute guest additions script] ******************************************
    virtualbox-iso: changed: [default]
    virtualbox-iso:
    virtualbox-iso: TASK [unmount guest additions ISO] *********************************************
    virtualbox-iso: changed: [default]
    virtualbox-iso:
    virtualbox-iso: TASK [remove the temporary mount point] ****************************************
    virtualbox-iso: ok: [default]
    virtualbox-iso:
    virtualbox-iso: TASK [upgrade all packages] ****************************************************
    virtualbox-iso: ok: [default]
    virtualbox-iso:
    virtualbox-iso: PLAY RECAP *********************************************************************
    virtualbox-iso: default                    : ok=8    changed=5    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0
    virtualbox-iso:
==> virtualbox-iso: Gracefully halting virtual machine...
==> virtualbox-iso: Preparing to export machine...
    virtualbox-iso: Deleting forwarded port mapping for the communicator (SSH, WinRM, etc) (host port 2302)
==> virtualbox-iso: Exporting virtual machine...
    virtualbox-iso: Executing: export debian11base --output output-virtualbox-iso-debian11base/debian11base.ovf
==> virtualbox-iso: Cleaning up floppy disk...
==> virtualbox-iso: Deregistering and deleting VM...
==> virtualbox-iso: Running post-processor: vagrant
==> virtualbox-iso (vagrant): Creating a dummy Vagrant box to ensure the host system can create one correctly
==> virtualbox-iso (vagrant): Creating Vagrant box for 'virtualbox' provider
    virtualbox-iso (vagrant): Copying from artifact: output-virtualbox-iso-debian11base/debian11base-disk001.vmdk
    virtualbox-iso (vagrant): Copying from artifact: output-virtualbox-iso-debian11base/debian11base.ovf
    virtualbox-iso (vagrant): Renaming the OVF to box.ovf...
    virtualbox-iso (vagrant): Compressing: Vagrantfile
    virtualbox-iso (vagrant): Compressing: box.ovf
    virtualbox-iso (vagrant): Compressing: debian11base-disk001.vmdk
    virtualbox-iso (vagrant): Compressing: metadata.json
Build 'virtualbox-iso' finished after 13 minutes 43 seconds.

==> Wait completed after 13 minutes 43 seconds

==> Builds finished. The artifacts of successful builds are:
--> virtualbox-iso: 'virtualbox' provider box: /home/martin/vagrant/boxes/blogpost.box

The operations should complete with the message shown in the output – build complete, box created and added in the directory specified. From that point onward you can add it to your inventory.

Happy Automation!

Install the Oracle Cloud Infrastructure CLI on Ubuntu 20.04 LTS

This is a short post on how to install/configure the Oracle Cloud Infrastructure (OCI) Command Line Interface (CLI) on Ubuntu 20.04 LTS. On a couple of my machines I noticed the default Python3 interpreter to be 3.8.x, so I’ll stick with this version. I used the Manual installation, users with higher security requirements might want to consider the offline installation.

Creating a virtual environment

The first step is to create a virtual environment to prevent the OCI CLI’s dependencies from messing up my python installation.

[martin@ubuntu: python]$ mkdir -p ~/development/python && cd ~/development/python
[martin@ubuntu: python]$ python3 -m venv oracle-cli

If this command throws an error you may have to install the virtual-env module via sudo apt install python3.8-venv

With the venv in place you need to activate it. This is a crucial step! Don’t forget to run it

[martin@ubuntu: python]$ source oracle-cli/bin/activate
(oracle-cli) [martin@ubuntu: python]$ 

As soon as the venv is activated you’ll notice its name has become a part of the prompt.

Downloading the OCI CLI

The next step is to download the latest OCI CLI release from Github. At the time of writing version 3.0.2 was the most current. Ensure you load the vanilla release, eg oci-cli-release.zip, not one of the distribution specific ones. They are to be used with the offline installation.

(oracle-cli) [martin@ubuntu: python]$ curl -L "https://github.com/oracle/oci-cli/releases/download/v3.0.2/oci-cli-3.0.2.zip" -o /tmp/oci-cli-3.0.2.zip
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   623  100   623    0     0   2806      0 --:--:-- --:--:-- --:--:--  2793
100 52.4M  100 52.4M    0     0  5929k      0  0:00:09  0:00:09 --:--:-- 6311k
(oracle-cli) [martin@ubuntu: python]$ 

Unzip the release in a temporary location and begin the installation by invoking pip using the “whl” file in the freshly unzipped directory. Just to make sure I always double-check I’m using the pip executable in the virtual environment before proceeding.

(oracle-cli) [martin@ubuntu: python]$ which pip
/home/martin/development/python/oracle-cli/bin/pip
(oracle-cli) [martin@ubuntu: python]$ pip install /tmp/oci-cli/oci_cli-3.0.2-py3-none-any.whl 
Processing /tmp/oci-cli/oci_cli-3.0.2-py3-none-any.whl
Collecting arrow==0.17.0
  Downloading arrow-0.17.0-py2.py3-none-any.whl (50 kB)
     |████████████████████████████████| 50 kB 2.7 MB/s 
...

You’ll notice additional packages are pulled into the virtual environment by the setup routine. As always, exercise care when using external packages. An offline installation is available as well if your security requirements mandate it.

At the end of the process you have a working installation of the command line interface.

Configuration

Before you can use the CLI you need to provide a configuration file. The default location is ~/.oci, which I’ll use as well.

(oracle-cli) [martin@ubuntu python]$ mkdir ~/.oci && cd ~/.oci

Inside of this directory you need to create a config file; the example below is taken from the documentation and should provide a starting point.

[DEFAULT]
user=ocid1.user.oc1..<unique_ID>
fingerprint=<your_fingerprint>
key_file=~/.oci/oci_api_key.pem
tenancy=ocid1.tenancy.oc1..<unique_ID>
region=us-ashburn-1

Make sure to update the values accordingly. Should you be unsure about the user OCID and/or API signing key to use, have a look at the documentation for instructions. Next time you invoke the CLI the DEFAULT configuration will be used. It is possible to add multiple configurations using the old Windows 3.11 .ini file format.

[DEFAULT]
user=...

[ANOTHERUSER]
user=...

Note that it’s strongly discouraged to store a potential passphrase (used for the API key) in the configuration file!

Happy Automation!