Tuesday, March 5, 2024

Bugs in Oracle Apex 23.2 and ORDS 23.4

I have recently installed Apex 23.2, ORDS 23.4 for POC purpose and come across below issues when working with APEX and ORDS.

Bug: Oracle Apex 23.2 with Interactive Grid

When working with Interactive Grid, the filters saved in the Interactive Grid not displaying correct values. The issue happens intermittently, and filter values change from date to string or filter operators may drop out of the list.

When users working with operators they see inconsistency in the values, which is a big concern.

After research I found that its known issue ang filed bug #36200437

For more detailed info please check below Oracle forum link

BUG in APEX 23.2 Interactive Grid: Multiple Criteria Filter Issue - Oracle Forums

Bug: ORDS 23.4 with PL/SQL

Please see the cause and action for error PLS-00306: wrong number or types of arguments in call to 'string”

Cause: 
This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have the wrong datatype, the declaration might be faulty, or the declaration might be placed incorrectly in the block structure. For example, this error occurs if the built-in square root function SQRT is called with a misspelled name or with a parameter of the wrong datatype.

Action: 
Check the spelling and declaration of the subprogram name. Also confirm that its call is correct, its parameters are of the right datatype, and, if it is not a built-in function, that its declaration is placed correctly in the block structure.

But in my case when working with PL/SQL code users receiving “PLS-00306: wrong number or types of arguments in call”. The same code works after some time, after research found that it’s a known bug.

For more details, please see the below Oracle forums link.

ORDS 23.4 with PL/SQL. PLS-00306: wrong number or types of arguments in call to - Oracle Forums

Hope this helps.

Thanks & Regards.
Satishbabu Gunukula, Oracle ACE Pro

Wednesday, February 7, 2024

SQL Firewall in Oracle 23c

SQL Firewall inspects all incoming statements and ensures only authorized SQL is run and it is embedded in the Oracle Database and unauthorized SQL Statements will be logged and blocked.

SQL firewall provides real-time protection from attacks and mitigate risks from SQL injection attacks, anomalous access, credential abuse or theft. SQL Firewall supports all commands except transaction control commands such as SAVEPOINT, COMMIT, ROLLBACK.

To administer SQL Firewall user must have SQL_FIREWALL_ADMIN role. To query DBA_SQL_FIREWALL* data dictionary the user must have SQL_FIREWALL_VIEWER role

You can Configure SQL Firewall using DBMS_SQL_FIREWALL package or Oracle Data Safe. SQL Firewall can be used in both root and Pluggable Database (PDB)


You can enable SQL Firewall using below command.
SQL> EXEC DBMS_SQL_FIREWALL.ENABLE;

Create and enable SQL Firewall capture for a user using below command

SQL> BEGIN
DBMS_SQL_FIREWALL.CAPTURE_CAPTURE (
Username => ‘SCOTT’
top_level_only => TRUE,
Start_capture => TRUE
);
END;


Enable SQL Firewall Allow List

SQL>BEGIN
DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST (
username => ‘SCOTT’,
enforce => DBMS_SQL_FIREWALL.ENFORCE_SQL,
block => TRUE );
END;

You can use below commands to START and STOP capture.

SQL> EXEC DBMS_SQL_FIREWALL.START_CAPTURE (‘SCOTT’);
SQL> EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE (‘SCOTT’);

You can generate an allow list using below procedures.

DBMS_SQL_FIREWALL.ADD_ALLOWED_CONTEXT
DBMS_SQL_FIREWALL.DELETE_ALLOWED_CONTEXT
DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL

The SQL Firewall may generate large volume of capture logs and to minimize performance impact the database memory needs to be sized to handle the load. It is advised to add additional 2GB to LARGE_POOL_SIZE parameter and also it advised to have SGA_TARGET to 8GB or more.

To purge logs you can use below procedure

BEGIN
DBMS_SQL_FIREWALL.PURGE_LOG (
username => ‘SCOTT’,
purge_time => '2024-01-10 12:00:00.00 -08:00',
log_type => 'DBMS_SQL_FIREWALL.ALL_LOGS'
);
END;
/

You can also enable and disable SQL Firewall Trace using below commands. The trace level value should be LOW, HIGH, HIGHEST based upon how much detail tracing you want to have.

-Session level Tracing Enable and Disable
ALTER SESSION SET EVENTS 'TRACE SQL_FIREWALL DISK=trace_level
ALTER SESSION SET EVENTS 'TRACE SQL_FIREWALL OFF

-System Level Tracing Enable and Disable
ALTER SYSTEM SET EVENTS 'TRACE SQL_FIREWALL DISK=trace_level
ALTER SYSTEM SET EVENTS 'TRACE SQL_FIREWALL OFF


You can query below Data Dictionary Views for SQL Firewall protections
DBA_SQL_FIREWALL_ALLOWED_SQL - View shows allowed SQL and Accessed objects
DBA_SQL_FIREWALL_ALLOWED_IP_ADDR – View shows the Users allowed IP address
DBA_SQL_FIREWALL_CAPTURE_LOGS – View shows the Capture log entries
DBA_SQL_FIREWALL_VIOLATIONS – View shows the SQL Firewall Violations

Thanks & Regards
https://oracleracexpert.com
Oracle ACE Pro


Friday, January 5, 2024

Oracle Agile Application or Quick Search performance issue and how to resolve?

When you come across any performance issue on agile first, you need to verify schema integrity by running agile9_check.sql.

The agile9_check.sql it verifies Database Schema version with Agile Schema version, and it MUST match. Also, it validates schema objects against Agile schema version and report any errors or warnings.

You may see ERRORS/WARNINGS
ERROR: Missing or INVALID index (columns) XXXXXXXXX on table XXXXX.
ERROR: Missing or INVALID FTS CTX indexes XXXXXXX.
WARNING: ACTIVITY_BASELINE_XXXXX does not belong to AGILE DB. Please Drop the Table if not required.

In case of any Errors/Warnings you need we need to fix the issue. If you have created any custom objects you will see WARNING that object doesn't  below to Agile DB and you can ignore. 

Once user fix the schema integrity download listFTSInfo.sql and run the script as Agile user and it will generate listFTSInfo_xxxxxxxxxx.log file. You need to search for "029", where it says "List Index Percent of Fragmentation" where you can see the indexes that has fragmentation.

If you see any fragmentation, it will effect Quick search and stuck threads will be generated.

<[STUCK] ExecuteThread: '19' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "1,247" seconds working on the request "Http Request Information: weblogic.servlet.internal.ServletRequestImpl@2f2fb94[GET /Agile/PCMServlet]
", which is more than the configured time (StuckThreadMaxTime) of "1,200" seconds in "server-failure-trigger". Stack trace:
java.net.SocketInputStream.socketRead0(Native Method)


To resolve the issue, Log into sqlplus as agile database user, and run agile_ctx_recreate.sql found under %Oracle_base%\admin\{SID}\create\{agile_schema_username} to rebuild CTX indexes.

Oracle support recommends stopping Agile application, run agile_ctx_recreate.sql and Start the Application.

It is highly advisable to run complie_Invalid_objects.sql, agile9stats.sql to recompile invalid objects and collect stats to improve the performance.

Refer Oracle Agile support doc for Full Text search (FTS) Enablement, synchronization, Indexing and optimization.

Frequently Asked Questions on Agile Product Lifecycle Management (PLM) Full Text Search (FTS) Enablement, Synchronization, Indexing, and Optimization (Doc ID 1503311.1)

For Agile Stuck thread issue, refer below Oracle support Doc ID

Agile Managed Servers Goes to Warning State, Having Stuck Thread at com.agile.cs.query.QuerySessionBean (Doc ID 2919154.1)

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

Sunday, December 31, 2023

Webinar: Oracle Database 23c Security new features

This Webinar helps you to understand Oracle Database 23c new Security features and make use of these cutting edge functionalities.

Date and time: Jan 8th 2024, 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.
  • SQL Firewall
  • Audit
  • Authentication
  • Authorization
  • Encryption
  • Autonomous Database
  • Other
To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com
You will receive an email confirmation with meeting link or Webinar link will be posted here.

Note that registrations are limited and first come and first serve basis.

For presentation link Click here

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

Thursday, November 9, 2023

ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

When users performing the transactions on tables where DOMAIN,SPATIAL indexes are part of it they may receive below error and unable to proceed.

SQL> DELETE FROM USER_URL where CXT_ID=8484884;
ERROR at line 1: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

The main reason for this error is user might have copied the table/index using export/import or different method but the CREATE INDEX and failed.

Find out what caused the failure of CREATE INDEX. In many cases not having enough space on the Tablespace where you are creating the index is the culprit.

SQL> SELECT INDEX_NAME, TABLE_NAME STATUS,DOMIDX_STATUS,DOMIDX_OPSTATUS FROM DBA_INDEXES WHERE DOMIDX_OPSTATUS='FAILED';

INDEX_NAME     TABLE_NAME STATUS   DOMIDX_STATUS  DOMIDX_OPSTATUS
------------------ --------------- ---------- --------------------- -------------------------  
USER_URL_IDX  USER_URL      VALID    VALID                    FAILED


When verified I found that schema refreshed recently, and index not created successfully.

ORA-31693: Table data object "FIN"."USER_URL" failed to load/unload and is being skipped due to error:
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

If you need workaround to proceed with transactions immediately, then drop the index.
The permanent fix will be drop and recreate index successfully.

To avoid this type of issues, make sure you perform following checks.

1. Before refresh verify the tablespace or file systems has enough space and add required space
2. After import check the data pump or import log file and fix all failures
3. Run utlrp.sql to recompile all invalid objects.

Thanks & Regards