How to create pdb in oracle 12c.

How to create a pluggable database PDB in a multitenant container database CDB using the files of the seed PDB$SEED

The new architecture provided by Oracle Database 12c enables an Oracle database to function as a
multitenant container database (CDB):
it can include zero, one, or many pluggable databases (PDBs), that is a portable collection of schemas, schema objects and nonschema objects.

A multitenant container database (CDB) is formed by the following components:
- the root container (and exactly one root) named CDB$ROOT that stores Oracle-supplied metadata and common users
 (a database user known in every container);
- the seed template (and exactly one seed) named PDB$SEED used, if you want, to create new PDBs.
It's not possible to add objects to or modify objects in the seed: it works only in READ ONLY mode;
- pluggable databases (zero, one, or many PDBs) named as you prefer that are your "old idea of databases"
 before Oracle Database 12c. A pluggable database contains the data and code required by your software
application and is fully backward compatible with Oracle Database releases before Oracle Database 12c.

The options for creating a pluggable database (the so called PDB) fall into two main categories:
copying and plugging in as you will see in this post and in the next posts.

Talking about copying, you have two options to copy a pluggable database:
create a pluggable database (PDB) in a multitenant container database (CDB) using the files of the seed (PDB$SEED);
create it by cloning a source PDB and plugging the clone into the CDB (bear in mind that the source PDB can be in the local CDB or in a remote CDB).

Today I'm going to describe how to copy a pluggable database using the files of the seed from the SQL*Plus command line.

If you want to see how to create a pluggable database copying the files of the seed using DBCA you can simply watch the following video (I will create a post about it using screenshot as soon as possible).

To create a new pluggable database copying the files of the seed you have to ensure that the current container is the root: 

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

your multitenant container database CDB must be also in read/write mode. My CDB is called CDB001:

SQL> select NAME, CDB, CON_ID, OPEN_MODE from V$DATABASE;

NAME   CDB   CON_ID OPEN_MODE
--------- --- ---------- --------------------
CDB001   YES        0 READ WRITE

Your script must include the CREATE PLUGGABLE DATABASE statement and eventually some other clauses, such as the following I used in my script:

- STORAGE: specify the limit of the amount of storage the PDB can use. Omitting this clause is equivalent to specify an unlimited amount;

- DEFAULT TABLESPACE: specify a default permanent tablespace for non-SYSTEM users. When you omit this clause the SYSTEM tablespace will be used as default permanent tablespace for non-SYSTEM users and this is not recommended;

- FILE_NAME_CONVERT: specify the target locations of the data files whereas the source files are those associated with the seed.

This parameter is required when Oracle Managed Files is not enabled and the PDB_FILE_NAME_CONVERT initialization parameter is not set

- other clauses you can use are: ROLES, TEMPFILE REUSE and PATH_PREFIX.

SQL> CREATE PLUGGABLE DATABASE PDB002
  2  ADMIN USER PDB002_ADMIN IDENTIFIED BY oracle
  3  storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M)
  4  DEFAULT TABLESPACE "USERS" DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON
  5  file_name_convert=('/app/oracle/oradata/CDB001/pdbseed/system01.dbf','/app/oracle/oradata/CDB001/PDB002/system01.dbf','/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf','/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf','/app/oracle/oradata/CDB001/pdbseed/temp01.dbf','/app/oracle/oradata/CDB001/PDB002/temp01.dbf');

Pluggable database created.
From the alert log (vi /app/oracle/diag/rdbms/cdb001/CDB001/trace/alert_CDB001.log):
Sat Jul 20 16:21:48 2013
CREATE PLUGGABLE DATABASE PDB002 ADMIN USER PDB002_ADMIN IDENTIFIED BY * storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M) DEFAULT TABLESPACE "USERS" DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON file_name_convert=('/app/oracle/oradata/CDB001/pdbseed/system01.dbf','/app/oracle/oradata/CDB001/PDB002/system01.dbf','/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf','/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf','/app/oracle/oradata/CDB001/pdbseed/temp01.dbf','/app/oracle/oradata/CDB001/PDB002/temp01.dbf')
Sat Jul 20 16:22:35 2013
****************************************************************
Pluggable Database PDB002 with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Deleting old file#2 from file$
Deleting old file#4 from file$
Adding new file#10 to file$(old file#2)
Adding new file#11 to file$(old file#4)
Successfully created internal service pdb002 at open
CREATE TABLESPACE USERS DATAFILE  '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON  SEGMENT SPACE MANAGEMENT AUTO
Completed: CREATE TABLESPACE USERS DATAFILE  '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON  SEGMENT SPACE MANAGEMENT AUTO
Sat Jul 20 16:22:49 2013
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB002 with pdb id - 4 is now marked as NEW.
****************************************************************
Completed: CREATE PLUGGABLE DATABASE PDB002 ADMIN USER PDB002_ADMIN IDENTIFIED BY * storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M) DEFAULT TABLESPACE "USERS" DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON file_name_convert=('/app/oracle/oradata/CDB001/pdbseed/system01.dbf','/app/oracle/oradata/CDB001/PDB002/system01.dbf','/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf','/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf','/app/oracle/oradata/CDB001/pdbseed/temp01.dbf','/app/oracle/oradata/CDB001/PDB002/temp01.dbf')

As you can see when you create a pluggable database from the SQL*Plus command line it is open in MOUNTED mode.
This is a different behaviour compared with the same operation performed by DBCA: in the final step DBCA is able to open the new pluggable database in READ WRITE mode.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          MOUNTED     4

To open your new pluggable database in READ WRITE mode execute the following alter pluggable database command:

SQL> alter pluggable database pdb002 open read write;

Pluggable database altered.

Now query again the V$PDBS view: your pluggable database is now available to the application.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          READ WRITE    4

Create Pluggable Database Manually using SQL Prompt

we will have a look & hands-on on creating a PDB manually with the help of SQL Prompt.

We can create PDB using PDB_FILE_NAME_CONVERT initialization parameter,
In following example i am going to create new Pluggable Database, name: ‘newpdb’

[oracle@OL6 ~]$ sqlplus “/ as sysdba”
// Ensure database globale name by following SQL query:

SQL> select global_name from global_name;

GLOBAL_NAME
——————————————————————————–
ORCL

// Create new pluggable database by following set of SQL queries:
SQL> ALTER SESSION SET PDB_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/pdbseed/’,’/u01/app/oracle/oradata/newpdb/’;

Session altered.

SQL> CREATE PLUGGABLE DATABASE newpdb ADMIN USER pdbadmin IDENTIFIED BY Admin123;

Pluggable database created.


// Pluggable database has been created successfully, ensure changes by issuing the following query:
column pdb_name FORMAT A25;
SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
NEWPDB MOUNTED

OR

column pdb_name FORMAT A25;

SQL>SELECT pdb_name, status FROM dba_pdbs ORDER BY pdb_name;

PDB_NAME STATUS
————————- ————-
NEWPDB NEW
PDB$SEED NORMAL

// Above result shows that, newly created pluggable database is in mount state,
Pluggable databases automatically created in mount state, Issue the following query to open pluggable database:

SQL> alter pluggable database newpdb open;

Pluggable database altered.


// Ensure the changes by following SQL query, Now newly created pluggable database is in READ WRITE (open) state.

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
NEWPDB READ WRITE

Connect your pluggable database with following:

SQL> show con_name

CON_NAME
——————————
CDB$ROOT

SQL> alter session set container=newpdb;

Session altered.

SQL> show con_name

CON_NAME
——————————
NEWPDB

You are now connected to your newly created PDB.