Oracle 26ai DBCA scripts that (help you to) create a database

TL;DR: Still on my quest to Create an Oracle 26ai Database From Scripts. I'll examine some of the resulting scripts from DBCA.

Image: Critical inspection of a gift, by a geek.

Note: Do not take this too serious. My day-job is more practical (and more meetings). This blog-series is more of a trip back to "the roots of the database". But maybe I learn a few things while I'm digging.

-- Background: Scripts from DBCA, much more complete than my own.

The end-goal is to have scripts that can create a fresh database. My first attempts worked OK, and I was able make the statement "create database" work. Check here (link) for the story.

But to obtain a complete, usable database with all the required components, I asked for the help of the DataBase Create Assistant. With the proper choices the DBCA from v26ai was able to generate the scripts. And those scripts created a brand new database, including all the "components". Report in Previous blog (link).

Those scripts can still be fetched from the tar file, link below.

In this blog, I will examine the generated scripts, make some comments, and start thinking about my own adaptations.

-- The main scripts: C003.sh and C003.sql

The script that runs everything is C003.sh. It creates the necessary directories, sets some env-variables, and kicks off the next script, C003.sql:

The main SQL scripts just calls a set of other, generated, scripts who then do the real work. They are all very similar, and I'll discuss them briefly with some comments and suggestions. 

Note that I already put in a call to "@accpwds.sql" to define the passwords, and thus to avoid having to type into the accept-prompts every time - I'm lazy.

-- The init.ora: Interesting.

Every startup needs an init or an spfile. The one generated by DBCA is interesting on a several points.

Some of the contents of init.ora.

The DB_NAME is specified, and this is Needed. From experimenting, we know that the only parameter required in the init-file is the db_name. Without the db_name, the database wont get created (ora-01502, missing or illegal db_name). 

The DB_CREATE_FILE_DEST is specified, because I told the DBCA to use OMF. From experimenting, I think I can say that only the db_file_create_dest is really needed to determine file-placement.

For testing, and any other "discardable" database, I would probably run without the RECOVERY_DEST parameters, just to save space and capacity and time. Especially with docker-on-laptop situations as myself and some DEVs do.

The DIAGNOSTIC_DEST should probably be set explicitly. If not set, it will point to either ORACLE_BASE or ORACLE_HOME/rdbms/log (where I would not want it). When running in containers, I used to set this value to a mapped volume to have the output always accessible, notably on "broken container". But I am not sure if the extra IO-calls from container to host have an impact. And if they do, whether it is worth the (small?) extra load.

I have a few more (geeky old-skool) remarks on the init.ora that was generated from the DBCA, but I'll save those, maybe for a separate blog sometime (insert future link here). 

The only other thing I want to point out here is the (c) notice : Isnt that a little old ? 

-- The Create-DB, and the specified properties.

The generated statement for "create database" from DBCA is much more elaborated than the one liner that I used for my first experiments (link). Here is the generated file CreateDB.SQL which does the startup (nomount) and then creates the database: 

First thing that struck me: I would do a different, more readable layout. But that is a detail (but will come back in many of the generated files, readable layout was not a priority?).

Notice how the DBCA has taken a much more "controlling" approach when compared to my earlier simple 1-liner:

 - A number of MAX-values specified. They come back in the view  V$CONTROLEFILE_RECORD_SECTION. I'll probably comment on those later.    

 - Explicitly ask for LMTs, via "extent management local" - OK with me.

 - Explicit SIZE and NEXT for the datafiles. Good, this reduces the "resize events" during create, and will make it faster on slow-disk systems (laptops running docker...)

 - Explicit BIGFILE as default, as would be expected from the RTFM anyway?

 - LOGFILEs are now 200M, and larger than the dlft 50M I got in my first attempt. This reduced the number of log-file-switch events during the next create-steps. I tend to make them larger still.

 - Explicit creation of an UNDO tablespace (which may still grow during creation).

 - Explicit Character-set values.

 - ENABLE PLUGGABLE DATABASE, which seems to be the dflt as well.

 - Specify LOCAL UNDO ON - I was a little surprised to find that the dflt for this setting is still "off", but that would make sense for a CDB with many (small) PDBs.

What strikes me as odd is that several of the explicitly specified values seem to be the (current) default settings anyway. In a more simple version of Create Database, I can leave them out and obtain the same result (should test, verify...)

But having them specified Explicitly has educational value: it tells the DBA/user what is happening, should anyone ever read this file...

Note that there is no explicit sizing for the Tablespaces and Datafiles for the SEED datatabase yet. The datafiles for seed (system and sysaux) tend to have a Lot of resize-ops, which can be prevented by creating them larger.

Another remark: I'm not terribly happy with the way the control-files get added to the init-ora file. That went wrong a few times when I used my own SQL*plus sessions. I think I prefer to set the value for control_files in the init.ora myself.

All in all this looked like a usable starting point to do some adaptations of my own.

-- Scripts following after the create: a lot of work gets done.

After the CreateDB.sql, the main script continues and calls some 10+ other SQL scripts to "complete the work". Most of the names are +/- self-explanatory. The contents of those scripts are all similar, and I'll show one:

I've added empty lines between the long, wrapping, host-commands to make it more readable... 

All scripts follow the same pattern:

Firstly they connect as SYS and do a spool-append although some scripts do the spool first, then connect. And some scripts repeat the spool-command multiple times.

Secondly, they all use host-calls to catcon.pl to run scripts that are located somewhere in $ORACLE_HOME. The one exception is the Catalog-script: it uses one call to catctl.pl, but the pattern is the same. The ancient programmer in me would like to simplify this layout.

About the re-connects: Some of the scripts re-connect several times. I am still not sure whether those re-connects are really needed, to create "fresh" connections, or whether they just come from some old code (template, legacy?). But in combining and simplifying the scripts, I dutifully included the re-connects: A fresh connection would never hurt...?

What I didnt like about them: The very long command-lines, the often repeating-pattern, and the hardcoded file-paths. It produces confused-looking code. I'll try to simplify some of that in future versions of my own scripts.

-- Locking users, could be a callable script.

A bit of a no-brainer, to me, was the lockaccount.sql: It does the Exact Same Code on CDB$ROOT and on PDB$SEED (I checked with diff). That can be extracted into a separate script to run twice. Why didnt oracle do that yet? But then again: that is a very small "optimization for the sake of optimizing".

-- Adding PDBs: Repeatable actions, Scriptable.

The creation of the PDB(s) is another repeating pattern. But this activity may have to be repeated several times, whenever a new, fresh, PDB is required.

Each requested PDB came with two scripts: to Create the PDB, and to do the postPDBCreation actions. The scripts differ only in PDB-name and spoolfile-name. And the last script has an "exit" at the end, supposedly to force an exit from SQL*Plus.

Here are the contents of one of the CreatePDB scripts:

The PDBCreate scripts first do the acutal Create. And then some swapping of the Default Temporary tablespace. From the naming of the TEMP tablespaces, it assumes that the original TEMP from the SEED is not (yet) ENCrypted.

The postPDBCreate scripts add a USERS tablespace, and do a datapatch. After that, it just seems to do several re-connects and selects on properties, tablespaces and registry. Not sure why, maybe just to check on the contents of various views (note: Investigate Why those reconnects and queries are done).

I see no reason why the creation of a PDB could not be "scripted" into a single, re-usable script that takes the pdb-name as argument.

Note: in my case, I often add two small PDBs: ORCL and FREEPDB1. Doing this allows scripts that count on those "Traditional names" to connect to known-targets (dont laugh... there are script out there with scott/tiger@orcl still in them). Alternatively, I have sometimes just created services by those names.

-- Round up, and possible follow ups.

Quite Happy with the scripts, They Worked. But I had some comments.

My first comment was the often less than readable layout.

Then there is the fact that they contain hardcoded paths, and are thus only usable on the machine where they were generated, or a system with identical layout. This may be on purpose. Oracle wants to be very sure the paths and files are correct, working, and point to software of correct version (e.g. the perl-version delivered with the same software, and not some other version that happens to be installed)

And some scripts look like they could do with a little work: why do they contain identical-repeating statements ? (probably a quirk from the generating-software).

Next point of attention was the fact that some of the "settings" for the init.ora and the create-statement were actually Defaults. They seem to act as reminders: You Can Set Something Here (charsets, storage-options). I might experiment with these parameters and properties.

The Really Positive outcome is: I have scripts that seem to create a "complete" database: The DBCA has done the RTFM work for me, packed all the components, and provide a Good starting point to compose my own, more "portable" scripts.

My aim is still to obtain scripts with clean, minimal code, and use dflts wherever possible. And then hopefully test-run that on various machines at my disposal.

-- -- -- -- -- End of this blogpost, for now -- -- -- -- --

-- Appendix 1: Links to scripts and files.

C001.sh : my initial attempt, a single, self-contained script to create a database.

C003_omf2.tar : The generated set of scripts from DBCA (with fix for the missing arguments to context.sql...)

-- -- -- -- -- End of this blogpost, for real -- -- -- -- --