Mar 12, 2018 3:16:02 PM by Anju Garg
In my previous article, I discussed the use of proxy PDBs with application containers to load balance an application across CDBs by relocating application PDB‘s). In this article, I will discuss the moving of an application container to another CDB by unplugging and plugging. It is useful when you need to move the production application container to another CDB.
In order to move the application container to a different CDB, the application container can be unplugged from the source CDB and plugged into the destination CDB. Unplugging an application container involves the unplugging of files for each constituent container (the application root, the application seed, and the application PDBs). An unplug operation can be performed using
In this case, the transfer of the files between servers is simplified. Only the PDB archive file needs to be copied or moved to the destination server since both the XML metadata file and the PDB datafiles required by the plugging operation are already a part of the archive file.
In this article, I will demonstrate moving an application container to another CDB by unplugging and plugging using archive files.
The current configuration is shown below in in Fig. 1. We have an Oracle database 12.2.0.1 CDB called orclcdb as shown. Within this CDB, besides the CDB Seed PDB pdb$seed, we have an application container with the application root sales_app_root for the sales application (sales_app). The application container sales_app_root houses one application PDB app_pdb1 which supports the sales_app application. We also have another 12.2.0.1 CDB called rmtcdb having only one PDB, namely, CDB Seed PDB pdb$seed.
Fig. 1
I will demonstrate moving application container sales_app_root from CDB orclcdb to CDB rmtcdb so that the configuration becomes as shown below in Fig. 2.
Fig. 2
SQL> conn sys/oracle@orclcdb as sysdba
set sqlprompt ORCLCDB$ROOT>
sho con_name
CON_NAME
------------------------------
CDB$ROOT
CDB$ROOT>SELECT con_id, name, open_mode, application_root app_root,
application_pdb app_pdb, application_seed app_seed
from v$containers
where application_root = 'YES' and application_PDb = 'NO' ;
CON_ID NAME OPEN_MODE APP_ROOT APP_PDB APP_SEED
---------- --------------- ---------- -------- -------- --------
3 SALES_APP_ROOT READ WRITE YES NO NO
CDB$ROOT> conn sys/oracle@host01:1522/sales_app_root as sysdba
set sqlprompt SALES_APP_ROOT>
SALES_APP_ROOT>sho con_name
SALES_APP_ROOT>@get_app_containers
CON_ID NAME OPEN_MODE APP_ROOT APP_PDB APP_SEED
--------- -------------------- ---------- -------- -------- --------
3 SALES_APP_ROOT READ WRITE YES NO NO
4 SALES_APP_ROOT$SEED READ WRITE NO YES YES
5 APP_PDB1 READ WRITE NO YES NO
SALES_APP_ROOT>@get_app_status
APP_NAME APP_VERSION APP_ID APP_STATUS IMPLICIT
-------------------------------------- ------------ ----- ------------ ------
APP$6020BA8D64AC386FE05364C909C02795 1.0 2 NORMAL Y
SALES_APP 1.0 3 NORMAL N
SALES_APP_ROOT>@get_sales_app_pdb_status
NAME APP_NAME APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
SALES_APP_ROOT$SEED SALES_APP 1.0 NORMAL
APP_PDB1 SALES_APP 1.0 NORMAL
SALES_APP_ROOT>@get_sales_app_objects
APP_NAME OWNER OBJECT_NAME OBJECT_TYPE SHARING APPLICATION
---------- ---------------- --------------- --------------- ---------------- ------------
SALES_APP SALES_APP_USER CUSTOMERS TABLE METADATA LINK Y
SALES_APP_ROOT>desc sales_app_user.customers
Name Null? Type
----------------------- -------- ----------------
CUST_ID NOT NULL NUMBER
CUST_NAME VARCHAR2(30)
CUST_ADD VARCHAR2(30)
CUST_ZIP NUMBER
SQL> conn sys/oracle@rmtcdb as sysdba
set sqlprompt RMTCDB$ROOT>
RMTCDB$ROOT>sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- --------------------- ---------- ----------
2 PDB$SEED READ ONLY NO
Now we will unplug the application container sales_app_root from the CDB orclcdb using archive files and plug it into CDB rmtcdb so as to create a new application container sales_app_root in CDB rmtcdb.
ORCLCDB$ROOT>ALTER PLUGGABLE DATABASE sales_app_root CLOSE;
SHOW pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ----------------------- ---------- ----------
2 PDB$SEE READ ONLY NO
3 SALES_APP_ROOT MOUNTED
4 SALES_APP_ROOT$SEED MOUNTED
5 APP_PDB1 MOUNTED
ORCLCDB$ROOT>ALTER PLUGGABLE DATABASE app_pdb1
UNPLUG INTO '/tmp/app_pdb1.pdb';
ALTER PLUGGABLE DATABASE sales_app_root$SEED
UNPLUG INTO '/tmp/sales_app_root_seed.pdb';
ALTER PLUGGABLE DATABASE sales_app_root
UNPLUG INTO '/tmp/sales_app_root.pdb';
As a result of the unplug operation, one compressed archive file having a .pdb extension has been created for each PDB. Since each archive file contains the manifest as well as all the data files of the respective PDB, even if we drop the unplugged PDBs we will still be able to plug them into the destination CDB.
ORCLCDB$ROOT>DROP PLUGGABLE DATABASE app_pdb1 INCLUDING DATAFILES;
DROP PLUGGABLE DATABASE sales_app_root$SEED INCLUDING DATAFILES;
DROP PLUGGABLE DATABASE sales_app_root INCLUDING DATAFILES;
sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
RMTCDB$ROOT>ho mkdir -p /u02/app/oracle/oradata/rmtcdb/sales_app_root/app_pdb1
ho mkdir -p /u02/app/oracle/oradata/rmtcdb/sales_app_root/sales_app_root_seed
-- Plug in application root sales_app_root
RMTCDB$ROOT>ALTER SESSION SET
db_create_file_dest='/u02/app/oracle/oradata/rmtcdb/sales_app_root/';
CREATE PLUGGABLE DATABASE sales_app_root AS APPLICATION CONTAINER
AS CLONE USING '/tmp/sales_app_root.pdb';
Pluggable database created.
RMTCDB$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 SALES_APP_ROOT MOUNTED YES NO NO
RMTCDB$ROOT>ALTER PLUGGABLE DATABASE sales_app_root OPEN;
-- Plug in application seed sales_app_root$seed
RMTCDB$ROOT>CONNECT sys/oracle@host01:1522/sales_app_root AS SYSDBA
sho parameter db_name
NAME TYPE VALUE
------------------------ ---------- -----------------------------
db_name string rmtcdb
RMTCDB$ROOT>sho con_name
CON_NAME
------------------------------
SALES_APP_ROOT
RMTCDB$ROOT>set sqlprompt SALES_APP_ROOT>
SALES_APP_ROOT>ALTER SESSION SET
db_create_file_dest='/u02/app/oracle/oradata/rmtcdb/sales_app_root/sales_app_root_seed';
SALES_APP_ROOT>CREATE PLUGGABLE DATABASE AS SEED
AS CLONE USING '/tmp/sales_app_root_seed.pdb';
sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
--------- ------------------------- ------------ ----------
3 SALES_APP_ROOT READ WRITE NO
5 SALES_APP_ROOT$SEED MOUNTED
ALTER PLUGGABLE DATABASE sales_app_root$seed OPEN;
-- Plug in application PDB app_pdb1
SALES_APP_ROOT>ALTER SESSION SET
db_create_file_dest='/u02/app/oracle/oradata/rmtcdb/sales_app_root/app_pdb1';
CREATE PLUGGABLE DATABASE app_pdb1
AS CLONE USING '/tmp/app_pdb1.pdb';
sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- --------------------- ----------- ----------
3 SALES_APP_ROOT READ WRITE NO
4 APP_PDB1 MOUNTED
5 SALES_APP_ROOT$SEED READ WRITE NO
SALES_APP_ROOT>ALTER PLUGGABLE DATABASE app_pdb1 OPEN;
SALES_APP_ROOT>@get_app_containers
CON_ID NAME OPEN_MODE APP_ROOT APP_PDB APP_SEED
--------- -------------------- ----------- -------- -------- --------
3 SALES_APP_ROOT READ WRITE YES NO NO
4 APP_PDB1 READ WRITE NO YES NO
5 SALES_APP_ROOT$SEED READ WRITE NO YES YES
APP_PDB1>desc sales_app_user.customers
Name Null? Type
------------------------------ ---------- ----------------------------
CUST_ID NOT NULL NUMBER
CUST_NAME VARCHAR2(30)
CUST_ADD VARCHAR2(30)
CUST_ZIP NUMBER
Thus, we have been able to move the application container sales_app_root from CDB orclcdb to CDB rmtcdb successfully.
References:
Scripts use in the 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_sales_app_pdb_status
-- Find out various versions of application SALES_APP and synchronization status of various application PDBs with it. Execute from application root.
SELECT c.name,
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';
get_sales_app_objects
-- Find out objects belonging to sales_app application.
select app.app_name, obj.owner, obj.object_name, obj.object_type,
obj.sharing, obj.application
from dba_objects obj, dba_applications app
where obj.owner in
(select username from dba_users
where oracle_maintained = 'N')
and obj.application = 'Y'
and obj.created_appid = app.app_id;
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.