Convert Regular PDB to Application PDB

 in this article, we will see converting regular PDB to Application PDB.

Step 1: Check regular PDB

SQL> SELECT con_id, name, open_mode, application_root,application_pdb, application_seed
from v$containers
where application_root = 'NO' and application_PDb = 'NO'
and application_seed = 'NO';  2    3    4  

    CON_ID NAME 															    OPEN_MODE  APP APP APP
---------- -------------------------------------------------------------------------------------------------------------------------------- ---------- --- --- ---
	 1 CDB$ROOT															    READ WRITE NO  NO  NO
	 2 PDB$SEED															    READ ONLY  NO  NO  NO
	 3 TESTPDB1															    MOUNTED    NO  NO  NO
	 7 PDB1 															    READ WRITE NO  NO  NO

I have two pdbs which are not associated with any application container.

Step 2: Now I will check which application container is available in my CDB$ROOT

SQL> SELECT con_id, name, open_mode, application_root,application_pdb, application_seed
from v$containers
where application_root = 'YES';  2    3  

    CON_ID NAME 															    OPEN_MODE  APP APP APP
---------- -------------------------------------------------------------------------------------------------------------------------------- ---------- --- --- ---
	 4 APP_PDB															    READ WRITE YES NO  NO
	 6 F348281081_21_1														    READ ONLY  YES YES NO

I have two application containers available F348281081_21_1 PDB is a clone of APP_PDB which was created while upgrading APP_PDB application.

Step 3: You can clone or unplug-plug regular pdb to create it as an Application PDB :

We will create a clone of PDB1 in the application container.

Step 4: Connect to application pdb

SQL> alter session set container=app_pdb;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
APP_PDB

Check application PDBs available with app_pdb

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 4 APP_PDB			  READ WRITE NO
	 5 APP_SAL			  MOUNTED

Step 5: Close PDB1 by connecting it from pdb1 service or connecting with CDB$ROOT

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

Step 6: Open pluggable database in a read-only state

SQL> alter pluggable database pdb1 open read only;

Pluggable database altered.

Step 7: Clone pdb1 to orapdb1

SQL> CREATE PLUGGABLE DATABASE ORAPDB1 FROM PDB1;

Pluggable database created.

Step 8: check show pdbs

SQL> SHOW PDBS;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 4 APP_PDB			  READ WRITE NO
	 5 APP_SAL			  MOUNTED
	 8 ORAPDB1			  MOUNTED

Step 9: Open orapdb1

SQL> alter pluggable database orapdb1 open ;

Warning: PDB altered with errors.

Step 10: Opening orapdb1 gives warning that PDB altered with errors so let us check pdb_plug_in_violations view to view errors associated with this PDB

SQL> SELECT NAME,CAUSE,ERROR_NUMBER,MESSAGE,STATUS FROM PDB_PLUG_IN_VIOLATIONS WHERE NAME='ORAPDB1';

NAME		     CAUSE							      ERROR_NUMBER MESSAGE					      STATUS
-------------------- ---------------------------------------------------------------- ------------ -------------------------------------------------- ---------
ORAPDB1 	     Non-Application PDB to Application PDB					 0 Non-Application PDB plugged in as an Application P PENDING
												   DB, requires pdb_to_apppdb.sql be run.

It says we need to run a pdb_to_apppdb.sql file to resolve this error.

Step 11: Connect to orapdb1 and run pdb_to_apppdb.sql

SQL> alter session set container=orapdb1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
ORAPDB1

Step 12: run pdb_to_apppdb SQL file

SQL> @?/rdbms/admin/pdb_to_apppdb

Step 13 : Check v$pdbs :

SQL> SELECT con_id, name, open_mode, application_root,application_pdb, application_seed from v$containers where application_pdb='YES';

    CON_ID NAME 															    OPEN_MODE  APP APP APP
---------- -------------------------------------------------------------------------------------------------------------------------------- ---------- --- --- ---
	 5 APP_SAL															    MOUNTED    NO  YES NO
	 6 F348281081_21_1														    READ ONLY  YES YES NO
	 8 ORAPDB1															    READ WRITE NO  YES NO

We can see here ORAPDB1 is listed as application pdb.

Stay tuned for More articles on Oracle Multitenant