OEM 13c WebTier Could Not Be Started Due To The Default OHS Keystore Certificate Expired

OMS start failed with error "WebTier Could Not Be Started."

$ emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
WebTier Could Not Be Started.
Error Occurred: WebTier Could Not Be Started.
Please check /u01/app/oracle/em13.5/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log for error details

emctl.log shows "Failed to start the server ohs1"

2026-04-19 08:06:38,272 [Thread-1] INFO  commands.BaseCommand - <OUT>NMProcess: SEVERE: Failed to start the server ohs1
2026-04-19 08:06:38,275 [Thread-1] INFO  commands.BaseCommand - <OUT>NMProcess: <Apr 19, 2026 8:06:38 AM EDT> <WARNING> <Server start command for OHS server 'ohs1' failed due to: [Failed to start the server ohs1
2026-04-19 08:06:38,275 [Thread-1] INFO  commands.BaseCommand - <OUT>NMProcess: Check log file /u01/app/oracle/em13.5/gc_inst/user_projects/domains/GCDomain/system_components/OHS/ohs_nm.log
2026-04-19 08:06:38,275 [Thread-1] INFO  commands.BaseCommand - <OUT>NMProcess: Check log file /u01/app/oracle/em13.5/gc_inst/user_projects/domains/GCDomain/servers/ohs1/logs/ohs1.log]. Please check Node Manager log and/or server 'ohs1' log for detailed information.>
  ... ...
2026-04-19 08:06:38,754 [main] ERROR commands.BaseCommand - WebTier Could Not Be Started.
2026-04-19 08:06:38,754 [main] ERROR wls.OMSController - OMSController failed for start oms
2026-04-19 08:06:38,755 [main] ERROR wls.OMSController - OMSController Error: WebTier Could Not Be Started.
java.lang.Exception: WebTier Could Not Be Started.
        at oracle.sysman.emctl.commands.StartCommand.startOMS(StartCommand.java:431) ~[emCoreSDKImpl.jar:?]
        at oracle.sysman.emctl.commands.StartCommand.execute(StartCommand.java:281) ~[emCoreSDKImpl.jar:?]
        at oracle.sysman.emctl.wls.OMSController.main(OMSController.java:246) [emCoreSDKImpl.jar:?]

ohs1.log shows "OHS:2057 Init: (localhost:443) Unable to initialize SSL environment, nzos call nzosSetCredential returned 28791"

[2026-04-19T08:06:37.1384-04:00] [OHS] [ERROR:32] [OH99999] [ossl] [host_id: host01] [host_addr: 10.10.28.25] [pid: 3028810] [tid: 140246783358336] [user: oracle] [VirtualHost: localhost:0] OHS:2057 Init: (localhost:443) Unable to initialize SSL environment, nzos call nzosSetCredential returned 28791
[2026-04-19T08:06:37.1384-04:00] [OHS] [ERROR:32] [OH99999] [ossl] [host_id: host01] [host_addr: 10.10.28.25] [pid: 3028810] [tid: 140246783358336] [user: oracle] [VirtualHost: localhost:0] OHS:2171 NZ Library Error: Unknown error

The error message shows OHS failed to communicate with locallhost using SSL, it usually means invalid certificate.

OHS has four keystores/wallets (default, console, proxy, upload) to store certificates for different purposes. The default wallet is used for SSL communication to localhost. There wallets are located under directory 

 <GC_INST>/user_projects/domains/GCDomain/config/fmwconfig/components/OHS/ohs1/keystores

 <GC_INST>/user_projects/domains/GCDomain/config/fmwconfig/components/OHS/instances/ohs1/keystores

Verify if certificate in default wallet expired, following How to Check Validity of Server Certificate from Oracle Wallet in Command Line.

$ cd /u01/app/oracle/em13.5/gc_inst/user_projects/domains/GCDomain/config/fmwconfig/components/OHS/ohs1/keystores/default

$ orapki wallet display -wallet ./
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=localhost,OU=GCDomain ohs1,O=FOR TESTING ONLY
Trusted Certificates:
Subject:        CN=CertGenCA,OU=FOR TESTING ONLY,O=MyOrganization,L=MyTown,ST=MyState,C=US

$ orapki wallet export -wallet ./ -dn 'CN=localhost,OU=GCDomain ohs1,O=FOR TESTING ONLY' -cert /tmp/export.cert
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

$ orapki cert display -cert /tmp/export.cert -summary
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Subject:        CN=localhost,OU=GCDomain ohs1,O=FOR TESTING ONLY
Issuer:         CN=CertGenCA,OU=FOR TESTING ONLY,O=MyOrganization,L=MyTown,ST=MyState,C=US
Valid Until:    Sat Apr 18 21:54:35 EDT 2026

The certificate has expired on Sat Apr 18 21:54:35 EDT 2026.

Solution

Since the certificate is self signed, we can create a new one with new expiration date as following,

* orapki should run from the location "<OMS_HOME>/oracle_common/bin" not from the "<OMS_HOME>/bin"
* Make sure performing these steps to the OHS stage location not under the instance location:
Stage location:
<GC_INST>/user_projects/domains/GCDomain/config/fmwconfig/components/OHS/ohs1/keystores/default
Instance location:
<GC_INST>/user_projects/domains/GCDomain/config/fmwconfig/components/OHS/instances/ohs1/keystores/default/

1. Rename existing wallet file

$ cd /u01/app/oracle/em13.5/gc_inst/user_projects/domains/GCDomain/config/fmwconfig/components/OHS/ohs1/keystores/default
$ ls -l
total 8
-rw-r----- 1 oracle oinstall 4341 Apr 19  2021 cwallet.sso
-rw------- 1 oracle oinstall    0 Apr 19  2022 cwallet.sso.lck

$ mv cwallet.sso cwallet.sso.expired

2. Create a new auto_login wallet with command
   orapki wallet create -wallet <wallet_location> -auto_login_only

$ echo $OMS_HOME
/u01/app/oracle/em13.5/middleware
$ pwd
/u01/app/oracle/em13.5/gc_inst/user_projects/domains/GCDomain/config/fmwconfig/components/OHS/ohs1/keystores/default

$ $OMS_HOME/oracle_common/bin/orapki wallet create -wallet ./ -auto_login_only
Oracle PKI Tool : Version 12.2.1.4.0SECINF-BP
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

$ ls -l /u01/app/oracle/em13.5/gc_inst/user_projects/domains/GCDomain/config/fmwconfig/components/OHS/ohs1/keystores/default
total 4
-rw------- 1 oracle oinstall 4085 Apr 19 11:26 cwallet.sso
-rw-r----- 1 oracle oinstall 4341 Apr 19  2021 cwallet.sso.expired
-rw------- 1 oracle oinstall    0 Apr 19  2022 cwallet.sso.lck

$ $OMS_HOME/oracle_common/bin/orapki wallet display -wallet ./
Oracle PKI Tool : Version 12.2.1.4.0SECINF-BP
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:

3. Add the new self signed certificate to the wallet with command

   orapki wallet add -wallet <wallet_location> -dn 'CN=localhost,OU=GCDomain ohs1,O=FOR TESTING ONLY' -keysize 2048 -self_signed -validity 3650 -sign_alg sha256 -auto_login_only

$ $OMS_HOME/oracle_common/bin/orapki wallet add -wallet ./ -dn 'CN=localhost,OU=GCDomain ohs1,O=FOR TESTING ONLY' -keysize 2048 -self_signed -validity 3650 -sign_alg sha256 -auto_login_only
Oracle PKI Tool : Version 12.2.1.4.0SECINF-BP
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

$ $OMS_HOME/oracle_common/bin/orapki wallet display -wallet ./
Oracle PKI Tool : Version 12.2.1.4.0SECINF-BP
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=localhost,OU=GCDomain ohs1,O=FOR TESTING ONLY
Trusted Certificates:
Subject:        CN=localhost,OU=GCDomain ohs1,O=FOR TESTING ONLY

$ $OMS_HOME/oracle_common/bin/orapki wallet export -wallet ./ -dn 'CN=localhost,OU=GCDomain ohs1,O=FOR TESTING ONLY' -cert /tmp/export.cert
Oracle PKI Tool : Version 12.2.1.4.0SECINF-BP
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.
$
$ $OMS_HOME/oracle_common/bin/orapki cert display -cert /tmp/export.cert -summary
Oracle PKI Tool : Version 12.2.1.4.0SECINF-BP
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Subject:        CN=localhost,OU=GCDomain ohs1,O=FOR TESTING ONLY
Issuer:         CN=localhost,OU=GCDomain ohs1,O=FOR TESTING ONLY
Valid Until:    Wed Apr 16 11:26:53 EDT 2036

New certificate valid in 10 years.

4. Copy new cwallet.sso file from stage location to instances folder location

$ cd /u01/app/oracle/em13.5/gc_inst/user_projects/domains/GCDomain/config/fmwconfig/components
$ cp -p ./OHS/ohs1/keystores/default/cwallet.sso ./OHS/instances/ohs1/keystores/default

In case of multi OMS environment, OHS instance location is deferent on deferent OMS node, for example

On node 1,  <GC_INST>/user_projects/domains/GCDomain/config/fmwconfig/components/OHS/instances/ohs1/keystores/default/

On node 2,  <GC_INST>/user_projects/domains/GCDomain/config/fmwconfig/components/OHS/instances/ohs2/keystores/default/

On node 3,  <GC_INST>/user_projects/domains/GCDomain/config/fmwconfig/components/OHS/instances/ohs3/keystores/default/

5. Restart oms with commands

  <OMS_HOME>/bin/emctl stop oms -all -force 

  <OMS_HOME>/binemctl start oms

How to Check Validity of Server Certificate from Oracle Wallet in Command Line

Command orapki is usually used to manamge Oracle wallet in command line.

For Oracle database installation, orapki can be found from "<ORCLE_HOME>/bin".

For Oracle Enterprise Manager installation, it can be found from "<OMS_HOME>/oracle_common/bin", not from the "<OMS_HOME>/bin".

List certificates with command

  orapki wallet display -wallet <wallet_location>

$ orapki wallet display -wallet ./
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=localhost,OU=GCDomain ohs1,O=FOR TESTING ONLY
Trusted Certificates:
Subject:        CN=CertGenCA,OU=FOR TESTING ONLY,O=MyOrganization,L=MyTown,ST=MyState,C=US

It shows two certificates in the wallet, one is User Certicates, the other is Trusted Certicate.

If want to check more details (i.e. expiration date) of the certificate, run following command to export certificate to a file, 

  orapki wallet export -wallet <wallet_location> -dn '<Certicate_DN>' -cert <certificate_file>

Then display certificate details from certificate file with command,

  orapki cert display -cert <certificate_file> [-summary]

$ orapki wallet export -wallet ./ -dn 'CN=localhost,OU=GCDomain ohs1,O=FOR TESTING ONLY' -cert /tmp/export.cert
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

$ orapki cert display -cert /tmp/export.cert -summary
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Subject:        CN=localhost,OU=GCDomain ohs1,O=FOR TESTING ONLY
Issuer:         CN=CertGenCA,OU=FOR TESTING ONLY,O=MyOrganization,L=MyTown,ST=MyState,C=US
Valid Until:    Sat Apr 18 21:54:35 EDT 2026

SQLcl with Directory Naming (LDAP) and saved credential in Oracle wallet

This post is going to show how to configure SQLcl to 

  * Use Directory Naming method (ldap.ora) to resolve connection string

  * Passwordless connection with saved credential in Oracle Wallet

The configuration applies on from version 23.1 to current 25.4.1. Configuration of  old versions can be found at Oracle SQLcl installation and configuration.

OEM 13c shows database and ASM instance DOWN when TCPS (TLS/SSL) enabled

Database server is configured with TCPS only, ASM and database instance can only be accessed with TCPS.

Both ASM and database instances are configured successfully in OEM. Test Connection succeeded in Monitoring Configuration page.

On database and ASM home page, OEM can connect to target and manage objects. However, the target status shows DOWN.

APEX (with ORDS) applications error with "Too Many requests Http Status code:429"

 The APEX is installed and configured with ORDS, when accessing APEX applications, the following erro occures,

    Too Many requests
      Http Status code: 429

     Request ID: o9U3yshYc10iRdjSwRRAmg
  Request Timestamp: 2025-10-29T16:49:31.451274Z

The Pool named: |default|lo| reached the maximum ORDS connections.

19c datapatch failed with error "ORA-04088: error during execution of trigger 'WMSYS.NO_VM_DDL'"

When running datapatch against Oracle 19c database, it failes with error "ORA-04088: error during execution of trigger 'WMSYS.NO_VM_DDL'".

The datapatch output is something like following,

Patch 37960098 apply (pdb PDB01): WITH ERRORS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/37960098/27641878/37960098_apply_CDB01_PDB01_2025Aug20_15_44_17.log (errors)
  -> Error at line 140412: script rdbms/admin/owmcvws.plb
      - ORA-04088: error during execution of trigger 'WMSYS.NO_VM_DDL'
      - ORA-00604: error occurred at recursive SQL level 2
      - ORA-04061: existing state of  has been invalidated
      - ORA-04061: existing state of package body "WMSYS.LT_CTX_PKG" has been
      - invalidated
      - ORA-04065: not executed, altered or dropped package body "WMSYS.LT_CTX_PKG"
      - ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_CTX_PKG"
      - ORA-06512: at "WMSYS.LTADM", line 9800
      - ORA-04061: existing state of package body "WMSYS.LT_CTX_PKG" has been
      - invalidated
      - ORA-04065: not executed, altered or dropped package body "WMSYS.LT_CTX_PKG"
      - ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_CTX_PKG"
      - ORA-06512: at "WMSYS.LTADM", line 9532
      - ORA-06512: at "WMSYS.OWM_DYNSQL_ACCESS", line 549
      - ORA-06512: at line 17
      - ORA-06512: at line 7
      - ORA-06512: at line 7
  -> Error at line 142841: script rdbms/admin/owmcvws.plb
      - ORA-04088: error during execution of trigger 'WMSYS.NO_VM_DDL'
      - ORA-00604: error occurred at recursive SQL level 1
      - ORA-04068: existing state of packages has been discarded
      - ORA-04061: existing state of package body "WMSYS.LTUTIL" has been invalidated
      - ORA-04065: not executed, altered or dropped package body "WMSYS.LTUTIL"
      - ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LTUTIL"
      - ORA-06512: at "WMSYS.LTADM", line 9437
      - ORA-06512: at "WMSYS.OWM_DYNSQL_ACCESS", line 544
      - ORA-06512: at line 15

Solution

Disable the trigger which caused the issues, then re-run datapatch.

PRCH-1000 : Failed to stop resources running from Oracle home After out-of-place patching

Oracle databases were running from old home '/u01/app/oracle/product/19.0.0/dbhome_1', new home '/u01/app/oracle/product/19.0.0/dbhome_2' is created by excuting 'opatchauto -out-of-place' from old home. 

After pathing, all databases are swithed to new home. After old home was deinstalled successfully, 'srvctl stop home' failed with following errors,

  PRCH-1059 : Failed while checking status of Oracle home

And 'srvctl status home' also failed with following errors,

  PRCH-1059 : Failed while checking status of Oracle home

APEX 404 Not Found after ORDS upgrade to 23.1 or later when SYS shows NO in column COMMON of DBA_USERS in non-cdb

When upgrading Oracle REST Data Services (ORDS) to version 23.1 or later, it completes with warnings as following

WARNING: Procedure APEX_ADMIN could not be added to allow list.
 The function or procedure is a common object.
 You must execute the following as a common user: 
 begin APEX_240100.wwv_flow_listener.sync_ords_gateway_allow_list; end;
WARNING: ORDS migrated 0 entry points from APEX to ORDS PL/SQL
 Procedure Gateway Allow List.

Now accessing APEX will return 404 error as following

                   Not Found
               HTTP Status Code: 404
       Request ID: EHFC-h-DNoA9A0Qo-_W
     Request Timestamp: 2024-12-17T13:20:38.684973Z
               Error Code: ORDS-2201
The procedure named apex could not be accessed, it may not be declared, or the user 
executing this request may not have been granted execute privilege on the procedure, or a 
function specified by security.requestValidationFunction configuration property has prevented 
access. Check the spelling of the procedure, check that the execute privilege has been 
granted to the caller and check the configured security.requestValidationFunction function. If 
using the PL/SQL Gateway Procedure Allow List, check that the procedure has been allowed 
via ords_admin.add_plsql_gateway_procedure.

CAUSE

Since pluggable database was introduced in Oracle database from 12.1, Oracle supplied users (such as SYS, SYSTEM, DBSNMP, etc) are set to YES for new colume COMMON (added from 12.1) of view DBA_USERS no matter whether the database is CDB or non-CDB.

However, if the database was upgraded from 11g or earlier to 12.1, the upgrade may not set COMMON properly because of bug

  25117045 - 12C UPGRADE DOES NOT SET THE COMMON-USER FLAG FOR ORACLE-SUPPLIED USERS

For example, I have a database which upgrade history as following

SQL> select version,comments from dba_registry_history where action='UPGRADE' order by action_time;

VERSION       COMMENTS
_____________ _________________________________________
10.2.0.3.0    Upgraded from 9.2.0.6.0
10.2.0.4.0    Upgraded from 10.2.0.3.0
10.2.0.5.0    Upgraded from 10.2.0.4.0
11.2.0.3.0    Upgraded from 10.2.0.5.0
12.1.0.2.0    Upgraded from 11.2.0.3.0
12.2.0.1.0    Upgraded from 12.1.0.2.0
12.2.0.1.0    Upgraded from 12.1.0.2.0
19.0.0.0.0    Upgraded from 12.2.0.1.0 to 19.7.0.0.0

The COMMON of Oracle supplied users as following

SQL> select username,common from dba_users where oracle_maintained='Y';

USERNAME      COMMON
_____________ _________
SYSTEM                    NO
SYS                       NO
SYSDG                     YES
SYSKM                     YES
SYSBACKUP                 YES
SYSRAC                    YES
DBSNMP                    NO
    ... ...
FLOWS_FILES               YES
APEX_PUBLIC_ROUTER        YES
APEX_240100               YES

It shows that, SYS is not common user, but APEX_240100 is common user. The procedure APEX_ADMIN is owned by COMMON user APEX_240100, which cannot be processed by local (non-common) user SYS. Therefore, ORDS upgrade returned the warnings.

WORKAROUND

The APEX 404 error can be fixed as a workaround by changing the pool.xml of ORDS databse pool.

Remove following line from the file

<entry key="security.requestValidationFunction">ords_util.authorize_plsql_gateway</entry>

Add following ling to the file

<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>

Restart ORDS application.

SOLUTION

Since the root cause is the COMMON of SYS user, we can update the COMMON to YES for Oracle supplied user (including SYS, SYSTEM). Then re-run the ORDS upgrade.

To update COMMON, following the steps,

1. Stop applications which are accessing database

If this is a RAC database, shutdown all instances except one. The remaining steps will be performed on the running instance. Ensure that no application  sessions are connected. Optionally, put the system into restricted session mode so that non-DBA connections cannot be made (alter system enable restricted session).

2. Backup the database

Make a backup of the database, or create a backup of table user$ with CREATE TABLE SELECT FROM 

connect / as sysdba
create table sys.backup_of_user$
   as select * from sys.user$;
create table sys.backup_of_user$_updated
   as select * from sys.user$ where bitand(spare1, 256) = 256;

3. Update the data dictionary using following commands

connect / as sysdba
update user$
 set spare1 = spare1 - bitand(spare1, 128) + 128
 where bitand(spare1, 256) = 256;
commit;

4. Ensure that stale user$ information is removed from the cache

If possible, shutdown and restart the instance. If that is not possible, try to flush the shared pool three times with commands

alter system flush shared_pool;

If RAC, start all other instances. If the system was previously put into restricted session mode, that can now be disabled (alter system disable restricted session).

5. Verify that the users (SYS, SYSTEM, etc) are now common users

SQL> select username,common from dba_users where oracle_maintained='Y';

USERNAME      COMMON
_____________ _________
SYSTEM                    YES
SYS                       YES
SYSDG                     YES
SYSKM                     YES
SYSBACKUP                 YES
SYSRAC                    YES
DBSNMP                    YES
    ... ...
FLOWS_FILES               YES
APEX_PUBLIC_ROUTER        YES
APEX_240100               YES

Script - List blocking session tree in Oracle single instance or RAC database

 File Name

  sess_blocking_tree.sql

Description

  Displays Oracle databsae session blocking tree.

  It support both single instance and RAC

Requirements

 User needs SELECT privilege on following views

    gv$instance

    gv$session

Usage

  Run with SQL*Plus or SQLcl

  @sess_blocking_tree

-- -----------------------------------------------------------------------------------
-- File Name    : sess_blocking_tree.sql
-- Author       : https://www.dbaplus.ca
-- Description  : Displays session blocking heirarchy
-- Requirements : Access to the following views
--                gv$instance
--                gv$session
-- Usage        : @sess_blocking_tree
-- Last Modified: 25-Nov-2024
-- -----------------------------------------------------------------------------------
set termout off
-- ----------------------------------------------------
-- Save current SET variable
-- ----------------------------------------------------
column v_set_save_file new_value V_SET_SAVE_FILE
select 'tmp_SQLPlus_ENV_&_USER' v_set_save_file from dual;
STORE SET &V_SET_SAVE_FILE replace
-- ----------------------------------------------------
-- Save current NLS_DATE_FORMAT
-- ----------------------------------------------------
column v_nls_date_format new_value V_NLS_DATE_FORMAT
select sys_context('USERENV','NLS_DATE_FORMAT') v_nls_date_format from dual;
-- ------------------------------------------------------------------------
-- Show INST_ID column when RAC database has more than one instance running
-- ------------------------------------------------------------------------
column v_inst_id new_value V_INST_ID
select decode(count(*),1,'','INST_ID,') v_inst_id  from gv$instance;
-- ------------------------------------------------------------------------
-- SET env variable
-- ------------------------------------------------------------------------
set linesize 512
set pagesize 999
set verify off
column username format a30
column osuser format a10
column machine format a25
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
set termout on
----------------------------------------------------------------------------- 
SELECT level,
       LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
       s.osuser, &V_INST_ID
       s.sid,
       s.serial#,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       s.logon_Time
from   gv$session s
where  level > 1
       or exists (select 1
               from   gv$session
               where  blocking_session = s.sid and blocking_instance=s.inst_id)
connect by prior s.sid = s.blocking_session
           and prior s.inst_id=s.blocking_instance
start with s.blocking_session is null;
---------------------------------------------
-- Restore SET variable and NLS_DATE_FORMAT
---------------------------------------------
set termout off
start &V_SET_SAVE_FILE
alter session set nls_date_format='&V_NLS_DATE_FORMAT';
column v_set_save_file clear
column v_nls_date_format clear
column v_inst_id clear
set termout on

Oracle Database uses Net Directory Naming with Microsoft Active Directory

 The demonstration is tested on following environment,

Windows Domain controller: DC01.lab.dbaplus.ca - Windows 2022
Workstation 1:  wkstn01 – Windows 11 + Oracle Client 19.3+ RU 19.22
Workstation 2:  wkstn02 – Oracle Linux 9.3 + Oracle Client 19.3

Listener failed to start after Oracle Database out-of-place patching

 After applying patches on Oracle Database home with out-of-place patching. The listener cannot be started with error,

TNS-01201: Listener cannot find executable <ORACLE_HOME>/bin/oracle for SID <ORACLE_SID>

PowerShell script to remotely start / stop MS SQL server on multiple machines

 This PowerShell script picks up server machines from given text file which includes all SQL server host names.

In order to run the script successfully, it needs following requirements,

1. User who runs the script has to be a domain user account who has start/stop SQL server privileges on all SQL servers listed by the text file. 

2. All SQL servers are member of same domain, and each server is listed as one line in a text file.

3. The script file accepts two parameters. First one is operation directive, it should be start, stop or status. Second parameter is full path of SQL server list file, the script will read all servers from the list file.

Install Oracle Database 19c Client on Oracle Linux 9 Subsystem on Windows 11

Beginning with 19.22 Jan2024 RU, the 19c database is certified on Oracle Linux 9.x and Red Hat Enterprise Linux (RHEL) 9.x. The minimum kernel version has to be 5.15.0 with Oracle Linux 9 and 5.14.0 with RHEL.

Therefore, I want to try 19c client on my favorite  Windows Subsystem for Linux which is running Oracle Linux 9. The installation includes following steps,

 1) Update WSL kernel version

 

    It is strongly recommended to update the kernel version to 5.15 or higher which is minimum version for Oracle Database 19c on Oracle Linux 9.

 2) Install Oracle Linux 9.1 Subsystem

    You also have other choices (e.g. Oracle Linux 9.2, Oracle Linux 9.3) if you are installing from Microsoft Store instead of command line.

 3) Configure Oracle Linux 9 for Oracle Database installation

 4) Install Oracle Database 19c (19.22) Client

OEM 13c Log4j Vulnerabilities Security Alert for SQL Developer shipped with Oracle Enterprise Manager (OEM) Cloud Control

The SQL Developer shipped with OEM 13c (13.4.1.0.0 and later) includes Apache Log4j 1.x and 2.x, which is scanned out with following vulnerabilities

  Log4j 2.x :CVE-2021-45046, CVE-2021-44228, CVE-2021-44832, CVE-2021-45105

  Log4j 1.x :CVE-2021-4104, CVE-2022-23302 and CVE-2022-23305

These log4j*.jar are located under SQL Developer directory $OMS_HOME/sqldeveloper.

Since SQL Developer is no longer required by the OEM, it is safe to delete whole SQL Developer directory to comply with the listed vulnerabilites.

$ cd $OMS_HOME
$ rm -rf sqldeveloper

Script - Check LOBSEGMENT Storage Usage of Oracle table BLOB column stored as SECUREFILE or BASICFILE

NAME

  CkSpaceBLOB.sql

DESCRIPTION

  Check the space usage of BLOB column which is stored as LOBSEGMENT type segment  

USAGE

     CkSapceBLOB <table_owner> <table_name> <lob_column_name>

Oracle Database 12.2/18c/19c ORA-00600: [qosdExpStatRead: expcnt mismatch]

Oracle database 12.2, 18c or 19c database alert log file reports ORA-00600: [qosdExpStatRead: expcnt mismatch]. The error message is something like following

2023-01-29T15:55:03.286965-05:00
Errors in file /u01/app/oracle/diag/rdbms/cdb01/cdb01/trace/cdb01_mz00_513756.trc  (incident=97764) (PDBNAME=PDB1):
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [65561], [25], [3125688], [1], [], [], [], [], [], [], []
PDB1(3):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2023-01-29T15:55:03.291141-05:00
Errors in file /u01/app/oracle/diag/rdbms/cdb01/cdb01/trace/cdb01_mz00_513756.trc:
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [65561], [25], [3125688], [1], [], [], [], [], [], [], []

19c database createtion getting ORA-46385 On AUDSYS.AUD$UNIFIED

When creating Oracle 19c databases, got errors as following,

comment on table AUDSYS.AUD$UNIFIED is
                        *
ERROR at line 1:

ORA-46385: DML and DDL operations are not allowed on table

"AUDSYS"."AUD$UNIFIED".

It was reported by Oracle support on 19.5. It is still there when creating non-cdb on 19.16. 

When the Unified Auditing is enabled on Oracle database home, the database creation tries to create a comment on internal Unified Auditing table and gets this error.

It is completely harmless and can be safely ignored.

Upgrade/patch JDK 8 used by Oracle Enterprise Manager 13.5 OMS and Agent

Starting April 2024, a Holistic patch is being introduced in OEM, consolidating all SPB, CPU, and JDK patches into a single patch. Applying a single patch will upgrade JDK, OPatch to the latest and apply all applicable latest FMW product patches on OMS home. OMSPatcher utility will be used to apply Holistic Patching.
There would be no need to upgrade/patch JDK serparately anymore.

This document is for upgrading JDK used by Enterprise Manager (EM) 13.5 in both OMS and Agent home on Linux, Solaris and Windows. 

How to Apply Patch on Oracle Enterprise Manger 13c (13.4/13.5) Agent in online / offline mode

Oracle Enterprise Manager (EM) 13.4/13.5 agent patch is released as one-off patch, Release Update, or bundle patches. These patches can be applied manually or through EM Cloud Control console.

Patch Oracle WebLogic Server 12c Release 2 12.2.1.x (12.2.1.3, 12.2.1.4) including WLS, OHS, Coherence, ADR and RDA

This instruction applies to Oracle WebLogic Server (WLS) 12.2.1.x standalone (Lite, Slim, Generic), Oracle Fusion Middleware (FMW) 12.2.1.x Infrastructure and WLS 12.2.1.x installed with Oracle Enterprise Manager (OEM) 13c.

The installation of these WLS releases includes following components,

  OPatch

  WebLogic Server

  Coherence Server

  Oracle HTTP Server (OHS)

  Automatic Diagnostic Repository (ADR) 

Oracle Data Pump Export/Import SODA Collection Data

Simple Oracle Document Access (SODA ) collections can be exported and imported using Oracle Data Pump Utilities starting with version 19.6.

In Oracle database, a SODA collection is analogous to database table. A SODA collection is mapped to /backed by a table. A SODA document is analogous to, and is in fact backed by, a row of a database table. The row has one column for each document component: key, content, version, and so on.

ORA-02291: integrity constraint (WWV_FLOW_FND_GU_INT_G_FK) violated - parent key not found - When Importing Workspace In APEX configured with ORDS & WebLogic

When creating users during importing workspace in APEX, got ORA-02291 error like following

ORA-02291: integrity constraint (APEX_200100.WWV_FLOW_FND_GU_INT_G_FK) violated - parent key not found
ORA-06512: at "APEX_200100.WWV_FLOW_FND_USER_INT", line 2067
ORA-06512: at "APEX_200100.WWV_FLOW_FND_USER_API", line 339
ORA-06512: at line 2
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause:    A foreign key value has no matching primary key value.
*Action:   Delete the foreign key or add a matching primary key.

This error occurs because pre-created user groups (SQL Developer, etc) do not have the same IDs in two APEX instances. It was reported as bug of APEX 4.2.1 when ORDS is used and deployed with WebLogic. But it also reported on APEX 18.1 and I got it on my APEX  20.1.

EM 12c/13c Configure Enterprise Manager with custom or third-party SSL Certificates

When deciding to configure Oracle Enterprise Manager with custom or third-party SSL certificates,  you usually need to complete configuration tasks with following EM components

  *  EM Cloud Control Console

  *  Oracle Management Service (OMS) and EM Agent

  *  WebLogic Server (WLS)

The configuration can be done in following steps.

Script - Enable Customized Password Complexity in Oracle non-cdb or cdb environment

The script creates a password complexity verification function, and enable the function with database profile. It also sets password policy with profile.

Export/import APEX workspace and application with SQLcl

One of the reason why I prefer SQLcl to SQL*Plus is that SQLcl has built-in "apex" commands. APEX workspace and application can be easily export / import with SQLcl.

Database failed to start with errors ORA-01565 ORA-17503 ORA-27300 ORA-27301 ORA-27302

 When starting database, get errors ORA-01565 ORA-17503 ORA-27300 ORA-27301 ORA-27302. For example,

$ srvctl start database -db db01
PRCR-1079 : Failed to start resource ora.db01.db
CRS-5017: The resource action "ora.db01.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/db01/parameterfile/spfiledb01.ora'
ORA-17503: ksfdopn:10 Failed to open file +DATA/db01/parameterfile/spfiledb01.ora
ORA-27300: OS system dependent operation:open failed with status: 13
ORA-27301: OS failure message: Permission denied
ORA-27302: failure occurred at: sskgmsmr_7
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/host01/crs/trace/ohasd_oraagent_grid.trc".

CRS-2674: Start of 'ora.db01.db' on 'host01' failed

Oracle SQLcl installation and configuration

Oracle SQL Developer Command Line (SQLcl) is a free command-line interface for Oracle Database that combines the power of SQL*Plus and SQL Developer. It allows you to interactively or batch execute SQL and PL/SQL. SQLcl provides in-line editing, statement completion, and command recall for a feature-rich experience, as well as supporting your previously written SQL*Plus scripts.

Script Find the Regular Source File of the Multi-level Symbolic Link on Linux / Unix

 A bash script is used to find out the final regular source file or directory of a multi-level symbolic on Linux and Unix (AIX, Solaris) platform.

Oracle RAC 19c pdb open failed with ORA-12963: A read-only instance cannot be the first instance to open a database

When opening pluggable database (pdb) on one node of two-node 19c RAC, got following errors

sys@rac01> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-12963: A read-only instance cannot be the first instance to open a database.

Oracle 12.2 EXPDP Fails Exporting Full PDB with ORA-39126 On KUPW$WORKER.FETCH_XML_OBJECTS

This issue happens in Oracle database 12.2 or higher version, the example in this post happened in 19.13.

Configure Reverse Path Filter parameter "rp_filter" on Oracle Linux and Red Hat Enterprise Linux

When installing Oracle Grid Infrastructure (GI) 12c or higher version (18c, 19c, 21c), multiple network interfaces (NIC) can be used as private interconnection to implement inter-communication load balance and failover. However, if this configuration is adopted on Oracle Linux (OL) 6 or Red Hat Enterprise Linux (RHEL) 6 or higher, the GI installation prerequists checking shows error 

Oracle Cloud Infrastructure Plugin xxx not present for instance ocid1.instance.oc1.*

When you enable or configure moniter or managerment features on compute instance in Oracle Cloud Infrastructure (OCI), you have to enable the relavant Oracle Cloud Agent plugin. 

Oracle 19c GI installation failed with PRVG-13605 : Query of chrony daemon for its time sources could not be performed

The issue happens on Oracle Linux / Red Hat Enterprise Linux 7/8 on which chronyd is adopted as the network time protocol daemon instead of ntpd. When installing Oracle Grid Infrastructure 19c on these platform, the installation fails during prerequisit check, the message shows that

Verifying Network Time Protocol (NTP) ...FAILED
  Verifying chrony daemon is synchronized with at least one external time
  source ...FAILED
  rac01: PRVG-13605 : Query of chrony daemon for its time sources could not
         be performed on node "rac01".
         PRVG-2043 : Command "/usr/bin/chronyc sources " failed on node
         "rac01" and produced the following output:
         506 Cannot talk to daemon

  rac02: PRVG-13605 : Query of chrony daemon for its time sources could not
            be performed on node "rac02".
            PRVG-2043 : Command "/usr/bin/chronyc sources " failed on node
            "rac02" and produced the following output:
            506 Cannot talk to daemon

EM 12c/13c Send Email Notification When OMS or Repository Database Down

In Oracle Enterprise Manager 12c/13c deployment, normally the OMS is responsible for sending all metric alert notifications, but in cases where the OMS or repository database is down, it is not able to do this. Therefore,  the Out of Band email notification is introduced as a backup mechanism for sending email notification when the OMS or repository database itself is down.

EM 13c Agent is unreachable as its first severity has not yet come after blackout end

The agent status shows "Agent Unreachable (Post Blackout)" in Oracle Enterprise Manager 13c console. On the agent home page, it shows following error message,

  Agent is unreachable as its first severity has not yet come after blackout end.

It is seen in EM 13c, can can be fixed by running following commands on agent host,

  ${AGENT_INST_HOME}/bin/emctl stop agent

  ${AGENT_INST_HOME}/bin/emctl  clearstate agent

  ${AGENT_INST_HOME}/bin/emctl start agent

RMAN left defunct server processes and NetBackup nborautil processes

RMAN jobs can successfully backup database to tape with NetBackup, but leave server processes (RMAN channels) and NetBackup nborautil processes running. The processes look like defunct processes.

Mount DVD/CDROM with command on Linux CentOS / Red Hat / Oracle Linux / Ubuntu

 Minimal install is an important security for deploying Linux. It means only essential packages are installed. Therefore, GUI is usually not available. It becomes helpful to know how to mount CDROM/DVD when you need to install packages/software from DVD.

Oracle 19c Installation on Red Had Enterprise Linux 8 / Oracle Linux 8 – [INS-08101] Unexpected error while executing the action at state: ‘supportedOSCheck’

When installing Oracle 19c Grid Infrastructure (GI) or 19c Database (runInstaller), got following error,

[INS-08101] Unexpected error while executing the action at state: 'supportedOSCheck'

Error details,

Cause - No additional information available.

Action - Cantact Oracle Spport Services or refer to the software manual.

Summary
 - java.lang.NullPointerException

Use oradebug To List All Internal Events Set In Oracle Database

Oracle internal events can be set at both sesson level and system level as following,

Identify SQL statements consuming temporary tablespace

You may have to find out the SQL statements which are using temporary tablespace when you get storage pressure for temporary tablespace.

Oracle 19c root.sh not working while gridSetup.sh switchGridHome to software-only installation

When the command "griddSetup.sh -switchGridHome" is used to perform Oracle database 19c out of place (OOP) patching, it will prompt to run root.sh on each cluster node. And root.sh will switch Grid Home from old one (without new patches) to new one (applied with new patches) on each node and also patch Grid Infrastructure Management Repository (GIMR) database on last node of the cluster.

EM 12c/13c Agent Unreachable (Agent is running but is currently not read to accept client requests)

EM 12c/13c create Agent Unreachable alerts as follows: 

Host=host01.dbaplus.ca 
Target type=Agent 
Target name=host01.dbaplus.ca:3872 
Categories=Availability 
Message=Agent Unreachable (REASON = The agent is running but is currently not ready to accept client requests). Host is reachable. 
Severity=Critical 
Event reported time=Nov 6, 2021 12:36:48 PM EDT 
Operating System=SunOS
Platform=sparc
Event Type=Target Availability 
Event name=Status 
Availability status=Agent Unreachable
Update Details:
Agent Unreachable (REASON = The agent is running but is currently not ready to accept client requests). Host is reachable.

Javascript/CSS: Add Copy-to-Clipboard Button in HTML Code Example

When feeling tired to select the text before copying them, I decide to add a COPY button to every code example block in my blog. The button will copy the whole example block to clipboard. I reached it through 4 steps.

Javascript/CSS: Add Line Number to HTML Source Code Block

 When I shared the source code in my blog before, I used to reference source code by highlighting the code lines with HTML tag. It was not an issue until I had many references to different lines of a long clode block. It could take too much of my time to highlight or mark the reference when I post an article. Therefore, I added the line number to the source code in my posts. The line-numbered code block looks like,

EM 12c/13c The listener is down: Failed to run lsnrctl

The listener is up and running. However, the lisener target shows as Down in the Oracle Enterprise Manager Cloud Control Console and the incident message as following,

The listener is down: Failed to run lsnrctl.

Change SYS password in Oracle Data Guard

In Oracle Data Guard configuration, redo transport uses Oracle Net sessions to transport redo data. These redo transport sessions are authenticated using either the Secure Socket Layer (SSL) protocol or a remote login password file. Most time, remote login password file is adopted because of the difficulty of meeting SSL authentication requirements.

Database startup failed with ORA-600 [dbgripmg_2: infinite init action] [ADR_CONTROL_AUX]

Oracle database startup failed with ORA-00600 as following

SQL> startup

ORACLE instance started.

AQ Queue Monitor Does Not Change Message MSG_STATE from WAIT to READY on RAC

In RAC database, the Advanced Queue (AQ) message MSG_STATE (column of view AQ$<QUEUE_TABLE>) stays with value 'WAIT' and will not be changed to 'READY' any more. It usually happens after database was shut down with immediate/abort option followed by database patching/upgrading. 

EM 12c/13c Update Oracle Home Path of Targets with SQL

 As out-of-place patching can dramatically help minimize database downtime, especially when multiple databases run out of same home, it does not require all databases shut down at same time, more and more DBA adopt this method. However, if you are patching a home which host multiple databases, you may feel frustrated to update the databases' Oracle Home property in Oracle Enterprise Manage by clicking through different windows and changing it one by one. Now, the first question you will ask is how I can update all databases at one time. The answer is to run SQL (PL/SQL) in EM repository database.

EM 12c/13c Change Lifecycle Status with SQL

The Lifecycle Status property of EM targets is often used to prioritize the notification of incident rule. For example, DBA will be paged while database which Lifecycle Status is 'Production' crashed, and only emailed for 'Test' database. This post is going to show how to use sql scripts to find out targets with different Lifecycle Status and how to change the Lifecycle Status.

EM 12c/13c How to find out new discovered targets with SQL

 When agent is deployed to new host, or new targets is installed or created on existing hosts, Oracle Enterprise Manger (EM) Cloud Control can discover the targets automatically. However, EM does not promote the new discovered targets automatically.

Script Run datapatch against all running instance in parallel

This script can be used to run datapatch against all currently running instances in parallel to apply sql patches after patches are applied on Oracle homes. It is helpful for post patching operations. It works for different instances running out of different Oracle homes and different home with different owners.

Install PostgreSQL on RHEL/OL/CentOS 6/7/8

There are many ways to install PostgreSQL on Linux box. Here, I am going to use yum to install different version of PostgreSQL on Red Hat Enterprise Linux (RHEL) 7/8, it also works for Oracle Linux (OL) / CentOS 7/8.

Java Stored Procedure failed with java.lang.OutOfMemoryError

Oracle database Jave stored procedure failed with "java.lang.OutOfMemoryError", the error stack looks like

ORA-29532: Java call terminated by uncaught Java exception: java.lang.OutOfMemoryError

ORA-06512: at "USER01.MYJAVAPROC", line 28

ORA-06512: at line 7

This error is thrown when there is insufficient space to allocate an object in the Java heap. In this case, The Java garbage collector cannot make space available to accommodate a new object, and the heap cannot be expanded further.  

Oracle 19.11 roothas.sh failed with "Out of memory" on AIX

When applying Oracle GI Release Update 19.11.0.0.210420 on AIX, command "roothas.sh -postpatch" failed with "Out of memory" as following

[root@host01]# /u01/app/oracle/product/19.0.0/grid_1/crs/install/roothas.sh -postpatch

Using configuration parameter file: /u01/app/oracle/product/19.0.0/grid_1/crs/install/crsconfig_params

Configure yum with proxy server on RHEL/OL/Fedora

When using yum to maintain packages on hosts running Red Hat Enterprise Linux (RHEL), Oracle Linux (OL) or Fedora, you may have to configure proxy option if the host is running behind firewall.

How to Permanently Change PowerShell Prompt

 The PowerShell command prompt indicates that PowerShell is ready to run a command,

PS C:\>

It is determined by the built-in Prompt function and can be customized by running following command to re-define Prompt function,

  function prompt {"<Script block>"}

Here, <Script block> is script block which will determine/generate prompt value (character string). For example, following have prompt include current date and time

PS C:\>

PS C:\> function Prompt {"$(Get-Date)> "}

06/03/2021 11:39:23>

06/03/2021 11:39:25>

The change is only valid for current session, the prompt of new session is still default value. In order to keep new prompt for all sessions, you have to create your own Prompt function and saving it in your PowerShell profile as following,

1. Find out your PowerShell profile file with "$profile"

PS C:\> $profile

C:\Users\admin\Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1

PS C:\>

2. Add customized Prompt function to profile

If the file or directory does not exist, you can manually create it, then add customized Prompt function to the file. For example, add following lines

function Prompt { "PS [" + ${ENV:USERNAME} + "@" + ${ENV:COMPUTERNAME} + "]> "}

The prompt will be in the format "PS [user-name@computer-name]> " in all new PowerShell sessions.

Grid Infrastructure 12.2 restore OCR, Voting File and mgmtdb when disk group corrupted

For some reason, Oracle desupported the placement of OCR and voting files directly on a shared file system from Grid Infrastructure 12.2 until rescinding the desupport for Standalone Clusters from 19.3. Therefore, when GI 12.2 is installed, OCR, Voting file, and OCR backup location are, by default, configured in the ASM disk group, and mostly likely in the same disk group. When the disk group cannot be mounted because of any issues, the cluster will not be brought up anymore.

Technically, GI has to be re-configured as like a new installation, that could be a big job. However, it may not be that bad. Although 12.2 does not allow you have OCR backup location out of ASM disk group, DBA still can copy the OCR backup file from ASM disk group to file system with command 'asmcmd cp'. I am going to demonstrate how we can maximize the opportunity of bringing the cluster backup without rebuild/reinstall it.

Windows How to log all output on the console to text file

Windows Command Prompt console does not have built-in facility to log the console output to file. In order to implement logging function, PowerShell has to be used instead of normal command console.

To enable logging, running following command under PowerShell prompt,

  Start-Transcript [-Path] "<file-name>" [-Append]

Here, 

  -Path parameter is used to specify the file "<file-name>" and "<file-name>" is full path of log file in which output messages will be saved

  -Append Indicates that the logging text will be added to the end of an existing file instead of overriding.

To stop logging, running command

  Stop-Trasnscript

Note: PowerShell can be started on Windows by clicking "Windows PowerShell" applicaiton or running command "PowerShell" in normal "Command Prompt" console, but DO NOT start PowerShell in "Windows Terminal". If the PowerShell is started from "Window Terminal", Start-Transcript may not be able to log everything. For example, non-Windows built-in command (e.g. sqlplus) output cannot be completely logged.

  

Example

PS> Start-Transcript -Path "C:\temp\test.log"

Transcript started, output file is C:\temp\test.log

PS>

PS> sqlplus system/oracle@orcl

SQL*Plus: Release 12.2.0.1.0 Production on Sun May 9 20:30:15 2021

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

Last Successful login time: Tue Apr 13 2021 16:46:29 -04:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

system@orcl> select host_name from v$instance;

HOST_NAME

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

host01

system@orcl> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

PS>

PS> Stop-Transcript

Transcript stopped, output file is C:\temp\test.log

The content of file C:\temp\test.log

**********************

Windows PowerShell transcript start

Start time: 20210509202857

Username: dbaplus

RunAs User: dbaplus

Configuration Name: 

Machine: wkstn01 (Microsoft Windows NT 10.0.19042.0)

Host Application: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Process ID: 15732

PSVersion: 5.1.19041.906

PSEdition: Desktop

PSCompatibleVersions: 1.0, 2.0, 3.0, 4.0, 5.0, 5.1.19041.906

BuildVersion: 10.0.19041.906

CLRVersion: 4.0.30319.42000

WSManStackVersion: 3.0

PSRemotingProtocolVersion: 2.3

SerializationVersion: 1.1.0.1

**********************

Transcript started, output file is C:\temp\test.log

PS> sqlplus system/oracle@orcl

SQL*Plus: Release 12.2.0.1.0 Production on Sun May 9 20:30:15 2021

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

Last Successful login time: Tue Apr 13 2021 16:46:29 -04:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

system@orcl> select host_name from v$instance;

HOST_NAME

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

host01

system@orcl> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

PS> Stop-Transcript

**********************

Windows PowerShell transcript end

End time: 20210509203043

**********************

OEM 12c/13c Database Target Discovered and Named with host name as suffix

 When EM discovers database targets, it generates default target name for the database in one of following formats,

  db_unique_name.db_domain   if both parameters db_unique_name and db_domain are set

  db_unique_name             if parameter db_unique_name is set, but db_domain is not set

  db_name.db_domain          if parameter db_unique_name is not set, but db_domain is set

  db_name                    if neither db_unique_name nor db_domain is set

EM discovery script retrieves these parameters' values from parameter file (pfile or spfile) of the database, not from running instance. If the discovery process has issue to locate or process parameter file, EM will name database target in format,

  <sid>_<hostname>

  

Therefore, when you find the new discovered database target is named in this format, it means EM agent discovery script has issue with parameter file. The details can be found from agent trace file "<AGENT_INST_HOME>/sysman/log/emagent_perl.trc". Most popular error messages look like

  ERROR:  initParameterFileUtl::convertSPFileToPFile: Failed to convert spfile

  

or

  ERROR:  initParameterFileUtl::getParameterFile: Cannot find any init parameter file for instance <instancename> in oracle home  <oracle_home>

For example, when Oracle Restart (standalone Grid Infrastructure) is installed and ASM storage is configured on server host1.dbaplus.ca, DBCA creates a database orcl (sid & db_name) in ASM diskgroup, the spfile will also be saved in ASM diskgroup and no parameter file (pfile/spfile) will be created under directory <ORACLE_HOME>/dbs. The database will be discovered by EM with default name orcl_host1.dbaplus.ca and you will see errors in agent trace file "emagent_perl.trc",

oracledb.pl: 2021-05-04 04:03:08,923: INFO:  DB_LISTENER_DISCOVERY:  processing sid="orcl"

oracledb.pl: 2021-05-04 04:03:08,926: ERROR:  initParameterFileUtl::getParameterFile: Cannot find any init parameter file for instance orcl in oracle home /u01/app/oracle/product/19.9.0/dbhome_1/dbs

oracledb.pl: 2021-05-04 04:03:08,930: ERROR:  initParameterFileUtl::getParameterFile: Cannot find any init parameter file for instance orcl in oracle home /u01/app/oracle/product/19.9.0/dbhome_1/dbs

oracledb.pl: 2021-05-04 04:03:09,065: ERROR:  initParameterFileUtl::getParameterFile: Cannot find any init parameter file for instance orcl in oracle home /u01/app/oracle/product/19.9.0/dbhome_1/dbs

oracledb.pl: 2021-05-04 04:03:09,069: ERROR:  initParameterFileUtl::getParameterFile: Cannot find any init parameter file for instance orcl in oracle home /u01/app/oracle/product/19.9.0/dbhome_1/dbs

The reason is that discovery script cannot find parameter file from <ORACLE_HOME>/dbs. To fix this problem, create a pfile 'initorcl.ora' under <ORACLE_HOME>/dbs with following content,

   spfile='<full path of spfile saved in diskgroup>'

Note: DO NOT leave any SPACE character at the beginning of the line (before word "spfile"). If you did, you will not see any errors in the trace file, but the target name will be <sid>_<hostname>.

Sample init file

$ srvctl config database -db orcl | grep spfile

Spfile: +DATA/orcl/PARAMETERFILE/spfile.919.1071658047

$

$ echo "spfile='+DATA/orcl/PARAMETERFILE/spfile.919.1071658047'" > $ORACLE_HOME/dbs/initorcl.ora

$

$ cat $ORACLE_HOME/dbs/initorcl.ora

spfile='+DATA/orcl/PARAMETERFILE/spfile.919.1071658047'