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