Toad World

Oracle Multi-tenant Application Containers – Part II – Create Application Container

Avatar photo
Post author:Written By

Overview

As I discussed in my last article Oracle Multi-tenant Application Containers Part-I, Oracle Database 12.2.0.1 introduces the concept of multi-tenant application containers that enable many PDBs to share application objects such as code, metadata, and data so that application administrators can now efficiently manage many application PDBs as one in a single Application Container, while securely isolating their individual customer-specific data. In this second article in the series, we will learn how to create an application container within a CDB.

Here, we have an Oracle database 12.2.0.1 CDB called orclcdb as shown. Within this CDB, we will create an application container for sales application sales_app whose common objects will be shared by four application PDBs North_app_pdb, South_app_pdb, East_app_pdb, and West_app_pdb. The application common objects for sales_app will be stored in application root sales_app_root. Also, we will create an application seed PDB called sales_app_root$seed.

I will demonstrate:

  • Creation of application root
  • Installation of application in application root
  • Creation of application PDB from
    • CDB Seed
    • Synced application seed
    • Unsynced application seed
    • Synced application PDB
  • Creation of Application Seed PDB from
    • CDB seed
    • Application root
    • Synced application PDB
  • Syncing of
    • Application seed PDB
    • Application PDB

Demonstration

Let us first connect to CDB orclcdb and verify that currently a CDB root and seed are associated with it. There are no user created PDBs.

SQL> conn sys/oracle@orclcdb as sysdba
     set sqlprompt 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 
         order by con_id;
    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
2 rows selected.
  • Create application container

A new application container can be created just like a regular PDB using the CREATE PLUGGABLE DATABASE statement, but we need to include the AS APPLICATION CONTAINER clause and specify a container name. As a result, an application container having the same name as the application root gets created. Let us create the application root sales_app_root, which implicitly creates the application container sales_app_root as well.

CDB$ROOT>ho mkdir -p /u01/app/oracle/oradata/orclcdb/sales_app_root
CDB$ROOT>CREATE PLUGGABLE DATABASE

sales_app_root AS APPLICATION CONTAINER

         ADMIN USER appadmin IDENTIFIED BY oracle
         CREATE_FILE_DEST='/u01/app/oracle/oradata/orclcdb/sales_app_root'; 
Pluggable database created.
CDB$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;
 
   CON_ID NAME            OPEN_MODE  APP_ROOT APP_PDB  APP
---------- --------------- ---------- -------- -------- ---
         1 CDB$ROOT        READ WRITE NO       NO       NO
         2 PDB$SEED        READ ONLY  NO       NO       NO
         3 SALES_APP_ROOT  MOUNTED    YES      NO       NO
3 rows selected.
CDB$ROOT>alter pluggable database sales_app_root open;
          sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3

SALES_APP_ROOT                READ WRITE

 NO

It is worth mentioning here that an application root belongs to only one container; i.e., CDB root (CDB$ROOT). The application root has its own service name, and users can connect to the application root in the same way that they connect to a PDB.

Connect to application root sales_app_root and note that when the application root is opened for the first time, the database automatically creates an implicit application with the name APP$<guid>, where guid is the global unique ID of the application root.

SQL>conn sys/oracle@host01:1522/sales_app_root as sysdba
               set sqlprompt SALES_APP_ROOT>
SALES_APP_ROOT>select app_name, app_version, app_id, app_status,
                      app_implicit implicit
              from dba_applications;
APP_NAME                       APP_VERSION  APP_ID APP_STATUS IMPLICIT
------------------------------------- ------------ ------ ---------- --------
APP$5313F8AEAFA337B0E05364C909C08D65   1.0            2 NORMAL     Y
 
SALES_APP_ROOT>select CON_ID , name, CON_UID, Guid from v$containers;
    CON_ID NAME            CON_UID GUID
---------- -------------------- ---------- --------------------------------
       3 SALES_APP_ROOT 2775271524 5313F8AEAFA337B0E05364C909C08D65

Let us install application sales_appversion 1.0 in application root sales_app_root.  The application creates an application common user named sales_app_user, grants necessary privileges, and then creates a metadata-linked table named sales_app_user.customers. These common application objects for application sales_appwill be shared with the application PDBs that belong to the application root.

— Begin the installation of application sales_app

SALES_APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION sales_app BEGIN INSTALL

‘1.0‘

;
Pluggable database altered.
SALES_APP_ROOT>@get_app_status
APP_NAME                               APP_VERSION   APP_ID APP_STATUS  IMPLICIT
-------------------------------------- ------------ ------ ------------ --------
SALES_APP                                                   3 INSTALLING   N
APP$5313F8AEAFA337B0E05364C909C08D65   1.0                2 NORMAL    Y
-- Create common application user sales_app_user and grant necessary privileges
SALES_APP_ROOT>CREATE USER sales_app_user IDENTIFIED BY oracle  CONTAINER=ALL;
                GRANT CREATE SESSION, create procedure, CREATE TABLE,
                     unlimited tablespace TO sales_app_user;

— Create common application metadata-linked table sales_app_user.customers

SALES_APP_ROOT>CREATE TABLE sales_app_user.customers SHARING=METADATA
               ( cust_id    NUMBER constraint cust_pk primary key,
                 cust_name  varchar2(30),
                 cust_add   varchar2(30),
                 cust_zip   NUMBER
               );

— End the application installation

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$5313F8AEAFA337B0E05364C909C08D65   1.0                 2 NORMAL Y
SALES_APP                               1.0                3

NORMAL

 N

Now we have created common objects for the application sales_appin application root sales_app_root. Next, we will create an application PDB which can share these objects.

  • Create application PDB north_app_pdb from CDB seed (PDB$Seed)

Currently no application / seed PDB is associated with the application container sales_app_root.

SALES_APP_ROOT>sho pdbs
    CON_ID CON_NAME                      OPEN MODE  RESTRICTED
----------  ------------------------------ ---------- ----------
       3    SALES_APP_ROOT                READ WRITE NO

Let us create application PDB north_app_pdb from CDB seed  PDB$Seed.

— Create the directory for the new application PDB north_app_pdb

SALES_APP_ROOT>ho mkdir -p /u01/app/oracle/oradata/orclcdb/sales_app_root/north_app_pdb

— Create new application PDB north_app_pdb

SALES_APP_ROOT>CREATE PLUGGABLE DATABASE north_app_pdb
               ADMIN USER pdb_admin IDENTIFIED BY Password1
               CREATE_FILE_DEST =         '/u01/app/oracle/oradata/orclcdb/sales_app_root/north_app_pdb';
Pluggable database created.

Now an application PDB north_app_pdbis associated with the application container sales_app_root.

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 NORTH_APP_PDB      MOUNTED    NO       YES      NO

— Open the application PDB north_app_pdb

SALES_APP_ROOT>ALTER PLUGGABLE DATABASE north_app_pdb OPEN;
               sho pdbs
    CON_ID CON_NAME                   OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
       3 SALES_APP_ROOT         READ WRITE NO
       4 NORTH_APP_PDB          READ WRITE NO

Connect to the north_app_pdbapplication PDB and check for the presence of the common application table sales_app_user.customers.

APP_ROOT>conn sys/oracle@host01:1522/north_app_pdb as sysdba
         set sqlprompt NORTH_APP_PDB>
NORTH_APP_PDB>desc sales_app_user.customers

ERROR:



ORA-04043: object sales_app_user.customers does not exist


We can see that the common application table sales_app_user.customers cannot be accessed from the north_app_pdbapplication PDB since it was created from CDB seed, which cannot share common application objects created in the application root. As a result, the application sales_app is not yet registered with the application PDB north_app_pdb.


SALES_APP_ROOT>@get_app_pdb_status


no rows selected


We will now synchronize north_app_pdbwith the application root so that the application sales_appgets registered with it and common application objects are visible to north_app_pdb.


NORTH_APP_PDB>ALTER PLUGGABLE DATABASE APPLICATION sales_app SYNC;
Pluggable database altered.
SALES_APP_ROOT>@get_app_pdb_status
NAME                 CON_UID   APP_NAME            APP_VERSIO APP_STATUS
-------------------- ---------- -------------------- ---------- ------------
NORTH_APP_PDB        2659474630 SALES_APP           1.0          NORMAL
NORTH_APP_PDB>desc sales_app_user.customers
 Name                                Null?      Type
 ---------------------------------  --------    -------------------------
 CUST_ID                            NOT NULL    NUMBER
 CUST_NAME                                      VARCHAR2(30)
 CUST_ADD                                       VARCHAR2(30)
 CUST_ZIP                                       NUMBER


Hence, an application PDB created from a CDB seed needs to synchronized with the application root so as to access common application objects.


Next, we will create an application seed database which can serve as a template for creating future application PDBs within the application container. An application seed can be created from the CDB seed, an existing application PDB, or an application root container. Presently, we will create a new application seed from the CDB seed (PDB$Seed).


    

  • Create application seed from CDB seed (PDB$Seed)
    • 



SALES_APP_ROOT>ho mkdir -p /u01/app/oracle/oradata/orclcdb/sales_app_root/sales_app_root_seed
SALES_APP_ROOT>create pluggable database as seed
               admin user seedadmin identified by oracle roles=(connect)
               CREATE_FILE_DEST=   '/u01/app/oracle/oradata/orclcdb/sales_app_root/sales_app_root_seed';
Pluggable database created.


Note that name of an application seed has the format <application_root>$SEED. Here it translates to sales_app_root$seed. 


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    NORTH_APP_PDB        READ WRITE NO        YES      NO
       5   SALES_APP_ROOT$SEED  MOUNTED    NO        YES      YES


Open the application seed and note that application sales_appis not yet registered with it since it has been created from the CDB seed, which cannot share common application objects created in the application root.


SALES_APP_ROOT>ALTER PLUGGABLE DATABASE sales_app_root$seed OPEN;
               sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
       3    SALES_APP_ROOT                READ WRITE NO
       4    NORTH_APP_PDB                  READ WRITE NO
       5 SALES_APP_ROOT$SEED               READ WRITE NO
SALES_APP_ROOT>@get_app_pdb_status
NAME                    CON_UID     APP_NAME            APP_VERSIO APP_STATUS
--------------------    ----------  -------------------- ---------- ----------
NORTH_APP_PDB           2659474630 SALES_APP            1.0     NORMAL


Hence, an application seed, when created from CDB seed, is not synchronized with the application root.


Let us now create an application PDB east_app_pdbfrom this unsynced application seed.


    

  • Create application PDB from unsynced application seed
    • 



SALES_APP_ROOT>ho mkdir -p /u01/app/oracle/oradata/orclcdb/sales_app_root/east_app_pdb
SALES_APP_ROOT>CREATE PLUGGABLE DATABASE east_app_pdb
               ADMIN USER pdb_admin IDENTIFIED BY oracle
               CREATE_FILE_DEST =
              '/u01/app/oracle/oradata/orclcdb/sales_app_root/east_app_pdb';
Pluggable database created.
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   NORTH_APP_PDB        READ WRITE NO       YES      NO
       5   SALES_APP_ROOT$SEED  READ WRITE NO       YES      YES
       8   EAST_APP_PDB         MOUNTED    NO       YES      NO


Open the application PDB east_app_pdb and note that it is as yet unsynced with application sales_appsince it has been created from an unsynced application seed.


SALES_APP_ROOT>alter pluggable database east_app_pdb open;
               sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SALES_APP_ROOT                 READ WRITE NO
         4 NORTH_APP_PDB                  READ WRITE NO
         5 SALES_APP_ROOT$SEED            READ WRITE NO
         8 EAST_APP_PDB                    READ WRITE NO
SALES_APP_ROOT>@get_app_pdb_status
NAME                    CON_UID APP_NAME             APP_VERSIO APP_STATUS
-------------------- ---------- -------------------- ---------- ------------
NORTH_APP_PDB        2659474630 SALES_APP            1.0          NORMAL


Hence, an application PDB when created from an unsynced application seed is created unsynced with the application root.


Let us synchronize the application seed with the application root so that the application sales_appcreated in application root gets registered with it.


SQL>conn sys/oracle@host01:1522/sales_app_root$seed as sysdba
    set sqlprompt SALES_APP_ROOT$SEED>
SALES_APP_ROOT$SEED>alter pluggable database application sales_app sync;
Pluggable database altered.
SALES_APP_ROOT>@get_app_pdb_status
NAME                    CON_UID APP_NAME             APP_VERSIO APP_STATUS
-------------------- ---------- -------------------- ---------- ------------
NORTH_APP_PDB        2659474630 SALES_APP            1.0          NORMAL
SALES_APP_ROOT$SEED   651701329 SALES_APP            1.0          NORMAL


Let us now create an application PDB west_app_pdbfrom this synced application seed.


    

  • Create application PDB from synced application seed
    • 



SALES_APP_ROOT>ho mkdir -p /u01/app/oracle/oradata/orclcdb/sales_app_root/west_app_pdb
SALES_APP_ROOT>CREATE PLUGGABLE DATABASE west_app_pdb
      ADMIN USER pdb_admin IDENTIFIED BY oracle
CREATE_FILE_DEST='/u01/app/oracle/oradata/orclcdb/sales_app_root/west_app_pdb';
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 NORTH_APP_PDB        READ WRITE NO       YES               NO
         5 SALES_APP_ROOT$SEED  READ WRITE NO       YES          YES
         6 WEST_APP_PDB         MOUNTED    NO       YES          NO
         8 EAST_APP_PDB         READ WRITE NO       YES          NO
SALES_APP_ROOT>alter pluggable database west_app_pdb open;
               sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SALES_APP_ROOT                 READ WRITE NO
         4 NORTH_APP_PDB                  READ WRITE NO
         5 SALES_APP_ROOT$SEED            READ WRITE NO
         6 WEST_APP_PDB                    READ WRITE NO
         8 EAST_APP_PDB                    READ WRITE NO


Verify that the application PDB west_app_pdb is synced with the application sales_app, as it has been created from synced application seed.


SALES_APP_ROOT>@get_app_pdb_status
NAME                    CON_UID APP_NAME             APP_VERSIO APP_STATUS
-------------------- ---------- -------------------- ---------- -----------
NORTH_APP_PDB        2659474630 SALES_APP             1.0            NORMAL
SALES_APP_ROOT$SEED   651701329 SALES_APP             1.0            NORMAL
WEST_APP_PDB         2183572329 SALES_APP             1.0            NORMAL


Hence, an application PDB when created from a synced application seed is created synced with the application root.


We saw that we need to manually sync an application seed created from a CDB seed.


Let us now drop the application seed and recreate it from the application root sales_app_root.


    

  • Create application seed from application root
    • 



— Drop application seed


SALES_APP_ROOT>ALTER PLUGGABLE DATABASE sales_app_root$SEED CLOSE IMMEDIATE;
               DROP PLUGGABLE DATABASE sales_app_root$SEED INCLUDING DATAFILES;
Pluggable database dropped.
SALES_APP_ROOT>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SALES_APP_ROOT                READ WRITE NO
         4 NORTH_APP_PDB                  READ WRITE NO
         6 WEST_APP_PDB                   READ WRITE NO
         8 EAST_APP_PDB                   READ WRITE NO


— Recreate application seed  from application root and open it


SALES_APP_ROOT>CREATE PLUGGABLE DATABASE AS SEEDFROM sales_app_root;
Pluggable database created.
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 NORTH_APP_PDB        READ WRITE NO       YES      NO
         6 WEST_APP_PDB         READ WRITE NO       YES      NO
         7 SALES_APP_ROOT$SEED  MOUNTED    NO       YES      YES
         8 EAST_APP_PDB         READ WRITE NO       YES      NO


When we open the application seed, we get a warning as the pdb_to_apppdb.sql script must be executed on the created regular PDB for it to become a full application PDB.


SALES_APP_ROOT>ALTER PLUGGABLE DATABASE SALES_APP_ROOT$SEED OPEN;


Warning: PDB altered with errors.


SALES_APP_ROOT> SELECT cause, type, message, status, action
                FROM pdb_plug_in_violations
                WHERE name='SALES_APP_ROOT$SEED';
CAUSE         TYPE     MESSAGE                    STATUS    ACTION
--------------- --------- ------------------------------ --------- ------------------------------
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.


 


CDB$ROOT>alter session set container= SALES_APP_ROOT$SEED;
         set sqlprompt SALES_APP_ROOT$SEED>
SALES_APP_ROOT$SEED>alter pluggable database SALES_APP_ROOT$SEED close immediate;
                     alter pluggable database SALES_APP_ROOT$SEED open;
SALES_APP_ROOT$SEED>@?/rdbms/admin/pdb_to_apppdb
..... Part of the output follows . . . .
SALES_APP_ROOT$SEED>-- leave the PDB in the same state it was when we started
SALES_APP_ROOT$SEED>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.
 
SALES_APP_ROOT$SEED>
SALES_APP_ROOT$SEED>WHENEVER SQLERROR CONTINUE;
SALES_APP_ROOT>


— SQL script pdb_to_apppdb.sql completes successfully


SALES_APP_ROOT> SELECT cause, type, message, status, action
      `         FROM pdb_plug_in_violations
          WHERE name='SALES_APP_ROOT$SEED';
CAUSE           TYPE      MESSAGE                        STATUS         ACTION
--------------- -------- ------------------------------ ---------  ----------------
Non-Application ERROR    Non-Application PDB plugged in RESOLVED Run pdb_to_apppdb.sql
PDB to Applica           as an Application PDB, requir
tion PDB                  es pdb_to_apppdb.sql be run.
SALES_APP_ROOT>sho pdbs          
   CON_ID CON_NAME                        OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SALES_APP_ROOT                 READ WRITE NO
         4 NORTH_APP_PDB                  READ WRITE NO
         6 WEST_APP_PDB                   READ WRITE NO
         7 SALES_APP_ROOT$SEED            READ WRITE YES
         8 EAST_APP_PDB                   READ WRITE NO


Note that the application seed is in sync with sales_appsince it was created from the application root that houses the application.


SALES_APP_ROOT>@get_app_pdb_status
NAME                 CON_UID   APP_NAME             APP_VERSIO APP_STATUS
-------------------- ---------- -------------------- ---------- ------------
NORTH_APP_PDB        2659474630 SALES_APP             1.0          NORMAL
WEST_APP_PDB         2183572329 SALES_APP             1.0          NORMAL
SALES_APP_ROOT$SEED  1481998572 SALES_APP             1.0          NORMAL


We saw that the application seed created from the application root is synced with the application, but script pdb_to_apppdb.sql must be run to convert it from a non-application PDB to an application PDB


Another method of creating a synced application seed is to create it from a synced application PDB. In this case, we need not run script pdb_to_apppdb.sql.


    

  • Create Application Seed from synced Application PDB
    • 



We will drop and recreate the application seed from the synced application PDB north_app_pdb.


— Drop application seed


SALES_APP_ROOT>ALTER PLUGGABLE DATABASE sales_app_root$SEED CLOSE IMMEDIATE;
                 DROP PLUGGABLE DATABASE sales_app_root$SEED INCLUDING DATAFILES;
                   sho pdbs
   CON_ID CON_NAME                        OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
       3  SALES_APP_ROOT                  READ WRITE NO
       4   NORTH_APP_PDB                  READ WRITE NO
       6   WEST_APP_PDB                   READ WRITE NO
       8   EAST_APP_PDB                   READ WRITE NO


— Recreate application seed from the synced north_app_pdb application PDB and open it


SALES_APP_ROOT>CREATE PLUGGABLE DATABASE AS SEEDFROM north_app_pdb
CREATE_FILE_DEST='/u01/app/oracle/oradata/orclcdb/sales_app_root/sales_app_root_seed';
  Pluggable database created.
SALES_APP_ROOT>ALTER PLUGGABLE DATABASE sales_app_root$SEED OPEN;
               @get_app_containers
    CON_ID NAME                 OPEN_MODE  APP_ROOT APP_PDB  APP_SEED
---------- -------------------- ---------- -------- -------- --------
         3 SALES_APP_ROOT       READ WRITE YES      NO      NO
         4 NORTH_APP_PDB        READ WRITE NO       YES      NO
         5 SALES_APP_ROOT$SEED  READ WRITE NO      YES      YES
         6 WEST_APP_PDB         READ WRITE NO       YES      NO
         8 EAST_APP_PDB         READ WRITE NO       YES      NO


It can be seen that the Seed database is automatically synced with the application sales_appsince it has been created from a synced application PDB.


SALES_APP_ROOT>@get_app_pdb_status
NAME                    CON_UID   APP_NAME             APP_VERSIO APP_STATUS
--------------------   ---------- -------------------- ---------- ----------
NORTH_APP_PDB          2659474630 SALES_APP          1.0            NORMAL
WEST_APP_PDB           2183572329 SALES_APP          1.0            NORMAL
SALES_APP_ROOT$SEED   375608118 SALES_APP            1.0            NORMAL


Similarly, it can be shown that if an application seed is created from an unsynced application PDB; e.g., east_app_pdb, it gets created unsynced.


Hence, the synchronization status of an application seed created from an application PDB is same as that of the source application PDB.


We have already learned how to create an application PDB from a CDB seed and an unsynced as well as a synced application seed. Now we will create a new application PDB south_app_pdb from a synced application PDB; i.e., north_app_pdb


    

  • Create application PDB from synced application PDB
    • 



SALES_APP_ROOT>ho mkdir -p /u01/app/oracle/oradata/orclcdb/sales_app_root/south_app_pdb
SALES_APP_ROOT>CREATE PLUGGABLE DATABASE south_app_pdb FROM north_app_pdb
              CREATE_FILE_DEST='/u01/app/oracle/oradata/orclcdb/sales_app_root/south_app_pdb';
Pluggable database created.
SALES_APP_ROOT>alter pluggable database south_app_pdb open;
                @get_app_containers
 
    CON_ID NAME                 OPEN_MODE  APP_ROOT APP_PDB  APP_SEED
---------- -------------------- ---------- -------- -------- --------
         3 SALES_APP_ROOT       READ WRITE YES      NO      NO
         4 NORTH_APP_PDB        READ WRITE NO       YES      NO
         5 SALES_APP_ROOT$SEED  READ WRITE NO       YES      YES
         6 WEST_APP_PDB         READ WRITE NO       YES      NO
         7 SOUTH_APP_PDB        READ WRITE NO       YES      NO
         8 EAST_APP_PDB         READ WRITE NO       YES      NO
6 rows selected.


 


Note that the application PDB south_app_pdb has been created synced with the application sales_app since it has been created from the synced application PDB north_app_pdb.


SALES_APP_ROOT>@get_app_pdb_status
NAME                 CON_UID    APP_NAME            APP_VERSIO APP_STATUS
-------------------- ---------- -------------------- ---------- ------------
NORTH_APP_PDB        2659474630 SALES_APP             1.0      NORMAL
WEST_APP_PDB         2183572329 SALES_APP             1.0      NORMAL
SALES_APP_ROOT$SEED  375608118  SALES_APP             1.0      NORMAL
SOUTH_APP_PDB        2255926930 SALES_APP             1.0      NORMAL


Had the application PDB been created from an unsynced application PDB, e.g., east_app_pdb, it would have been created unsynced.


Hence, synchronization status of an application PDB created from another application PDB is same as the source application PDB.


In this article, we learned various methods of creating application PDBs and application seeds. Note that here we have created just one application in the application container, but it is quite possible to create multiple applications (e.g., HR application) within an application container and various application PDBs can be synchronized with separate applications as desired.


Summary


    

  • Application root contains application common objects which are visible only to the application PDBs and seed PDBs within the container when they have been synchronized with the application.
    • 

  • Application seed can be created from

      

    • CDB seed – Application seed is created unsynced with the application in application root
      • 

    • Application root – Application seed is created synced with the application in application root but script pdb_to_apppdb.sql needs to be run
      • 

    • Application PDB – Synchronization status of Application Seed created  is same as the source application PDB
      • 

    

    • 

  • Application PDBs can be created from

      

    • CDB seed – Application PDB is created unsynced with the application in application root
      • 

    • Application seed – Synchronization status of created Application PDB is same as the source application seed
      • 

    • Application PDB – Synchronization status of created Application PDB is same as the source application PDB
      • 

    

    • 

  • It is possible to create multiple applications within an application container and various application PDBs can be synchronized with separate applications as desired.
    • 



References:



Scripts used in the article:


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_containers.sql


— Find out the containers in an application root when executed from an 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_pdb_status.sql


— Find out the 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: 






	


		

			

    		About the Author
    	


			

		

			
				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 passionate about learning and has keen interest in RAC and Performance Tuning.  You can learn all about Anju’s credentials and read more from her via her technical blog site at http://oracleinaction.com/				


			
		


	





	

		

			
Notable Replies

		

		
    		
  1. 
			
    
				
    
					
    
						
						says:
					
    
				
    
				
    
					abhinav.bhavaraju
					
    
					
    
					
    Hello,
    

    Thanks for the example of application PDBs.
    
I have few questions on this one.
    

      

    1. When we create the application using "begin" but we wanted to abort, can we do it? If yes how?
      2. 

    2. We know that we can uninstall the application from application root, but can we drop the application instead or do we have to drop the entire application root and start from the beginning?
      3. 

    

    Regards
    
Abhinav
    
Please advise.
    
				
    
			
    
		
    2. 
    		
  2. 
			
    
				
    
					
    
						
						says:
					
    
				
    
				
    
					cjroca
					
    
					
    
					
    Hi Everyone, Does anybody know how to create the APP tables by using impdp?, because I've seen several posts regarding app container and installing application by creating tables using CREATE statement, but what about installing tables with millons of rows????.
    
Thank you in advance.
    
				
    
			
    
		
    3. 
    		
  3. 
			
    
				
    
					
    
						
						says:
					
    
				
    
				
    
					mbzmak
					
    
					
    
					
    I'm not sure whether that's the only procedure, but one possible solution could be:
    

      

    1. use Data Pump import with contents=metadata_only and sqlfile parameters, optionally also the exclude parameter to filter on what you want to be on the script
      2. 

    2. run the obtained script on the application root in the context of installing an application
      3. 

    3. sync your APP pds(s)
      4. 

    4. then re-run the Data Pump Import with contents=data_only towards the APP pdb(s)
      5. 

    
				
    
			
    
		
    4. 
    

		

			

				Continue Discussion