Another advice regarding database creation:
Use your own templates in DBCA to create databases.
This way you can configure not only the options and components to create but also redo log layout, your set of parameters and much more. And it is less error prone than a “create database” script.
Use your own template in DBCA
Creating your own template in DBCA (Database Configuration Assistant) is quite easy and straight forward. In DBCA select “Manage Templates”:
and create a new template:
from an existing database template:
Choose the “Custom Database” template as blueprint:
Choose the components you’d like to have
Then later you can choose which options you’d like to configure:
Adjust Default Parameters and Storage Locations
Very important: Adjust the default parameters as needed.
AUDIT_TRAIL for instance will be set in DBCA always to defaults which may not meet your requirements and needs. See also the description and the potential values in the screen below when you mark a parameter:
It is even more important to “Customize Storage Location” as this will allow you to change the default setting for redo logs and tablespaces.
Adjust for instance the size of the redo logs, multiplex them and place them on the correct storage:
And don’t forget to click “Apply” after every change as otherwise your changes will be lost without notice:
Once done, the template got created:
Create a database with your own template
Choose now your own template to create a new database from it:
But interestingly enough, this doesn’t give you now a one-click creation. You still have the choice to create your database as a non-CDB or CDB, to change things – but the values you have chosen get populated into the fields of DBCA.
If you desire an automatic one-command creation you will have to use the silent mode where you can pass in your own template.
See my previous blog post about creating custom databases:
I don’t see an advanced config options for changing storage locations…. I am trying to create a new template from existing template or exsiting database in both ways I don’t see the option to customize storage locations while creating a new template.
is this option available in 126.96.36.199 ?
I have it even in 188.8.131.52:
“Manage Templates” ==>
“Create Database Template” and “From an Existing Template” ==>
“Custom Database” ==>
Give it a name “MyDBtemplate” ==>
Now you should see /DBCA page 5 of 12/ the page to change the locations.
Very well explained Mike. Thanks for very clear steps. It helped me to export existing database templates.
Very Frustrating, I created a template without the components without the spatial and mulitmedia options,
used this template to create a cdb instance/database (oracle 19.16) creation failed with rman failed to restore datafiles, Also created a template with compatibility set to 12.2, tried to create database, database failed with ora-00201 error, the init.ora files have compatibility 12.2, why is this failing, very frustrating
so just for my understanding:
1. You created your own template in 19.16.0 DBCA
2. This template is used to create a CDB – and it does not have SPATIAL and MULTIMEDIA (but what else does it have selected)
3. Then you advice DBCA to create a new database using exactly this template
4. And this fails with the above errors??
Did you open an SR? Then please share the SR number with me as well.