Migrating an Existing Large Pluggable Database to an Application Container

Oracle Multitenant Application Containers is an optional deployment topology in Oracle Multitenant where a user created Container Database (CDB) stores data and metadata related to one or more application back ends. An Application Container consists of an Application Root where a master definition of an application is created and managed (table, view, package definitions for example) and where hosted application pluggable databases may share common data and metadata defined and managed in the Application Root. 

Diagram 1. Oracle Multitenant Application Containers Topology

This blog demonstrates the use case where you already have a very large PDB and want to create an Application Container without having to execute a full database clone. The diagram below describes a large PDB hosted in the same Container Database (CDB) as the target Application Container. The steps described below are relevant for environments where the source large PDB is hosted in another CDB.

Diagram 2. Oracle Multitenant Large Pluggable Database Migration as Application Root

For this operation we demonstrate how to execute a metadata clone, define Data Linked objects in the Application Root and set data sharing for targeted objects assigned to specific users. In our example below we leverage the following Oracle Multitenant technologies:

• Pluggable database metadata cloning
• Multitenant pluggable database unplug
• Multitenant plug-in operation as application container with the NOCOPY option
• Application installation in the APPLICATION$ROOT container
• The following subprograms in the DBMS_PDB_ALTER_SHARING package:
  • DBMS_PDB_ALTER_SHARING.SET_USER_EXPLICIT()
  • DBMS_PDB_ALTER_SHARING.SET_METADATA_LINKED()
  • DBMS_PDB_ALTER_SHARING.SET_DATA_LINKED()
  • DBMS_PDB_ALTER_SHARING.SET_EXT_DATA_LINKED()

Further details on the Oracle Multitenant Application Containers architecture, usage and migrating existing applications are available in the following documentation.

• About Migrating an Existing Application to an Application Container
  You can migrate an application to an application root by creating an application root using an existing PDB.
• Creating an Application Root Using an Existing PDB
  Migrate an application that is installed in a PDB by copying the PDB to an application container.
• Creating an Application PDB Using an Existing PDB
  After migrating an existing application to an application root, you can use an existing PDB that uses the application to create an application PDB.

Steps to migrate a large PDB to an Oracle Multitenant Application:

We need to migrate a large PDB (TESTPDB) to an application container (APPROOT) and use the PDB schema within the application without recreating the schema in the application root. Additionally, we need to create an application PDB (APPPDB1) under the application root. The database users defined below – SHUSER1, SHUSER2, SHUSER3 are fictional users for the purposes of this demonstration. You will replace these users with the users relevant to your environement.

   1. Create a PDB from large PDB using Metadata clone for App PDB.

— Connect to CDB$ROOT. 
create pluggable database PDB_META1 from TESTPDB no data;
 

 2. Unplug large PDB and drop PDB keeping datafiles.

 — Connect to CDB$ROOT.
alter pluggable database TESTPDB close instances=all;
alter pluggable database TESTPDB unplug into ‘/scratch/sysvldb/testpdb3.xml’;
drop pluggable database TESTPDB keep datafiles;
 

 3. Create App container by plugging PDB using NOCOPY.

— Connect to CDB$ROOT
create pluggable database APPROOT as application container using ‘/scratch/sysvldb/testpdb3.xml’ nocopy tempfile reuse;
alter pluggable database APPROOT open instances=all;
alter session set container=APPROOT;
alter user shuser1 identified by oracle; #Optional
 

 4.  Install application in app root and map users to application:

— Start installation
alter session set container=APPROOT;
alter pluggable database application sales_app begin install ‘1.0’;

— Map all users to application.
exec dbms_pdb_alter_sharing.set_user_explicit (‘SHUSER1’);
exec dbms_pdb_alter_sharing.set_user_explicit (‘SHUSER2’);
exec dbms_pdb_alter_sharing.set_user_explicit (‘SHUSER3’);
 

 5. Set sharing for each schema objects and verify objects wrt to user.

— Set object data sharing type – METADATA_LINKED, DATA_LINKED or EXTENDED DATA LINKED
exec dbms_pdb_alter_sharing.SET_METADATA_LINKED (schema_name =>’SHUSER1′,object_name => ‘EMPLOYEE’, namespace => 1);
exec dbms_pdb_alter_sharing.SET_DATA_LINKED (schema_name =>’SHUSER1′,object_name => ‘DEPARTMENT’, namespace => 1);
exec dbms_pdb_alter_sharing.SET_EXT_DATA_LINKED (schema_name =>’SHUSER2′,object_name => ‘SALES’, namespace => 1);
exec dbms_pdb_alter_sharing.SET_DATA_LINKED (schema_name =>’SHUSER3′,object_name => ‘CUSTOMERS’, namespace => 1);
exec dbms_pdb_alter_sharing.SET_DATA_LINKED (schema_name =>’SHUSER3′,object_name => ‘PRODUCT’, namespace => 1);

— Verify the sharing is set right
select object_name, object_type, sharing, application from dba_objects  where owner = ‘SHUSER1’;

OBJECT_NAME     OBJECT_TYPE             SHARING                 A

————–               ———————–           ——————            –

EMPLOYEE            TABLE                           METADATA LINK      Y
DEPARTMENT       TABLE                           DATA LINK                Y
 

— End Application Install
alter pluggable database application sales_app end install;
 

 6. Create App PDB by cloning PDB_META1.

— Connect to application container:
alter session set container=APPROOT;

— Create App PDB.
create pluggable database APPPDB1 from PDB_META1;
alter pluggable database APPPDB1 open instances=all;
alter session set container=APPDB1;

— Run pdb_to_apppdb.sql
@${ORACLE_HOME}/rdbms/admin/rdbms/admin/pdb_to_apppdb.sql

— Close and re-open App PDB.
alter pluggable database close instances=all;
alter pluggable database open instances=all;

— Sync Application
alter pluggable database application sales_app sync;

— Verify Application objects
select object_name, object_type, sharing, application from dba_objects  where owner = ‘SHUSER1’;
 

7. Upgrade application in App root and sync app in App PDB

— Connect to application container:
alter session set container=APPROOT;

— upgrade application: 
alter pluggable database application SALES_APP begin upgrade to ‘2.0’;
drop table SHUSER1.TEST_SHO_9;
alter pluggable database application end upgrade;

— Connect to App PDB and Sync Application.
alter session set container=APPPDB1;
alter pluggable database application sales_app sync;

— Verify object which was dropped 
select object_name, object_type, sharing, application from dba_objects where owner = ‘SHUSER1’;

Conclusion

This blog demonstrates the step-by-step operations necessary to complete the migration of a large pluggable database to an Application Container where the application data and metadata is migrated from a PDB as a meta data clone of the source database and plugged in to the target Application Container preserving and using the original datafiles. The operation illustrates the necessary execution of DBMS_PDB_ALTER_SHARING package subprograms and the creation of data and metadata sharing application pluggable databases. These operations are supported for non-CDB to Application Container migrations as well as PDB to Application Container migration.

In collaboration with Shoaib Farooqui, Senior Member Technical Staff, Shared SaaS Dev Functions