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