ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^212","kglseshtTable")

We recently encountered the following error in 19c, which typically occurs when the database needs additional shared memory. In most of the cases setting MAX_SGA_SIZE to a higher value will resolve the issue.

Below are some possible cause

•  Insufficient memory allocated via initialization parameters
•  Fragmentation in app design
•  Auto tuning issues
•  A Bug causing the issue
•  Memory leaks

ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^334","kglseshtTable")
< ORA-00604: error occurred at recursive SQL level 1 < ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","unknown object","KGLH0^f185eace","kglHeapInitialize:temp")
< ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^394","kglseshtTable")


The error message will provide the amount of memory unavailable, memory pool facing the issue and failed allocation details

 

I would highly suggest running below query to get the optimal value for SGA_TARGET
Select * from V$SGA_TARGET_ADVICE

Note that in order to initialization parameters to take into effect we need to bounce the instance.

AGLT>oerr ora 04031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
// If the error is issued from an Oracle Streams or XStream process,
// increase the initialization parameter STREAMS_POOL_SIZE or increase
// the capture or apply parameter MAX_SGA_SIZE.
// parameter MAX_SGA_SIZE.

Refer below links for Oracle support notes

• This Oracle support note provides information about ORA-04031 related bugs and which release they were fixed

OERR: ORA-4031 "unable to allocate %s bytes of shared memory ("%s","%s","%s")" (Doc ID 4031.1)

• This Oracle note provides detailed troubleshooting and diagnosing details

Troubleshooting and Diagnosing ORA-4031 Error [Video] (Doc ID 396940.1)

• This Oracle note provides detailed understanding and tuning of the of the shared pool

NOTE:62143.1 - Troubleshooting: Understanding and Tuning the Shared Pool

Thanks & Regards,

https://oracleracexpert.com

Annotations in Oracle 23ai

Oracle Database23ai introduced new feature annotations, using this feature user can add additional comments to objects and allow storing database object metadata. It helps to share the metadata across applications.

You can add annotations to any supported schema objects with CREATE privilege and you can add or drop annotations using ALTER privilege, the object can have more than one annotation as well. The supported schema objects include tables, indexes, views, materialized views. An annotation will consist of a name and an optional value, which can be entered as freeform text fields.

You can drop the annotations, or it will be dropped when a schema object is dropped. You can query USER|ALL|DBA_ANNOTATIONS_USAGE to view annotations for an object.

Annotation has three clauses i.e ADD, DROP or REPLACE

ADD – create annotations for an existing object.
DROP – removes the annotation from an object.
REPLACE – change annotation_value. This clause used with ALTER statements.

In below example, we’re adding annotation both table and columns

CREATE TABLE emp (
emp_id number ANNOTATIONS (ColumnInfo ‘Employee ID’),
emp_name varchar2(30) ANNOTATIONS (ColumInfo ‘Employee Name’),
dept varchar2(20) )
ANNOTATIONS ( TableInfo ‘Employee Table’);

SQL> SELECT ANNOTATION_NAME, ANNOTATION_VALUE from USER_ANNOTATIONS_USAGE WHERE Object_Name ='EMP' AND Object_Type = 'TABLE' AND Column_Name IS NULL;

ANNOTATION_NAME ANNOTATION_VALUE
-------------------- --------------------
TABLEINFO Employee Table

You can drop annotation from an existing table using below example
SQL> ALTER TABLE emp ANNOTATIONS (DROP TableInfo);

You can add annotation to the table using below example, note that ADD keyword is optional.
SQL> ALTER TABLE emp ANNOTATIONS (ADD TableInfo ‘Employee Table’);

SQL> SELECT ANNOTATION_NAME, ANNOTATION_VALUE from USER_ANNOTATIONS_USAGE WHERE Object_Name ='EMP' AND Object_Type = 'TABLE' AND Column_Name IS NOT NULL;

ANNOTATION_NAME ANNOTATION_VALUE
-------------------- --------------------
EMP_ID Employee ID
EMP_NAME Employee Name

In below example you can add, replace and drop annotations at column level. Note that ADD keyword is optional.

SQL> ALTER TABLE EMP modify dept ANNOTATIONS (ADD ColumnInfo ‘Dept Name’);
SQL> ALTER TABLE EMP modify dept ANNOTATIONS (REPLACE ColumnInfo ‘Deptartment Name’);
SQL> ALTER TABLE EMP modify dept ANNOTATIONS (DROP ColumnInfo);

Thanks & Regards,

https://oracleracexpert.com

Authentication enhancements in Oracle 23c

Oracle 23c offers longer passwords improved security in authentication now supports up to 1024 bytes

• Oracle Data pump Export and import support longer encryption passwords up to 2024 bytes long

• Oracle Call interface (OCI) and Oracle C++ Call interface support up to 1024 bytes long password for user authentication.
• JDBC think driver support up to 1024 characters for password
• Oracle Database (including Autonomous) and clients supports password up to 1024 bytes

You can login into Oracle Database using Microsoft Azure Active Directory single sing-on OAuth2 access token. Multicloud feature integrates Oracle Database and Azure AD and you can perform this integration on

• Oracle 19.16 and later (Back ported) but not for Oracle 21c.
• Oracle Autonomous Database on Dedicated/Shared Exadata Infrastructure
• Oracle Exadata Clod Service
• Oracle Base Database Service

You can map AD users to Oracle Database schema and roles and also you can login ODP.NET can login into Oracle Database Using Microsoft Azure Active Directory. The UTL_HTTP support SHA-256/512 and XDB HTTP supports SHA512, authentication and updated Kerberos Library support.

The password length helps accommodating Oracle Identity Access management (IAM) and Identity Cloud service (IDCS) and helps enabling uniform password rules.

Oracle 23c offers many Improvements in Kerberos security and MIT Kerberos version 1.20.1 supports cross domain, windows credential guard and multiple principals.

KERBEROS5_CC_NAME and KERBEROS5_PRINCIPAL can be specified in tnsnames.ora and the values must match for user authentication.

kuser =
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orahost)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=ORCL))
(SECURITY=(KERBEROS5_CC_NAME = /tmp/kuser/krb.cc) (KERBEROS5_PRINCIPAL = kprinc)))

Kerberos parameters can be specified in Sqlnet.ora file but note that some parameters you can set at server level, and some are at client level and few you can set on both.

You can set below parameters on both client and server

SQLNET.AUTHENTICATION_SERVICES=(KERBEROS5)

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle

SQLNET.KERBEROS5_CONF=<Kerberos_configfile_path >

SQLNET.KERBEROS5_CONF_MIT=(TRUE)

SQLNET.FALLBACK_AUTHENTICATION=FALSE

SQLNET.KERBEROS5_CLOCKSKEW=1200

The below parameter is not required on the server, but in case if your client in Microsoft Windows then you may want to consider setting OSMSFT:// or MSLSA

SQLNET.KERBEROS5_CC_NAME= <Kerberos_CC_name_withpath>

This setting is not usually required for the client or the server.

SQLNET.KERBEROS5_REALMS=<Kerberos_realms_path >

Only set this parameter on the server

SQLNET.KERBEROS5_KEYTAB=<Kerberos_keytab_path > 

Thanks & Regards,

https://oracleracexpert.com

Opatch failed with "Make failed to invoke "/usr/bin/make -f ins_net_client.mk client_sharedlib"

I encountered a patch failure while upgrading to Oracle 19.23. I received "Opatch session completed with warnings".

The error clearly indicates that the root cause is a missing "shrept.lst" file.

Make failed to invoke "/usr/bin/make -f ins_net_client.mk client_sharedlib ORACLE_HOME=/oracle/product/19.x.0.0/dbhome_1 OPATCH_SESSION=apply"....'genclntsh: genclntsh: Could not locate /oracle/product/19.x.0.0/dbhome_1/network/admin/shrept.lst

make: *** [ins_net_client.mk:143: client_sharedlib] Error 1
'
Make failed to invoke "/usr/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=/oracle/product/19.x.0.0/dbhome_1 OPATCH_SESSION=apply"....'genclntsh: genclntsh: Could not locate /oracle/product/19.x.0.0/dbhome_1/network/admin/shrept.lst
make: *** [ins_rdbms.mk:56: client_sharedlib] Error 1
'
Make failed to invoke "/usr/bin/make -f ins_ldap.mk ldapsearch ORACLE_HOME=/oracle/product/19.x.0.0/dbhome_1 OPATCH_SESSION=apply"....'genclntsh: genclntsh: Could not locate /oracle/product/19.x.0.0/dbhome_1/network/admin/shrept.lst

make: *** [/oracle/product/19.x.0.0/dbhome_1/ldap/lib/env_ldap.mk:2474: /oracle/product/19.x.0.0/dbhome_1/lib/libclntsh.so] Error 1
'
The following make actions have failed :

Re-link fails on target "client_sharedlib".
Re-link fails on target "client_sharedlib".
Re-link fails on target "ldapsearch".

Do you want to proceed? [y|n]
y
User Responded with: Y
Patch 36233263 successfully applied.
Sub-set patch [34765931] has become inactive due to the application of a super-set patch [36233263].
Please refer to Doc ID 2161861.1 for any possible further required actions.
OPatch Session completed with warnings.
Log file location: /oracle/product/19.x.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-08-24_22-25-17PM_1.log

I have reviewed the opatch log file to verify the error and I can see the the same error.

[Aug 24, 2024 11:32:40 PM] [INFO] Deleted the file "/oracle/product/19.x.0.0/dbhome_1/.patch_storage/unzip_action/1723357551241/inventory/Templates/perl/bin/zipdetails"

[Aug 24, 2024 11:32:40 PM] [INFO] --------------------------------------------------------------------------------
[Aug 24, 2024 11:32:40 PM] [INFO] The following warnings have occurred during OPatch execution:
[Aug 24, 2024 11:32:40 PM] [INFO] 1) OUI-67200:Make failed to invoke "/usr/bin/make -f ins_net_client.mk client_sharedlib ORACLE_HOME=/oracle/product/19.x.0.0/dbhome_1 OPATCH_SESSION=apply"....'genclntsh: genclntsh: Could not locate /oracle/product/19.x.0.0/dbhome_1/network/admin/shrept.lst
make: *** [ins_net_client.mk:143: client_sharedlib] Error 1
'
[Aug 24, 2024 11:32:40 PM] [INFO] 4) OUI-67124:Re-link fails on target "client_sharedlib".
Re-link fails on target "client_sharedlib".
Re-link fails on target "ldapsearch".
[Aug 24, 2024 11:32:40 PM] [INFO] --------------------------------------------------------------------------------
[Aug 24, 2024 11:32:40 PM] [SEVERE] OUI-67008:OPatch Session completed with warnings.
[Aug 24, 2024 11:32:40 PM] [INFO] Finishing UtilSession at Sat Aug 24 23:32:40 PDT 2024
[Aug 24, 2024 11:32:40 PM] [INFO] Log file location: /oracle/product/19.x.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-08-24_22-25-44PM_1.log

we found that shrept.lst file was missing and have rollback the patch using
$ opatch rollback -id <xxxxxx>

We copied the shrept.lst file from another environment, re-applied the patch using "opatch apply," and the process completed successfully without any issues.

Thanks & Regards

https://oracleracexpert.com

Column Level Audit in Oracle 23ai

Oracle Database support auditing at column level and Audit actions at column level introduced in Oracle 23ai Database. Using this feature, you can Audit column level for tables and views.

Before Oracle 21c and before you can audit at table level. Oracle deprecated traditional auditing in Oracle 21c, and it is desupported from Oracle 23ai.

In Oracle 23ai user can perform audit actions at individual column level by creating audit policies. You can audit action on a Tableau or view column using “CREATE AUDIT POLICY” Statement

For example,

• Audit all actions on a table

    SQL> CREATE AUDIT POLICY aud_all_act_emp ACTIONS ALL ON SCOTT.EMP;

When using ACTIONS ALL user should be cautious. Because enabling on an online transaction processing (OLTP) workload. Will lead large number of audit records.

• Audit action on a column

    SQL> CREATE AUDIT POLICY aud_col_bon_act_emp ACTIONS SELECT (BONOUS) ON                SCOTT.EMP;

Users can query UNIFIED_AUDIT_TRAIL dictionary view to view audit events information

SQL> SELECT OBJECT_NAME,SQL_TEXT FROM UNIFIED_AUDIT_TRAIL WHERE OBJECT_NAME = 'EMP';

OBJECT_NAME     SQL_TEXT
------------------------ ----------------------------------------------------------------
EMP                         SELECT BONUS FROM EMP WHERE EMPNO=123

You can also audit actions on SYS objects using CREATED AUDIT POLICTY statement.

SQL> CREATE AUDIT POLICY aud_col_sys_obje ACTIONS SELECT ON SYS.TABLES;

To audit recursive actions, use ONLY TOPLEVEL clause in the CREATE AUDIT POLICY statement.

User can audit PL/SQL packages, functions, procedures and triggers, pls consider below points

• You can audit standalone PL/SQL packages, functions and procedures 
• Auditing a PL/SQL package will audit all functions and procedures within the package. You cannot audit individual producers and functions within the PL/SQL package. 
• Auditing all executions will enable auditing all triggers, functions and procedures within PL/SQL packages.
• Auditing EXECUTE operation on a PL/SQL stored procedure or function, will determine the success or failure of the operation auditing purpose.

For a list of Object-level Database action audit options, you can refer Oracle documentation here

Thanks & Regards,

https://oracleracexpert.com