Drop Application Root Clones

Drop Application Root Clones

I hope you have checked my previous article on ORA-65011: Pluggable database does not exist.
If not, must check it first. This article is in continues of my previous article. Click here to Check.

There was an open question in my mind,
=> What if I don't require my previous application root versions?
=> What if I already SYNC/upgraded my all PDBs to latest version?
=> How to check how many application versions are there in my application root?
=> How to drop/remove application root clones after synchronizing all PDBs?

Here is the answer I found after few TESTs in my test environment.

MUST NOTE
When we upgrade our application, Oracle Database automatically clones the application root. At the time of upgrade, application PDBs point to the clone. Due to this applications continue to run during the upgrade also.

Here it is recommended to SYNC your application PDB with your application root. If application PDBs are not SYNC with root, PDBs will still point to the clone.
Here Clone PDBs are occupying disk space because those are copy of application root PDB.

Here is the query to find all PDBs & Containers.


SQL> select con_id, name, open_mode, restricted,application_root app_root, application_pdb app_pdb, application_seed app_seed, application_root_con_id app_root_id
from v$containers ;

    CON_ID NAME                      OPEN_MODE  RESTRICTED APP_ROOT APP_PDB  APP_SEED APP_ROOT_ID
---------- ------------------------- ---------- ---------- -------- -------- -------- -----------
         3 ORDERPDB                  READ WRITE NO         NO       NO       NO
         5 F3094217277_21_4          READ WRITE NO         YES      YES      NO                 9
         9 SALESAPPROOT              READ WRITE NO         YES      NO       NO
        10 SALESAPPROOT$SEED         READ WRITE NO         NO       YES      YES                9
        11 SALESAPPDB1               READ WRITE NO         NO       YES      NO                 9
        12 F3094217277_21_1          READ WRITE NO         YES      YES      NO                 9
        13 SALESAPPDB2               READ WRITE NO         NO       YES      NO                 9
        15 F3094217277_21_2          READ WRITE NO         YES      YES      NO                 9
        16 F3094217277_21_3          READ WRITE NO         YES      YES      NO                 9

9 rows selected.
in above output F3094217277_21_1, F3094217277_21_2, F3094217277_21_3, F3094217277_21_4 are my application root clones.
After upgrading my application root, I have SYNC my all PDBS as below,

SQL> alter session set container=SALESAPPDB1;
Session altered.

SQL> alter pluggable database application ONLINESTORE sync;
Pluggable database altered.

SQL>
SQL> alter session set container=SALESAPPDB2;
Session altered.

SQL> alter pluggable database application ONLINESTORE sync;
Pluggable database altered.

SQL>
SQL> alter session set container=SALESAPPROOT$SEED;
Session altered.

SQL> alter pluggable database application ONLINESTORE sync;
Pluggable database altered.

Once you SYNC pluggable databases to latest version than must set the application compatibility version.

Here is the example,

Syntax
ALTER PLUGGABLE DATABASE APPLICATION SET COMPATIBILITY VERSION '';
Example
in my case application name is ONLINESTORE and latest version is 1.4

SQL> ALTER PLUGGABLE DATABASE APPLICATION ONLINESTORE SET COMPATIBILITY VERSION '1.4';
Pluggable database altered.
Once you set the application compatibility version, Oracle will automatically drop all the application root clones which was created by previous application root version upgrades.

So after running above command, Now I can see all cloned PDBs are dropped as below,

SQL> select con_id, name, open_mode, restricted,application_root app_root, application_pdb app_pdb, application_seed app_seed, application_root_con_id app_root_id
from v$containers;

    CON_ID NAME                      OPEN_MODE  RESTRICTED APP_ROOT APP_PDB  APP_SEED APP_ROOT_ID
---------- ------------------------- ---------- ---------- -------- -------- -------- -----------
         3 ORDERPDB                  READ WRITE NO         NO       NO       NO
         9 SALESAPPROOT              READ WRITE NO         YES      NO       NO
        10 SALESAPPROOT$SEED         READ WRITE NO         NO       YES      YES                9
        11 SALESAPPDB1               READ WRITE NO         NO       YES      NO                 9
        13 SALESAPPDB2               READ WRITE NO         NO       YES      NO                 9
       
5 rows selected.

So now, I am able to drop/remove application root clones successfully. Dropping application root clones will save your disk space too hence it is very useful.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert