Oracle introduced the Oracle Database Multitenant concept with release 12c to ease the deployment of applications in the cloud by storing their data in a self-contained “Pluggable database” (PDB). I didn’t to have my data in the cloud right now but I wanted to store some test data into a new PDB. This small tutorial will show you how I created a new PDB for that purpose.
Starting with Oracle Database release 12c, a “Container database” (CDB) can consolidate multiple “Pluggable database” (PDB) by splitting the data dictionary between common objects in the CDB and the PDB specific objects in the PDB’s data dictionary. The idea is that it would allow to easily unplug a PDB from environment (on premises for instance) and to plug it into an other one (in the cloud for instance).
It’s not something that I needed right now, but I decided to create a new PDB for storing some test data. The process of creating a new PDB is really quick and straightforward as it is done by copying a seed PDB.
First, I connected to my oracle db instance as an administrator using the sqlplus command line.
I wanted to check what was the location of the seed data files used during the creation of the new PDB. I used the “select name from v$datafile;” statement to discover that were located in the”/opt/oracle/oradata/ORCLCDB/pdbseed/” folder.
I executed the “CREATED PLUGGABLE DATABASE” statement to create a new PDB named “DATAPDB” with the following parameters:
CREATE PLUGGABLE DATABASE DATAPDB
ADMIN USER dataadm IDENTIFIED BY apassword
DEFAULT TABLESPACE data
DATAFILE ‘/opt/oracle/oradata/ORCLCDB/DATAPDB/data.dbf‘ SIZE 250M
AUTOEXTEND ON
PATH_PREFIX = ‘/opt/oracle/oradata/ORCLCDB/DATAPDB/‘
FILE_NAME_CONVERT = (‘/opt/oracle/oradata/ORCLCDB/pdbseed/‘,
‘/opt/oracle/oradata/ORCLCDB/DATAPDB/‘);
- The “ADMIN USER” clause specified the local administrator (dataadm here) for the PDB and the “IDENTIFIED” provided a password.
- The “DEFAULT TABLESPACE” clause specified the name of a default permanent tablespace (data here).
- The “DATAFILE” clause specified the location of the single data file for the tablespace (‘/opt/oracle/oradata/ORCLCDB/DATAPDB/data.dbf‘ here).
- The “SIZE” clause specified the default size for the data file (250 MB here).
- The “AUTOEXTEND ON” clause enabled the automatic extension of the data file.
- The “PATH_PREFIX” clause specified the path prefix (‘/opt/oracle/oradata/ORCLCDB/DATAPDB/‘ here) added to the PDB’s directory object paths and it was mandatory.
- The “FILE_NAME_CONVERT” clause specified the location (‘/opt/oracle/oradata/ORCLCDB/pdbseed‘ here) of the PDB seed data files and the target location (‘/opt/oracle/oradata/ORCLCDB/DATAPDB/‘) to copy these files.
I checked the status of the PDBs using the “show pdbs” statement.
The new “DATAPDB” was currently mounted but not opened. Therefore I executed the “alter pluggable database DATAPDB open;” statement to open it.
I checked one more time the status of the PDBs using the “show pdbs” statement.
And this time the “DATAPDB” PDB was opened in “READ WRITE” mode and was therefore ready to be used.
I added a new entry in the “tnsnames.ora” file in order to be able to connect to the new PDB.
And finally I tried to connect to the new PDB using the new local administrator account.
And everything went smoothly. I now had a new PDB to store my test data!
The post How to create a new pluggable database (PDB) appeared first on MyBIJourney
