Differences

This shows you the differences between two versions of the page.

--- oracle_multitenant_management [2021/11/12 12:05] – andonovj
+++ oracle_multitenant_management [2021/11/13 17:26] (current) – removed andonovj
@@ Line 1: / Line 1: @@
-===== Overview =====
-Application containers are new concept from 12.2 and they allow us to group PDB based on their purpose. Furthermore, we can treat these PDB as one whole in the way that we can partition among them, even if they are remote and thus increase the performance:
-{{:multitenant-application-container.png?600|}}
-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.
-===== Management =====
-The management of an application root container is very similar to the management of a single PDB.
-====Operations====
-The operations of application PDBs and application containers are the same as normal PDBs:
-  * Creation
-  * Deletion
-  * Alteration
-===Creation===
-To create application root container, just create a normal PDB, but AS application root
-<Code:bash|Create application root container>
-CONN / AS SYSDBA
-CREATE PLUGGABLE DATABASE appcon1 AS APPLICATION CONTAINER ADMIN USER app_admin IDENTIFIED BY Password1;
-ALTER PLUGGABLE DATABASE appcon1 OPEN;
-<
-</Code>
-=== Deletion ===
-To delete a application container, use the following command:
-<Code:bash|Drop a application container>
-CONN / AS SYSDBA
-ALTER PLUGGABLE DATABASE appcon1 CLOSE;
-DROP PLUGGABLE DATABASE appcon1 INCLUDING DATAFILES;
-</Code>
-=== Create application PDB ===
-To create an application PDB, we can use the same syntax as normal PDB, just we need to change the CDB :)
-<Code:bash| Create application PDB>
-CONN / AS SYSDBA
-ALTER SESSION SET container = appcon1;
-CREATE PLUGGABLE DATABASE apppdb1 ADMIN USER pdb_admin IDENTIFIED BY Password1;
-ALTER PLUGGABLE DATABASE apppdb1 OPEN;
-</Code>
-=== Drop application PDB ===
-To drop an application PDB, we can use the same command as a normal PDB
-<Code:bash|Drop application PDB>
-CONN / AS SYSDBA
-ALTER PLUGGABLE DATABASE appcon1 CLOSE;
-DROP PLUGGABLE DATABASE appcon1 INCLUDING DATAFILES;
-</Code>
-Application containers and application roots are mostly useful when we install and deal with applications. But what is application in a DB term.
-===== Applications ======
-Within an application container, an application is the named, versioned set of common data and metadata stored in the application root. In this context of an application container, the term “application” means “master application definition.” For example, the application might include definitions of tables, views, and packages.
-For example, you might create multiple sales-related PDBs within one application container, with these PDBs sharing an application that consists of a set of common tables and table definitions. You might store multiple HR-related PDBs within a separate application container, with their own common tables and table definitions.
-{{:cncpt389.png?600|}}
-To have an application, firstly we need to install an application.
-====Installation====
-Firstly we need to create an application PDB, you know already the steps from above so I will make them shorter :)
-=== Install application container and PDB ===
-<Code:bash|Install application PDB>
-#Create application root
-CONN / AS SYSDBA
-CREATE PLUGGABLE DATABASE appcon1 AS APPLICATION CONTAINER ADMIN USER app_admin IDENTIFIED BY Password1;
-ALTER PLUGGABLE DATABASE appcon1 OPEN;
-#Change the CDB to the newly created container
-CONN / AS SYSDBA
-ALTER SESSION SET container = appcon1;
-#Create one or more application PDB (repeat if needed)
-CREATE PLUGGABLE DATABASE apppdb1 ADMIN USER pdb_admin IDENTIFIED BY Password1;
-ALTER PLUGGABLE DATABASE apppdb1 OPEN;
-#Sync them all (repeat if needed)
-ALTER SESSION SET container = apppdb1;
-ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;
-</Code>
-=== Install the application===
-To install an application, we can use the following process:
-<Code:bash|Install application>
-#Change to the application root
-CONN / AS SYSDBA
-ALTER SESSION SET container = appcon1;
-#Install the application
-ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN INSTALL '1.0';
-</Code>
-Now we can start configuring all that is needed for that application:
-<Code:bash|Configure the PDB for the application needs>
-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;
-</Code>
-=== End the installation ===
-To end the installation we simply have to do the following command:
-<Code:bash|End installation>
-ALTER PLUGGABLE DATABASE APPLICATION ref_app END INSTALL;
-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>
-</Code>
-Bare in mind, at this point the PDBs, don't know about that installation, so if we try to see it from their point of view, it doesn't exist:
-=== Check ===
-<Code:bash|Check on PDB level>
--- 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>
-</Code>
-==== Sync ====
-To sync the PDBs with the application container, just execute the following:
-<Code:bash|Sync the PDB with application container>
--- 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>
-</Code>
-===Verify===
-We can now verify that the application is visible from the PDBs
-<Code:bash|Verification>
-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>
-</Code>
-==== Upgrade ====
-The upgrade in that case is the modification of the existing structure, either be: index creation / deletion, new column, etc.
-To upgrade an application, the process is very similiar:
-<Code:Bash|Upgrade application>
-#Connect to the application root
-CONN / AS SYSDBA
-ALTER SESSION SET container = appcon1;
-#Edit the tag
-ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN UPGRADE '1.0' TO '1.1';
-#Do the changes
-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;
-#End upgrade
-ALTER PLUGGABLE DATABASE APPLICATION ref_app END UPGRADE;
-</Code>
-We have to, of course, still sync with the PDBs, otherwise it won't be visible:
-<Code:bash|Sync with the PDBS>
--- 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>
-</Code>
-We can again verify the version as follows:
-<Code:bash|Verification>
-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>
-</Code>
-==== Uninstall ====
-To uninstall an application, we have to:
-  - Uninstall it
-  - Delete all the data
-  - Sync (again :D )
-So let's do it:
-===Uninstallation===
-<Code:bash>
-#Connect to the application root
-CONN / AS SYSDBA
-ALTER SESSION SET container = appcon1;
-#Star the uninstall
-ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN UNINSTALL;
-#Delete all the data
-DROP USER ref_app_user CASCADE;
-DROP TABLESPACE ref_app_ts INCLUDING CONTENTS AND DATAFILES;
-#End the uninstallation
-ALTER PLUGGABLE DATABASE APPLICATION ref_app END UNINSTALL;
-</Code>
-Done, we have uninstalled and deleted the data, but we still have to SYNC the changes:
-===Sync===
-<Code:bash>
--- 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>
-</Code>
-After that, we can verify that the application has been uninstalled:
-=== Verification ===
-<Code:bash>
-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>
-</Code>