Creating an Oracle 26ai database from a Single Script - Cool.

TL;DR: The One Script you will Ever Need to create an Oracle 26ai Database. And for you to configure, should you want to.

After a remark from a colleague, I created this un-necessary, but Very Satisfying script: All-In-One. And I added some "minor" checks and improvements to the previous (multi-script) versions.

You can use this single script to create your CDB database and/or add PDBs. You can also review, adjust, and configure the various parts before running them. Hence you can create Any Oracle 23ai database, on Any (linux) Server with minimal effort.

Why? Partly from hobby, partly from a challenge by a critic, partly bcse it was just Fun to Do.

However....

First I ended up with too many components, too many scripts... 

Hence, after some hard scripting-work...

Now I just carry one file to do the job.

-- Background: "Too many scripts" ?

(you can skip "background" + "why" if you are in a hurry)

I've written this many times: I want Simple, Portable, Readable, universally Usable scripts. And to Create a database, I took inspiration from the DBCA, but I amended (simplified) the generated scripts a bit. See Earlier Blogs (link to start of series).

And then a colleague @ cstmr said: Why do I need to download so many files? And your generic-script-system fails if just one of those files is missing....

Note: at this cstmr they tend to either request access to some schema or they just pull the free-version from gvenzl (link) and run their own. Creating a database by yourself from scratch is considered a bit last decade...

Of course I have a git-repo with my own scripts as well, but it contains even more scripts. That repo is such a chaos that I didnt even want readers to clone it as I tend to keep older verions of my experiments available as well.

All while doing real-life work-stuff, that remark about a Single Script stuck with me. And I got thinking about Why + How. Here is the Result...

-- Scripts: Why again...

I was +/- succesful after my experiments with the "minimalistic create database", I ended up with a collection of portable scripts for the various sub-tasks. And creating separate scripts made sense to me. I created separate scripts for the following reasons:

- To eliminate hard-coded references to names and paths - Achieved.

- To eliminate repeating pieces of code - Mostly Achieved.

- To use as helper-scripts (accpwd, lock_accounts, and 4_crdb_pdb) - Achieved.

- To monitor, to check state and timing (chk_xzy.sql, sec_cre.sql) - Achieved.

And the additional reason why I appreciate separate these script is something I would call Compartmentalization and Transparency. Each script has its own function, a Specific Job to do. From 1_create via 2_catalog and 3_components to 4_pdbs... 

Also, I can examine and adjust the individual scrips at will.

I especially wanted to maintain that capability for "Inspection and Tweaking", and at first I did not want to complicate life by rolling it all into one script. But the "Idea" was planted. 

Also, in the process of writing those blogs/notes, I had created probably about 100 databases with various versions of the scripts (over a period of months...). With all those "test runs" I had come up with quite a list of notes, mostly included in the comments of the scripts. Some of those notes also merited attention. You will find that there are at least three ToDo lists inside the script still...

-- First part: Generate the scripts, and offer them for Review.

So I went ahead and crafted the One Script anyway and here is the result: C009.sh. (I'm open to suggestions for a better name, but the numbering suits me for now...)

This means you now only need one script to download. That One Script will generate the others.

But with a twist: No Overwriting. If a certain script (filename) already exists, it will be left untouched. This allows you to put your own edits and configs in the scripts and keep those.

And once the main script has detected or generated all the components (a matter of seconds), you can stop it to review the parts before you run them. You can thus edit any of the generated components. And the next run from the same directory will preserve your modifications (unless you removed the generated file...).

Once you are happy with all the components, you can make it "run the lot" and have them create your database.

This "trick" has several benefits:

1) Only one single script to use, but

2) Review and Edit of pieces is possible before the actual run. And

3) The SQL scripts remain separate.

It may seem trivial, but having the SQL-scripts separate keeps them more readable because there is no need to "escape \$" and the set-echo works as expected. This seems like Details. Some ancient SQL-script kids will understand. For me turned out to be very practical.

-- Let's demo...

To start the create-database actions, I rename the script C009.sh to NEWCDB.sh. This rename is the only place where I have to "edit" the new SID name.

I then run the script to let it start the magic....

First the script will tell me about its intentions: It wants to create a database and displays BASE, HOME, SID, host and a timestamp. I can hit enter...

In the example above, the necessary files are already present from an earlier run, located in $ORACLE_BASE/admin/$ORACLE_SID/scripts. The "ls -l" will show me how old these files are, and I pressed "nope <enter>" to allow myself to inspect + edit them.

For example, when not running on the FREE version, I can increase the memory-parameters to claim more memory (and thus run a little faster). Or I can adjust the file-destinations where my data will be placed. See comment in the init.ora.

Once I was happy with the sripts, I re-ran the NEWCDB.sh to start the actual create and I gave it the <enter> to go ahead and create the database.

It started up the instance, using the (generated, edited) init.ora. And then it can proceed with the Real Work:

I still like to see that "Database created" ...  This one took 31 sec.

Once you let it to run.. It will take a while depending on you hardware, but in the end you will end up with a CDB and (by default) two PBs. The end should look similar to this, when the script chk_crdb1.sql (generated!) lists some items:

Notice how I created two PDBs: FREEPDB1 and ORCL.

The script does a final query to show the installed components: 

select comp_id, status from dba_registry;

In most cases, all components except RAC will be installed. Note that on the "slim" version of the FREE-containers, some of the create-scripts for java component is missing. When using the "slim" image, you should probably only use the pre-installed (running) database and refrain from creating your own.

-- why so "complicated" ?

I had a few alternatives to take the scripts back into "one main script". 

Firstly, I could execute all commands sequentially. This would lead to a very long script, with a lot of repeated parts. And it would also mean to escape some shell-variables and a lot of v$views with \$VAR and V\$VIEW. Those slashed are not good for readability or maintainability.

Alternatively, I could create functions inside the script and call them to avoid repeating code. Most of those functions would become here-docs, which still meant  careful escaping all kinds of \$VARS and V$VIEWS, still not ideal for readability.

In both cases, the reading and the maintenance of that rather complicated script would become more difficult than keeping the separate scripts that each have a clear task.

So to (more of less) satisfy all parties, I rolled the various scripts into the main one, and then have them Generated when needed, and make them available for "Inspection" before running them.

For myself, I will use the new+improved main script (currently C009.sh) to "generate" the parts and I will probably tweak them. And every now and then, I will roll some of my tweaks and edits back into the main script.

-- Minor improvement: Detect "SID already running"

Sort of speaks for itself. Prevents a lot of messy errors, and prevents possible corruption of existing database. Minor issue. Yeah. Sure.

-- Minor improvement:  Use proper, separated, admin-directories.

Up to now I didnt care about the location of my scripts and files, as most of my tests were hobby/evolving/throw-away runs. But I started to realize that the scripts and the create-logs Do Merit a private directory so the files from different databases remain separate (and do not overwrite eachother ;-) ).

You will now find the essentials after each run in their proper location:

$ORACLE_BASE/admin/$ORACLE_SID/scripts/

In some next version, I should probably also change my current use of /tmp for the output of catcon and catctl. At the time I just wanted to get rid of that huge load of stdout from those programs.

-- "Minor" improvement: ORACLE_SID in UPPERCASE.

When trying out the scripts, I re-discovered that it is just Impractical to have a lowercase SID. I also remember wrestling with that years ago as well. I dont want to go into the details, but at some point I just decided to get it over with and specify Mandatory UPPERCASE for the SID.  

For example (in v26ai):

select * from global name;  Yep, always Upper.

select sys_context ('userenv', 'DB_NAME') from dual;  hmm, lowercase if so specified...

select name for v$database;  Again, always Upper.

Select instance_name from v$instance;  Surprise, lower if so specified.

And there are a number of other places where I found the upper/lower to be inconsistent or confusing.  Maybe a topic for future-blog, or future beer with an Oracle-Veteran: Why didnt Oracle just Force Upper Case as well...?

For PDBs, the issue is already fixed: Uppercase. And I can recommend reading this short blog by Daniel, as he tell you to not push your luck when it comes to naming...

If you feel adventurous: Tweak the script and try the following SIDs on a single machine: ORCL, Orcl, orcl. Then add OrcL and a few other combinations. Enjoy. Thank me later.

To avoid future confusion for now: I just forced UPPER.

-- Remaining lists of "Things ToDo" : Many.

If you look inside the script, you will find many ToDo remarks, some of them still scattered throughout. Those will have to wait. Or remain for others to fix if they feel the need. 

Hence feel free to send me comments, suggestions or criticism.

The item high on my list used to be "cleanup". As I created many test-databases over the last months (for this blog-series alone, probably 100+) and thus I got many test-runs of of the create-script(s), I began to feel the need to "Clean Up" properly: remove datafiles, but also admin-files, $OH/dbs-files, auditing, diag- and trace-files... The Lot. 

And if you are like me, dont pay attention to details and set only the absolute minimum parameters, you end up with overlooked files in several locations. That may be worth a blog all by itself (see e.g. info07_files.sql, maybe later).

But also please note: If you have no exotic requirements and/or just want Efficient "test databases", spin up some containers (link to gvenzl/oracle-free). That is much faster.

-- Summary: One Script... Cool.

After a lot more tweaking than I had anticipated (several evenings...), I did end up with a Single Script. Not quite done yet, not as Elegant as I had wanted yet, but already Happy with the results.

If you take the One Script, you wont have to carry around all the other utilities anymore. But you maintain an easy way to tweak the generated scripts, should you want to.

And while creating the "one script", I have included a few minor improvement, notably to prevent the script from running against an in-place database (and potentially making a big mess).

As for the name... maybe some day I'll call it ONE.sh ?

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

Appendix: That One Script...

C009.sh : The One Script You Need. It is not small, but it is One Single Script. And it provides both scripts for reading- and tweaking, and the Ability to (automatically) Execute.

Try it.

Do Examine the generated scripts...

Run the generated scripts...

Let me know what you think.

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