TL;DR: I simplified the assistant-generated scripts from v21c, I learned a few things, and it all still worked. We are slowly Getting There in v23c
Spoiler: still a long-ish script, but it worked Fine.
Note: Aug 2023, still using the Free Developer Release of 23c.
Update: Oracle has, on 02-May-2024, announced to rename version 23c as 23ai. This series of blogs was researched and written with the early releases of v23, when the suffix was still "c" (for cloud), but I am too lazy to go and modify all the referneces to the new 23ai (artificial intelligence).
As soon as time permits, I will repeat my "testing" on the new versions, but on first impression, it all works identically on 23ai.
Background:
Still on my quest of trying to create a database, manually-scripted, without using (recovering) "the template". But with minimal effort, minimal scripting, minimal typing (even minimal RTFM, which is never a safe idea...). In parts 1 and 2. Earlier I explained how+why I am using a set of scripts generated by the previous version, v21c.
It was more work than I expected. In the next stage I am going to get angry with "too many, too complicated and too-hardcoded scripts". But it seems the old-ish code Works Fine against the New Version. Legacy ;-)
Again: Keep in Mind that I am using the "Free Developer Release v23c". No guarantee this will work correctly, and nog guarantee this will still work when v23c comes out for General Availability.
I can't wait to get a G.A version and check if the new DBCA still agrees with my approach.
But... So far it is going Well.. I got to the stage where "Catalog" is created in the CDB and in the SEED with little problems. Here is how:
The components (list taken from v21c)
When I check the generated script for creation of a v21c database, it shows the following:
Mind the long-ish list of items after "CreateDBCatalog" (covered in part-2). Those scripts install the "additional components" that the DBCA wanted me to install. That list looks familiar: that same list has been there since waay back. I guess I'd better run (most of) those scripts on v23c as well...
The first script, JServer.sql, looks like:
As you can see, similarly long commands as we have seen in the "CreateDBCatalog" in part-2, except that I already put in $ORACLE_HOME to simplify. And by opening the scripts one after another, they all look more or less similar (and equally hard to read on first attempt) Note: I Hate long command-lines and complicated scripts - apologies if I repeat that too often.
On first attempt, they ran fine after just editing the file-paths to adjust to 23c, Good sign. But I wanted to do a little more Inspection using Eyeball Mark-I on a large-screen (e.g. have a good look at the subject...). So I concatenated them all into one big script and viewed them on another editor, to better horizontally compare the various lines.
The following of the generated scripts were very similar:
JServer.sql
context.sql
ordinst.sql and interMedia.sql (huh?)
cwmlite.sql (olap),
spatial.sql
... are all very much alike (but not quite). They contain a lot of repeating, identical-looking calls with generally only two understandable differences: The -b <logname> and the end of the line the "script + options" are different.
That makes sense, in multi-tenant, those components are installed via catcon.pl in both CDB$ROOT and PDB$SEED.
Some remarks on the scripts:
ordinst and interMedia: Deprecated, checked them anyway. Those are now just stubs, and can probably be removed from the list of scripts to run. So I deleted them from the calling-script.
CreateClusterDBViews: Doesn't use the -c (or -C) option, but is otherwise identical. Apparently, leaving out -c and -C runs the scripts against both CDB$ROOT and PDB$SEED. It runs both catclust and catfinal (which is effectively empty), thereby sort-of signalling the "End of Catalog stuff".
LockAccount: Does a stop/start of PDB$SEED and then runs a block of PL/SQL in both CDB$ROOT and PDB$SEED to presumably lock-down default accounts. OK. We can keep that code as is for the moment (would v23c contain more dflt-accounts, more items to lock or protect...?).
PostDBCreation: Generates the spfile and does the classic utlrp. OK, that should fix the "invalid objects" we had after doing the catalog in part-2.
Then a bit of a surprise, good and bad: The last three scripts are:
PDBCreation: Only contains a spool-append command to a logfile.
plug_freepdb1: Creates an Actual PDB (yesss, finally...), with the name "hardcoded" in the script, and file-name-convert directives included. I'd like to tweak this stmnt to at least get rid of hardcoded names + paths.
postPDBCreation_freepdb1: Adds a user-tablespace with hardcoded datafile-path (hm), it does an OPatch, and does a number of selects to report things to spoolfile.
I am going to keep the PDB-related scripts (the last 3) for later, and first run the scripts to add all the (mandatory or recommended?) components to CDB and SEED.
To Simplify...
Already I had them all concatenated into one, so I decide to consolidate the scripts up-to, but not including, any Create-PDB work. I'll keep everything in one single script, call it crdb3_comp(onents).sql. And I cannot resist to simplify it just a little.
I am also going to keep the spfile-creation under my own (manual, but scripted) control, putting a "comment" in front of that command, for the moment (yes, I understand the risk.. some script may want to set parameters to spfile/scope-both).
The idea for crdb3_comp.sql is born.. now just had to do a bit of editing+testing.
The main changes I made are reduction of hardcoded file-paths and smaller commands over-all, for readability and editability. I then removed a lot of lines that did just spool-append. I'm keeping everything the main logfile. There will also be the logfiles from catcon.pl which I directed to /tmp.
The resulting script, crdb3_comp.sql:
/* -----------------
crdb3_comp.sql : install basic components before creating more pdbs
notes:
- wasnt happy with the long commandlines, still not happy
- after utlrp: 0 invalids. Good Sign.
- why repeated connect? to verify un/pw ?, or Need for fresh session??
- option -a is undocumented (Martin berx Berger traced: probably GUI/windows related)
--------------- */
-- get passwords
@accpws
-- one main logfile for this script...
spool crdb3_comp
-- Wanted shorter commands to Run CatCon.. Hence define rcc
DEFINE rcc="$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp -v "
prompt first JServer
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
host &&rcc -b initjvm -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" $ORACLE_HOME/javavm/install/initjvm.sql;
host &&rcc -b initxml -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" $ORACLE_HOME/xdk/admin/initxml.sql;
host &&rcc -b xmlja -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" $ORACLE_HOME/xdk/admin/xmlja.sql;
host &&rcc -b catjava -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" $ORACLE_HOME/rdbms/admin/catjava.sql;
prompt next is context
-- keeping the connect, as each "script" originally did a fresh connection
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
host &&rcc -b catctx -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" -a 1 $ORACLE_HOME/ctx/admin/catctx.sql 1Xbkfsdcdf1ggh_123 1SYSAUX 1TEMP 1LOCK;
host &&rcc -b dr0defin -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" -a 1 $ORACLE_HOME/ctx/admin/defaults/dr0defin.sql 1\"AMERICAN\";
host &&rcc -b dbmsxdbt -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" $ORACLE_HOME/rdbms/admin/dbmsxdbt.sql;
prompt ordinst and interMedia: stubbs removed, product no longer avaiable.
prompt next is cwmlite.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
host &&rcc -b olap -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" -a 1 $ORACLE_HOME/olap/admin/olap.sql 1SYSAUX 1TEMP;
prompt next is spatial.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
host &&rcc -b mdinst -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" $ORACLE_HOME/md/admin/mdinst.sql;
prompt next is CreateClustDBViews.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
host &&rcc -b catclust -U "SYS"/"&&sysPassword" $ORACLE_HOME/rdbms/admin/catclust.sql;
host &&rcc -b catfinal -U "SYS"/"&&sysPassword" $ORACLE_HOME/rdbms/admin/catfinal.sql;
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
prompt next is lockAccount.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
alter session set "_oracle_script"=true;
alter pluggable database pdb$seed close;
alter pluggable database pdb$seed open;
BEGIN
FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (
'SYS','SYSTEM','SYSRAC','XS$NULL') )
LOOP
IF item.AUTHENTICATION_TYPE='PASSWORD' THEN
dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
execute immediate 'alter user ' ||
sys.dbms_assert.enquote_name(
sys.dbms_assert.schema_name(
item.USERNAME),false) || ' password expire account lock' ;
ELSE
dbms_output.put_line('Locking: ' || item.USERNAME);
execute immediate 'alter user ' ||
sys.dbms_assert.enquote_name(
sys.dbms_assert.schema_name(
item.USERNAME),false) || ' account lock' ;
END IF;
END LOOP;
END;
/
alter session set container=pdb$seed;
BEGIN
FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (
'SYS','SYSTEM','SYSRAC','XS$NULL') )
LOOP
IF item.AUTHENTICATION_TYPE='PASSWORD' THEN
dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
execute immediate 'alter user ' ||
sys.dbms_assert.enquote_name(
sys.dbms_assert.schema_name(
item.USERNAME),false) || ' password expire account lock' ;
ELSE
dbms_output.put_line('Locking: ' || item.USERNAME);
execute immediate 'alter user ' ||
sys.dbms_assert.enquote_name(
sys.dbms_assert.schema_name(
item.USERNAME),false) || ' account lock' ;
END IF;
END LOOP;
END;
/
alter session set container=cdb$root;
prompt next is postDBCreation.sql
SET VERIFY OFF
host $ORACLE_HOME/OPatch/datapatch -skip_upgrade_check -db FREE;
connect "SYS"/"&&sysPassword" as SYSDBA
prompt note on spfile: I prefer to do my spfile manually,
prompt and at the moment in dflt location
prompt I might consider moving it out of OH later.
-- create spfile='/opt/oracle/dbs/spfilefree.ora' FROM pfile='/opt/oracle/admin/free/scripts/init.ora';
connect "SYS"/"&&sysPassword" as SYSDBA
host &&rcc -b utlrp -U "SYS"/"&&sysPassword" $ORACLE_HOME/rdbms/admin/utlrp.sql;
select comp_id, status from dba_registry;
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup ;
spool off
prompt Done crdb3_comp. Please Verify: errors, invalids objs etc...
That is the script I did the actually run with.
(feel free to copy, I checked: Chrome, Firefox and Opera.. allow copy-paste. One day I'll get around to using proper code-windows in blogger..)
Now... Guess how many invalid-objects? .... None in the CDB.
And in the PDB$SEED, where we still had 600+ Invalid objects after stage 2... :
Really Happy so far. Off course, I have no idea if v23c has or will introduced additional components or scripts to run at Create-Time. I havn't even checked the fairly long page of RTFM in all its details on this yet....
First, I want my Databases!
With Maximum-Lazyness-Default, minimal effort and Minimal-scripting (but Readable and Portable scripts, Please...).
So far So Good.
All in all, it looks like the generated scripts from v21c are still working. This combination of CDB + Seed should now be ready for PDB-creation. I will of course try to minimalise and simplify that create-process, just a little.
Next blog will describe the creation + running of crdb4_pdb.sql in which I finally manage to create 2 PDBs. It worked. But I wasn't quite happy with the result, yet.
------- End of Part 3 ------
Disclaimer: Don't Try this at Work.
But if you seriously plan to try this at work: Read The Disclaimer underneath Part 1.
PS: Special Thanks to Martin "berx" Berger for digging down the catcon.pl -a option.
----- Real End of Part 3 ----
No comments:
Post a Comment