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