ODA 19.12: How can you manage DB Systems with odacli?

Introduction

As virtualization is available for all ODAs now, you could ask yourself if it’s a good idea to use this virtualization or not. Databases can be created as bare metal, but also within a DB System for better isolation. And both can be used on the same ODA.

If you’re looking for how to deploy an ODA with KVM virtualization, please have a look at my previous blogpost here.

What is a DB System?

A DB System is an OCI (the public Cloud from Oracle) feature brought to the ODA. This is a single component that includes a VM with a Linux OS, a DB home and a database. On ODA, you don’t really need that because databases are most of the time configured as bare metal ones (running on the server itself), but it’s now possible to configure DB System as well. Appart for better isolation (for example, separating PROD from DEV databases on the same appliance), this is also needed for network segregation: it’s possible to associate different DB Systems to different VLANs. And if you need to tweak a database, it does not impact the other DB Systems.

The drawbacks I could see are for sure more complexity and more systems to manage. Use bare metal only if you want to keep it simple.

Creating a DB System

For creating a DB System, you will need a template (provided aside the 19.12 patch), and corresponding GI and DB clones:

cd /opt/dbi
unzip p33152237_1912000_Linux-x86-64.zip
odacli update-repository -f /opt/dbi/odacli-dcs-19.12.0.0.0-ODAVM.zip
odacli describe-dbsystem-image
DB System Image details
--------------------------------------------------------------------------------
Component Name        Supported Versions    Available Versions
--------------------  --------------------  --------------------

DBVM                  19.12.0.0.0           19.12.0.0.0

GI                    19.12.0.0.210720      19.12.0.0.210720
                      21.3.0.0.210720       not-available

DB                    19.12.0.0.210720      19.12.0.0.210720
                      21.3.0.0.210720       not-available

Let’s use 19c version in this example, as 21c is an innovation release.

A DB System will need a CPU pool (it can be shared across multiple DB Systems) and a json file with various parameters (mine is provided at the end):

odacli create-cpupool -n cpupool4dbsystems -c 4 -dbs
odacli create-dbsystem -p /opt/dbi/create_dbsystem_srvdb01.json

DB System creation lasts 30 minutes, and this one has an odb2 shape.

Note these 2 points:
– The shape for the VM and for the database need to be the same, or you will get an error

DCS-10045:Validation error encountered: The DB System shape and Database shape should be the same.

– Multiple DB Systems cannot have the same database name. It would normally be possible, but not on ODA.

DCS-10044:Object dbName already exists with same value VIRTDB.

odacli to manage DB Systems

You can list the DB systems configured on your ODA:

odacli list-dbsystems

Name                  Shape       Cores  Memory      GI version          DB version          Status           Created                  Updated
--------------------  ----------  -----  ----------  ------------------  ------------------  ---------------  -----------------------  -----------------------
srvdb01               odb2        4      16.00 GB    19.12.0.0.210720    19.12.0.0.210720    FAILED           2021-11-25 16:04:05 CET  2021-11-25 16:22:15 CET

And as DB Systems are VMs, stop and start commands are available :

odacli stop-dbsystem -n srvdb01
odacli start-dbsystem -n srvdb01

You can also have extended information about a DB System, for example its current status:

odacli describe-dbsystem -n srvdb01 | grep State
             Target State:  ONLINE
            Current State:  ONLINE

Another example, the shape of the VM and database:

odacli describe-dbsystem -n srvdb01 | grep Shape
                    Shape:  odb2
                    Shape:  odb2

You can change the shape of a DB System (but only going up is allowed):

odacli modify-dbsystem -n srvdb01 -s odb4

odacli list-dbsystems
Name                  Shape       Cores  Memory      GI version          DB version          Status           Created                  Updated
--------------------  ----------  -----  ----------  ------------------  ------------------  ---------------  -----------------------  -----------------------
srvdb01               odb4        4      32.00 GB    19.12.0.0.210720    19.12.0.0.210720    CONFIGURED       2021-11-24 19:02:04 CET  2021-11-25 14:58:49 CET

Your DB System will need a restart to correctly apply the shape to the database.

Basically, you can also change network settings and cpu pool association.

For sure you can also delete a db system (after stopping it):

odacli delete-dbsystem -n srvdb01

odacli inside the DB System

The purpose of a DB System is to host a single database. odacli is then limited inside a DB System.

So what can we do? Displaying information about database and dbhome is OK:

odacli list-databases

ID                                       DB Name    DB Type  DB Version           CDB        Class    Shape    Storage    Status        DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
37b7becf-7697-4ea8-9dd7-d91cfb36444a     VIRTDB     SI       19.12.0.0.210720     false      OLTP     odb4     ASM        CONFIGURED   7916c735-1503-4fb4-b414-3dcdb4c68517

odacli list-dbhomes

ID                                       Name                 DB Version                               Home Location                                 Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
6dabc902-cc0e-4fa6-82a7-9fa1f6361599     OraDB19000_home1     19.12.0.0.210720                         /u01/app/oracle/product/19.0.0.0/dbhome_1     CONFIGURED

Creating a new dhbome is also OK, but not creating a database:

odacli create-dbhome -v 19.12.0.0.210720
odacli list-dbhomes

ID                                       Name                 DB Version                               Home Location                                 Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
6dabc902-cc0e-4fa6-82a7-9fa1f6361599     OraDB19000_home1     19.12.0.0.210720                         /u01/app/oracle/product/19.0.0.0/dbhome_1     CONFIGURED
b1a0bfd9-5db5-4bce-8e4d-49f07480cc4e     OraDB19000_home2     19.12.0.0.210720                         /u01/app/oracle/product/19.0.0.0/dbhome_2     CONFIGURED


odacli create-database -u VIRTDB2 -dh 'b1a0bfd9-5db5-4bce-8e4d-49f07480cc4e' -n VIRTDB2 -s odb1 -r asm
DCS-10001:Internal error encountered: Available Memory is less than SGA Size { Available : 796mb and SGA Size : 7782mb }.

There is not enough memory for another instance, let’s decrease the shape of the previous database and retry:

odacli modify-database -i 37b7becf-7697-4ea8-9dd7-d91cfb36444a -s odb1

odacli list-databases

ID                                       DB Name    DB Type  DB Version           CDB        Class    Shape    Storage    Status        DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
37b7becf-7697-4ea8-9dd7-d91cfb36444a     VIRTDB     SI       19.12.0.0.210720     false      OLTP     odb1     ASM        CONFIGURED   6dabc902-cc0e-4fa6-82a7-9fa1f6361599

Let’s retry:

odacli create-database -u VIRTDB2 -dh '3a6e44cc-6f22-4696-b84f-55affdc5e09b' -n VIRTDB2 -s odb1 -r asm
odacli list-databases

ID                                       DB Name    DB Type  DB Version           CDB        Class    Shape    Storage    Status        DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
37b7becf-7697-4ea8-9dd7-d91cfb36444a     VIRTDB     SI       19.12.0.0.210720     false      OLTP     odb1     ASM        CONFIGURED   6dabc902-cc0e-4fa6-82a7-9fa1f6361599
3270f020-afd6-4658-a029-754b48832d3b     VIRTDB2    SI       19.0.0.0             false      OLTP     odb1     ASM        FAILED       b1a0bfd9-5db5-4bce-8e4d-49f07480cc4e

Actually, it does not work because of an internal error:

odacli describe-job -i ecdda655-cbac-4782-aa88-f6a6b5eb5960 | grep Message
                Message:  DCS-10001:Internal error encountered: Failed to create the database VIRTDB2.

Let’s remove the first database and create another one, it may work:

odacli delete-database -i 37b7becf-7697-4ea8-9dd7-d91cfb36444a
odacli delete-database -i 3270f020-afd6-4658-a029-754b48832d3b
odacli create-database -u VIRTDB2 -dh '404a58c6-913e-4c6d-bdb1-f0f99b684fe2' -n VIRTDB2 -s odb1 -r asm
odacli list-databases

ID                                       DB Name    DB Type  DB Version           CDB        Class    Shape    Storage    Status        DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
6f2ec94b-7a79-4ea4-9d0a-d6fc613ddcfa     VIRTDB2    SI       19.12.0.0.210720     false      OLTP     odb1     ASM        CONFIGURED   b1a0bfd9-5db5-4bce-8e4d-49f07480cc4e

Yes it works. But the shape looks like odb4, not odb1:

[root@srvdb01 ~]# su - oracle
Last login: Wed Nov 24 18:44:18 CET 2021
[oracle@srvdb01 ~]$ . oraenv <<< VIRTDB2
ORACLE_SID = [oracle] ? The Oracle base has been set to /u01/app/oracle
[oracle@srvdb01 ~]$ sqlplus -s / as sysdba
sho parameter sga_

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
sga_max_size			     big integer 7792M
sga_min_size			     big integer 0
sga_target			     big integer 7792M
unified_audit_sga_queue_size	     integer	 1048576

Managing databases on a DB System is not very clean. You can do things, but I would not recommend.

Features linked to the database created during provisioning are OK, for example applying a backup configuration for your database:

odacli create-backupconfig -n nfsBackupConfig -w 7 -d NFS -c /backup/srvdb01

odacli modify-database -in VIRTDB2 -bin nfsBackupConfig

odacli list-schedules | grep -e VIRTDB2 -e ID -e ---
ID                                       Name                      Description                                        CronExpression                 Disabled
---------------------------------------- ------------------------- -------------------------------------------------- ------------------------------ --------
86211dc2-af0c-4e21-a8b6-58df85d0fd1f     database_backup_6f2ec94b-7a79-4ea4-9d0a-d6fc613ddcfa backup database : VIRTDB2                          0 24 5 1/1 * ? *               false
e49d5605-d1f2-47ea-9d6e-f266fb5d724b     archive_log_backup_6f2ec94b-7a79-4ea4-9d0a-d6fc613ddcfa backup archive logs : VIRTDB2                      0 0/30 * ? * * *               false

Backups are now scheduled for my database on this DB System, with a 7-day retention on a nfs share.

Conclusion

DB Systems should be seen as single objects and managed at the DB System level mainly. If you need to change something locally on the database it’s still possible but odacli may not help.

Demo json file

create_dbsystem_srvdb01.json

{
    "system": {
        "name": "srvdb01",
        "shape": "odb2",
        "systemPassword": "**********",
        "timeZone": "Europe/Zurich",
        "diskGroup": "DATA",
        "cpuPoolName": "cpupool4dbsystems",
        "enableRoleSeparation": true,
        "customRoleSeparation": {
            "groups": [
                {
                    "name": "oinstall",
                    "id": 1001,
                    "role": "oinstall"
                },
                {
                    "name": "dbaoper",
                    "id": 1002,
                    "role": "dbaoper"
                },
                {
                    "name": "dba",
                    "id": 1003,
                    "role": "dba"
                },
                {
                    "name": "asmadmin",
                    "id": 1004,
                    "role": "asmadmin"
                },
                {
                    "name": "asmoper",
                    "id": 1005,
                    "role": "asmoper"
                },
                {
                    "name": "asmdba",
                    "id": 1006,
                    "role": "asmdba"
                }
            ],
            "users": [
                {
                    "name": "grid",
                    "id": 1000,
                    "role": "gridUser"
                },
                {
                    "name": "oracle",
                    "id": 1001,
                    "role": "oracleUser"
                }
            ]
        }
    },
    "database": {
        "name": "VIRTDB",
        "uniqueName": "VIRTDB",
        "domainName": "dbi-lab.ch",
        "adminPassword": "********",
        "version": "19.12.0.0.210720",
        "edition": "SE",
        "type": "SI",
        "dbClass": "OLTP",
        "shape": "odb2",
        "role": "PRIMARY",
        "targetNodeNumber": null,
        "enableDbConsole": false,
        "enableUnifiedAuditing": true,
        "redundancy" : "MIRROR",
        "characterSet": {
            "characterSet": "AL32UTF8",
            "nlsCharacterset": "AL16UTF16",
            "dbTerritory": "AMERICA",
            "dbLanguage": "ENGLISH"
        },
        "rmanBackupPassword": null,
        "enableTDE": false,
        "isCdb": false
    },
    "network": {
        "domainName": "dbi-lab.ch",
        "ntpServers": ["21.39.35.10"],
        "dnsServers": [
            "8.8.8.8","8.8.4.4"
        ],
        "nodes": [
            {
                "name": "srvdb01",
                "ipAddress": "10.36.10.242",
                "netmask": "255.255.255.0",
                "gateway": "10.36.10.1",
                "number": 0
            }
        ],
  "publicVNetwork": "pubnet"
    },
    "grid": {
        "language": "en"
    }
}