TL;DR: My Minimalistic script to create a database Worked, sortof (link to previous blog). Now let me Examine the first results and make some comments.
And Please take note: I do this "scripting" mostly for Fun and to Explore. This is not a Serious Suggestion to always create your own database or to ignore the RTFM.
This is how I felt after some minimal-scripted "create databas" attempts:
Image: Nice abakus, but that thing isnt going to work for us...
-- Background : trying to create a database, from scripts.
In the previous post, I showed that this command (link) can still be used:
SQL > Create Database ;
It was actually very satisfying to see that command run:
That Worked !
But the resulting database was far from "finished". In this post I want to show some of the findings. And maybe make some recommendations to Future Colleagues and to Oracle on how to (not) use the defaults for this command.
-- So we have created this new database. Let's Examine...
Using the slightly altered script, now called C001.sh, I've repeated the work from the previous post, and added some items and SQL-statements to the script (link below).
Without pretending to be "complete", I want to point out a few things and comment on the results.
First, I had to find out What exactly I had created with that very-basic command and only 1 parameter in the pfile. For this, I used an old script: chk_crdb1.sql (link below), and it produced this:
Voila, our database is there. It is running in NO-ARCHIVELOG (which is efficient), and so far it only has the the PDB$SEED attached to it. Let's check the files that make up this database:
We find...
A single Control-file. Basic, and fine for now - but you should multiply your control-files in Prod.Two log-files. They are OK, but I used to add more and bigger ones to reduce the "switch-logfile" events (old habit, I know...).
The datafiles for tablespaces SYSTEM, SYSAUX and SYS_UNDO. I did like the short names.
Notice from the previous check: No BIGFILE tablespaces (yet) ? And those files are rather small in size. The alert-file contains a lot of resize-operations already. That seems like a waste of time to me?
Possibly, the the old "smallfile" type is kept here as dflt for backward-compatibility. And remember, this is only the CDB and the SEED... I would assume any capable DBA to make a choice here, smallfile/bigfile depending on the situation.
But no TEMP files (no rows selected...) and no (default-)TEMP-tablespace? Spotting that as an ommission, I created one...
It appears I can create the TEMP tablespace(s), but it needs a "destination" (or a file-spec). It feels as if Oracle does not want to place your TEMP-file(s) in the same default as your regular datafiles unless you specify it.
And the initial size is an impressive 100M. You can even specify "bigfile" here if you want that (do try!).
Spoilers: in the scripts generated by DBCA, the bigfile-option and the temp-tablespaces are covered. And the DBCA produces larger files for all tablespaces.
-- About those file-locations...
On creating a database, you will probably, consciously, place or move your data-, log- and controlfiles somewhere safe and spacious. But a lot of the trace/dump/audit destinations might remain dflt.
Without specifying any parameter, most of the file-destinations end up under the ORACLE_HOME. Understandably, but it presents a risk: if you leave it like that, some of those directories will collect log- and trace-files forever, and that can bite you at some unexpected moment, sometimes years later. I would prefer for most (all) of those parameters to end up in the "diag" destination by default.
The result is a number of parameters set explicitly in the subsequent script: C002.sh. Some will get flagged as obsolete or deprecated but still... That way I feel I have more control over where my files end up. I would point those preferably to either "data" or "diag" destinations, and not keep them under "home".
(I've been mumbling about a Read-Only O_HOME since ... 2003 or so, and I'm kinda happy Oracle got round to providing for that option since, I think, v18c - different topic, maybe later)
-- Connecting: you need that orapw-file..
To examine this database (a.k.a. geek around inside SQL), I wanted to connect with SQLDeveloper. That, of course, required a password-file. My next version had to include this:
# need a pwdfile, if only to be able to connect SQLDev for inspection
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} \
password=oracle \
force=y format=12
No big deal. I inserted that even Before starting SQL*Plus for the startup-nomount.
Note to self: I had a dflt LISTENER running at port 1521, and the instance had dutifully registered. Way back, I made it a sport to run:
lsnrctl start
...without any parameter-file. And that often just worked fine back then, and still does. I might do a topic on that sometime: The Simplest listener.ora is ... No listener.ora (probably not best-practice, but Ideal for Lazy DBAs like me).
And thus, I was able to connect with SQLDeveloper. The tool didnt even complain (yet) about not having "catalog views" - Until of course I tried to open a list of objects.
SQLDeveloper is still a Very Cool Tool - Tip of the hat to That Jeff Smith and his team.
There was a lot I stumbled across, but most of it was too nerdy to be of use. I needed to move on if I ever wanted to get to my target of "usable scripts".
Maybe later, or maybe over a beer with a few geeks sometime...
-- Some improvements to my script: C002.sh
From what I found + learned, I created C002.sh. This script will first create a slightly more complete init.ora file. In the init-file, I wanted to at least put the the various trace-, dump-, log- and data-files in "proper" locations under ../oradata, ../oracle/diag, and ../oracle/admin directories.
And I wanted a larger chunk of memory, as the dflt half-GB seemed extremely small to me. I've kept them humble: the values in that script should be low enough still work on the FREE-version.
Spoiler: The adjustment of file-sizes for datafiles and logfiles will have to wait, because I already know the "generated" script from DBCA will create much larger datafiles from the get-go.
Warning: in my C00n.sh scripts I have the scripts "compose the inti-file" on the go. This keeps the script as an "all in one". But beware of this script over-writing an existing pfile!
-- Wrap up: List of Findings (so far..)
I've listed and numbered some items that I found "interesting" and possibly "relevant"...
1. Always have a password-file, an orapw$ORACLE_SID. Not only bcse "security" but also because it allows you to connect from "elsewhere". For inspection of x$views, v$views, and other SQL-exposed information, the use of SQLDeveloper or a similar tool to easily move around in the SQL-space is just more efficient.
2. Files end up in $ORACLE_HOME/dbs, and then some. Setting db_create_file_dest should fix that for "data". But I would prefer for most (all) of the other parameters, even the obsolete and deprecated ones, to point to the "diag" destination by default.
3. The initial files sizes are very small. And the AUTOEXTEND - NEXT value is also small. I would recommend to start with larger sizes, or adjust those values early. Or .. have Oracle provide larger values for start- or default-settings.
4. No default BIGFILE for tablespaces (yet). This seems to run against what the RTFM says about BIGFILE as being the new default (link to create-db, April 2026) ? No big deal as my demo-system doesn't really need bigfiles..
5. Default memory parameters are very small... You probably want to set those larger yourself. Note that the FREE version has limits here.
6. Tip.. check nr of processes, logwriters and jobqueues, and memory settings.. you can probably economize on those, especially if you are on a very small system under load.
7. Bonus-Geek-Tip-1: Check the object owned by Public and System. Why, and Why so Early in the create-process ?
8. Bonus-Geek-Tip-2: study the alert file and the ancient sql.bsq. Lots of interesting stuff there but not immediately relevant for my mission of "simple scripts".. Maybe Later.
These are the things that I found and deemed worthy to mention. There is probably a lot more. Feel free to copy the scripts below and explore for yourself...
-- Summary: This was an Interesting Excercise. Now move on...
By looking at all these "default" results, I re-visited some ancient knowledge and I learned a few (useful ??) tidbits.
The main message is: Use Sensible Defaults. And verify them. You should probably fill your init.ora with reasonable values for memory- and with proper file-path parameters.
You could do worse than using the scripts provided by the DBCA on "save as scripts". My next post should be about using the DBCA to generate those better "example scripts".
-- -- -- -- -- End of this Blogpost, for now -- -- -- -- --
Appendix: links to scripts
C001.sh : The script to create and do some examining.
C002.sh : The improved version.
chk_crdb1.sql : a script that will peek into a freshly-created instance.
info07_files.sql : find most of the file-destinations, know where your info goes.
-- -- -- -- -- End of this Blogpost, for Real -- -- -- -- --
No comments:
Post a Comment