You can install, upgrade, and patch an application in an application root.

You must issue an ALTER PLUGGABLE DATABASE ... BEGIN statement to start the operation and an ALTER PLUGGABLE DATABASE ... END statement to end the operation. You can issue these statements in the same user session or in different user sessions.

The following is the typical process for creating and maintaining an application in an application container:

1. Create the application container.

2. Install the application in the application root using ALTER PLUGGABLE DATABASE ... BEGIN INSTALL.

   This step includes creating the application data model and configuring the application common users and application common objects.

   Note:

   SQL*Loader is the only supported utility for bulk inserts into tables during application install, upgrade, and patch operations.

3. Create the application PDBs in the application root.

4. Synchronize each application PDB that should install the application with the application root. The statement is ALTER PLUGGABLE DATABASE APPLICATION ... SYNC.

5. Load the data for each application PDB.

6. Maintain the application. Upgrade using ALTER PLUGGABLE DATABASE ... BEGIN UPGRADE, and patch using ALTER PLUGGABLE DATABASE ... BEGIN PATCH.

7. Synchronize application PDBs that should apply changes from upgrades and patches.

8. Add new application PDBs whenever necessary.

9. If necessary, uninstall the application using ALTER PLUGGABLE DATABASE ... BEGIN UNINSTALL.

The application container also manages the versions of the application and the patches to the application.

The application container manages versions as follows:

• When you install an application, you must specify the application version number.

• When you upgrade an application, you must specify the old application version number and the new application version number.

• When you patch an application, you must specify the minimum application version number for the patch and the patch number.

As the application evolves, the application container maintains all of the versions and patch changes that you apply.

You can also configure the application container so that different application PDBs use different application versions. For example, if you provide an application to various customers, and each customer has its own application PDB, some customers might wait longer to upgrade the application. In this case, some application PDBs can use the latest version of the application, whereas other application PDBs can use an older version of the application.

The application module name is set by the DBMS_APPLICATION_INFO.SET_MODULE procedure or the equivalent OCI attribute setting.

The module name is necessary during application maintenance because of other activity that might be occurring in the database. For example, statements issued by background processes should not be captured in the application capture tables. Also, other users might execute statements that are unrelated to the application. A module name check distinguishes what should be captured from what should not be captured. Only sessions whose module name matches the module name of the session where APPLICATION BEGIN was issued are considered for capture.

SELECT app_capture_module FROM dba_applications WHERE app_name='APEX';

Some clauses, such as the SHARING clause, are valid only when issued between an ALTER PLUGGABLE DATABASE ... BEGIN statement and an ALTER PLUGGABLE DATABASE ... END statement. For these clauses, if the module name for a session does not match, then this session is not included in between the BEGIN and END statements, causing statements that include the clause to fail with ORA-65021 or other errors.

The most common cause for a module name mismatch is the default module name. For example, SQL*Plus sets a default module name when a connection is made to the database. A connection as a SYSDBA user results in one default module name (for example, sqlplus@host1 (TNS V1-V3)), whereas a connection as a non-SYSDBA user results in a different default module name (for example, SQL*Plus). When SYSDBA and non-SYSDBA users are both performing maintenance, you must explicitly set the module name in each session to the same value, and not rely the default settings in SQL*Plus.

SELECT app_capture_service FROM dba_applications WHERE app_name='APEX';

SQL> CONNECT / AS SYSDBA
Connected.

SQL> select module from v$session where audsid = SYS_CONTEXT('USERENV','sessionid'); 

MODULE
----------------------------------------------------------------
sqlplus@host1 (TNS V1-V3)

SQL> CONNECT dba1
Password: *************
Connected.

SQL> select module from v$session where audsid = SYS_CONTEXT('USERENV','sessionid'); 

MODULE
----------------------------------------------------------------
SQL*Plus

You issue ALTER PLUGGABLE DATABASE APPLICATION statements to install an application in the application root.

You install the application in the application root only. Application PDBs that synchronize with the application install the application automatically. With the automated method, you can perform the installation using one or more of the following techniques: scripts, SQL statements, and graphical user interface tools.

Start of the installation with an ALTER PLUGGABLE DATABASE APPLICATION BEGIN INSTALL statement and the end of the install with an ALTER PLUGGABLE DATABASE APPLICATION END INSTALL statement. Each installation must be associated with an application name and version number, which are specified in the ALTER PLUGGABLE DATABASE APPLICATION statements.

In automated propagation, the application is installed in the application PDBs that synchronize with the application in the application root.

You issue ALTER PLUGGABLE DATABASE APPLICATION statements to upgrade an application in the application root.

After an upgrade, application changes caused by the upgrade propagate to the application PDBs that synchronize with the application root.

To patch an application in the application root, issue ALTER PLUGGABLE DATABASE APPLICATION statements.

You patch the application in the application root only. The application PDBs that synchronize with the application apply the changes. You can perform the patch using one or more of the following techniques: scripts, SQL statements, and graphical user interface tools.

The patch is restricted to a small set of operations. In general, destructive operations, such as dropping a table, are not allowed in a patch. If you attempt to patch an application, and the operation raises an “operation not supported in an application patch” error, then upgrade the application instead of patching it to make the necessary changes.

Indicate the start of the patch with an ALTER PLUGGABLE DATABASE APPLICATION BEGIN PATCH statement and the end of the patch with an ALTER PLUGGABLE DATABASE APPLICATION END PATCH statement. Each patch must be associated with an application name, starting version number, and ending version number. Specify these values in the ALTER PLUGGABLE DATABASE APPLICATION statements.

Application changes for the patch are propagated to the application PDBs that synchronize with the application in the application root.

1. In SQL*Plus, ensure that the current container is the application root.
2. Run the ALTER PLUGGABLE DATABASE APPLICATION BEGIN PATCH statement in the following form:

   ALTER PLUGGABLE DATABASE APPLICATION application_name 
     BEGIN PATCH patch_number 
     MINIMUM VERSION 'minimum_application_version_number';

   For example, run the following statement if the application_name is salesapp, the patch_number is 987654, and the minimum_application_version_number is 4.2:

   ALTER PLUGGABLE DATABASE APPLICATION salesapp 
     BEGIN PATCH 987654 MINIMUM VERSION '4.2';

   The minimum_application_version_number indicates the minimum application version at which an application installation must be before the patch can be applied to it.
3. Patch the application using scripts, SQL statements, and graphical user interface tools.
4. Run the ALTER PLUGGABLE DATABASE APPLICATION END PATCH statement in the following form:

   ALTER PLUGGABLE DATABASE APPLICATION application_name 
     END PATCH patch_number;

   For example, run the following statement if the application_name is salesapp and the patch_number is 987654:
   ALTER PLUGGABLE DATABASE APPLICATION salesapp END PATCH 987654;

   Note:

   Ensure that the application_name and patch_number match in the ALTER PLUGGABLE DATABASE APPLICATION BEGIN PATCH statement and the ALTER PLUGGABLE DATABASE APPLICATION END PATCH statement.
5. Synchronize all of the application PDBs that must patch the application by issuing an ALTER PLUGGABLE DATABASE APPLICATION statement with the SYNC clause.

You can migrate an application to an application root by creating an application root using an existing PDB.

If the application is installed in more than one PDB, then you can use one of the PDBs to create the application root. You can use one of the methods available for copying a PDB to an application root, such as cloning the PDB or plugging in the PDB as an application root.

When common users, roles, or profiles exist in the PDB used to create the application root, you must run procedures in the DBMS_PDB package to associate them with the application. When an application root created from a PDB is first opened, each local user, role, and profile is marked as common. The procedures in the DBMS_PDB package associate the user, role, or profile with the application. Therefore, all DDL operations on the user, role, or profile must subsequently be done within an application BEGIN...END block of this application.

When shared database objects exist in the application root, you must run procedures in the DBMS_PDB package to associate the database objects with the application as application common objects. Therefore, all DDL operations on the application common objects must subsequently be done within an application BEGIN...END block of this application.

After the application root is in place, you can create application PDBs in the new application container using the existing PDBs. The application PDBs that you create must contain the application objects, including their data. Additional steps are necessary to synchronize the application version and patch number and to establish shared database objects in the application PDBs.

Migrate an application that is installed in a PDB by copying the PDB to an application container.

1. In the CDB, create the application root by cloning the existing PDB, relocating the existing PDB, or by unplugging and plugging in the existing PDB.
   The new application root must contain all database objects used by the application.
2. With the application root as the current container, start an application installation operation by issuing an ALTER PLUGGABLE DATABASE ... BEGIN INSTALL statement.
3. Optional: Query the COMMON column in the DBA_USERS, DBA_ROLES, and DBA_PROFILES views to determine which users, roles, and profiles are common.
4. Run the following procedures in the DBMS_PDB package to associate users, roles. and profiles with the application:
   • Run the SET_USER_EXPLICIT procedure to set application common users.
   • Run the SET_ROLE_EXPLICIT procedure to set application common roles.
   • Run the SET_PROFILE_EXPLICIT procedure to set application common profiles.
   If you do not have EXECUTE privilege on the DBMS_PDB package, then you can run these procedures in the DBMS_PDB_ALTER_SHARING package.
5. Optional: With the application root as the current container, query the SHARING column in the DBA_OBJECTS view to determine which database objects are shared.
6. Run the following procedures in the DBMS_PDB package to associate database objects with the application:
   • Run the SET_DATA_LINKED procedure to set data-linked application common objects.
   • Run the SET_METADATA_LINKED procedure to set metadata-linked application common objects.
   • Run the SET_EXT_DATA_LINKED procedure to set extended data-linked application common objects.
   If you do not have EXECUTE privilege on the DBMS_PDB package, then you can run these procedures in the DBMS_PDB_ALTER_SHARING package.
7. End the application installation operation by issuing an ALTER PLUGGABLE DATABASE ... END INSTALL statement.
8. Optional: Rerun the queries that you ran previously to ensure that the sharing properties of the database objects are correct and that the common properties of the users, roles, and profiles are correct.
9. Optional: If existing PDBs use the application, then create application PDBs using these existing PDBs.
   See "Creating an Application PDB Using an Existing PDB".

After migrating an existing application to an application root, you can use an existing PDB that uses the application to create an application PDB.

1. In the application root, create the application PDB by cloning the existing PDB or by unplugging and plugging in the existing PDB.
   Violations will be reported during PDB creation.
2. Connect to or switch to the new PDB as a user with the required privileges.
3. Run the pdb_to_apppdb.sql script in the ORACLE_HOME/rdbms/admin directory.
   The script automatically synchronizes the application PDB with the application root.
4. Optional: Query the SHARING column in the DBA_OBJECTS view to ensure that the sharing properties of the database objects are correct.
5. Optional: Query the COMMON column in the DBA_USERS, DBA_ROLES, and DBA_PROFILES views to ensure that the common properties of the users, roles, and profiles are correct.

A proxy PDB can synchronize an application root and a replica of the application root.

An application might be installed in several application containers. Installing, upgrading, and patching the application are more efficient when you use proxy PDBs.

In this configuration, one application container has the master application root. The master application root is where you install, upgrade, and patch the application. Application root replicas are exact copies of the master application root. Each application root replica is referenced by a proxy PDB in the master application root.

When a proxy PDB is synchronized with the application changes in the master application root, it propagates the changes to its referenced application root replica. After the application root replica is synchronized, application PDBs that are plugged into the application root replica can synchronize with the replica and in this way receive the changes.

The following figure shows a configuration that synchronizes an application root replica using a proxy PDB.

Figure 17-3 Synchronizing an Application Root Replica with a Proxy PDB

Description of "Figure 17-3 Synchronizing an Application Root Replica with a Proxy PDB"

In addition, when an application root replica is configured and has its own application PDBs, a query that includes the CONTAINERS clause in the master application root can return data from the current application container and from the application container with the application root replica. The query can show results from the application root replica and from any open application PDBs plugged into the replica.

When multiple application containers run the same application, the application in the application containers can be kept synchronized using proxy PDBs.

1. Create the application container with the master application root by using a CREATE PLUGGABLE DATABASE statement.
   Install the application in the application container now or later.
2. Create the application container with the application root replica in one of the following ways:
   • Create an empty application container using any supported method.
   • Clone the master application root.
   If the port of the listener used by the application root replica is not 1521, then a PORT clause is required during creation. If the host of the application root replica is different from the host of the master application root, then a HOST clause is required during creation.
   This application root replica will be referenced by the proxy PDB.
3. In the master application root, create a proxy PDB that references the application root replica that you created in the previous step.
4. Open and synchronize the proxy PDB.
   When the proxy PDB is synchronized, it propagates the changes in the master application root to the application root replica.
5. Optional: In the master application root, modify the application by installing, upgrading, or patching it.
6. Optional: Synchronize the proxy PDB with the application changes in the master application root by running the ALTER PLUGGABLE DATABASE APPLICATION statement with the SYNC clause.
   When the proxy PDB is synchronized, it propagates the changes in the master application root to the application root replica.

You issue ALTER PLUGGABLE DATABASE APPLICATION statements to uninstall an application from the application root.

You uninstall the application from the application root only, and application PDBs that synchronize with the application uninstall the application automatically. The uninstall operation can be done with one or more of the following: scripts, SQL statements, and graphical user interface tools.

You must indicate the start of the uninstallation with an ALTER PLUGGABLE DATABASE APPLICATION BEGIN UNINSTALL statement and the end of the uninstallation with an ALTER PLUGGABLE DATABASE APPLICATION END UNINSTALL statement. Each uninstallation must be associated with an application name and version number, which are specified in the ALTER PLUGGABLE DATABASE APPLICATION statements.

Uninstalling an application does not remove the application from the data dictionary. It marks the application as UNINSTALLED so that upgrade, patch, and uninstall of the application is disallowed.

Destructive changes to application objects are allowed during application uninstallation. Applications running in an application PDB continue to function during uninstallation and after the application is uninstalled from the application root. The application can continue to function in the application PDB because the ALTER PLUGGABLE DATABASE APPLICATION BEGIN UNINSTALL statement creates a clone of the application root called an application root clone. An application root clone serves as a metadata repository for old versions of application objects, so that application PDBs that have not been synchronized with latest version of the application can continue to function. Because the clone is created while the application PDB is open, local undo must be configured at the CDB level before an application can be uninstalled.

To uninstall an application in from application container, run the ALTER PLUGGABLE DATABASE APPLICATION BEGIN UNINSTALL statement to begin the uninstallation and the ALTER PLUGGABLE DATABASE APPLICATION END UNINSTALL statement to end it. The application uninstalled from the application PDBs that synchronize with the application in the application root.

1. In SQL*Plus, ensure that the current container is the application root.
2. Run the ALTER PLUGGABLE DATABASE APPLICATION BEGIN UNINSTALL statement in the following form:
   ALTER PLUGGABLE DATABASE APPLICATION application_name BEGIN UNINSTALL;
   For example, run the following statement if the application_name is salesapp:
   ALTER PLUGGABLE DATABASE APPLICATION salesapp BEGIN UNINSTALL;
3. Uninstall the application using scripts, SQL statements, or graphical user interface tools.
4. Run the ALTER PLUGGABLE DATABASE APPLICATION END UNINSTALL statement in the following form:
   ALTER PLUGGABLE DATABASE APPLICATION application_name END UNINSTALL;
   For example, run the following statement if the application_name is salesapp:
   ALTER PLUGGABLE DATABASE APPLICATION salesapp END UNINSTALL;

   Note:

   Ensure that the application_name matches in the ALTER PLUGGABLE DATABASE APPLICATION BEGIN UNINSTALL statement and theALTER PLUGGABLE DATABASE APPLICATION END UNINSTALL statement.
5. Synchronize all of the application PDBs that must uninstall the application by issuing an ALTER PLUGGABLE DATABASE APPLICATION statement with the SYNC clause.

Create application common objects by issuing a CREATE statement when the current container is the application root and specifying the SHARING clause.

You can specify the sharing attribute by including the SHARING clause in the CREATE statement or by setting the DEFAULT_SHARING initialization parameter in the application root. When you set the DEFAULT_SHARING initialization parameter, the setting is the default sharing attribute for all database objects of a supported type created in the application root. However, when a SHARING clause is included in a CREATE statement, its setting overrides the setting for the DEFAULT_SHARING initialization parameter.

You can specify one of the following for the sharing attribute:

• METADATA: A metadata link shares the database object’s metadata, but its data is unique to each container. These database objects are referred to as metadata-linked application common objects. This setting is the default.

• DATA: A data link shares the database object, and its data is the same for all containers in the application container. Its data is stored only in the application root. These database objects are referred to as data-linked application common objects.

• EXTENDED DATA: An extended data link shares the database object, and its data in the application root is the same for all containers in the application container. However, each application PDB in the application container can store data that is unique to the application PDB. For this type of database object, data is stored in the application root and, optionally, in each application PDB. These database objects are referred to as extended data-linked application common objects.

• NONE: The database object is not shared.

For most types of application common objects, the only valid settings for the SHARING clause are METADATA and NONE. The following types of application common objects allow additional settings for the SHARING clause:

• For tables (excluding object tables), the SHARING clause can be set to METADATA, DATA, EXTENDED DATA, or NONE. For object tables, only METADATA or NONE is valid.

• For views (excluding object views), the SHARING clause can be set to METADATA, DATA, EXTENDED DATA, or NONE. For object views, only METADATA or NONE is valid.

• For sequences, the SHARING clause can be set to METADATA, DATA, or NONE.

  With a metadata-linked sequence, each application PDB has its own sequence. When the metadata-linked sequence is incremented using the NEXTVAL pseudocolumn in one application PDB, it does not affect the value of the sequence in the other application PDBs in the application container.

  With a data-linked sequence, each application PDB shares the same sequence in the application root. When the metadata-linked sequence is incremented using the NEXTVAL pseudocolumn in one application PDB, all other application PDBs in the same application container also see the change.

Application common objects can be created or changed only as part of an application installation, upgrade, or patch. An application PDB applies changes to application common objects when it synchronizes with the application that made the changes. If an application PDB is closed when an application common object is created, dropped, or modified, then the appropriate changes are applied in the application PDB when it is opened and synchronized with the application.

The names of application common objects must not conflict with those of local database objects in any of the application PDBs that belong to the application root or Oracle-supplied common objects in the CDB root. If a newly opened application PDB contains a local database object whose name conflicts with that of an application common object, then the application PDB is opened in RESTRICTED mode. In this case, you must resolve the naming conflict before the application PDB can be opened in normal mode.

For metadata-linked application common objects, the metadata for the object is stored once in the application root.

A metadata link in each application PDB that belongs to the application root enables the application PDBs to share the metadata for the object, including the object name and structure. The data for the object is unique to each container, including the application root and each application PDB that belongs to the application root.

Data definition language (DDL) operations on a metadata-linked application common object can be run in the application root only as part of an application installation, upgrade, or patch. However, the data can be modified in an application PDB using normal data manipulation language (DML) operations.

For example, consider a company with several regional offices. The company wants the structure of the information about employees to be consistent, but each office has different employees. If this company has a human resources application in an application container, it can create a separate application PDB for each regional office and use a metadata-linked table to store employee information. The data structure of the table, such as the columns, is the same in the application PDB for each regional office, but the employee data is different.

Another example might involve a company that builds and maintains a sales application that is used by several different businesses. Each business uses the same sales application, but the data for each business is different. For example, each business has different customers and therefore different customer data. To ensure that each client uses the same data structure for its application, the company might create an application container with metadata-linked application common objects. Each business that uses the sales application has its own application PDB, and the data structure is the same in each application PDB, but the data is different.

For data-linked application common objects, both the metadata and the data for the object is stored once in the application root. A data link in each application PDB that belongs to the application root enables the application PDBs to share the metadata and data of the object.

DDL operations on a data-linked application common object can be run in the application root only as part of an application installation, upgrade, or patch. In addition, the data can be modified using normal DML operations only in the application root. The data cannot be modified in application PDBs.

For example, consider a company with several regional offices. The company wants the information about the products they sell, such as the product names and descriptions, to be consistent at all of the regional offices. If this company has a sales application in an application container, then it can create a separate application PDB for each regional office and use a data-linked table to store product information. Each application PDB can query the product information, and the product information is consistent at each regional office.

Data-linked application common objects are also useful for data that is standard and does not change. For example, a table that stores the postal codes for a country might be a data-linked application common object in an application container. All of the application PDBs access the same postal code data in the application root.

For an extended data-linked object, each application PDB can create its own data while sharing the common data in the application root. Only data stored in the application root is common for all application PDBs.

DDL operations on an extended data-linked application common object can be run in the application root only as part of an application installation, upgrade, or patch. However, the data can be modified in the application root or in an application PDB using normal DML operations.

For example, a sales application in an application container might support several application PDBs, and all of the application PDBs need the postal codes in the United States for shipping purposes. In this case the postal codes can be stored in the application root so that all of the application PDBs can access it. However, one application PDB also makes sales in Canada, and this application PDB requires the postal codes for the United States and Canada. This one application PDB can store the postal codes for Canada in an extended data-linked object in the application PDB instead of in the application root.

You can issue DML on metadata-linked application objects as normal.

For data-linked application objects, issue DML as normal in the application root. For extended data-linked application objects, issue DML as normal in the application root and in application PDBs.

The map object is the partitioned table.

The names of the partitions in the map table match the names of the application PDBs in the application container. The metadata-linked object is not physically partitioned at the table level, but it can be queried using the partitioning strategy used by the container map.

To associate the map table with the metadata-linked table, specify the map table in ALTER PLUGGABLE DATABASE ... CONTAINER_MAP while connected to the application root. You can create no more than one container map in an application container. You cannot create container maps in the CDB root.

Starting in Oracle Database 18c, for a CONTAINERS() query to use a map, the partitioning column in the map table does not need to match a column in the metadata-linked table. Assume that the table sh.sales is enabled for the container map pdb_map_tbl, and cname is the partitioning column for the map table. Even though sh.sales does not include a cname column, the map table routes the following query to the appropriate PDB: SELECT * FROM CONTAINERS(sh.sales) WHERE cname = 'US' ORDER BY time_id.

This example uses a container map to route queries to PDBs that store data for a geographical region.

The following illustration of an application root shows a map object, a metadata-linked table, and a query on the metadata-linked table. The query is executed in the appropriate application PDB.

Figure 17-4 Container Map

Description of "Figure 17-4 Container Map"

The illustration shows an application container with three application PDBs named AMER, EURO, and ASIA. The PDBs store data for the corresponding regions. A metadata-linked table named oe.cmtb stores information for an application. This table has a COUNTRY column. For this partitioning strategy, partition by list is used to create a map object that creates a partition for each region. The country value, which is GERMANY in the query shown in the illustration, determines the region, which is EURO.

This example uses a container map to route queries to PDBs that store data for a particular department.

Consider another example that uses a range-partitioned table for the map object. The following SQL statement creates the map object in the application root:

CREATE TABLE app_con_admin.conmap (
   department_id NUMBER NOT NULL)
PARTITION BY RANGE (department_id) (
PARTITION apppdb1 VALUES LESS THAN (100),
PARTITION apppdb2 VALUES LESS THAN (200),
PARTITION apppdb3 VALUES LESS THAN (300));

ALTER PLUGGABLE DATABASE SET CONTAINER_MAP='app_con_admin.conmap';

SELECT employee_id 
FROM   CONTAINERS(hr.employees) 
WHERE  department_id = 10 
AND    CON_ID IN (44); 

SELECT employee_id 
FROM   hr.employees 
WHERE  department_id = 10;

As shown in the first query with the CONTAINERS clause, when the query only pertains to a single application PDB, the query must specify the container ID of this application PDB in the WHERE clause. This requirement might cause application changes.

The second query uses the container map, replacing the CONTAINERS clause. The second query does not specify the container because the container map directs the query to the correct application PDB. Queries that use container maps are generally more efficient than queries that use the CONTAINERS clause.

The container map must be created by a common user with ALTER DATABASE system privilege. Queries run against an object that is enabled for container map. Query privileges are determined by privileges granted on the object.