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 Security new features and head start using these features

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
  • OtherT
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


Friday, October 20, 2023

Oracle "Hyperion EPMA Server" service is not coming online

Recently I have encountered this issue with Oracle Hyperion. When we restarted Hyperion server, the “Hyperion EPMA Server” service is not coming online and application is down.

Here are the Errors from event.log

EPMA_Server
[EPMA Server Startup] ERROR SVR_ERR_PROCESSMGR_CANT_INIT_SESSIONMGR:Cannot initialize the Session Manager.Hyperion.DimensionServer.Interface.Exceptions.EPMAServiceException: Cannot initialize the Session Manager. ---> Hyperion.CommonServices.Exceptions.SessionManagerException: An error was encountered by the CAS Security Provider: Class: java.lang.NullPointerException
StackTrace:

at Hyperion.DimensionServer.SessionManager.CasSecurityProvider.GetDataBaseDetails(String& vendor, String& serverName, Int32& dbPort, String& dbName, String& username, String& password, String& jdbcUrl)
at Hyperion.DimensionServer.SessionManager.SessionManager.InitializeSqlConnectionString()
at Hyperion.DimensionServer.SessionManager.SessionManager.Initialize(String configFileName, Boolean restorePastInstanceSessions, Boolean enableCaching)
--- End of inner exception stack trace ---


Hyperion EPMA DimmensionServer
Service cannot be started. Hyperion.DimensionServer.Interface.Exceptions.EPMAServiceException: Cannot initialize the Session Manager. ---> Hyperion.CommonServices.Exceptions.SessionManagerException: An error was encountered by the CAS Security Provider: Class: java.lang.NullPointerException
StackTrace:
at Hyperion.DimensionServer.SessionManager.CasSecurityProvider.GetDataBaseDetails(String& vendor, String& serverName, Int32& dbPort, String& dbName, String& username, String& password, String& jdbcUrl)
at Hyperion.DimensionServer.SessionManager.SessionManager.InitializeSqlConnectionString()
at Hyperion.DimensionServer.SessionManager.SessionManager.Initialize(String configFileName, Boolean restorePastInstanceSessions, Boolean enableCaching)
--- End of inner exception stack trace ---

The event log is no much helpful. But note that the Hyperion EPMA Service will not come up if you have any issues with database. Check the logs to see any “ORA- “errors .

I found below error in EssbaseAdminServices0.log
#### <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <> <> <> <1329242417925> ORA-28001: the password has expired

Now it’s clear that Database user password expired.

SQL> select USERNAME, ACCOUNT_STATUS from dba_users where USERNAME = ‘SHARED_SERVICES’;

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SHARED_SERVICES EXPIRED(GRACE)

Do not change the password. If you change the password then you need to re-configure the Hyperion service in order to use the new password.

If you know the password then set the using below command.
SQL> Alter user SHARED_SERVICES identified by ‘xxxxxx’;

If you don’t know the password then reset the password following below steps
SQL> select password from sys.user$ where name='SHARED_SERVICES';

PASSWORD
------------------------------
61266722B44D5BG418

SQL> alter user QWERTY identified by values '61266722B44D5BG418’;
User altered.

Also make sure to modify PASSWORD_LIFE_TIME to “UNLIMITED” for DEFAULT or User profile that you are using

SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;
Profile altered.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com