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:
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.
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.
No comments:
Post a Comment