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
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
