I still see that many dba is creating their databases using “General Purpose or Transaction Processing” template.
Why you shouldn’t use it? Because it is way too general! if you click the “view details” on the right:
Almost all the “Components” are true. So it installs everything even if you don’t use it. is that a bad thing? Yes, because;
- some of them requires Licenses (that means Extra Cost) and you are installing it and if someone uses it then, you have to pay to Oracle. As a small note, you cannot remove them during the installation (I will explain why in a minute)
- after you created your database, your upgrade/patching times will increase dramatically. when you upgrade your database, Oracle upgrades everything in the database and that includes the components too whether you use or not! This means more down time (based on your upgrade/patching method).
- This database is created with predefined set-up like 200M redo log files. of course you can change them but again you might missed it and that means extra job after db creation. By the way, you cannot change some of them for example default block size! (I will explain why)
- you cannot use “some” options with latest installed versions! for example timezone file version! even if your database home is patched (or have already) the latest timezone file version, if you create database with “General Purpose” template, then you will surprised when you open your database, an old version of timezone file will be in use! (again, I am about to explain why)
Why? because this is a “used database”! This database is not “new”. General Purpose database makes things easy for you and speed up the installation processes a little bit more and the reason is, this database is already created! Go to your ORACLE_HOME directory (for ex: /u01/app/oracle/product/19c/dbhome_1) and then go to assistants/dbca/templates. The templates that you see in dbca are here, and also “Seed_Database” is here. if you use General Purpose, Oracle will RESTORE a database from Seed_Database.dbf file (and if this is a container database then pdbseed.dfb too). this is a pre-created database.
This is why, if you use this template, you cannot change block size because pre-created database is used (you cannot change default block size after creation). This is why, your database will have an old timezone file because it is installed in already, you must update timezone file. this is why you cannot remove options during the installation because they are already installed.
So, don’t use General Purpose template. create your own or at least use “custom template”. that way, you will have full control of your database and it is really easy. actually go to templates directory that I mentioned above, copy General_Purpose.dbc with a new name, and then open and change whatever you want but I don’t recommend because general templates has some extra sections and missing some sections since it is restoring a seed database.
I won’t go into whole details of template file but here is the head of it:
<?xml version = '1.0'?>
<DatabaseTemplate name="General Purpose" description=" " version="184.108.40.206.0">
<option name="OMS" value="true" includeInPDBs="true"/>
<option name="JSERVER" value="true" includeInPDBs="true"/>
<option name="SPATIAL" value="true" includeInPDBs="true"/>
<option name="IMEDIA" value="true" includeInPDBs="true"/>
<option name="ORACLE_TEXT" value="true" includeInPDBs="true">
<option name="SAMPLE_SCHEMA" value="false" includeInPDBs="false"/>
<option name="CWMLITE" value="true" includeInPDBs="true">
<option name="APEX" value="false" includeInPDBs="false"/>
<option name="DV" value="true" includeInPDBs="true"/>
<option name="NET_EXTENSIONS" value="true" includeInPDBs="true"/>
here are our “Components” for example.
OMS: Oracle Label Security
JSERVER: Java virtual machine
IMEDIA: Multi-media (it is deprecated any more, don’t install it)
ORACLE_TEXT: Oracle Text search
APEX: Apex but don’t install it, it has a separate installation.
DV: Database Vault
NET_EXTENSIONS: .net extensions for Windows OS only.
Some components are depended for example if you want to install Database Vault, you must install Oracle Label Security too. You can check this on Oracle Support via the specific versions. Be careful, pre 18c versions, Spatial is depended to Multi-media (IMEDIA) but after 18c it is deprecated and Spatial is no longer depended to it. So, the version is important.
if you down in the template file, you will see database parameters. you can set your defaults in that section. For example, I use unified auditing so disable traditional audit by setting audit_trail to NONE.
you can create a template via dbca (on the first screen choose “Manage Template”). Also, you can create a template from an existing database via dbca using cli:
dbca -silent \
-sourceDB orcl \
-templateName orcl_db_template \
-sysDBAUserName SYS \
Use your own templates and remove unnecessary components from your databases. it will be a relief for you and your databases 🙂
Wish you all healthy happy days.