Table of contents
Preamble
In the two first posts we have seen pluggable database upgrade by container move and on how to clone and physically move pluggable databases clone across containers. In this last part let see how application container works.
It is mandatory to use Oracle Managed Files (OMF) when working with application containers or you will end up with unrecoverable error like:
SQL> alter pluggable database application myapp sync; alter pluggable database application myapp sync * ERROR at line 1: ORA-01537: cannot add file '/u01/app/oracle/oradata/ORCL1/myapp/app_tbs01.dbf' - file already part of database |
So to activate OMF I set at root container level (the instance name will be concatenated automatically to parameter value):
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata' scope=both; System altered. |
I do not like that much OMF because you cannot even choose your datafiles name and I rate it a bit messy. I could accept it while working with ASM but for traditional filesystem I like to choose the path and file names. Maybe a little bit old school but for application container I had no choice…
Testing has been done on Red Hat Enterprise Linux Server release 7.9 (Maipo) with Oracle Enterprise Edition 21c (21.6).
Application root creation
The big picture is the following:
So let’s create this application root that will contains my application seed (optional component) and the application containers:
SQL> create pluggable database appcon01
as application container
admin user yjaquier identified by "secure_password";
Pluggable database created.
SQL> alter pluggable database appcon01 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 APPCON01 READ WRITE NO
SQL> set lines 200
SQL> col pdb_name for a10
SQL> select pdb_name,application_root,application_pdb,application_seed,application_clone from dba_pdbs;
PDB_NAME APP APP APP APP
---------- --- --- --- ---
PDB$SEED NO NO NO NO
APPCON01 YES NO NO NO |
Application container creation
Move to your application container enable OMF and create your first application container:
SQL> alter session set container=appcon01;
Session altered.
SQL> create pluggable database app01
admin user pdb_admin identified by "secure_password";
Pluggable database created.
SQL> alter pluggable database app01 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 APPCON01 READ WRITE NO
4 APP01 READ WRITE NO |
Application installation
Install your application in application root:
SQL> alter session set container=appcon01;
Session altered.
SQL> alter pluggable database application myapp begin install '1.0';
Pluggable database altered.
SQL> create tablespace app_tbs datafile size 10m;
Tablespace created.
SQL> create user app_user identified by "secure_password"
container = all
default tablespace app_tbs
quota unlimited on app_tbs;
User created.
SQL> grant connect,resource to app_user;
Grant succeeded.
SQL> show parameter default_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
default_sharing string METADATA
SQL> create table app_user.test01 sharing=extended data
(
id integer,
first_name varchar2(20) not null,
last_name varchar2(20) not null,
constraint test01_pk primary key(id));
Table created.
SQL> insert into app_user.test01 values(1,'Yannick','Jaquier');
1 row created.
SQL> commit;
Commit complete.
SQL> alter pluggable database application myapp end install;
Pluggable database altered. |
Remark:
Notice the new sharing keyword to instruct if your table and its data will be shared with all containers. There are multiple options for this sharing clause. Refer to official document, here I have chosen to let application containers manage their own data keeping a golden image of figures in application root.
Something weird happened to me: if you try to connect with object owner using something like: connect app_user/”secure_password”@//server01.domain.com:1531/appcon01. Then the create table statement will fail for:
ERROR at line 1: ORA-65021: illegal use of SHARING clause |
Don’t really know if it’s a bug or an expected behavior…
There are few DBA views to control what has been created:
SQL> set lines 200 SQL> col app_name for a15 SQL> select app_name,app_version,app_status from dba_applications where app_name='MYAPP'; APP_NAME APP_VERSION APP_STATUS --------------- ------------------------------ ------------ MYAPP 1.0 NORMAL SQL> col app_version_comment for a10 SQL> select * from dba_app_versions where app_name='MYAPP'; APP_NAME APP_VERSION APP_VERSIO APP_VERSION_CHECKSUM APP_ROOT_CLONE_NAME --------------- ------------------------------ ---------- -------------------- ---------------------------------------------------------------- MYAPP 1.0 0 SQL> select * from dba_app_statements where app_name='MYAPP'; . . . |
Now if I connect to the first applicative container of my application root:
SQL> alter session set container=app01;
Session altered.
SQL> desc app_user.test01
ERROR:
ORA-04043: object app_user.test01 does not exist
SQL> alter pluggable database application myapp sync;
Pluggable database altered.
SQL> desc app_user.test01
Name Null? Type
-------------------------------------- -------- ---------------------------
ID NOT NULL NUMBER(38)
FIRST_NAME NOT NULL VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(20)
SQL> select * from app_user.test01;
ID FIRST_NAME LAST_NAME
---------- -------------------- --------------------
1 Yannick Jaquier |
I can insert a row in one of my applicative containers without disturbing the application container (obviously in the case of a primary key you must be cautious, DBA_APP_ERRORS can help):
SQL> insert into app_user.test01 values(10,'Larry','Ellison');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from app_user.test01;
ID FIRST_NAME LAST_NAME
---------- -------------------- --------------------
1 Yannick Jaquier
10 Larry Ellison
SQL> alter session set container=appcon01;
Session altered.
SQL> select * from app_user.test01;
ID FIRST_NAME LAST_NAME
---------- -------------------- --------------------
1 Yannick Jaquier |
There is a powerful CONTAINERS function to see figures of all containers from application root:
SQL> alter session set container=appcon01;
Session altered.
SQL> set lines 200
SQL> col con$name for a15
SQL> select con$name, a.*
from containers(app_user.test01) a
order by con_id,id;
CON$NAME ID FIRST_NAME LAST_NAME FULL_NAME CON_ID
--------------- ---------- -------------------- -------------------- ---------------------------------------- ----------
APPCON01 1 Yannick Jaquier Yannick Jaquier 3
APP01 1 Yannick Jaquier Yannick Jaquier 4
APP01 10 Larry Ellison 4
APP02 1 Yannick Jaquier Yannick Jaquier 5 |
Application upgrade
I create a second application container in my application root:
SQL> alter session set container=appcon01;
Session altered.
SQL> create pluggable database app02 admin user pdb_admin identified by "secure_password";
Pluggable database created.
SQL> alter pluggable database app02 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 APPCON01 READ WRITE NO
4 APP01 READ WRITE NO
5 APP02 READ WRITE NO
SQL> alter session set container=app02;
Session altered.
SQL> desc app_user.test01
ERROR:
ORA-04043: object app_user.test01 does not exist
SQL> alter pluggable database application myapp sync;
Pluggable database altered.
SQL> desc app_user.test01
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
FIRST_NAME NOT NULL VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(20)
SQL> select * from app_user.test01;
ID FIRST_NAME LAST_NAME
---------- -------------------- --------------------
1 Yannick Jaquier |
I start application upgrade from 1.0 to 2.0. The upgrade will be to add a FULL_NAME column to my test table:
SQL> alter session set container=appcon01; Session altered. SQL> alter pluggable database application myapp begin upgrade '1.0' to '2.0' comment 'Release 2.0'; Pluggable database altered. SQL> alter table app_user.test01 add full_name varchar2(40); Table altered. SQL> alter pluggable database application myapp end upgrade to '2.0'; Pluggable database altered. |
I now have two versions of my application:
SQL> set lines 200 SQL> col app_name for a15 SQL> select app_name,app_version,app_status from dba_applications where app_name='MYAPP'; APP_NAME APP_VERSION APP_STATUS --------------- ------------------------------ ------------ MYAPP 2.0 NORMAL SQL> col app_version_comment for a15 SQL> select * from dba_app_versions where app_name='MYAPP'; APP_NAME APP_VERSION APP_VERSION_COM APP_VERSION_CHECKSUM APP_ROOT_CLONE_NAME --------------- ------------------------------ --------------- -------------------- ---------------------------------------------------------------- MYAPP 1.0 0 F3923760660_3_1 MYAPP 2.0 Release 2.0 0 |
I can synchronize application container by application container to point on latest application release:
SQL> alter session set container=app01;
Session altered.
SQL> desc app_user.test01
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ID NOT NULL NUMBER(38)
FIRST_NAME NOT NULL VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(20)
SQL> alter pluggable database application myapp sync to '2.0';
Pluggable database altered.
SQL> desc app_user.test01
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ID NOT NULL NUMBER(38)
FIRST_NAME NOT NULL VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(20)
FULL_NAME VARCHAR2(40)
SQL> select * from app_user.test01;
ID FIRST_NAME LAST_NAME FULL_NAME
---------- -------------------- -------------------- ----------------------------------------
1 Yannick Jaquier
10 Larry Ellison |
You can control application release number of your applicative container with:
SQL> col pdb_name for a10
SQL> select pdb_name, app_name, app_version, app_status from dba_pdbs a, dba_app_pdb_status b
where a.dbid = b.con_uid
PDB_NAME APP_NAME APP_VERSION APP_STATUS
---------- --------------- ------------------------------ ------------
APP01 MYAPP 2.0 NORMAL
APP02 MYAPP 1.0 NORMAL |
Application patch
Let’s patch our application to fill the FULL_NAME column of my test table:
SQL> alter session set container=appcon01;
Session altered.
SQL> alter pluggable database application myapp begin patch 201 minimum version '2.0' comment 'To fill FULL_NAME column of TEST01';
Pluggable database altered.
SQL> update app_user.test01 set full_name=first_name||' '||last_name where full_name is null;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from app_user.test01;
ID FIRST_NAME LAST_NAME FULL_NAME
---------- -------------------- -------------------- ----------------------------------------
1 Yannick Jaquier Yannick Jaquier
SQL> alter pluggable database application myapp end patch 201;
Pluggable database altered.
SQL> set lines 200
SQL> col patch_comment for a35
SQL> select * from dba_app_patches;
APP_NAME PATCH_NUMBER PATCH_MIN_VERSION PATCH_STAT PATCH_COMMENT PATCH_CHECKSUM
--------------- ------------ ------------------------------ ---------- ----------------------------------- --------------
MYAPP 201 2.0 INSTALLED To fill FULL_NAME column of TEST01 0 |
I can now patch application that have minimum release 2.0 with this patch:
SQL> alter session set container=app01;
Session altered.
SQL> select * from dba_app_patches;
no rows selected
SQL> alter pluggable database application myapp sync to patch 201;
Pluggable database altered.
SQL> select * from dba_app_patches;
APP_NAME PATCH_NUMBER PATCH_MIN_VERSION PATCH_STAT PATCH_COMMENT PATCH_CHECKSUM
--------------- ------------ ------------------------------ ---------- ----------------------------------- --------------
MYAPP 201 2.0 INSTALLED To fill FULL_NAME column of TEST01 0
SQL> select * from app_user.test01;
ID FIRST_NAME LAST_NAME FULL_NAME
---------- -------------------- -------------------- ----------------------------------------
10 Larry Ellison
1 Yannick Jaquier Yannick Jaquier |
Of course only common figures are concerned by the path. The local figures remain unaffected…
Application seed creation
This application seed that is an application golden image must be created inside an application root and from official documentation:
SQL> alter session set container=appcon01; Session altered. SQL> create pluggable database as seed admin user pdb_admin identified by "secure_password"; Pluggable database created. SQL> alter pluggable database appcon01$seed open; Pluggable database altered. SQL> set lines 200 SQL> col pdb_name for a15 SQL> select pdb_name,application_root,application_pdb,application_seed,application_clone from dba_pdbs order by con_id; PDB_NAME APP APP APP APP --------------- --- --- --- --- APPCON01 YES NO NO NO APP01 NO YES NO NO APP02 NO YES NO NO APPCON01$SEED NO YES YES NO SQL> col name for a15 SQL> select name,open_mode,total_size/(1024*1024) as "Size MB",application_root,application_pdb,application_seed,application_root_clone from v$pdbs order by dbid; NAME OPEN_MODE Size MB APP APP APP APP --------------- ---------- ---------- --- --- --- --- APPCON01$SEED READ ONLY 870 NO YES YES NO APP02 READ WRITE 930 NO YES NO NO APP01 READ WRITE 950 NO YES NO NO APPCON01 READ WRITE 4730 YES NO NO NO |
Once the application seed is created sync all or a list of application inside and open it read only:
SQL> alter session set container=appcon01$seed;
Session altered.
SQL> alter pluggable database application all sync;
Pluggable database altered.
SQL> alter pluggable database close immediate;
Pluggable database altered.
SQL> alter pluggable database open read only;
Pluggable database altered.
SQL> alter session set container=appcon01;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 APPCON01 READ WRITE NO
4 APP01 READ WRITE NO
5 APP02 READ WRITE NO
6 APPCON01$SEED READ ONLY NO |
References
- Filename parameters for Create Pluggable Database with OMF and non-OMF (Doc ID 2095929.1)
- Multitenant : Application Containers in Oracle Database 12c Release 2 (12.2)
