报错信息：
12:27:17 SQL> set timing on 12:27:20 SQL> exec dbms_workload_repository.create_snapshot; BEGIN dbms_workload_repository.create_snapshot; END;
* ERROR at line 1: ORA-13509: error encountered during updates to a AWR table ORA-02291: integrity constraint (ORA-02291: integrity constraint (SYS.WRM$_SNAPSHOT_FK) violated - parent key not found.) violated - parent key not found ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 101 ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 139 ORA-06512: at line 1
Elapsed: 00:00:00.03
MMON process is not taking the AWR snapshots automatically. By default MMON process should gather statistics every hour, but we are seeing no snapshot in DBA_HIST_SNAPSHOT
Attemting to take a snapshot manually returns errors:
SQL> BEGIN 2 DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL'); 3 END; 4 / BEGIN * ERROR at line 1: ORA-13509: error encountered during updates to a SWRF table ORA-02291: integrity constraint (ORA-02291: integrity constraint (SYS.WRM$_SNAPSHOT_FK) violated - parent key not found .) violated - parent key not found ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 8 ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 31 ORA-06512: at line 1 SQL> SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot;
no records found.
To recreate the AWR objects catnoawr.sql and catawr.sql scripts from $ORACLE_HOME/rdbms/admin have been run on the database.
When we take a snapshot or mmon slave takes it, we insert a row into WRM$_SNAPSHOT. This table refers to parent table WRM$_DATABASE_INSTANCE by the foreign key constraint WRM$_SNAPSHOT_FK.
catnoawr.sql script drops the table WRM$_DATABASE_INSTANCE and catawr.sql creates it, there by flushing the data. We also store the information if the instance metadata has been populated to WRM$_DATABASE_INSTANCE or not, in a SGA flag. After recreation of the WRM$_DATABASE_INSTANCE table, this flag sets and indicates there is no metadata in the WRM$_DATABASE_INSTANCE table. So, while populating WRM$_SNAPSHOT it gives the obvious error ORA-02291.
To take the snapshots we need to populate WRM$_SNAPSHOT table successfully and for that instance metadata should be present in WRM$_DATABASE_INSTANCE.
So, if we have recreated the AWR objects by running catnoawr.sql and catawr.sql, then we need to bounce the database. On restart of the database instance the WRM$_DATABASE_INSTANCE will be populated with the required data. Then we can take manual snapshots and also MMON process will start collecting the snapshots.
http://www.xifenfei.com/forum/accident/%E9%87%8D%E5%BB%BA%E5%AE%8Cawr%E4%B9%8B%E5%90%8E%EF%BC%8C%E4%B8%8D%E9%87%8D%E5%90%AF%E8%AE%A9awr%E6%AD%A3%E5%B8%B8%E6%94%B6%E9%9B%86
