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

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

Webinar: Oracle Database 23c New Security features

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

Date and time: June 14th 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 Zoom Link Click here
For presentation link Click here

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

The listen port 8080 is already in use by another process using ORDS

I have come across below error when installing ORDS for APEX using 23.x+ version. When you try to bring up the ORDS you may receive the same warning message

/oracle:DBA>ords --config /oracle/ords_conf install

ORDS: Release 23.4 Production on Tue Feb 27 05:42:02 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/oracle/ords_conf/

The configuration folder /oracle/ords_conf does not contain any configuration files.

Oracle REST Data Services - Interactive Install

Enter a number to select the type of installation
[1] Install or upgrade ORDS in the database only
[2] Create or update a database pool and install/upgrade ORDS in the database
[3] Create or update a database pool only
Choose [2]:
Enter a number to select the database connection type to use
[1] Basic (host name, port, service name)
[2] TNS (TNS alias, TNS directory)
[3] Custom database URL
Choose [1]:
Enter the database host name [localhost]: orasrv1
Enter the database listen port [1521]:
Enter the database service name [DBA]: ADB
Provide database user name with administrator privileges.
Enter the administrator username: SYS
Enter the database password for SYS AS SYSDBA:
Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@//orasrv1:1521/ADB

Retrieving information.
Enter the default tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [SYSAUX]: ORDS_DATA

Enter the temporary tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [TEMP]: TEMP

Enter a number to select additional feature(s) to enable:

[1] Database Actions (Enables all features)
[2] REST Enabled SQL and Database API
[3] REST Enabled SQL
[4] Database API
[5] None
Choose [1]:

Enter a number to configure and start ORDS in standalone mode
[1] Configure and start ORDS in standalone mode
[2] Skip
Choose [1]:

Enter a number to select the protocol
[1] HTTP
[2] HTTPS
Choose [1]: 2
Enter the HTTPS port [8443]:
Enter a number to select the certificate type
[1] Use self-signed certificate (generates automatically)
[2] Use my SSL certificate (requires SSL certificate and SSL certificate private key)
Choose [1]:
Enter the SSL hostname: orasrv1
Enter the APEX static resources location: /oracle/apex/images
The setting named: db.connectionType was set to: basic in configuration: default
The setting named: db.hostname was set to: orasrv1 in configuration: default
The setting named: db.port was set to: 1521 in configuration: default
The setting named: db.servicename was set to: ADB in configuration: default
The setting named: plsql.gateway.mode was set to: proxied in configuration: default
The setting named: db.username was set to: ORDS_PUBLIC_USER in configuration: default
The setting named: db.password was set to: ****** in configuration: default
The setting named: feature.sdw was set to: true in configuration: default
The global setting named: database.api.enabled was set to: true
The setting named: restEnabledSql.active was set to: true in configuration: default
The setting named: security.requestValidationFunction was set to: ords_util.authorize_plsql_gateway in onfiguration: default
The global setting named: standalone.https.port was set to: 8443
The global setting named: standalone.https.host was set to: orasrv1
The global setting named: standalone.static.path was set to: /oracle/apex/images/
The global setting named: standalone.static.context.path was set to: /i
The global setting named: standalone.context.path was set to: /ords
The global setting named: standalone.doc.root was set to: /oracle/ords_conf/global/doc_root
2024-02-22T03:00:32.213Z INFO Created folder /oracle/ords/logs
2024-02-22T03:00:32.214Z INFO The log file is defaulted to the current working directory located at /oracle/ords/logs
2024-02-22T03:00:32.432Z INFO Installing Oracle REST Data Services version 23.4.0.r3461619 in NON_CDB
2024-02-22T03:00:33.913Z INFO ... Verified database prerequisites
2024-02-22T03:00:34.311Z INFO ... Created Oracle REST Data Services proxy user
2024-02-22T03:00:34.912Z INFO ... Created Oracle REST Data Services schema
2024-02-22T03:00:35.474Z INFO ... Granted privileges to Oracle REST Data Services
2024-02-22T03:00:39.099Z INFO ... Created Oracle REST Data Services database objects
2024-02-22T03:00:50.174Z INFO Completed installation for Oracle REST Data Services version 23.4.0.r3461619. Elapsed time: 00:00:17.662
2024-02-22T03:00:50.244Z INFO Completed configuring PL/SQL gateway user for Oracle REST Data Services version 23.4.0.r3461619. Elapsed time: 00:00:00.66
2024-02-22T03:00:50.245Z INFO Log file written to /oracle/ords/logs/ords_install_2024-02-22_060032_21483.log
2024-02-22T03:00:50.725Z INFO HTTP and HTTP/2 cleartext listening on host: 0.0.0.0 port: 8080
2024-02-22T03:00:50.726Z INFO HTTPS and HTTPS/2 listening on host: 0.0.0.0 port: 8443
2024-02-22T03:00:50.746Z INFO Disabling document root because the specified folder does not exist: /oracle/ords_conf/global/doc_root
2024-02-22T03:00:50.746Z INFO Default forwarding from / to contextRoot configured.
2024-02-22T03:00:50.803Z SEVERE Could not start Standalone Mode because the listen port: 8080 is already in use by another process. Check if another instance of ords is already running/oracle:DBA>
When
2024-02-22T03:00:50.245Z INFO Log file written to /oracle/ords/logs/ords_install_2024-02-22_060032_21483.log
2024-02-22T03:00:50.725Z INFO HTTP and HTTP/2 cleartext listening on host: 0.0.0.0 port: 8080
2024-02-22T03:00:50.726Z INFO HTTPS and HTTPS/2 listening on host: 0.0.0.0 port: 8443
2024-02-22T03:00:50.746Z INFO Disabling document root because the specified folder does not exist: /oracle/ords_conf/global/doc_root
2024-02-22T03:00:50.746Z INFO Default forwarding from / to contextRoot configured.
2024-02-22T03:00:50.803Z SEVERE Could not start Standalone Mode because the listen port: 8080 is already in use by another process. Check if another instance of ords is already running

/oracle:DBA>ords --config /oracle/ords_conf serve

The main reason for the error is the port 8080 already in use by another processes.

The ports 80 and 8080 are common ports for Web servers (HTTP) to use. First check is ORDS is already running by checking the processes.

$ps -ef |grep ords

If you don’t see any processes, then there might be another processes using the 8080 port and run below command to check the process

$ netstat -lnptu |grep 8080
tcp 0 0 0.0.0.0:8080 0.0.0.0:* LISTEN

If you see any processes, you have few options,

1. Check is it safe to kill the application that is using port 8080, unless you know that is this application or processes I don’t recommend this
2. Change the ORDS port to any another port such as 8088

First run the below command to check any value set for

$ords config get standalone.http.port

Cannot get setting standalone.http.port because the setting is not found in database pool default located at /oracle/ords_conf/databases/default

As you see ORDS setting “standalone.http.port” doesn’t have any value set in the config. So we are safe to assign another port

$ords config set standalone.http.port 8081

Now you can try to bring up the ORDS using below command

ords --config /oracle/ords_conf serve

When ORDS processes comes online you should see below message

apped local pools from /oracle/ords_conf/databases:
/ords/ => default => VALID

2024-02-22T07:04:22.648Z INFO Oracle REST Data Services initialized