This post looks the option of creating a database using a template, where the template was created from a CDB containing a PDB. During the testing it was observed that 12.1, 12.2 and 18c behaves differently.
The following steps were done on databases of all three versions. Two additional tablespaces were created in the root container (called ROOTBS) and in the PBD (called TEST).
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBONE READ WRITE NO
CON_ID NAME
---------- -------
1 USERS
1 ROOTBS
1 SYSAUX
1 SYSTEM
1 UNDOTBS1
1 TEMP
2 SYSAUX
2 SYSTEM
2 TEMP
3 SYSAUX
3 TEMP
3 TEST
3 SYSTEM
The basic template creation steps are shown below (for 12.1 only).
Creating CDB Using Template in 12.1
Select the template created earlier during the create database using DBCA. The show details button will give a detail list of tablespace included in the template. In this case it shows the two tablespaces created earlier.
Summary page also list the tablespaces.
During the DB creation process following error is shown but DBCA is able to run to completion.
At the end of the CDB creation, the newly created CDB will have a PDB, with the same name as the PDB where the template was created from.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBONE READ WRITE NOThe new CDB will also have the both root container level tablespace and PDB level tablespace.
SQL> select con_id,name from v$tablespace order by 1;
CON_ID NAME
---------- ------------------------------
1 ROOTBS
1 USERS
1 SYSAUX
1 SYSTEM
1 UNDOTBS1
1 TEMP
2 SYSAUX
2 SYSTEM
2 TEMP
3 SYSTEM
3 SYSAUX
3 TEMP
3 TEST
Creating CDB Using Template in 12.2
Similar to 12.1 a template was created on 12.2 DB. Additional tablespace created for root container and on PDB.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DEVPDB READ WRITE NO
SQL> select con_id,name from v$tablespace order by 1;
CON_ID NAME
---------- ------------------------------
1 USERS
1 SYSAUX
1 TEMP
1 SYSTEM
1 UNDOTBS1
1 ROOTBS
2 TEMP
2 UNDOTBS1
2 SYSAUX
2 SYSTEM
3 SYSAUX
3 UNDOTBS1
3 TEMP
3 USERS
3 ASANGA
3 SYSTEMThe DB creation was done using the template.
The summary page also list the additional tablespaces.
However due to
existing issue (tracked under bug 26921308) DB creation fails.
Creating CDB Using Template in 18c
The 18c database had following additional tablespaces for root container and PDB.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DOCKLAND READ WRITE NO
SQL> select con_id,name from v$tablespace order by 1;
CON_ID NAME
---------- ------------------------------
1 USERS
1 SYSAUX
1 TEMP
1 SYSTEM
1 UNDOTBS1
1 ROOTBS
2 TEMP
2 UNDOTBS1
2 SYSAUX
2 SYSTEM
3 SYSAUX
3 UNDOTBS1
3 TEMP
3 USERS
3 ASANGA
3 SYSTEMThe Database was created using the template which showed the tablespaces for PDB as well.
However, the newly created CDB didn't have any new PDBs. But it had a the root container tablespaces which was listed on the template.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL> select con_id,name from v$tablespace order by 1;
CON_ID NAME
---------- ------------------------------
1 SYSTEM
1 UNDOTBS1
1 ROOTBS
1 TEMP
1 USERS
1 SYSAUX
2 TEMP
2 SYSAUX
2 UNDOTBS1
2 SYSTEMThis shows that DBCA templates for CDB and for PDBs other methods such as
cloning or transporting must be used.
Related Metalink Notes
Can PDB Templates be Created in DBCA?
[ID 2128673.1]
12.2 Dbca: Custom Template Doesn't Work As Expected With Cdb Option
[ID 2283829.1]
ORA-65101 Wrong option for CDB parameter in a Template which created by DBCA with silent mode
[ID 2270420.1]
12.2 DBCA TEMPLATE NOT SAVE SETTING FOR "INCLUDE IN PDBS" OF "DATABASE OPTIONS"
[ID 2274760.1]
