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

    Jul 10, 2017 4:22:29 PM by Anju Garg

    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_app version 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_app will 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_app in 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_pdb is 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_pdb application 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_pdb application 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_pdb with the application root so that the application sales_app gets 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_app is 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_pdb from 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_app since 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_app created 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_pdb from 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 SEED FROM 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_app since 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 SEED FROM 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_app since 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: Oracle

    Anju Garg

    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 : Oracle 9i Database Administration OCP Oracle 11g Database Administration OCP Oracle 11g Performance Tuning OCE Oracle 11g R2 RAC OCE Oracle 11g SQL Tuning OCE Oracle 12c Database Administration OCP Oracle Real Application Clusters 12c Certified Implementation Specialist 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/