How to create a pluggable database? Options

codeling 1181 - 5025
@2016-12-06 14:21:10
By default, there are two PDBs created. One is the default database created during installation (PDBORCL12C) and the other one is a seed database which you can use to create new PDBs. To find out the list of PDBs, you can query the v$pdb or use the show pdbs command: 
c:\> sqlplus /nolog
SQL> conn /as sysdba

SQL> show pdbs

   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
--------- ------------------------------ ---------- ----------
        2 PDB$SEED                       READ ONLY  NO
        3 PDBORCL12C                     MOUNTED
SQL> select name, open_mode,con_id from v$pdbs;

NAME         OPEN_MODE      CON_ID
------------ ---------- ----------
PDB$SEED     READ ONLY           2
PDBORCL12C   MOUNTED             3

SQL> col NAME format A12
SQL> select FILE#,, ts.ts#, ts.con_id
  2  from v$datafile d, v$tablespace ts
  3  where d.ts#=ts.ts#
  4  and d.con_id=ts.con_id
  5  order by 4,3;
     FILE# NAME                TS#     CON_ID
---------- ------------ ---------- ----------
         1 SYSTEM                0          1
         3 SYSAUX                1          1
         5 UNDOTBS1              2          1
         6 USERS                 4          1
         2 SYSTEM                0          2
         4 SYSAUX                1          2
         7 SYSTEM                0          3
         8 SYSAUX                1          3
         9 USERS                 3          3
        10 EXAMPLE               4          3
10 rows selected.
The second SQL query shows the database files associated with each database. You can see that the CDB (CON_ID=1) has SYSTEM, SYSAUX, UNDOTBS1 and USERS files. The seed PDB, pdb$seed(CON_ID=2),  has two SYSETM and SYSAUX files. The pdborcl12c database created by default (CON_ID=3) has SYSTEM, SYSAUX, USERS and EXAMPLE data files.  

If we query the v$datafile view, we can verify the details for each PDBs. The following example list the database files for PDB 
SQL> select name from v$datafile where con_id = 3;
Step 2: Create a new pluggable database 

Now, we can create a pluggable database. If you are not sure that you are in the container database, you can use the following command:
SQL> alter session set container=cdb$root;
Session altered.
The we can create a new pluggable database from the seed database. You can also copy from an existing PDB. 

Why do I get the "ORA-65081: database or pluggable database is not open in read only mode." error?
You need to make sure the exisiting database is in open and read only mode before creating PDB from it. The following command will be helpful.
SQL>alter pluggable database pdb1 close;
Pluggable database altered.
SQL>alter pluggable database pdb2 open read only;
Pluggable database altered.

SQL> create pluggable database pdbdemo admin user admin identified by admin
file_name_convert = ('pdbseed', 'pdbdemo');
Pluggable database created.

SQL> select pdb_name from cdb_pdbs;

SQL> col name format a30
SQL> select Name, Open_Mode, Inst_ID from gv$PDBs;

NAME                           OPEN_MODE     INST_ID
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           1
PDBORCL12C                     MOUNTED             1
PDBDEMO                        MOUNTED             1

SQL> alter pluggable database pdbdemo open;
Pluggable database altered.

SQL> select Name, Open_Mode, Inst_ID from gv$PDBs;

NAME                           OPEN_MODE     INST_ID
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           1
PDBORCL12C                     MOUNTED             1
PDBDEMO                        READ WRITE          1

SQL> select name, con_id from v$active_services order by 1;
NAME                               CON_ID
------------------------------ ----------
SYS$BACKGROUND                          1
SYS$USERS                               1                   1
orcl12cXDB                              1                   4                3
6 rows selected.
If logging into a PDB, the operations are pretty much the same as a database in Oracle Database 11g.  The default tablespace for the newly created PDB from the seed  is SYSTEM. 
SQL> alter session set container=pdbdemo;
Session altered.

SQL> show con_name

SQL> show user

SQL> select property_value from database_properties
     where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

Why do I get the "ORA-65016: FILE_NAME_CONVERT must be specified" error when creating pluggable database?
The file_name_convert specifies how new data filenames are derived from those of the seed or from an exsiting PDB. During the PDB creation Oracle Database 13c actually only copies 2 database files, for the System and the Sysaux tablespaces, to the location of new PDB. The other database files for undo, redo, and so on are global for the whole CDB and belongs to the container called CDB$Root.

Step 3: Dropping PDBs 

If you want to remove a PDB created, you can run the following command in the CDB. The PDB to be deleted have to be closed before deletion: 
SQL> alter session set container=cdb$root;
Session altered.

SQL> drop pluggable database pdbdemo including datafiles;
drop pluggable database seed_copy including datafiles
ERROR at line 1:
ORA-65025: Pluggable database PDBDEMO is not closed on all instances.

SQL> alter pluggable database pdbdemo close;
Pluggable database altered.

SQL> drop pluggable database pdbdemo including datafiles;
Pluggable database dropped.

codeling 1181 - 5025
@2017-10-20 23:12:20

Pluggable Database not open automatically

After restarting the server, pluggable databases are in Mounted mode.

SQL> select Name, Open_Mode, Inst_ID from gv$PDBs;

NAME                           OPEN_MODE     INST_ID
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           1
PDBORCL12C                     MOUNTED             1
PDBDEMO                        MOUNTED             1

Of couse, you can open all pdbs by using the following command every time after restarting the computer:

SQL> alter pluggable database all open;

But you can save the open state by using the following command:

SQL> alter pluggable database PDBDEMO save state;

Oracle will then preserve the mode when you restart the CDB. You can find the current saved state by querying dba_pdb_saved_states.

If you have several and want them all to open on restart, you can do:

SQL> alter pluggable database all save state;


Users browsing this topic