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
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
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:
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 WARNING
s 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
Hi Mike,
I know, one should not touch internal database elements, so my solution is completely unsupported.
(But I’m still in an 18c test environment).
I simply deleted all WARNING and PENDING rows stored in the underlying table PDB_ALERT$
As far as I can see no issues with that.
Best regards
Werner
😉
Mike
Thank you this was very helpful.
Welcome 🙂