TL;DR: Creating the "catalog", using a script from v21c as example. Beware: v23c Can Differ !
Spoiler: It ends with Optimism.
Background:
Still on my quest of trying to create a database, scripted, but with minimal effort, minimal scripting, minimal typing. Because the create-assistant from "Free Developer Release 23c" doesn't provide an option to generate scripts yet, I took the set of scripts generated by the DBCA from a v21c as example.
Warning: This is part 2 of what could now become a 3 or 4-part series... (here is Part 1)
Next: Catalog.sql
Next step is to run the "catalog" scripts. From the generated scripts (which are from version v21c!!), I see that the first script to run after the basic "create database" is the CreateDBCatalog.sql.
This is a script that uses catcon.pl to run four components:
catpcat.sql - a fancy utility to run the old catalog and catproc
owminst.plb - known as workflow, apparently wm goes into CDB
pupbld.sql - known pub stuff for SQL*Plus, CLI tools
pupdel.sql - didn't know this one yet, but OK, seems a logical addition
The Challenge here, even in very old versions of DBCA, was always the adaptation of the many hardcoded file-paths. The DBCA would assume you only wanted to run the scripts on the one server where you generated them. Porting those scripts to anther server was a recipe for problems...
The original CreateDBCatalog, generated from DBCA v21c looked like this:
Good luck deciphering that on a 80x25 terminal, but ok...
I could not resist trying to simplify a little: I added $ORACLE_HOME where appropriate, and I redirected the logs to /tmp (good riddance, and Yes I still read logfiles. Sometimes..). This made the scripts a _little_ more simple and portable but I still think they should be made more readable, Simpler. Might do Later.
After some editing version looked like this (copy-able code, I hope...):
-- crdb2_cat.sql : call scripts to generate "catalog" etc..
-- adapted from CreateDBCatalog.sql, as genrated by dbca-v21c.
-- get the pwds, we need m
@accpws
set echo on
set verify off
connect "SYS"/"&&sysPassword" as SYSDBA
spool crdb2_cat.log append
alter session set "_oracle_script"=true;
alter pluggable database pdb$seed close;
alter pluggable database pdb$seed open;
-- oracle-home missing for catpcat? supposedly catctl.pl catches that ?
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -u "SYS"/"&&sysPassword" -n 2 -icatpcat -c 'CDB$ROOT PDB$SEED' -a -d $ORACLE_HOME/rdbms/admin -l /tmp rdbms/admin/catpcat.sql;
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp -v -b owminst -U "SYS"/"&&sysPassword" $ORACLE_HOME/rdbms/admin/owminst.plb;
-- make sure system pwd is correct
alter user system identified by "&&systemPassword" ;
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp -v -b pupbld -u SYSTEM/&&systemPassword -U "SYS"/"&&sysPassword" $ORACLE_HOME/sqlplus/admin/pupbld.sql;
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp -v -b pupdel -u SYS/&&sysPassword -U "SYS"/"&&sysPassword" $ORACLE_HOME/sqlplus/admin/pupdel.sql;
-- again, make sure system pwd is correct, got error here ?
alter user system identified by "&&systemPassword" ;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /opt/oracle/admin/free/scripts/sqlPlusHelp.log append
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp -v -b hlpbld -u SYSTEM/&&systemPassword -U "SYS"/"&&sysPassword" -a 1 $ORACLE_HOME/sqlplus/admin/help/hlpbld.sql 1helpus.sql;
spool off
Running this script took +/- 2hrs on my 5yr old, trusty MB-Air but only just >1hr on the heavy, 10yrs old, MB-Pro (which is still overall faster, and has more CPUs). I always end up doing a lot of testing on that old thing.
But the result was OK. No alarming warnings in the *.log and *.lst files.
----- intermezzo -----
Checking for compile errors...
The CDB$ROOT had 1 invalid object, a trigger on the sqlplus-help system. I can live with that "error", for the moment. The PDB$SEED has 600+ INVALID objects. That may need investigation. But I know there are some calls to utlrp in the next set of scripts. And Don't Forget: This is a generated script taken from v21c, and ported to a "Free Developer Release v23c". Which means that We are effectively testing the Big Red Flagship product, for Free.
----- end of intermezzo -----
Did it Work: Yes (but...?)
What worries me a little is that during the fairly long runtime of these scripts, I did some RTFM on Create-Database v23c (remember, folks, Always RTFM). I stumbled across the need to run catcdb.sql and catcdb.pl. I checked: that script isn't called anywhere in the generated scripts. But a quick glance in catcdb.sql and the catcdb.pl scripts show that catcdb.sql does _approximately_ (my interpretation!) the same as the generated CreateDBCatalog: it invokes catalog, catproc, owminst, and pupbld. Also, catcdb.sql seems intended for interactive usage (it asks for passwords at the commandprompt! )
For the moment I'm going to assume that CreateDBCatalog (intended for automatic-running) and catcdb.sql (plus catcdb.pl) are equivalent, where the latter is meant for purely for interactive usage. (this needs more RTFM-ing, or maybe some reader already Knows..)
In future, I hope the DBCA for v23c will soon allow me to "generate scripts". That way I can (should) be able to get a more Quality Assured version of these scripts.
File Sizes, possibly important:
Also, while running the "catalog" script(s), I noticed the datafiles growing bit by bit, so after several tests, I decided again to help a little by resizing them proactively. (I already knew the the resize-command works straight after the create-database, see earlier blog). I begin to appreciate the construction whereby you can specify sizes on "create time", and the SEED is stated to inherit from the CDB, and the PDBs then inherit from the SEED. It is just that I dont like to add too many items to my "minimalistic" create scripts yet. If I hardcode 1G size now, that may bite me in the tail later...
If you consider creating manually (which I probably would not recommend) and if you also want to limit size (for example to keep your container-footprint small), then study this part, check/search for the "tablespace_datafile" clauses and their cascading effect on ROOT and SEED. And try to aim for small datafiles (even if I would prefer tablespaces of type "bigfile", but that is another discussion).
Additionally, I hate it when an UNDO tablespace grows to several 100s of M, but cannot quickly be shrunk down due to the infamous ORA-03297 (grey-bearded DBA's will add this number to their hate-list, with 1555 and 12154 etc..).
Note to Oracle: Can we fix that? Can I request EASY UNDO-RESIZING, for the next release (v25d or whatever) ?
In a real world, there is always someone who will (+/- correctly) say: But Disk is Cheap Now... Yes, that is true. but I still don't like to waste resource.
So Far So Good.
I seem to have survived up to this stage.
Next, I will go over on the various components that the DBCA (when it was v21c and earlier) normally would install in the CDB, followed by some post-creation steps (stage 3, let's call it crdb3_..) and then I will hopefully, finally, add 1 or 2 PDBs (crdb4 ?). All strictly "scripted" of course, and from scripts that I hope are small, Simple, Readable and Portable (to linux/unix systems).
------- End of Part 2 ------
Disclaimer: Don't Try this at Work.
But if you seriously plan to try this at work: Read The Disclaimer underneath Part 1.
------ Real End of Part 2 ------
No comments:
Post a Comment