Overview
In my most recent article, I discussed the cross-container DML statement, which is a convenient way for the Application Root administrator to perform DML operations on a table in any application PDB within the application container, without connecting to it or creating corresponding database link.
In this article I will discuss conversion of a regular PDB to an application PDB so that applications that are already installed in it can also take advantage of application containers.
Current Scenario
Here, we have an Oracle database 12.2.0.1 CDB called orclcdb as shown. Within this CDB, besides CDB Seed PDB pdb$seed, we have a regular PDB called orclpdb which supports its own application. Also, we have created an application container sales_app_root for the sales application (sales_app) of an organization. The application container sales_app_root houses four application PDBs north_app_pdb, south_app_pdb, east_app_pdb and west_app_pdb which support the sales_app application for various regional offices of the organization. While sharing the structure of the metadata-linked customers table stored in the application root sales_app_root, each application PDB can store region-specific customer data in the table.
It is desired that the regular PDB orclpdb, besides supporting its own application, should also be able to access common application objects of the application sales_app. In order to fulfill this requirement, we will clone the regular PDB orclpdb to application PDB centre_app_pdb within the application container sales_app_root as shown below so that common application objects of thesales_app application are accessible to it.
The process involves following steps:
- Clone the regular PDB orclpdb into the application root sales_app_root. as application PDB centre_app_pdb. Violations will be reported during the opening of application PDB as application sales_app in the application root does not exist in application PDB yet.
- Connect to the new application PDB centre_app_pdb and execute the $ORACLE_HOME/rdbms/admin/pdb_to_apppdb.sql script so that common application objects are accessible in the application PDB. For example, the common application user sales_app_user that exists in the application root is marked as common in the plugged application PDB centre_app_pdb. The script automatically synchronizes the application PDB with the application root.
Demonstration
Let us first connect to CDB orclcdb and verify that there is one regular PDB, orclpdb, in this CDB.
SQL> conn sys/oracle@orclcdb as sysdba
set sqlprompt CDB$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 = 'NO' and application_PDb = 'NO'
and application_seed = 'NO';
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
8 ORCLPDB READ WRITE NO NO NO
Note that there is one application container sales_app_root in this CDB.
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
---------- -------------------- ---------- ---------- ---------- ----------
15 SALES_APP_ROOT READ WRITE YES NO NO
Connect to application root sales_app_root and note that there are four application PDBs (north_app_pdb, east_app_pdb, west_app_pdb, south_app_pdb) ) associated with it.
SQL> conn sys/oracle@host01:1522/sales_app_root as sysdba
set sqlprompt SALES_APP_ROOT>
SALES_APP_ROOT>sho con_name
CON_NAME
------------------------------
SALES_APP_ROOT
SALES_APP_ROOT>@get_app_containers
CON_ID NAME OPEN_MODE APP_ROOT APP_PDB APP_SEED
---------- -------------------- ---------- -------- -------- --------
4 SOUTH_APP_PDB READ WRITE NO YES NO
5 EAST_APP_PDB READ WRITE NO YES NO
6 WEST_APP_PDB READ WRITE NO YES NO
7 NORTH_APP_PDB READ WRITE NO YES NO
15 SALES_APP_ROOT READ WRITE YES NO NO
5 rows selected.
Besides an implicit application, another application sales_appis currently installed in this container. All the application PDBs are synced with the application sales_appversion 1.0.
SALES_APP_ROOT>@get_app_status
APP_NAME APP_VERSION APP_ID APP_STATUS IMPLICIT
------------------------------------ ------------ ------ ------------ --------
APP$5DED1EE7F9C418C7E05364C909C0F9BD 1.0 2 NORMAL Y
SALES_APP 1.0 21 NORMAL N
SALES_APP_ROOT>@get_sales_app_pdb_status
NAME APP_NAME APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
SOUTH_APP_PDB SALES_APP 1.0 NORMAL
EAST_APP_PDB SALES_APP 1.0 NORMAL
WEST_APP_PDB SALES_APP 1.0 NORMAL
NORTH_APP_PDB SALES_APP 1.0 NORMAL
Let us check the common objects currently in the application sales_app. There is a metadata-linked common application table sales_app_user.customerswith structure as shown.
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
Let us create a local user l_userand a local table l_user.ltabin the regular pdb orclpdb to represent an application supported by it.
ORCLPDB>create user l_user identified by oracle;
User created.
ORCLPDB>grant connect, unlimited tablespace, create table to l_user;
Grant succeeded.
ORCLPDB>create table l_user.l_tab (id number);
insert into l_user.l_tab values (1);
commit;
select * from l_user.l_tab;
ID
----------
1
In order to clone the regular pdb orclpdbas an application PDB centre_app_pdbin the application container sales_app_root, let us open orclpdbin read only mode.
ORCLPDB>ALTER PLUGGABLE DATABASE orclpdb CLOSE immediate;
ALTER PLUGGABLE DATABASE orclpdb OPEN READ ONLY;
Pluggable database altered.
Create a folder to house datafiles for the new application PDB centre_app_pdband clone the regular PDB orclpdbas application PDB centre_app_pdb in the application containersales_app_root.
SALES_APP_ROOT>ho mkdir /u01/app/oracle/oradata/orclcdb/sales_app_root/centre_app_pdb
SALES_APP_ROOT>ALTER SESSION SET db_create_file_dest =
'/u01/app/oracle/oradata/orclcdb/sales_app_root/centre_app_pdb';
CREATE PLUGGABLE DATABASE centre_app_pdb FROM orclpdb;
Pluggable database created.
SALES_APP_ROOT>sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 CENTRE_APP_PDB MOUNTED
4 SOUTH_APP_PDB READ WRITE NO
5 EAST_APP_PDB READ WRITE NO
6 WEST_APP_PDB READ WRITE NO
7 NORTH_APP_PDB READ WRITE NO
15 SALES_APP_ROOT READ WRITE NO
When we try to open the newly created application PDB centre_app_pdb, it opens with a warning.
SALES_APP_ROOT>alter pluggable database centre_app_pdb open;
Warning: PDB altered with errors.
SALES_APP_ROOT>@get_app_containers
CON_ID NAME OPEN_MODE APP_ROOT APP_PDB APP_SEED
---------- -------------------- ---------- -------- -------- --------
3 CENTRE_APP_PDB READ WRITE NO YES NO
4 SOUTH_APP_PDB READ WRITE NO YES NO
5 EAST_APP_PDB READ WRITE NO YES NO
6 WEST_APP_PDB READ WRITE NO YES NO
7 NORTH_APP_PDB READ WRITE NO YES NO
15 SALES_APP_ROOT READ WRITE YES NO NO
6 rows selected.
The content of the view PDB_PLUG_IN_VIOLATIONSview explains that the script pdb_to_apppdb.sqlscript must be executed on the converted regular PDB centre_app_pdbfor it to become a full application PDB so that the application sales_appis accessible in it.
SALES_APP_ROOT>SELECT cause, type, message, status, action
FROM pdb_plug_in_violations
WHERE name='CENTRE_APP_PDB';
CAUSE TYPE MESSAGE STATUS ACTION
--------------- --------- ------------------------------ --------- ------------------------------
Application WARNING Application SALES_APP in Appli PENDING Fix the application in the PDB
cation Root does not exist in or the application root
Application PDB.
Non-Application ERROR Non-Application PDB plugged in PENDING Run pdb_to_apppdb.sql.
PDB to Applica as an Application PDB, requir
tion PDB es pdb_to_apppdb.sql be run.
Note that while the local user(l_user) / table (l_tab) created in the parent regular PDB orclpdbare accessible in the newly created application PDB centre_app_pdb, the common application table sales_app_user.customersfor the application sales_appis not accessible yet because the application PDB centre_app_pdbhas not been synchronized with the application root sales_app_root.
CENTRE_APP_PDB>select * from l_user.l_tab;
ID
----------
1
CENTRE_APP_PDB>select * from sales_app_user.customers;
select * from sales_app_user.customers
*
ERROR at line 1:
ORA-00942: table or view does not exist
SALES_APP_ROOT>@get_sales_app_pdb_status
NAME APP_NAME APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
SOUTH_APP_PDB SALES_APP 1.0 NORMAL
EAST_APP_PDB SALES_APP 1.0 NORMAL
WEST_APP_PDB SALES_APP 1.0 NORMAL
NORTH_APP_PDB SALES_APP 1.0 NORMAL
Let us execute the conversion script pdb_to_apppdb.sqlon the newly created application PDB centre_app_pdb.
As I connect remotely to centre_app_pdb and execute the script, the script fails on a step and returns me to the OS prompt.
SALES_APP_ROOT>CONNECT sys/oracle@host01:1522/centre_app_pdb AS SYSDBA
set sqlprompt CENTRE_APP_PDB>
CENTRE_APP_PDB>@$ORACLE_HOME/rdbms/admin/pdb_to_apppdb
CENTRE_APP_PDB>create or replace view sys.cdb$common_root_objects&pdbid sharing=object as
2 select u.name owner, o.name object_name, o.type# object_type, o.namespace nsp,
3 o.subname object_subname, o.signature object_sig,
4 decode(bitand(o.flags, &sharing_bits),
5 &edl+&mdl, 'EDL', &dl, 'DL', 'MDL') sharing
6 from sys.obj$ o, sys.user$ u
7 where o.owner#=u.user# and bitand(o.flags, &sharing_bits) <> 0
8 and bitand(o.flags,&fedobjflag)=&fedobjflag;
old 1: create or replace view sys.cdb$common_root_objects&pdbid sharing=object as
new 1: create or replace view sys.cdb$common_root_objects4 sharing=object as
old 4: decode(bitand(o.flags, &sharing_bits),
new 4: decode(bitand(o.flags, (65536+131072+4294967296)),
old 5: &edl+&mdl, 'EDL', &dl, 'DL', 'MDL') sharing
new 5: 4294967296+65536, 'EDL', 131072, 'DL', 'MDL') sharing
old 7: where o.owner#=u.user# and bitand(o.flags, &sharing_bits) <> 0
new 7: where o.owner#=u.user# and bitand(o.flags, (65536+131072+4294967296)) <> 0
old 8: and bitand(o.flags,&fedobjflag)=&fedobjflag
new 8: and bitand(o.flags,134217728)=134217728
create or replace view sys.cdb$common_root_objects4 sharing=object as
*
ERROR at line 1:
ORA-65021: illegal use of SHARING clause
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@host01 bin]$
After reconnecting to the application root sales_app_root, I note that centre_app_pdbis open in migrate mode.
SALES_APP_ROOT>sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 CENTRE_APP_PDB MIGRATE YES
4 SOUTH_APP_PDB READ WRITE NO
5 EAST_APP_PDB READ WRITE NO
6 WEST_APP_PDB READ WRITE NO
7 NORTH_APP_PDB READ WRITE NO
15 SALES_APP_ROOT READ WRITE NO
After various repeated trials, I realized that there is a bug. The script pdb_to_apppdb.sqlfails when it is executed after connecting to the application PDB centre_app_pdbremotely. Connection using “Alter session set container ..“ results in successful execution.
SALES_APP_ROOT>alter session set container= centre_app_pdb;
set sqlprompt CENTRE_APP_PDB>
CENTRE_APP_PDB>sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 CENTRE_APP_PDB MIGRATE YES
CENTRE_APP_PDB>alter pluggable database centre_app_pdb close immediate;
alter pluggable database centre_app_pdb open;
Warning: PDB altered with errors.
-- This time script completes successfully
CENTRE_APP_PDB>@$ORACLE_HOME/rdbms/admin/pdb_to_apppdb
.
.
.
CENTRE_APP_PDB>
CENTRE_APP_PDB>-- leave the PDB in the same state it was when we started
CENTRE_APP_PDB>BEGIN
2 execute immediate '&open_sql &restricted_state';
3 EXCEPTION
4 WHEN OTHERS THEN
5 BEGIN
6 IF (sqlcode <> -900) THEN
7 RAISE;
8 END IF;
9 END;
10 END;
11 /
PL/SQL procedure successfully completed.
CENTRE_APP_PDB>
CENTRE_APP_PDB>WHENEVER SQLERROR CONTINUE;
CENTRE_APP_PDB>sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 CENTRE_APP_PDB READ WRITE YES
Let us reopen the newly created application PDB centre_app_pdbto bring it out of RESTRICTED mode.
CENTRE_APP_PDB>alter pluggable database centre_app_pdb close immediate;
alter pluggable database centre_app_pdb open;
Pluggable database altered.
CENTRE_APP_PDB>sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 CENTRE_APP_PDB READ WRITE NO
The script automatically synchronizes the application PDB with the application root.
SALES_APP_ROOT>@get_sales_app_pdb_status
NAME APP_NAME APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
SOUTH_APP_PDB SALES_APP 1.0 NORMAL
EAST_APP_PDB SALES_APP 1.0 NORMAL
WEST_APP_PDB SALES_APP 1.0 NORMAL
NORTH_APP_PDB SALES_APP 1.0 NORMAL
CENTRE_APP_PDB SALES_APP 1.0 NORMAL
5 rows selected.
Note that now newly created application PDB centre_app_pdbcan recognize:
- Common application entities of the application sales_app; i.e., metadata-linked application table customers owned by the common application user sales_app_user
- Application objects created in the parent regular PDB orclpdb
CENTRE_APP_PDB>select username, common from dba_users where username = 'SALES_APP_USER';
USERNAME COMMON
--------------- ----------
SALES_APP_USER YES
CENTRE_APP_PDB>select owner, object_name, object_type, sharing from dba_objects where owner = 'SALES_APP_USER';
OWNER OBJECT_NAME OBJECT_TYPE SHARING
--------------- -------------------- ----------------------- -----------------
SALES_APP_USER CUSTOMERS TABLE METADATA LINK
CENTRE_APP_PDB>select * from sales_app_user.customers;
no rows selected
CENTRE_APP_PDB>select * from l_user.l_tab;
ID
----------
1
Hence, we have successfully converted a regular PDB orclpdb to an application PDB centre_app_pdbin the application container sales_app_root so that besides supporting its existing application(s), it can take advantage of the common application objects of application sales_app in the application container as well.
Summary
- A regular PDB can be converted to an application PDB so that applications already installed in it can take advantage of application containers.
- The process involves following steps:
- Clone / Plug the regular PDB into application root as application PDB. Violations will be reported during the opening of the application PDB as the application in the application root does not exist in the application PDB yet.
- Connect to the new application PDB and execute the $ORACLE_HOME/rdbms/admin/pdb_to_apppdb.sql script so that the application PDB is
– converted to a full application PDB
– synchronized with the application root
- An application PDB so created can access
- Existing application objects
- Common application objects in the application container
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;
Start the discussion at forums.toadworld.com