ORA-13509: error encountered during updates to a AWR tableORA-02291: integrity constraint (ORA-02291: integrity constraint (SYS.WRM$_SNAPSHOT_FK) violated – parent key not found.) violated – parent key not found

Cause:

After recreation of the AWR, snapshots are not generated and below error is thrown

kIsH@Xhydra<>BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');
END;
/  2    3    4
BEGIN
*
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 99
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 145
ORA-06512: at line 2

kIsH@Xhydra<>!oerr ora 2291
02291, 00000,"integrity constraint (%s.%s) violated - parent key not found"
// *Cause: A foreign key value has no matching primary key value.
// *Action: Delete the foreign key or add a matching primary key.

Action:

Below is the record flow and dependent internal tables for snapshot. If data is not populated in both tables, then snapshots will not be generated

wrm$_snapshot —> wrm$_database_instance

kIsH@Xhydra<>select STARTUP_TIME,VERSION,DB_NAME,INSTANCE_NAME,LAST_ASH_SAMPLE_ID,PLATFORM_NAME from wrm$_database_instance;

no rows selected


kIsH@Xhydra<>select count(*) from wrm$_snapshot;

  COUNT(*)
----------
         0

Bounce the database

kIsH@Xhydra<>startup force;
ORACLE instance started.

Total System Global Area 1459614512 bytes
Fixed Size                  9134896 bytes
Variable Size             369098752 bytes
Database Buffers         1073741824 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
kIsH@Xhydra<>BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');
END;
/  2    3    4

PL/SQL procedure successfully completed.

kIsH@Xhydra<>select STARTUP_TIME,VERSION,DB_NAME,INSTANCE_NAME,LAST_ASH_SAMPLE_ID,PLATFORM_NAME from wrm$_database_instance;

STARTUP_TIME         VERSION           DB_NAME   INSTANCE_NAME    LAST_ASH_SAMPLE_ID PLATFORM_NAME
-------------------- ----------------- --------- ---------------- ------------------ --------------------
22-JUL-22 07.04.58.0 19.0.0.0.0        TEST      test                              1 Linux x86 64-bit
00 PM


kIsH@Xhydra<>select count(*) from wrm$_snapshot;

  COUNT(*)
----------
         1

Leave a Reply