Securing Oracle Enterprise Manager 13cR2

Oracle released Oracle Enterprise Manager 13cR2 at the beginning of October 2016. I have upgraded my production system to this new version, and here I provide a 13cR2-compatible version of my EM13c security checkup script. In addition to updating the script for EM13cR2, I have also updated it to take account of Oracle’s recommendation that single-instance non-RAC databases such as OEM repositories should now apply the DBBP Bundle Patch (previously known as the engineered systems bundle patch).

Latest Updates

Initial release on October 28, 2016. Other than repository database patches, only the WebLogic component in EM13cR2 has patches so far. This release includes the WLS PSU 12.1.3.0.161019.

Download

I have created a repository on github for my EM13c scripts. You can access it at https://github.com/brianpardy/em13c. To directly access the EM13cR2 security checkup script, use https://raw.githubusercontent.com/brianpardy/em13c/master/checksec13R2.sh.

Example Output


Performing EM13c R2 security checkup version 1.0 on omshost.domain.com at Fri Oct 28 12:11:25 EDT 2016.

Using port definitions from configuration files
/etc/oragchomelist
/oracle/oem/gc_inst1/em/EMGC_OMS1/emgc.properties
/oracle/oem/gc_inst1/em/EMGC_OMS1/embip.properties

Agent port found at omshost.domain.com:3872
BIPublisher port found at omshost.domain.com:9803
BIPublisherOHS port found at omshost.domain.com:9852
NodeManager port found at omshost.domain.com:7403
OMSconsole port found at omshost.domain.com:7802
OMSproxy port found at omshost.domain.com:7301
OMSupload port found at omshost.domain.com:4903
WLSadmin found at omshost.domain.com:7102

Repository DB version=12.1.0.2.0 SID=oemdb host=omshost.domain.com

Using OPENSSL=/usr/bin/openssl1 (has TLS1_2=2)
Repository DB on OMS server, will check patches/parameters in /oracle/oem/product/12.1.0/db

(1) Checking SSL/TLS configuration (see notes 1602983.1, 1477287.1, 1905314.1)

(1a) Forbid SSLv2 connections
Confirming ssl2 disabled for Agent at omshost.domain.com:3872... OK
Confirming ssl2 disabled for BIPublisher at omshost.domain.com:9803... OK
Confirming ssl2 disabled for NodeManager at omshost.domain.com:7403... OK
Confirming ssl2 disabled for BIPublisherOHS at omshost.domain.com:9852... OK
Confirming ssl2 disabled for OMSconsole at omshost.domain.com:7802... OK
Confirming ssl2 disabled for OMSproxy at omshost.domain.com:7301... OK
Confirming ssl2 disabled for OMSupload at omshost.domain.com:4903... OK
Confirming ssl2 disabled for WLSadmin at omshost.domain.com:7102... OK

(1b) Forbid SSLv3 connections
Confirming ssl3 disabled for Agent at omshost.domain.com:3872... OK
Confirming ssl3 disabled for BIPublisher at omshost.domain.com:9803... OK
Confirming ssl3 disabled for NodeManager at omshost.domain.com:7403... OK
Confirming ssl3 disabled for BIPublisherOHS at omshost.domain.com:9852... OK
Confirming ssl3 disabled for OMSconsole at omshost.domain.com:7802... OK
Confirming ssl3 disabled for OMSproxy at omshost.domain.com:7301... OK
Confirming ssl3 disabled for OMSupload at omshost.domain.com:4903... OK
Confirming ssl3 disabled for WLSadmin at omshost.domain.com:7102... OK

(1c) Forbid TLSv1 connections
Confirming tls1 disabled for Agent at omshost.domain.com:3872... OK
Confirming tls1 disabled for BIPublisher at omshost.domain.com:9803... OK
Confirming tls1 disabled for NodeManager at omshost.domain.com:7403... OK
Confirming tls1 disabled for BIPublisherOHS at omshost.domain.com:9852... OK
Confirming tls1 disabled for OMSconsole at omshost.domain.com:7802... OK
Confirming tls1 disabled for OMSproxy at omshost.domain.com:7301... OK
Confirming tls1 disabled for OMSupload at omshost.domain.com:4903... OK
Confirming tls1 disabled for WLSadmin at omshost.domain.com:7102... OK

(1c) Forbid TLSv1.1 connections
Confirming tls1_1 disabled for Agent at omshost.domain.com:3872... OK
Confirming tls1_1 disabled for BIPublisher at omshost.domain.com:9803... OK
Confirming tls1_1 disabled for NodeManager at omshost.domain.com:7403... OK
Confirming tls1_1 disabled for BIPublisherOHS at omshost.domain.com:9852... OK
Confirming tls1_1 disabled for OMSconsole at omshost.domain.com:7802... OK
Confirming tls1_1 disabled for OMSproxy at omshost.domain.com:7301... OK
Confirming tls1_1 disabled for OMSupload at omshost.domain.com:4903... OK
Confirming tls1_1 disabled for WLSadmin at omshost.domain.com:7102... OK

(1c) Permit TLSv1.2 connections
Confirming tls1_2 available for Agent at omshost.domain.com:3872... OK
Confirming tls1_2 available for BIPublisher at omshost.domain.com:9803... OK
Confirming tls1_2 available for NodeManager at omshost.domain.com:7403... OK
Confirming tls1_2 available for BIPublisherOHS at omshost.domain.com:9852... OK
Confirming tls1_2 available for OMSconsole at omshost.domain.com:7802... OK
Confirming tls1_2 available for OMSproxy at omshost.domain.com:7301... OK
Confirming tls1_2 available for OMSupload at omshost.domain.com:4903... OK
Confirming tls1_2 available for WLSadmin at omshost.domain.com:7102... OK

(2) Checking supported ciphers at SSL/TLS endpoints (see notes 2138391.1, 1067411.1)
Checking LOW strength ciphers on Agent (omshost.domain.com:3872, protocol tls1_2)... OK
Checking MEDIUM strength ciphers on Agent (omshost.domain.com:3872)... OK
Checking HIGH strength ciphers on Agent (omshost.domain.com:3872)... OK

Checking LOW strength ciphers on BIPublisher (omshost.domain.com:9803, protocol tls1_2)... OK
Checking MEDIUM strength ciphers on BIPublisher (omshost.domain.com:9803)... OK
Checking HIGH strength ciphers on BIPublisher (omshost.domain.com:9803)... OK

Checking LOW strength ciphers on NodeManager (omshost.domain.com:7403, protocol tls1_2)... OK
Checking MEDIUM strength ciphers on NodeManager (omshost.domain.com:7403)... OK
Checking HIGH strength ciphers on NodeManager (omshost.domain.com:7403)... OK

Checking LOW strength ciphers on BIPublisherOHS (omshost.domain.com:9852, protocol tls1_2)... OK
Checking MEDIUM strength ciphers on BIPublisherOHS (omshost.domain.com:9852)... OK
Checking HIGH strength ciphers on BIPublisherOHS (omshost.domain.com:9852)... OK

Checking LOW strength ciphers on OMSconsole (omshost.domain.com:7802, protocol tls1_2)... OK
Checking MEDIUM strength ciphers on OMSconsole (omshost.domain.com:7802)... OK
Checking HIGH strength ciphers on OMSconsole (omshost.domain.com:7802)... OK

Checking LOW strength ciphers on OMSproxy (omshost.domain.com:7301, protocol tls1_2)... OK
Checking MEDIUM strength ciphers on OMSproxy (omshost.domain.com:7301)... OK
Checking HIGH strength ciphers on OMSproxy (omshost.domain.com:7301)... OK

Checking LOW strength ciphers on OMSupload (omshost.domain.com:4903, protocol tls1_2)... OK
Checking MEDIUM strength ciphers on OMSupload (omshost.domain.com:4903)... OK
Checking HIGH strength ciphers on OMSupload (omshost.domain.com:4903)... OK

Checking LOW strength ciphers on WLSadmin (omshost.domain.com:7102, protocol tls1_2)... OK
Checking MEDIUM strength ciphers on WLSadmin (omshost.domain.com:7102)... OK
Checking HIGH strength ciphers on WLSadmin (omshost.domain.com:7102)... OK

(3) Checking self-signed and demonstration certificates at SSL/TLS endpoints (see notes 1367988.1, 1399293.1, 1593183.1, 1527874.1, 123033.1, 1937457.1)
Checking certificate at Agent (omshost.domain.com:3872, protocol tls1_2)... OK
Checking demo certificate at Agent (omshost.domain.com:3872, protocol tls1_2)... OK
Checking certificate at BIPublisher (omshost.domain.com:9803, protocol tls1_2)... OK
Checking demo certificate at BIPublisher (omshost.domain.com:9803, protocol tls1_2)... OK
Checking certificate at NodeManager (omshost.domain.com:7403, protocol tls1_2)... OK
Checking demo certificate at NodeManager (omshost.domain.com:7403, protocol tls1_2)... OK
Checking certificate at BIPublisherOHS (omshost.domain.com:9852, protocol tls1_2)... OK
Checking demo certificate at BIPublisherOHS (omshost.domain.com:9852, protocol tls1_2)... OK
Checking certificate at OMSconsole (omshost.domain.com:7802, protocol tls1_2)... OK
Checking demo certificate at OMSconsole (omshost.domain.com:7802, protocol tls1_2)... OK
Checking certificate at OMSproxy (omshost.domain.com:7301, protocol tls1_2)... OK
Checking demo certificate at OMSproxy (omshost.domain.com:7301, protocol tls1_2)... OK
Checking certificate at OMSupload (omshost.domain.com:4903, protocol tls1_2)... OK
Checking demo certificate at OMSupload (omshost.domain.com:4903, protocol tls1_2)... OK
Checking certificate at WLSadmin (omshost.domain.com:7102, protocol tls1_2)... OK
Checking demo certificate at WLSadmin (omshost.domain.com:7102, protocol tls1_2)... OK

(4) Checking EM13c Oracle home patch levels against 19 Oct 2016 baseline (see notes 1664074.1, 1900943.1, 822485.1, 1470197.1, 1967243.1)

(4a) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) DATABASE BUNDLE PATCH: 12.1.0.2.161018 (OCT2016) (24340679)... OK
24340679 24340679 Patch 24340679 : applied on Tue Oct 25 11:45:36 EDT 2016 Patch description: "DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)"

(4a) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016) (24315824)... OK
Patch 24315824 : applied on Tue Oct 25 11:55:55 EDT 2016 19231857, 19895362, 23265965, 24448282, 22670413, 24315824, 19623450

(4a) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) OCW Interim patch for 24846605 (24846605)... OK
Patch 24846605 : applied on Tue Oct 25 11:51:12 EDT 2016 Patch description: "OCW Interim patch for 24846605"

(4a) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) EM QUERY WITH SQL_ID 4RQ83FNXTF39U PERFORMS POORLY ON ORACLE 12C RELATIVE TO 11G (20243268)... OK
Patch 20243268 : applied on Tue Oct 25 14:30:20 EDT 2016 20243268

(4b) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) sqlnet.ora SQLNET.ENCRYPTION_TYPES_SERVER parameter (76629.1, 2167682.1)... OK
(AES128,AES256,AES192,3DES168)

(4b) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) sqlnet.ora SQLNET.ENCRYPTION_SERVER parameter (76629.1, 2167682.1)... OK
requested

(4b) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) sqlnet.ora SQLNET.ENCRYPTION_TYPES_CLIENT parameter (76629.1, 2167682.1)... OK
(AES128,AES256,AES192,3DES168)

(4b) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) sqlnet.ora SQLNET.ENCRYPTION_CLIENT parameter (76629.1, 2167682.1)... OK
requested

(4b) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) sqlnet.ora SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER parameter (76629.1, 2167682.1)... OK
(SHA1)

(4b) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) sqlnet.ora SQLNET.CRYPTO_CHECKSUM_SERVER parameter (76629.1, 2167682.1)... OK
requested

(4b) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) sqlnet.ora SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT parameter (76629.1, 2167682.1)... OK
(SHA1)

(4b) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) sqlnet.ora SQLNET.CRYPTO_CHECKSUM_CLIENT parameter (76629.1, 2167682.1)... OK
requested

(4b) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) sqlnet.ora SSL_VERSION parameter (1545816.1)... OK
1.0

(4b) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) sqlnet.ora SSL_CIPHER_SUITES parameter (1545816.1)... OK
(SSL_RSA_WITH_AES_128_CBC_SHA,SSL_RSA_WITH_AES_256_CBC_SHA)

(4b) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) listener.ora SSL_VERSION parameter (1545816.1)... OK
1.0

(4b) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) listener.ora SSL_CIPHER_SUITES parameter (1545816.1)... OK
(SSL_RSA_WITH_AES_128_CBC_SHA,SSL_RSA_WITH_AES_256_CBC_SHA)

(4c) *NEW* (/oracle/oem/Middleware13cR2) WLS PATCH SET UPDATE 12.1.3.0.161019 (23744018)... OK
Patch 23744018 : applied on Wed Oct 19 09:56:54 EDT 2016

(5) Checking EM13c Java patch levels against 19 Oct 2016 baseline (see notes 1492980.1, 1616397.1)

(5a) WLS (/oracle/oem/Middleware13cR2/oracle_common/jdk) JAVA SE JDK VERSION 1.7.0-111 (13079846)... OK
1.7.0_111

All tests succeeded.

Visit https://pardydba.wordpress.com/2016/10/28/securing-oracle-enterprise-manager-13cr2/ for the latest version.

Previous Versions

Fix: Plugin error when upgrading EM13cR1 agent to EM13cR2 (13.2) on Windows 2008 R2 x64

I ran into the following issue while attempting to upgrade to the Oracle management agent on my one Windows (2008 R2, x64) server to the 13.2.0.0.0 version distributed with Oracle Enterprise Manager 13.2. The agent upgrade repeatedly failed in the “Upgrading Management Agent” step with an error message:


Exit Code :0
The version is 13.1.0.0.0
Checking for the version 13.2.0.0.0
The agent is not upgraded successfully
[...]
Plugins upgrade failed.
Plugin upgrade failed.
Check the file E:/agent13c/agent_inst/install/plugins_upgrade.txt.status on agent for plugin upgrade status.
Check latest E:/agent13c/agent_inst/install/logs/agentplugindeploy_.log
Plugins upgrade failed.0

The log file referenced in the error message contains some, but not much, additional information:


The command executed for discovery at _2016_10_12_11_48_28 is : E:\agent13c\agent_inst\bin\emctl_upgrade.bat set_discovery_root plugin oracle.sysman.si E:\agent13c\agent_13.2.0.0.0\plugins\oracle.sysman.si.discovery.plugin_13.2.1.0.0 13.2.1.0.0
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
return value is : 65280
Install case : set_discovery_root failure...existing on error value 65280

I fixed this issue by installing the Microsoft Visual C++ 2010 Service Pack 1 Redistributable Package MFC Security Update package on the server. Finally, create a new directory called “prerelogs” inside of the …\agent_13.2.0.0.0\cfgtoollogs\ directory, or else my deployment hung forever during the “Performing prerequisite checks” step. Note that “prerelogs” does not contain the letter q, do not use ‘prereqlogs’. The agent deployment uses this directory to extract software for running the prerequisite checks; possibly setting SCRATCHPATH or another extra parameter would resolve this issue without manually creating the directory, but this worked for me.

After installation of the VC++ 2010 package, which did not require a reboot, followed by creating of the prerelogs directory, I repeated the agent upgrade and it completed successfully.

This issue may or may not occur on other Windows versions, I have not heard any other reports of problems. The first release of EM13c did not seem to require this package.

Script to automate lock down of all EM13c agents to TLSv1.2 with EMCLI

I could not find any obvious documentation about locking down Oracle Enterprise Manager 13c management agents to forbid TLSv1 and TLSv1.1, permitting only TLSv1.2, so I went looking and found the emdpropdefs.xml file in $AGENT_HOME/agent_13.1.0.0.0/sysman/admin/ that documents the existence of the minimumTLSVersion property in emd.properties:

name='minimumTLSVersion'
modifiable='true'
defaultValue='TLSv1'
description='The oldest version of the TLS protocol which this agent should support when accepting connections or initiating connections to the OMS. Currently supported values are "TLSv1", "TLSv1.1", and "TLSv1.2".'
valueType='String'
advanced='true'
migrate='source'
filename='emd.properties'
category='Runtime Settings'
internal='true'
restartRequired='true'

I tested this parameter on my OMS server agent, restarted the agent, and confirmed with my Securing Oracle Enterprise Manager 13c script that the agent no longer allowed connections using any protocol other than TLSv1.2. Next I wanted to automated this, to avoid the effort of manually changing this property on each agent and then restarting that agent, so I went directly to EMCLI which allows EM13c admins to (among many other things) set agent properties and restart agents. I then created a script to fetch a list of all agents, check for the TLS protocols each agent permits, and then apply the change and restart the agent for every agent that I had not already locked down. I have copied this script below.

Before using the script, you must login to EMCLI using “emcli login -username=yourusername” and provide your password. For security reasons I elected not to wrap the EMCLI login within this script; that way you do not have to trust my script to handle your password securely, as the script never sees your password. For the step to restart your agents to work correctly, you need to make sure that your EM13c user account has preferred host credentials set for your agent targets that can successfully login to the host server and restart the agent.

Here is a copy of the script, followed by the (anonymized) output from a sample run. Someday soon I will get set up on github to make it easier to retrieve my scripts, but for now you can copy and paste this. This script expects to find the emcli binary inside of the $MW_HOME/bin directory, so make sure you have $MW_HOME set before running it, or provide the full path to EMCLI within the script. It will also log you out of EMCLI when the script completes.


#!/bin/bash
#
# This script will retrieve a list of agents from your EM13c environment,
# determine if they allow connections using TLS protocol versions older
# than TLSv1.2, and then disable all protocols older than TLSv1.2.
#
# Finally it will restart each modified agent to apply the change.
#
# You need to login to EMCLI first before running this script.
#
# Released v0.1: Initial beta release 5 Oct 2016
#
#
# From: @BrianPardy on Twitter
# https://pardydba.wordpress.com/
#
# Known functional on Linux x86-64, may work on Solaris and AIX.

EMCLI=$MW_HOME/bin/emcli

if [[ -x "/usr/sfw/bin/gegrep" ]]; then
GREP=/usr/sfw/bin/gegrep
else
GREP=`which grep`
fi

OPENSSL=`which openssl`

if [[ -x "/usr/bin/openssl1" && -f "/etc/SuSE-release" ]]; then
OPENSSL=`which openssl1`
fi

OPENSSL_HAS_TLS1_2=`$OPENSSL s_client help 2>&1 | $GREP -c tls1_2`

$EMCLI sync
NOT_LOGGED_IN=$?

if [[ $NOT_LOGGED_IN > 0 ]]; then
echo "Login to EMCLI with \"$EMCLI login -username=USER\" then run this script again"
exit 1
fi

for agent in `$EMCLI get_targets -targets=oracle_emd | grep oracle_emd | awk '{print $4}'`
do
echo
if [[ $OPENSSL_HAS_TLS1_2 > 0 ]]; then
echo -n "Checking TLSv1 on $agent... "

OPENSSL_RETURN=`echo Q | $OPENSSL s_client -prexit -connect $agent -tls1 2>&1 | $GREP Cipher | $GREP -c 0000`

if [[ $OPENSSL_RETURN == 0 ]]; then
echo "allows TLSv1"
else
echo "already forbids TLSv1"
fi
fi

if [[ $OPENSSL_HAS_TLS1_2 > 0 ]]; then
echo -n "Checking TLSv1.1 on $agent... "

OPENSSL_TLS11_RETURN=`echo Q | $OPENSSL s_client -prexit -connect $agent -tls1_1 2>&1 | $GREP Cipher | $GREP -c 0000`

if [[ $OPENSSL_RETURN == 0 ]]; then
echo "allows TLSv1.1"
else
echo "already forbids TLSv1.1"
fi
fi

if [[ $OPENSSL_RETURN == 0 || $OPENSSL_TLS11_RETURN == 0 ]]; then
$EMCLI set_agent_property -agent_name=$agent -name=minimumTLSVersion -value=TLSv1.2 -new

echo
echo "Restarting $agent to apply changes"
$EMCLI restart_agent -agent_name=$agent -credential_setname="HostCreds"
RESTART_RETURN=$?

if [[ $RESTART_RETURN != 0 ]]; then
echo "Unable to restart agent: restart agent manually or set preferred host credentials for agent"
fi
fi
done

$EMCLI logout

exit 0

Sample (anonymized) output below. Note how the script cannot restart an agent lacking preferred host credentials. In this case, I assign preferred host credentials and then re-run the script to complete the process.


Synchronized successfully

Checking TLSv1 on server1.subdomain.domain.com:1830... already forbids TLSv1
Checking TLSv1.1 on server1.subdomain.domain.com:1830... already forbids TLSv1.1

Checking TLSv1 on server2.domain.com:3872... already forbids TLSv1
Checking TLSv1.1 on server2.domain.com:3872... already forbids TLSv1.1

Checking TLSv1 on server3.domain.com:3872... already forbids TLSv1
Checking TLSv1.1 on server3.domain.com:3872... already forbids TLSv1.1

Checking TLSv1 on server4.domain.com:3872... already forbids TLSv1
Checking TLSv1.1 on server4.domain.com:3872... already forbids TLSv1.1

Checking TLSv1 on server5.domain.com:1830... already forbids TLSv1
Checking TLSv1.1 on server5.domain.com:1830... already forbids TLSv1.1

Checking TLSv1 on server6.domain.com:1830... already forbids TLSv1
Checking TLSv1.1 on server6.domain.com:1830... already forbids TLSv1.1

Checking TLSv1 on server7.domain.com:3872... already forbids TLSv1
Checking TLSv1.1 on server7.domain.com:3872... already forbids TLSv1.1

Checking TLSv1 on server8.domain.com:3872... already forbids TLSv1
Checking TLSv1.1 on server8.domain.com:3872... already forbids TLSv1.1

Checking TLSv1 on server9.domain.com:3872... already forbids TLSv1
Checking TLSv1.1 on server9.domain.com:3872... already forbids TLSv1.1

Checking TLSv1 on server10.domain.com:1830... already forbids TLSv1
Checking TLSv1.1 on server10.domain.com:1830... already forbids TLSv1.1

Checking TLSv1 on server11.domain.com:1830... already forbids TLSv1
Checking TLSv1.1 on server11.domain.com:1830... already forbids TLSv1.1

Checking TLSv1 on server12.domain.com:1830... already forbids TLSv1
Checking TLSv1.1 on server12.domain.com:1830... already forbids TLSv1.1

Checking TLSv1 on omshost.domain.com:3872... already forbids TLSv1
Checking TLSv1.1 on omshost.domain.com:3872... already forbids TLSv1.1

Checking TLSv1 on server13.domain.com:3872... allows TLSv1
Checking TLSv1.1 on server13.domain.com:3872... allows TLSv1.1
Agent Property minimumTLSVersion has been successfully updated to the value TLSv1.2.

Restarting server13.domain.com:3872 to apply changes
The Restart operation is in progress for the Agent: server13.domain.com:3872
The Agent "server13.domain.com:3872" has been restarted successfully.
---------------------
Operation Output
---------------------
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.Stopping agent ... stopped.Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.Starting agent ................ started.

Checking TLSv1 on server14.domain.com:1830... allows TLSv1
Checking TLSv1.1 on server14.domain.com:1830... allows TLSv1.1
Agent Property minimumTLSVersion has been successfully updated to the value TLSv1.2.

Restarting server14.domain.com:1830 to apply changes
The Restart operation is in progress for the Agent: server14.domain.com:1830
Unable to restart agent: restart agent manually or set preferred host credentials for agent

Checking TLSv1 on server15.domain.com:3872... already forbids TLSv1
Checking TLSv1.1 on server15.domain.com:3872... already forbids TLSv1.1

Checking TLSv1 on server16.domain.com:3872... already forbids TLSv1
Checking TLSv1.1 on server16.domain.com:3872... already forbids TLSv1.1

Checking TLSv1 on server17.domain.com:3872... already forbids TLSv1
Checking TLSv1.1 on server17.domain.com:3872... already forbids TLSv1.1
Logout successful

See you at OpenWorld 2016!

For the first time in the 20 years I’ve worked with Oracle’s products, I will attend OpenWorld this year. If you see me there please feel free to stop me and say hello. I will attend sessions here and there, though I will not give any presentations or talks. I do expect to have an interesting surprise to share, though. Stay tuned.

Oracle PSU 12.1.0.2.160719 (patch 23054246) for Linux x86-64 requires libodbcinst

Oracle recently released patch 23054246 (DATABASE PATCH SET UPDATE 12.1.0.2.160719) for database 12.1.0.2, containing security updates from the July 2016 critical patch update advisory.

[EDIT 20160726: Oracle has documented this issue in MOS note 2163593.1]

This patch appears to have introduced a dependency on libodbcinst. During my first attempt to install this patch, I received errors while linking libsqora. The error appears as follows in OPatch logs:


[Jul 20, 2016 11:22:57 AM] The following warnings have occurred during OPatch execution:
[Jul 20, 2016 11:22:57 AM] 1) OUI-67200:Make failed to invoke "/usr/bin/make -f ins_odbc.mk isqora ORACLE_HOME=/oracle/oem/product/12.1.0/awrdb"....'/usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld: cannot find -lodbcinst
collect2: ld returned 1 exit status
make: *** [/oracle/oem/product/12.1.0/awrdb/odbc/lib/libsqora.so.12.1] Error 1
'
[Jul 20, 2016 11:22:57 AM] 2) OUI-67124:Re-link fails on target "isqora".
[Jul 20, 2016 11:22:57 AM] 3) OUI-67200:Make failed to invoke "/usr/bin/make -f ins_odbc.mk isqora ORACLE_HOME=/oracle/oem/product/12.1.0/awrdb"....'/usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld: cannot find -lodbcinst
collect2: ld returned 1 exit status
make: *** [/oracle/oem/product/12.1.0/awrdb/odbc/lib/libsqora.so.12.1] Error 1
'
[Jul 20, 2016 11:22:57 AM] 4) OUI-67124:
NApply was not able to restore the home. Please invoke the following scripts:
- restore.[sh,bat]
- make.txt (Unix only)
to restore the ORACLE_HOME. They are located under
"/oracle/oem/product/12.1.0/awrdb/.patch_storage/NApply/2016-07-20_11-20-22AM"

After installing the unixODBC package on my SLES11 system, this error went away.

[Update: see also Brian Peasland’s blog post “July 2016 PSU fails to make isqora” for a different workaround to this issue that does not involving installing any additional packages.]

At the time of release, Oracle’s installation requirements for database 12.1.0.2 listed the unixODBC package as an optional dependency, required only “[i]f you intend to use ODBC”. This no longer seems to hold true. At the moment Oracle has not made it clear whether or not patch 23054246 contains a bug that introduces the libodbcinst dependency or if the database platform will require this library in all cases going forward.

If you have attempted patch application without libodbcinst available, the opatch apply step will fail and you will have to manually revert the patch, following the instructions that OPatch provides and/or contact Oracle Support for guidance. In my case, I followed the instructions to revert, installed unixODBC, then attempted again to apply the patch, at which point it completed successfully as expected. If you have not yet attempted to apply this patch, I highly recommend installing unixODBC first. I have already seen two others report on Twitter that they encountered this issue but none have yet confirmed to me that installing unixODBC resolved the problem. I believe it will.

UPDATE: See also “BUG 24332805 – OUI-67124:RE-LINK FAILS ON TARGET “ISQORA” DURING JUL 2016 PSU APPLY” once made public.

Review: Full Genomes Corp third party analysis of Veritas Genetics raw WGS data

In this post, I will provide my review of Full Genomes Corp‘s service offering third party analysis of raw data produced by Veritas Genetics‘ $999 whole genome sequencing (Veritas myGenome). After I released my raw genome data to the public domain, FGC contacted me and offered to run my WGS data through their BAM processing pipeline at no cost. I naturally accepted and agreed to write a review.

This service from FGC includes three categories of analysis: mtDNA, YDNA, and autosomal ancestry. As of now, I have received my mtDNA and YDNA results; the autosomal analysis takes longer to produce and I will leave it out of scope for this review.

Getting Started

After creating an account on the FGC site, I needed to provide them with access to the BAM file that Veritas Genetics produced. My participation in the Personal Genome Project made this easy as I only had to give them the URL to my BAM file on the PGP public data repository.

A little bit more than two weeks later I received email reporting that I had results ready. When I logged back in to FGC a prominent link provided access to download all of my results in a single zip archive. This zip archive contained a readme file directing me to two PDF documents with further information: one focused on extracting private SNPs from YDNA results and the second describing the individual data files FGC returns, which I will get to below.

Mitochondrial DNA results

I have already had my full mitochondrial DNA sequenced by FamilyTreeDNA, so I did not expect to learn anything new from FGC’s data analysis, which produced two files. The first file contains a list of variants found in my mtDNA with respect to the Yoruba reference sequence by position. The second file contains my full mtDNA sequence in FASTA format.

The FASTA file took me by surprise, as they indicated a heteroplasmic length variant that FamilyTreeDNA had not come across (or had not informed me of) in their Sanger sequencing. FGC found a deletion at position 310, the loss of a T flanked by C repeats on both sides. I do not know if this information will turn out relevant for me, but who knows, I prefer to have it.

YDNA results

FGC grouped my YDNA results into two folders: YSTR and YSNP.

YSTR

YSTR results consisted of two output files generated from lobSTR. The first file contains roughly 3000 lines of data reporting identified YSTRs according to NIST/lobSTR standards, with some additional markers FGC has added to lobSTR.

The second file contains a subset of the first file including only those YSTR markers which FamilyTreeDNA tests and reports, counted according to FamilyTreeDNA’s standards. Mine reported values for 95 FTDNA-style markers.

Prior to whole genome sequencing I had only FTDNA’s 67 marker YSTR results combined with 23andMe‘s v3 chip Y SNPs with which to determine my YDNA haplogroup, giving nothing more specific than the huge R1b M269 group. I have not yet found my YSTR results from FGC particularly useful as not very many males from my line appear to have taken YDNA testing, so I do not have many data points to compare to.  I do have several close matches on FTDNA’s 67 marker test sharing variants of my surname which have convinced me that I don’t need to consider non paternity events along my direct male line going back at least 400 years based on the known years when Paradis YDNA arrived in Canada from France.

Once more Paradis-descended men take YDNA tests like the Veritas myGenome, FGC Y-Elite, FTDNA Big Y or others, I expect this data to have more value in tracing drift across this line.

YSNP

YSNP results consisted of five separate files. Two described as variant discovery reports, two as variant genotyping, and one haplogroup classification report containing output from yKnot that identifies my sample’s place in the ISOGG tree.

Haplogroup Classification

I have provided below a portion of my yKnot file showing the placement of my YDNA on the ISOGG tree back to the R1b M343+ branch. For the moment, I sit on the S1217+/Z295+ branch (ISOGG, Big Tree). I do not match any subclades of S1217+/Z295+ yet identified, but I will follow developments in this area, and, having my genome already sequenced, can place myself on future revised trees without the need for any further SNP testing.

*Extras: Z1518+, Y4010+, 50f2(P)+, Z14907+, PH3244*, Y2550+, P80+, CTS1789+, CTS12019+, L1228+, M3629+, Z3327+, Z28+, FGC5628+, CTS12440+, PF2372+, M162_1*, FGC5085+, Z13028+, P266+, Z12253+, L798+, DYS257_2+, Z28771*, P27.2_2+, Y2252+, CTS616+, CTS2646*, M118+, M236+, Y2754+, FGC20667*, M141+, L665+, L588+, Z14350+, P34_5+, Z6859+, Z889+, Z13537*, Z6171+, Z1237+, FGC756+, BY451+,     P19_1*, P79*, PF2276+, Z16986+, M5220+, FGC1920+, Z12467+, Z1842+, V161.1+, V190+, CTS6911+, CTS2518+, FGC4872+, Y5185*, Y2986+, Z1101+, CTS32+, Z15165+, IMS-JST022457+, PF2779+, S730+, S504+, Z836*, Z14050+, IMS-JST029149+, M1994*, L990+, P198+, Z16208+, PF3126+, Z2182*
R1b1a2a1a2a1a1a
|Matches: S1217+, Z295+
|____R1b1a2a1a2a1a1
     |Matches: S230+, Z209+, S356+, Z220+
     |____R1b1a2a1a2a1a
          |Matches: Z272+
          |*No-calls: Z274?, S229?
          |____R1b1a2a1a2a1
               |Matches: Z195+, S227+
               |*No-calls: S355?, Z196?
               |____R1b1a2a1a2a
                    |Matches: DF27+, S250+
                    |____R1b1a2a1a2
                         |Matches: P312+, PF6547+, S116+
                         |____R1b1a2a1a
                              |Matches: L151+, PF6542+, L52+, PF6541+, P310+, PF6546+, S129+, P311+, PF6545+, S128+, PF6539+
                              |*No-calls: (being investigated as to placement: L11?, S127)?
                              |____R1b1a2a1
                                   |Matches: L51+, M412+, PF6536+, S167+
                                   |____R1b1a2a
                                        |Matches: L23+, PF6534+, S141+, L49.1+, S349.1+
                                        |____R1b1a2
                                             |Matches: M269+, CTS623+, CTS2664+, PF6454+, CTS3575+, PF6457+, CTS8728+, L1063+, PF6480+, S13+, CTS12478+, PF6529+, F1794+, PF6455+, L265+, PF6431+, L407+, PF6252+, L478+, PF6403+, L482+, PF6427+, L483+, L500+,   PF6481+, L773+, PF6421+, YSC0000276+, L1353+, PF6489+, YSC0000294+, M520+, PF6410+, PF6399+, S10+, PF6404+, PF6505+, YSC0000225+,   PF6409+, PF6411+, PF6425+, PF6430+, PF6432+, PF6434+, PF6438+, PF6475+, S17+, YSC0000269+, PF6482+, YSC0000203+, PF6485+, S3+, PF6494+, PF6495+, PF6497+, YSC0000219+, PF6500+, PF6507+, PF6509+, L150.1+, PF6274.1+, S351.1+
                                             |*No-calls: PF6443?
                                             |**Mismatches: CTS8591- (exp. +), CTS8665- (exp. +), FGC464- (exp. +), CTS10834- (exp. +), CTS11468- (exp. +), FGC49- (exp. +)
                                             |____R1b1a
                                                  |Matches: P297+, PF6398+, L320+
                                                  |____R1b1
                                                       |Matches: P25_3+, L278+, M415+, PF6251+
                                                       |**Mismatches: P25_1- (exp. +), P25_2- (exp. +)
                                                       |____R1b
                                                            |Matches: M343+, PF6242+

Variant Genotyping

The first variant genotyping file provides my results at a little over 54,000 known SNPs. The second variant genotyping file provides results for an additional 16,600 SNPs. The results provided include counts of each base called at the SNP position as identified in my BAM file data, the SNP position on the chromosome, and the build 37 reference sequence call at that position. I do not know the criteria used to place each SNP in each file. I consider these files more as an intermediate step in the data analysis, used to generate the other returned files, but I expect I will find some more direct use for them as well.

Variant Discovery

The two variant discovery reports provide the most detailed and useful information in my opinion, as they include quality rankings on variants as well as the specific details of variants such as SNPs and INDELs. Even more usefully, these files contain the results for the kits most similar to mine within FGC’s database, which can help in identifying private variants that originated in much more recent genealogical times. Because these files include data from others as well as my own, I cannot comfortably release them to the general public without redacting other individuals’ data. For public facing purposes if someone wanted to run comparisons against my detailed data I would most likely refer them to the Big Tree (if R1b) or advise that they pursue their own analysis with FGC directly. The how-to document FGC provides with this analysis (Reading the Full Genomes analysis reports) explains working with this data much better than I could in my own words. The inclusion of quality scores greatly simplifies the process of narrowing down on key SNPs, and I look forward to spending more time with this data — probably after more Paradis males have had next generation YDNA sequencing as my results appear rather distant from the nearest matching males in any database except for the one Paradis I’ve found with a Big-Y at FTDNA.

Data Sharing

It pleased me to see that FGC offers a very quick and easy method to share your results with any email address you provide. I took advantage of this to share my data with Alex Williamson for inclusion in the Big Tree to aid in reconstructing the phylogeny of the R1b tree under R P312. For now, my Big Tree entry sits in the R-Z295/S1217 paragroup, awaiting more submissions sharing SNPs with me to help identify a terminal SNP more recent than the estimated 3900 year old Z295. I don’t match any SNPs identified as downstream of Z295 on the FTDNA tree, the ISOGG tree, or the YFull tree. I encourage any other Z295 or Paradis/Pardy/Paradee/etc male to get your YDNA analyzed and shared with these projects so we can better place ourselves on the tree.

More Info

If this has interested you, I highly recommend you take a look at another review and description of FGC’s analysis.

wp-1460183418001.png

Take my $1000 genome, please!

I have just released my whole genome sequence (WGS) to the public domain (CC0, no rights reserved), via the Harvard Personal Genome Project (PGP). I believe that my data represents both the first $1000 genome-with-analysis ever performed as well as the first $1000 genome released for public use. Thank you to both the PGP and to Veritas Genetics for making this possible. I would like to specifically thank Mirza Cifric, CEO of Veritas Genetics and also Christen Hart of Veritas for acting as my liaison and dealing with my frequent email requests for status updates. From my PGP profile page you can download my genome data (as a BAM file (17.8GB) or in VCF format (383MB)), as well as my 23andMe (v3, pre-FDA letter) SNP chip data and my full mitochondrial DNA sequence as tested by FamilyTreeDNA (since deposited in GenBank as accession ID KU530226).

Why would I do this?

Put simply, I wanted to make a contribution to science. Further, since working for a genomic drug development company in the 2000s where I met, then married, a bioinformatician, I’ve had an interest in the potential applications of genomics, from what some then referred to as the “pharmaceutically tractable genome” to today’s “precision medicine”. That employer spun off an early DNA sequencing platform (454 Life Sciences pyrosequencing, the first company to complete and make public an individual human genome), and I find it fitting that an ex-employee, and one from the IT staff, not even the scientific team, would release the first public $1000 genome.

I would like to see science make some good use of my genetic data. Only a relatively small number of whole genome sequences available for scientific research without privacy or intellectual property encumbrances exist. As a participant in the PGP, by making my genome available I hope not only to directly support scientific research but to aid the PGP’s other research goal to identify the risk and consequences of having one’s genetic data available to the public without any effort at de-identification or obfuscation. I have the benefit of living in one of the few states with genetic information laws that exceed the US Federal Genetic Information Nondiscrimination Act in placing restrictions on life insurance providers and others.

After my first blood labs with my current primary care doctor, she told me that I had the absolute worst blood levels of vitamin D that she had ever seen, along with the best HDL/LDL cholesterol levels she had seen. This comes from a genetic basis, not anything that I have pursued through diet or lifestyle. In fact my cholesterol should be, frankly, terrible, and though I live only a few miles south of the 45th parallel I get enough sun that lack of exposure can’t account for my vitamin D levels alone. My 23andMe data, when run through Promethease, reveals a train wreck throughput the vitamin D pathway, as well as matching many variants known to increase HDL cholesterol. With my whole genome sequence released for any imaginable use, I hope that researchers can either spot something unique enough on its own or work my data into genome wide association studies (GWAS) to tease out some drug targets or relevant alleles.

As a PGP participant I have filled out the PGP’s phenotype surveys to help associate phenotypes with my genotype. I have done the same at OpenHumans and remain willing to provide further phenotype data on request. I will attend the GET Conference and GET Labs 2016 at the end of April and get signed up with some other research studies.

You can also find my autosomal SNP chip data on GEDMatch as kit M205442, my YDNA data at ysearch under id CZVXU, and my full mitochondrial DNA sequence in GenBank as KU530226 (though services report my mtDNA haplogroup as U2e1*, I hope the next build of PhyloTree will note the mtDNA SNPs I carry extraneous to U2e1 and define a new haplogroup as with my deposition several mtDNA sequence motifs now have three independent depositions, enough to justify naming a new U2e1* branch). I have much of my genealogy traced several generations back and several apparent triangulation groups worth of matches. Genealogy traces my surname back to the Paradis in Quebec but hits a brick wall in the mid 1800s, though my YDNA 67-STR results at FTDNA show close matches with other tested Paradis males who have traceable lineages back to Pierre Paradis of Mortagne-au-Perche, France (d. 1675), apparent patriarch of new world Paradis/Pardy lines. Several of my lines go back to early US colonials (Trowbridge provides my nexus to Charlemagne, though I’ve found no Mayflower descendents), as well as mixed ancestry (French/German/more) Creoles along the German Coast in Louisiana. I also have a bit of direct Scottish (Halcro) ancestry along with other Scots-Irish.

How can a security and privacy aware individual choose to release this data?

For me, the recognition that sequencing continues to fall in price and will eventually become ubiquitous to the point of banality, coupled with the fact that we shed DNA all day long convinces me that any genetic privacy we may believe we have now exists only for a disappearing moment in history and only in lieu of a determined adversary willing to put some effort into collection. Setting aside the issue of disclosing one’s unique genetic signature to third parties, simply knowing what secrets sit in one’s own DNA empowers some individuals but makes others uneasy. Some people do not want to know if their genetics give them a high probability of Alzheimers, or a disposition to cancer. Some regulators believe they cannot trust the public to make responsible decisions once given knowledge of the forbidden fruit in their genetic code. Because science does not yet know enough about the complex interactions of all parts of the genome to determine the exact medical significance of every gene or non-gene variant, the interpretation of your static genome can and will change with the ongoing discovery of new genetic associations and with failures to replicate previously reported associations. By donating my sequence to an unencumbered public dataset I hope to help speed up this process and embolden others to take this step to share for science, with eyes wide open as to the limitations of data de-identification and possibilities of personalized medicine. Whether you share your genome through the PGP, your microbiome through uBiome, the next virus you catch through GoViral, your FitBit data through OpenHumans, your direct to consumer SNP chip results through OpenSNP, or any other data through any other platform, each of us has a unique chance to contribute to research to better lives today and our species tomorrow.

What does whole genome sequencing give a non-expert that SNP genotyping doesn’t?

Several years ago I took 23andMe’s genotyping test. As this occurred prior to the FDA sending 23andMe a nastygram barring them from reporting health-relevant results, I received a decent amount of information relevant to health issues. So why bother having a whole genome sequence done? To put it simply, a WGS has more long-term value than a genotyping SNP chip. As 23andMe V2 customers discovered, as time moves on and science learns more about genetic variants, and as new builds of the human genome get released, SNP results based on older data lose their relevance. New genome scaffolds obsolete what we believed we knew about older SNPs. New SNPs get discovered with more meaningful disease associations than those believed to associate with diseases years ago during chip design. With my whole genome sequence in my pocket, I have better positioning for the future as I can look up newly-reported variants going forward whether or not the designer of the probes on a SNP chip foresaw the relevance of that genetic region. If I develop cancer in the future, I or my medical providers can compare the sequence of a tumor cell to my genome sequence, easing the process of identifying genes that may have gone haywire and caused cancer, and potentially informing the selection of anti-cancer drugs that could save my life. Further, by ordering and releasing my whole genome sequence, scientists working with public datasets can perform more useful analyses than those available simply from releasing my SNP chip data.

Go use my data!

Updates

Mike Cariaso has graciously run Promethease against my WGS data. Results here. Unfortunately Promethease results expire after a number of days, rendering this report now inaccessible.