Monday, December 12, 2022

Webinar: Oracle Data pump enhancements in Oracle 21c

This Webinar helps you to understand Oracle data pump new features in Oracle 21c and take an advantage of these features.

Date and time: Dec 21st 2022 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.
  • Introduction to Oracle Data Pump
  • CHECKSUM, CHECKSUM_ALGORITHM
  • VERIFY_ONLY and VERIFY_CHECKSUM
  • INCLUDE and EXCLUDE in the Same Operation
  • Index Compression
  • Transportable Tablespace Enhancements
  • JSON Data Type Support
  • Export/Import from Cloud Object Store
  • Q&A
To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com
Please use Zoom Link to join the Webinar.  

Note that registrations are limited and first come and first serve basis.
You will receive an email confirmation with meeting session link.

For presentation link Click here 

Thanks & Regards,
http://www.oracleracexpert.com

Tuesday, November 8, 2022

Physical Standby Recovery or Sync Using Recover from Service from 18c

The RECOVER FROM SERVICE is used to recover data guard database which is out of sync or lagging standby. Before 12c the user needs to determine the current SCN, take a backup and apply, start managed recovery of standby and but in 12c they automated these steps but user still needs to perform few manual steps such as control file update. 

This feature has been improved in Oracle 18c and covers all the steps that DBA used to perform manually

Primary : DBPRI
Standby: DBSTB

Steps if the database is Oracle 18c or above

1. Check the Data Guard Broker configuration.

DGMGRL> show configuration
Configuration – dg_config
Protection Mode: MaxPerformance

Members:
dbpri - Primary database
Error: ORA-16724: cannot resolve gap for one or more members

  dbstb - Physical standby database
  Warning: ORA-16809: multiple warnings detected for the member

Fast-Start Failover: Disabled

Configuration Status:
ERROR (status updated 5 seconds ago)

We can see that both Primary and Physical standby are out of sync

2. Stop Recovery Managed process on the standby

DGMGRL> edit database DBSTB set state=APPLY-OFF;

Or

RMAN> alter database recover managed standby database cancel;

3. Run roll forward command

$rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Mar 11 12:12:48 202
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: DBSTB (DBID=xxxxxxxx)
RMAN> recover standby database from service DBPRI
Starting recover at 11-MAR-22
using target database control file instead of recovery catalog
Oracle instance started…….

media recovery complete, elapsed time: 00:00:00
Finished recover at 05-MAR-22
Executing: alter system set standby_file_management=auto
Finished recover at 05-MAR-22

4. Enable the Recovery Managed process on the standby

DGMGRL> edit database DBSTB set state=APPLY-ON;

Or

RMAN> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


5. Verify the Data Guard configuration status

DGMGRL> show configuration

Configuration - dg_config
Protection Mode: MaxPerformance

Members:
dbpri - Primary database
  dbstb - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 56 seconds ago)

Now standby Database has been recovered and in sync with the primary.

Thanks & Regards

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