If you create a database using dbca (Database Configuration Assistant) step 3 allows you to select one of two templates (Data Warehouse or General Purpose or Transaction Processing). Both of them have the checkbox Include datafiles enabled. If you choose one of them the database creation is easy and within less that 5 minutes the new database is up and running.
Why not?
First of all I’m interested to know the differences between them both – and that might be for interest for you as well.
Lets have a look at the datafiles in the directory $ORACLE_HOME/assistants/dbca/templates:
-rw-r-----. 1 oracle oinstall 10772 Apr 5 2019 New_Database.dbt
-rw-r-----. 1 oracle oinstall 86548480 Apr 17 2019 pdbseed.dfb
-rw-r-----. 1 oracle oinstall 274554880 Apr 17 2019 Seed_Database.dfb
-rw-r-----. 1 oracle oinstall 18726912 Apr 17 2019 Seed_Database.ctl
-rw-r-----. 1 oracle oinstall 4888 Apr 17 2019 Data_Warehouse.dbc
-rw-r-----. 1 oracle oinstall 4768 Apr 17 2019 General_Purpose.dbc
-rw-r-----. 1 oracle oinstall 6611 Apr 17 2019 pdbseed.xml
There are two dbc files with some similarties to the names of the templates in dbca. Those are the XML-Files with the structure and detailed information about the database files
The additional files with the postfix dfb are compressed RMAN backup of the datafiles. Two files because we do have a CDB and PDB$SEED. For the later pluggin of the PDB$SEED an XML manifest ((pdbseed.xml) is required as well.
Last but not least a backup of the controlfiles named Seed_Database.ctl is stored in this directory. In this case “Seed Database” has nothing to do with PDB$SEED but is only the name of the templates. Finally one file is left over: New_Database.dbt. This file is used with the Custom Database and not with the templates we are talking about.
Where’s the point?
There are two database templates but only one set of datafiles. Namely the both databases Data Warehouse and General Purpose are using the same datafiles and the only difference is one single parameter: the parameter star_transformation_enabled=TRUE is set for the Data Warehouse template. To be fair the Sample Schema has a different tablespace named EXAMPLE but that is irrelevant because the option is switched off.
To conclude: there are no differences between the both templates. The parameter star_transformation_enabled=TRUE kann be set later on as needed even without restarting the database.
Should you use this templates?
No!
Why not?
Because with this template all options including the be loved OJVM are installed. Yes you can deinstall options you don’t need or do not have a valid license for later. But the far better approach is to go combersome way and use the Custom Database and set the options and configuration parameters as needed.
For a single database like in production environments it shouldn’t make a difference if the database creation takes 5 minutes or one hour. But for a production database as a DBA you should better know what you installed.
But what about your testing or development environments where you might need to create databases on the fly. And what about coding standards or compliance rules for databases? Wouldn’t it be better to create databases with identical layouts?
This is why templates are still useful.
The first steop is to create a database (a Custom Database of course) and go through all the necessary steps until your database fits to your needs including your own roles, users, profiles and whatsoever – and don’t use the roles DBA, RESOURCE, CONNECT or the DEFAULT profile.
Now you can run the dbca and create your own template with the step “Manage Templates”.
It’s by the way irrelevant if you convert the files to Oracle Managed Files (OMF) or not. If you later create a database using that template you have still the choise to create OMF.
My experience is that is it not useful to create a PDB template as well. So applications and data is not a subject for templates. And you know CREATE PLUGGABLE DATABASE already uses the PDB$SEED as template and will take only seconds.
One Remark
Don’t use the default location for your templates but put them on a share / NFS file system. Now you can use one single template for all database and you do not have to copy the template around. But be careful: while creating the template the location of the files will be stored as Variable LOCATION in the file <Templatename>.dbc. So if you need to move the templates to a new location you need to modify the path variable.
For the future: Create a CDB with all our rules within 5 minutes – no probleme!