How to Create a Pluggable Database (PDB)

  • by

There're several ways that can create a PDB.

  1. Create a PDB by using SQL*Plus
  2. Create a PDB by using DBCA
  3. Create a PDB by cloning from a local PDB.
  4. Create a PDB by cloning from a remote PDB.

A. Create a PDB by Using SQL*Plus

To create a PDB, the best way is to create it by SQL*Plus, it's simple and fast. Here in this case, we create a simple, nearly empty PDB from PDB$SEED.

1. Create PDB

Let's check current PDBs.

SQL> conn / as sysdba
Connected.
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO

We have PDB$SEED and ORCLPDB1 currently. Now we start to create a PDB named ORCLPDB2.

SQL> CREATE PLUGGABLE DATABASE ORCLPDB2 ADMIN USER PDBADMIN IDENTIFIED BY zaq1xsw2 DEFAULT TABLESPACE USERS DATAFILE '/u01/app/oracle/oradata/ORA19C1/ORCLPDB2/users01.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED FILE_NAME_CONVERT=('/u01/app/oracle/oradata/ORA19C1/pdbseed/','/u01/app/oracle/oradata/ORA19C1/ORCLPDB2/');

Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO
         4 ORCLPDB2                       MOUNTED

As you can see, we create the PDB with default tablespace USERS for new accounts. In which, FILE_NAME_CONVERT is required for creating a PDB from seed.

Please note that, don't use PATH_PREFIX as an attribute of creating a PDB. It's a pretty rigid constraint in UNIX platform, even though you use the latest release 19.3.

Since we created the PDB from pdbseed, so all data files are copied from the pdbseed, including tempfile.

SQL> alter session set container=ORCLPDB2;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
ORCLPDB2

2. Rename Tempfile

Let's see what tempfile we have.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA19C1/ORCLPDB2/temp012020-07-14_23-10-41-107-PM.dbf

Does it conform to your naming rule? If not, we should rename it.

SQL> alter database rename file '/u01/app/oracle/oradata/ORA19C1/ORCLPDB2/temp012020-07-14_23-10-41-107-PM.dbf' to '/u01/app/oracle/oradata/ORA19C1/ORCLPDB2/temp01.dbf';

Database altered.

3. Open PDB

Although we rename the tempfile, we still can open the database, because the database will create any missing tempfile for us.

SQL> alter pluggable database ORCLPDB2 open;

Pluggable database altered.

Let's check the new tempfile.

[oracle@ora19c1 ~]$ ll /u01/app/oracle/oradata/ORA19C1/ORCLPDB2/temp01.dbf
-rw-r----- 1 oracle oinstall 51388416 Mar 23 23:40 /u01/app/oracle/oradata/ORA19C1/ORCLPDB2/temp01.dbf

It's there.

4. Remove Original Tempfile

Since the old tempfile is no longer used, we should remove it.

[oracle@ora19c1 ~]$ rm /u01/app/oracle/oradata/ORA19C1/ORCLPDB2/temp012020-07-14_23-10-41-107-PM.dbf

B. Create a PDB by Using DBCA

Leave a Reply

Your email address will not be published. Required fields are marked *