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