Apr 9, 2018 11:13:53 AM by Anju Garg
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:
The newly created application root contains all of the database users / objects used by the application.
DBMS_PDB
package.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:
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
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.
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
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.
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.
SALES_APP_ROOT>@get_app_status
APP_NAME APP_VERSION APP_ID APP_STATUS IMPLICIT
-------------------------------------- ------------ ------ ------------ ------
APP$6177CEC316D62D55E05364C909C0DB70 1.0 2 NORMAL Y
SALES_APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION sales_app BEGIN INSTALL '1.0';
SALES_APP_ROOT>exec dbms_pdb.set_user_explicit ('SALES_APP_USER');
SALES_APP_ROOT>exec dbms_pdb.SET_DATA_LINKED (schema_name =>'SALES_APP_USER',-
object_name => 'PRODUCTS',-
namespace => 1);
SALES_APP_ROOT>exec dbms_pdb.SET_METADATA_LINKED (schema_name =>'SALES_APP_USER',-
object_name => 'CUSTOMERS',-
namespace => 1);
SALES_APP_ROOT>exec dbms_pdb.SET_EXT_DATA_LINKED (schema_name =>'SALES_APP_USER',-
object_name => 'ZIP_CODES',-
namespace => 1);
SALES_APP_ROOT> ALTER PLUGGABLE DATABASE APPLICATION sales_app END INSTALL '1.0';
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
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
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
SALES_APP_ROOTR>CREATE PUBLIC DATABASE LINK cdbroot_link
CONNECT TO system IDENTIFIED BY oracle
USING 'orclcdb';
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.
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;
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
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 :
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/
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.