BEA-002936 maximum thread constraint or BEA-000337 [STUCK] ExecuteThread in WebLogic

I come across an issue and found below error message in server log file. As per oracle note Doc ID 1356278.1 this message is only informational and can be safely ignored. But our planning application crashed and we had to restart the services.

####<Sep 8, 2022 11:06:22 AM PDT> <Info> <WorkManager> <SCLWPA2188> <Planning0> <[ACTIVE] ExecuteThread: '29' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1662660382282> <BEA-002936> <maximum thread constraint ASYNC_REP_FLUSH_WM is reached>
####<Sep 8, 2022 11:08:39 AM PDT> <Info> <WorkManager> <SCLWPA2188> <Planning0> <[ACTIVE] ExecuteThread: '31' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1662660519981> <BEA-002936> <maximum thread constraint ASYNC_REP_FLUSH_WM is reached>

In many cases the error may clear after some time. After research I found that WebLogic Server associates some internal work managers whenever a JMS server is created and below are the parameters

weblogic.jms.<JMSServer_NAME>.AsyncPush
Min threads = 1, max threads = unlimited, high fair share

weblogic.jms.<JMSServer_NAME>.Limited
Min threads = 1, max threads = 8, normal fair share.

weblogic.jms.<JMSServer_NAME>.System
Min threads = 1, max threads = unlimited, high fair share.

The work managers will expand up to the max limit and will shrink back when the load returns to normal, however shrinking doesn’t start unless there are few idle state threads.

As you see the work managers have a limited thread count of 8, but this limit is internal and cannot be changed without contacting Oracle support.  

Users may also see STUCK thread issues

Sep 3, 2022 11:23:31 AM EDT> <Error> <WebLogicServer> <abcsystems.orass.com> <server-cc113sp> <[ACTIVE] ExecuteThread: '33' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <45443435326649> <BEA-000337> <[STUCK] ExecuteThread: 45 for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for “550" seconds working on the request "Workmanager: default, Version: 0, Scheduled=true, Started=true, Started time: 678511 ms

The root cause Thread gets stuck if it is continually working (not idle) for a set period of time. This time is called the StuckThreadMaxTime, and has a default value of 600 seconds. The stuck thread cannot complete its current work or accept any new work the server logs the message. Note that if all threads gets stuck then server health state will change to either “warning” or “critical”

The stuck thread errors may disappear after some time if you see these errors frequently then you should start tuning to avoid issues. Note that restart will clear these errors.

I would recommend you go to WebLogic configuration and finetune your parameters based upon the current settings such as “Stuck thread max time” and “Stuck Thread Timer Interval”

To fine tune the parameter go to weblogic console à Domain à Environment à Server à <Managed Server> à Configuration > Tuning

Make sure you apply changes and reboot the server for new changes to take into effect. You can also refer below WLS documentation and Metalink note for more details.

WLS documentation : https://docs.oracle.com/cd/E23943_01/apirefs.1111/e13952/taskhelp/tuning/TuningExecuteThreads.html
Metalink Note: Note 1302472.1 - Information about STUCK Threads Condition and Tuning in Weblogic Server

Thanks & Regards

http://oracleracexpert.com, Oracle ACE Pro

RMAN-04009: warning from auxiliary database: ORA-28002: the password will expire within 6 days

I have come across an issue when trying to duplicate a database by connection to Recovery catalog database. You will come across this warning when the user profile has reached PASSWORD_LIFE_TIME value limit and you have entered into PASSWORD_GRACE_TIME value

RMAN-04009: warning from auxiliary database: ORA-28002: the password will expire within 6 days

First check the profile that user has been assigned.

SQL> select username , profile from dba_users where username=’RMAN’;
username profile
------------- ---------------
RMAN DEFAULT

Check the current status of the user
SQL> select USERNAME, ACCOUNT_STATUS from dba_users where USERNAME = ‘RMAN’;
USERNAME ACCOUNT_STATUS
------------- ----------------- --------------------------------
RMAN EXPIRED(GRACE)

Check the RESOUCE_NAME, LIMIT for the Default profile.
SQL> select RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT';

RESOURCE_NAME LIMIT
---------------------------  -------------------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
RESOURCE_NAME LIMIT
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
INACTIVE_ACCOUNT_TIME UNLIMITED

As we see password PASSWORD_LIFE_TIME set to 180 days

First you need to change the password for the issue to resolve.
SQL> Alter user rman identified by rman_new_password;

As current password life time set 180 days, You can change the password life time to avoid this issue in future. Either you can set to specific number of days or UNLIMITED.

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.

You can check the modified resource limit for PASSWORD_LIFE_TIME
SQL> select RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME';

RESOURCE_NAME   LIMIT
---------------------------  -------------------------------------------------
PASSWORD_LIFE_TIME UNLIMITED

Hope this helps

Thanks & Regards

http://oracleracexpert.com, Oracle ACE Pro

Automatic Indexing in Oracle 19c & 21c

The DBA’s are responsible for Index management, which includes monitor index, add, change and remove based upon workload and ad-hoc manner. It will be difficult to decide type of index, when to create, change or drop and measure the impact, so there will be both positive and negative effects.

In Oracle 19c, the automatic indexing feature introduced and it automatically creates, rebuilds, and drops indexes to improve the performance based upon application workload and changes. This helps to optimize the database and improve performance without any user intervention.

The DBMS_AUTO_INDEX package is used for managing the automatic indexing and user can find out the current automatic index configuration by querying CDB_AUTO_INDEX_CONFIG view.

• CONFIGURE Procedure - Configures automatic indexing.
• DROP_AUTO_INDEXES Procedure - Drop the automatically created indexes manually by overriding the retention parameter.
• DROP_SECONDARY_INDEXES Procedure -Deletes all the indexes, except the ones used for constraints
• REPORT_ACTIVITY Function - Generate Report of the automatic indexing operations
• REPORT_LAST_ACTIVITY Function - Generate Report of the last automatic indexing operation

AUTO_INDEX_MODE : The automatic indexing is controlled using the AUTO_INDEX_MODE property, It defines different modes of operation.

• REPORT ONLY: Turn on automatic indexing and new indexes are invisible and not available for SQL operations
• IMPLEMENT: Turn on automatic indexing and new indexes are visible available for SQL operations
• OFF: Turn OFF automatic indexing but does not disable existing auto indexes.

SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF');

AUTO_INDEX_SCHEMA Procedure - When automatic indexing enabled all schemas will be used for auto indexes and you can exclude or include any schemas by using AUTO_INDEX_SCHEMA parameter

The below examples adds the SALES schema to AUTO INDEXES exclusion list

begin
dbms_auto_index.configure(
parameter_name => 'AUTO_INDEX_SCHEMA',
parameter_value => 'SALES',
allow => FALSE);
end;

The below examples removes the SALES schema from AUTO INDEXES exclusion list

begin
dbms_auto_index.configure(
parameter_name => 'AUTO_INDEX_SCHEMA',
parameter_value => 'SALES',
allow => NULL);
end;

The below example removes all the schemas from AUTO INDEXES exclusion list.

begin
dbms_auto_index.configure(
parameter_name => 'AUTO_INDEX_SCHEMA',
parameter_value => NULL,
allow => TRUE);
end;

You can also define retention period that you want auto indexes. The below example sets the retention period of auto indexes to 30 days.

begin
dbms_auto_index.configure(
parameter_name => 'AUTO_INDEX_RETENTION_FOR_AUTO',
parameter_value => '30');
end;

By default automatic indexes created in default permanent tablespace and you can change this by using AUTO_INDEX_DEFAULT_TABLESPACE

SQL> exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEX_TBS');

You can also allocate percentage of the tablespace can be used for auto indexes. In below example you can use 10%

SQL> exec dbms_auto_index.configure(‘AUTO_INDEX_SPACE_BUDGET’, ‘10’);


DROP_AUTO_INDEXES Procedure: Using this procedure you can Drop the automatically created indexes manually by overriding the retention parameter.

The below example will drop a single index in SALES schema and it allow recreate. If you do not want to recreate set the value to FALSE

begin

dbms_auto_index.drop_auto_indexes(

owner => ‘SALES’,

index_name => '”SYS_AI_45rfg54nxvjcty”',

allow_recreate => TRUE);

end;

/

The below example will drop all auto indexes owned by SALES and will not allow recreate as allow_recreate set to FALSE

begin

dbms_auto_index.drop_auto_indexes(

owner => 'SALES',

index_name => NULL

allow_recreate => FALSE);

end;

/

DROP_SECONDARY_INDEXES Procedure – This procedure deletes all the indexes, except the ones used for constraints

The below example deletes all auto indexes except the ones used for constraints

begin
dbms_auto_index.drop_secondary_indexes;
end;

The below example deletes all auto indexes from the REGION table in the SALES schema except the ones used for constraints

begin
dbms_auto_index.drop_secondary_indexes('SALES', 'REGION');
end;

The below example deletes all auto indexes in the SALES schema except the ones used for constraints

begin
dbms_auto_index.drop_secondary_indexes('SALES');
end;

REPORT_ACTIVITY Function – By using this function you can generate Report of the automatic indexing operations

The below example generates automatic indexing operations report executed in the last 24 hours.

declare
act_report clob := null;
begin
act_report := dbms_auto_index.report_activity();
end;

or

SQL> select dbms_auto_index.report_activity() from dual;

The below example generates HTML automatic indexing operations report executed in the last 6 hours activity.

SQL> select dbms_auto_index.report_activity(activity_start => systimestamp-0.25, activity_end => systimestamp-1, type => 'HTML') from dual;

REPORT_LAST_ACTIVITY Function -The below example generates report of the last automatic indexing executed in a database.

declare
act_report clob := null;
begin
act_report := dbms_auto_index.report_last_activity();
end;

or

SQL>  select dbms_auto_index.report_last_activity() from dual;

The Oracle 21c comes with an enhancement in Automatic indexing, which helps to reduce the over head of the cursor invalidations during automatic index creation and also added new enhancements to improve query performance.

Thanks & Regards

http://oracleracexpert.com, Oracle ACE