Multitenant : Application container database Maintenance on 12.2 version

Multitenant : Application container database Maintenance on 12.2 version

Scenario Preview : We have created application container database and 2 pluggable database already created. We have to perform modification or add application tables on application container database. So all modification were reflected in plugable database.

Application Container database :  HR_APP

Application Pluggable database :  HR_PDB1

                                                       :  HR_PDB2

SQL> conn sys/system123@hr_ac as sysdba
Connected.
SQL> column app_name format a15
SQL> column app_version format a10
SQL>column app_status format a15
SQL> SELECT APP_NAME, APP_VERSION, APP_STATUS FROM DBA_APPLICATIONS WHERE APP_IMPLICIT='N';

APP_NAME        APP_VERSIO APP_STATUS
--------------- ---------- ---------------
HR_APP          1.0        NORMAL

• Begin upgrade at application container database level.

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app BEGIN UPGRADE '1.0' TO '2.0';

Pluggable database altered.

• Make changes at script either modify or add tables on it.

SQL> host vi /home/oracle/scripts/hr_app_v1.1.sql

SQL> @/home/oracle/scripts/hr_app_v1.1.sql

Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.

• End Upgrade after completion of script at application container database.

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app END UPGRADE TO '2.0';

Pluggable database altered.

SQL> ALTER SESSION SET CONTAINER= hr_pdb1;

Session altered.

• SYNC application container database with multiple pluggable database.

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;

Pluggable database altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

oracle@srv4:~> sqlplus sys/system123@hr_ac as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 26 15:52:02 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

• Connecting with root container and check status below of PDB and Container application PDB database.

SQL> conn / as sysdba
Connected.
SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST1                          READ WRITE NO
         4 PLM1                           READ WRITE NO
         5 PDBREF                         READ ONLY  NO
         6 PROXYPDB                       READ WRITE NO
         7 HR_AC                          READ WRITE NO
         8 HR_PDB1                        READ WRITE NO
         9 HR_PDB2                        READ WRITE NO
        10 F2441155579_21_1               READ ONLY  NO
SQL> col name format a20
SQL> SELECT CON_ID, NAME, APPLICATION_ROOT, OPEN_MODE, APPLICATION_ROOT_CON_ID FROM V$PDBS ORDER BY 3,1;

    CON_ID NAME                 APP OPEN_MODE  APPLICATION_ROOT_CON_ID
---------- -------------------- --- ---------- -----------------------
         2 PDB$SEED             NO  READ ONLY
         3 TEST1                NO  READ WRITE
         4 PLM1                 NO  READ WRITE
         5 PDBREF               NO  READ ONLY
         6 PROXYPDB             NO  READ WRITE
         8 HR_PDB1              NO  READ WRITE                       7
         9 HR_PDB2              NO  READ WRITE                       7
         7 HR_AC                YES READ WRITE
        10 F2441155579_21_1     YES READ ONLY                        7

9 rows selected.

SQL> col name format a80
SQL> SELECT NAME FROM V$DATAFILE WHERE CON_ID=7;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRCDB/STDRAC/ADB299969CDB0AB5E0534A38A8C0071F/datafile/
o1_mf_system_hn9vs1qd_.dbf

/u01/app/oracle/oradata/SMRCDB/STDRAC/ADB299969CDB0AB5E0534A38A8C0071F/datafile/
o1_mf_sysaux_hn9vs1tf_.dbf

/u01/app/oracle/oradata/SMRCDB/STDRAC/ADB299969CDB0AB5E0534A38A8C0071F/datafile/
o1_mf_undotbs1_hn9vs1tf_.dbf

/u01/app/oracle/oradata/SMRCDB/STDRAC/ADB299969CDB0AB5E0534A38A8C0071F/datafile/
o1_mf_hr_tbs_hn9zsfl1_.dbf

NAME
--------------------------------------------------------------------------------

SQL> ALTER SESSION SET CONTAINER=HR_AC;

Session altered.

SQL> column app_name format a15
SQL>column app_version format a5
SQL> column app_status format a15
SQL> SELECT APP_NAME, APP_VERSION, APP_STATUS FROM DBA_APPLICATIONS WHERE APP_IMPLICIT='N';

APP_NAME        APP_V APP_STATUS
--------------- ----- ---------------
HR_APP          2.0   NORMAL