TL;DR: The scripts generated by DBCA work, although some tweaks and caveats apply. But in the end: Yes. They Work!
image: Startup of the DBCA from Oracle 26ai (to generate scripts).
NB: Running X11 windows in XQuartz on a mac does not always yield nice screenshots, and sometimes the display(-driver) seems to get in the way, see ths previous blog(link). Hence I used a camera to snap the screens.
-- Background: Scripts to create a database (and using X11).
Since I got on the quest of "Creating a Database from Scripts", I've been moderately successful. I can do:
SQL > Create Database ;
and get a more or less running RDBMS.
The next steps would be to install things like catalog, catproc, and various (mandatory) components.
I also found the initial sizes of datafiles and redologs rather small, leading to a lot of messages in the alert-file about significant resource- and time-consuming file operations.
And maybe I want those fancy "bigfile" tablespaces from the get-go.
Because I didnt feel like ploughing through the RTFM for all the details (link to create database), I asked the DBCA to generate the scripts for me: The Oracle Provided "Assistant" would know how to do this the correct way, right ?
In earlier attempts (link to 2023), when there was only the v23 "FREE" version available, the DBCA would not offer this option (yet). I had to revert to the scripts generated from v21c and adjust them. That worked at the time, but I was never 100% sure if I had everything covered correctly. Now that the version 26ai is General Available, I can do this properly, with the correct version of DBCA.
This current hobby-project / research was done mostly with v23.26.1 in May 2026 to be precise. And I would expect to re-do this with the version that is supposed to drop any time now and re-verify the findings.
Note however: I am still striving for the "simplest possible scripts", preferably scripts that I can port to any (linux) environment where Oracle 26ai is installed.
-- How I generated the scripts.
My main platform for experimenting are the container-images provided by the oracle container-registry (go into the enterprise-database section - link). I also use/test on the "FREE" images by GVenzl (link). The FREE version does not permit (yet?) to "generate scripts". Hence to re-play this blog, you need the proper Oracle containers. But I do tend to test any generated script on the FREE containers as well.
The DBCA is a gui-tool, running on X-windows (X11). Hence first I had to get X-windows running in my container. I've described that in an earlier blogpost (link)
I'm assuming the vbox-versions still come with a working X11 installed, havnt verified that recently. In case the X11 is missing in your vbox: the install from previous blog applies to the vbox images as well.
I repeated + tested some of this work on the "FREE" containers provided by Gerald Venzl. Those images are completely hassle-free, license-free, and are very easy to pull and run. Check them if you need something for testing, or any other activity where you quickly need 1 or more database(s) to spin-up.
Without too much ado (see earlier blog), yum was "correctly configured" and I could install X11. I also often use yum pick up a number of other goodies like procps, rlwrap and git.
Once you get X11 and have "the eyes" (or xterm) running from inside the container, you are Good.
Let's run the DBCA...
-- Running DBCA, and choosing "generate scripts"
The screenshot above shows the DBCA (lowercase), and we opt to create a database. On the next screen, we choose "Advanced":
There we go. And once in the advanced mode, we choose "custom":
Custom seems to be the only option that really generates "Create scripts". The other options just generate "recover-scripts" that recover (bakups of) templates.
For my exercise, I accepted a lot of the defaults. I didnt want to experiment much yet. But feel free to explore.
I picked a name (C003), I chose "Local Undo" and I wanted two PDBs:
Two PDBs, just to see how it would handle more than one. Basically to get a nice set of scripts to work with.
For storage: I preferred to choose OMF.
For now, OMF. Reading the RTFM and some experience convinced me that OMF is much more convenient than managing the files myself. But you may have a different opinion, especially if you do not like funny and overly long names (GUIDs) for directories.
We do not need the database created for us (yet), but we want the scripts to study and to tweak. So we continue to "finish" and ... Viola.. (yes, Viola, an old aquaintance).
We obtain the scripts in /opt/oracle/admin/C003/scripts:
Those are the scripts that can "Create a Database".
-- Runing the scripts : C003.sh does it all (with 1 fix)
The complete set of script can be run form one shell script: C003.sh. I'll put up a link to a tar-file so you can see for yourself. Feel free to Test:
mkdir /opt/oracle/admin/C003
cd /opt/oracle/admin/C003
tar -xvf /tmp/C003_omf2.tar
cd scripts
./C003.sh
... and watch the miracle unfold.
Running the script(s) can take up to an hour, or even longer, depending on the speed of your hardware. On an old MBair from 2018, running the container in docker, it takes 2hrs. But on a beefy MBP M5 only about 25min.
Beware: because of bug 37280585, it needs a tweak in the script context.sql: You need to add 4 arguments to the call to catctx. I've fixed that in the tar-ed scripts, so you can just run them on your testbed if you want to try.
If you do test it: Let me know what you find on your system: Any issues? How many directory-paths did you have to edit? How long did the running take ?
-- First Inspection: looks OK. But some comments...
After creation, I did some inspecting, notably with scripts chk_crdb1.sql and chk_early.sql. From the initial checks, I noticed several things:
I now have (mostly) BIGFILE tablespaces, by default, as confirmed by my self-created tabelespace "ABC".
The sizes of file and tablespaces are much larger than in my previous blogs about "create database" (link). But there are a still quite a lot of "resize" events in the alert-log. I can probably fix that.
All components are VALID, except RAC: that was expected. The container-image I use is not suited for RAC. NB: XDB is Deprecated.
There were no Invalid Objects in the generated database.
So far I am moderately happy with the scripts. And they will serve as "examples" for me to simplify a little more. I'll probably discuss them in some follow-ups. Ultimately, I want to end up with something more "portable": a set of scripts I can easily run on any linux machine to create a database.
-- Roundup so far - We have Scripts. Let's tweak them (in next blogs)
The scripts worked. And I feel confident that I can work with them. I have a lot of remarks though:
1) Those scripts will only work on the machine (or platform or container) that generated them or a system with a Very Similar layout. The SID and the file-paths are hardcoded in many places. I would like some more Generic, Portable version. I might come back to this in next blogs.
2) The scripts are not very readable (to me). I would reformat and re-write a few parts before I was more happy with them (next blogs...). More trendy kids would hand them to an AI and ask for suggestions. I just used eyeball-mark-one, some common sense from hard-gained experience, and some of my "personal preferences".
3) For some of the initial configurations (the init.ora, and the initial sizes of files) I would do some things differently myself. I'll tweak some and blog it..
4) File-paths: I'm choosing OMF notably to avoid/reduce hard-coded paths in the scripts. Note that if we choose "storage location from template", all scripts will contain hardcoded file-paths which makes the scripts less "portable" as we would have to edit those paths for every new database we want to create. Reading the RTFM and some experience convinced me that OMF is much more convenient than managing the files myself. But you may have a different opinion, especially if you do not like funny and overly long names (GUIDs) for directories.
There is more. Later.
But despite all my stubborn comments, I'm already a happy bunny with those scripts.
-- -- -- -- -- End of this blogpost, for now -- -- -- -- --
Appendix: Links to files.
C003_omf2.tar.gz : the generated scripts (with fix for bug 37280585). Try them !
chk_crdb1.sql : script used in previous create-experiments.
chk_early.sql : additional script for 1st check of a newly created database.
-- -- -- -- -- End of this blog, For Real -- -- -- -- --
No comments:
Post a Comment