Oracle

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”.

What I understood from documentation is:

When not using the “FORCE” keyword, the two following commands behave the same way:

ALTER SYSTEM KILL SESSION ‘sid, serial#’ IMMEDIATE = ALTER SYSTEM KILL SESSION ‘sid, serial#’ TIMEOUT 0

Basically, it instructs Oracle to terminate a session and roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.

With the “FORCE” keyword alone, it terminates the session like “IMMEDIATE”, however waiting first for a timeout of 5 seconds.

With the “FORCE TIMEOUT 0”, then the connection is closed immediately, when there is some activity going on.

Here a summary of what I observed:

ALTER SYSTEM KILL SESSION command:Kill idle sessionKill ongoing select
… IMMEDIATEKiller:
System altered.
Killed session:
ORA-03135: connection lost contact
ORA-03114: not connected to ORACLE		Killer:
ORA-00031: session marked for kill
Killed session:
ORA-00028: your session has been killed
… FORCEKiller:
System altered.
Killed session:
ORA-03135: connection lost contact
ORA-03114: not connected to ORACLE		Killer:
System altered.
Killed session:
ORA-00028: your session has been killed
… TIMEOUT 0Killer:
System altered.
Killed session:
ORA-00028: your session has been killed
ORA-01012: not logged on		Killer:
ORA-00031: session marked for kill
Killed session:
ORA-00028: your session has been killed
… FORCE TIMEOUT 0;Killer:
System altered.
Killed session:
ORA-03135: connection lost contact
ORA-03114: not connected to ORACLE		Killer:
System altered.
Killed session:
ORA-03113: end-of-file on communication channel

Just for completion, if using “DISCONNECT SESSION” instead, then the session might just automatically failover if the parameters are correctly set.

Le me know if you have other inputs.

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…)

New My Oracle Support – updates and issues 1

This entry was posted in My Oracle Support Oracle on by

Oracle is taking seriously the confusion created with the abrupt migration to the new My Oracle Support (MOS) portal. Today there was a meeting between several VPs and other responsible and the Oracle ACE community. Thanks a lot!

We see many things are being improved and solved and the team is working hard to get the trust back from the community. We are here to help!

My top issues today (15.Dec.2025) with the new MOS are here:

Content related issues

  1. A list with “Last viewed Docs” + “Last searches” sections would very useful to find again documents. The list should include the last updated date in a column
  2. In “My Favorites” section, it is important to see the “last updated date” as a column. Old DocId may be removed. Folders/Categories were useful
  3. The “Favorites” were the base for the “Oracle Support HOT Topics” daily email, and this was where users can get information if any of our favorites was updated. This email seems not to exist anymore.
  4. Bugs are not part of the search – it is useful to know the bug exists, even if it is marked “not public”. This was the case in old MOS;
  5. Missing wget script to download patch – this was important, as usually we do not have browsers where we need the patch;
  6. SRs in “Pending Final Closure” do not appear any where (neither open, nor close). Should be in “need your attention” part. (Example SR 4-0001431240)
  7. There are still too many “Request Header Or Cookie Too Large” errors!
  8. Login timeout even when actively using the portal!
  9. Search results do not prioritize recent and most viewer content (example, search for “CPU October 2025” – it does not give KB795208 which is a top50 document)
  10. Internal links are not working (low priority)

UX related issues

  1. Choose sections to have in front page
    • My Favorites + My last viewed searches are important for me in first page
    • Archived Service Requests are not important to me in first page
  2. The Search box should be visible on every page, without need to return to the Home!
  3. Some text overflows the defined column and cannot be read (see KI35841)
  4. Instead of having two columns (left column with Title, DocID, Last Updated, Service | right with Doc text)
    • better would be two cells, top cell with content of the left column, followed by bottom cell with text
    • both cells would have 1296px width, which would give more place for tables (and the document title in top)

E-Mail related issues

  1. SR update emails do not have the “Title” of SR neither information the “Tenancy” the SR relates. As consultant I’ve about 50 CSI/Tenancies and this is important to prioritize my work
  2. SR Creation and Update emails do not have the information about the “Tenancy”.

I’m sending this information also by email to my contacts at MOS, hoping it helps prioritizing the issues.

Recognize Oracle Client installation

This entry was posted in Oracle Oracle 23ai and tagged client on by

On my environment script I want to recognize if an ORACLE_HOME belongs to a database or is just a client installation. Oracle does the following in its Oracle 26ai client runInstaller:

...
ORACLE_SERVER_PATH="${ORACLE_HOME}/inventory/Components21/oracle.server";
ORACLE_GSM_PATH="${ORACLE_HOME}/inventory/Components21/oracle.dslm";
# Check if the directory oracle.server exists
# If it exists, then the product is db otherwise is client
if [ -d $ORACLE_SERVER_PATH ]; then
...
  ${ORACLE_HOME}/perl/bin/perl -I${ORACLE_HOME}/perl/lib -I${ORACLE_HOME}/bin ${ORACLE_HOME}/bin/dbSetup.pl -J-D${CVU_OS_SETTINGS} "$@"   # bug 33519960 - support paths with whitespaces
else
  if [ -d $ORACLE_GSM_PATH ]; then
    ${ORACLE_HOME}/perl/bin/perl -I${ORACLE_HOME}/perl/lib -I${ORACLE_HOME}/bin ${ORACLE_HOME}/bin/dbSetup.pl "$@"      # bug 33519960 - support paths with whitespaces
  else
    ${ORACLE_HOME}/perl/bin/perl -I${ORACLE_HOME}/perl/lib -I${ORACLE_HOME}/bin ${ORACLE_HOME}/bin/clientSetup.pl "$@"      # bug 33519960 - support paths with whitespaces
  fi
fi

Basically here the logic:

  • Directory ${ORACLE_HOME}/inventory/Components21/oracle.server exists ➡️ Oracle Database installation
  • Directory ${ORACLE_HOME}/inventory/Components21/oracle.dslm exists ➡️ Global Data Services installation

On my script I simplified by checking:

  • File ${ORACLE_HOME}/bin/clientSetup.pl exists ➡️ Oracle Full client installation

Otherwise is a DB installation, as I don’t expect to use my script in any GDS server.

Extend partition from VMWare in Oracle Linux – part 1 2

This entry was posted in Linux Oracle and tagged Oracle Linux 9 on by

The client uses VMWare as virtualization platform for the Oracle Database VMs (yes, he is aware of the License implications).

The VM is running out of space in two partitions. The VMWare Admin extended the disks on their platform. Now I need to make this extension visible in Oracle Linux. This things we do once every 10 years, when versions, filesystems and tools already changed.

For this post in 2025, I’m using Oracle Linux 9, xfs filesystems. All operation are online, with databases running.

I divide this post in two parts:

Extend single partition of block device

oracle@vmware-vm01 MIG01C $ cat /etc/system-release 
Oracle Linux Server release 9.6

The partition tree is below, and I need to increase the /u02 from 100G to 300G. The disk is /dev/sdb

[root@vmware-vm02 ~]# lsblk 
NAME                             MAJ:MIN RM   SIZE RO TYPE MOUNTPOINTS
sda                                8:0    0   774G  0 disk 
├─sda1                             8:1    0   250M  0 part /boot/efi
├─sda2                             8:2    0     1G  0 part /boot
└─sda3                             8:3    0    77G  0 part 
  ├─ol_vmware-vm02-root          252:0    0    10G  0 lvm  /
  ├─ol_vmware-vm02-swap          252:1    0     4G  0 lvm  [SWAP]
  ├─ol_vmware-vm02-var_log_audit 252:2    0     1G  0 lvm  /var/log/audit
  ├─ol_vmware-vm02-u01           252:3    0    50G  0 lvm  /u01
  ├─ol_vmware-vm02-home          252:4    0     5G  0 lvm  /home
  ├─ol_vmware-vm02-var_log       252:5    0     2G  0 lvm  /var/log
  └─ol_vmware-vm02-var           252:6    0     5G  0 lvm  /var
sdb                                8:16   0   100G  0 disk 
└─sdb1                             8:17   0   100G  0 part 
  └─vg_u02-lv_u02                252:7    0   100G  0 lvm  /u02
sr0                               11:0    1 356.9M  0 rom
(more…)

TDE misconfigured and ORA-28353: failed to open wallet

This entry was posted in Oracle Oracle 19c Oracle 23ai Security TDE on by

I did script the TDE wallet configuration for my client.

  • Script 1 – Set the static parameters TABLESPACE_ENCRYPTION and WALLET_ROOT in the spfile.
  • Script 2 – Restart the database
  • Script 3 – Create the wallet, open the wallet, set the Masterkey, create the autologin wallet.

When running this 3rd script on a database it failed on the second command:

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY  "wallet_password";
keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "wallet_password";
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "wallet_password"
*
ERROR at line 1:
ORA-28353: failed to open wallet
(more…)

Recover missing Masterkey – the famous ORA-28374 2

This entry was posted in Oracle Oracle 19c Oracle 23ai Security on by

The client plugged a non-encrypted PDB into a CDB with TDE Encryption Wallet enabled. All works fine, we can read the data. However when trying to create a tablespace we get:

CDB1 SQL> create tablespace TBS_NEW;
Error starting at line : 1 in command -
create tablespace t1
Error report -
ORA-28374: typed master key not found in wallet
28374. 0000 -  "typed master key not found in wallet"
*Cause:    You attempted to access encrypted tablespace or redo logs with a typed master key not existing in the wallet.
*Action:   Copy the correct Oracle Wallet from the instance where the tablespace was created.

What is wrong?

(more…)

Encrypt the whole Oracle database online with TDE

This entry was posted in Oracle Oracle 19c Security on by

Some programs are part of Oracle ISV – Independent Software Vendors – program and include various types of Oracle licenses. This allows to install the application database in various Oracle configurations. At my customer, the software includes Advance Security Option Oracle license. This was the reason we decided, even before moving to the cloud, to encrypt the database.

Here is a summary on how to perform full online encryption (TDE) of a database. The process is quite simple, but there are known surprises you might want to avoid.

(more…)