Purging of PDB_PLUG_IN_VIOLATIONS in Oracle 18c

PDB_PLUG_IN_VIOLATIONS is a strange view in the Oracle database universe. It does not follow Oracle naming conventions – and it has too many underscores in its name. Furthermore I get always annoyed by the fact that it doesn’t get purged automatically. In addition it accumulates a massive number of useless information over time. But in Oracle Database 18c there’s a least some relief: A new call gets introduced which allows purging of PDB_PLUG_IN_VIOLATIONS in Oracle 18c.

PDB_PLUG_IN_VIOLATIONS

As others have written a lot already about PDB_PLUG_IN_VIOLATIONS I won’t repeat it but rather share the links with you:

• https://martincarstenbach.wordpress.com/2016/06/29/tales-from-the-field-potential-reasons-for-pdb-plug-in-violations-part-1/
• https://martincarstenbach.wordpress.com/2016/07/14/tales-from-the-field-potential-reasons-for-pdb-plug-in-violations-part-2/
• https://blog.dbi-services.com/12c-multitenant-internals-pdb_plug_in_violations/
• http://grid-it.nl/plug-in-violations-and-datapatch/

And of course you’ll find information as well in the official Oracle documentation:

• PDB_PLUG_IN_VIOLATIONS

PDB_PLUG_IN_VIOLATIONS displays information about incompatibilities between a PDB and the CDB to which it belongs. The important fields are TYPE (“ERROR” or “WARNING“) and STATUS (“PENDING“,”RESOLVED” or “IGNORE“). And due to the column format I highly advice that you define either column format commands or use the SQL Developer instead.

What will you find in PDB_PLUG_IN_VIOLATIONS

The view gets populated whenever you plugin a PDB or when you do a plugin compatibility check. This sounds promising but you’ll find also a lot of not so helpful information over time. Have a quick look on my previous blog post:

• Unplug an Oracle 12.2.0.1 PDB and plug it into Oracle 18c in the Cloud

There you’ll find the following warnings in PDB_PLUG_IN_VIOLATIONS after the upgrade:

column message format a50
column status format a9
column type format a9
column con_id format 9

select con_id, type, message, status
  from PDB_PLUG_IN_VIOLATIONS
 where status<>'RESOLVED'
 order by time;

CON_ID TYPE	 MESSAGE					    STATUS
------ --------- -------------------------------------------------- ---------
     4 WARNING	 Tablespace SYSTEM is not encrypted. Oracle Cloud m PENDING
		 andates all tablespaces should be encrypted.

     4 WARNING	 Tablespace SYSAUX is not encrypted. Oracle Cloud m PENDING
		 andates all tablespaces should be encrypted.

     6 WARNING	 Database option RAC mismatch: PDB installed versio PENDING
		 n 18.0.0.0.0. CDB installed version NULL.

     6 WARNING	 Database option APS mismatch: PDB installed versio PENDING
		 n NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option CATJAVA mismatch: PDB installed ve PENDING
		 rsion NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option CONTEXT mismatch: PDB installed ve PENDING
		 rsion NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option DV mismatch: PDB installed version PENDING
		  NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option JAVAVM mismatch: PDB installed ver PENDING
		 sion NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option OLS mismatch: PDB installed versio PENDING
		 n NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option ORDIM mismatch: PDB installed vers PENDING
		 ion NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option SDO mismatch: PDB installed versio PENDING
		 n NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option XML mismatch: PDB installed versio PENDING
		 n NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option XOQ mismatch: PDB installed versio PENDING
		 n NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Tablespace SYSTEM is not encrypted. Oracle Cloud m PENDING
		 andates all tablespaces should be encrypted.

     6 WARNING	 Tablespace SYSAUX is not encrypted. Oracle Cloud m PENDING
		 andates all tablespaces should be encrypted.


15 rows selected.

There are bugs to prevent the view from getting overloaded with useless information:

• Bug 16192980 - NO SIMPLE WAY TO CLEAN ROWS FROM PDB_PLUG_IN_VIOLATIONS AFTER DBMS_PDB CALL
• Bug 17262059 - PDB_PLUG_IN_VIOLATIONS DISPLAYS USELESS INFORMATION AND DOES NOT GET PURGED

And with Oracle 18c at least a PURGE mechanism has been implemented.

Purging of PDB_PLUG_IN_VIOLATIONS in Oracle 18c

These fixes for the bug/s let you – at least – purge parts of the content of PDB_PLUG_IN_VIOLATIONS in Oracle Database 18c. As far as I see it is currently not documented.

 exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS( pdb_name => 'PDB12201' );

But even after the execution you will find out that RESOLVED entries only get purged. All content with STATUS “PENDING” remains in PDB_PLUG_IN_VIOLATIONS – no matter if the entries make sense or not.

In my above example 9 RESOLVED entries (not displayed above) get removed. And only one WARNING (about a RAC Option mismatch) disappears – but only after I restart the PDB:

CON_ID TYPE	 MESSAGE					    STATUS
------ --------- -------------------------------------------------- ---------
     6 WARNING	 Database option RAC mismatch: PDB installed versio PENDING
		 n 18.0.0.0.0. CDB installed version NULL.

All other 14 WARNINGs remain valid. I don’t picture the content of the view with >4000 PDBs in an highly active environment.

Summary

The new procedure DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS in Oracle Database 18c will only purge RESOLVED entries. This may help to shrink the content of PDB_PLUG_IN_VIOLATIONS but doesn’t solve all issues with the view.

–Mike