It happens when I try to deploy ASH Analytics on one of our production databases. We’ve done this before and there was no issue with it.. It seams this is a “feature” of the latest OEM Cloud control. We’ve patched recently the OMS to 220.127.116.11.1 (PSU Jan’2013) and probably this is the problem.
Usually ASH Analytics is easy to setup. Doug Burns have demonstrated this in his lecture “Falling in Love All Over Again: OEM 12c Performance Page Enhancements” on the latest BGOUG seminar. Generally you need a user with some specific privileges (OEM tells you what is needed). After you create such user, you click on Performance home -> ASH analytics, provle the credentials, and the feature gets deployed by and OEM job. But in the latest release the deployment job fails with
Error while executing the script : oracle.sysman.assistants.common.dbutil.SQLFatalErrorException:
Error creating PL/SQL Object PRVT_AWR_DATA : PACKAGE BODY – PLS-00201: identifier ‘SECRETUSER.ASHVIEWER’ must be declared
Error creating PL/SQL Object PRVT_AWR_DATA : PACKAGE BODY – PL/SQL: Statement ignored
at line number – 2580
Driver SQL Script encountered errors.
After three failures I knew it was not my fault. And, of course, I could not find anything similar in Metalink nor Google.
Fortunately at the beginning of the job log you can see the scripts that will be executed. Even better: the packages are not wrapped and it is easy the identify the problem. And the problem is the wrong order of execution of the scripts.
The sql scripts can be found on the OEM app server, in
plugins/oracle.sysman.db.oms.plugin_18.104.22.168.0/sql/db/latest/instance/. The order in the job is:
The second script fails with the aforementioned error, because it needs the package
ashviewer. As you can see, this package is created by the last 2 files. The solution is to run the scripts in the flowing order:
After this ASH Analytics works just fine.