Explore Oracle Stuff

Monday, April 13, 2026

Bulk Insert With PDML

In this article I have shown how to prove benefit of using append hint with PDML enabled.

This is benefical for datawarehoue load job.

This test has been done in oracle database 19c and 26 ai to show the change in feature

--I have created a script which does following

1. Create an empty table with same structure using CTAS but with 1=2 condition

2. Load data from a big table with append parallel hint

3. show execution plan which clearly says PDML is disabled

I have seen many cases where developer thinks parallelism is enabled for their bulk insert.

This is not true as PDML is always disabled by default.

SQL> @test_pdml.sql

11:53:24 SQL> set pagesize 3000

11:53:24 SQL> create table cisadm.ci_cc_bkp tablespace cists_01 as select * from cisadm.ci_cc where 1=2;

Table created.

11:53:24 SQL> insert into cisadm.ci_cc_bkp select /*+ append parallel */ * from cisadm.ci_cc;

125347818 rows created.
12:03:37 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 13bpyjxguarsc, child number 0
-------------------------------------
insert into cisadm.ci_cc_bkp select /*+ append parallel */ * from
cisadm.ci_cc
Plan hash value: 547790614
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | |
| 1 | LOAD AS SELECT | CI_CC_BKP | | 2070K| 2070K| 2070K (0)|
| 2 | PX COORDINATOR | | | 73728 | 73728 | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 125M| | | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 125M| 256K| 256K| 4096K (0)|
| 5 | PX BLOCK ITERATOR | | 125M| | | |
|* 6 | TABLE ACCESS FULL | CI_CC | 125M| | | |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 15
- PDML is disabled in current session
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

32 rows selected.
In this case we can see that Select is using parallel query slave but hint clearly says PDML is disabled.
What is the effect of APPEND here?
Basically it bypasses buffer cache and load data directly into table above high water mark.Once it is committed high water mark is adjusted.
This can be proved like below
get the OS process id of the session which is executing SQL.This can be found using below
connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12

grep -i "direct path read" ccbx_p001_71004.trc
WAIT #140032935645648: nam='direct path write' ela= 270 file number=75 first dba=1147016 block cnt=8 obj#=4538084 tim=432713792172
WAIT #140032935645648: nam='direct path write' ela= 974 file number=75 first dba=4062721 block cnt=15 obj#=4538084 tim=432730881085
WAIT #140032935645648: nam='direct path write' ela= 94 file number=60 first dba=2092031 block cnt=1 obj#=4538084 tim=432732169887
WAIT #140032935645648: nam='direct path write' ela= 315 file number=123 first dba=2764673 block cnt=15 obj#=4538084 tim=432732178566
WAIT #140032935645648: nam='direct path write' ela= 347 file number=123 first dba=3763601 block cnt=15 obj#=4538084 tim=432742177527

We can also check like below from ash
select sample_time,SESSION_ID,SESSION_SERIAL#,EVENT from v$active_session_history where event like '%direct path write%'
SQL> /
SAMPLE_TIME SESSION_ID SESSION_SERIAL# EVENT
------------------------------ ---------- --------------- ----------------------------------------------------------------
13-APR-26 01.34.58.375 PM 81 32622 direct path write
13-APR-26 01.34.41.367 PM 81 32622 direct path write
13-APR-26 01.34.34.363 PM 81 32622 direct path write

It takes almost 7 mis to load the data into a table.
Now we will enable parallel DML in session level and see whether we can count records inserted
12:45:45 SQL> alter session enable parallel dml;
Session altered.
12:45:45 SQL> insert into cisadm.ci_cc_bkp select /*+ append parallel */ * from cisadm.ci_cc;
125347818 rows created.
12:47:08 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 13bpyjxguarsc, child number 2
-------------------------------------
insert into cisadm.ci_cc_bkp select /*+ append parallel */ * from
cisadm.ci_cc
Plan hash value: 2226083196
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | 73728 | 73728 | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 125M| | | |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| CI_CC_BKP | | 12M| 12M| 2070K (0)|
| 4 | OPTIMIZER STATISTICS GATHERING | | 125M| 256K| 256K| 4096K (0)|
| 5 | PX BLOCK ITERATOR | | 125M| | | |
|* 6 | TABLE ACCESS FULL | CI_CC | 125M| | | |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 144 because of degree limit
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Now it completed in 2 minutes and there is a slight change in the plan operation from

LOAD AS SELECT to LOAD AS SELECT (HYBRID TSM/HWMB)

This has been explained in detailed in below blog which says temporary segments are created and merged together to support parallel DML.
https://blogs.oracle.com/optimizer/space-management-and-oracle-direct-path-load#:~:text=As%20the%20name%20suggests%2C%20this%20is%20a,segment%20merge%20and%20high%20water%20mark%20brokering
We also see below error as in 19c we can not read in the same session where parallel DML is enabled.This restriction has been lifted in 26ai

11:52:38 SQL> select count(*) from cisadm.ci_cc_bkp;
select count(*) from cisadm.ci_cc_bkp
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

So if you have a code which update another tables based on some updated data we just did then transaction will fail.

Now we did same thing in oracle 26 ai but this time with hint enable_parallel_dml
13:28:52 SQL> set pagesize 3000
13:28:52 SQL> alter session set container=PDBTEST;
Session altered.
13:28:52 SQL> create table cisadm.ci_cc_bkp tablespace cists_01 as select * from cisadm.ci_cc where 1=2;
Table created.

13:28:52 SQL> insert into cisadm.ci_cc_bkp /*+ enable_parallel_dml */ select /*+ append parallel */ * from cisadm.ci_cc;
125347818 rows created.
13:35:34 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cawjwksmavw38, child number 0
-------------------------------------
insert into cisadm.ci_cc_bkp /*+ enable_parallel_dml */ select /*+
append parallel */ * from cisadm.ci_cc
Plan hash value: 547790614
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | |
| 1 | LOAD AS SELECT | CI_CC_BKP | | 2071K| 2071K| 2071K (0)|
| 2 | PX COORDINATOR | | | 73728 | 73728 | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 113M| | | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 113M| 256K| 256K| 4096K (0)|
| 5 | PX BLOCK ITERATOR | | 113M| | | |
|* 6 | TABLE ACCESS FULL | CI_CC | 113M| | | |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- automatic DOP: Computed Degree of Parallelism is 8 because of degree limit
- PDML is disabled in current session
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

33 rows selected.
13:35:34 SQL> alter session enable parallel query;
Session altered.
13:35:34 SQL> select count(*) from cisadm.ci_cc_bkp;
COUNT(*)
----------
125347818
13:38:02 SQL> commit;
Commit complete.

So we see hint report says PDML is disabled.

This works fine when we use both hint parallel enable_parallel_dml together..

insert /*+ enable_parallel_dml parallel */ into cisadm.ci_cc_bkp

select /*+ append parallel */ * from cisadm.ci_cc

Hope you have learnt something useful

Sunday, March 8, 2026

Easy peasy non-cdb migration to cdb

Problem statement:Lets assume you want to migrate your existing non-cdb database and plug into a cdb running in same database server and storage.At the same time you want to minimize downtime of this migration as much as possible. This can be achieved using a feature called refreshable pdb introduced in Oracle 12.2

Test environment:

OS:Oracle Enterprise Linux 8
Database version :Oracle Enterprise edition 19.26

Database size:5 TB

Storage:ASM

On source

create user dblinkuser identified by <password> default tablespace users temporary tablespace temp;
grant create session,create pluggable database,select_catalog_role to dblinkuser;
grant read on sys.enc$ to dblinkuser;

On target

create database link clonepdb connect to dblinkuser identified by <password> using 'pdb1';
CREATE PLUGGABLE DATABASE pdb1 FROM pdb1@clonepdb REFRESH MODE EVERY 10 MINUTES;
SQL> CREATE PLUGGABLE DATABASE pdb1 FROM pdb1@clonepdb REFRESH MODE EVERY 10 MINUTES;
Pluggable database created.

Pre-requisite:-Make sure during this process archivelog is not deleted during this process.So if you have a scheduled archivelog backup script which takes backup and delete archivelog then disable it during that process.

For 5 TB database it took only 2 hrs.

Let me try to summarize the steps here

1.It remotely connects to source non-cdb database using dblink created above and copy datafiles.

2.It creates a scheduler job in target cdb database

2.Once pdb creation is completed it remains in mount mode and tries to sync data periodically based on your statement "REFRESH MODE EVERY N MINUTES".During refresh it tries to reads online redo from last applied SCN using some scheduler jobs.If that is found in redo log then it creates partial archivelog from that redo and changes are pushed back to target pdbs.Once it is synced partial archivelogs are deleted.

If you check source non-cdb database alert log you can find similar alerts like below

Deleted Oracle managed file +RECO/pdb1/partial_archivelog/2026_03_08/thread_1_seq_184.451.1227321403

Since refresh is based on dblink which again based on tnsname used on the definition of dblink,so jst comment out tns entry of the source database and you can see following error in last log of target cdb.

Errors in file /u01/app/odaorabase/oracle/diag/rdbms/cdbtest/CDBTEST/trace/CDBTEST_j000_21417.trc:
ORA-12012: error on auto execute of job "SYS"."pdb1_2438444115_REFRESH"
ORA-17627: ORA-01109: database not open
ORA-17629: Cannot connect to the remote database server
ORA-06512: at "SYS.DBMS_SQL", line 2995

so,now you came to know about the scheduler job which is created in target pdb.

On the target pdb run a final manual refresh to apply any last-minute changes

SQL>ALTER PLUGGABLE DATABASE pdb1 REFRESH;

Convert the target PDB from a refreshable clone to a regular, standalone PDB by setting its refresh mode to NONE using below command

SQL>ALTER PLUGGABLE DATABASE pdb1 REFRESH MODE NONE;

Now open the target pdb database

SQL>ALTER PLUGGABLE DATABASE pdb1 open;

Check the status of target pdb

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBTEST READ WRITE
pdb1 READ WRITE

For me it was opened with below warning

Warning: PDB altered with errors.

Lets see alert log what is this warning about.Same can also be found using below query

select type, cause, message from PDB_PLUG_IN_VIOLATIONS where name='pdb1' and status != 'RESOLVED';

pdb1(4) Error Violation: Non-CDB to PDB, Cause: PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run., Action: Run noncdb_to_pdb.sql.

pdb1(4) Error Violation: SQL Patch, Cause: Interim patch 29213893/25940729 (DBMS_STATS FAILING WITH ERROR ORA-01422 WHEN GATHERING STATS FOR USER$ TABLE): Installed in the CDB but not in the PDB, Action: Call datapatch to install in the PDB or the CDB

So I have 2 action items here

I need to run run noncdb_to_pdb.sql and apply datapatch

set ORACLE environment

$ORACLE_HOME/OPatch/datapatch -verbose

SQL>ALTER SESSION SET CONTAINER=pdb1;

SQL>@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Make sure all components are valid for target pdb.In my case below component was invalid

select COMP_NAME,VERSION_FULL,STATUS from dba_registry;
COMP_NAME VERSION_FULL STATUS
---------------------------------------- ------------------------------ ---------------------------------
Oracle Database Catalog Views 19.26.0.0.0 VALID
Oracle Database Packages and Types 19.26.0.0.0 INVALID
Oracle XML Database 19.26.0.0.0 VALID
Oracle Workspace Manager 19.26.0.0.0 VALID
JServer JAVA Virtual Machine 19.26.0.0.0 VALID
Oracle XDK 19.26.0.0.0 VALID
Oracle Database Java Packages 19.26.0.0.0 VALID
Oracle Text 19.26.0.0.0 VALID
Oracle Multimedia 19.26.0.0.0 VALID
OLAP Analytic Workspace 19.26.0.0.0 VALID
Oracle OLAP API 19.26.0.0.0 VALID
Spatial 19.26.0.0.0 VALID
Oracle Label Security 19.26.0.0.0 VALID
Oracle Database Vault 19.26.0.0.0 VALID
Oracle Real Application Clusters 19.26.0.0.0 VALID

I just executed utlrp.sql under $ORACLE_HOME/rdbms/admin to compile invalid components and after that it became valid.There are some limitations of this feature which has been described in below articles.

I hope you have learned something useful.

Reference:-

https://dohdatabase.com/2024/08/28/a-few-details-about-using-refreshable-clone-pdb-for-non-cdb-to-pdb-migration/

https://dohdatabase.com/2023/09/27/how-to-upgrade-to-oracle-database-19c-and-migrate-to-a-pdb-using-refreshable-clone-pdbs/

Saturday, February 28, 2026

Transaction priority in oracle database 23 ai

This has been tested in Oracle Database 23ai Free Release 23.0.0.0.0 using sample hr schema.

Problem statement:Suppose management decided to increase salary of job clerk whose joining date is less then 01-JAN-2019.Now one of the HR was trying to update through an application but mistakenly chosen wrong year as 2020 but she did not commit by pressing confirmation button. Now the rows are locked as commit is not performed.Same has been tried from a new session but that hangs forever as previous session was not commited. At this stage DBA can be involved and previous session can be killed so that new session can commit.

This problem can be easily solved in 23ai if we set parameter txn_priority and priority_txns_medium_wait_target in session and pdb level

connect to hr schema and pdb.Issue below command which says medium transaction will wait for 180 seconds in case any low priority transaction blocks medium transaction.

alter system set priority_txns_medium_wait_target=180;

From session 1 set transaction priority as below

alter session set txn_priority='LOW';

--Issue below command to increase salary of those employees whose hire date is less then 1st Jan 2020 and belongs to JOB clerk

update employees set salary=salary*1.2 where job_id='ST_CLERK' and hire_date<to_date('01-JAN-2020','DD-MON-YYYY');

--Do not commit here

Lets say someone from another session realized hire date should be less than 01-JAN-2019.So he execute below statement with medium priority

--Session 2 starts here

alter session set txn_priority='MEDIUM';

update employees set salary=salary*1.2 where job_id='ST_CLERK' and hire_date<to_date('01-JAN-2019','DD-MON-YYYY');

--Now session 2 will wait until it reaches180 seconds

After wait of 180 seconds low transaction will be rolled back automatically

During this time open 3rd session and execute below query

select sid,event,seconds_in_wait,blocking_session from v$session where event like '%enq%';

SID EVENT SECONDS_IN_WAIT BLOCKING_SESSION

---------- ---------------------------------------------------------------- --------------- ----------------

49 enq: TX - row lock (MEDIUM priority) 78 35

Once 180 second is past go to session 1 and try to commit.You will see following error

SQL> commit;

commit

ERROR at line 1:

ORA-63302: Transaction must roll back

ORA-63300: Transaction is automatically rolled back since it is blocking a higher priority transaction from another session.

Help: https://docs.oracle.com/error-help/db/ora-63302/

This proves that since medium transaction has high priority it has rolled back after wait of 180 seconds.

Now you can issue commit in 2nd session to make that change permanently.

I hope you have learned something useful which can be used in your application logic

Saturday, February 7, 2026

OEM 13.5 installation using silent mode

1. Satisfy pre-requisites for installing OMS

Make sure following packages exist

To check whether any package exist use below command from root user

rpm -qa <package name>

Platform	64-Bit Packages for 64-Bit Platform
Oracle Linux 9 Red Hat Linux 9	· binutils-2.35.2-17.0.1.el9 · gcc-11.2.1-9.4.0.2.el9 · gcc-c++-11.2.1-9.4.0.2.el9 · glibc-2.34-28.0.1.el9_0.2.x86_64 · glibc-devel-2.34-28.0.1.el9_0.2.x86_64 · libaio-0.3.111-13.el9.x86_64 · libgcc-11.2.1-9.4.0.2.el9.x86_64 · libnsl-2.34-83.0.2.el9.x86_64 · libstdc++-11.2.1-9.4.0.2.el9.x86_64 · libstdc++-devel-11.2.1-9.4.0.2.el9.x86_64 · make-4.3-8.el9 · motif-2.3.4-25.el9.x86_64 · motif-devel-2.3.4-25.el9.x86_64 · openssl-3.0.1-41.0.1.el9_0 · sysstat-12.5.4-3.el9

Platform

64-Bit Packages for 64-Bit Platform

Oracle Linux 9

Red Hat Linux 9

· binutils-2.35.2-17.0.1.el9

· gcc-11.2.1-9.4.0.2.el9

· gcc-c++-11.2.1-9.4.0.2.el9

· glibc-2.34-28.0.1.el9_0.2.x86_64

· glibc-devel-2.34-28.0.1.el9_0.2.x86_64

· libaio-0.3.111-13.el9.x86_64

· libgcc-11.2.1-9.4.0.2.el9.x86_64

· libnsl-2.34-83.0.2.el9.x86_64

· libstdc++-11.2.1-9.4.0.2.el9.x86_64

· libstdc++-devel-11.2.1-9.4.0.2.el9.x86_64

· make-4.3-8.el9

· motif-2.3.4-25.el9.x86_64

· motif-devel-2.3.4-25.el9.x86_64

· openssl-3.0.1-41.0.1.el9_0

· sysstat-12.5.4-3.el9

Kernel parameter ip_local_port_range should be set as 11000 - 65000

Make this change in /etc/sysctl.conf and use syctl -a command to check whether it reflects the change.

If it does not shows the change you may need to bounce the server

Set below database process parameter

alter system set processes=600 scope=spfile;

alter system set session_cached_cursors=300 scope=spfile;

Set parameter _allow_insert_with_update_check is TRUE and validate like below

alter system set "_allow_insert_with_update_check"=TRUE scope=spfile;

SELECT name,value FROM v$parameter WHERE name LIKE '/_%' ESCAPE '/' AND isdefault = 'FALSE' ORDER BY name;

Create a blank file called /etc/oraInst.loc and change ownership to oracle:dba

2. Download OEM 13c release 5 for Linux from below link and stage it in a shared directory

Oracle Enterprise Manager Downloads for Linux x86-64

3. Invoke the installer and generate the response file you need to use for performing a silent installation.

./em13500_linux64.bin -getResponseFileTemplates -outputLoc /mnt/oemcloud_1350

Launcher log file is /tmp/OraInstall2026-01-30_01-41-46PM/launcher2026-01-30_01-41-46PM.log.

Extracting the installer . . . . . . . . . . . . . . . . . . . . . . . . . . . Done

Copying response file template(s)

to /mnt/oemcloud_1350

Copying response file template softwareOnlyWithPlugins_install.rsp

Copying response file template emprereqkit_upgrade.rsp

Copying response file template upgrade.rsp

Copying response file template new_install.rsp

Copying response file template emprereqkit_install.rsp

Copying response file template softwareOnlyWithPlugins_upgrade.rsp

Finished copying response file template(s)

The log(s) can be found here: /tmp/OraInstall2026-01-30_01-41-46PM.

4. Edit the new_install.rsp file and enter appropriate values for the parameters described below

UNIX_GROUP_NAME=dba

INVENTORY_LOCATION=/u01/app/oraInventory

INSTALL_UPDATES_SELECTION=skip

STAGE_LOCATION=<string> ---Not required as we have skipped install_updates_selection to be skipped

ORACLE_MIDDLEWARE_HOME_LOCATION=/u01/app/oracle/middleware

ORACLE_HOSTNAME=<OMS server fully qualified hostname>

AGENT_BASE_DIR=/u01/app/oracle

WLS_ADMIN_SERVER_USERNAME=weblogic

WLS_ADMIN_SERVER_PASSWORD=<Admin server password>

WLS_ADMIN_SERVER_CONFIRM_PASSWORD=<Admin server password>

NODE_MANAGER_PASSWORD=<Node manager password>

NODE_MANAGER_CONFIRM_PASSWORD=<Node manager password>

ORACLE_INSTANCE_HOME_LOCATION=/u01/app/oracle/gc_inst

CONFIGURE_ORACLE_SOFTWARE_LIBRARY=true

SOFTWARE_LIBRARY_LOCATION=/u01/app/oracle/swlib

DATABASE_HOSTNAME=<Hostname for management repository>

LISTENER_PORT=1521

SERVICENAME_OR_SID=<SID of repository database>

SYS_PASSWORD=

SYSMAN_PASSWORD=

SYSMAN_CONFIRM_PASSWORD=

DEPLOYMENT_SIZE=MEDIUM

MANAGEMENT_TABLESPACE_LOCATION=+DATA

CONFIGURATION_DATA_TABLESPACE_LOCATION=+DATA

JVM_DIAGNOSTICS_TABLESPACE_LOCATION=+DATA

EMPREREQ_AUTO_CORRECTION=false

AGENT_REGISTRATION_PASSWORD=

AGENT_REGISTRATION_CONFIRM_PASSWORD=

STATIC_PORTS_FILE= (keep it blank to use default port)

PLUGIN_SELECTION={}

b_upgrade=false

EM_INSTALL_TYPE=NOSEED

CONFIGURATION_TYPE=ADVANCED

5. Run the installer like below

./em13500_linux64.bin -J-Djava.io.tmpdir=/mnt/oemcloud_1350 -silent -responseFile /mnt/oemcloud_1350/new_install.rsp -invPtrLoc /etc

Install log finished with below messages

Starting of OMS failed.

Starting export oms config...

Executing command: /u01/app/oracle/middleware/bin/emctl exportconfig oms -dir /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/backup

Export config of OMS is successful.

*** The installation was Successful, but some configuration assistants were failed or cancelled or skipped. ***

Tried to start OMS stack using emctl start oms command but Web tier failed to start

This is a known issue described in below MOS article and actual error can be found in node manager log like below

/u01/app/oracle/middleware/ohs/bin/httpd: error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory>

It is trying to find that file under $ORACLE_HOME/lib

EM 13.5 : OMS Startup Failure During Installation on OL/RHEL9

KB502768

Download the patch 35775632: PLACEHOLDER FOR STUBS.TAR PATCH FOR OL9 THAT FIXES BUG 33794281 and copy in /mnt/oemcloud_1350

Unzip the zip file p35775632_190000_Linux-x86-64.zip and it will creates stubs.tar

Copy stubs.tar into $OMS_HOME/lib/stubs and extract (cmd: $ tar -xf stubs.tar)

cp -r /mnt/oemcloud_1350/stubs.tar $ORACLE_HOME/lib/stubs

cd $ORACLE_HOME/lib/stubs

tar -xvf stubs.tar

libc-2.17.0-stub.so

libc_nonshared_stat.a

libc.so

libc.so.6

libm-2.17.0-stub.so

libm.so

libm.so.6

libpthread-2.17.0-stub.so

libpthread.so

libpthread.so.0

libpthread_stubs.a

Once the files are extracted, run the following command:

$ORACLE_HOME/bin/genclntsh

This command reads libraries we just extracted from previous step and generates file under $ORACLE_HOME/lib/ libclntshcore.so.12.1

Resume the Install from OUI or execute the runConfig.sh command to resume.

cd $ORACLE_HOME/oui/bin

$ OMS_HOME/oui/bin> ./runConfig.sh ORACLE_HOME=$ORACLE_HOME MODE=perform ACTION=configure COMPONENT_XML={encap_oms.1_0_0_0_0.xml}

Setting the invPtrLoc to /u01/app/oracle/middleware/oraInst.loc

perform - mode is starting for action: configure

log4j:ERROR No appenders could be found for category (oracle.sysman.oms.StartOMS).

log4j:ERROR Please initialize the log4j system properly.

The AgentFreshInstaller is starting now

Oraclehome : ../u01/app/oracle/agent_13.5.0.0.0

InstanceHome : /u01/app/oracle/agent_inst

Agent Base Directory : /u01/app/oracle

The oraclehome /u01/app/oracle/agent_13.5.0.0.0