Category: Uncategorized

Dropping AWR Snapshot is failing with ORA-13516: AWR Operation failed: AWR Schema is unavailable due maintenance

arcsdegeoLeave a comment

A very interesting case I have faced in an Oracle database environment patched with 19.27 RU, I tried dropping an AWR snapshot and it failed as shown below:

BEGIN

    DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => ‘62386’, high_snap_id => ‘62387’);

   END;

   / 


ERROR at line 1:
ORA-13516: AWR Operation failed: AWR Schema is unavailable due maintenance
ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 193
ORA-06512: at line 2

Solution:

Its unfortunately a bug !

// Index WRH$_SGASTAT_U in “WRH$_SGASTAT” table was in UNUSABLE state:

select * from dba_indexes where index_name=’WRH$_SGASTAT_U’;

// Drop the constraint:
alter table wrh$_sgastat drop constraint wrh$_sgastat_u;

// Turn off partition check:


alter session set events ‘14524 trace name context forever, level 1’;

// Add the constraint back:
alter table wrh$_sgastat add constraint wrh$_sgastat_u unique (dbid, snap_id, instance_number, name, pool, con_dbid, stattype) using index local tablespace SYSAUX;

// Turn on partition check:
alter session set events ‘14524 trace name context off’;

Then, Try dropping the AWR Snapshot again….should work.

BEGIN

   DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => ‘62386’, high_snap_id => ‘62387’);

   END;

   / 

PL/SQL procedure successfully completed.

What’s New in Oracle 19.28 [19c with July 2025 RU]

arcsdegeoLeave a comment

Oracle has just released their quarterly Oracle database security patches (july 2025). This quarter 6 security vulnerabilities were patched and new features and enhancements were introduced.

Lately (for the last few quarters) Oracle has introduced many good and new features in 19c [ so far the long term release] !

After Applying 19.28 RU a new set of system parameter were introduced:

SQL> show parameter mfa

NAME TYPE VALUE

mfa_duo_api_host string
mfa_oma_iam_domain_url string
mfa_sender_email_displayname string
mfa_sender_email_id string
mfa_smtp_host string
mfa_smtp_port integer 587

its related to : Oracle Database Multifactor Authentication (MFA) : https://blogs.oracle.com/database/post/new-mfa-jul2025

Another new enhancement is in Unified Auditing where a set of “Real Application Security” Audit actions were added to multiple pre-configured audit policies such as ORA_CIS_RECOMMENDATIONS

in 19.27 :

select * from AUDIT_UNIFIED_POLICIES where policy_name=’ORA_CIS_RECOMMENDATIONS’ and AUDIT_OPTION_TYPE=’XS ACTION’;

in 19.28:

select * from AUDIT_UNIFIED_POLICIES where policy_name=’ORA_CIS_RECOMMENDATIONS’ and AUDIT_OPTION_TYPE=’XS ACTION’;

Oracle 26ai Enhancement To  Monitor DATA PUMP Operations Through New Built-In Views

arcsdegeoLeave a comment

New and nice feature introduced in 23ai is the ability to view the progress of datapump operations through database views:

GV$DATAPUMP_PROCESS_INFO

GV$DATAPUMP_PROCESSWAIT_INFO

GV$DATAPUMP_SESSIONWAIT_INFO

GV$DATAPUMP_JOB

To simulate:

I will create a dummy account called “tim” and will grant the account “DBA” role.

SQL> alter session set container=FREEPDB1;

Session altered.

SQL> create user tim identified by tim123;

User created.

SQL> grant DBA to tim;

Grant succeeded.

SQL> alter user tim default role all;

User altered.

And I will define a directory :

SQL> create directory tmp as ‘/tmp’;

I will then execute the following export datapump command for simualtion (will take full pluggable database export backup):

expdp tim/tim123@//localhost:1521/FREEPDB1 directory=tmp FULL=Y dumpfile=test_dump.dmp logfile=EXP_TEST.log

The following view will show the account used for the export and operating system process id’s (SPID):

Wait event as shown below is “db file sequential read” for the datapump job:

Oracle database 26ai  unified_audit_trail_exclude_columns parameter

arcsdegeoLeave a comment

A very intresting parameter  unified_audit_trail_exclude_columns is introduced in 23.6 release. This parameter has no official documentation about it so I played with it to understand what is used for.

The parameter by default is set to “none”. However, the parameter can accept one of the following values:  none, dp_clob_parameters1,  rls_info, sql_binds,  sql_text

Not sure why this parameter is introduced, what is the objective this parameter will serve.

I have set the parameter to sql_text as shown below:

SQL> alter system set unified_audit_trail_exclude_columns=’sql_text’;

If you create a dumy user and checking the unified audit log after setting the parameter unified_audit_trail_exclude_columns to “sql_text”:

SQL> create user c##dummy99 identified by dummy123;

Then, checking audit logs:

SQL> select OS_USERNAME,DBUSERNAME,action_name,SQL_TEXT,OBJECT_NAME from unified_audit_trail where ACTION_NAME=’CREATE USER’ order by event_timestamp desc;

So, the parameter effectively took place and the SQL_TEXT column is empty and not showing the SQL statement for the user creation, the event itself is of course recorded as shown in the above picture.
 

Newly Introduced parameters in Oracle Database 19.27 RU [ Oracle April 2025 Critical Patch Update]

arcsdegeoLeave a comment

In oracle database 19c release update (19.27) , two new parameters were introduced in the database system:

OPTIMIZER_CAPTURE_SQL_QUARANTINE


https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_CAPTURE_SQL_QUARANTINE.html

OPTIMIZER_USE_SQL_QUARANTINE


https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_USE_SQL_QUARANTINE.html

It enables the automatic creation of SQL Quarantine configurations.

Oracle 19.26 RU changed DB_LOST_WRITE_PROTECT parameter

arcsdegeoLeave a comment

In Oracle January 2025 RU patches, a new and good change has been introduced. The default parameter DB_LOST_WRITE_PROTECT value changed from none to “AUTO” after applying the RU. This is a good move to ensure data protection and detecting problems early. I have talked about database lost write issue in an old blog post: https://geodatamaster.com/2020/09/04/oracle-database-lost-write-detection-recovery-options/

reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DB_LOST_WRITE_PROTECT.html

Oracle 26ai vector column data type

arcsdegeoLeave a comment

First create a database account called “vector” in FREEPDB1 pluggable database as follows:

SQL> CREATE USER VECTOR IDENTIFIED BY VECTOR123

DEFAULT TABLESPACE “USERS”

TEMPORARY TABLESPACE “TEMP”;

GRANT “DB_DEVELOPER_ROLE” TO “VECTOR”;

ALTER USER “VECTOR” DEFAULT ROLE ALL;

ALTER USER “VECTOR” QUOTA UNLIMITED ON USERS;

Starting in oracle 23ai you can define vector data type column in different ways:

23ai > sqlplus “vector/vector123″@FREEPDB1

SQL> create table t1 (v vector);

Table created.

SQL> desc t1

 Name                                      Null?    Type

 —————————————– ——– —————————-

 V                                                  VECTOR(*, *)

The above column data type corresponds to —-> VECTOR(*, *) which means embedding vector data will be aribitary in terms of dimension and data type.

SQL> create table t2 (v vector(235,*));

Table created.

SQL> desc t2;

 Name                                      Null?    Type

 —————————————– ——– —————————-

 V                                                  VECTOR(235, *)

The above means acceppted vector data must have 235 dimensions with arbitary data type.

SQL>  create table t3 (v vector(444,int8));

Table created.

SQL> desc t3

 Name                                      Null?    Type

 —————————————– ——– —————————-

 V                                                  VECTOR(444, INT8)

The above means acceppted vector data must have 444 dimensions with data type of INT8. Accepted data types for vector data are (INT8, FLOAT32,FLOAT64), worth stating that FLOAT32 is the default data type if format is not specified.

SQL> create table galaxies (id number , name varchar2(50), doc varchar2(500), embedding vector);

Table created.

SQL> insert into galaxies values( 9 , ‘NGC1073’, ‘NGC 1073 is a barred spiral galaxy in Cetus constellation.’, ‘[0,1,1,0,0]’);

1 row created.

SQL> commit;

Important Remark: restrictions with VECTOR column data type:

Can’t be used with external tables, IOTs, cluster tables, global temporary tables, primar key, foreing key, unique constraint,….etc.

  • Of course you can add multiple vector data type columns on the same table, no restriction is imposed on having multiple columns with vector data type.
  • The maximum number of dimensions a vector data type can have is 65,536.

Datapatch error patch XXXX: XML descriptor does not exist in either the file system or SQL registry

arcsdegeoLeave a comment

In one of the environments (19c) after successfully applying Oracle patches at binary level, the following error was thrown when datapatch command was executed:

Error: prereq checks failed!
patch XXXX: XML descriptor does not exist in either the file system or SQL registry
Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log
/opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_16524_2025_01_31_14_35_22/sqlpatch_invocation.log
for information on how to resolve the above errors.

so the tool was searching for an old patch that doesn’t exist anymore under $ORACLE_HOME/sqlpatch directory.

— Possible Solutions:

Possible solution 1: if the patch number XXXX is related to old java patch, then apply latest OJVM patch…you can execute the following command to check:
$ORACLE_HOME/OPatch/opatch lspatches

Possible solution 2: copy the missing patch folder from another database environment to this environment (the environment you are facing the error) , under this folder: $ORACLE_HOME/sqlpatch

Oracle 26ai new feature: INSERT INTO VALUES ACCEPT MULTIPLE ROWS

arcsdegeoLeave a comment

very useful new feature in Oracle 23ai is the ability to insert multiple row values in one shot, unlike older releases where you need to repeat the SQL command multiple times for each row insertion.

To illustrate:

create table sh1.dummy (fname varchar2(20));

insert into sh1.dummy values (’emad’),
(‘ricardo’),
(‘john’);

Then, you can query the whole table to verify:

select * from sh1.dummy;