Pluggable Database in Oracle is a great feature of Multitenant architecture which is called PDB and PDB is created under CDB. To know details of CDB and PDB, you can go my article: Overview of CDB and PDB in Oracle Database - step by step to understand CDB and PDB
This article describes how to Create New Pluggable Databases in Oracle 19c.
First of all, connect database with SQLPlus with sys user: sqlplus /nolog
SQL> connect sys as sysdba; Enter password:
At now, to check connection name to make sure you’re in the correct location, type
SQL> show con_name;
Output will as like:
CON_NAME ------------------------------ CDB$ROOT
And, to check PDBs:
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORAPDB READ WRITE NO 5 MFIPDB READ WRITE NO
Here we have two PDB and one root PDB which is called SEED PDB.
To create pdb database, first In SQL*Plus, ensure that the current container is the CDB root or an application root. after connect
CREATE PLUGGABLE DATABASE pdb_name ADMIN USER pdb_database_admin_user IDENTIFIED BY pdb_database_admin_user_password FILE_NAME_CONVERT = (pdbseed_location, new_pdb_location);
Example:
CREATE PLUGGABLE DATABASE pdbdev ADMIN USER pdbdevadmin IDENTIFIED BY DevAdmin#123 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/ORCL/pdbseed/', '/u01/app/oracle/oradata/ORCL/pdbdev/');
First of all we need to create new_pdb_location using following command:
mkdir /u01/app/oracle/oradata/ORCL/pdbdev/
Check all pdbs:
Show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORAPDB READ WRITE NO 4 PDBDEV MOUNTED 5 MFIPDB READ WRITE NO
When database is created, by default, database is MOUNTED mode and need to open it.
SQL> alter pluggable database PDBDEV OPEN;
Pluggable database altered.
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORAPDB READ WRITE NO 4 PDBDEV READ WRITE NO 5 MFIPDB READ WRITE NO SQL> show con_name; CDB$ROOT
When you connect to the Oracle database server, you connect to a container database (CDB) named ROOT. To show the current database, you use the SHOW command:
SQL> SHOW con_name; CON_NAME ------------------------------ CDB$ROOT
Next, you need to switch to a pluggable database. Noted that during the installation of Oracle, we already created a pluggable database named pdbdev.
To switch to the pdbdev pluggable database, you use the following statement:
SQL> ALTER SESSION SET CONTAINER = pdbdev; Session altered.
Note that if you use a different pluggable database, feel free to change it in the command.
If you execute the show command again, the database now is PDBORCL.
SQL> SHOW con_name; CON_NAME ------------------------------ PDBDEV
Now pluggable database pdbdev is connected to use. This the all to create PDB from CDB from scratch and if you want to use this database, go through following sections.
Before creating a new user, you need to change the database to open by executing the following command:
SQL> ALTER DATABASE OPEN; Database altered.
Then, you create a new user for creating the sample database in the pluggable database using the following CREATE USER statement:
SQL> CREATE USER saidul IDENTIFIED BY Saidul#123; User created.
The above statement created a new user named saidul with a password specified after the IDENTIFIED BY clause, which is Saidul#123 in this case.
After that, you grant privileges to the saidul user by using the following GRANT statement:
SQL> GRANT CONNECT, RESOURCE, DBA TO saidul; Grant succeeded.
Finally, you can connect to the pluggable database ( PDBORCL) using the OT user account. Type the password ( Orcl1234) for the OT user when SQL*plus prompts you for the password.
SQL> CONNECT saidul@pdbdev Enter password: Connected
Note that saidul user only exists in the PDBDEV pluggable database, therefore, you must explicitly specify the username as saidul@pdbdev in the CONNECT command.
If show error during connection as like
ERROR: ORA-12154: TNS:could not resolve the connect identifier specified Warning: You are no longer connected to ORACLE.
You need to update ltnsnames.ora. Just open tnsnames.ora file and append following line
LISTENER_PDBDEV = (ADDRESS = (PROTOCOL = TCP)(HOST = oradb.mfi.com)(PORT = 1521)) PDBDEV = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oradb.mfi.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdbdev.mfi.com) ) )
Restart listener and try to connect again. I hope you will see:
Connected.
Enjoy!
Use the CREATE PLUGGABLE DATABASE statement to create a PDB in a CDB using the files of the PDB seed (PDB$SEED). This statement enables you to perform the following tasks: