Creating an Oracle 26ai database - Parameters and parameter-files.

TL;DR: The minimum amount of parameters for the init.ora when creating an Oracle 26ai database from scripts.

Simple is better: I'll provide just the bare minimum of parameters. The rest is up to you.

Image: looking inside init.ora. I dont really Want to get into too many details, but then sometimes I couldn't resist either.

-- Background : I now have a set of scripts...

It started as a hobby-project: Create a database from "scripts", as opposed to recovering a Template (link). And I want to keep those script as minimalistic and as portable as possible.

So far, I have tested a one-liner create-statement (not ideal, see link), and I have used scripts generated by the DBCA as examples (better, link). I also have re-worked the DBCA-scripts to be more readable, and to be usable on as many (linux-)machines as possible by removing hard-coded references where possible (link to previous post).

Next, I want to examine the init.ora generated from DBCA, and maybe add a few items from myself. 

-- Parameters files: init.ora, spfile.ora, and dont tweak too much.

Any software needs parameters to tell it how to run, where to run, and where to get/put data. For Oracle, that starts with the "parameter file", either an init<SID>.ora or an spfile<SID>.ora. And for a totally fresh database, it will have to be an init.ora, as the spfile.ora can only be created "from pfile" on a running instance. (We can experiment with editing an spfile, but that seems like a pointless, and definitely unsupported way of doing things - Forget it!)

As for how to provide an example of a parameter file, a competing product has the habit of providing a very large, complete, file with All parameters in it. But with the majority commented out so the system will revert to defaults. The administrator can use such a file to inspect the available parameters and uncomment the parameters they like to experiment with.

Years ago, colleagues+me did the same: we provided an init.ora with lots of commented parameters, and hints on what you could do to Set, Tweak and Troubleshoot if you considered that necessary. Such examples did tend to lead to a lot of hobby-tweaking and un-justified obsessions with parameters that supposedly "had worked at previous client".

Hence this post comes with a Warning: Dont Tweak exotic parameters unless you Know What you are Doing.

-- The location of the parameter files.

By default, Oracle looks for the init<SID>.ora file in the directory $ORACLE_HOME/dbs. 

Note: If you opt for a Read-Only Oracle Home (R/O OH), you will have to provide the files in  $ORACLE_BASE_HOME. The container-images I'm using from the Oracle container repository (link) and from Gerald Venzl (the FREE version, link) are equipped to use it.

Both images use links to a dbconfig-directory to provide for databases where the data-files and the accompanying config (init, spfile, pwd) need to be stored on (mounted) volumes outside of the actual container. I've constructed similar setups, way-back-when, to allow VMs and VBox machines to point to files "on the outside".

<small> 

I've been toying with the idea for a R/O OH, or rather the concept of a "golden image" since my early days as a DBA. Partly inspired by the unix/linux concept of FSH (link). Partly because at several customers, we used to deploy images or tarballs, or just "disk clones" rather than run  the installer on every machine. It was  with Oracle18c that our favorite vendor finally started supporting that with roohctl. 

The container-images which I now use both employ symbolic links to place the parameter files "out of oracle home". I might have some comments on both solutions. But that is something for another blog. I must admit that I have not used (tested) an R/O OH in earnest yet. Something else to test/play with. Later.

</small>

For the moment, I'll just use the old-fashioned $ORACLE_HOME/dbs, and pretend not to know that those files are actually at the end of a symbolic link.

-- First contents of the init.ora : db_name and the file-placements.

For the creation of an Oracle database, we have established that the absolute minimum is an init.ora file with the DB_NAME in it, see the very first blog in this series (link).

From my minimalistic point of view, I would prefer to start and run with as little settings as possible in the init.ora. But I found that I need at least to specify some file-destination parameters.

DB_CREATE_FILE_DEST: I consider this one Mandatory. otherwise my files end up in ORACLE_HOME/dbs, as I noticed on my first attempts (link).

DB_RECOVERY_FILE_DEST: When I specify this at create-time, I will get  multiplexed redo-files and controlfiles. (and if you set this: the setting requires a SIZE spec as well, see example below). 

For the CONTROLFILES, I am open to two options: 

a) Explicitly specify, and preferably put them in the create-destination, as shown in the example below. Or...

b) Dont specify, have them as OMF, and even, lazily, multiply them automatically when using the recovery_dest (I like this, the simplest option)

And when I use the OMF option, I have to add them to the init-file just before generating the spfile (for which I got a script, controlfiles_to_init.sql, with the trick copied from the DBCA-generated files, link below...)

The multiplexing is a Good idea, but only if you really need that. For a simple, disposable, test-system, I prefer to heave just a single copy of redo- and controlfiles. I like to squeeze out those extra (milli)seconds of disk-performance for my test-systems which are often containers running on a developer-laptop.

DIAGNOSTIC_DEST is another important parameter. If left out, you diag-data will end up in $ORACLE_HOME/rdbms/log, and I prefer not to have it there. For containers, I have sometimes sent the diag-data to a mounted volume to have it secure when the container dies. At some time I might elaborate on how I create my containers. Maybe Later.

Here is how I create the top-part my init.ora:

You can see how I "cat" everything into a file, starting with the comment for the date+time.

On CONTROL_FILES: If I leave that out (use dflt) the controlfile will end up in the  db_file_create_dest, or in the ORACLE_HOME/dbs. 

For prod- or otherwise important system, you might want to keep at least two copies of redo- and contrlfiles, one of which should be with the datafiles and the other one in the db_recovery_file_dest (really old discussion, still valid).

Note: It may be a good idea to specify controlfiles explicitly also to prevent sprawling them all over the place. Sometimes you find a old, lost, ctlfile somewhere and wonder how it got there...

So, when I use a recovery-dest, I prefer to use OMF for the controlfiles: that way they get created and replicated automatically to the correct locations. And I really should use controlfile_to_init.sql (link below) very early to prevent me from losing the file(s).

Edit: I just noticed I forgot the  ${ENVVAR} braces around one of the variables in the example. Will fix in script.

-- Init.ora: Notes on memory, process and cursors.

In the init-file generated by DBCA (link below), I found settings for memory and process, some of which seemed derived from the host-machine and some were values I had somehow directly or indirectly specified.

(Note to self : play with DBCA more, try out, verify... )

This prompted me to include the following in my own init.ora:

SGA_TARGET: Specified at 1500m, mostly. Because that value will fit just inside the limits for the FREE version of the software. Specify higher if you have the need for more and the correct (non-free) software.

PGA_AGGREGATE_TARGET: specified at 512m, because I found that to be adequate for most of my (lab/test-)systems.

PROCESSES: Specified at 200, because the dflt of 680 is rather high (for a lab/test-system). I dont quite know yet why DBCA specified 320. Something to look into.

OPEN_CURSORS: specified at 300, because the dflt of 50 is very low, and will almost certainly cause problems.

That leads me to have the 2nd part of the init-ora defined like this:

You may notice I keep the REMOTE_LOGIN_PASSWORDFILE to allow connections from elsewhere, if only to connect with SQLDeveloper. Your preference may vary, but for (lab-)testing environments I like to have that option.

Just one Note, or rather a Question to Oracle: Why are some of the dflt parameters still at values that remind me of 1999? If I dont specify them, I end up with very low values for SGA, PGA_TARGET and OPEN_CURSORS. Low values that make my create-database steps take longer. And those low values make the system almost unsuitable, even for simple unit-testing of database-activity.

For serious PROD systems, I would of course alway re-evaluate those parameters,  and probably add a raft of others to configure and tweak my system for whatever purpose I was creating it.

-- init.ora: a word on the default undo-tablespace.

I can leave out the parameter for UNDO_TABLESPACE. 

The default for this parameter will be (link): the first available undo-tablespace, by whatever name. When I realized this, I found I could even omit the undo-tablespace from the create-database. If no undo-ts is specified, the create-stmnt will default to SYS_UNDOTS, which will then be detected as the first available. 

When I did leave out the undo-tablespace from the initial Create Database, I found I had better resize that dflt SYS_UNDOTS. Or it would end up very tiny at 15M and would be subject to a lot of re-sizing during the create process. Hence I might as well create it with my desired size from the start.

Note: Nearly Every database I know out there uses "UNDOTBS1". I wont blame anyone for using this most-common value. But as a minimalist, out of principle, I stuck to the default ;-)

(note to self: try a funny name like OOPS_UNDO). 

-- init.ora: the parameters you may want to keep, as reminders.

With the parameter above, I think I have covered the bare minimum. I did leave out a few from the generated set that you may want to keep, even if they are initially set to default:

COMPATIBLE: was set to, and defaults to 23.6.0, in my current version of 23.26.1.

NLS_LANGUAGE and NLS_TERRITORY: they default to american/america on my systems, and I didnt see the need to specify them unless I would want non-dflt values (as many of you may want).

The original as it was generated by DBCA (link below) contained a few more settings. Have look and tell me what you think of it.  I kinda liked the (c) notice and the early date. But I also think it could do with a few more improvements. Maybe later.

For now, I was happy with the defaults, some which where specified in the generated file anyway. In your case, you may want to keep some of the generated entries, as reminders and as "prompts" for setting them.

-- Round up: The Minimalistic init.ora.

At this point I have a minimalistic init.ora. And I have sufficient comments (and some notes and more ideas) to use this as a generic example-init in my bare-bones create script.

The init.ora inside C008.sh, the create-database script v8, covers the Essentials: 

DB_Name, 

File-placement, 

Memory, 

Remote_pwdfile.

I could insert more examples and comments. I have some parameters I Want to Set myself (... obsessively). But it would grow into a too-large file. 

At this point, I will stop, and I want to leave room for colleagues to insert their own essential-items and their hobby-parameters. A long, somewhat old-fashioned discussion on init-parameters is for another time+place (and drinks). But from what I sometimes find as non-default parameters in spfiles, often stemming from the looong history of some of the databases out there, I would recommend any DBA-team to do something like a "Yearly review of Parameters."

For now, the minimum provided in C008.sh should get most systems started, and anyone can expand on it. I'll go over the lot once more - I still have a few ideas for tests/plays/improvements. Maybe later.

Try it for yourself.

Enjoy.

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

Appendix A: the links to files.

C008.sh : my latest version to generate a database. Try it.

To complete the creation of a database with my C008.sh, you need the following files in the same directory:

2_crdb_catalog.sql : run the stmnts from CreateDBCatalog.

3_crdb_comp.sql : run the scripts for various components.

lock_accounts.sql : lock the default accounts.

4_crdb_pdb.sql : add a PDB.

And some helper- or utility-files:

chk_crdb1.sql : Inspect a database, if needed immediately after creation.

chk_early.sql : Inspect a database, tablespaces, files.

sec_cre.sql : show the numer of seconds since creation (of the CDB) - I use this to get an idea of timing on various platforms.

controlfile_to_init.sql : add the controlfiles, just before "create spfile" 

And, as someone remarked, you need a file accpwd.sql, where you define your passwords for SYS, SYSTEM and PDBADMIN for use in the scripts. I was too lazy to type passwords in all the time. And often you want your scripts to run without human intervention.

For the curious:

initC003.ora : Generated from DBCA, used as my starting point.

And a link to the DBCA-generated source, from which all scripts were re-written, as documented in an earlier blog (link), here is :

C003_omf2.tar : the tarball with generated files from DBCA.

Note: To simplify further, I might roll all those scripts into a single one. One sh-script to create Your Database...  Would that be useful, or is that over-doing it?

Additional: It may be interesting to discuss how I create containers when I want data to survive (container-)crashes. I tend to mount external volumes for config, for data, and for diagnostic-destinations.

-- -- -- -- -- End of this blogpost, for Real -- -- -- -- --

Create an Oracle 26ai database, scripts to add PDBs.

TL;DR: Creating a fresh Oracle 26ai database from scripts: Adding the PDBs to a CDB, and discuss the generated examples from DBCA.

image: the famous analogy of usb-plugins

-- background: I almost have a Database...

The aim is to create a database purely using SQL-statements and portable, re-usable scripts (link to first blog in series). The most simple attempts resulted in databases that were dimensioned way to small. And I still had to include the various scripts for Catalog, Catproc, and the now mandatory components. I had to ask the DBCA for help.

From the DBCA scripts, I have managed to re-write the generated statements to be more generic, more portable. I already got as far as creating a CDB, including the catalog and the various +/- mandatory components (link to previous blog). 

Now it is time to add some PDBs. 

But I have some remarks about the generated plug-scripts.

-- The generated script: lots of actions ? 

When I asked DBCA to generate scripts for a CDB with two PDBs (link), I got 2 x 2  scripts. For each PDB there is a "plug_PDB" and a "postPDBCreation" script, see the ls -l in the screenshot below. The scripts dont look like they were made for readability, but that is not my only remark.

Here is the first part of the generated script that creates the PDB. The empty lines are added by me for readability...

At the top: Should I remark on that spool command, twice ??

But the Create-pluggable command is straightforward, I like the simplicity. It could be done even shorter. As a minimalist, I got it down to:

SQL > Create pluggable database <PDBNAME> 

    admin user <USER> identified by <PWD>; 

Unfortunately, It seems can I not leave out the admin-user clause. But I can re-use the same admin user for every new PDB.

The PDB is created as MOUNTED, and has to be OPENed explicitly. Also, the script will register the PDB immediately with the listener(s) defined in the init or spfile, rather than wait for the max-60sec interval from LREG to do that.

So far so good.

-- Replace the TEMP-orary tablespace ?

The next statements seemed a bit strange to me: First it connects to the new PDB, and that makes sense. Then it selects the CON_ID for the new PDB, and it checks for the BIGFILE property of the TEMP tablespace of the CDB$ROOT. But it does not use that data in any way ? 

Here is the rest of the code (again with white-lines added by me):

There is a little dance to replace a (possibly) existing TEMP tablespace. It does that by adding another Temporary tablespace, then drops and re-creates the (possibly) existing TEMP tablespace for the PDB. And while doing that, it also does CLOSE, OPEN and "register", twice. 

In the end, the PDB is left with exactly the same TEMP tablespace, but now freshly re-created, hence possibly without any ballast or leftover from CDB or SEED? The name of the intermediate temp-tablespace hints at some ENCryption that needs to be added ?

Note-to-self: Find out Why TEMP is replaced.

-- The postPDB actions - USERS tablespace, datapatch, and ... ?

The postPDBcreation looks like this (part 1, white lines added by me): 

The two main actions I detect are 

1) Adding a default tablespace USERS, and 

2) Running datapatch on the new PDB.

The addition of the USERS tablespace is no longer needed here. When I added the creation of "default tablespace users"  into the initial Create Database statement for the CDB, Oracle automatically added a "USERS" tablespace to the SEED as well. From that point on, every new PDB inherited this default-tablespace from the SEED. Hence I could leave it out. Or I can leave it in, and tolerate the error when it happens, just in case someone forgets to add this default tablespace. You choose...

The use of Opatch-datapatch seems recommended, I'll just keep that. As far as I know, datapatch is idempotent, hence it can be run multiple times without negative consequences.

But then it a little gets confusing. Check part 2 of the postPDB script:

The various statements that follow the opatch/datapatch are more mysterious. They seem to only do several verifications (selects...), but I dont really see Why. There are several re-connects, several alter-sessions, and several identical spool-appends. I'll keep the various selects, as they wont hurt ?

And at the end, there are two identical alter-sessions to re-connect to the CDB$ROOT?  Why?

I found this all a bit confusing. Maybe we should ask Oracle for some additional comments at this point.

-- The simplified script: a compromise

From carefully reading both files, plug_PDB and post_PDB, I created one single sql-file: 4_crdb_pdb.sql (link below). You are invited to have a look...

My main change was to remove hardcoded names, and instead use a parameter for the PDB_NAME. I also removed the repeating spool-commands which I thought were unnecessary. 

As a compromise, I kept most of the other statements, including some of the reconnects, the close-and-reopen, the replacement of TEMP, and the alter-system-register commands. 

And I kept the creation of the USERS tablespace, in case I come across a SEED that doesnt have a USERS tablespace yet. 

I still think there are too many comands in the script though. In a later stage, I might try more extensive testing of a smaller, more simple version, notably to avoid the many re-connects. But how often. do you add a fresh PDB to a CDB? In my practice, in most cases, a "new" PDB is a clone or plug-in taken from an existing PBB anyway.

So far, I'm happy with 4_crdb_pdb.sql as it is. The advantage I now have is that my script can be used to quickly add a PDB to any CDB, should I want that. (provided they use OMF. For non-OMF I still need to provide the file-name-convert. But most of mine use OMF. I'm lazy).

In the latest version of script "to create a database", I've added this :

Two simple calls to add two PDBs. Check the script C007.sh in the link below. Go on, Try it.

-- Some Questions on the creation of PDBs.

Q1: Why does the PDB gets created as "mounted"? I suspect there is a (simpole) reason for this, but I havnt seen it, or I have forgotten. I may have to RTFM on this.

Q2: Why the elaborate replacement of the TEMP tablespace? Even if a replacement is desired for some ENCryption reason, this could be done without the reconnects and queries in between ?  (review this? test this?).

Q3: Why are the new Temporary tablespaces of type SMALLFILE ? Is there a particular reason for that ?  (Note: when unspecified, the new tablespace ends up BIGFILE, supposedly because that is specified a default for this CDB)

And I also have a few less burning questions.

Q42: why the explicit close + open + "resister", twice ?? 

Q43: the select-queries to check for con_id and temp-tablespace do not seem to serve any purpose? And they use an upper ( name ), but the pdb-names used are always uppercase anyway, even if initially specified in lowercase ?

Q44: Has anyone managed to create a PDB with a lowercase name ? I know how to create a TABLE with lowercase name, but a PDB??

-- Conclusions so far: We have a Database.

It worked Well. I could use the generated scripts, and create a single, more generic script with a set of commands to create just-any PDB. And the new script Worked Fine as well.

Links  to scripts in appendix below.

But out of all the scripts that the DBCA produced, the two script to create a PDB are the most confusing of the set. They seem to do more actions than striktly required, and the purpose of those actions is unclear to me, as yet.

Should I go on and experiment with Label Security and DataVault? Or first discuss/experiment some init-parameters ? 

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

Appendix: links to files.

C007.sh : The main script that does "create database" and calls the others. 

2_crdb_catalog.sql : run the stmnts from CreateDBCatalog.

3_crdb_comp.sql : run the scripts for various components.

lock_accounts.sql : lock the default accounts.

4_crdb_pdb.sql : add a PDB.

Plus some helper-scripts I have used in between, they are still embedded in the main scripts, just to provide some info.

chk_crdb1.sql : Inspect a database, if needed immediately after creation.

chk_early.sql : Inspect a database, tablespaces, files.

sec_cre.sql : show the numer of seconds since creation (of the CDB) - I use this to get an idea of timing on various platforms.

And a link to the DBCA-generated source, from which all scripts were re-written, as documented in an earlier blog (link), here is :

C003_omf2.tar : the tarball with generated files from DBCA.

-- -- -- -- -- End of this blogpost, for Real -- -- -- -- --

Oracle 26ai Create Scripts: Catalog and Components

TL;DR: The DBCA showed me how to add catalog, catproc, and various components. We are Getting Somewhere.

I'll try to make those scripts more generic, more portable.

Image: puzzle, re-assembling pieces ... ?  (isnt AI just great at generating images? Regardless of prompt, I kept getting an image in "pieces", I guess AI knows what is a puzzle, and assumes to know about "pieces". Sigh....)

-- Background: Portable scripts to Create a Database.

I'm still on my hobby-project to assemble a set of scripts that will create a database for me. Portable scripts that I can use at any (linux) machine where Oracle 26ai is installed. 

I have tried a "naked" create statement (link), with moderate success (link). I have then asked the DBCA to provide me with a set of example-scripts (link) and started tweaking them to make them more generic and portable (link).

In this episode, I want to re-write the set of scripts that do the calls to create the catalog and the various components. Those come just after the actual create.

-- After the initial create: a lot of scripts doing lots of things....

The original scripts generated by DBCA can still be found in the tar-file (link below)

The master-sql script did nothing but call other scripts, see also earlier blogpost. Here are the contents of the generated C003.sql:    

From the generated scripts, I started by picking up the actions being done by the scripts. In the same order:

CreateDB.sql: This does the actual create and in the generated version it adds the controlfiles-parameter to the init.ora so the system can survive a restart or reboot. I have "customized" this script to make it more generic, see previous episode (link).

CreateDBFiles.sql: This adds tablespace USERS. In my case, I have already done this in the main create statement, hence I was comfortable to just assign the DEFAULT tablespace. Notice that I would expect this to be done for SEED and subsequent new PDBs as well ? I checked, it seems it did. So Dflt-Tablespace is taken care of. Less lines in the script(s).

CreateDBCatalog.sql: this script does several things:

- It does alter-session to set "_oracle_script", and bounces the PDB$SEED.

- It runs catctl.pl to apply catpcat.sql against CDB$ROOT and PDB$SEED.

- It then runs 4 scripts via catcon.pl. These commands look like they can be simplified. Only one of those, pupbld.sql, does not use the SYS user. 

Next are a set of generated scripts that all use catcon.pl, all with the same options, to run 1 or more installer scripts. Those are:

    JServer.sql

    context.sql

    cwmlite.sql

    spatial.sql

    CreateClusterDBViews.sql.

Since they all follow exactly the same pattern, I can simplify this. I'll remove hardcoded paths, and remove the hardcoded SID that the DBCA has put there. 

And I may as well benefit from some of the defaults that catcon.pl "assumes" so I can also leave out the credentials (both the -U and -u) and the list of PDBs (the -c).

LockAccounts.sql is next. I mentioned this in a previous episode: repeated runs of same code, can be reduced to a smaller, callable script. Consider that done.

PostDBCreation.sql then does a few things:

- It does datapatch for -d ORACLE_SID (yep, I'll remove the hardcoded SID...)

- It generates an spfile to allow persistence of init-parameters. 

- It runs utlrp.sql to try and compile any invalid objects (via catcon.pl, with the same pattern, but the the DBCA didnt include a -c "list" here. That taught me there was a default: It does All Containers by default anyway.

- It does a select from dba_registry. Why ? 

- It does a shutdown and startup of the instance (always a good check!)

- It does one more select (another check...)

That concludes the the generated scripts, up until the creation of the first PDB.

I specifically asked DBCA to generate (scripts for) two PDBs so I could see how it would work for "multiples". DBCA gave me two pairs of scripts:

Plug_C003p001.sql

PostPDBCreation_C003p001.sql

And similar, near identical scripts for plugin p002... The creation of the PDBs merits a separate discussion. Later. 

-- The scripts can be simplified...

I started by examining the calls to catcon.pl. If you format them, they all look like this one (which I edited into multiple-lines, the original is a one-line):

From reading carefully, plus checking the parameters and their defaults, for catcon.pl, I could bring that back to shorter, more readable commands.

The -U and -u options default to "/ as sysdba", which allowed me to remove most of them. 

The -c option defaults to "all containers", hence could also be left out.

The -l option (log-dir) was pointing to the (hardcoded)script-dir. I made it point to /tmp.

The -n option (nr of sqlplus to spawn). Not sure if I should keep this. But given I only have CDB and SEED, I suspect 2 is ok for now. Maybe investigate later.

And of course, the hardcoded file-paths could benefit from $ORACLE_HOME.

 My other reduction in code was to DEFINE the call to catcon.pl so I could refer to a long command-string (perl, catcon.pl, and the regular options) with one short variable: &&CATCON.

The more readable result looked like this:

First, check the DEFINEs. 

And by also removing the -c, -u and -U options, I end up with nice, short, Portable and Readable statements. A little formatting with spaces also helps.

And thus, CreateDBCatalog got morphed into 2_crdc_catalog.sql.

Next, the scripts for the other components got concatenated into 1 single script, and morphed into 3_crdb_comp.sql.

Links to both in the appendix below. Feel free to inspect and try them out.

Caveat: I realize that by "out-smarting" the DBCA, I am deliberately taking a risk. I can sort-of understand why the DBCA uses hardcoded paths, but it means you can only run that script on a server with identical layout. 

My own Scripts are hopefully a bit more portable, but...: Do Not do this in Prod (yet) unless you Know What you are Doing.

-- Follow the DBCA...

Please also notice that I tried to preserve the ordering of the components and the quirks from DBCA in my own scripts.

I keep the order of the called-scripts the same, in case there is some required sequence of events. The scripts 2_crdb_catalog and 3_crdb_comp also contain comments to indicate where the calls originated from. 

And I have the resulting scripts do an equal amount of re-connects, even though I doubt if that is really necessary.

For the lock_accounts.sql, I verified that both pieces of code, for the ROOT and for the SEED, are literally identical. When I was certain, I reduced it to one single script.

Finally, from the postDBCreation, we copy the call to datapatch and utlrp.sql.  Funny enough the recompile is only applied to the CDB? But in no case did I ever find INVALID object in a freshly created database. I found that reassuring.

At this point, I have a CDB that is ready to receive PDBs. Suffice to say: the generated scripts worked Fine. And they lead to my "simplified" version: 4_crdb_pdb.sql. I'll discuss that one separate, but feel free to test it already, link below.

-- A few more remarks to Note.

NB1: Controlfiles to init or spfile:

If you are using OMF for the controlfile(s) and your controlfile is not yet explicitly named, you need the controlfile-parameter in the init or spfile. That is the time to put the trick from CreateDB.sql back in. It will add that parameter to the bottom of the init.ora. I often run with init.ora for a while before generatign my spfile, but that is probably not Best Practice. You may need something like ctlfiles_to_init.sql which I derived from the generated CreateDB.sql.

NB2: It is a long process, it takes Time, Patience...

Creating or "building" your database from scripts takes time. Because of the wait, I added a very primitive "timer". The (supersimple) script sec_cre.sql will spit out the "seconds since create" to stdout. On older machines: count on 1-2hrs to get to creating your first PDB. On a fast (2026, M5) laptop: It may still be somewhat less then 30min. On an Exadata: Not tested yet (you tell me...). You can see why the DBCA would prefer to restore a template, rather than "create from scripts" 

NB3 on scripting:

The use of $ in names (cdb$root, pdb$seed and various v$views) makes it often tricky to produce a HereDoc with <<EOF. To avoid too many escapes with "\$", I'm keeping some of the sql-code in separate sql scripts instead of adding the code as HereDoc. If I ever go extreme, I'd put it all in one single script, but that will be a bit more effort, and the resulting script would be ... cryptic ? 

NB4: Interesting sql-scripts.

On inspecting some of these scripts and the *.bsq items, I got a sneak-peak into some of the sql-code. It was Interesting. Maybe later.

-- Summary, roundup so far, Consolidated scripts.

It took some reading + thinking, but from the original nine scripts, I got it back to just two main scripts and some helpers, and I got the really long lines back to two more readable ones.

2_crdb_catalog.sql

3_crdb_comp.sql

And some helper scripts: lock_account.sql and sec_cre.sql. Future work will be in a few more scripts: 4_crdb_pdbs.sql (next episode) and maybe a 5_crdb_services.sql.

Most importantly (to me), I got the hardcoded items out of the scripts. These scripts are now easily "portable" and re-usable, notably for the following reasons:

ORACLE_SID: The Only point to define the SID of the database-to-be-created is now the basename of the main script.

ORACLE_BASE and ORACLE_HOME are used instead of the hardcoded-paths. It is your task to make sure those variables are set correctly ;-)

So to create a new CDB, all you need to do is to copy the most recent script, say C006.sh to YOURCDB.sh and you are ready to create it. Link below. Do Test and let me know....

In future versions, I would consider to remove some of the (hardcoded) values from the init.ora and replace them with more ${VARIABLE} items. Notably file-paths for various destinations could be ENV variables or could be defined at the start of the main script.

And, call me obsessed, but there are also a few v$parameters left that point to $ORACLE_HOME/rdbms/log and I will probably try to alter those, just out of habit.

But first, in the next episode, adding PDBs.

And as I keep scripting, all of these may still evolve, but the current versions are usable. You can already Try them out, and let me know...

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

-- Appendix 1: Links to scripts and files.

C003_omf2.tar : the original generated script from DBCA (with fix for the missing arguments to context.sql...)

C001.sh : my original example, a single, self-contained script to create a database.

C006.sh : my main create-script (for now)

2_crdb_catalog : add the contents of CreateDBCatalog.sql

3_crdb_comp : the other components, from DBCA-scripts.

lock_accounts.sql : single script for repeated action

4_crdb_pdbs : add a PDB (not finished at time of this writing...)

sec_cre.sql : Timing script to report "seconds since create" to stdout. I was too lazy to deduce this from alert-file, or object-timestamps.

ctlfiles_to_init.sql : Add the Controlfiles to the init.ora.

If you put those scripts in a directory, and your ENV is set correctly, any of the <SID>.sh will create a database. Try it and let me know...

A note on Naming of the scripts: I called the scripts 2_crdb_catalog.sql and 3_crdb_comp.sql. With the "2" and. "3" indicating the 2nd and 3rd script to run. The nr-1 script was originally the create-database, 1_crdb_create.sql, but I included that as HereDoc into the <SID>.sh to allow for easy use of $ORACLE_SID and some quick-repeating test and experiments.

My scripts evolve over time. In current situation the main script, SID.sh (currently C006.sh) calls the others one by one. You can and should adjust them as you please.

Do Experiment!

There will of course be 4_crdb_pdb and maybe even 5_crdb_services. Later.

-- -- -- -- -- End of this blogpost, for Real -- -- -- -- --

PS: Except for the nerd-images, no AI-generated content was used in writing this blogpost...

Experimenting with DBCA-scripts of Oracle 26ai: Create Database.

 TL;DR: I've got generated scripts from DBCA (see previous blogs), and I experiment with them. First I morf the initial Create-statement and script into something that is easy to test+repeat. I learn to clean up my own mess. And then I discuss the actual "create database".

image: Creating, Testing, and cleaning up the pieces.

I needed a thorough cleanup-routine to really delete all artifacts of a database for repeat of various tests (and it is not perfect yet...)

-- Background: Got scripts from DBCA, now tweak and re-use them.

My end-goal is still to obtain scripts that can create a fresh database. Scripts that I can take to any (linux) system where oracle is installed, and Create a Database. 

It is still a "Hobby Project" and I would not (yet) recommend to take this to your PRODuction system. But anyone should be able to take the scripts and modify them without too much hassle wherever they see fit.

I will use a base-set generated from the DBCA, under the assumption that those are "complete and usable". I already have created a database from them. I will now morf the scripts into something that I can easily re-use.

For portable, generic, scripts, I want first to avoid hard-coded paths. Furthermore, I want "minimalistic" parameters, minimal code, and use defaults wherever I can. 

Of course I want scripts that I can quickly adapt to different CDB-names, add PDB-additions, and modify them for various "purpose databbases". 

And dont forget: I need a cleanup-system in case my tests need repeating or go horribly wrong: some easy way to "wipe clean". On containers, I can always just re-create the container, but on actual servers I may need to do a cleanup.

-- The Create script: define ORACLE_SID and make it re-runnable.

My first attempts worked OK, and I was able make the very basic statement work:

    SQL > Create Database C001;

See previous blogs (link) for the story. But I needed more.

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 blogs on how to generate - (link to generate) and my initial comments on the generated scripts (link to remarks).

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

In this blogpost, I will use the generated "create" script, and experiment with the very start of the process: the Create itself. Slowly, I'm developing a more generic, less "hardcoded", more portable version of the scripts.

Originally, the DBCA provided a script that calls everything: C003.sh. I've re-worked that to a sh-script where the base-name is the SID. I expect to "test" this a lot, so I better make it easily-runnable and re-runnable.

I came up with C004.sh as a main-controlling script for my next database:

This way I grab the SID, upper and lowercase to access the subdirs in diagnostic-dest. And I will consider to define items like DATA_DEST, BASE, HOME, and others as well at the top of the script.

Next is the "editing" of the init-ora file. This can happen inside the sh-script to allow the DB_NAME and a few other parameters to be set. Let's use an <<EOF for that:

I'll just create the init file on the fly, from the same script. Keep everything in one place for now. A more detailed discussion of the init-file contents will have to wait. Maybe later.

-- The actual Create Database, adjusted from the DBCA.

After just a little formatting, I inserted the statement from CreateDB.sql in my script:

This statement is still the same one as generated by DBCA, but it uses the ${ORACLE_SID} instead of the hardcoded value. Otherwise it is un-altered (as of yet). I have used this in various tests to try some variations.

Once I could easily re-run this statement, I started experimenting with some modifications.

-- Initial sizing of datafiles and logfiles.

From looking at the previously created C003 (previous blog), I know that the nr of "Resize operations" during create was 200+. Mostly because the initial fileSIZE and the NEXT specifications were too small, especially on the SEED-datafiles. I'll add larger values to the next version of the create-statement. Check C005.sh linked below.

For the redo-log files, I preferred 500M size or even larger to reduce log-switches during busy times. I think there is very little downside to Large redo files. If you worry about delay between log-switches, or delay in archive-generation: use an archive_lag_target and re-think your backup- or DG-settings. Maybe Discuss some other time.

I also managed to include size-specifications for the SEED: system and sysaux, both to eliminate the resize-ops during the running of catalog and other scripts, and to allow future sizes should someone want that. All for the next version of the script. 

The qualification: Extent Management Local: if I leave that out, the SYSTEM tablespace becomes DICTIONARY managed. Not a big problem, but rather old-skool. If you want to have your SYSTEM tablespace as LMT, you have to specify this explicitly. 

I suspect I know the reason why the default for SYSTEM is still Dictionary: If you have older databases with Dict-magaged tablespaces that you want to plug in, you need the SYSTEM of the CDB to be Dictionary managed as well (note: maybe check this, Dict-managed is really old...). 

-- Various MAX-values, affecting the Controlfile - Let's test.

I was slightly worried when I noticed the MAX-parameters in the generated script.  You can find them via V$CONTROLFILE_RECORD_SECTION. In older versions, those MAX-values used to be imposed as "Limits" and sometimes bit you when you didnt expect. Anyone who ever had to "re-create a controlfile" under stress will remember. Nowadays, they mainly determine the space that gets "reserved" in the controlfile, and the space will "expand" automatically when needed. 

I did a quick test: 

First I re-created a database with no MAX values specified and I checked the values in V$CONTROLFILE_RECORD_SECTION using the script chk_ctlrecords.sql (link below).

Those initial, default values look a little low to me. Could that pose a problem?

(Anyone spot the anomaly? something to look into? Maybe Later)

So I created a shell-scrpt to add redo-files, add threads and add tablespaces (e.g. datafiles). I tried to "cross those limits" and see what happened with this test: add_files.sh (link to script below).

And after adding a load of files, I re-check the values:

The values have simply gone up. No problems encountered. That looks Good to me.

Actually, if you have run the catalog script, the alert-log will contain a few messages about expanding the ctlfile:

Oracle silently "Expanded the controlfile" and you were Informed via the alert-log. NB: Without a catalog, that message shows up as an Error with a mention of some missing object - feel free to check for yourself.

From reading the RTFM, you may want to keep the MAXLOGHISTORY if you are running RAC. Maybe something for another test.

My first conclusion is: Those MAX values are there for information, but they dont seem to pose a problem anymore. And I will remove them to simplify my create-statement a little further.

-- Leave out some more options...? Maybe Not.

Since I like "minimal scripts", I have considered to also leave out the spec of SYS and SYSTEM passwords, and the Character-sets.

When I left out the Char-set spec, I ended up with US7ASCII. Not a good idea. 

I found I could leave out the National Character set as it seems to defaults to AL16UTF16 anyway, but I decided to keep it in as reference. Feel free to change those at your convenience... 

You could also leave out the SYS and SYSTEM IDENTIFIED BY. But the RTFM says you will then revert to the very old and well known defaults. To make it look good to your CISO and SecOps ppl, just specify them.

In my case, I usually have the passwords defined in a file that I name "accpwds.sql". During create-time: it mimics the sqlplus-accept command to define the pwds. Just dont upload that file to any git-repo, and dont stick with those values for too long. Hmm, Maybe I'll also mention in a later blog that some of the scripts still can display the pwd-value in the proces-listing, try "ps -ef" to check for yourself.

-- Cleanup, because of all the failed or insufficient (test-) runs...

While building and testing the scripts, and experimenting with the rest of the generated scripts, I often ran into error where files already existed (example:.... ).

Hence I needed a (semi-automatic) cleanup-script as well. This is slightly risky to have laying around: if you accidentally execute that script, it Wipes your Database. Hence semi- and not totally automatic.

The cleanup scripts, an -.sh and an -.sql, find all possible files from the v$ views (the dba_% views only come in after the catalog is created), and generate the rm-commands. They also try to clean up the diag-dest and other file-destinations. Links below.

Feel free to re-use, but be careful. This is not something you want to happen by accident (yes, this happened to me once, in summer of 1996. And I still remember 30yrs later, colleagues HenkG and YuriB, and some customer-ppl will also remember....).

-- Some Questions for Oracle...

Non-critical, but out of curiousity, I will probably try to ask Oracle-folks at some time about the following Items...

1. LMT/Dictionary: Why is the dflt for SYSTEM tablespace still DICTIONARY ? (presumably for compatibility with Really Old tablespaces that need to be plugged in?)  

2. Sizing: Why are the initial tablespaces (SYSTEM, SYSAUX, TEMP, and SYS_UNDO) created with such small values for SIZE and NEXT? They are Very Small on the "naked" create-statement (see previous blog, link) but even on the generated-scripts the values are still low and generate a lot of "resize" events in the alert-log?.

3. Controle-file contents: Why are the MAX-values still specified when these are (probably) no obstacles to a growing databases anymore ? Leaving them out, at least in the generated scripts, would make the create-stmnt more simple, elegant? 

More questions ? dflt charsets? dflt-passwords? maybe later...

Round up, my Create Database is somewhat improved (simplified)

Quite Happy with the new script: it works for me. Have a look at C005.sh (link below)

I have the init.ora and the create-statement in One Single place. I can now easily create databases with other ORACLE_SID-values. And I can add all the other items from the DBCA-generated code such as catalog, scripts for the various components,  and the creation of pluggable-databases. Stay tuned as I continue exploring.

The "cleanup" script to remove files is a little risky, but has proven very useful.

I also know now that some of the definitions at create-time are not Set in Stone (maxfiles...), and I feel confident that I can run with the defaults, rather than having to enter defensively large values at Create-Time. If needed, Oracle will automatically "increase the max" of some of them and the alert-file will show the expand-messages.

Next episode, I want to add the Catalog and run all the other scripts that the DBCA listed. I will try to simplify them a little. Notably to remove some of the hardcoded setting like SID and PATHs.

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

Appendix 1: Links to scripts and files.

C001.sh : my first example, a single, self-contained script to create a database.

C003_omf2.tar : the generated script from DBCA (with fix for the missing arguments to context.sql...)

C004.sh : a shell script with my "first improvements", runnable. test-able

chk_ctlrecords.sql : list part of the v$contrlfile_record_section.

add_files.sql : Add redo-files, redo-threads and tablespaces to expand controlfile.

mk_rmall.sh : generate rm-script and shutdown-abort the instance.

mk_rmall.sql : sql-file is needed by the mk_rm_all.sh  script (for now...)

C005.sh : my "final" create-statement (for now)

-- -- -- -- -- End of this blogpost, for Real -- -- -- -- --

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 -- -- -- -- --

Creating a Database: Ask DBCA to help you...

TL;DR: The scripts generated by DBCA work, although some tweaks and caveats apply. But in the end: Yes. They Work!

image: Startup of the DBCA from Oracle 26ai (to generate scripts). 

NB: Running X11 windows in XQuartz on a mac does not always yield nice screenshots, and sometimes the display(-driver) seems to get in the way, see this previous blog (link). Hence I used a camera to snap the screens. With apologies for the image-quality.

-- Background: Scripts to create a database (and using X11).

Since I got on the quest of "Creating a Database from Scripts", I've been moderately successful. I can do:

SQL > Create Database ;

and get a more or less running RDBMS. 

The next steps would be to install things like catalog, catproc, and various (mandatory) components. 

I also found the initial sizes of datafiles and redologs rather small, leading to a lot of messages in the alert-file about significant resource- and time-consuming file operations. 

And maybe I want those fancy "bigfile" tablespaces from the get-go.

Because I didnt feel like ploughing through the RTFM for all the details (link to create database), I asked the DBCA to generate the scripts for me: The Oracle Provided "Assistant" would know how to do this the correct way, right ?

In earlier attempts (link to 2023), when there was only the v23 "FREE" version available, the DBCA would not offer this option (yet). I had to revert to the scripts generated from v21c and adjust them. That worked at the time, but I was never 100% sure if I had everything covered correctly. Now that the version 26ai is General Available, I can do this properly, with the correct version of DBCA. 

This current hobby-project / research was done mostly with v23.26.1 in May 2026 to be precise. And I would expect to re-do this with the version that is supposed to drop any time now and re-verify the findings.

Note however: I am still striving for the "simplest possible scripts", preferably scripts that I can port to any (linux) environment where Oracle 26ai is installed.

-- How I generated the scripts.

My main platform for experimenting are the container-images provided by the oracle container-registry (go into the enterprise-database section - link). I also use/test on the "FREE" images by GVenzl (link). The FREE version does not permit (yet?) to "generate scripts". Hence to re-play this blog, you need the proper Oracle containers. But I do tend to test any generated script on the FREE containers as well.

The DBCA is a gui-tool, running on X-windows (X11). Hence first I had to get X-windows running in my container. I've described that in an earlier blogpost (link)

I'm assuming the vbox-versions still come with a working X11 installed, havnt verified that recently. In case the X11 is missing in your vbox: the install from  previous blog applies to the vbox images as well.

I repeated + tested some of this work on the "FREE" containers provided by Gerald Venzl. Those images are completely hassle-free, license-free, and are very easy to pull and run. Check them if you need something for testing, or any other activity where you quickly need 1 or more database(s) to spin-up.

Without too much ado (see earlier blog), yum was "correctly configured" and I could install X11. I also often use yum pick up a number of other goodies like procps, rlwrap and git. 

Once you get X11 and have "the eyes" (or xterm) running from inside the container, you are Good. 

Let's run the DBCA...

-- Running DBCA, and choosing "generate scripts" 

The screenshot above shows the DBCA (lowercase), and we opt to create a database. On the next screen, we choose "Advanced":

There we go. And once in the advanced mode, we choose "custom":

Custom seems to be the only option that really generates "Create scripts". The other options just generate "recover-scripts" that recover (bakups of) templates.

For my exercise, I accepted a lot of the defaults. I didnt want to experiment much yet. But feel free to explore.

I picked a name (C003), I chose "Local Undo" and I wanted two PDBs:

Two PDBs, just to see how it would handle more than one. Basically to get a nice set of scripts to work with.

For storage: I preferred to choose OMF.

For now, OMF. Reading the RTFM and some experience convinced me that OMF is much more convenient than managing the files myself. But you may have a different opinion, especially if you do not like funny and overly long names (GUIDs) for directories.

In the almost-final screen, I un-ticked the "create", and only ticked the "generate scripts". The actual creation can take quite some time, and I just wanted the scripts:

We do not need the database created for us (yet), but we want the scripts to study and to tweak. So we continue to "finish" and ... Viola.. (yes, Viola, an old aquaintance).

We obtain the scripts in /opt/oracle/admin/C003/scripts:

Those are the scripts that can "Create a Database".

-- Runing the scripts : C003.sh does it all (with 1 fix)

The complete set of script can be run form one shell script: C003.sh. I'll put up a link to a tar-file so you can see for yourself. Feel free to Test:

mkdir /opt/oracle/admin/C003

cd /opt/oracle/admin/C003

tar -xvf /tmp/C003_omf2.tar

cd scripts

./C003.sh 

... and watch the miracle unfold.

Running the script(s) can take up to an hour, or even longer, depending on the speed of your hardware. On an old MBair from 2018, running the container in docker, it takes 2hrs. But on a beefy MBP M5 only about 25min.

Beware: because of bug 37280585, it needs a tweak in the script context.sql: You need to add 4 arguments to the call to catctx. I've fixed that in the tar-ed scripts, so you can just run them on your testbed if you want to try. 

If you do test it: Let me know what you find on your system: Any issues? How many directory-paths did you have to edit? How long did the running take ?

-- First Inspection: looks OK. But some comments...

After creation, I did some inspecting, notably with scripts chk_crdb1.sql and chk_early.sql. From the initial checks, I noticed several things:

I now have (mostly) BIGFILE tablespaces, by default, as confirmed by my self-created tabelespace "ABC".

The sizes of file and tablespaces are much larger than in my previous blogs about "create database" (link). But there are a still quite a lot of "resize" events in the alert-log. I can probably fix that.

All components are VALID, except RAC: that was expected. The container-image I use is not suited for RAC. NB: XDB is Deprecated.

There were no Invalid Objects in the generated database.

So far I am moderately happy with the scripts. And they will serve as "examples" for me to simplify a little more. I'll probably discuss them in some follow-ups. Ultimately, I want to end up with something more "portable": a set of scripts I can easily run on any linux machine to create a database.

-- Roundup so far - We have Scripts. Let's tweak them (in next blogs)

The scripts worked. And I feel confident that I can work with them. I have a lot of remarks though:

1) Those scripts will only work on the machine (or platform or container) that generated them or a system with a Very Similar layout. The SID and the file-paths are hardcoded in many places. I would like some more Generic, Portable version. I might come back to this in next blogs.

2) The scripts are not very readable (to me). I would reformat and re-write a few parts before I was more happy with them (next blogs...). More trendy kids would hand them to an AI and ask for suggestions. I just used eyeball-mark-one, some common sense from hard-gained experience, and some of my "personal preferences".

3) For some of the initial configurations (the init.ora, and the initial sizes of files) I would do some things differently myself. I'll tweak some and blog it..

4) File-paths: I'm choosing OMF notably to avoid/reduce hard-coded paths in the scripts. Note that if we choose "storage location from template", all scripts will contain hardcoded file-paths which makes the scripts less "portable" as we would have to edit those paths for every new database we want to create. Reading the RTFM and some experience convinced me that OMF is much more convenient than managing the files myself. But you may have a different opinion, especially if you do not like funny and overly long names (GUIDs) for directories. 

There is more. Later.

But despite all my stubborn comments, I'm already a happy bunny with those scripts.

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

Appendix: Links to files.

C003_omf2.tar.gz : the generated scripts (with fix for bug 37280585). Try them !

chk_crdb1.sql : script used in previous create-experiments.

chk_early.sql : additional script for 1st check of a newly created database.

-- -- -- -- -- End of this blog, For Real -- -- -- -- --