8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Multitenant : Application Containers in Oracle Database 12c Release 2 (12.2)

Application containers are a new feature in Oracle Database 12c Release 2 (12.2) that allow you to define an application root, like a mini CDB root container, along with dependent application container PDBs. An application root can house one or more applications, each made up of shared configuration, metadata and objects that are used by the pluggable databases associated with the application root.

Multitenant : Application Container

The examples in this article use Oracle Managed Files (OMF) so no file name conversions are needed. Although not currently documented, OMF is mandatory for application container synchronisation. Some pieces will work with the appropriate FILE_NAME_CONVERT or PDB_FILE_NAME_CONVERT settings if you need them, but others will fail.

From 12.2 onward we are allowed to have a Proxy PDB, Application Root Container and a single user-defined PDB (regular or Application PDB) inside a single CDB without having to pay for the Multitenant Option. Notice we are still limited to a single user-defined PDB.

Manage an Application Root
Manage Application PDBs
Manage Applications
Application Common Objects
Manage an Application Seed
Application Container Views

Manage an Application Root

Creating a new application container is similar to creating a regular PDB, but you need to include the AS APPLICATION CONTAINER clause.

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE appcon1 AS APPLICATION CONTAINER ADMIN USER app_admin IDENTIFIED BY Password1;
ALTER PLUGGABLE DATABASE appcon1 OPEN;

You can also create an application container by cloning an existing PDB.

Once the application container is closed it can be dropped in the normal way provided it has no associated PDBs.

CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE appcon1 CLOSE;
DROP PLUGGABLE DATABASE appcon1 INCLUDING DATAFILES;

Basic management of the application container is similar to a regular PDB, including cloning, unplug, plugin etc. An application container can only be unplugged if it has no associated PDBs.

Manage Application PDBs

To create an application PDB you must be connected to an application root, not the CDB root. The application root container in this case is in the local instance, but it could be in a remote instance if we defined a local proxy PDB to pointing to it.

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

Once connected to the application root we can create a pluggable database as normal, only this time it is an application PDB.

CREATE PLUGGABLE DATABASE apppdb1 ADMIN USER pdb_admin IDENTIFIED BY Password1;
ALTER PLUGGABLE DATABASE apppdb1 OPEN;

Once we've created a new application PDB we should sync it with all the applications defined in the application root. We will discuss applications later.

ALTER SESSION SET container = apppdb1;
ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;

We can drop an existing application PDB in the normal way. That cab be done from the application root or from the regular root container.

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

ALTER PLUGGABLE DATABASE apppdb1 CLOSE;
DROP PLUGGABLE DATABASE apppdb1 INCLUDING DATAFILES;

Manage Applications

Applications are managed in the application root container using the APPLICATION clause of the ALTER PLUGGABLE DATABASE command, described below.

ALTER PLUGGABLE DATABASE APPLICATION
{ { app_name
    { BEGIN INSTALL 'app_version' [ COMMENT 'comment' ]
    | END INSTALL [ 'app_version' ]
    | BEGIN PATCH number [ MINIMUM VERSION 'app_version' ] [ COMMENT 'comment' ]
    | END PATCH [ number ]
    | BEGIN UPGRADE 'start_app_version' TO 'end_app_version' [ COMMENT 'comment' ]
    | END UPGRADE [ TO 'end_app_version' ]
    | BEGIN UNINSTALL
    | END UNINSTALL
    | SET PATCH number
    | SET VERSION 'app_version'
    | SET COMPATIBILITY VERSION { 'app_version' | CURRENT }
    | SYNC  }
  |
  { ALL SYNC }
}

If you spend some time looking at the documentation you will see there is a lot of detail about this functionality. The examples given here are purposely simplistic to allow you to try something out and get a feel for the feature.

Install an Application

Here we will step through the process of creating a simple application in the application root container.

Connect to the application root container.

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

We initiate the install of a new application, giving it a string to identify the version of the application.

ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN INSTALL '1.0';

Now we must build the application components. We'll keep this simple by creating a single table.

CREATE TABLESPACE ref_app_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER ref_app_user IDENTIFIED BY ref_app_user
  DEFAULT TABLESPACE ref_app_ts
  QUOTA UNLIMITED ON ref_app_ts
  CONTAINER=ALL;

GRANT CREATE SESSION, CREATE TABLE TO ref_app_user;

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

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

Once the application components are complete we must end the installation process.

ALTER PLUGGABLE DATABASE APPLICATION ref_app END INSTALL;

The application is now listed in the DBA_APPLICATIONS view.

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

SQL>

At this point we need to sync the application PDBs associated with the application root container, so they can see the new application. The following commands connect to the application container, check for the presence of the application objects, sych the application and check for the presence of the objects again.

-- Connect to application container.
CONN / AS SYSDBA
ALTER SESSION SET container = apppdb1;

SHOW CON_NAME

CON_NAME
------------------------------
APPPDB1
SQL>


-- Check for presence of application objects.
DESC ref_app_user.reference_data;
ERROR:
ORA-04043: object ref_app_user.reference_data does not exist

SQL>


-- Sync the application with the application root.
ALTER PLUGGABLE DATABASE APPLICATION ref_app SYNC;
--ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;


-- Check for presence of application objects.
DESC ref_app_user.reference_data;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 DESCRIPTION                                        VARCHAR2(50)

SQL>

We can see the sync operation made the application available in the application container.

The application is now registered against the application PDB, so it is listed in the DBA_APP_PDB_STATUS view.

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

COLUMN name FORMAT A20
COLUMN app_name FORMAT A20
COLUMN app_version FORMAT A10

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 = 'REF_APP';

NAME                    CON_UID APP_NAME             APP_VERSIO APP_STATUS
-------------------- ---------- -------------------- ---------- ------------
APPPDB1              4291055883 REF_APP              1.0        NORMAL

SQL>

Upgrade an Application

In this section we will demonstrate a simple upgrade to the application we created in the last section.

Connect to the application root container.

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

Initiate an upgrade of the application we created earlier, providing a new version string to identify the change.

ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN UPGRADE '1.0' TO '1.1';

Make the necessary changes to the application. In this case we will add a new column to the table and create a function to return data from the table.

ALTER TABLE ref_app_user.reference_data ADD (
  created_date DATE DEFAULT SYSDATE
);

CREATE OR REPLACE FUNCTION ref_app_user.get_ref_desc (p_id IN reference_data.id%TYPE)
  RETURN reference_data.description%TYPE
AS
  l_desc reference_data.description%TYPE;
BEGIN
  SELECT description
  INTO   l_desc
  FROM   reference_data
  WHERE  id = p_id;

  RETURN l_desc;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

GRANT EXECUTE ON ref_app_user.get_ref_desc TO PUBLIC;

Once the changes are complete we must end the upgrade process.

ALTER PLUGGABLE DATABASE APPLICATION ref_app END UPGRADE;

As before, we need to connect to the dependent application container and sync it, so the changes are visible.

-- Connect to application container.
CONN / AS SYSDBA
ALTER SESSION SET container = apppdb1;

SHOW CON_NAME

CON_NAME
------------------------------
APPPDB1
SQL>


-- Check for presence of application objects.
DESC ref_app_user.reference_data;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 DESCRIPTION                                        VARCHAR2(50)

SQL>


SELECT ref_app_user.get_ref_desc(1) FROM dual;
SELECT ref_app_user.get_ref_desc(1) FROM dual
       *
ERROR at line 1:
ORA-00904: "REF_APP_USER"."GET_REF_DESC": invalid identifier

SQL>


-- Sync the application with the application root.
ALTER PLUGGABLE DATABASE APPLICATION ref_app SYNC;
--ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;


-- Check for presence of application objects.
DESC ref_app_user.reference_data;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 DESCRIPTION                                        VARCHAR2(50)
 CREATED_DATE                                       DATE

SQL>


SELECT ref_app_user.get_ref_desc(1) FROM dual;

REF_APP_USER.GET_REF_DESC(1)
--------------------------------------------------------------------------------
Description of 1

SQL>

We can see the modification to the table and the new function became visible after the sync operation.

The new version of the application is now listed in the DBA_APP_PDB_STATUS view.

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

COLUMN name FORMAT A20
COLUMN app_name FORMAT A20
COLUMN app_version FORMAT A10

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 = 'REF_APP';

NAME                    CON_UID APP_NAME             APP_VERSIO APP_STATUS
-------------------- ---------- -------------------- ---------- ------------
APPPDB1              4291055883 REF_APP              1.1        NORMAL

SQL>

Uninstall an Application

In this section we will uninstall the application we created previously.

Connect to the application root container.

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

Initiate an uninstall of the application.

ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN UNINSTALL;

Perform any actions necessary to remove the application.

DROP USER ref_app_user CASCADE;
DROP TABLESPACE ref_app_ts INCLUDING CONTENTS AND DATAFILES;

Once the application components are removed we can end the uninstall process.

ALTER PLUGGABLE DATABASE APPLICATION ref_app END UNINSTALL;

We now sync the application conatiner associated with the application root container, so the application is removed.

-- Connect to application container.
CONN / AS SYSDBA
ALTER SESSION SET container = apppdb1;

SHOW CON_NAME

CON_NAME
------------------------------
APPPDB1
SQL>


-- Check for presence of application objects.
DESC ref_app_user.reference_data;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 DESCRIPTION                                        VARCHAR2(50)
 CREATED_DATE                                       DATE

SQL>

SELECT ref_app_user.get_ref_desc(1) FROM dual;

REF_APP_USER.GET_REF_DESC(1)
--------------------------------------------------------------------------------
Description of 1

SQL>


-- Sync the application with the application root.
ALTER PLUGGABLE DATABASE APPLICATION ref_app SYNC;
--ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;


-- Check for presence of application objects.
DESC ref_app_user.reference_data;
ERROR:
ORA-04043: object ref_app_user.reference_data does not exist

SQL>

SELECT ref_app_user.get_ref_desc(1) FROM dual;
SELECT ref_app_user.get_ref_desc(1) FROM dual
       *
ERROR at line 1:
ORA-00904: "REF_APP_USER"."GET_REF_DESC": invalid identifier

SQL>

We can see the sync operation removed the application from the application container.

The application is now marked as uninstalled for the application PDB.

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

COLUMN name FORMAT A20
COLUMN app_name FORMAT A20
COLUMN app_version FORMAT A10

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 = 'REF_APP';

NAME                    CON_UID APP_NAME             APP_VERSIO APP_STATUS
-------------------- ---------- -------------------- ---------- ------------
APPPDB1              4291055883 REF_APP              1.1        UNINSTALLED

SQL>

Application Common Objects

Objects created in an application are known as application common objects, which can take one of three forms.

Metadata-Linked (METADATA): The application root holds the metadata information that all linked PDBs will reference using a metadata-link. This can be used to centralise the definition of application objects, but all data will be held locally, not centrally.
Data-Linked (DATA): The application root holds both the metadata and data for the object. All linked PDBs will reference both the centralised metadata and data. The data-link is similar to a synonym, but between containers.
Extended Data-Linked (EXTENDED DATA): The application root holds the metadata and data for the object. All linked PDBs will reference both the centralised metadata and data. In addition to the central shared data, the local application PDB can insert its own data, which the other PDBs can't see.

The DEFAULT_SHARING parameter, which has METADATA as a default value, determines the default type of sharing used.

SHOW PARAMETER default_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
default_sharing                      string      METADATA
SQL>

This can be reset, or the type of sharing can be altered at the object level, as shown below.

The following example uses SHARING=METADATA to create a metadata-linked object.

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

The following example uses SHARING=DATA to create and populate a data-linked object.

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

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

Any attempt to insert into this table from the application PDB will result in an error.

INSERT INTO ref_app_user.reference_data VALUES (6, 'Description for 6');
INSERT INTO ref_app_user.reference_data VALUES (6, 'Description for 6')
                         *
ERROR at line 1:
ORA-65097: DML into a data link table is outside an application action

SQL>

The following example uses SHARING=EXTENDED DATA to create and populate an extended data-linked object.

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

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

This object will allow data to be inserted from the application PDB, but this local data will not be visible to the other PDBs.

INSERT INTO ref_app_user.reference_data VALUES (6, 'Description for 6');

1 row created.

SQL>

Manage an Application Seed

An application root container can optionally have an application seed database, allowing you to have a preconfigured seed for creation of new application containers. An application seed can be created from the CDB seed, an existing application container or an application root container. In this example we will create a new application seed from the CDB seed.

Connect to the application root container.

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

Create the application seed from the CDB seed, using the AS SEED clause. The seed will be named after the application root container, with the addition of the "$SEED" suffix.

-- Create the application seed.
CREATE PLUGGABLE DATABASE AS SEED ADMIN USER pdb_admin IDENTIFIED BY Password1;
ALTER PLUGGABLE DATABASE appcon1$SEED OPEN;

-- Sync it will all the applications.
ALTER SESSION SET CONTAINER=appcon1$SEED;
ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;

-- Switch it to read-only.
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE OPEN READ ONLY;

We can drop an existing application seed like any other PDB. This has no impact on application container PDBs created using it.

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

ALTER PLUGGABLE DATABASE appcon1$SEED CLOSE;
DROP PLUGGABLE DATABASE appcon1$SEED INCLUDING DATAFILES;

To create the application seed from the APPPDB1 application container we created earlier, we would do the following.

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

CREATE PLUGGABLE DATABASE AS SEED FROM apppdb1;
ALTER PLUGGABLE DATABASE appcon1$SEED OPEN;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE OPEN READ ONLY;

To create the application seed from the application root we would do the following. The open command will result in some violations, but they can be ignored as the "pdb_to_apppdb.sql" will fix them.

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

CREATE PLUGGABLE DATABASE AS SEED FROM appcon1;
ALTER PLUGGABLE DATABASE appcon1$SEED OPEN;
ALTER SESSION SET CONTAINER=appcon1$SEED;
@$ORACLE_HOME/rdbms/admin/pdb_to_apppdb.sql
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE OPEN READ ONLY;

Application Container Views

The following views are available from the application root container and in some cases the application container PDBs to give information about applications.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.