Monday, September 19, 2022

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

Thursday, September 8, 2022

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

Tuesday, September 6, 2022

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

Friday, August 26, 2022

DBMS_CLOUD Package in Oracle 21c

In order to work with data in object stores you can either use DBMS_CLOUD package or manually define external table. The package was DBMS_CLOUD package introduced in Oracle Autonomous Database to work with an object store, we can also use in on-prem for versions 19c and 21c but you need to install manually. You can refer Oracle note How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1) for setup and other details.

You can run below command to create directory, grant access on directory and DBMS_CLOUD package to emp_usr user.

CREATE OR REPLACE DIRECTORY EMP_DIR AS '/oracle/emp_dir';
GRANT EXECUTE ON DBMS_CLOUD TO emp_usr;
GRANT READ, WRITE ON DIRECTORY emp_dir TO emp_usr, C##CLOUD$SERVICE;

Note DBMS_CLOUD is owned by a separate schema C##CLOUD$SERVICE, this user locked by default. Both emp_usr, C##CLOUD$SERVICE user needs to have access.

Credential - You can create credential using the CREATE_CREDENTIAL procedure for your object store.

GRANT CREATE CREDENTIAL TO emp_usr;

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL (
CREDENTIAL_NAME => 'cloud_obj_cred',
USERNAME => '<username>,
PASSWORD => '<user auth token>'
) ;
END;
/

You can use UPDATE_CREDENTIALS procedure to make changes to the credentials

BEGIN
DBMS_CREDENTIAL.UPDATE_CREDENTIAL(
CREDENTIAL_NAME => 'cloud_obj_cred',
ATTRIBUTE => 'username',
VALUE => 'emp_usr');
END;
/

To drop credential you can use DROP_CREDENTIAL procedure.

BEGIN
DBMS_CLOUD.DROP_CREDENTIAL(CREDENTIAL_NAME => 'cloud_obj_cred’);
END;
/

You can disable and enable the credentials using DISABLE_CREDENTIAL and ENABLE_CREDENTIAL

BEGIN
DBMS_CREDENTIAL.DISABLE_CREDENTIAL('cloud_obj_cred’);
END;
/

BEGIN
DBMS_CREDENTIAL.ENABLE_CREDENTIAL('cloud_obj_cred’);
END;
/

Object store - For managing the objects in the cloud object store, please use below commands

To transfer a file from direct to cloud object store use the PUT_OBJECT procedure

BEGIN
DBMS_CLOUD.PUT_OBJECT (
CREDENTIAL_NAME => 'cloud_obj_cred’,
OBJECT_URI => 'https://xxxxxxxxxxxx/emp_file.txt',
DIRECTORY_NAME => 'emp_dir',
FILE_NAME => 'emp_file.txt');
END;
/

In order to transfer a object from cloud object store to the directory use the GET_OBJECT procedure

BEGIN
DBMS_CLOUD.GET_OBJECT (
CREDENTIAL_NAME => 'cloud_obj_cred’,
OBJECT_URI => 'https://xxxxxxxxxxxx/emp_file.txt',
DIRECTORY_NAME => 'emp_dir',
FILE_NAME => 'emp_file.txt');
END;
/

You can use dbms_cloud.list_objects to lists objects in the object store and use GET_METADATA function In order to get specific object metadata

SELECT * FROM DBMS_CLOUD.LIST_OBJECTS(CREDENTIAL_NAME => 'cloud_obj_cred’,LOCATION_URI => 'https://xxxxxxxxxxx');

SELECT * FROM DBMS_CLOUD.GET_METADATA(CREDENTIAL_NAME => 'cloud_obj_cred’,LOCATION_URI => 'https://xxxxxxxxxxx/emp_file.txt') AS METADATA FROM DUAL;

You can delete objects from the cloud object store using DELETE_OBJECT procedure
 
BEGIN
DBMS_CLOUD.DELETE_OBJECT(
CREDENTIAL_NAME => 'cloud_obj_cred’,
OBJECT_URI => 'https://xxxxxxxx/emp_file.txt');
END;
/

You can delete files from the directory using DELETE_FILE procedure
 
BEGIN
DBMS_CLOUD.DELETE_FILE(
DIRECTORY_NAME => 'emp_dir',
FILE_NAME => 'emp_file.txt');
END;
/

To list the files in a directory use LIST_FILES function

SELECT * FROM DBMS_CLOUD.LIST_FILES(DIRECTORY_NAME => 'emp_dir');

To export data into a cloud object store in the required format use EXPORT_DATA procedure

BEGIN
DBMS_CLOUD.EXPORT_DATA (
CREDENTIAL_NAME => 'cloud_obj_cred’,
FILE_URI_LIST => 'https://xxxxxxxxxxx/emp_file.csv',
QUERY => 'select * from emp',
FORMAT => '{"TYPE" : "csv"}');
END;
/

To delete all operation use DELETE_ALL_OPERATIONS procedure

BEGIN
DBMS_CLOUD.DELETE_ALL_OPERATIONS;
END;
/

To delete specific operation use DELETE_OPERATIONS procedure , but you need to provide operation ID

BEGIN
DBMS_CLOUD.DELETE_OPERATION(<id>);
END;
/

If you enter problems with DBMS_CLOUD with the user or role you can test the configuration using the same sample code that was used for the DBMS_CLOUD setup .

Thanks & Regards,
http://oracleracexpert.com, Oracle ACE  

Wednesday, May 25, 2022

Multitenant: DBCA PDB Remote Clone or Relocate in Oracle 19c

In Oracle 19c, you can clone or relocate a pluggable database (PDB) from one CDB (multitenant container database) to another using the DBCA (Database Configuration Assistant).

The following pre-requisites must met

• The local and remote PDBs must be in the archive log mode and local undo mode.
• It must have the CREATE PLUGGABLE DATABASE privilege in the local CDB root container.
• The database user in the remote PDB that the database link connects must have the CREATE       PLUGGABLE DATABASE, SYSOPER and SESSION privileges.
• The same database options installed on local and remote PDB’s

You can use below query to verify database has local undo mode and archive log mode enabled

SQL> select property_name, property_value from database_properties
where property_name = 'local_undo_enabled';
SQL> select log_mode from v$database;

First, create a user that is used in the database link automatically to connect during the cloning operation. When using DBCA we need to supply the credentials only and no need to create database link

SQL> CREATE USER c##remote_user1 IDENTIFIED BY password CONTAINER=ALL;
SQL> GRANT create session, create pluggable database TO c##remote_user1 CONTAINER=ALL;


You can launch DBCA in silent mode to clone PDB1 from CDB1 as PDB11 in CDB11

$dbca -silent -createPluggableDatabase -createFromRemotePDB -remotePDBName PDB1 -remoteDBConnString CDB1 -remoteDBSYSDBAUserName SYS -remoteDBSYSDBAUserPassword xxxxxxxx -sysDBAUserName sys -sysDBAPassword xxxxxxxx -dbLinkUsername c##remote_user1 -dbLinkUserPassword xxxxxxxx -sourceDB CDB11 -pdbName PDB11

Prepare for db operation
50% complete
Create pluggable database using remote clone operation
100% complete
Pluggable database "PDB11" plugged successfully.
Look at the log file "/oracle/cfgtoollogs/dbca/CDB11/PDB11/CDB11.log" for further details.

You can connect to CDB11 and check the status.

$sqlplus sys@CDB11 as sysdba

SQL> SHOW PDBS
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB11 READ WRITE NO

If you have cloned PDB11 as part of some testing and need to cleanup then use below commands by connecting to CDB11 as SYS

SQL> alter pluggable database PDB11 close;
SQL> drop pluggable database PDB11 including datafiles;



You can also use DBCA to delete the pluggable database that was cloned.

$dbca -silent -deletePluggableDatabase -sourceDB CDB11 -pdbName PDB11

Prepare for db operation
25% complete
Deleting Pluggable Database
40% complete
82% complete
94% complete
100% complete
Pluggable database "PDB11" deleted successfully.
Look at the log file "/oracle/cfgtoollogs/dbca/CDB11/PDB11/CDB11.log" for further details.

You can also use DBCA delete the instance using below command in silent mode

$dbca -silent -deleteDatabase -sourceDB CDB11 -sysDBAUserName sys -sysDBAPassword xxxxxxxxx

The relocatePDB command relocates a PDB from a remote CDB to a local CDB.

$dbca -relocatePDB
-pdbName name_of_the_local_pdb_to_create
-sourceDB database_name_of_the_local_pdb
-remotePDBName name_of_the_remote_pdb_to_relocate
-remoteDBConnString db_connection_string_of_the_remote_pdb
-sysDBAUserName name_of_the_sysdba_user
-sysDBAPassword password_of_the_sysdba_user
-dbLinkUsername name_of_the_dblink_user_of_the_remote_pdb
-dbLinkUserPassword password_of_the_dblink_user_of_the_remote_pdb

Example:
$ dbca -silent -relocatePDB -pdbName PDB11 -sourceDB CDB11  -remotePDBName PDB1 -remoteDBConnString TESTDB -remoteDBSYSDBAUserName sys  -remoteDBSYSDBAUserPassword xxxxxxx  -dbLinkUsername c##remote_user1 -dbLinkUserPassword xxxxxxx

Prepare for db operation
50% complete
Create pluggable database using relocate PDB operation
100% complete
Pluggable database "PDB11" plugged successfully.
Look at the log file "/oracle/cfgtoollogs/dbca/CDB11/PDB11/CDB11.log" for further details.


Refer Oracle documentation for more details and syntax

When running the DBCA in silent mode the outcome will be reported as exit codes. These exit codes helps to identify the command is successful or failed.

Exit Code Description
0  : Command execution successful
6  : Command execution successful but with warnings
-1 : Command execution failed
-2 : Invalid input from user
-4 :  Command canceled by user

Thanks & Regards,
http://oracleracexpert.com, Oracle ACE