ORA-13831: SQL profile or patch name specified is invalid [On 19c]

 Problem:

As of a sudden one critical application connecting to a 19c database crashed and kept throwing the following error in its logs:

ORA-13831: SQL profile or patch name specified is invalid

Analysis:

A quick search revealed that we're hitting Bug 29942554 

Because I had to get the application up and running quickly I've decided to go with the workaround (and luckily this bug has a workaround). But why I'm writing this post then? Because the workaround is not clear, so I thought of writing this article.

Solution:

1. Searching the Failing SQL Statement details shows that a new plan was created and accepted just few seconds before the application crash, and that would be the reason for triggering this bug:

SQL> SELECT SQL_HANDLE,PLAN_NAME,CREATED,LAST_MODIFIED,FIXED,ADAPTIVE,ENABLED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%SELECT TYP_LF_BOOKED_HELD(FLT.FAR%' ORDER BY CREATED;

 2. Disabling that latest created SQL Plan for the problematic SQL will work around this bug and get the application back running:

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_6ef2b0406c073b2e',
    plan_name      => 'SQL_PLAN_6xwph81q0fftfdbdc6b11',
    attribute_name  => 'enabled',
    attribute_value  => 'NO');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

Conclusion:

SQL Statements having a Baseline may hit bug 29942554 if a new plan get accepted and added to the same baseline. As a workaround, disable the new accepted plan inside the Baseline.