oem – grepOra

OEM: Metric “Tablespace Allocation Metric” not Collected – Agent is Running but Not Ready

By matheusdba 5 de January de 2022 20 de September de 2022

Hi all,

That’s an interesting case with OEM. A client reported the metric “Tablespace Allocation Metric” is not being updated on OEM for a specific database. In this case, the last gathering was in Nov/2020, as you’ll see.

When checking for it, the first try as usual was checking on the OEM agent status, and here is what I got:

oracle:dbserver@mydb02 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 12.1.0.5.0
OMS Version            : (unknown)
Protocol Version       : 12.1.0.1.0
Agent Home             : /u01/app/oracle/product/agent12c/agent_inst
Agent Log Directory    : /u01/app/oracle/product/agent12c/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/product/agent12c/core/12.1.0.5.0
Agent Process ID       : 61641
Parent Process ID      : 61394
Currently initializing component             : Target Manager (2) (54 of 70)
Receivelet Interaction Manager Current Activity: Outstanding receivelet event tasks
----------------------------------
        TargetID = oracle_pdb.c4test_PDB1 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c3test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c3test_PDB2 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30 
        TargetID = oracle_pdb.c4test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c6test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c3test_PDB3 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30 
        TargetID = rac_database.c1prod - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30 


Target Manager Current Activity              : Compute Dynamic Properties (total operations: 37, active: 7, finished: 28)


Current target operations in progress
-------------------------------------
        oracle_pdb.c6test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c4test_PDB1 - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c3test_PDB2 - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c3test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c4test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c3test_PDB3 - LOAD_TARGET_DYNAMIC running for 120 seconds
        rac_database.c1test - LOAD_TARGET_DYNAMIC running for 120 seconds


Dynamic property executor tasks running
------------------------------


---------------------------------------------------------------
Agent is Running but Not Ready

Agent not ready, that’s interesting.
Trying then to clear the agent state as this has solved some previous similar cases:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl clearstate agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
EMD clearstate completed successfully

Now running the problematic metric manually:

oracle:dbserver02@c1test2 /u01/app/oracle: runCollection c1test_DW:oracle_pdb tbspAllocation                                                                <
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD runCollection error:The agent is running but is currently not ready to accept client requests

Ok, trying to just upload the case:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl upload
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error:The agent is running but is currently not ready to accept client requests

Maybe something is stuck, so let’s kill the process and start all over again:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl stop agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Stopping agent ...

 stopped.

Making sure we have no agent process running:

oracle:dbserver02@c1test2 /u01/app/oracle:  ps -ef | grep java | agent
oracle:dbserver02@c1test2 /u01/app/oracle:

Also adjusting the threshod for metric running:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl  setproperty agent -a
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
EMD setproperty succeeded
oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl setproperty agent -allow_new -name _cancelThread  -value 210
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
EMD setproperty succeeded

And starting the agent:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Starting agent ........................................................................................................................... started but not ready.

On the Agent log:

oracle:dbserver02@c1test2 /u01/app/oracle: tail /u01/app/oracle/product/agent12c/agent_inst/sysman/log/gcagent.log
oracle:dbserver02@c1test2 /u01/app/oracle:
2020-12-20 13:15:03,457 [35:686116F5] DEBUG - StatusAgentAction: satisfyRequest Begin
2020-12-20 13:15:03,457 [35:686116F5] DEBUG - Agent Overall Health: 0
2020-12-20 13:15:03,457 [35:686116F5] DEBUG - StatusAgentAction: satisfyRequest End
Response:
initializing
2020-12-20 13:15:03,457 [35:686116F5] INFO - >>> Reporting response: StatusAgentResponse (initializing) (request id 1) <<< 2020-12-20 13:15:03,457 [35:686116F5] DEBUG - closing request input stream for "StatusAgentRequest (AGENT timeout:300)" 2020-12-20 13:15:03,457 [35:686116F5] DEBUG - overriding the buffer with a thread local copy (size: 8192b) 2020-12-20 13:15:03,458 [35:686116F5] DEBUG - closing request output stream for "StatusAgentRequest (AGENT timeout:300)" 2020-12-20 13:15:03,458 [35:686116F5] DEBUG - StatusAgentAction.call() is complete. 2020-12-20 13:15:03,458 [35:B5326F3F:HTTP Listener-35 - /emd/lifecycle/main/] DEBUG - removing entry for emdctl@18081@dbserver02=>[160849530330001] completely
2020-12-20 13:15:03,458 [35:B5326F3F] DEBUG - requests executed.
2020-12-20 13:15:03,458 [35:B5326F3F] DEBUG - HTTPListener Threads deallocated resource back to LifecycleRequestHandler partition
2020-12-20 13:15:03,458 [35:3C0B0663:HTTP Listener-35] DEBUG - using connection SCEP@1197017148 [d=true,io=1,w=true,b=false|false],NOT_HANDSHAKING, in/out=0/0 Status = OK HandshakeStatus = NOT_HANDSHAKING
bytesConsumed = 5 bytesProduced = 26
2020-12-20 13:15:03,780 [35:3C0B0663] DEBUG - using connection SCEP@1197017148  [d=true,io=1,w=true,b=false|false],NOT_HANDSHAKING, in/out=0/0 Status = OK HandshakeStatus = NOT_HANDSHAKING
bytesConsumed = 26 bytesProduced = 5
2020-12-20 13:15:06,986 [31:858161EB] DEBUG - Submitting task SchedulerHeartbeat for execution
2020-12-20 13:15:06,986 [395:1AE716D8] DEBUG - Begin task SchedulerHeartbeat on Thread: GC.SysExecutor.8
2020-12-20 13:15:06,986 [395:F944F4C8:GC.SysExecutor.8 (SchedulerHeartbeat)] DEBUG - Scheduler heartbeat
2020-12-20 13:15:06,988 [395:F944F4C8] DEBUG - Scheduling next SchedulerHeartbeat after delay 29998 including periodShift of 0 milliseconds
2020-12-20 13:15:06,988 [395:1AE716D8:GC.SysExecutor.8] DEBUG - End task SchedulerHeartbeat
2020-12-20 13:15:07,016 [31:858161EB] DEBUG - Submitting task HeapMonitorTask for execution
2020-12-20 13:15:07,017 [396:1AE716D9] DEBUG - Begin task HeapMonitorTask on Thread: GC.SysExecutor.9
2020-12-20 13:15:07,017 [396:391F60D7:GC.SysExecutor.9 (HeapMonitorTask)] DEBUG - Scheduling next HeapMonitorTask after delay 5000 including periodShift of 0 milliseconds
2020-12-20 13:15:07,017 [396:1AE716D9:GC.SysExecutor.9] DEBUG - End task HeapMonitorTask
2020-12-20 13:15:12,017 [31:858161EB] DEBUG - Submitting task HeapMonitorTask for execution
2020-12-20 13:15:12,017 [37:1AE716D0] DEBUG - Begin task HeapMonitorTask on Thread: GC.SysExecutor.0
2020-12-20 13:15:12,017 [37:FE21F10E:GC.SysExecutor.0 (HeapMonitorTask)] DEBUG - Scheduling next HeapMonitorTask after delay 5000 including periodShift of 0 milliseconds
2020-12-20 13:15:12,017 [37:1AE716D0:GC.SysExecutor.0] DEBUG - End task HeapMonitorTask
2020-12-20 13:15:12,189 [33:6D553CF6] DEBUG - HTTPListener Threads deallocated resource back to LifecycleRequestHandler partition
2020-12-20 13:15:12,190 [35:3C0B0663] DEBUG - using connection SCEP@1611645943  [d=true,io=1,w=true,b=false|false],NOT_HANDSHAKING, in/out=0/0 Status = OK HandshakeStatus = NOT_HANDSHAKING
bytesConsumed = 100 bytesProduced = 121
2020-12-20 13:15:12,191 [35:7107E334:HTTP Listener-35 - /emd/persistence/main/] DEBUG - HTTPListener Threads allocated resource from LifecycleRequestHandler partition
2020-12-20 13:15:17,017 [31:858161EB] DEBUG - Submitting task HeapMonitorTask for execution
2020-12-20 13:15:17,018 [45:1AE716D1] DEBUG - Begin task HeapMonitorTask on Thread: GC.SysExecutor.1
2020-12-20 13:15:17,018 [45:CBCC52CF:GC.SysExecutor.1 (HeapMonitorTask)] DEBUG - Scheduling next HeapMonitorTask after delay 5000 including periodShift of 0 milliseconds
2020-12-20 13:15:17,018 [45:1AE716D1:GC.SysExecutor.1] DEBUG - End task HeapMonitorTask

Following MOS Enterprise Manager12c: Oracle Database Tablespace Monthly Space Usage shows no data (Doc ID 1536654.1), a few changes were made:

$/AGENT_INST/bin/emctl setproperty agent -allow_new -name MaxInComingConnections -value 150
$/AGENT_INST/bin/emctl setproperty agent -allow_new -name _cancelThread  -value 210

The status before the change:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 12.1.0.5.0
OMS Version            : (unknown)
Protocol Version       : 12.1.0.1.0
Agent Home             : /u01/app/oracle/product/agent12c/agent_inst
Agent Log Directory    : /u01/app/oracle/product/agent12c/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/product/agent12c/core/12.1.0.5.0
Agent Process ID       : 61641
Parent Process ID      : 61394
Currently initializing component             : Target Manager (2) (54 of 70)
Receivelet Interaction Manager Current Activity: Outstanding receivelet event tasks
----------------------------------
        TargetID = oracle_pdb.c4test_PDB1 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c3test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c3test_PDB2 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30 
        TargetID = oracle_pdb.c4test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c6test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c3test_PDB3 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30 
        TargetID = rac_database.c1test - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30 

Target Manager Current Activity              : Compute Dynamic Properties (total operations: 37, active: 7, finished: 28)

Current target operations in progress
-------------------------------------
        oracle_pdb.c6test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c4test_PDB1 - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c3test_PDB2 - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c3test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c4test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c3test_PDB3 - LOAD_TARGET_DYNAMIC running for 120 seconds
        rac_database.c1test - LOAD_TARGET_DYNAMIC running for 120 seconds

Dynamic property executor tasks running
------------------------------


---------------------------------------------------------------
Agent is Running but Not Ready

And the status after the change:

oracle:dbserver02@c1test2 /u01/app/oracle:  /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 12.1.0.5.0
OMS Version            : 12.1.0.5.0
Protocol Version       : 12.1.0.1.0
Agent Home             : /u01/app/oracle/product/agent12c/agent_inst
Agent Log Directory    : /u01/app/oracle/product/agent12c/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/product/agent12c/core/12.1.0.5.0
Agent Process ID       : 56994
Parent Process ID      : 56654
Agent URL              : https://dbserver02:3872/emd/main/
Local Agent URL in NAT : https://dbserver02:3872/emd/main/
Repository URL         : https://omsweb:4903/empbs/upload
Started at             : 2020-12-20 13:08:35
Started by user        : oracle
Operating System       : Linux version 3.10.0-957.27.2.el7.x86_64 (amd64)
Last Reload            : (none)
Last successful upload                       : 2020-12-20 13:40:41
Last attempted upload                        : 2020-12-20 13:40:41
Total Megabytes of XML files uploaded so far : 1.02
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 10.85%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2020-12-20 13:40:40
Last successful heartbeat to OMS             : 2020-12-20 13:40:40
Next scheduled heartbeat to OMS              : 2020-12-20 13:41:40

---------------------------------------------------------------
Agent is Running and Ready

Great! Agent issue resolved.
However, the metric is not being gathered not even after running it manually:

oracle:dbserver01@c1test1 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl control agent runCollection c1test_CDBROOT:oracle_pdb tbspAllocation
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD runCollection completed successfully

oracle:dbserver01@c1test1 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl status agent scheduler | grep tbspAllocation
2020-12-28 23:05:14.562 : rac_database:c1test:tbspAllocation_cdb
2020-12-29 03:07:21.988 : rac_database:c4prod:tbspAllocation_cdb
2020-12-29 03:08:11.888 : rac_database:c6prod:tbspAllocation_cdb
2020-12-29 03:09:39.103 : rac_database:c2prod:tbspAllocation_cdb
2020-12-29 03:09:55.372 : rac_database:c3prod:tbspAllocation_cdb

oracle:dbserver01@c1test1 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl control agent runCollection c1test_DW:oracle_pdb tbspAllocation
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD runCollection completed successfully

On OEM Repository database:

SQL> select TARGET_NAME,TARGET_TYPE,TARGET_GUID,max(ROLLUP_TIMESTAMP )
from mgmt$metric_daily where TARGET_NAME like '%c1test%'
and TARGET_TYPE='oracle_pdb'
and METRIC_NAME='tbspAllocation'
group by TARGET_NAME,TARGET_TYPE,TARGET_GUID;  2    3    4    5

TARGET_NAME                    TARGET_TYPE          TARGET_GUID                      MAX(ROLLUP_TIMESTAM
------------------------------ -------------------- -------------------------------- -------------------
c1test_DW         oracle_pdb           7B1DF5DD4555EB978330A6D522004D44 2020-11-12 00:00:00
c1test_CDBROOT    oracle_pdb           4CE72911295C0287E053837F649B7D0E 2020-11-12 00:00:00


SQL> select TARGET_NAME,TARGET_TYPE,TARGET_GUID,ROLLUP_TIMESTAMP from mgmt$metric_daily where TARGET_NAME like '%c1test%' and TARGET_TYPE='oracle_pdb' and ROLLUP_TIMESTAMP>sysdate-3 order by 4

TARGET_NAME                    TARGET_TYPE          ROLLUP_TIMESTAMP       AVERAGE
------------------------------ -------------------- ------------------- ----------
c1test_DW         oracle_pdb           2020-11-06 00:00:00  1575.9375
c1test_DW         oracle_pdb           2020-11-07 00:00:00  1575.9375
c1test_DW         oracle_pdb           2020-11-08 00:00:00  1575.9375
c1test_DW         oracle_pdb           2020-11-09 00:00:00  1575.9375
c1test_DW         oracle_pdb           2020-11-10 00:00:00  1575.9375
c1test_DW         oracle_pdb           2020-11-11 00:00:00  1575.9375
c1test_DW         oracle_pdb           2020-11-12 00:00:00  1575.9375
c1test_CDBROOT    oracle_pdb           2020-11-05 00:00:00 37581.5625


TARGET_NAME                    TARGET_TYPE          ROLLUP_TIMESTAMP       AVERAGE
------------------------------ -------------------- ------------------- ----------
c1test_CDBROOT    oracle_pdb           2020-11-08 00:00:00  227138.75
c1test_CDBROOT    oracle_pdb           2020-11-09 00:00:00 455087.688
c1test_CDBROOT    oracle_pdb           2020-11-10 00:00:00 278230.875
c1test_CDBROOT    oracle_pdb           2020-11-11 00:00:00 208727.188
c1test_CDBROOT    oracle_pdb           2020-11-12 00:00:00 454964.063

Ok, so in summary: After fixing all issues on OEM side, everything running fine, still the database metrics are not being updated.

Long story short: After some investigation, bumped in MOS Database Hangs With Simple Queries like on view dba_data_files & dba_free_space (Doc ID 2665935.1)
Turns out this seemed to be a match. So proceeding with the recommendation:

SQL> alter session set container=DW;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 DW                             READ WRITE NO
SQL> select count(*) from dba_recyclebin;

  COUNT(*)
----------
     28522

SQL> purge recyclebin;

Recyclebin purged.

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

Once done, all issues solved and metric being collected again:

Some additional reference:

Database Tablespace Metrics: Tablespace Allocation Is Not Collected (Metric tbspAllocation) (Doc ID 404692.1)
EM 12c : emctl start agent Fails With Error ‘Starting agent … started but not ready’ (Doc ID 1591477.1)
EM12c : emctl start / status agent ‘Agent Running but Not Ready’ ‘ERROR – The agent is overloaded [current requests: 30]’ Reported in gcagent.log (Doc ID 1546529.1)

I hope it helps!

Enterprise Manager

OEM 12.5: Tablespace Allocation Metric Not Collected—Agent Is Running but Not Ready

By matheusdba 1 de December de 2021 28 de November de 2021

Hi all,

First of all, accept our apologies for the long period without posts. We are about to resolve it and restart with posts. 2021 was such a crazy year, but all is settling down.

Jumping to what you are here for: So, I ran across an interesting case within OEM 12 (Oracle Enterprise Manager) Release 5. A client reported the “tablespace allocation metric” was not being updated on OEM for a specific database. In this case, the most recent gathering was done in November 2020, as I”ll show you shortly. Then, we discovered the problem one month later. This post describes what we did to solve the issue.

As usual, the first thing I did was check on the Oracle Enterprise Manager (OEM) agent status. This is what it said:

oracle:dbserver@mydb02 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 12.1.0.5.0
OMS Version : (unknown)
Protocol Version : 12.1.0.1.0
Agent Home : /u01/app/oracle/product/agent12c/agent_inst
Agent Log Directory : /u01/app/oracle/product/agent12c/agent_inst/sysman/log
Agent Binaries : /u01/app/oracle/product/agent12c/core/12.1.0.5.0
Agent Process ID : 61641
Parent Process ID : 61394
Currently initializing component : Target Manager (2) (54 of 70)
Receivelet Interaction Manager Current Activity: Outstanding receivelet event tasks
----------------------------------
TargetID = oracle_pdb.c4test_PDB1 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29
TargetID = oracle_pdb.c3test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29
TargetID = oracle_pdb.c3test_PDB2 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30
TargetID = oracle_pdb.c4test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29
TargetID = oracle_pdb.c6test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29
TargetID = oracle_pdb.c3test_PDB3 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30
TargetID = rac_database.c1prod - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30


Target Manager Current Activity : Compute Dynamic Properties (total operations: 37, active: 7, finished: 28)


Current target operations in progress
-------------------------------------
oracle_pdb.c6test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
oracle_pdb.c4test_PDB1 - LOAD_TARGET_DYNAMIC running for 120 seconds
oracle_pdb.c3test_PDB2 - LOAD_TARGET_DYNAMIC running for 120 seconds
oracle_pdb.c3test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
oracle_pdb.c4test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
oracle_pdb.c3test_PDB3 - LOAD_TARGET_DYNAMIC running for 120 seconds
rac_database.c1test - LOAD_TARGET_DYNAMIC running for 120 seconds


Dynamic property executor tasks running
------------------------------


---------------------------------------------------------------
Agent is Running but Not Ready

“Agent not ready.” Now, that’s interesting.

Next, I tried to clear the agent state as this had solved some previous similar cases:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl clearstate agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
EMD clearstate completed successfully

Then, I ran the problematic metric manually:

oracle:dbserver02@c1test2 /u01/app/oracle: runCollection c1test_DW:oracle_pdb tbspAllocation <
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD runCollection error:The agent is running but is currently not ready to accept client requests

This shows me trying to upload the case:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl upload
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error:The agent is running but is currently not ready to accept client requests

I thought maybe something was stuck, so I decided to kill the process and start all over again:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl stop agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Stopping agent ...

stopped.

Here, I made sure I had no agent process running:

oracle:dbserver02@c1test2 /u01/app/oracle: ps -ef | grep java | agent
oracle:dbserver02@c1test2 /u01/app/oracle:

I also adjusted the threshold for metric running:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl setproperty agent -a
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
EMD setproperty succeeded
oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl setproperty agent -allow_new -name _cancelThread -value 210
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
EMD setproperty succeeded

And strating the agent

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Starting agent ........................................................................................................................... started but not ready.

This was on the agent log:

oracle:dbserver02@c1test2 /u01/app/oracle: tail /u01/app/oracle/product/agent12c/agent_inst/sysman/log/gcagent.log
oracle:dbserver02@c1test2 /u01/app/oracle:
2020-12-20 13:15:03,457 [35:686116F5] DEBUG - StatusAgentAction: satisfyRequest Begin
2020-12-20 13:15:03,457 [35:686116F5] DEBUG - Agent Overall Health: 0
2020-12-20 13:15:03,457 [35:686116F5] DEBUG - StatusAgentAction: satisfyRequest End
Response:
initializing
2020-12-20 13:15:03,457 [35:686116F5] INFO - >>> Reporting response: StatusAgentResponse (initializing) (request id 1) <<<
2020-12-20 13:15:03,457 [35:686116F5] DEBUG - closing request input stream for "StatusAgentRequest (AGENT timeout:300)"
2020-12-20 13:15:03,457 [35:686116F5] DEBUG - overriding the buffer with a thread local copy (size: 8192b)
2020-12-20 13:15:03,458 [35:686116F5] DEBUG - closing request output stream for "StatusAgentRequest (AGENT timeout:300)"
2020-12-20 13:15:03,458 [35:686116F5] DEBUG - StatusAgentAction.call() is complete.
2020-12-20 13:15:03,458 [35:B5326F3F:HTTP Listener-35 - /emd/lifecycle/main/] DEBUG - removing entry for emdctl@18081@dbserver02=>[160849530330001] completely
2020-12-20 13:15:03,458 [35:B5326F3F] DEBUG - requests executed.
2020-12-20 13:15:03,458 [35:B5326F3F] DEBUG - HTTPListener Threads deallocated resource back to LifecycleRequestHandler partition
2020-12-20 13:15:03,458 [35:3C0B0663:HTTP Listener-35] DEBUG - using connection SCEP@1197017148 [d=true,io=1,w=true,b=false|false],NOT_HANDSHAKING, in/out=0/0 Status = OK HandshakeStatus = NOT_HANDSHAKING
bytesConsumed = 5 bytesProduced = 26
2020-12-20 13:15:03,780 [35:3C0B0663] DEBUG - using connection SCEP@1197017148 [d=true,io=1,w=true,b=false|false],NOT_HANDSHAKING, in/out=0/0 Status = OK HandshakeStatus = NOT_HANDSHAKING
bytesConsumed = 26 bytesProduced = 5
2020-12-20 13:15:06,986 [31:858161EB] DEBUG - Submitting task SchedulerHeartbeat for execution
2020-12-20 13:15:06,986 [395:1AE716D8] DEBUG - Begin task SchedulerHeartbeat on Thread: GC.SysExecutor.8
2020-12-20 13:15:06,986 [395:F944F4C8:GC.SysExecutor.8 (SchedulerHeartbeat)] DEBUG - Scheduler heartbeat
2020-12-20 13:15:06,988 [395:F944F4C8] DEBUG - Scheduling next SchedulerHeartbeat after delay 29998 including periodShift of 0 milliseconds
2020-12-20 13:15:06,988 [395:1AE716D8:GC.SysExecutor.8] DEBUG - End task SchedulerHeartbeat
2020-12-20 13:15:07,016 [31:858161EB] DEBUG - Submitting task HeapMonitorTask for execution
2020-12-20 13:15:07,017 [396:1AE716D9] DEBUG - Begin task HeapMonitorTask on Thread: GC.SysExecutor.9
2020-12-20 13:15:07,017 [396:391F60D7:GC.SysExecutor.9 (HeapMonitorTask)] DEBUG - Scheduling next HeapMonitorTask after delay 5000 including periodShift of 0 milliseconds
2020-12-20 13:15:07,017 [396:1AE716D9:GC.SysExecutor.9] DEBUG - End task HeapMonitorTask
2020-12-20 13:15:12,017 [31:858161EB] DEBUG - Submitting task HeapMonitorTask for execution
2020-12-20 13:15:12,017 [37:1AE716D0] DEBUG - Begin task HeapMonitorTask on Thread: GC.SysExecutor.0
2020-12-20 13:15:12,017 [37:FE21F10E:GC.SysExecutor.0 (HeapMonitorTask)] DEBUG - Scheduling next HeapMonitorTask after delay 5000 including periodShift of 0 milliseconds
2020-12-20 13:15:12,017 [37:1AE716D0:GC.SysExecutor.0] DEBUG - End task HeapMonitorTask
2020-12-20 13:15:12,189 [33:6D553CF6] DEBUG - HTTPListener Threads deallocated resource back to LifecycleRequestHandler partition
2020-12-20 13:15:12,190 [35:3C0B0663] DEBUG - using connection SCEP@1611645943 [d=true,io=1,w=true,b=false|false],NOT_HANDSHAKING, in/out=0/0 Status = OK HandshakeStatus = NOT_HANDSHAKING
bytesConsumed = 100 bytesProduced = 121
2020-12-20 13:15:12,191 [35:7107E334:HTTP Listener-35 - /emd/persistence/main/] DEBUG - HTTPListener Threads allocated resource from LifecycleRequestHandler partition
2020-12-20 13:15:17,017 [31:858161EB] DEBUG - Submitting task HeapMonitorTask for execution
2020-12-20 13:15:17,018 [45:1AE716D1] DEBUG - Begin task HeapMonitorTask on Thread: GC.SysExecutor.1
2020-12-20 13:15:17,018 [45:CBCC52CF:GC.SysExecutor.1 (HeapMonitorTask)] DEBUG - Scheduling next HeapMonitorTask after delay 5000 including periodShift of 0 milliseconds
2020-12-20 13:15:17,018 [45:1AE716D1:GC.SysExecutor.1] DEBUG - End task HeapMonitorTask

Following MOS Enterprise Manager12c: Oracle Database Tablespace Monthly Space Usage shows no data (Doc ID 1536654.1), I made a few changes:

$/AGENT_INST/bin/emctl setproperty agent -allow_new -name MaxInComingConnections -value 150
$/AGENT_INST/bin/emctl setproperty agent -allow_new -name _cancelThread -value 210

Here’s the status before the change:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 12.1.0.5.0
OMS Version : (unknown)
Protocol Version : 12.1.0.1.0
Agent Home : /u01/app/oracle/product/agent12c/agent_inst
Agent Log Directory : /u01/app/oracle/product/agent12c/agent_inst/sysman/log
Agent Binaries : /u01/app/oracle/product/agent12c/core/12.1.0.5.0
Agent Process ID : 61641
Parent Process ID : 61394
Currently initializing component : Target Manager (2) (54 of 70)
Receivelet Interaction Manager Current Activity: Outstanding receivelet event tasks
----------------------------------
TargetID = oracle_pdb.c4test_PDB1 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29
TargetID = oracle_pdb.c3test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29
TargetID = oracle_pdb.c3test_PDB2 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30
TargetID = oracle_pdb.c4test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29
TargetID = oracle_pdb.c6test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29
TargetID = oracle_pdb.c3test_PDB3 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30
TargetID = rac_database.c1test - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30

Target Manager Current Activity : Compute Dynamic Properties (total operations: 37, active: 7, finished: 28)

Current target operations in progress
-------------------------------------
oracle_pdb.c6test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
oracle_pdb.c4test_PDB1 - LOAD_TARGET_DYNAMIC running for 120 seconds
oracle_pdb.c3test_PDB2 - LOAD_TARGET_DYNAMIC running for 120 seconds
oracle_pdb.c3test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
oracle_pdb.c4test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
oracle_pdb.c3test_PDB3 - LOAD_TARGET_DYNAMIC running for 120 seconds
rac_database.c1test - LOAD_TARGET_DYNAMIC running for 120 seconds

Dynamic property executor tasks running
------------------------------


---------------------------------------------------------------
Agent is Running but Not Ready

And this was the status after the change:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 12.1.0.5.0
OMS Version : 12.1.0.5.0
Protocol Version : 12.1.0.1.0
Agent Home : /u01/app/oracle/product/agent12c/agent_inst
Agent Log Directory : /u01/app/oracle/product/agent12c/agent_inst/sysman/log
Agent Binaries : /u01/app/oracle/product/agent12c/core/12.1.0.5.0
Agent Process ID : 56994
Parent Process ID : 56654
Agent URL : https://dbserver02:3872/emd/main/
Local Agent URL in NAT : https://dbserver02:3872/emd/main/
Repository URL : https://omsweb:4903/empbs/upload
Started at : 2020-12-20 13:08:35
Started by user : oracle
Operating System : Linux version 3.10.0-957.27.2.el7.x86_64 (amd64)
Last Reload : (none)
Last successful upload : 2020-12-20 13:40:41
Last attempted upload : 2020-12-20 13:40:41
Total Megabytes of XML files uploaded so far : 1.02
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0
Available disk space on upload filesystem : 10.85%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2020-12-20 13:40:40
Last successful heartbeat to OMS : 2020-12-20 13:40:40
Next scheduled heartbeat to OMS : 2020-12-20 13:41:40

---------------------------------------------------------------
Agent is Running and Ready

Great! Agent issue resolved.

However, the metric was not being gathered—not even after running it manually:

oracle:dbserver01@c1test1 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl control agent runCollection c1test_CDBROOT:oracle_pdb tbspAllocation
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD runCollection completed successfully

oracle:dbserver01@c1test1 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl status agent scheduler | grep tbspAllocation
2020-12-28 23:05:14.562 : rac_database:c1test:tbspAllocation_cdb
2020-12-29 03:07:21.988 : rac_database:c4prod:tbspAllocation_cdb
2020-12-29 03:08:11.888 : rac_database:c6prod:tbspAllocation_cdb
2020-12-29 03:09:39.103 : rac_database:c2prod:tbspAllocation_cdb
2020-12-29 03:09:55.372 : rac_database:c3prod:tbspAllocation_cdb

oracle:dbserver01@c1test1 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl control agent runCollection c1test_DW:oracle_pdb tbspAllocation
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD runCollection completed successfully

Meanwhile, on the OEM repository database:

SQL> select TARGET_NAME,TARGET_TYPE,TARGET_GUID,max(ROLLUP_TIMESTAMP )
from mgmt$metric_daily where TARGET_NAME like '%c1test%'
and TARGET_TYPE='oracle_pdb'
and METRIC_NAME='tbspAllocation'
group by TARGET_NAME,TARGET_TYPE,TARGET_GUID; 2 3 4 5

TARGET_NAME TARGET_TYPE TARGET_GUID MAX(ROLLUP_TIMESTAM
------------------------------ -------------------- -------------------------------- -------------------
c1test_DW oracle_pdb 7B1DF5DD4555EB978330A6D522004D44 2020-11-12 00:00:00
c1test_CDBROOT oracle_pdb 4CE72911295C0287E053837F649B7D0E 2020-11-12 00:00:00


SQL> select TARGET_NAME,TARGET_TYPE,TARGET_GUID,ROLLUP_TIMESTAMP from mgmt$metric_daily where TARGET_NAME like '%c1test%' and TARGET_TYPE='oracle_pdb' and ROLLUP_TIMESTAMP>sysdate-3 order by 4

TARGET_NAME TARGET_TYPE ROLLUP_TIMESTAMP AVERAGE
------------------------------ -------------------- ------------------- ----------
c1test_DW oracle_pdb 2020-11-06 00:00:00 1575.9375
c1test_DW oracle_pdb 2020-11-07 00:00:00 1575.9375
c1test_DW oracle_pdb 2020-11-08 00:00:00 1575.9375
c1test_DW oracle_pdb 2020-11-09 00:00:00 1575.9375
c1test_DW oracle_pdb 2020-11-10 00:00:00 1575.9375
c1test_DW oracle_pdb 2020-11-11 00:00:00 1575.9375
c1test_DW oracle_pdb 2020-11-12 00:00:00 1575.9375
c1test_CDBROOT oracle_pdb 2020-11-05 00:00:00 37581.5625


TARGET_NAME TARGET_TYPE ROLLUP_TIMESTAMP AVERAGE
------------------------------ -------------------- ------------------- ----------
c1test_CDBROOT oracle_pdb 2020-11-08 00:00:00 227138.75
c1test_CDBROOT oracle_pdb 2020-11-09 00:00:00 455087.688
c1test_CDBROOT oracle_pdb 2020-11-10 00:00:00 278230.875
c1test_CDBROOT oracle_pdb 2020-11-11 00:00:00 208727.188
c1test_CDBROOT oracle_pdb 2020-11-12 00:00:00 454964.063

In summary: After fixing all the issues on the OEM side, with everything running fine, the database metrics were still not being updated:

To make a long story short, after some investigation, I came across the following in MOS (My Oracle Support): Database Hangs With Simple Queries like on view dba_data_files & dba_free_space (Doc ID 2665935.1)

This seemed to be a match. So, I proceeded with its recommendations on OMS database:

SQL> alter session set container=DW;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 DW READ WRITE NO
SQL> select count(*) from dba_recyclebin;

COUNT(*)
----------
28522

SQL> purge recyclebin;

Recyclebin purged.

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

With that done, all the issues were solved and the metric was being collected again:

Tablespace allocation metric.

Here are some additional references:

Database Tablespace Metrics: Tablespace Allocation Is Not Collected (Metric tbspAllocation) (Doc ID 404692.1)
EM 12c : emctl start agent Fails With Error ‘Starting agent … started but not ready’ (Doc ID 1591477.1)
EM12c : emctl start / status agent ‘Agent Running but Not Ready’ ‘ERROR – The agent is overloaded [current requests: 30]’ Reported in gcagent.log (Doc ID 1546529.1)

I hope this helps!

If you have any questions or thoughts, please leave them in the comments. And don’t forget to sign up for the next post.

Enterprise Manager

OEM 12c Agent: java.lang.OutOfMemoryError: Java heap space

By matheusdba 14 de January de 2020 21 de April de 2020

Hi all,

So I got this error from OEM in a client. When connecting noticed OEM agent was down.

From logs, it failed to restart because of error “java.lang.OutOfMemoryError: Java heap space“.

I tried to run clearstate but got same error.

Searched on MOS – found MOS Duplicate 1952593.1: EM12c: emctl start agent Fails With Target Interaction Manager failed at Startup java.lang.OutOfMemoryError: Java heap space reported in gcagent_errors.log (Doc ID 1902124.1)

Solution seems to be moving /agent_inst/sysman/emd/state/ content to another location before running clearstate.

After doing so, all worked fine! So if you are facing the same, try this out!

oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/bin$ ./emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent is Not Running
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/bin$ cd ..
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst$ cd sysman
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman$ cd log/
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ ls -lrt | tail
-rw-r----- 1 oracle dba 4096 Jan 9 09:47 emagent.nohup.lr
-rw-r----- 1 oracle dba 11165 Jan 9 09:48 startup.info
-rw-r----- 1 oracle dba 153432 Jan 9 09:48 gcagent_errors.log
-rw------- 1 oracle dba 540010788 Jan 9 09:48 heapDump_7.hprof
-rw-r----- 1 oracle dba 4687452 Jan 9 09:48 gcagent.log
-rw-r----- 1 oracle dba 1746 Jan 9 09:48 agabend.log
-rw-r----- 1 oracle dba 2863 Jan 9 09:48 gcagent_pfu.log
-rw-r----- 1 oracle dba 84308 Jan 9 09:48 emagent.nohup
-rw-r----- 1 oracle dba 762142 Jan 9 10:06 emdctlj.log
-rw-r----- 1 oracle dba 121782 Jan 9 10:06 emctl.log
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ date
Monday, January 9, 2020 10:06:24 AM PST
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ tail gcagent.log
2020-01-09 09:48:50,875 [32:F9C26A76] INFO - *jetty*: Graceful shutdown SslSelectChannelConnector@0.0.0.0:3872
2020-01-09 09:48:50,877 [32:F9C26A76] INFO - *jetty*: Graceful shutdown ContextHandler@63b5a40a@63b5a40a/emd/lifecycle/main,null
2020-01-09 09:48:50,877 [32:F9C26A76] INFO - *jetty*: Graceful shutdown HTTPLifecycleHandler@7284aa02
2020-01-09 09:48:50,877 [32:F9C26A76] INFO - *jetty*: Graceful shutdown ContextHandler@5dac13d7@5dac13d7/emd/main,null
2020-01-09 09:48:50,877 [32:F9C26A76] INFO - *jetty*: Graceful shutdown HTTPRequestHandler@52a34783
2020-01-09 09:48:50,877 [32:F9C26A76] INFO - *jetty*: Graceful shutdown ServletContextHandler@201d592a@201d592a/emd/browser,null
2020-01-09 09:48:50,878 [32:F9C26A76] INFO - *jetty*: Graceful shutdown ContextHandler@19a9bea3@19a9bea3/emd/persistence/main,null
2020-01-09 09:48:50,878 [32:F9C26A76] INFO - *jetty*: Graceful shutdown HTTPAgentPersistenceHandler@3d89acb5
2020-01-09 09:48:50,878 [32:F9C26A76] INFO - *jetty*: Graceful shutdown ContextHandler@5722cc7e@5722cc7e/,null
2020-01-09 09:48:53,932 [32:F9C26A76] INFO - *jetty*: Shutdown hook complete
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ tail gcagent_errors.log
at oracle.sysman.gcagent.target.interaction.execution.Threshold.initThreshold(Threshold.java:4132)
at oracle.sysman.gcagent.target.interaction.execution.TargetInteractionMgr.init(TargetInteractionMgr.java:225)
at oracle.sysman.gcagent.target.interaction.execution.TargetInteractionMgr.tmNotifier(TargetInteractionMgr.java:171)
at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.invokeNotifier(TMComponentSvc.java:998)
at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.invokeInitializationStep(TMComponentSvc.java:1083)
at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.doInitializationStep(TMComponentSvc.java:916)
at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.notifierDriver(TMComponentSvc.java:812)
at oracle.sysman.gcagent.tmmain.TMMain.startup(TMMain.java:256)
at oracle.sysman.gcagent.tmmain.TMMain.agentMain(TMMain.java:557)
at oracle.sysman.gcagent.tmmain.TMMain.main(TMMain.java:546)
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ less gcagent_errors.log
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ cd ..
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman$ cd config/
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ ls -lrt | tail
-rw-r----- 1 oracle dba 8001 May 13 2016 b64LocalCertificate.txt
-rw-r----- 1 oracle dba 17893 May 13 2016 b64InternetCertificate.txt
-rw-r----- 1 oracle dba 7833 May 13 2016 emd.properties.2016_05_13_10_05_31
-rw------- 1 oracle dba 499 May 20 2016 s_jvm_options.opt.save
-rw-r----- 1 oracle dba 8202 Sep 26 01:20 emd.properties.bkp
-rw-r----- 1 oracle dba 8204 Jan 9 09:46 emd.properties.bak
-rw-r----- 1 oracle dba 156 Jan 9 09:46 private.properties.bak
-rw-r----- 1 oracle dba 8204 Jan 9 09:48 emd.properties
-rw-r----- 1 oracle dba 266 Jan 9 09:48 autotune.properties
-rw-r----- 1 oracle dba 156 Jan 9 09:48 private.properties
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ date
Monday, January 9, 2020 10:07:56 AM PST
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ n/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent is Not Running
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ which emctl
/db/oracle/product/agent12c/12.1.0.4/agent_inst/bin/emctl
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ e agent; emctl start agent

Oracle Enterprise Manager Cloud Control 12c Release 4 Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved. EMD clearstate failed: Offline clearstate failed : java.lang.OutOfMemoryError: Java heap space Oracle Enterprise Manager Cloud Control 12c Release 4 Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved. Starting agent ...........................................................................................................................failed. Consult emctl.log and emagent.nohup in: /db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ cd .. oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman$ cd emd oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$ mkdir state-20200109 oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$ mv state/* state\-20200109/ oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$ du -hs state* 1K state 166M state-20200109 oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$ emctl clearstate agent; emctl sta
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
EMD clearstate completed successfully
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Starting agent ......................... started.
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$ emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 12.1.0.4.0
OMS Version : 12.1.0.4.0
Protocol Version : 12.1.0.1.0
Agent Home : /db/oracle/product/agent12c/12.1.0.4/agent_inst
Agent Log Directory : /db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log
Agent Binaries : /db/oracle/product/agent12c/12.1.0.4/core/12.1.0.4.0
Agent Process ID : 27885
Parent Process ID : 27848
Agent URL : https://x0319vp114.nordstrom.net:3872/emd/main/
Local Agent URL in NAT : https://x0319vp114.nordstrom.net:3872/emd/main/
Repository URL : https://oemcloud.nordstrom.net:4900/empbs/upload
Started at : 2020-01-09 10:15:39
Started by user : oracle
Operating System : SunOS version 5.11 (sparcv9)
Last Reload : (none)
Last successful upload : 2020-01-09 10:17:35
Last attempted upload : 2020-01-09 10:17:35
Total Megabytes of XML files uploaded so far : 0.59
Number of XML files pending upload : 1
Size of XML files pending upload(MB) : 0
Available disk space on upload filesystem : 50.38%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2020-01-09 10:16:54
Last successful heartbeat to OMS : 2020-01-09 10:16:54
Next scheduled heartbeat to OMS : 2020-01-09 10:17:54

---------------------------------------------------------------
Agent is Running and Ready
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$

Database, Enterprise Manager

OEM Real-Time Data not Displayed

By matheusdba 15 de May de 2019 9 de May de 2019

Hi all,
So I was facing the follwoing situation in a client:
– SYS as SYSDBA passoword working to connect on database.
– Connecting to targets with this credentials working fine.
– However when loading real-time graphs it was simply not working.
– Tried to create manually the named credentials, still not working now with the error:

Listener refused the connection with the following error: ORA-12514, TNS:listener does not currently know of the service requested in connect descriptor.

See what I was getting from database home:

NewScreenshot 2019-05-09 às 11.34.33.png

Weird, but hey, the solution was already posted in Grepora!

Ok, I discovered anyway from MOS BUG 28513706 – 13c2EM: DB home page shows Connection Cache with this Cache Name does not exist. it miht be related to connection pools.
Even thought this was closed as not a bug, a learned a new workaround for it:

1. [OMS_HOME]/bin/emctl set property -name use_pooled_target_connections -value false 

2. Restart oms 

[OMS_HOME]/bin/emctl stop oms -all 

[OMS_HOME]/bin/emctl start oms

Hope it helps!
Cheers!

Database, Enterprise Manager

OEM Report: Last 6 month Database Space Usage and Growth [2]

By matheusdba 19 de March de 2019 3 de April de 2020

Hey,
So I see you liked the last OEM Report post. But some have asked me for a table with all database space usage something like a summary table for executive view, once the last report gives it by the database, right…

Here it goes:
– I hid the target names for safety, but check on the last column “AVG 6-m Growth”. This is the one you want.
– This obviously needs to be in another Report, once no DB filter is present. So, here is an example of the table and the code:

And let me ask you: Would you like to have this view from the ASM Perspective? Like this?

ASM-Growth

Great, right?
The codes for this:

NOTE: We got comments on this regarding the sysdate implicit conversion to timestamp. It worked fine to by, but in case you have any issue, you might want to check over this. Thank you for pointing out, Günter Nowak!

#Database Space Usage information in the last 6 months

select CURRENT_ALLOCATEDSPACE.TARGET_NAME,               
round(CURRENT_ALLOCATEDSPACE.SIZE_GB,2) as "Current allocated(GB)",
round(CURRENT_USEDSIZE.SIZE_GB,2) as "Current Used (GB)",
round(CURRENT_ALLOCATEDSPACE.SIZE_GB- CURRENT_USEDSIZE.SIZE_GB,2) as "Current Free (GB)",
round(CURRENT_USEDSIZE.SIZE_GB-USED_LAST_MONTH.SIZE_GB,2) as "1-m Growth (GB)",
round(MAX_USED.SIZE_GB,2) as "Max Used 6-m (GB)",
round((CURRENT_USEDSIZE.SIZE_GB- FIRST_USEDSPACE.SIZE_GB)/6,2) as "AVG 6-m Growth " 
from 
		(select T.TARGET_NAME,  --FIRST USED SPACE
		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP,
		sum(M.AVERAGE/1024)as SIZE_GB
		from   MGMT$METRIC_DAILY M,
		MGMT$TARGET_TYPE T,
			(select T.TARGET_NAME, MIN(ROLLUP_TIMESTAMP) MIN_TIME 
			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	  
			where (T.TARGET_TYPE='rac_database' OR 
			(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and      
			M.TARGET_GUID=T.TARGET_GUID and
			M.METRIC_GUID=T.METRIC_GUID and
			T.METRIC_NAME='tbspAllocation' and
			T.METRIC_COLUMN='spaceUsed' and
			M.METRIC_NAME=T.METRIC_NAME and
			M.METRIC_COLUMN=T.METRIC_COLUMN and
			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME, 			M.METRIC_COLUMN) MIN_DATE 		where (T.TARGET_TYPE='rac_database' OR  		(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='tbspAllocation' and 		T.METRIC_COLUMN='spaceUsed' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MIN_DATE.TARGET_NAME AND M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME 		group by t.target_name,m.rollup_timestamp) FIRST_USEDSPACE,	--FIRST USED SPACE 		(select T.TARGET_NAME,    --CURRENT ALLOCATED SPACE 		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME, max(ROLLUP_TIMESTAMP) MAX_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where (T.TARGET_TYPE='rac_database' OR  			(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and       			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='tbspAllocation' and 			T.METRIC_COLUMN='spaceAllocated' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and 			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME, 			M.METRIC_COLUMN) MAX_DATE 		where (T.TARGET_TYPE='rac_database' OR  		(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='tbspAllocation' and 		T.METRIC_COLUMN='spaceAllocated' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MAX_DATE.TARGET_NAME AND M.ROLLUP_TIMESTAMP=MAX_DATE.MAX_TIME 		group by t.target_name,m.rollup_timestamp) CURRENT_ALLOCATEDSPACE,--CURRENT ALLOCATED SPACE 		(select T.TARGET_NAME,  --USED SPACE IN LAST MONTH 		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME, MIN(ROLLUP_TIMESTAMP) MIN_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where (T.TARGET_TYPE='rac_database' OR  			(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and       			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='tbspAllocation' and 			T.METRIC_COLUMN='spaceUsed' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and 			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-30),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME, 			M.METRIC_COLUMN) MIN_DATE 		where (T.TARGET_TYPE='rac_database' OR  		(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='tbspAllocation' and 		T.METRIC_COLUMN='spaceUsed' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MIN_DATE.TARGET_NAME AND M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME 		group by t.target_name,m.rollup_timestamp) USED_LAST_MONTH, --USED SPACE IN LAST MONTH 		(select A_SIZE.TARGET_NAME, --MAX USED SPACE IN 6 MONTHS 		round(max(A_SIZE.SIZE_GB),2) as SIZE_GB 		from (select T.TARGET_NAME,   			M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 			sum(M.AVERAGE/1024) as SIZE_GB 			from MGMT$METRIC_DAILY M, 			MGMT$TARGET_TYPE T 			where (T.TARGET_TYPE='rac_database' OR  			(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and       			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='tbspAllocation' and 			T.METRIC_COLUMN='spaceUsed' and 			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME, 			M.METRIC_COLUMN, 			M.ROLLUP_TIMESTAMP) A_SIZE 		group by A_SIZE.TARGET_NAME) MAX_USED,	--MAX USED SPACE IN 6 MONTHS				   		
			(select T.TARGET_NAME,   --CURRENT USED SPACE 		
				M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME, max(ROLLUP_TIMESTAMP) MAX_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where (T.TARGET_TYPE='rac_database' OR  			(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and      	 			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='tbspAllocation' and 			T.METRIC_COLUMN='spaceUsed' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate)
			group by T.TARGET_NAME,
			M.METRIC_COLUMN) MAX_DATE
		where (T.TARGET_TYPE='rac_database' OR 
		(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and      
		M.TARGET_GUID=T.TARGET_GUID and
		M.METRIC_GUID=T.METRIC_GUID and
		T.METRIC_NAME='tbspAllocation' and
		T.METRIC_COLUMN='spaceUsed' and
		M.METRIC_NAME=T.METRIC_NAME and
		M.METRIC_COLUMN=T.METRIC_COLUMN and
		T.TARGET_NAME=MAX_DATE.TARGET_NAME AND M.ROLLUP_TIMESTAMP=MAX_DATE.MAX_TIME
		group by t.target_name,m.rollup_timestamp) CURRENT_USEDSIZE				 					     
WHERE 
CURRENT_ALLOCATEDSPACE.TARGET_NAME=CURRENT_USEDSIZE.TARGET_NAME and
CURRENT_ALLOCATEDSPACE.TARGET_NAME=USED_LAST_MONTH.TARGET_NAME and 
CURRENT_ALLOCATEDSPACE.TARGET_NAME=FIRST_USEDSPACE.TARGET_NAME and 
CURRENT_ALLOCATEDSPACE.TARGET_NAME=MAX_USED.TARGET_NAME 
order by 3 desc

# ASM Diskgroup Usage information in the last 6 months

select CURRENT_ALLOCATEDSPACE.TARGET_NAME "DB Name" , 
CURRENT_ALLOCATEDSPACE.KEY_VALUE  "Diskgroup",
round(CURRENT_ALLOCATEDSPACE.SIZE_GB,2) as "Current Allocated (GB)",
round(CURRENT_ALLOCATEDSPACE.SIZE_GB-CURRENT_FREESIZE.SIZE_GB,2) as "Current Used (GB)",
round(CURRENT_FREESIZE.SIZE_GB,2) as "Current Free (GB)",
round((CURRENT_ALLOCATEDSPACE.SIZE_GB-CURRENT_FREESIZE.SIZE_GB)-(TOTAL_SPACE_LAST_MONTH.SIZE_GB-FREE_LAST_MONTH.SIZE_GB),2) as "1-m Growth (GB)",
round(USED_SPACE_INFORMATION.USED_SPACE,2) as "Max Used 6-m (GB)",
round(((CURRENT_ALLOCATEDSPACE.SIZE_GB-CURRENT_FREESIZE.SIZE_GB)- (FIRST_TOTAL_SPACE.SIZE_GB-FIRST_FREESPACE.SIZE_GB))/6,2) as "AVG 6-m Growth" 
from 
		(select T.TARGET_NAME,  --FIRST FREE SPACE
		M.KEY_VALUE,
		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP,
		sum(M.AVERAGE/1024)as SIZE_GB
		from   MGMT$METRIC_DAILY M,
		MGMT$TARGET_TYPE T,
			(select T.TARGET_NAME, M.KEY_VALUE, MIN(ROLLUP_TIMESTAMP) MIN_TIME 
			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	  
			where T.TARGET_TYPE='osm_cluster' and      
			M.TARGET_GUID=T.TARGET_GUID and
			M.METRIC_GUID=T.METRIC_GUID and
			T.METRIC_NAME='DiskGroup_Usage' and
			T.METRIC_COLUMN='free_mb' and
			M.METRIC_NAME=T.METRIC_NAME and
			M.METRIC_COLUMN=T.METRIC_COLUMN and
			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME,M.key_value,M.METRIC_COLUMN) MIN_DATE 		where T.TARGET_TYPE='osm_cluster'  and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='DiskGroup_Usage' and 		T.METRIC_COLUMN='free_mb' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MIN_DATE.TARGET_NAME AND M.KEY_VALUE=MIN_DATE.KEY_VALUE AND M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME 		group by t.target_name,M.KEY_VALUE, m.rollup_timestamp) FIRST_FREESPACE,	--FIRST FREE SPACE 		(select T.TARGET_NAME,  --FIRST TOTAL  SPACE 		M.KEY_VALUE, 		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME, M.KEY_VALUE, MIN(ROLLUP_TIMESTAMP) MIN_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where T.TARGET_TYPE='osm_cluster' and       			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='DiskGroup_Usage' and 			T.METRIC_COLUMN='total_mb' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and 			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME,M.key_value,M.METRIC_COLUMN) MIN_DATE 		where T.TARGET_TYPE='osm_cluster'  and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='DiskGroup_Usage' and 		T.METRIC_COLUMN='total_mb' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MIN_DATE.TARGET_NAME AND M.KEY_VALUE=MIN_DATE.KEY_VALUE AND M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME 		group by t.target_name,M.KEY_VALUE, m.rollup_timestamp) FIRST_TOTAL_SPACE,	--FIRST TOTAL  SPACE		 		(select T.TARGET_NAME,    --CURRENT TOTAL SPACE 		M.KEY_VALUE, 		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME, M.KEY_VALUE, max(ROLLUP_TIMESTAMP) MAX_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where T.TARGET_TYPE='osm_cluster'  and       			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='DiskGroup_Usage' and 			T.METRIC_COLUMN='total_mb' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and 			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME, m.key_value, 			M.METRIC_COLUMN) MAX_DATE 		where T.TARGET_TYPE='osm_cluster' and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='DiskGroup_Usage' and 		T.METRIC_COLUMN='total_mb' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MAX_DATE.TARGET_NAME AND M.KEY_VALUE=MAX_DATE.KEY_VALUE AND M.ROLLUP_TIMESTAMP=MAX_DATE.MAX_TIME 		group by t.target_name,m.key_value,m.rollup_timestamp order by 1,3) CURRENT_ALLOCATEDSPACE,--CURRENT TOTAL SPACE 		(select T.TARGET_NAME,  --FREE SPACE IN LAST MONTH 		M.KEY_VALUE, 		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME,M.KEY_VALUE, MIN(ROLLUP_TIMESTAMP) MIN_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where T.TARGET_TYPE='osm_cluster' and       			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='DiskGroup_Usage' and 			T.METRIC_COLUMN='free_mb' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and 			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-30),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME,M.KEY_VALUE) MIN_DATE 		where T.TARGET_TYPE='osm_cluster' and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='DiskGroup_Usage' and 		T.METRIC_COLUMN='free_mb' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MIN_DATE.TARGET_NAME AND  M.KEY_VALUE=MIN_DATE.KEY_VALUE and  M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME 		group by t.target_name,m.key_value, m.rollup_timestamp) FREE_LAST_MONTH, --FREE SPACE IN LAST MONTH 		(select T.TARGET_NAME,  --TOTAL SPACE IN LAST MONTH		 		M.KEY_VALUE, 		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME,M.KEY_VALUE, MIN(ROLLUP_TIMESTAMP) MIN_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where T.TARGET_TYPE='osm_cluster' and       			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='DiskGroup_Usage' and 			T.METRIC_COLUMN='total_mb' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and 			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-30),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME,M.KEY_VALUE) MIN_DATE 		where T.TARGET_TYPE='osm_cluster' and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='DiskGroup_Usage' and 		T.METRIC_COLUMN='total_mb' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MIN_DATE.TARGET_NAME AND  M.KEY_VALUE=MIN_DATE.KEY_VALUE and  M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME 		group by t.target_name,m.key_value, m.rollup_timestamp) TOTAL_SPACE_LAST_MONTH, --TOTAL SPACE IN LAST MONTH	 		(SELECT SPACE_ALLOCATED.TARGET_NAME, -- USED_SPACE_INFORMATION 		SPACE_ALLOCATED.KEY_VALUE,  		max(SPACE_ALLOCATED.SIZE_GB- FREE_SPACE.SIZE_GB) USED_SPACE 		FROM( 				select T.TARGET_NAME,  				M.KEY_VALUE,  				M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 				sum(M.AVERAGE/1024) as SIZE_GB 				from MGMT$METRIC_DAILY M, 				MGMT$TARGET_TYPE T 				where T.TARGET_TYPE='osm_cluster' and       				M.TARGET_GUID=T.TARGET_GUID and 				M.METRIC_GUID=T.METRIC_GUID and 				T.METRIC_NAME='DiskGroup_Usage' and 				T.METRIC_COLUMN='total_mb' and 				M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
				M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 				group by T.TARGET_NAME,	M.KEY_VALUE, 				M.ROLLUP_TIMESTAMP) SPACE_ALLOCATED, 				(select T.TARGET_NAME,  				M.KEY_VALUE,  				M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 				sum(M.AVERAGE/1024) as SIZE_GB 				from MGMT$METRIC_DAILY M, 				MGMT$TARGET_TYPE T 				where T.TARGET_TYPE='osm_cluster' and       				M.TARGET_GUID=T.TARGET_GUID and 				M.METRIC_GUID=T.METRIC_GUID and 				T.METRIC_NAME='DiskGroup_Usage' and 				T.METRIC_COLUMN='free_mb' and 				M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
				M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 				group by T.TARGET_NAME,	M.KEY_VALUE, 				M.ROLLUP_TIMESTAMP) FREE_SPACE 		WHERE SPACE_ALLOCATED.TARGET_NAME=FREE_SPACE.TARGET_NAME AND 		SPACE_ALLOCATED.KEY_VALUE=FREE_SPACE.KEY_VALUE AND 		SPACE_ALLOCATED.MONTH_TIMESTAMP=FREE_SPACE.MONTH_TIMESTAMP  		group by  SPACE_ALLOCATED.TARGET_NAME, SPACE_ALLOCATED.KEY_VALUE) USED_SPACE_INFORMATION, -- USED_SPACE_INFORMATION				 		(select T.TARGET_NAME,   --CURRENT FREE SPACE 		M.KEY_VALUE, 		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME, M.KEY_VALUE, max(ROLLUP_TIMESTAMP) MAX_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where T.TARGET_TYPE='osm_cluster' and      	 			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='DiskGroup_Usage' and 			T.METRIC_COLUMN='free_mb' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and  			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate)
			group by T.TARGET_NAME,M.KEY_VALUE ,M.METRIC_COLUMN) MAX_DATE
		where T.TARGET_TYPE='osm_cluster' and      
		M.TARGET_GUID=T.TARGET_GUID and
		M.METRIC_GUID=T.METRIC_GUID and
		T.METRIC_NAME='DiskGroup_Usage' and
		T.METRIC_COLUMN='free_mb' and
		M.METRIC_NAME=T.METRIC_NAME and
		M.METRIC_COLUMN=T.METRIC_COLUMN and
		T.TARGET_NAME=MAX_DATE.TARGET_NAME AND M.KEY_VALUE=MAX_DATE.KEY_VALUE AND M.ROLLUP_TIMESTAMP=MAX_DATE.MAX_TIME
		group by t.target_name,m.key_value,m.rollup_timestamp) CURRENT_FREESIZE	  --CURRENT FREE SPACE			
WHERE 
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=CURRENT_FREESIZE.TARGET_NAME and CURRENT_ALLOCATEDSPACE.KEY_VALUE=CURRENT_FREESIZE.KEY_VALUE) and
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=FREE_LAST_MONTH.TARGET_NAME and CURRENT_ALLOCATEDSPACE.KEY_VALUE=FREE_LAST_MONTH.KEY_VALUE) and 
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=FIRST_FREESPACE.TARGET_NAME and CURRENT_ALLOCATEDSPACE.KEY_VALUE=FIRST_FREESPACE.KEY_VALUE) and 
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=TOTAL_SPACE_LAST_MONTH.TARGET_NAME and CURRENT_ALLOCATEDSPACE.KEY_VALUE=TOTAL_SPACE_LAST_MONTH.KEY_VALUE) and
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=FIRST_TOTAL_SPACE.TARGET_NAME and CURRENT_ALLOCATEDSPACE.KEY_VALUE=FIRST_TOTAL_SPACE.KEY_VALUE) and
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=USED_SPACE_INFORMATION.TARGET_NAME AND CURRENT_ALLOCATEDSPACE.KEY_VALUE=USED_SPACE_INFORMATION.KEY_VALUE )
order by 4 desc

Database, Enterprise Manager, SQL | PLSQL

OEM Report: Last 6 month Database Space Usage and Growth

By matheusdba 5 de March de 2019 21 de April de 2020

Hello All!
So I had worked in some very useful reports to have in OEM. In next weeks I’ll share some code you may like… 🙂

To create it? Enterprise -> Reports -> Information Reports. There are several nice default reports there you me like.

Now, let’s go to the first Report, as per title:

1. First you Select the Database:

2. Then you see the report:

Report3

That’s nice, right?
Here is a report for another database with actual 640GB average growth per month (its expected, once DBSize is over 60TB):

Growth

So, Mat, can you share the queries? Of course:

More“OEM Report: Last 6 month Database Space Usage and Growth”

Enterprise Manager

Check Ports in use by OEM

By matheusdba 26 de February de 2019 11 de December de 2018

So, how to quickly check which ports are indeed in use by OEM?

Easy:

[oracle@greporasrv ~]$ cat $OMS_HOME/install/portlist.ini
Enterprise Manager Upload Http Port=4889
Enterprise Manager Upload Http SSL Port=4903
OHS Http Port=9788
BI Publisher Http Port=9701
Enterprise Manager Central Console Http SSL Port=7802
Node Manager Http SSL Port=7403
BI Publisher Http SSL Port=9803
OHS Http SSL Port=9851
Managed Server Http Port=7202
Oracle Management Agent Port=3872
Enterprise Manager Central Console Http Port=7788
Admin Server Http SSL Port=7102
Managed Server Http SSL Port=7301

Hope it helps!

Enterprise Manager

OEM13c: Regions that display real-time data will not be displayed. Exception while loading RAC Database Home Page: null

By matheusdba 12 de February de 2019 24 de October de 2019

Hi all,
So I was having this issue from a Database Home page on OEM 13c:

Actually, OMS log was presenting several null pointer exceptions… So, whats is the deal?

Everything seemed to match to MOS Bug 22957131 – OEM13C: Exception while loading RAC Database Home Page: null.

The solution?
– Patch 25197714 for the EM 13.2 OMS
– Patch 25155095 for the EM 13.1 OMS

Also, those fixes are included on following Boudle Patches:
– 13.2.1.0.161231
– 13.1.1.0.161220

Applied the patch and solved my case. Hope it helps you!

More Reference:
– EM 13C: Target Database Home Page Displays Message in Enterprise Manager 13c Cloud Control: Regions that display real-time data will not be displayed. Exception while loading RAC Database Home Page: null (Doc ID 2210123.1)
– Note 2219797.1 Enterprise Manager 13.2 Master Bundle Patch List
– Note 2124038.1 Enterprise Manager 13.1 Master Bundle Patch List for the Management Agent and Plug-ins

Enterprise Manager, Linux

OEM Metric “Memory Utilization” Different on 12c and 13c

By matheusdba 29 de January de 2019 21 de April de 2020

So, as rollout strategy we created a new OEM13c to decommission a 12c. However during the testes, noticed Memory Utilization metric was a lot different between 12c and 13c. Why?

Happens that the Memory Utilization is calculated differently between 12c and 13c, but also seems 13c is more accurate, as per MOS The Host Memory Utilization Percentage Calculation in Enterprise Manager Cloud Control (Doc ID 1908853.1)

Well, those who are familiar with memory use computations in the operating system might become confused when examining the memory use metric data from Enterprise Manager 12c and 13c Cloud Control. Metrics such as Memory Utilization (%) do not have an equivalent in the OS, but OS data will be used in its derivation.

This is the formula used by Enterprise Manager 12.1.0.3 for Linux Memory Utilization (%), for example:

Memory Utilization (%) = (100.0 * (activeMem) / realMem)
 = 100 * 25046000/99060536
 = 25.28
EM Shows : 25.5

* On this, activeMem is Active Memory (Active), and realMem is Total Memory (MemTotal).

Comparing this with MemFree, which is not valid, might provide an impression that utilization is not being accurately represented.

Also, the “OEM13c value” was already collected in OEM12c, but under metric name “Used Logical Memory”. And basically “Memory Utilization” in 12c uses “activeMem” instead of “realMem-(freeMem+Buffers+Cached)”. As per image below.

OEM12_grep_mem

The formula in place on 13c is exactly the same as used to fix MOS EM 13c: Incorrect Memory Utilization Reported for Linux Hosts in Enterprise Manager 13.1.0.0.0 Cloud Control (Doc ID 2144976.1)

Example:

[root@greporasrv ~]# free
             total       used       free     shared    buffers     cached
Mem:     264087460  257669460    6418000    7657500     461088   11008128
-/+ buffers/cache:  246200244   17887216
Swap:     25165820    3365104   21800716

(100.0 * (realMem-(freeMem+Buffers+Cached)) / realMem)
100*(264087460-(6418000+461088+11008128))/264087460) = 93,22678328

As per OEM13c:

Also, by checking on server using SAR, seems value in OEM 13c is more accurate, indeed:

[root@greporasrv ~]# sar -r
Linux 2.6.39-400.294.4.el6uek.x86_64 (greporasrv) 	08/29/2017 	_x86_64_	(44 CPU)

12:00:01 AM kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit
12:10:01 AM   5377540 258709920     97.96    719080  10775828  83876744     29.00
12:20:01 AM   6131220 257956240     97.68    719504  10721084  82467712     28.51
12:30:01 AM   5623060 258464400     97.87    719700  10720972  83456216     28.85
12:40:01 AM   5606572 258480888     97.88    719836  10779108  83228440     28.77
12:50:01 AM   5783256 258304204     97.81    719860  10848644  82925908     28.67
01:00:01 AM   4151148 259936312     98.43    719888  11589048  84400040     29.18
01:10:01 AM   3717000 260370460     98.59    719904  11534336  84838784     29.33
01:20:01 AM   4282412 259805048     98.38    720164  11480792  84047568     29.06
01:30:01 AM   4473128 259614332     98.31    720184  11483604  83857348     28.99
01:40:01 AM   5113136 258974324     98.06    720256  11528492  83036284     28.71
01:50:01 AM   4971036 259116424     98.12    720284  11587956  82955128     28.68
02:00:01 AM   4026540 260060920     98.48    720344  11663184  86489692     29.90
02:10:01 AM   4312916 259774544     98.37    720380  11678316  83834592     28.98
02:20:01 AM   5058980 259028480     98.08    720408  11624028  82876972     28.65
02:30:01 AM   4609908 259477552     98.25    720556  11541392  83871244     29.00
02:40:01 AM   5020668 259066792     98.10    720592  11574912  82887808     28.66
02:50:01 AM   5175916 258911544     98.04    720748  11619572  82725252     28.60
03:00:01 AM   4701236 259386224     98.22    720780  11687100  83421624     28.84
03:10:01 AM   4757976 259329484     98.20    721204  11648864  83298716     28.80
03:20:01 AM   4485280 259602180     98.30    721248  11719272  83299472     28.80
03:30:01 AM   4267068 259820392     98.38    721264  11794688  83683344     28.93
03:40:01 AM   4080264 260007196     98.45    721404  11856796  83863540     28.99
03:50:01 AM   4864276 259223184     98.16    721676  11975372  82735744     28.60
04:00:01 AM   4427284 259660176     98.32    721696  12056676  83450524     28.85
04:10:01 AM   4868184 259219276     98.16    721736  11863420  82860464     28.65
04:20:01 AM   4711608 259375852     98.22    721760  11877192  83205684     28.77
04:30:01 AM   4452764 259634696     98.31    721928  11945108  83515596     28.87
04:40:01 AM   4800700 259286760     98.18    722072  12015444  82681320     28.58
04:50:01 AM   4796588 259290872     98.18    722212  12075496  82703948     28.59
05:00:01 AM   4320164 259767296     98.36    722372  12164956  83390596     28.83
05:10:01 AM   3350940 260736520     98.73    722488  12120116  84525028     29.22
05:20:01 AM   4200236 259887224     98.41    722628  11965996  83510580     28.87
05:30:01 AM   4028020 260059440     98.47    722640  12019516  83720748     28.94
05:40:01 AM   3929740 260157720     98.51    722720  12069520  83632964     28.91
05:50:01 AM   2719452 261368008     98.97    723460  14408924  83745112     28.95
06:00:01 AM   1530448 262557012     99.42    723644  14943264  84618304     29.25
06:10:01 AM   2925268 261162192     98.89    605748  13363596  84792452     29.31
06:20:02 AM   3235532 260851928     98.77    605916  13811664  83516740     28.87
06:30:01 AM   3265640 260821820     98.76    606072  13848028  83385196     28.83
06:40:01 AM   2102756 261984704     99.20    606232  14745508  83638764     28.92
06:50:01 AM   2386376 261701084     99.10    606644  14821232  83118484     28.74
07:00:01 AM   5343496 258743964     97.98    186908  12019804  84375032     29.17
07:10:01 AM   5073472 259013988     98.08    219044  12597104  83579876     28.90
07:20:01 AM   5380380 258707080     97.96    241300  12600412  83107160     28.73
07:30:01 AM   5063504 259023956     98.08    253984  12653840  83373804     28.82
07:40:01 AM   8241032 255846428     96.88    269960   9772232  83072188     28.72
07:50:01 AM   8549616 255537844     96.76    278472   9853288  82646916     28.57
08:00:01 AM   8185864 255901596     96.90    287296   9938816  83179808     28.76
08:10:01 AM   7797504 256289956     97.05    295856  10029904  83464160     28.86
08:20:01 AM   8813696 255273764     96.66    302620   9930672  82081220     28.38
08:30:01 AM   8574984 255512476     96.75    309156   9880124  82557600     28.54
08:40:01 AM   8010072 256077388     96.97    314804   9912220  83241764     28.78
08:50:01 AM   8791112 255296348     96.67    319568   9980532  81787424     28.28

Database, Enterprise Manager

OMSPatcher finds that previous patching session is not yet completed – What to do?

By matheusdba 15 de January de 2019 4 de December de 2018

Hey all,
As usual, a client reached out with this issue:

OMSPatcher finds that previous patching session is not yet completed.
Please refer log file "/u01/app/oracle/middleware/cfgtoollogs/omspatcher/28018178/omspatcher_2018-07-09_23-44-58PM_deploy.log" 
for the previous session and execute the script "/u01/app/oracle/middleware/.omspatcher_storage/oms_session/scripts_2018-07-09_23-44-39PM/run_script_singleoms_resume.sh"  to complete the previous session. OMSPatcher can proceed to execute new operations only if previous session is completed successfully.

Interesting, right?
This means a patch execution in July failed and it wasn’t noticed.

What to do? Point is, the error itself already say what needs to be done.
You just may want to make it properly. How? Here is a quick Action Plan:

ZER0) Check the Deploy log to understand the root cause for the failure on previous patch and fix it.

In my case?
Not all required components were down.
A simple “stop oms” stops only the OMS managed server, JVMD engine, and HTTP server but leaves Node Manager and Administration Server running.
However, a “stop oms -all” stops all Enterprise Manager processes including Administration Server, OMS, HTTP Server, Node Manager, Management Server, JVMD engine, and Oracle BI Publisher (if it is configured on the host). This was the fixing.

Step-by-Step:

1. Blackout targets to avoid unwanted pages.
– On OEM: Enterprise–>Monitoring–>Blackouts

2. Shutdown OMS and AGENT

cd $AGENT_HOME/bin
./emctl stop agent
cd $OMS_HOME/bin
./emctl stop oms -all

3. Resume Patching with issue (with provided command)
(in my case):

/u01/app/oracle/middleware/.omspatcher_storage/oms_session/scripts_218-07-09_23-44-39PM/run_script_singleoms_resume.sh

4. Verify patches got installed

$OMS_HOME/OPatch/opatch lsinventory
$OMS_HOME/OMSPatcher/omspatcher lspatches

5. Start the OMS and agent

cd $AGENT_HOME/bin
./emctl start agent
cd $OMS_HOME/bin
./emctl start oms
./emctl status oms -details

6. Sync EMCLI with server changes:

$OMS_HOME/bin/emcli login -username=sysman
Enter password : <-- sysman password
$OMS_HOME/bin/emcli sync

More“OMSPatcher finds that previous patching session is not yet completed – What to do?”