Cucumbers, coffee and chocolate or how to create non-cdb on Exadata Cloud at Customer.

For those who are puzzled by the title here is a short explanation. I didn’t pay too much attention to what I had in my fridge and one day I found only a couple of cucumbers, chocolate and some coffee. That was not too bad but I couldn’t call it a proper nutrition diet. It was at the same time when I was exploring a possibility to have a non-cdb 12.1 Oracle database on an Exadata Cloud at Customer (ExaCC). One might think the blog is about comparing the unusual diet with the non-cdb deployment on a cloud environment telling that you should not really use non-cdb as you probably shouldn’t eat only cucumbers, chocolate and coffee. But it is not true, the blog is how to create such non-cdb on an ExaCC.

Oracle documentation for ExaCC says “Each database using Oracle Database 12c Release 1 or a later release is configured as a container database (CDB). ” Of course you probably can create a container database and then manually recreate it using dbca utility or scripts as non-container. But I was looking for a more efficient and appropriate way to do that. After some digging I found that non-cdb databases were indeed supported and could be created using standard ExaCC tools. 

First we needed to add an Oracle database home image to the ExaCC from the software library using the dbaascli utility. Here is what we had in the library:

[oracle@exacc01 ~]$ sudo dbaascli cswlib list
DBAAS CLI version 19.4.4.2.2
Executing command cswlib list
 
INFO : Log file => /var/opt/oracle/log/list/list_2020-09-10_10:24:40.158254345269.log
INFO : dbimage fixup executed.
 
############ List of Available BP #############
-JUL2019 (For DB Versions )
-OCT2019 (For DB Versions  11204 12102 12201 18000 19000)
-JAN2020 (For DB Versions  11204 12102 12201 18000 19000)
-APR2020 (For DB Versions  11204 12102 12201 18000 19000)
-JUL2020 (For DB Versions  11204 12102 12201 18000 19000)
 
############ List of Available NONCDB BP #######
-JUL2019 (For DB Versions )
-OCT2019 (For DB Versions  12102 12201 19000)
-JAN2020 (For DB Versions  12102 12201 18000 19000)
-APR2020 (For DB Versions  12102 12201 18000 19000)
-JUL2020 (For DB Versions  12102 19000)

We could see that the non-cdb version of 12.1.0.2 has several bundle packs versions including the latest bundle patch. The next step was to put the image to the ExaCC directory on ACFS. I used the same dbaascli utility for that.

[oracle@exacc01 ~]$ sudo dbaascli cswlib download --version 12102 --bp JUL2020 --bp_update no --cdb no
DBAAS CLI version 19.4.4.2.2
Executing command cswlib download --version 12102 --bp JUL2020 --bp_update no --cdb no
INFO : Download running for BP JUL2020 and version 12102, it may take few mins...
INFO: CSWLIB update NCJUL2020 db image bits
INFO: Log file is: /var/opt/oracle/log/misc/cswlib/cswlib_2020-09-10_10:26:30.345137349058.log
INFO : dbimage fixup executed.
INFO: CSWLIB update_bits of NCJUL2020 12102 succeded !
INFO: dbaascli dbimage activateBP needs to be run to make the downloaded BP the ???default??? !
 
[oracle@exacc01 ~]$ sudo dbaascli dbimage list
DBAAS CLI version 19.4.4.2.2
Executing command dbimage list
 
INFO : Log file => /var/opt/oracle/log/dbimage_list/dbimage_list_2020-09-10_10:35:55.032959381422.log
INFO : dbimage fixup executed.
 
################## List of Available dbimages on acfs  ###################
 
-APR2019 (For DB Versions 11204, 12102, 12201, 18000, 19000) => 22G consumed on acfs
 
######################## Default BP on acfs #############################
 
-11204 => APR2019
-12102 => APR2019
-12201 => APR2019
-18000 => APR2019
-19000 => APR2019
 
################## Acfs disk space usage ######################
 
 Filesystem              Size  Used Avail Use% Mounted on
/dev/asm/acfsvol01-291  1.1T  393G  728G  36% /acfs01
 
################# /var/opt/oracle/dbaas_acfs/dbnid space usage ###################
 
 29G
[oracle@exacc01 ~]$

As soon as image was there we could try to make it active (default). I could include the clause to do that to the “cswlib download” command but decided to run it separately.

[oracle@exacc01 ~]$ sudo dbaascli dbimage activateBP --version 12102 --bp JUL2020 --cdb no
DBAAS CLI version 19.4.4.2.2
Executing command dbimage activateBP --version 12102 --bp JUL2020 --cdb no
INFO : Log file is => /var/opt/oracle/log/dbimage_activateBP/dbimage_activateBP_2020-09-10_10:50:31.50136628763.log
INFO : dbimage fixup executed.
INFO : activateBP completed

But when I checked again the list of images it looked like the non-cdb image was still not default.

[oracle@exacc01 ~]$ sudo dbaascli dbimage list
DBAAS CLI version 19.4.4.2.2
Executing command dbimage list
 
INFO : Log file => /var/opt/oracle/log/dbimage_list/dbimage_list_2020-09-10_10:50:48.17518129280.log
INFO : dbimage fixup executed.
 
################## List of Available dbimages on acfs  ###################
 
-APR2019 (For DB Versions 11204, 12102, 12201, 18000, 19000) => 22G consumed on acfs
-JUL2020 (For NON CDB Versions 12102) => 6.3G consumed on acfs
 
######################## Default BP on acfs #############################
 
-11204 => APR2019
-12102 => APR2019
-12201 => APR2019
-18000 => APR2019
-19000 => APR2019
 
################## Acfs disk space usage ######################

Anyway, I tried to create database from OCI console and see how it would work.

The database has been created successfully but when I looked to the new database in the OCI I noticed that it was not using the new non-cdb image with the latest patch. It was the same old patch from 2019.

And indeed when I logged to the database it was again the standard container database with one default PDB inside.

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

I double checked the logs to make sure my “dbimage activateBP ” command completed successfully and it looked correct.

2020-09-10 11:18:05.146438 - INFO : Log file is => /var/opt/oracle/log/dbimage_activateBP/dbimage_activateBP_2020-09-10_11:18:05.126034139047.log
2020-09-10 11:18:05.147157 - INFO : dbimage fixup executed.
2020-09-10 11:18:05.158078 - INFO: /var/opt/oracle/dbaas_acfs/dbnid/db11204_bits_EXA.zip is symlink
2020-09-10 11:18:05.158318 - INFO: /var/opt/oracle/dbaas_acfs/dbnid/db12102_bits_EXA.zip is symlink
2020-09-10 11:18:05.158482 - INFO: /var/opt/oracle/dbaas_acfs/dbnid/db12201_bits_EXA.zip is symlink
2020-09-10 11:18:05.158649 - INFO: /var/opt/oracle/dbaas_acfs/dbnid/db18000_bits_EXA.zip is symlink
2020-09-10 11:18:05.158786 - INFO: /var/opt/oracle/dbaas_acfs/dbnid/db19000_bits_EXA.zip is symlink
2020-09-10 11:18:05.166358 - Output from cmd unlink /var/opt/oracle/dbaas_acfs/dbnid/NONCDB/db12102_bits_EXA.zip run on localhost  is:
2020-09-10 11:18:05.177730 - cmd took 0.010516881942749 seconds
2020-09-10 11:18:05.178137 - Output from cmd unlink /var/opt/oracle/dbaas_acfs/dbnid/NONCDB/exadbf_12102.tar.gz run on localhost  is:
2020-09-10 11:18:05.185052 - cmd took 0.00657081604003906 seconds
2020-09-10 11:18:05.185558 - Output from cmd ln -s /var/opt/oracle/dbaas_acfs/dbnid/NONCDB/JUL2020/db12102_bits_EXA.zip /var/opt/oracle/dbaas_acfs/dbnid/NONCDB/db12102_bits_EXA.zip run on localhost  is:
2020-09-10 11:18:05.197854 - cmd took 0.011944055557251 seconds
2020-09-10 11:18:05.198213 - Output from cmd ln -s /var/opt/oracle/dbaas_acfs/dbnid/NONCDB/JUL2020/exadbf_12102.tar.gz /var/opt/oracle/dbaas_acfs/dbnid/NONCDB/exadbf_12102.tar.gz run on localhost  is:
2020-09-10 11:18:05.203248 - cmd took 0.00438904762268066 seconds
2020-09-10 11:18:05.204144 - Output from cmd chown -R oracle:oinstall /var/opt/oracle/dbaas_acfs/dbnid/NONCDB run on localhost  is:
2020-09-10 11:18:05.224291 - cmd took 0.0194790363311768 seconds
2020-09-10 11:18:05.224682 - INFO : activateBP completed

Still the new image didn’t become active and it was showing the old image as the default one.

################## List of Available dbimages on acfs  ###################
 
-APR2019 (For DB Versions 11204, 12102, 12201, 18000, 19000) => 22G consumed on acfs
-JUL2020 (For NON CDB Versions 12102) => 6.3G consumed on acfs
 
######################## Default BP on acfs #############################
 
-11204 => APR2019
-12102 => APR2019
-12201 => APR2019
-18000 => APR2019
-19000 => APR2019
 
################## Acfs disk space usage ######################

Then I dropped the database and tried to recreate it using the dbaasapi utility and put explicitly “NONCDB” to the options. I created a json file like this:

[oracle@exacc01 ~]$ cat /home/oracle/working/create_non_cdb.json
{
    "object": "db",
    "action": "start",
    "operation": "createdb",
    "params": {
        "nodelist": "",
        "cdb": "no",
        "bp": "JUL2020",
        "dbname": "testdb03",
        "edition": "EE_EP",
        "version": "12.1.0.2",
        "adminPassword": "VerySecretPassword",
        "charset": "WE8ISO8859P1",
        "ncharset": "AL16UTF16",
        "backupDestination": "NONE"
    },
    "outputfile": "/home/oracle/working/createnoncdb.out",
    "FLAGS": ""
}
[oracle@exacc01 ~]$

After running dbaasapi with the created input file.

[opc@exacc1 ~]$ sudo dbaasapi -i /home/oracle/working/create_non_cdb.json
[opc@exacc1 ~]$

The database was successfully created and it was non-cdb database.

SQL> SHOW pdbs
SQL> SELECT name,dbid,con_dbid,cdb,con_id FROM v$database;
 
NAME		DBID   CON_DBID CDB	CON_ID
--------- ---------- ---------- --- ----------
TESTDB03  2993373418 2993373418 NO	     0
 
SQL>

In the OCI web console we could clearly see that it was using the image with the latest patch. If you’re reading from the phone and it is not visible for you it says “Database Version: 12.1.0.2.200714”

So, we can create a non-cdb database on the ExaCC and perform all standard operations with it. Please keep in mind that only 12.1 and 19c are supported for non-cdb deployments with the latest bundle patch. If you want a 12.2 or 18c you probably need to use some non-standard ways to make it.  Hope this short blog can help somebody in their work. and save some time. And, by the way, for those who are curious about cucumbers and chocolate, I still think that using non-cdb databases on an ExaCC is a bad habit. ExaCC is a consolidation platform and using non-cdb limits its ability to reduce overhead. Keep in mind that you can deploy only a limited number of databases per CPU.

Exadata Cloud at Customer – number of active CPUs and adding a new database.

Let’s imagine a typical working day, and you are getting a request to add a new database to your Exadata Cloud at Customer (ExaCC). If you are not familiar with the product, you can read about it in detail here. In short, it is an Exadata machine with a cloud interface, something like Oracle Exadata Cloud Service, but with the hardware installed in your datacenter. 

You carry on with the request, fill up all the information on the database creation page, and push the button “Create Database”. 

Everything looks correct, and you’ve probably done it several times already and don’t expect any surprises. But somehow, after a while, you are getting notification that your request has failed, and the database was not created.

When it happened the first time to me, I was disappointed by the lack of any useful information in OCI console and logs. I was expecting a bit more than just “failed due to an unknown error”.

Here is starting a quick troubleshooting part. I went to the logs on the VM cluster and, after some research, found the real reason why the request had failed.

 root@exacc01:~>tail -16 /var/opt/oracle/ocde/ocde_createdb_testdb03.out
Removed all entries from cfg file : /var/opt/oracle/ocde/ocde_createdb_testdb03.cfg matching passwd
 
ERROR : rac: _is_cpu_count_ok, cpu_count 8 is not enough for running_dbs_count 20. Please increase the number of cpus.At a minimum we should be one vcpu per two DBs.
ERROR: OCDE createdb pre-reqs failed, please check logs
corereg: secure: Wallet location is not defined, securing corereg means losing all credentials.
INFO: corereg: secure: Removed all entries matching passwd or decrypt_key from corereg file /var/opt/oracle/creg/testdb03.ini
INFO: Total time taken by ocde is 8 seconds
OCDE failed with message: ERROR: OCDE createdb pre-reqs failed, please check logs
 
 
INFO : ocde_time_format is 2020/04/05 09:06:05
OCDE failed with message: ERROR: OCDE createdb pre-reqs failed, please check logs
 
 
 
#### Completed OCDE with errors, please check logs ####
root@exacc01:~>

Going a little bit deeper to the Oracle ExaCC tools, we can find the Perl module “rac.pm” and the “_is_cpu_count_ok()” function there. That function compares the number of CPU to the twice of the number of running database instances. As a result, we have the hardcoded limit of available container databases on the ExaCC, which is bound to twice of number of OCPU for the VM cluster.

We can apply two workarounds for the issue. The first is to scale up the number of OCPU on the ExaCC. You don’t have to keep them all the time. You can burst it up only for the database creation and then scale down to the original number.

The second workaround is to shut down one of the existing databases for the duration of the creation of a new one. I don’t think it is the best course of action, but it might be acceptable if you know that some databases can be stopped at certain times.

The summary is short. We have a hardcoded limit of 2*OCPU for the databases, which can be solved in a couple of ways. And the level of logging in the OCI interface is not adequate, and you need to dig into the logs by yourself or create an Oracle support SR to get the real cause of the error.