OEM 13c

Reporting Database Sizes

by

I frequently need to pull size information about a large number of databases. OEM has an interesting capacity planning feature, but some times I just want the raw data. This is the base query I start with and add on additional information or slice it in different ways to fit what I need. I run this in the OEM repository.

col target_name for 130
SELECT target_name, SUM (maximum)
    FROM sysman.mgmt$metric_daily t
   WHERE     target_type IN ('oracle_database', 'rac_database')
         AND metric_column IN ('spaceAllocated')
         AND TRUNC (rollup_timestamp) = TRUNC (SYSDATE) - 1
         AND UPPER (target_name) LIKE '%&1%'
GROUP BY target_name
ORDER BY target_name

This gives you the size, by database as of the previous day.

13C OMS and TLSv1.2

by

A while ago I deployed OEM 13c to manage and monitor our databases. We have a lot of different systems on different architectures and operating systems. For the most part the move from OEM12c to OEM 13c was pretty smooth. As part of this process we were instructed to lock everything down to TLSv1.2, which is a huge pain inside of OEM. All the internal connections from the different components and nothing was listed in the documentation together. It took a lot of trial and error, but once it was setup, the deployment of the agents went just fine.

At least until I got to our AIX hosts. This is one of the longest open tickets I have ever had with oracle. Bug:23708579. After 9 months Oracle finally got me a patch that resolved the problem this week.

 

$ ./emctl start agent 
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved. 
Starting agent ................ failed. 
SSL Configuration failed at Startup 
Consult emctl.log and emagent.nohup in: /u01/app/oracle/product/agent13c/agent_inst/sysman/log

From the log files

27656382 :: 2017-04-11 11:49:08,943::AgentLifeCycle.pm: Processing setproperty agent 
27656382 :: 2017-04-11 11:49:08,943::AgentStatus.pm:Processing setproperty agent 
27656382 :: 2017-04-11 11:49:12,411::AgentStatus.pm:/u01/app/oracle/product/agent13c/agent_13.2.0.0.0/bin/emdctl setproperty agent -name SSLCipherSuites -value TLS_RSA_WITH_AES_128_CBC_SHA returned 0 
27656382 :: 2017-04-11 11:49:12,412::Cleaning up agent command lock 
27656382 :: 2017-04-11 11:49:12,412::AgentCommandLock:closed file handle of emctl lockfile 
25624672 :: 2017-04-11 11:49:23,995::Initializing the agent command locking system 
25624672 :: 2017-04-11 11:49:24,039::AgentLifeCycle.pm: Processing stop agent 
25624672 :: 2017-04-11 11:49:24,039::AgentLifeCycle.pm: ParentProcess id=9175258 
25624672 :: 2017-04-11 11:49:26,815::AgentStatus.pm:emdctl status agent returned 1 
25624672 :: 2017-04-11 11:49:26,815::Status Output:Status agent Failure:Unable to connect to the agent at https://myoemserver:3872/emd/lifecycle/main/ [Connection refused]

There was a lot of confusion over this issue and originally they told me it was a bug in AIX that we would need to get IBM to fix. It turns out the patch that was needed was an agent patch

After applying patch: 25237184 the agent can now be locked to TLSv1.2 by adding the following to emd.properties

_frameworkTlsProtocols=TLSv1.2 
_frameworkSSLContextProtocol=TLSv1.2

and then by re-securing the agent with the “-protocol tlsv1.2” flag

./emctl secure agent "myPassword" -protocol tlsv1.2

After this the agent was able to start up and start communicating with the OMS.

 

Targets.xml was rejected: loaded with a wrong agent token

by

I noticed an agent on one of my database servers was down this morning. Agents crash from time to time, no big deal. I will just restart it

$ ./emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Starting agent ............ failed.
Target Manager failed at Startup: targets.xml was rejected: loaded with a wrong agent token
Consult emctl.log and emagent.nohup in: /u01/app/oracle/product/12.1.0.4/agent12c/agent_inst/sysman/log

Well that sucks. Something is out of sync. Usually this will happen when your file system is filled up and a change cannot be written to the targets.xml. I wasn’t out of space though. I’m not quite sure what caused this, but I know how to fix it.

make a backup of the targets.xml file, blank out the file, and add the opening and closign targets tag

cd /u01/app/oracle/product/12.1.0.4/agent12c/agent_inst/sysman/emd/
mv targets.xml targets.xml.20170220
echo "<Targets></Targets>" > targets.xml

Then re-synchronize the agent from OMS, this will re-populate the targets.xml

emcli login -username=SYSMAN
emcli sync
emcli resyncAgent -agent="dbsrv01:3872"

it takes about another 10 minutes to resynchronize but while it is running the agent will look like this

$ ./emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 12.1.0.2.0
OMS Version       : (unknown)
Protocol Version  : 12.1.0.1.0
Agent Home        : /u01/app/oracle/product/12.1.0.4/agent12c/agent_inst
Agent Binaries    : /u01/app/oracle/product/12.1.0.4/agent12c/core/12.1.0.2.0
Agent Process ID  : 794210
Parent Process ID : 793987
Agent URL         : https://dbsrv01:3872/emd/main/
Repository URL    : https://oem1:4900/empbs/upload
Started at        : 2017-02-20 11:56:00
Started by user   : oracle
Last Reload       : (none)
Last successful upload                       : (none)
Last attempted upload                        : (none)
Total Megabytes of XML files uploaded so far : 0
Number of XML files pending upload           : 95
Size of XML files pending upload(MB)         : 0.9
Available disk space on upload filesystem    : 73.85%
Collection Status                            : [RESYNC]
Heartbeat Status                             : Agent is blocked
Last attempted heartbeat to OMS              : 2017-02-20 11:59:03
Last successful heartbeat to OMS             : (none)
Next scheduled heartbeat to OMS              : 2017-02-20 12:02:03

After a little waiting around the agent is back to normal

$ ./emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 12.1.0.2.0
OMS Version       : 12.1.0.4.0
Protocol Version  : 12.1.0.1.0
Agent Home        : /u01/app/oracle/product/12.1.0.4/agent12c/agent_inst
Agent Binaries    : /u01/app/oracle/product/12.1.0.4/agent12c/core/12.1.0.2.0
Agent Process ID  : 794210
Parent Process ID : 793987
Agent URL         : https://dbsrv01:3872/emd/main/
Repository URL    : https://oem1:4900/empbs/upload
Started at        : 2017-02-20 11:56:00
Started by user   : oracle
Last Reload       : (none)
Last successful upload                       : 2017-02-20 12:19:53
Last attempted upload                        : 2017-02-20 12:19:53
Total Megabytes of XML files uploaded so far : 2.15
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 73.62%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2017-02-20 12:19:04
Last successful heartbeat to OMS             : 2017-02-20 12:19:04
Next scheduled heartbeat to OMS              : 2017-02-20 12:20:04

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