Wednesday, October 31, 2012

ORA-13516: AWR Operation failed: only a subset of SQL can be issued

First started noticing that OEM is not showing any stats in other words no change in the session activity...
Digging into more found that the error "ORA-13516: AWR Operation failed: only a subset of SQL can be issued" showing up.

So, tried to create a snapshot manually and got the same error:

SQL> exec dbms_workload_repository.create_snapshot;
BEGIN dbms_workload_repository.create_snapshot; END;

*
ERROR at line 1:
ORA-13516: AWR Operation failed: only a subset of SQL can be issued
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 10
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 33
ORA-06512: at line 1


Looking into metalinks note#1226534 its apparent that either you have to run CATAWR.SQL or just bounce the instance (well, in my case its RAC database so no need to bounce the entire db but just the instance). 

Bounced the instance and now everything is running smooth again...
SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.