Toad World Blog
In my previous article we learnt how to move an application container across CDBs. As we already know, Multitenant Application Containers eliminate the overhead of replicating application definitions by enabling many PDBs to share application objects such as code, metadata, and data. Multitenant Application Containers also allow application administrators to efficiently manage many application PDBs as one in a single Application Container while securely isolating their individual customer-specific data. These valuable features of Multitenant Application Containers can be leveraged for existing applications running in more than one PDB by migrating such applications to an application container.
In this article, we will learn how to migrate multiple PDBs running the same application to an application container. Assume that currently there are many PDBs that are running the same application and hence all these PDBs have the same objects, users, roles, and profiles required by the application. These PDBs can be migrated to an application container by following these steps:
- Create an application root from one of the PDBs using one of the following methods:
- Clone an existing PDB as an application root.
- Relocate an existing PDB as an application root.
- Unplug and plug in an existing PDB as an application root.
The newly created application root contains all of the database users / objects used by the application.
- Install an application in the application root and associate the database objects, users, roles, and profiles with the application by running procedures in the
DBMS_PDBpackage. - Create application PDBs in the new application container using the existing PDBs.
Now, I will demonstrate the migration of multiple PDBs running the same application to an application container by means of a sample scenario.
Current scenario:
Here, we have an Oracle database 12.2.0.1 CDB called orclcdb, as shown below in Fig. 1. Within this CDB, besides CDB Seed PDB pdb$seed, we have four regular PDBs east_pdb, west_pdb, north_pdb and south_pdb representing databases for four regional offices of an organization. All these regular PDs supports the same sales application and hence contain the following application user/objects:
- Application user sales_app_user
- Application tables
- sales_app_user.customers
- sales_app_user.products
- sales_app_user.zip_codes.
Fig. 1
In order to avoid replication of the application objects, we will create an application container sales_app_root which stores various common application objects for the sales application (sales_app) as shown in Fig. 2. Subsequently, within the application container sales_app_root, we will create the application PDBs east_app_pdb, west_app_pdb, north_app_pdb and south_app_pdb from the existing regular PDBs east_pdb, west_pdb, north_pdb and south_pdb respectively so that they can share common application objects stored in the application root sales_app_root.
Fig. 2
Overview of the steps:
- Clone one of the PDBs; say, east_pdb, as application root sales_app_root.
- Verify that the new application root sales_app_root contains all of the database objects used by the application.
- With sales_app_root as the current container, start installation of the sales application sales_appby issuing an ALTER PLUGGABLE DATABASE ... BEGIN INSTALL statement.
- Run the procedure DBMS_PDB.SET_USER_EXPLICIT to associate the user sales_app_user with the application sales_app as an application common user.
- Run the DBMS_PDB.SET_DATA_LINKED procedure to associate table sales_app_user.products with the application sales_app as a data-linked application common table.
- Run the DBMS_PDB.SET_METADATA_LINKED procedure to associate table sales_app_user.customers with the application sales_app as a metadata-linked application common table.
- Run the DBMS_PDB.SET_EXT_DATA_LINKED procedure to associate table sales_app_user.zip_codes with the application sales_app as an extended data-linked application common table.
- End the application installation operation by issuing an ALTER PLUGGABLE DATABASE ... END INSTALL statement.
- Ensure that application sales_apphas been installed with various associated application objects and
- The COMMON property of the user sales_app_user is correct
- The SHARING and APPLICATION properties of the common application tables are correct
- In the application root sales_app_root, clone existing regular PDBs east_pdb, west_pdb, north_pdb and south_pdb as application PDBs east_app_pdb, west_app_pdb, north_app_pdb and south_app_pdbb, respectively. Violations will be reported during the opening of application PDBs.
- Switch to each of the application PDBs as a user with the required privileges and run the pdb_to_apppdb.sql script in the ORACLE_HOME/rdbms/admin directory. Optionally, drop source regular PDBs east_pdb, west_pdb, north_pdb and south_pdb.
- Ensure that in all the application PDBs
- The common property of the user sales_app_user is correct
- The SHARING and APPLICATION properties of the common application tables are correct
Demonstration:
- Check that at present there are four regular PDBs in CDB orclcdb.
CDB$ROOT>sho parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
db_name string orclcdb
CDB$ROOT>@get_app_containers
CON_ID NAME OPEN_MODE APP_ROOT APP_PDB APP_SEED
---------- -------------------- ---------- -------- -------- --------
1 CDB$ROOT READ WRITE NO NO NO
2 PDB$SEED READ ONLY NO NO NO
3 EAST_PDB READ WRITE NO NO NO
4 WEST_PDB READ WRITE NO NO NO
5 NORTH_PDB READ WRITE NO NO NO
6 SOUTH_PDB READ WRITE NO NO NO
6 rows selected.
- Clone one of the PDBs, say, east_pdb, as application root sales_app_root.
CDB$ROOT>ho mkdir -p /u02/app/oracle/oradata/orclcdb/sales_app_root
ALTER session SET db_create_file_dest='/u02/app/oracle/oradata/orclcdb/sales_app_root/';
CREATE PLUGGABLE DATABASE sales_app_root AS APPLICATION CONTAINER
FROM east_pdb ;
Pluggable database created.
CDB$ROOT>@get_app_containers
CON_ID NAME OPEN_MODE APP_ROOT APP_PDB APP_SEED
------- -------------- ---------- -------- -------- --------
1 CDB$ROOT READ WRITE NO NO NO
2 PDB$SEED READ ONLY NO NO NO
3 EAST_PDB READ WRITE NO NO NO
4 WEST_PDB READ WRITE NO NO NO
5 NORTH_PDB READ WRITE NO NO NO
6 SOUTH_PDB READ WRITE NO NO NO
7 SALES_APP_ROOT READ WRITE YES NO NO
- The new application root sales_app_root contains all of the database objects used by the application. When application root sales_app_rootis first opened,
- The application user sales_app_user (a local user in source regular PDB east_pdb) has been marked as COMMON in sales_app_root. Since the user sales_app_user is not associated with any application yet, DDL operation can be performed on it even from outside an application action although it has been marked as COMMON.
EAST_PDB>select username, common from dba_users
where username = 'SALES_APP_USER';
USERNAME COMMON
------------------ ----------
SALES_APP_USER NO
SALES_APP_ROOT>select username, common, oracle_maintained from dba_users
where common = 'YES' and oracle_maintained = 'N';
USERNAME COMMON ORACLE_MAINTAINED
--------------- ---------- --------------------
SALES_APP_USER YES N
SALES_APP_ROOT1>alter user sales_app_user identified by oracle;
User altered.
- The SHARING attribute of application tables remains NONE as in source regular PDB east_pdb. Also, since application tables are not yet associated with any application, a DDL operation can be performed on them even from outside an application action.
EAST_PDB>select object_name, object_type, sharing, namespace from dba_objects
where owner = 'SALES_APP_USER';
OBJECT_NAME OBJECT_TYPE SHARING NAMESPACE
-------------------- --------------------- ------------------ ---------
CUSTOMERS TABLE NONE 1
SALES_APP_ROOT>select object_name, object_type, sharing, namespace
from dba_objects where owner = 'SALES_APP_USER';
OBJECT_NAME OBJECT_TYPE SHARING NAMESPACE
-------------------- ------------ -------------------- ---------
CUSTOMERS TABLE NONE 1
PRODUCTS TABLE NONE 1
ZIP_CODES TABLE NONE 1
SALES_APP_ROOT1>alter table sales_app_user.customers
modify (cust_name varchar2(31));
Table altered.
- It can be seen that currently there is only an implicit application installed in the application container sales_app_root.
SALES_APP_ROOT>@get_app_status
APP_NAME APP_VERSION APP_ID APP_STATUS IMPLICIT
-------------------------------------- ------------ ------ ------------ ------
APP$6177CEC316D62D55E05364C909C0DB70 1.0 2 NORMAL Y
- With sales_app_root as the current container, start installation of the sales application sales_app by issuing an ALTER PLUGGABLE DATABASE ... BEGIN INSTALL statement.
SALES_APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION sales_app BEGIN INSTALL '1.0';
- Run the procedure DBMS_PDB.SET_USER_EXPLICIT to associate user sales_app_user with the application sales_app as an application common user.
SALES_APP_ROOT>exec dbms_pdb.set_user_explicit ('SALES_APP_USER');
- Run the DBMS_PDB.SET_DATA_LINKED procedure to associate the table sales_app_user.products with the application sales_app as a data-linked application common table.
SALES_APP_ROOT>exec dbms_pdb.SET_DATA_LINKED (schema_name =>'SALES_APP_USER',-
object_name => 'PRODUCTS',-
namespace => 1);
- Run the DBMS_PDB.SET_METADATA_LINKED procedure to associate table sales_app_user.customers with the application sales_app as a metadata-linked application common table.
SALES_APP_ROOT>exec dbms_pdb.SET_METADATA_LINKED (schema_name =>'SALES_APP_USER',-
object_name => 'CUSTOMERS',-
namespace => 1);
- Run the DBMS_PDB.SET_EXT_DATA_LINKED procedure to associate table sales_app_user.zip_codes with the application sales_app as an extended data-linked application common table.
SALES_APP_ROOT>exec dbms_pdb.SET_EXT_DATA_LINKED (schema_name =>'SALES_APP_USER',-
object_name => 'ZIP_CODES',-
namespace => 1);
- End the application installation operation by issuing an ALTER PLUGGABLE DATABASE ... END INSTALL statement.
SALES_APP_ROOT> ALTER PLUGGABLE DATABASE APPLICATION sales_app END INSTALL '1.0';
- With the application root as the current container, ensure that application sales_apphas been installed and
- The common property of the user sales_app_user is correct
- The SHARING and APPLICATION properties of the common application tables are set appropriately
SALES_APP_ROOT> @get_app_status
APP_NAME APP_VERSION APP_ID APP_STATUS IMPLICIT
-------------------------------------- ------------ ------ ------------ ------
APP$6177CEC316D62D55E05364C909C0DB70 1.0 2 NORMAL Y
SALES_APP 1.0 3 NORMAL N
SALES_APP_ROOT>select username, common, oracle_maintained from dba_users
where common = 'YES' and oracle_maintained = 'N';
USERNAME COMMON ORACLE_MAINTAINED
--------------- ---------- --------------------
SALES_APP_USER YES N
SALES_APP_ROOT>select object_name, object_type, sharing, application
from dba_objects
where owner = 'SALES_APP_USER';
OBJECT_NAME OBJECT_TYPE SHARING APPLICATION
-------------------- -------------- ------------------- ------------
CUSTOMERS TABLE METADATA LINK Y
PRODUCTS TABLE DATA LINK Y
ZIP_CODES TABLE EXTENDED DATA LINK Y
- Now that the user sales_app_user and the application table sales_app_user.customers have been associated with the application sales_app, it is not possible to perform a DDL on them from outside an application install / upgrade / patch operation.
SALES_APP_ROOT>alter user sales_app_user identified by oracle;
alter user sales_app_user identified by oracle
*
ERROR at line 1:
ORA-65274: operation not allowed from outside an application action
SALES_APP_ROOT1>alter table sales_app_user.customers modify (cust_name varchar2(32));
alter table sales_app_user.customers modify (cust_name varchar2(32))
*
ERROR at line 1:
ORA-65274: operation not allowed from outside an application action
- In the application root sales_app_root, clone the existing regular PDBs east_pdb, west_pdb, north_pdb and south_pdb as application PDBs east_app_pdb, west_app_pdb, north_app_pdb and south_app_pdbrespectively.
- Note that currently no application PDBs are associated with sales_app_root.
SALES_APP_ROOT>@get_app_containers
CON_ID NAME OPEN_MODE APP_ROOT APP_PDB APP_SEED
---------- -------------------- ----------- -------- -------- --------
7 S ALES_APP_ROOT READ WRITE YES NO NO
- Create a database link from application root sales_app_root to CDB$root
SALES_APP_ROOTR>CREATE PUBLIC DATABASE LINK cdbroot_link
CONNECT TO system IDENTIFIED BY oracle
USING 'orclcdb';
- Clone regular PDB west_pdb as application PDB west_app_pdb in the application root sales_app_root. Violations will be reported during opening of the application PDB.
SALES_APP_ROOT>ho mkdir -p /u02/app/oracle/oradata/orclcdb/sales_app_root/west_app_pdb
SALES_APP_ROOT>ALTER session SET
db_create_file_dest='/u02/app/oracle/oradata/orclcdb/sales_app_root/west_app_pdb';
SALES_APP_ROOT>CREATE PLUGGABLE DATABASE west_app_pdb FROM west_pdb@cdbroot_link ;
SALES_APP_ROOT>Alter PLUGGABLE DATABASE west_app_pdb open;
Warning: PDB altered with errors.
- Switch to the application PDB west_app_pdb as a user with the required privileges and run the pdb_to_apppdb.sql script in the ORACLE_HOME/rdbms/admin directory. Optionally, drop source regular PDB west_pdb.
WEST_APP_PDB>@$ORACLE_HOME/rdbms/admin/pdb_to_apppdb
WEST_APP_PDB>sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ----------- ----------
8 WEST_APP_PDB READ WRITE YES
-- Reopen in non-restricted mode
WEST_APP_PDB>alter pluggable database west_app_pdb close immediate;
alter pluggable database west_app_pdb open;
sho pdbs
P
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
8 WEST_APP_PDB READ WRITE NO
ORCLCDB$ROOT>alter pluggable database west_pdb close immediate;
drop pluggable database west_pdb including datafiles;
- Similarly clone rest of the existing regular PDBs east_pdb, north_pdb and south_pdb as application PD's east_app_pdb, north_app_pdb and south_app_pdb respectively. Check that all four regular PDBs have been converted to application PDBs and are in sync with application sales_app version 1.0.
ORCLCDB$ROOT>@get_app_containers
CON_ID NAME OPEN_MODE APP_ROOT APP_PDB APP_SEED
---------- ---------------- ------------- -------- -------- --------
1 CDB$ROOT READ WRITE NO NO NO
2 PDB$SEED READ ONLY NO NO NO
3 SOUTH_APP_PDB READ WRITE NO YES NO
4 NORTH_APP_PDB READ WRITE NO YES NO
7 SALES_APP_ROOT READ WRITE YES NO NO
8 WEST_APP_PDB READ WRITE NO YES NO
9 EAST_APP_PDB READ WRITE NO YES NO
SALES_APP_ROOT>@get_app_pdb_status
NAME CON_UID APP_NAME APP_VERSIO APP_STATUS
-------------------- ---------- -------------------- ---------- ------------
WEST_APP_PDB 2620482286 SALES_APP 1.0 NORMAL
EAST_APP_PDB 3418848181 SALES_APP 1.0 NORMAL
NORTH_APP_PDB 1856849412 SALES_APP 1.0 NORMAL
SOUTH_APP_PDB 65437774 SALES_APP 1.0 NORMAL
- Ensure that in all the application PDBs
- The common property of the user sales_app_user is correct
- The SHARING and APPLICATION properties of the common application tables are correct
SALES_APP_ROOT>select con$name, username, common from containers (dba_users)
where username = 'SALES_APP_USER';
CON$NAME USERNAME COMMON
-------------------- -------------------- ----------
SALES_APP_ROOT SALES_APP_USER YES
SOUTH_APP_PDB SALES_APP_USER YES
NORTH_APP_PDB SALES_APP_USER YES
WEST_APP_PDB SALES_APP_USER YES
EAST_APP_PDB SALES_APP_USER YES
.
SALES_APP_ROOT>select con$name, object_name, owner, application, sharing
from containers(dba_objects)
where owner = 'SALES_APP_USER';
CON$NAME OBJECT_NAME OWNER APPLICATION SHARING
-------------- ------------- --------------- ------------ --------------------
SALES_APP_ROOT CUSTOMERS SALES_APP_USER Y METADATA LINK
SALES_APP_ROOT PRODUCTS SALES_APP_USER Y DATA LINK
SALES_APP_ROOT ZIP_CODES SALES_APP_USER Y EXTENDED DATA LINK
SOUTH_APP_PDB CUSTOMERS SALES_APP_USER Y METADATA LINK
SOUTH_APP_PDB PRODUCTS SALES_APP_USER Y DATA LINK
SOUTH_APP_PDB ZIP_CODES SALES_APP_USER Y EXTENDED DATA LINK
NORTH_APP_PDB CUSTOMERS SALES_APP_USER Y METADATA LINK
NORTH_APP_PDB PRODUCTS SALES_APP_USER Y DATA LINK
NORTH_APP_PDB ZIP_CODES SALES_APP_USER Y EXTENDED DATA LINK
WEST_APP_PDB CUSTOMERS SALES_APP_USER Y METADATA LINK
WEST_APP_PDB PRODUCTS SALES_APP_USER Y DATA LINK
WEST_APP_PDB ZIP_CODES SALES_APP_USER Y EXTENDED DATA LINK
EAST_APP_PDB CUSTOMERS SALES_APP_USER Y METADATA LINK
EAST_APP_PDB PRODUCTS SALES_APP_USER Y DATA LINK
EAST_APP_PDB ZIP_CODES SALES_APP_USER Y EXTENDED DATA LINK
15 rows selected.
Thus, we have successfully migrated four regular PDBs (east_pdb, west_pdb, north_pdb and south_pdb) running the same sales application to an application container sales_app_root as application PDBs (east_app_pdb, west_app_pdb, north_app _pdb and south_app _pdb), thereby enabling them to share application objects and be administered easily as one.
References:
Scripts used in this article:
get_app_containers.sql
-- Find out the containers in an application root when executed from application root
SELECT con_id, name, open_mode, application_root app_root,
application_pdb app_pdb, application_seed app_seed
from v$containers
order by con_id;
get_app_status.sql
-- Find out the status of various applications in an application container when executed from an application root
select app_name, app_version, app_id, app_status, app_implicit implicit from dba_applications;
get_app_pdb_status.sql
-- Find out the available versions of application sales_app with which various application / seed PDBs are in sync currently
SELECT c.name, aps.con_uid, aps.app_name, aps.app_version, aps.app_status
FROM dba_app_pdb_status aps
JOIN v$containers c
ON c.con_uid = aps.con_uid
WHERE aps.app_name = 'SALES_APP';
---------------------------------------------------------------------------------------------------------------------------------------
Tags: Oracle
Written by Anju Garg
Anju Garg is an Oracle Ace with over 14 years of experience in IT Industry in various roles. Since 2010, she has been involved in teaching and has trained more than a hundred DBAs from across the world in various core DBA technologies like RAC, Data guard, Performance Tuning, SQL statement tuning, Database Administration etc.
She is a regular speaker at Sangam and OTNYathra. She writes articles about Oracle and is one of the reviewers of the following book published by Pearson Oracle Problem-Solving and Troubleshooting Handbook
She is certified for :
- Oracle 9i Database Administration OCP
- Oracle 11g Database Administration OCP
- Oracle 11g Performance Tuning OCE
- Oracle 11g R2 RAC OCE
- Oracle 11g SQL Tuning OCE
- Oracle 12c Database Administration OCP
- Oracle Real Application Clusters 12c Certified Implementation Specialist
She is passionate about learning and has keen interest in RAC and Performance Tuning. She shares her knowledge via her technical blog at http://oracleinaction.com/