Importance of Database and System Audits in PeopleSoft
PeopleSoft provides Audit reports which includes DDDAUDIT, SYSAUDIT, Alter Audit to check the integrity of the PeopleSoft system especially after major changes to the system such an upgrade, major customization, application of bundles/ patches. It is also recommended to have regular maintenance runs of these audits to resolve integrity issues on a proactive basis. It is important to understand the purpose and the usage of these reports to make correct decisions for the objects in the system. There was an earlier article on the same topic with the focus on UPGCOUNT to report the counts of the tables. In this post, I’ll just highlight the other reports.
DDDAUDIT, the Database Audit report reports the inconsistencies between the PeopleSoft Record and Index objects and the Database objects. During an Upgrade, a significant number of objects get dropped or added newly in the latest version. It is important to review this report to filter such objects (TABLE-3 & VIEWS-2) and remove them from the database if they are dropped at the PS metadata tables. Likewise, new objects (TABLE-2 & VIEWS-1) need to be built correctly so as not to have any inconsistencies reported. Unique Indexes (INDEX-3) reported in this report after an Upgrade usually indicates that the base table has duplicate rows which requires validation of the upgrade and conversion scripts.
SYSAUDIT, the System Audit report reports orphan PeopleSoft objects & inconsistencies within the PeopleSoft metadata. Usually, this report runs into a number of pages in an Upgrade because of the large amount of changes between one version and another. However, one can fix the object inconsistencies during the development phase when most of the retrofit activity is completed which automatically resolves many of the issues. The error descriptions and their resolutions can be found in Peoplebooks at this location: Home > PeopleBooks > Enterprise PeopleTools 8.51 PeopleBook: Data Management > Ensuring Data Integrity.
Security Retrofit during an Upgrade can take inputs from SEC-28 section of the SYSAUDIT which reports invalid entries in PSAUTHITEM, one of the core tables for Permission lists. For PeopleSoft Systems which face App Server errors/ disruptions due to an invalid object load in the cache, the root cause might point to PSLOCK version integrity for which a version Application Engine may have to be run. For Systems where there are frequent Tree Level changes, the section on Tree Integrity queries can be referred to check and cleanup accordingly. There are more than 300 integrity checks and reported on individual section on the various object types that one can find in the SYSAudit report.
The Alter Audit helps bring synchronization between the structure of the PS record definition and the actual database table. This is to ensure that all data types, format, length and the structure match.
It is a good practice to regularly run these reports and ensure they are clean so that potential issues due to such inconsistencies can be avoided in the system. Keeping clean audits helps you to apply major bundles or maintenance packs or even prepare for Upgrades in a controlled environment.