Recently, Tim Hall posted an excellent article about Application containers, a new feature in Oracle Database 12c Release 2 (12.2.0.1). With application containers, it is now possible to create a single master application definition in the application root of the application container. The common objects defined in the master application definition are visible to all application PDBs within the application container when the application PDBs have been synchronized with the application root.

Tim has demonstrated the concept of applilcation containers using Oracle Managed Files (OMF) . I tried out the same example without OMF and discovered that without OMF it works only if the application doesn’t use any new tablespaces/datafiles. If the application creates new datafiles, application PDB’s always fails to sync with the  application.

Here is my example:

– Create a new application root container appcon1 (non-OMF – Using FILE_NAME_CONVERT)

CDB$ROOT>conn / as sysdba

         CREATE PLUGGABLE DATABASE appcon1 AS APPLICATION CONTAINER
         ADMIN USER app_admin IDENTIFIED BY Password1
         FILE_NAME_CONVERT = ('/pdbseed/', '/appcon1/');

– Connect to the application root container and create an application PDB apppdb1 from CDB seed

CDB$ROOT>ALTER PLUGGABLE DATABASE appcon1 OPEN;
         ALTER SESSION SET container = appcon1;

APPCON1>CREATE PLUGGABLE DATABASE apppdb1
        ADMIN USER pdb_admin IDENTIFIED BY Password1
        FILE_NAME_CONVERT = ('/pdbseed/', '/appcon1/apppdb1/');

– Open the application PDB apppdb1

APPCON1>ALTER PLUGGABLE DATABASE apppdb1 open;
        SHOW PDBS

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 APPCON1 READ WRITE NO
5 APPPDB1 READ WRITE NO

-- Connected to the application root container, create a simple application ref_app

APPCON1>ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN INSTALL '1.0';

– Create application components

. Create a tablespace ref_app_ts

APPCON1>CREATE TABLESPACE ref_app_ts
        DATAFILE '/u01/app/oracle/oradata/orclcdb/appcon1/app_tbs1.dbf'
        SIZE 1M AUTOEXTEND ON NEXT 1M;

.  Create a common application user ref_app_user with default tablespace ref_app_ts created above

APPCON1>CREATE USER ref_app_user IDENTIFIED BY ref_app_user
        default tablespace ref_app_ts
        quota unlimited on ref_app_ts
        CONTAINER=ALL;

– Grant appropriate privileges to common appication user ref_app_user

APPCON1>GRANT CREATE SESSION, CREATE TABLE
-- , unlimited tablespace
TO ref_app_user;

– Create and populate a table reference_data owned by ref_app_user whose data will be shared across all the application PDB’s synced with the application ref_app

APPCON1>CREATE TABLE ref_app_user.reference_data SHARING=DATA (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT t1_pk PRIMARY KEY (id)
);

APPCON1>INSERT INTO ref_app_user.reference_data
SELECT level, 'Description of ' || level
FROM dual
CONNECT by level <= 5;
COMMIT;

– After all the application components have been defined, end the installation process.

APPCON1>ALTER PLUGGABLE DATABASE APPLICATION ref_app END INSTALL '1.0';

-- Verify that application ref_app version 1.0 has been created in application root appcon1 as listed in the DBA_APPLICATIONS view.

APPCON1>COLUMN app_name FORMAT A20
COLUMN app_version FORMAT A10

SELECT app_name,
app_version,
app_status
FROM dba_applications
WHERE app_name = 'REF_APP';

APP_NAME APP_VERSIO APP_STATUS
-------------------- ---------- ---------------
REF_APP 1.0 NORMAL

– Connect to application PDB apppdb1 and verify it cannot access common application table ref_app_user.reference_data as it has not been synced with the application ref_app yet

APPCON1>CONN / AS SYSDBA

ALTER SESSION SET container = apppdb1;
APPPDB1>DESC ref_app_user.reference_data;

ERROR:
ORA-04043: object ref_app_user.reference_data does not exist

– When an attempt is made to sync the application PDB apppdb1 with the application ref_app, ORA-01537 is encountered.

APPPDB1>ALTER PLUGGABLE DATABASE APPLICATION ref_app SYNC;

ALTER PLUGGABLE DATABASE APPLICATION ref_app SYNC
*
ERROR at line 1:
ORA-01537: cannot add file
'/u01/app/oracle/oradata/orclcdb/appcon1/app_tbs1.dbf' - file already part of database

However, above command succeeds if tablespace ref_app_ts not created and ref_app_user give unlimited tablespace privilege.

Please go through following update on above issue.

References:
Multitenant: Application Containers in Oracle Database 12c Release 2

————————————————————————————————————————-

Related Links:

Home

Oracle 12c Index

Oracle Database 12.2.0.1 – Application PDB unable to sync – An update