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.