Yearly Archives: 2026

Database specific password wallet with 26ai client: the new SEPS_WALLET_LOCATION parameter

This entry was posted in Oracle AI 26ai Security on by

One inconvenient I had in the past with the usage of password wallets for Oracle users was their maintenance. With the years, there was just a lot of old entries. At the end you would do a script that would test all credentials and delete the ones which did not work or there was no more network alias equivalent.

In OCI, to connect to Autonomous Databases, there is since few years a possibility to have a wallet parameter defined at the network connection or tnsnames, using “(SECURITY=(wallet_location=/home/oracle/wallets/databases)))” – sometimes also seen using “my_wallet_directory”. But this only works for TLS connections and using certificates.

At my client I wanted to have a wallet per database, so that at the time of decommissioning, we just delete the whole wallet.

After looking around, I finally found out that using a 26ai client there are new parameters for the connection string and one of them is SEPS_WALLET_LOCATION, which allows to specify the location of the wallet for a specific entry.

This way I can do:

# My DB is ANJO_DB
$ echo $ORACLE_SID
ANJO_DB

# Create wallet
orapki wallet create -wallet $ORACLE_BASE/admin/$ORACLE_SID/wallet -pwd <wallet_pwd> -auto_login_local

# Add credential
orapki secretstore create_credential -wallet $ORACLE_BASE/admin/$ORACLE_SID/wallet -pwd <wallet_pwd> -connect_string $ORACLE_SID -username sys -password <sys_pwd>

# Add the wallet location in tnsnames
$ grep $ORACLE_SID $TNS_ADMIN/tnsnames.ora
ANJO_DB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anjovm1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ANJO_DB.WSL.HOME))(security=(SEPS_WALLET_LOCATION=/u00/app/oracle/admin/ANJO_DB/wallet)))

# Check with tnsping
tnsping ANJO_DB

TNS Ping Utility for Linux: Version 23.26.2.0.0 - Production on 30-JUN-2026 14:10:10

Copyright (c) 1997, 2026, Oracle.  All rights reserved.

Used parameter files:
/u00/app/oracle/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anjovm1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ANJO_DB.WSL.HOME))(security=(SEPS_WALLET_LOCATION=/u00/app/oracle/admin/ANJO_DB/wallet)))
OK (10 msec)

# Connect
$ sqlplus /@ANJO_DB
SQL*Plus: Release 23.26.2.0.0 - Production on Tue Jun 30 14:11:40 2026
Version 23.26.2.0.0

Copyright (c) 1982, 2026, Oracle.  All rights reserved.

Last Successful login time: Tue Jun 30 2026 13:52:44 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.30.0.0.0

SQL>

The documentation about this and other new elements of the tnsnames.ora file in Oracle 26ai are at https://docs.oracle.com/en/database/oracle/oracle-database/26/netrf/local-naming-parameters-in-tns-ora-file.html

ASM diskgroup space calculator for ExaCC

This entry was posted in Cloud Engineer Systems ExaCC ideas OCI Oracle and tagged OCI on by

In ExaCC and probably on Exadata in OCI it is sometimes a bit complex to calculate how much space you need to add to the ASM, as you can only change the total space and the distribution.

When you just want to add 10TB (or GB) space to DATA diskgroup, you need to play a while or do an excel.

With the calculator below, you can now do it easily:

Edit any cell — usable, raw (×3), % of total, or the total row itself. Other values update accordingly.

Lock Disk group Usable space Raw space (×3) % of total
DATA
TB TB %
RECO
TB TB %
SPARSE
TB TB %
Total
TB TB 100%

When looking inside asmcmd, you see the “Raw space”. This is how much from disk space is used. The Usable space is what the users see and what you need to consider.

How to create an audit policy based on user profile? Example using a context

This entry was posted in Audit ideas Oracle on by

The customer wanted to have some audit policies that would be applied only to users who have a certain profile – lets say the profile APP_PROFILE.

Using unified auditing, it is possible to enable a policy for certain roles using AUDIT POLICY policy_name BY USERS WITH GRANTED ROLES role_name;

And you can use context on audit policies using CREATE AUDIT POLICY policy_name ACTIONS <action_list> WHEN q'!SYS_CONTEXT('<context_name>','<context_attr>') = <value>!' EVALUATE BY SESSION;

The user profile is unfortunately not automatically available in the default USERENV context.

The way to have audit policy on users with a certain profile, needs to go via a logon trigger, which either sets a ROLE to a user, or sets a CONTEXT.

I’ve decided on the latter option. For this I need few steps – this is done inside the PDB:

1. Create a context – AUDIT_CONTEXT -, which must say what program is allowed to set it

CREATE OR REPLACE CONTEXT audit_context USING audsys.proc_set_audit_flag;

2. Create the procedure referenced above – AUDSYS.PROC_SET_AUDIT_FLAG, that sets the context

CREATE OR REPLACE PROCEDURE audsys.proc_set_audit_flag IS
    v_profile VARCHAR2(128);
  BEGIN
    SELECT profile INTO v_profile
      FROM dba_users
     WHERE username = SYS_CONTEXT('USERENV', 'SESSION_USER');

    IF v_profile = 'APP_PROFILE' THEN
    dbms_output.put_line('Set context AUDIT Y');
      DBMS_SESSION.SET_CONTEXT('AUDIT_CONTEXT', 'AUDIT', 'Y');
    ELSE
      dbms_output.put_line('Set context AUDIT N');
      DBMS_SESSION.SET_CONTEXT('AUDIT_CONTEXT', 'AUDIT', 'N');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_SESSION.SET_CONTEXT('AUDIT_CONTEXT', 'AUDIT', 'N');
END proc_set_audit_flag;
/

3. Grant access to the procedure

The procedure will be called by low privilege users. So the procedure needs access to DBA_USERS, this is done via an intermediary role ROLE_SET_AUDIT_FLAG; and any user needs to be able to execute the procedure, so it needs to be available to PUBLIC.

CREATE ROLE IF NOT EXISTS role_set_audit_flag;
GRANT select ON dba_users TO role_set_audit_flag;
GRANT role_set_audit_flag TO PROCEDURE audsys.proc_set_audit_flag;
GRANT execute ON audsys.proc_set_audit_flag TO public;

4. Create a logon trigger – TRIGGER_SET_AUDIT_CONTEXT – calling this procedure

CREATE OR REPLACE TRIGGER trigger_set_audit_context
  AFTER LOGON ON DATABASE
BEGIN
  audsys.proc_set_audit_flag;
EXCEPTION
  WHEN OTHERS THEN NULL;
END;
/

5. Use this construct within an audit policy – AUDIT_APP_USERS

CREATE AUDIT POLICY audit_app_users 
ACTIONS PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE 
WHEN q'!SYS_CONTEXT('AUDIT_CONTEXT', 'AUDIT') = 'Y'!' EVALUATE PER SESSION;

AUDIT POLICY audit_app_users;

Conclusion

Now, when I connect with a user having the APP_USER profile, I can see I’ve the right context

MIGUEL@ANJO.PDB1> SELECT * FROM SESSION_CONTEXT;

       NAMESPACE    ATTRIBUTE    VALUE
________________ ____________ ________
AUDIT_CONTEXT      AUDIT      Y

1 row selected.

And on the audit trail we can now see the entries from that policy:

SYS@ANJO.PDB1> select  EVENT_TIMESTAMP, DBUSERNAME, UNIFIED_AUDIT_POLICIES from audsys.unified_audit_trail order by 1 desc fetch first 1 rows only;

                  EVENT_TIMESTAMP    DBUSERNAME            UNIFIED_AUDIT_POLICIES                                                                            
_________________________________ _____________ _________________________ 
29-APR-2026 16:03:46.841520000    MIGUEL           AUDIT_APP_USERS

Oracle Update Advisor in 19c – tutorial of the new easy way to find and download Oracle DB release updates

This entry was posted in Oracle Oracle 19c Oracle AI 26ai Patchset Update and tagged Release Updates on by

Oracle released a new version of its “Oracle Update Advisor”. This tool is part of Oracle AI Database 26ai, allows to anyone to check if their Oracle installation is up to date and download a new Gold Image updated to the latest version.

When using it with Oracle 19c, you need to download the tool “dbcactl“, which complements existing Database Creation Assistant – dbca – with the “managePatches” option – this is the Oracle Update Advisor.

To download dcbactl, you need to go to https://updates.oracle.com/download/6789999.html . While latest available version visible is 26.2.1, it is not downloadable. I did my tests using the version 25.4.1.

(more…)

Alter system kill session “force timeout 0” ? new form of “kill immediate”?

This entry was posted in Oracle Oracle AI 26ai on by

The other day I saw that besides the catcon.pl, there is a catcon_kill_sess_gen.sql:

ls -1 $ORACLE_HOME/rdbms/admin/catcon*
/u00/app/oracle/product/19.30.0/rdbms/admin/catcon_kill_sess_gen.sql
/u00/app/oracle/product/19.30.0/rdbms/admin/catcon.pl
/u00/app/oracle/product/19.30.0/rdbms/admin/catcon.pm
/u00/app/oracle/product/19.30.0/rdbms/admin/catconst.pm
/u00/app/oracle/product/19.30.0/rdbms/admin/catcont.sql

When looking at this file, I noticed it kills catcon session using a “ALTER SYSTEM KILL SESSION ‘sid, serial#’ FORCE TIMEOUT 0”, except if on Oracle 12.1, where still does “IMMEDIATE” instead:

This new syntax “FORCE TIMEOUT x” is first described in Oracle AI 26ai DB documentation (but it seems to work from version 12.2). Basically it theoretically allows the draining of sessions by setting a timeout until the session terminates some task, before killing it.

There are two new elements here: “FORCE” and “TIMEOUT x”.

(more…)

SQL and how Oracle filter the resultset

This entry was posted in Oracle Oracle AI 26ai on by

While reading the very interesting Oracle AI Database 26ai New Features Guide, I got to know that there is a new “QUALIFY” filter clause (there is also a new “FILTER” clause). However, what I found more interesting in the documentation was this sentence:

The order of operations is as follows: FROM → WHERE → GROUP BY → HAVING → WINDOW → QUALIFY → DISTINCT → ORDER BY → FETCH FIRST.

This is somehow a back to the roots and basic SQL information that I had in the back of my head, but never though really end-to-end about.

Here an example of query using all except DISTINCT elements of the operations order:

SELECT region, city, AVG(temperature), AVG(AVG(temperature)) OVER w AS avg_temp_region 
    FROM city, regions 
    WHERE city.region_id = region.region_id 
    GROUP BY region, city 
    HAVING region NOT LIKE 'LISBON' 
    WINDOW w AS (PARTITION BY region)
    QUALIFY avg_temp_region > 10
    ORDER BY region
    FETCH FIRST 5 ROWS ONLY;

I don’t think I could write a SQL like that, but at least now I know how it looks like.

How to Patch/Upgrade Java (JDK and JRE) in both Oracle Home and OEM Agent 13.5 home to latest certified version

This entry was posted in Enterprise Manager Oracle Oracle 19c Patchset Update Security on by

A vulnerability scan from the customer pointed out many problems due to old patch backups and old JDK versions installed in a Oracle VM.

Many of the problems were with the files:

/u01/app/oracle/product/oem13c/agent/agent_13.5.0.0.0/oracle_common/jdk/jre/lib/rt.jar
/u01/app/oracle/product/19.0.0/dbhome_1/jdk/jre/lib/rt.jar

What I learned:

  • OEM Agent 13.5 default java version is 1.8.0_261, also after patching to the latest RU!
  • Oracle OPatch has its own JRE and this is not updated when applying latest JDK patch for Oracle Home
  • Upgrade JDK in Oracle Home is online

What I already knew:

Below how I “cleanup” the vulnerabilities at my customer VM. This instructions are for java version 1.8.0_481, latest certified for Oracle in January 2026 :

(more…)

Follow the progress of a PDB remote cloning

This entry was posted in cloning Multitenant Oracle Oracle 19c on by

For a PDB migration, I’ve configured a Refresh PDB clone.

SQL> CREATE PLUGGABLE DATABASE P1QXPTO from P1QXPTO@C1Q_OLDCDB REFRESH MODE EVERY 2 HOURS;

The PDB is 16TB and copying will take a few hours. To get the progress, it is not so straight forward:

  • there is no size in V$PDBs until the end
  • the ASM space is reserved from the beginning
  • there are no files in V$DATAFILE or V$ASM_FILE during the copy

The only place to follow the PDB clone is using V$SQL_LONGOPS, as explained at KB135098 – How to Monitor PDB Clone / Move On ‘Create Pluggable Database’ with ‘COPY’ Clause Statement Execution.

(more…)