Implementing Databases

Creating a PDB first requires that you have a 12c database as a container. If the DBA does not explicitly select this or specify the ENABLE PLUGGABLE DATABASE keyword, the result is a normal database without the possibility of housing new databases. In this case, you need to create a new database. Creating a PDB always involves copying an existing PDB. This is either the Seed Database PDB$SEED, which is implicitly created when creating the container and cannot be changed, or some other PDB. This database, however, must have OPEN READ-ONLY status while the copy is being made.

Additionally, a PDB administrator must specify who owns the PDB_DBA role. This role does not have any permissions by default, but additional permissions and roles can be assigned when creating the PDB if desired. Before the PDB can be created, the uniqueness of the resulting file name must be ensured. This step is done either manually via the FILE_NAME_CONVERT parameter or automatically using OMF. For simplicity, we recommend using OMF and ASM here (Listing 1).

SQL> select * from CDB_PDBS order by pdb_id;
PDB_ID PDB_NAME  DBID       CON_UID    GUID                             STATUS   CREATION_SCN
=====================================================================;
2      PDB$SEED  4062623230 4062623230 E0C9D94CE3B6497BE04380B0A8C06105 NORMAL        1720734      1
SQL> select name from v$datafile order by 1;
====================================================================;
DATA01/CDB1/DATAFILE/sysaux.256.820013801
DATA01/CDB1/DATAFILE/sysaux.257.820013845
DATA01/CDB1/DATAFILE/undotbsl.259.820013893
DATA01/CDB1/DATAFILE/users.258.820013891
DATA01/CDB1/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.265.820013923
DATA01/CDB1/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.266.820013923
6 rows selected
;
SQL> create pluggable database pdb001 admin user admin identified by;
Pluggable database created.
SQL> alter pluggable database pdb001 open;
Pluggable database altered.
SQL> select * from CDB_PDBS order by pdb_id;
PDB_ID PDB_NAM  DBID       CON_UID    GUID                             STATUS   CREATION_SCN CON_ID
------ -------- ---------- ---------- -------------------------------------------------------------
2      PDB$SEED 4062623230 4062623230 E0C9D94CE3B6497BE04380B0A8C06105 NORMAL        1720734      1
3      PDB001   1700339437 1700339437 E0D0BE79135B75B0E04380B0A8C00F14 NORMAL        1956354      1
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------------
+DATA01/CDB1/DATAFILE/sysaux.256.820013801
+DATA01/CDB1/DATAFILE/system.257.820013845
+DATA01/CDB1/DATAFILE/undotbs1.259.820013893
+DATA01/CDB1/DATAFILE/users.258.820013891
+DATA01/CDB1/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.265.820013923
+DATA01/CDB1/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.266.820013923
+DATA01/CDB1/E0D0BE79135B75B0E04380B0A8C00F14/DATAFILE/sysaux.271.820043565
+DATA01/CDB1/E0D0BE79135B75B0E04380B0A8C00F14/DATAFILE/system.275.820043565

As you can see from Listing 1, PDBs must be opened manually before use and after each start of the instance. You can write a trigger for this or use the grid infrastructure. Oracle Restart then registers resources and their dependencies, monitors where appropriate, or starts and stops in the correct order in the usual way. Services handle this process for PDBs. As soon as you create a service and associate it with a PDB, the associated database is automatically opened when the corresponding service starts.

The information available at PDB and CDB level follows the principle of visibility. For example, an AWR report  – run against a PDB  – only contains information about the PDB in question. However, an AWR report at CDB level contains information on all databases. The situation is similar for additional views that use the CDB_ prefix. They contain information about all PDBs.

Upgrade Paths

You have three possible approaches for shifting from a non-PDB to a PDB: You can export/import using Data Pump (as of 10g Release 1, older versions may need to make an intermediate step), use GoldenGate, or convert a non-CDB to a PDB. The 10g or 11g database must be upgraded to 12c (Figure 3).

Figure 3: Several innovations relate to ease of installation and upgrading from earlier versions of Oracle to the new release.

During the conversion process, the database can be converted to a PDB using the DBMS_PDB package (after upgrading to 12c) and then plugged into a CDB. In-place upgrades are possible so far from 10.2.0.5, 11.1.0.7, 11.2.0.2, and higher; the result is initially a non-CDB.

Use of this new concept requires the Enterprise Edition and presupposes the additional multitenancy license. At list price, the license costs as much as the Enterprise Edition itself. The Standard Edition can also use PDBs; however, the number of possible databases is limited to one. This may sound like a bad joke, but the next version, 12.2, will no longer have classical non-CDB databases; thus, non-PDB databases need to be converted – even in the Standard Edition.

Hierarchical Storage Management

Also new in version 12c is the ability to apply rules automatically to tables, partitions, or blocks based on the last access (read and write, full-table scan, or single access) that describe what will happen to the object after a specific deadline elapses. These rules envision, for example, that after a variable number of days since the last read/write, full-table scan, or single-row access, the table, partition, or block in question is either compressed or moved to a different tablespace.

For example, it is possible to compress a partition to save space after 30 days without access using advanced compression. After 60 days without access, compression can be increased again through the use of (E)HCC, and after 90 days without access, the already compressed partition can be shifted to another tablespace that resides on a slower and cheaper storage. In this way, the database can separate frequently used data from infrequently used data, automatically compress the latter, and shift it to cheaper storage.