The EBS Online Patching Database Compliance Checker, ADZDDBCC.sql, currently has 50 sections, section 1 through section 47, with sections 5.0 and 5.1, and sections 91 and 92. New sections are added as Oracle finds violations that can be checked reliably. There are some violations that are very difficult to check, such as user defined types, because there are almost unlimited variations of user defined types.
Oracle states in the ADZDDBCC.lst:
“Please do not attempt to correct violations in objects owned by Oracle.
P1 violations must be fixed before using the system or object.
P2 violations might not pose an immediate problem, but may cause later issues if the affected objects are changed in a future online patch.
P3 violations are minor issues that can be deferred or ignored”
There are two different groups of violations, priority and compliance: P1, P2 and P3 priority violations and Minimal, Full and Internal compliance violations. You should fix all P1 and P2 compliance violations and all minimal priority violations. I usually fix both the minimal and full compliance violations, because in the future, as your development team better understands how to implement custom code, they may chose to create online patches for custom code.
“Minimal Compliance [minimal]
These checks represent the minimum requirement for correct operation of E-Business Suite Release 12.2. Do not attempt to operate the system if there are P1 minimal compliance violations. Custom code should pass the minimal compliance checks before being used in a Release 12.2 system.
Full Compliance [full]
These checks indicate whether an object can be patched using Online Patching. Objects which do not meet full compliance may have limitations in how they can be patched, or may need to be patched using downtime patching. Full compliance also requires that all minimal compliance checks are passed. Custom code that will only be patched using downtime patching does not need to meet the full compliance level.”
“This report also implements a number of internal checks which verify that the upgrade and Online Patching Enablement process executed correctly. Violations of internal checks indicate upgrade failure and should be reviewed with support if the cause is not understood.”
The following is the header from section-15 from the ADZDDBCC.lst output from AADZDDBCC.sql. Even though this is a P3 priority violation, I usually fix this because of “clutter and confusion”.
**********************************************************************
DOC> SECTION-15 [minimal]
DOC> **********************************************************************
DOC> “Synonym must point to an object.”
DOC>
DOC> – P3: broken synonyms cause clutter and confusion.
DOC> – Fix: Correct or drop these synonyms.
DOC>#
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
However, there can be thousands of these broken synonyms. Most violations can be fixed with minor changes. However, because there are so many of these violations I chose to write SQL generating SQL that can be run to fix all the violations in one script. The following SQL script is one I created to fix this section:
select ‘drop synonym ‘||syn.owner||’.’||syn.synonym_name|| ‘;’
from dba_synonyms syn
where syn.table_owner in
( select oracle_username from fnd_oracle_userid
where read_only_flag in (‘A’, ‘B’, ‘C’, ‘E’, ‘U’) )
and not exists
( select obj.object_name
from dba_objects obj
where obj.owner = syn.table_owner
and obj.object_name = syn.table_name )
and not exists
( select 1
from system.fnd_oracle_userid fou
, fnd_product_installations fpi
, ad_obsolete_objects aoo
where fpi.application_id = aoo.application_id
and fou.oracle_id = fpi.oracle_id
and fou.oracle_username = syn.owner
and ((aoo.object_name = syn.synonym_name and aoo.object_type = ‘SYNONYM’) or
(aoo.object_name = syn.table_name and aoo.object_type = ‘TABLE’)) )
order by 1, 2
I also create a script that will create all the synonyms, just in case:
select ‘create synonym ‘ ||syn.owner|| ‘.’ ||syn.synonym_name|| ‘ for ‘ ||syn.table_owner|| ‘.’ ||syn.table_name || ‘;’
from dba_synonyms syn
where syn.table_owner in
( select oracle_username from fnd_oracle_userid
where read_only_flag in (‘A’, ‘B’, ‘C’, ‘E’, ‘U’) )
and not exists
( select obj.object_name
from dba_objects obj
where obj.owner = syn.table_owner
and obj.object_name = syn.table_name )
and not exists
( select 1
from system.fnd_oracle_userid fou
, fnd_product_installations fpi
, ad_obsolete_objects aoo
where fpi.application_id = aoo.application_id
and fou.oracle_id = fpi.oracle_id
and fou.oracle_username = syn.owner
and ((aoo.object_name = syn.synonym_name and aoo.object_type = ‘SYNONYM’) or
(aoo.object_name = syn.table_name and aoo.object_type = ‘TABLE’)) )
order by syn.owner, syn.synonym_name;
Section-26 contains objects that are mostly Oracle seeded objects, so no action is necessary. However, some objects are owned by custom schemas and these typically need to be fixed by your development team.
**********************************************************************
SECTION-26 [minimal]
**********************************************************************
“Query/DML statements must access tables via the APPS table synonym.”
– P2: These objects may operate incorrectly after the referenced table has been patched.
Fix: Change the object to reference tables via the APPS table synonym.