How To Create A PDB From Scratch
Did you read my last post about the Multitenant Architecture? Today, we are going to go a bit deeper and learn about PDBs and how to create them.
PDBs can be created in multiple ways, however one thing is for sure, when you create the PDB you must be connected to a root, either the CDB Root (CDB$ROOT) or the application root of the application container, otherwise you will get an error. I think this is obvious, but I’ll state the obvious…you can only create PDBs in a Multitenant Architecture, not in a non-CDB database.
When you create a PDB, a GUID is assigned to it at creation time, a globally unique identifier. The GUID is mostly used to generate the directory name where the datafiles for the PDB will reside. Do not confuse GUID with DBID, these are two different things. Below is an example of the GUID usage in the datafile name.
SQL> select name, guid from v$pdbs;
NAME GUID
--------------- --------------------------------
PDB$SEED B77EDFC647625FA4E0530400000A77CB
DENMARK B7B3322E71514B40E0530400000A5893
FRANCE B7B3322E71544B40E0530400000A5893
...
11 rows selected.
SQL> alter session set container=DENMARK;
Session altered.
SQL> select file_name from dba_data_files;
FILE_NAME
-----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB2/B7B3322E71514B40E0530400000A5893/datafile/o1_mf_system_hyshpxb5_.dbf
/u01/app/oracle/oradata/CDB2/B7B3322E71514B40E0530400000A5893/datafile/o1_mf_sysaux_hyshpxbh_.dbf
/u01/app/oracle/oradata/CDB2/B7B3322E71514B40E0530400000A5893/datafile/o1_mf_undotbs1_hyshpxbl_.dbf
PDBs can be created in many ways: starting from scratch, cloning, relocating, plugging in, or referencing as a proxy. Each method has also multiple ways to go about it, which we will cover in future post.
Today we’ll look at creating PDBs from scratch, that is with the CREATE PLUGGABLE DATABASE statement. The PDB is created either from the PDB seed or from the application seed database. During this process the seed files are copied to the location of the new PDB, and are associated with the new PDB. This is why we have a seed PDB, to be able to create quickly PDBs from it.
You can create a PDB in a one line statement, with very few clauses, but also you can use a more complicated create statement, with multiple clauses.
CREATE PLUGGABLE DATABASE testpdb1 ADMIN USER dradmin IDENTIFIED BY password;
With this statement, you create the PDB either in the root container, if you are connected to the root, or in the application root if you are connected to the application root. If you create the PDB in the application root, then you basically created an application PDB. This will make more sense, when I cover the application containers in a future post. Once the database is created, it will be in MOUNTED state, and you will need to open it:
alter pluggable database testpdb1 open;
The create command completes very fast, the tablespaces from the seed are created (SYSTEM, SYSAUX, TEMP, UNDO), and the administrator user, a local user, in this case DRADMIN, is also created in the PDB. This user has the following role granted by default: PDB_DBA, with create pluggable database, and create session privileges in it.
SQL> alter session set container=testpdb1;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
14 TESTPDB READ WRITE NO
SQL> select tablespace_name from dba_tablespaces order by 1;
TABLESPACE_NAME
------------------------------
SYSAUX
SYSTEM
TEMP
UNDOTBS1
SQL> select username, common from dba_users where username='DRADMIN';
USERNAME COM
--------------- ---
DRADMIN NO
SQL> select granted_role from dba_role_privs where grantee='DRADMIN';
GRANTED_ROLE
--------------------------------------------------------------------------------
PDB_DBA
SQL> select PRIVILEGE from dba_sys_privs where grantee='PDB_DBA';
PRIVILEGE
----------------------------------------
CREATE PLUGGABLE DATABASE
CREATE SESSION
SQL> select PRIVILEGE from dba_sys_privs where grantee='DRADMIN';
no rows selected
The create database statement could be much more complicated, with different clauses. You can specify predefined roles to be granted to the administrator, you could specify other tablespaces to be created within the same statement, constraints on the tablespace sizes, and datafile location when not using OMF (instead of using the GUID naming convention). Below are some examples.
--example for non-OMF database
CREATE PLUGGABLE DATABASE testpdb2
ADMIN USER DRADMIN IDENTIFIED BY password
ROLES=(DBA)
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/CDB1/testpdb2/sales01.dbf'
SIZE 250M AUTOEXTEND ON
PATH_PREFIX = '/u01/app/oracle/oradata/CDB1/testpdb2/'
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/CDB1/datafile/',
'/u01/app/oracle/oradata/CDB1/testpdb2');
--example for OMF:
CREATE PLUGGABLE DATABASE testpdb3
ADMIN USER DRADMIN IDENTIFIED BY password
ROLES=(DBA)
DEFAULT TABLESPACE users datafile size 250M AUTOEXTEND ON;
Questions you might have about the database:
- How do I know if the database is a CDB or non-CDB?
- Can I see the datafiles of all the PDBs, in one spot, or do I need to connect to each PDB?
- Is the PDB part of the root container, or application root container?
These are all great questions to ask, I would ask them myself. So let’s see the answers to them.
Is the database CDB or non-CDB?
connect / as sysdba
col is_cdb for A10
SELECT name as db_name, cdb as Is_CDB, CON_ID FROM V$DATABASE;
DB_NAME IS_CDB CON_ID
--------- ---------- ----------
CDB1 YES 0
Check out the datafiles from within the root container. For this you would use the CDB_DATA_FILES view, and the DBA_PDBS. Please note that by default the PDB$SEED datafiles are not included. A hidden parameter needs to be set, in order to see the PDB$SEED datafiles. This is an expected behavior. Also the DBA_PDBS has no entry for the root container, but my statement will also show you that information.
alter system set "_EXCLUDE_SEED_CDB_VIEW" = FALSE ;
SET LINESIZE 150
SET PAGESIZE 100
COLUMN PDB_NAME FORMAT A8
COLUMN TABLESPACE_NAME FORMAT A15
COLUMN FILE_NAME FORMAT A45
SELECT case when p.pdb_id is null then 1
else p.pdb_id end as pdb_id
, case when p.PDB_NAME is null then 'CDB$ROOT'
else p.pdb_name end as pdb_name
, d.TABLESPACE_NAME, d.FILE_NAME
FROM DBA_PDBS p right join CDB_DATA_FILES d
on p.PDB_ID = d.CON_ID
ORDER BY 1;
PDB_ID PDB_NAME TBS_NAME FILE_NAME
------- -------- ---------- ---------------------------------------------
1 CDB$ROOT USERS /u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_hyll6tyv_.dbf
1 CDB$ROOT SYSAUX /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_hyll60dg_.dbf
1 CDB$ROOT SYSTEM /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_hyll4m16_.dbf
1 CDB$ROOT UNDOTBS1 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_hyll6sox_.dbf
2 PDB$SEED SYSTEM /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_hylls62d_.dbf
2 PDB$SEED UNDOTBS1 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_hylls62s_.dbf
2 PDB$SEED SYSAUX /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_hylls62o_.dbf
3 PDB1 SYSTEM /u01/app/oracle/oradata/CDB1/B77EB2745A16558DE0530400000AA52A/datafile/o1_mf_system_hylmj0w7_.dbf
3 PDB1 USERS /u01/app/oracle/oradata/CDB1/B77EB2745A16558DE0530400000AA52A/datafile/o1_mf_users_hylmjn39_.dbf
3 PDB1 UNDOTBS1 /u01/app/oracle/oradata/CDB1/B77EB2745A16558DE0530400000AA52A/datafile/o1_mf_undotbs1_hylmj0wq_.dbf
3 PDB1 SYSAUX /u01/app/oracle/oradata/CDB1/B77EB2745A16558DE0530400000AA52A/datafile/o1_mf_sysaux_hylmj0wn_.dbf
...
Is the PDB part of root container or application container?
col app_root for A8
col app_pdb for A8
col name for A15
select con_id, name
,APPLICATION_ROOT as APP_ROOT
,APPLICATION_PDB as APP_PDB
from v$pdbs order by con_id;
CON_ID NAME APP_ROOT APP_PDB
---------- ---------- -------- --------
2 PDB$SEED NO NO
3 PDB1 NO NO
4 PDB2 NO NO
5 APP_MASTER YES NO
6 APP_MASTER$SEED NO YES
7 APP1 NO YES
Today’s post is a bit longer than usual, and I really hope you found it useful! Here is a mind map to put everything together for this post.
If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!
If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the best price, only $18.99 CAD available until the end of the month!
