Author Archives: Brian Pardy

About Brian Pardy

Oracle DBA, freshwater aquarist, amateur genetic genealogist

EM13cR2 AWR Warehouse “Error communicating with agent” during transfer step with custom certificates

I have just noticed and resolved an issue in my EM13c R2 AWR Warehouse environment that I brought upon myself, hence a blog post for any others who might run into this, which also seems like a good time to release the scripts I use to generate and populate Oracle wallets for my EM13c agents.

After moving an AWRW source database from one EM13c managed server to a different EM13c managed server (same OS, same DB release), AWRW loads from that server began to fail. While debugging the issue, I first had to resolve an already-documented issue (see MOS note 2075341.1) where the source database had a NULL definition for the CAW_EXTR directory object, then fix up the data in the DBSNMP.CAW_EXTRACT_PROPERTIES table to reflect the CAW_EXTR directory. After resolving that, AWRW extracts ran successfully from the source database, but began to hang indefinitely during the CAW_RUN_ETL_NOW job in the transferAWR/transferFile job step, displaying only a cryptic error message:

An unhelpful error message

A helpful error message

I ran through many debugging steps: changing preferred credentials, bouncing the agents, checking for firewalls blocking connectivity, none seemed to help. Eventually I realized the step I had missed in setting up the new managed server where the source database now runs: I had not generated an Oracle wallet for the agent on the new server, while I did have an Oracle wallet for the agent on the previous, now-retired server. This created an issue because I have secured the agent on my OMS host (where my AWRW repository database runs) with a custom third party certificate, and the new agent, lacking a wallet containing a trusted root certificate to which it could trace the repository agent’s certificate, could not initiate a connection from the AWRW source DB host agent to the AWRW repository DB host agent.

I generated a wallet for the new agent, added the trusted root certificate and a certificate for the host to the wallet, stopped the agent, deployed the wallet, and started the agent. After those steps, running the AWRW load from this source database completed successfully. I believe that the missing trusted root certificate prevented the creation of a secure channel between the two agents. I probably did not need to add the host certificate to resolve this problem, but consider it a good practice anyway.

If you read this far, you may find my create_agent_wallets.sh script useful to generate wallets and certificate signing requests for every agent in your environment. If you find the wallet creation script useful, you may also find my import_agent_wallets.sh script useful to populate those wallets with signed certificates received from your CA.

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.