Plugging In an Unplugged PDB

How to Plug a PDB and Unplugged PDB in Multitenant Architecture. In this Tutorial we will do the procedures using the .pdb file methods.


Current Scenario


Currently in this example, the Database has a CDB$ROOT, where the PDB$SEED and ORADES_PDB01 belong to the CDB$ROOT. There is also an Application Container called APP_CONT_HMP01with an application container called HMP_PDB01.


Here's how the current scenario was created for this example: Access this link

show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORADES_PDB01                   READ WRITE NO
         4 APP_CONT_HMP01                 READ WRITE NO
         5 HMP_PDB01                      READ WRITE NO

Unplugging in an PDB


Unplugging a PDB disassociated the PDB from an CDB can be used in the following ways:

  • When you want to move the PDB to a different CDB;

  • Do you want to archive the PDB for later use;

  • When you no longer want the PDB to be available.

When disconnecting from the PDB, you can create an XML file that will contain metadata about the PDB or you can choose the extension (.pdb) which is more complete where it will be compressed into a single file with metadata, data files and the wallet file.


Connect to the root of CDB$ROOT and the PDB (ORADES_PDB01), it must be closed before being disconnected the unplugged PDB is in mounted mode.


Recommendation before any procedure makes a Full Backup!

sqlplus / as sysdba

ALTER PLUGGABLE DATABASE ORADES_PDB01 CLOSE IMMEDIATE;

Session altered.

Let's do the Unplugging PDB ORADES_PDB01 to archive and Plugging in another location.

It is necessary to create directories with enough space to properly archive.


ALTER PLUGGABLE DATABASE ORADES_PDB01 UNPLUG INTO '/u01/app/orades/my_directory/data/ORADES_PDB01.pdb';

Pluggable database altered.

We will use the DROP PLUGGABLE DATABASE clauses with INCLUDING DATAFILES to remove the data files from the disk.

DROP PLUGGABLE DATABASE ORADES_PDB01 INCLUDING DATAFILES;

Pluggable database dropped.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO 
         4 APP_CONT_HMP01                 READ WRITE NO
         5 HMP_PDB01                      READ WRITE NO                

The Pluggable ORADES_PDB01 ceased to exist but it was archived in (.pdb) format.


[orades@orades-19c data]$ pwd
/u01/app/orades/my_directory/data
[orades@orades-19c data]$ ls -la
drwxr-xr-x 2 orades oinstall        23 Sep 25 14:28 .
drwxr-xr-x 3 orades oinstall        18 Sep 25 14:25 ..
-rw-r--r-- 1 orades asmadmin 108540929 Sep 25 14:29 ORADES_PDB01.pdb


Plugging in an PDB


Let's understand some rules when connecting to a PDB and its users.

  • PDB users who use temporary tablespaces will use temporary tablespaces as the default for the target PDB;

  • Users who have common accounts that existed on the CDB but not on the destination CDB will no longer have common privileges, to avoid problems Close the PDB connect to root and create a common user account with the same name;

  • You can also use Data Pump to export / import the blocked user data to the new schema.


Pluggable database


Now let's take ORADES_PDB01 (orades_pdb_01.pdb) and plug it into CONTAINER APP_CONT_HMP01.

ALTER SESSION SET CONTAINER=app_cont_hmp01;

Session altered.

CREATE PLUGGABLE DATABASE ORADES_PDB01 USING '/u01/app/orades/my_directory/data/orades_pdb_01.pdb' 
COPY 
TEMPFILE REUSE;  

Pluggable database created.

show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ORADES_PDB01                   MOUNTED
         4 APP_CONT_HMP01                 READ WRITE NO
         5 HMP_PDB01                      READ WRITE NO
         

Let's open ORADES_PDB01

ALTER PLUGGABLE DATABASE ORADES_PDB01 OPEN;

Warning: PDB altered with errors.

Don't worry this warning is normal as ORADES_PDB01 has not yet become the full application's PDB.

See the alert log line '$ORACLE_BASE/diag/rdbms/orades/orades/trace/alert_orades.log':

Violations: Type: 1, Count: 1
ORADES_PDB01(6):*******************************************************
ORADES_PDB01(6):WARNING:Pluggable Database ORADES_PDB01 with pdb id - 6 is
ORADES_PDB01(6):    altered with errors or warnings. Please look into
ORADES_PDB01(6):    PDB_PLUG_IN_VIOLATIONS view for more details.
ORADES_PDB01(6):*******************************************************

The content of the PDB_PLUG_IN_VIOLATIONS view explains that the pdb_to_apppdb.sql script must be run for the PDB to be converted to a regular PDB.

SELECT cause, type, message, status, action FROM PDB_PLUG_IN_VIOLATIONS WHERE name='ORADES_PDB01';


Making an application PDB complete


Connect to the new PDB application 'ORADES_PDB01' and run the script $ORACLE_HOME/ rdbms/admin/pdb_to_apppdb.sql so that common application objects are accessible in the application's PDB.

The script automatically synchronizes the application's PDB with the application's root.


ALTER SESSION SET CONTAINER=ORADES_PDB01;

Session altered.

show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ORADES_PDB01                   READ WRITE YES     

Execute o script abaixo fazendo login no novo aplicativo PDB 'ORADES_PDB01'

@$ORACLE_HOME/rdbms/admin/pdb_to_apppdb

After the script finishes, I need to close and reopen the PDB.

ALTER PLUGGABLE DATABASE ORADES_PDB01 CLOSE IMMEDIATE;

Pluggable database altered.

ALTER PLUGGABLE DATABASE ORADES_PDB01 OPEN;

Pluggable database altered.

show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ORADES_PDB01                   READ WRITE NO  
exit; 

Log out and access sqlplus again and see that there are no more restrictions on PDBs.

show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED 
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORADES_PDB01                   READ WRITE NO
         4 APP_CONT_HMP01                 READ WRITE NO
         5 HMP_PDB01                      READ WRITE NO

Procedure performed successfully!