A Short Note on Pesky CSSCAN Results Before a Characterset Change

Read Time:2 Minute, 15 Second

A client of mine was busy correcting one of his development streets and therefore the NLS charactersets of about 10 11.2.0.4 EE databases had to be changed.
Most of the databases were originally configured with WE8ISO8859P15, others with WE8ISO8859P1 and all of them had to get WE8MSWIN1252. Some of them were single instances (export, development and test), some RAC (acceptance) and some of them were RAC plus Dataguard (production, for the procedure see Doc ID 1124165.1).

The initial results of the csscan’s always showed table SYS.REG$ with a couple of LOSSY entries and all of them indicated 1 to 4 user tables containing LOSSY (or Convertible) data. The user tables were truncated and imported again after the conversion. And there where the (RAC) instances showing troublesome entries in some WRI$_%, WRH$_% and/or WRR$_%-tables including table SYS.WRI$_ADV_MESSAGE_GROUPS.

For most of this tables we could figure out quite quickly how to remedy these entries, but table SYS.WRI$_ADV_MESSAGE_GROUPS eluded us for a while. All we found on the internet, in the Oracle documentation and on MySupport were about other specific SYS.WRI$- or WRH$ tables, but SYS.WRI$_ADV_MESSAGE_GROUPS was never mentioned in connection with csscans. So, I tried to figure out what this table actually is used for and found that…

… WRM$-tables (10 tables) contain the metadata information for the Automatic Workload Repository (AWR).
… WRH$ tables (125 tables) store historical data or snapshots of the Workload History collected by the MMON process. They can be purged by first reducing the retention time for the history followed by the purging of the statistics:

exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(0);
exec DBMS_STATS.PURGE_STATS(sysdate);

These two types of tables are, according to Oracle Doc ID 258904.1, not to be truncated, but have to be purged with DBMS_STATS package to prevent them from any harm.

… WRR$-tables (31 tables) support the workload replay functionality.
… WRI$-tables (96 tables) are related to the advisory functions.

But my pesky little SYS.WRI$_ADV_MESSAGE_GROUPS-table turned out to belong to the Task Scheduler and that the Database Migration Utility (DMU) sometimes needs this table to be cleared as well (Doc ID 2018250.1). Luckyly, tasks were not used in these databases and finding out which task entry was to blame was senseless and too time consuming. So, we decided to remove all the tasks and found this command to do so:

exec DBMS_ADVISOR.DELETE_TASK('%');

… and really, it worked!

Table SYS.WRI$_ADV_MESSAGE_GROUPS was clean and we could finally execute CSALTER.PLB without any further problems.

About Post Author

Karin Kriebisch

Karin is an experienced, broadly orientated Oracle consultant (senior DBA consultant (OCP)) and is specializing in Oracle Database Security (Oracle Implementation Specialist Security 11g). Karin uses to work in divers enterprise environments like pension fonds, banks or pharmaceuticals, where ITIL and Service Management software products (e.g. Assyst, Remedy, Jira, GLPI) play a big role. She is regularly working for custumers in development projects and migrations, so her experience lead her to pay special attention in the areas of maintenance and (database)infrastructure. Her knowledge embraces multiple operating systems like OpenVMS, Linux, HP-UX and of course Windows, and she is comfortable with Oracle Database versions starting with Oracle 7 up to 12c and virtualization environments like vmWare and Oracle Virtualbox. Add to this her ability of scripting (for Windows commandline and Powershell, *nix shells) and her knowlegde of Oracle applicatieservers (incl. WebLogic) to her portfolio and it makes her a technical allround Oracle specialist. Karin is very customer and service orientated and always strives to reach her goals. She has a very sharp analytical mind and a keen eye for the details in the big picture. Karin is a senior DBA at AMIS b.v. for more than 15 years and she still likes the changing environments she is able to work in and meeting new and interesting people all the time.

karin.kriebisch@amis.nl