Minimalistic Creation of an Oracle Database, v23c

  TL;DR: To create a v23c database "manually", with Minimal effort and minimalistic scripts, here is how I did it.

Spoiler: It Worked, in the end.

Note: Aug 2023, still using the Free Developer Release of 23c.

Goal: 

The aim is to be Lazy, to type minimal code, and to keep scripts as simple as possible, but still create a complete database: a Container + at least 2 PDBs. And do this for version 23c, for which the Free Developer Release 23c is available for download since early 2023.

I would like to use "simple commands" that can be easily and flexibly used by Developers, Dev-supporting DBAs and SysAdmins.

My resulting scripts should be usable to create a database quickly on Any (linux) platform where Oracle can be installed.

Background: 

When I tried to create a database with the developer-release of version 23c, I first noticed that the trusty "db create assistant", the DBCA, would only allow me to use a template. The Assistant didn't offer the option to "generate scripts" anymore. I liked those scripts, both as examples and for inspiration/research (what is in there...? what happens ? is sql.bsq still used...?). As v23c comes together for General Availability (GA) the DBCA-assistant will be made "complete" again (or so Gerald and Loïc +/- promised). Not to be stopped, I went back to my notes since versions 11.2 and 19.3, from when I created the databases all by myself, and I tried to apply it to 23c. Here is "The Report".

More on how I approached it: I took some old examples.

To depart, I used the generated scripts from a v21c assistant, where the assistant still allowed me to do the "generate scripts".  These scripts were my examples on how to install and complete all the (required and more) components. I would definitely recommend to simplify those scripts a little, if that is possible (Oracle?).

But my first obstacle was, dont laugh:

First I had to change all occurrences of ORCL, ORCLCDB and ORCLPDBn (the defaults from the iron-age) to use the names FREE and FREEPDBn, because "FREE" is the only name the early developer-release for v23c will accept as database name. No big deal. It is a Brave New World... feeling FREE (but yeah, limited to that one name...).

Note: This demonstrates, again, the need for Simplicity: the less text, the less (hardcoded) code,  and the less editing you have to do, the better. You become more agile, more portable, less prone to typos, and more Robust.

To make a database useful, you need a minimum of additional scrips (think catalog, catproc, etc) and at least 1 PDB, but preferably more PDBs. Hence some more scripting and experimenting to do.

So Let's Start this...

First, I composed a minimal init.ora file. After some trial and error, it contained this:

# the minimalistic init.ora file

# name is fixed for the 23c-FREE edition...
db_name='FREE'

# file-parameters, mimimum of information to where to place files.
control_files               = '/opt/oracle/oradata/FREE/control01.ctl'
db_create_file_dest         = '/opt/oracle/oradata/'
db_create_online_log_dest_1 = '/opt/oracle/oradata/'
db_create_online_log_dest_2 = '/opt/oracle/oraflra/'

diagnostic_dest             ='/opt/oracle'

# needed
undo_tablespace             = 'UNDOTBS1'

# for PDB, we need some filename-convert ? (apparently not)

# use the default! (experiment later, at own risk!)
db_block_size		    = 8192

# set these out of habit..
audit_trail  ='db'
compatible   ='23.0.0'

# isnt this the dflt yet in 23 ? yep, it is.
# enable_pluggable_database=true

# some memory params, all goes faster if you can give space, if you have.. 
# you can actually leave this out, dflts may also work?? needs test.

open_cursors            = 300
processes               = 300

pga_aggregate_target    = 512m
sga_target              = 1536m

Notice there are very few references to file-paths, meaning very little to edit if you move to another box or system. And there are at the moment minimal memory settings, which is partly because the Developer Release 23c has some built-in limitations. 

With that minimum of parameters, with some experience from earlier versions+attempt, and by re-reading the Doc on Create-Database (always RTFM folks, Always RTFM ), I was able to use the following, simple, minimalistic create-statement to create my first v23c database:

spool crdb1

-- pwds get "accepted" in separate file, file and pwds are reused.

@accpws

-- we need the pw file, in the dflt location.
host $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=&&sysPassword force=y format=12

-- we need at least some directories to place the data.
host mkdir -p /opt/oracle/oradata/FREE
host mkdir -p /opt/oracle/oraflra/FREE

set echo on

connect / as sysdba 

-- there must be an init or spfile, but mine is minimal... 
startup nomount ;

-- minimal create stmnt 

CREATE DATABASE FREE
EXTENT MANAGEMENT LOCAL
DEFAULT TABLESPACE users
DEFAULT TEMPORARY TABLESPACE temp
UNDO TABLESPACE undotbs1
ENABLE PLUGGABLE DATABASE
   SEED
   SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
   SYSAUX DATAFILES SIZE 100M;

-- add a few checks..
select name, open_mode from v$database ;
show pdbs 

prompt Create Database Done. Please Verify.

That was my script crdb1.sql: create the database.. from minimal init-content and a minimal create command. There are a lot of caveats and Warnings needed here (Don't Try This At Work ;-) ...), but it worked. A Good Start:

Result: The CDB Container and the PDB$SEED are created. Process-wise, a ps -ef | grep $ORACLE_SID shows 59 processes already. 

Me Curious, so immediately checking: lsnrctl status shows:

...And because of the LISTENER, we can already connect from a client-stack with SQLcl or SQL*Plus. Aha, that needs testing:

Yes. From the MB-air, I can already connect, just like that. And now double-check, connect from the "server": 

And indeed from the actual "server" (in reality a bash-shell inside a container), we get the same results: Same DBID. We are looking at a (minimalistic, not finished yet) CDB. Created very "easily" with minimal editing and scripting. And I can already connect over TNS.

Looking around:

Inside the database there are no USER-views or DBA-views yet, only V$ views to "inspect the system".

The First Check:

But wait until you see the filenames:

As was expected, due to the minimalistic (lazy) setting of parameters and the minimal typing effort, I have OMF files, and the Pluggable, the PDB$SEED, has gotten this horrendous subdirectory-name which makes memorising the file-paths impossible. I Really Like OMF, I dont mind about the crazy but readable filenames.dbf. I don't like the long subdir-names, but those are what you get if you, lazily, don't want to specify file_name_convert or other directives for the files of the PDBs (there is a reason, but that is out of scope for now).

I also tried to Increase the undo-tbs, just to see if I can... Resize command worked fine. Because I know some of the next operation will make my datafiles grow, I can already pre-grow the files. Pre-allocate space instead of grow incrementally,  to make the work slightly more efficient. And thanks to OMF, I can also add bigger logfile-groups with Very Simple commands :

Bigger logfiles and pre-grown datafiles might help the subsequent actions (catalog, components) to finish a little faster.  (And if you are re-playing this : don't forget to switch-logfile, do a checkpoint, and drop the small-ish logfile group 2 as well...). 

Success ? Not complete yet, but Getting There!

So.. basic operations already work nicely: resizing of datafiles, and adding of logfiles. I can even do that work over a connect from a client which allows me to run some of my trusty old maintenance-scripts right from my laptop-client machine. 

But since we don't have a PDB yet, we are not done, not by a long way. It looks like I will have to do a little more work: running the "catalog" scripts, adding the "required" components, and then finally creating one or more PDBs. 

More to come:

I will need to create + run at least three more scripts : 

crdb2_cat.sql: to add the catalog (this was actually the slowest part)

crdb3_comp.sql: to add a number of essential "mandatory" components.

crdc4_pdb.sql: to finally add some PDBs and show it All Really Works.

Spoiler: It is going to Work. But some of it is for the next Blogs.

---- End of Part 1 ---- 

Some Disclaimers, as ppl have asked (written 06 Aug 2023)

My Server: 

As platform, I used the container image provided by Oracle, which can be found here: container-registry.oracle.com/database/free, image id: 7c64410c08d5afc711a9da0cc7cfa6e4e66bc7ec4456c3ba5867823c8e40ef57

And I've ran this image via docker-desktop on a both an MBP and an MBAir, more or less as if it where a little Virtual Machine. Most of the actual work for this blog was done on a humble MBair from 2018. Kinda shows you really need Big Iron and Large Servers to run Oracle, right?

The Scripts:

The original "generated scripts" were generated on a VM with Oracle v21 installed. I copied them off and edited them to fit in the (often hardcoded) version/name 23c. By editing and simplifying the generated scripts, I got to the results above. You may see more of the generated scripts and their 21c origin in the next episodes.

Warnings, just in case: 

Dont Do This At Work.

and Use This at Your Own Risk.

The work and scripts and results in the above blog is all just for research purposes, and bcse I am a curious, slightly weird person. I am not suggesting you should do this for any Serious system, and Definitely Not with the pre-/developer release of 23c.

---- finally, the real end of part 1 ---