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
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.
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
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);
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.
Post a Comment