Soumya's Database Blog : 2021

How to mitigate the impact associated with CVE-2021-44228 and CVE-2021-45046 on Oracle Fusion Middleware 12.2.1.4

Recently in Dec2021, a vulnerability was found in Log4j, an open-source logging library commonly used by apps and services across the internet. This vulnerability has shaken the entire world. To mitigate this vulnerability in Oracle Fusion Middleware, Oracle recommends to apply following patches .

Download following patches for WLS Release 12.2.1.4

1. Download latest Opatch for WLS 28186730

2. For the WLS and FMW Infrastructure, if you have not been applying quarterly security updates, Coherence Patch 33286160 is a prerequisite.

3. WLS PATCH SET UPDATE 12.2.1.4.210930 (Patch 33416868)

4. WLS OVERLAY PATCH FOR 12.2.1.4.0 OCT 2021 PSU (Patch 33671996) for CVE-2021-44228,CVE-2021-45046

I will be applying these patches in windows environment.

SET ORACLE_HOME=D:\app\oracle\product\12.2.1.4

Set JAVA_HOME=D:\app\oracle\jdk

Apply patch 28186730 - This patch installs latest version of Opatch for FMW/WLS

D:\app\oracle\product\12.2.1.4\OPatch>%java_home%\bin\java -jar C:\Users\Administrator\Downloads\6880880\opatch_generic.jar -silent oracle_home=D:\app\oracle\product\12.2.1.4

Launcher log file is C:\Users\Administrator\AppData\Local\Temp\1\OraInstall2021-12-19_05-06-36PM\launcher2021-12-19_05-06-36PM.log.

Extracting the installer . . . . Done

Checking if CPU speed is above 300 MHz. Actual 2400 Passed

Checking swap space: must be greater than 512 MB Passed

Checking if this platform requires a 64-bit JVM. Actual 64 Passed (64-bit not required)

Checking temp space: must be greater than 300 MB. Actual 272082 MB Passed

Preparing to launch the Oracle Universal Installer from C:\Users\Administrator\AppData\Local\Temp\1\OraInstall2021-12-19_05-06-36PM

Installation Summary

….

The install operation completed successfully.

Logs successfully copied to C:\Program Files\Oracle\Inventory\logs.

Shutdown entire application services(Nodemanager , Weblogic , FORMS, Reports, OHS etc)

Navigate to patch location and apply the patch

unzip p33286160_1221411_Generic.zip

List out the installed components to see the installed Coherence version

C:\Users\Administrator>%ORACLE_HOME%/OPatch/opatch.bat lsinventory -jdk %JAVA_HOME% -inactive

Oracle Interim Patch Installer version 13.9.4.2.1

Oracle Home : D:\app\oracle\product\12.2.1.4

Central Inventory : C:\Program Files\Oracle\Inventory

from :

OPatch version : 13.9.4.2.1

OUI version : 13.9.4.0.0

Log file location : D:\app\oracle\product\12.2.1.4\cfgtoollogs\opatch\opatch2021-12-19_16-18-03PM_1.log

OPatch detects the Middleware Home as "D:\app\oracle\product\12.2.1.4"

Lsinventory Output file location : D:\app\oracle\product\12.2.1.4\cfgtoollogs\opatch\lsinv\lsinventory2021-12-19_16-18-03PM.txt

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

Local Machine Information::

Hostname: CSAPP2

ARU platform id: 233

ARU platform description:: Microsoft Windows Server 2003 (64-bit AMD)

There are no inactive patches installed in this Oracle Home.

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

OPatch succeeded.

cd C:\Users\Administrator\Downloads

C:\Users\Administrator\Downloads>%ORACLE_HOME%/OPatch/opatch apply 1221411 -jdk %JAVA_HOME%

Oracle Interim Patch Installer version 13.9.4.2.1

Oracle Home : D:\app\oracle\product\12.2.1.4

Central Inventory : C:\Program Files\Oracle\Inventory

from :

OPatch version : 13.9.4.2.1

OUI version : 13.9.4.0.0

Log file location : D:\app\oracle\product\12.2.1.4\cfgtoollogs\opatch\opatch2021-12-19_16-22-18PM_1.log

OPatch detects the Middleware Home as "D:\app\oracle\product\12.2.1.4"

Verifying environment and performing prerequisite checks...

OPatch continues with these patches: 1221411

Do you want to proceed? [y|n]

User Responded with: Y

All checks passed.

Backing up files...

Applying interim patch '1221411' to OH 'D:\app\oracle\product\12.2.1.4'

Patching component oracle.coherence, 12.2.1.4.0...

Patch 1221411 successfully applied.

Log file location: D:\app\oracle\product\12.2.1.4\cfgtoollogs\opatch\opatch2021-12-19_16-22-18PM_1.log

OPatch succeeded.

Unzip patch 33416868

Apply patch

C:\Users\Administrator\Downloads>%ORACLE_HOME%/OPatch/opatch apply 33416868 -jdk %JAVA_HOME%

Unzip patch 33671996

Apply patch

cd C:\Users\Administrator\Downloads\33671996

%ORACLE_HOME%/OPatch/opatch apply

Mitigation Plan

If patching is not possible at this time, you may mitigate the Log4j vulnerabilities with the below steps.

This mitigation applies to Log4j v2 prior to 2.16.0, including 2.15.

1. Navigate to the location:

ORACLE_HOME/oracle_common/modules/thirdparty/

2. Run the below command for the installed Log4j version 2 files:

12.2.1.3.0: log4j-1.2.17.jar - This is expected to contain a version 2 file
12.2.1.4.0: log4j-2.11.1.jar
14.1.1.0.0: log4j-core-2.11.1.jar and log4j-api-2.11.0.jar

Unix:

zip -q -d log4j*.jar org/apache/logging/log4j/core/lookup/JndiLookup.class

Windows:

Use a zip utility to extract the contents as a .zip, remove JndiLookup.class, and re-zip.

Reference:-Security Alert CVE-2021-44228 / CVE-2021-45046 Patch Availability Document for Oracle WebLogic Server & Fusion Middleware (Doc ID 2827793.1)

The “visiblepw” is not set the sudoers file and as a result, the user will not be able to run sudo over ssh

While installing agent in target host for oracle enterprise manager 13c, following error is observed

Solution:-

This issue has two different solutions

1. Change the entry “Defaults !visiblepw” to “Defaults visiblepw” in the /etc/sudoers file and re-try Agent Deployment. This needs to be done in host server.

2. You can also set the "oracle.sysman.prov.agentpush.enablePty" property to true in the "/data2/app/oracle/middleware/sysman/prov/agentpush/agentpush.properties" file, which is present on the OMS host,

And pass -S argument to the sudo command

/usr/bin/sudo -S -u %RUNAS% %COMMAND%

Now retry again the agent deployment , this time it should succeed.

Enterprise manager 13c: Error During Agent Installation

While installing agent in target host for oracle enterprise manager 13c, following error is observed

EM 12c, 13c: Error During Agent Installation : Ensure central inventory is owned by install user, has read and write permission

Solution:-

Make sure in inventory_loc in /etc/oraInst.loc file points to a valid inventory location and it is writable by the installing user in the target host server.

inventory_loc=/DEV/R12DEV/db/tech_st/19.3.0/oraInventory

where <valid inventory location> must be writable by the installing user.

SQL Query to get details of a concurrent process in EBS R12.1.3

To get forms process id first.

SQL> SET LINES 222

SQL> col MODULE for a40

SQL> col ACTION for a40

select sid,serial#,process,module,action from v$session where process=(SELECT p.os_process_id FROM FND_CONCURRENT_REQUESTS r, FND_CONCURRENT_PROCESSES p

where r.controlling_manager = p.concurrent_process_id and request_id=211503612)

To get more details of process id and command kill that session:-

select s.ECID ,s.inst_id, s.SID,s.SERIAL#,p.spid,s.status,s.machine, s.ACTION, s.MODULE, s.TERMINAL,s.sql_id,s.last_call_et,s.event, s.client_info,s.PLSQL_SUBPROGRAM_ID,s.PROGRAM,s.client_identifier

, ( SELECT max( substr( sql_text , 1, 40 )) FROM gv$sql sq WHERE sq.sql_id = s.sql_id ) AS sql_text

, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = 0) AS plsql_entry_object

, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = plsql_entry_subprogram_id) AS plsql_entry_subprogram

, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = 0) AS plsql_entry_object

, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = PLSQL_SUBPROGRAM_ID) AS plsql_entry_subprogram

, 'alter system kill session ' || '''' || s.SID || ',' || s.serial# ||',@'|| s.inst_id||''''|| ' immediate;' kill_session

from gv$session s ,gv$process p

where

s.process='32488' --forms OS process ID

--s.program like '%frm%'

and p.addr=s.paddr

and p.inst_id = s.inst_id

;

How to configure webutil in forms 12.2.1.4

1. Create webutil schema

SQL> create user webutil identified by Concept_ora1;

SQL> grant dba to webutil;

Cd D:\app\oracle\product\12.2.1.4\bin

D:\weblogic\Middleware\12.2.1.4\bin>sqlplus webutil@PRDPDB

SQL> @D:\app\oracle\product\12.2.1.4\forms\create_webutil_db.sql

SQL> create public synonym webutil_db for webutil.webutil_db;

2. Download the jacob version 1.2 and unzip it in the directory of your choice - https://github.com/freemansoft/jacob-project/releases

unzip jacob-1.20.zip

cd jacob-1.20

copy Jacob.jar D:\app\oracle\product\12.2.1.4\forms\java\

copy jacob-1.20-x86.dll D:\app\oracle\product\12.2.1.4\forms\webutil\win32

copy jacob-1.20-x64.dll D:\app\oracle\product\12.2.1.4\forms\webutil\win64

edit below file

D:\app\oracle\product\12.2.1.4\user_projects\domains\prd\config\fmwconfig\components\FORMS\instances\forms1\bin\sign_webutil.bat

replace “D:\app\oracle\product\12.2.1.4\jdk\bin” with “D:\app\oracle\product\12.2.1.4\oracle_common\jdk\bin”

and edit following parameters

SET JAR_KEY_PASSWORD=xyz123

SET KEYSTORE_PASSWORD=xyz123

SET VALIDDAYS=3600

Use sign_webutil.bat to sign Jacob.jar using following command:

D:\app\oracle\product\12.2.1.4\user_projects\domains\prd\config\fmwconfig\components\FORMS\instances\forms1\bin\sign_webutil.bat D:\app\oracle\product\12.2.1.4\forms\java\jacob.jar

3. Download webutil_demo form from the following link and place the webutil_demo.fmb form in the path D:\app\oracle\product\12.2.1.4\forms

https://www.dropbox.com/s/mdkhjh7997r8arh/webutil_demo.fmb?dl=0

4. Compile webutil.pll to create webutil.plx

Open forms compiler

D:\app\oracle\product\12.2.1.4\bin\frmcmp.exe

Navigate to path D:\app\oracle\product\12.2.1.4\forms

and compile webutil_demo.fmb

Double click on webutil_demo.fmb and connect to database and compile forms.

1. Make the following modifications to the [webutil] configuration of the formsweb.cfg file,

Edit formsweb.cfg in

D:\app\oracle\product\12.2.1.4\user_projects\domains\prd\config\fmwconfig\servers\WLS_FORMS\applications\formsapp_12.2.1\config

In [default]

section search for archive and add Jacob.jar , frmwebutil.jar

archive=frmall.jar,frmwebutil.jar,jacob.jar

[wutil]

WebUtilArchive=frmwebutil.jar,jacob.jar

baseSAAfile=webutilsaa.txt

archive=frmall.jar,jacob.jar,frmwebutil.jar

form=D:\app\oracle\product\12.2.1.4\forms\webutil_demo.fmx

WebUtilMaxTransferSize=24573

5. Modify webutil.cfg file

In following location D:\app\oracle\product\12.2.1.4\user_projects\domains\prd\config\fmwconfig\components\FORMS\instances\forms1\server

Change following parameters

transfer.database.enabled=FALSE

transfer.database.enabled=TRUE

transfer.appsrv.enabled=FALSE

transfer.appsrv.enabled=TRUE

transfer.appsrv.accessControl=TRUE

transfer.appsrv.accessControl=FALSE

Also change following ,

From

install.syslib.0.0.7.1=jacob-1.18-M2-x86.dll|167424|1.18-M2|true

install.syslib.0.0.7.1=jacob-1.20-x86.dll|189440|1.20|true

From

install.syslib.0.1.7.1=jacob-1.18-M2-x64.dll|204800|1.18-M2|true

install.syslib.0.1.7.1=jacob-1.20-x64.dll|227328|1.20|true

transfer.appsrv.read.1=c:\temp

#List transfer.appsrv.write.<n> directories

transfer.appsrv.write.1=c:\temp

Note: verify name, size ,version of jacob-1.18-M2-x64.dll and jacob-1.18-M2-x86.dll.

Edit default.env in

D:\app\oracle\product\12.2.1.4\user_projects\domains\prd\config\fmwconfig\servers\WLS_FORMS\applications\formsapp_12.2.1\config

and add following in CLASSPATH variable in following location

CLASSPATH= D:\app\oracle\product\12.2.1.4\forms\java\jacob.jar

Open webutil form from browser.

Note: Make sure end user has jre 1.8 32bit installed to open the form window without any issue.

URL: http://csapp2:9001/forms/frmservlet?config=wutil

Provide username:webtuil

Password: xyz123

Database: PRDPDB

If DOWNLOAD/UPLOAD features don’t work,

open the webutil_demo.fmb through forms compiler and check program units section.

Look for DOWNLOAD_AS and UPLOAD_AS section and make sure its path is pointing to c:/temp path and temp directory exists inside C:/

How to find out uncommitted transactions in oracle 19c

V$TRANSACTION lists the active transactions in the database

Use following query to find out uncommitted active transactions in database

select t.start_time,a.sid,a.serial#,a.username,a.status,a.schemaname,

a.osuser,a.process,a.machine,a.terminal,a.program,a.module,to_char(a.logon_time,'DD/MON/YY HH24:MI:SS') logon_time

from v$transaction t, v$session a

where a.saddr = t.ses_addr

order by start_time;

To find out sql statement of uncommitted transaction

SELECT a.SID, a.SERIAL#, a.USERNAME, a.OSUSER, a.PROGRAM, a.EVENT

,TO_CHAR(a.LOGON_TIME,'YYYY-MM-DD HH24:MI:SS')

,TO_CHAR(T.START_DATE,'YYYY-MM-DD HH24:MI:SS')

,a.LAST_CALL_ET, a.BLOCKING_SESSION, a.STATUS

SELECT Q.SQL_TEXT

FROM V$SQL Q

WHERE Q.LAST_ACTIVE_TIME=T.START_DATE

AND ROWNUM<=1) AS SQL_TEXT

FROM V$SESSION a,

V$TRANSACTION T

WHERE a.SADDR = T.SES_ADDR;

To find out if your current session has any uncommitted transaction

SELECT COUNT(*) FROM v$transaction t, v$session s, v$mystat m WHERE t.ses_addr = s.saddr AND s.sid = m.sid AND ROWNUM = 1;

If the output is zero it means it has no uncommitted transaction.

If output is 1, it states session has uncommitted transactions.

Shell script to stop weblogic , forms, reports , ohs instance in 12.2.1.4

Create an env file with following details.

Note: It is assumed that boot.properties file is configured with weblogic username and password. Otherwise the script will prompt for username and password.

cat /home/oracle/app_env

ORACLE_BASE=/d01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/12.2.1.4; export ORACLE_HOME

MW_HOME=$ORACLE_HOME ; export MW_HOME

WLS_HOME=$MW_HOME/wlserver; export WLS_HOME

WL_HOME=$WLS_HOME ;export WLS_HOME

DOMAIN_HOME=/d01/app/oracle/product/12.2.1.4/user_projects/domains/prod_domain; export DOMAIN_HOME

JAVA_HOME=/usr/java/jdk1.8.0_231-amd64; export JAVA_HOME

export OHS_INST=/d01/app/oracle/product/12.2.1.4/user_projects/domains/prod_domain/config/fmwconfig/components/OHS/instances/ohs1 ;export OHS_INST

PATH=$JAVA_HOME/bin:$PATH ; export PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export FORMS_PATH=/d01/app/oracle/product/12.2.1.4/forms; export FORMS_PATH

Here, ORACLE_BASE stands for oracle base

ORACLE_HOME stands for middleware home

MW_HOME also stands for middleware home

DOMAIN_HOME stands for domain home

OHS_INST stands for Oracle HTTP Server instance home

Now create shell script

vi stop_services.sh

# Start NodeManager

#!/bin/bash

. /home/oracle/app_env

echo "*************************Stopping Ohs Instance ********************************"

# Stop the web tier.

$DOMAIN_HOME/bin/stopComponent.sh ohs1

sleep 5

echo "*************************Stopping Standalone Report Server ********************************"

$DOMAIN_HOME/bin/stopComponent.sh rep_server1_prod

sleep 5

echo "*************************Stopping Forms Services ********************************"

# Stop the managed Servers

$DOMAIN_HOME/bin/stopManagedWebLogic.sh WLS_FORMS

sleep 5

echo "*************************Stopping Reports Services ********************************"

$DOMAIN_HOME/bin/stopManagedWebLogic.sh WLS_REPORTS

sleep 5

echo "*************************Stopping WebLogic********************************"

# Stop the WebLogic Domain

$DOMAIN_HOME/bin/stopWebLogic.sh

sleep 5

echo "*************************Stopping NodeManager********************************"

$DOMAIN_HOME/bin/stopNodeManager.sh

echo "*************************All Services are stopped now!********************************"

Shell script to start weblogic , forms, reports , ohs instance in 12.2.1.4

Create an env file with following details.

Note: It is assumed that boot.properties file is configured with weblogic username and password. Otherwise the script will prompt for username and password.

cat /home/oracle/app_env

ORACLE_BASE=/d01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/12.2.1.4; export ORACLE_HOME

MW_HOME=$ORACLE_HOME ; export MW_HOME

WLS_HOME=$MW_HOME/wlserver; export WLS_HOME

WL_HOME=$WLS_HOME ;export WLS_HOME

DOMAIN_HOME=/d01/app/oracle/product/12.2.1.4/user_projects/domains/prod_domain; export DOMAIN_HOME

JAVA_HOME=/usr/java/jdk1.8.0_231-amd64; export JAVA_HOME

export OHS_INST=/d01/app/oracle/product/12.2.1.4/user_projects/domains/prod_domain/config/fmwconfig/components/OHS/instances/ohs1 ;export OHS_INST

PATH=$JAVA_HOME/bin:$PATH ; export PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export FORMS_PATH=/d01/app/oracle/product/12.2.1.4/forms; export FORMS_PATH

Here, ORACLE_BASE stands for oracle base

ORACLE_HOME stands for middleware home

MW_HOME also stands for middleware home

DOMAIN_HOME stands for domain home

OHS_INST stands for Oracle HTTP Server instance home

Now create shell script

vi start_services.sh

# Start NodeManager

#!/bin/bash

. /home/oracle/app_env

echo "*************************Starting NodeManager********************************"

nohup $DOMAIN_HOME/bin/startNodeManager.sh > /dev/null 2>&1 &

sleep 10

echo "*************************Starting WebLogic********************************"

# Start WebLogic Domain

nohup $DOMAIN_HOME/bin/startWebLogic.sh > /dev/null 2>&1 &

sleep 60

echo "*************************Starting Forms Services ********************************"

# Start the managed Servers

nohup $DOMAIN_HOME/bin/startManagedWebLogic.sh WLS_FORMS > /dev/null 2>&1 &

sleep 10

echo "*************************Starting Reports Services ********************************"

nohup $DOMAIN_HOME/bin/startManagedWebLogic.sh WLS_REPORTS > /dev/null 2>&1 &

sleep 10

echo "*************************Starting Ohs Instance ********************************"

# Start the web tier.

$DOMAIN_HOME/bin/startComponent.sh ohs1 > /dev/null 2>&1 &

sleep 10

echo "*************************Starting Standalone Report Server ********************************"

$DOMAIN_HOME/bin/startComponent.sh rep_server1_prod > /dev/null 2>&1 &

sleep 10

echo "*************************All Services are running. Run this url to check http://192.168.23.12:9001/forms/frmservlet?config=cspprd ********************************"

#chmod 775 start_services.sh

Gather all undo information using a single script

In our daily life, sometimes we require to gather various information related to undo tablespace and for many of us it becomes challenging to remember all the views to gather that information.

Using this script, we can gather various information related to undo tablespace in a single go.

[db@server102 ~]$ cat undotablespace.sql

#Please execute following script to generate html file

set markup html on spool on

SPOOL UNDO_INFO.HTML

set pagesize 200

set echo on;

select * from v$version;

show parameter undo

alter session set nls_date_format='DD-MON-YY HH:MI:SS AM';

select * from v$database;

select * from gv$instance;

select inst_id,sid,name,value from gv$spparameter where name like '%undo%';

select a.ksppinm "Parameter",

b.ksppstvl "Session Value",

c.ksppstvl "Instance Value",

a.KSPPDESC "Describtion"

from x$ksppi a, x$ksppcv b, x$ksppsv c

where a.indx = b.indx and a.indx = c.indx

and a.ksppinm like '%smu%';

select nam.ksppinm NAME, val.KSPPSTVL VALUE

from x$ksppi nam, x$ksppsv val

where nam.indx = val.indx and (nam.ksppinm like '%undo%' or nam.ksppinm like '_smu%' or nam.ksppinm in ('event', '_first_spare_parameter','_rollback_segment_count' ) )

order by 1;

select max(maxquerylen), max(tuned_undoretention) , max(undoblks), avg (undoblks), avg(maxquerylen), avg(tuned_undoretention) from v$undostat;

select max(maxquerylen),max(tuned_undoretention) from dba_hist_undostat;

select status, round(sum(bytes)/(1024*1024)) size_mb, count(status) number_of_ext

from dba_undo_extents

group by status;

select tablespace_name,status,count(*) from dba_rollback_segs group by status,tablespace_name order by 1;

select object_name, reason from dba_outstanding_alerts;

SELECT tablespace_name, status,round(sum(bytes)/(1024*1024)) size_m, COUNT (*)

FROM SYS.dba_undo_extents

GROUP BY tablespace_name, status order by tablespace_name,status;

select to_char(begin_time, 'DD-MON-YYYY HH24:MI:SS') begin_time,

tuned_undoretention from v$undostat;

select to_char(begin_time, 'DD-MON-YYYY HH24:MI:SS') begin_time,

to_char(end_time, 'DD-MON-YYYY HH24:MI:SS') end_time,

undotsn, undoblks, txncount, maxconcurrency as "MAXCON",

maxquerylen, tuned_undoretention

from v$undostat order by 1;

select * from v$undostat ;

select * from gv$undostat;

column UNXPSTEALCNT heading "# Unexpired-Stolen"

column EXPSTEALCNT heading "# Expired-Reused"

column SSOLDERRCNT heading "ORA-1555"

column NOSPACEERRCNT heading "Out-Of-space"

column MAXQUERYLEN heading "Max Query Length"

select inst_id, to_char(begin_time,'DD-MON-YYYY HH24:MI:SS') "Begin Time",

unxpstealcnt, expstealcnt , ssolderrcnt, nospaceerrcnt, maxquerylen, tuned_undoretention "Tuned Undo"

from gv$undostat

where SSOLDERRCNT > 0

order by inst_id, begin_time;

select tablespace_name,block_size,status,contents,retention,extent_management,segment_space_management,status,bigfile from dba_tablespaces

where contents='UNDO' order by contents,tablespace_name;

select tablespace_name,file_name,round(bytes/1024/1024) "SIZE (MB)",autoextensible,round(maxbytes/1024/1024) "MAX SIZE (MB)", status

from dba_data_files where tablespace_name in (select tablespace_name from dba_tablespaces where contents='UNDO') order by tablespace_name,file_name;

SELECT /* + RULE */ df.tablespace_name "Tablespace",

df.bytes / (1024 * 1024) "Size (MB)",

SUM(fs.bytes) / (1024 * 1024) "Free (MB)",

Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

FROM dba_free_space fs,

(SELECT tablespace_name,SUM(bytes) bytes

FROM dba_data_files where tablespace_name in (select tablespace_name from dba_tablespaces where CONTENTS='UNDO')

GROUP BY tablespace_name) df

WHERE fs.tablespace_name (+) = df.tablespace_name

GROUP BY df.tablespace_name,df.bytes

SELECT s.sid, s.serial#, s.username, u.segment_name, count(u.extent_id) "Extent Count", t.used_ublk, t.used_urec, s.program

FROM v$session s, v$transaction t, dba_undo_extents u

WHERE s.taddr = t.addr and u.segment_name like '_SYSSMU'||t.xidusn||'_%$' and u.status = 'ACTIVE'

GROUP BY s.sid, s.serial#, s.username, u.segment_name, t.used_ublk, t.used_urec, s.program

ORDER BY t.used_ublk desc, t.used_urec desc, s.sid, s.serial#, s.username, s.program;

select b.name "UNDO Segment Name", b.inst# "Instance ID", b.status$ STATUS, a.ktuxesiz "UNDO Blocks", a.ktuxeusn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq

from x$ktuxe a, undo$ b

where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%' and a.ktuxeusn = b.us#;

spool off

set markup html off spool off

Execute the sql

[db@server102 ~]$ sqlplus / as sysdba

SQL> @undotablespace.sql

OEM 13.5 installation fails during OMSCA With the Error: OMS service creation failed

Environment Details:-

OMS DB Version	19.3.0
OEM Version	13.5.0.0.0
OS	Windows Server 2019

Error Details:-

During Oracle Enterprise manager 13.5 installation following error message is seen inside

OMS_HOME/cfgtoollogs/omsca/omsca_<timestamp>.log location

Aug 28, 2021 11:24:51 AM oracle.sysman.omsca.util.CoreOMSConfigAssistantUtil execCommand

INFO: Output messages of the command C:\app\Middleware\bin\emctl.bat create service -oms_svc_name OracleManagementServer_EMGC_OMS1_1 :

Oracle Enterprise Manager Cloud Control 13c Release 5

Creating oms service OracleManagementServer_EMGC_OMS1_1...

OMS service creation failed. Aborting...

Aug 28, 2021 11:24:51 AM oracle.sysman.omsca.util.CoreOMSConfigAssistantUtil execCommand

INFO: error messages of the command C:\app\Middleware\bin\emctl.bat create service -oms_svc_name OracleManagementServer_EMGC_OMS1_1 :

Solution:-

1) Ensure that the following packages are installed on the OMS Server. If not install them.

Microsoft Visual C++ 2010 Redistributable Package
Microsoft Visual C++ 2012 Redistributable Package
Microsoft Visual C++ 2015 Redistributable Package Update 3

2) Delete the <OMS_BASE_DIR>\gc_inst directory created during the oms configuration.

3) If the OEM installer is still running click on "Retry".

If not, execute following from command prompt:

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

This should resolve the issue.