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.

Securing Oracle Enterprise Manager 13c

Introduction

This post continues my series on securing Oracle Enterprise Manager environments with some updates relevant to EM13c. Oracle has made significant security improvements with Oracle Enterprise Manager 13c over the prior 12c version, first released in October 2011, more than four and a half years ago at this point. In the interest of security, I have to strongly recommend that any sites still using EM12c upgrade to (or perform a fresh installation of) EM13c as soon as possible. More recent versions of EM12c like 12.1.0.5 (June 2015) continue to use the same technology stack as the initial release, and the world of security has massively changed since then. Notably, EM13c uses Java 7, WebLogic 12.1.3, and disables SSLv3 out of the box.

Just to recap, back at the EM12c original release date:

  • Practically nobody had ever heard of Edward Snowden
  • The first release of Java 7 celebrated its three month birthday
  • Two months later, Oracle released WebLogic 12c; EM12c users remained on WebLogic 10.3.6
  • One month earlier, the public learned of the BEAST attack and people still believed that using RC4 (immune to BEAST) as a workaround improved security (spoiler warning: it did not)
  • We had three years to wait before the POODLE vulnerability caused vendors to recognize the need to disable SSLv3 (you DID disable SSLv3, right?)
  • Oracle still considered the MD5 hashing algorithm good enough to use in self-signed certificates produced by EM12c, despite flaws known to exist since 1996
  • Web browsers considered the SHA-1 hashing algorithm, now also deprecated due to brokenness, good enough to use

As the security world’s known unknowns collapsed around us, proactive EM12c administrators sought to make the best of their lot. Outside of Oracle, I and others poked at the software and wrote blog articles, while inside Oracle effort proceeded to support more recent Java releases that brought with them better cipher suites and hashing algorithms, as well as the usual security fixes. This process took some time for all involved and hit a few bumps along the way.

I do not intend in this post to review general day-to-day EM13c security design such as user roles or privileges, object level security within OEM, or integration with identity providers like LDAP; only the infrastructure level issues that tend to change in brief large bursts as new attacks come out. See this excellent list of EM13c blogs, links and videos that Philip Brown has provided for more details on these and other items.

On to EM13c

EM13c admins need to keep an eye on the same sorts of items as with EM12c. We really should read the documentation, even if only the Security Guide. I admit I often do not. It contains good information.

Patches

I consider it critical for admins to keep up with the OEM periodic patches, particularly security patches. The script below covers patches up to and including March 31, 2016. I plan to update again after the next set of Oracle security patches arrives, likely mid-April.

Certificates

The process for applying certificates on EM13c does not appear to have changed significantly from the prior version. I have confirmed that the new “omspatcher” tool that replaces opatchauto when applying a system patch to the OMS works perfectly fine with certificates on WebLogic that use the SHA-256 hashing algorithm. Given the upcoming deprecation of SHA-1 across all major browsers I do not see any valid reason not to use SHA-256 with new certificates.

Ciphersuites

Out of the box, my EM13c installation rejected weak ciphersuites and accepted the strong ones. Unfortunately it still accepted some that these versions of Java and OpenSSL consider as MEDIUM strength, so I want to disable those across the entire environment, leaving only the strongest ciphersuites available in this release and permitting other ciphersuites only where necessary.

[UPDATE 20160518: Please see MOS note 2138391.1 for the official procedure to disable weak cipher suites in EM13c.]

We will have to live with these unwanted ciphersuites enabled until Oracle provides a supported procedure to disable them across the entire stack. I have performed some preliminary tests and I find it very easy to get OEM into a situation where it cannot startup after manually editing config files that define enabled ciphersuites. The script below will identify ports permitting ciphersuites you may wish to disable when a supported method becomes available.

UPDATE 20160720: Take particular care of watching the ciphersuites accepted by your agents if you upgrade the JDK that the agents use. I just attempted a JDK update on an agent from the distributed version to 1.7.0-111, and that agent began to accept LOW and MEDIUM strength ciphersuites again, thus I have omitted JDK updates for agents from the check script.

Security Checkup

Below I provide an early version of the script I use to validate EM13c security configuration. I based this on my earlier EM12c script, linked above. The script will become more useful once I implement patch level checking after release of the first set of EM13c patches, but for the moment it will inspect your EM13c environment to identify relevant ports and confirm that your system will not respond to SSLv2 or SSLv3 requests, does respond to TLSv1 requests, supports HIGH, but not LOW or MEDIUM strength ciphersuites (as defined by the version of OpenSSL installed on your OMS host), and finally checks for the presence of demonstration-not-for-production-use certificates and self-signed certificates.

(A caveat on self-signed certificate checking: OpenSSL, not this script, performs the check, therefore if OpenSSL cannot validate your certificate to a trusted root, this script cannot either. If a well known certification authority has signed your certificates, OpenSSL should validate them successfully, but it may not do so if you use an internal certificate authority to sign certificates. In that case you should install a copy of your internal CA as a trusted root certificate in the system trust store so that OpenSSL can validate your EM13c certificates. I cannot document this process for every OS but Linux users should look to the documentation for the update-ca-certificates or update-ca-trust commands. If my script below incorrectly reports your certificate as self-signed, you can ignore the finding or address the issue at the OpenSSL level.)

EM13c TLS Security Checkup Script

[LATEST UPDATE: 20160914 bugfix and enhancements, no new patch checks, version 0.8]. Thank you to Paige who reported a bug in the check of the SSL_CIPHER_SUITES parameter. I had a typo in the cipher suite names for the SSL_CIPHER_SUITES parameter, which I have now fixed. In researching this I realized that this parameter provides control over SSL/TLS authentication for clients, which I do not use in my environment. Instead I use native SQL*Net encryption, controlled by the various ENCRYPTION_(CLIENT|SERVER), ENCRYPTION_TYPES_(CLIENT|SERVER), CRYPTO_CHECKSUM_(CLIENT|SERVER), and CRYPTO_CHECKSUM_TYPES_(CLIENT|SERVER) parameters, which I have added into the script. The script will check to make sure that you do not permit MD5 as a SQL*Net checksum algorithm and that you do not permit DES, DES40, 3DES112, nor any of the RC4_ algorithms for SQL*Net encryption. Unfortunately due to bug 23587582, you will encounter problems promoting targets in OEM unless you allow use of the 3DES168 encryption algorithm and SHA1 hashing algorithm. Generally I would prefer to disable both of those for security reasons but I will permit them in this script as long as they remain necessary for full OEM functionality.

[PRIOR UPDATE: 20160819 for 20160816 bundle patches, version 0.7]. With this update, I have added support for TLSv1.1 and TLSv1.2 to the protocol checks. I have also added support for the optional SLES11 openssl1 package which provides a newer OpenSSL supporting TLSv1.1 and TLSv1.2 for systems on SLES11 like mine. The script will now dynamically determine (by parsing the “openssl s_client help” output) if your OpenSSL version supports TLSv1.2. If your OpenSSL version DOES support TLSv1.2, the script will now flag any support of TLSv1 or TLSv1.1 as a failure in your OEM stack. If your OpenSSL does NOT support TLSv1.2, the script will consider TLSv1 support in OEM as acceptable. If you notice problems with this new functionality please let me know.

Compatibility

Only tested on Linux x86-64, but may work on AIX and Solaris as the EM12c version I built this upon did work there. Planned future enhancements include checking that you have disabled non-encrypted HTTP access to EM13c components, upgraded Java to the most recent EM13c-supported release, and more.

You can download the latest version of the script from pastebin.


#!/bin/bash
#
# This script should examine your EM13c environment, identify the ports
# each component uses, and check for SSLv2/SSLv3 usage, as well as make
# sure that weak cipher suites get rejected. It will also validate your
# system comparing against the latest recommended patches and also flags
# the use of demo or self-signed certificates. Latest enhancement adds
# checks for the EM13c WLS Java JDK version.
#
# Released v0.1: Initial beta release 5 Apr 2016
# Changes v0.2: Updated for current patches
# Changes v0.3: APR2016 patchset added
# Changes v0.4: Plugin updates for 20160429
# Changes v0.5: Plugin updates for 20160531
# Changes v0.6: Plugin/OMS/DB updates for 20160719 CPU + Java check
# Changes v0.7: Plugin/OMS updates for 20160816 bundles
# Support for SLES11 OpenSSL 1 parallel package
# Add checks for TLSv1.1, TLSv1.2
# Permit only TLSv1.2 where supported by OpenSSL
# Changes v0.8: Fix broken check for SSL_CIPHER_SUITES
# Add checks for ENCRYPTION_SERVER, ENCRYPTION_CLIENT,
# CRYPTO_CHECKSUM_SERVER, CRYPTO_CHECKSUM_CLIENT,
# ENCRYPTION_TYPES_SERVER, ENCRYPTION_TYPES_CLIENT,
# CRYPTO_CHECKSUM_TYPES_SERVER, CRYPTO_CHECKSUM_TYPES_CLIENT
#
# From: @BrianPardy on Twitter
#
# Known functional on Linux x86-64, may work on Solaris and AIX.
#
# Run this script as the Oracle EM13c software owner, with your environment
# fully up and running.
#
# Thanks to Dave Corsar, who tested a previous version on Solaris and
# let me know the changes needed to make the script work on Solaris.
#
# Thanks to opa tropa who confirmed AIX functionality on a previous
# version and noted the use of GNU extensions to grep, which I have
# since removed.
#
# Thanks to Paige, who informed me of a broken check for the
# SSL_CIPHER_SUITES parameter that led me to add the additional checks
# for SQL*Net encryption
#
# In order to check selections for ENCRYPTION_TYPES and CRYPTO_CHECKSUM_TYPES
# I have to make some judgement calls. Due to MD5's known issues, I consider
# it unacceptable for CRYPTO_CHECKSUM_TYPES. Unfortunately SHA256, the
# best choice available, can cause problems with target promotion in OEM
# (see MOS note 2167682.1) so this check will simply make sure you do not
# permit MD5, but will not enforce SHA256. This same issue also requires
# allowing 3DES168 as an encryption algorithm to promote targets, though
# I would generally not allow 3DES168 for security reasons. This check
# will simply make sure you do not permit DES, DES40, 3DES112, or any
# of the RC4_* algorithms.
#
#
# Dedicated to our two Lhasa Apsos:
# Lucy (6/13/1998 - 3/13/2015)
# Ethel (6/13/1998 - 7/31/2015)
#

SCRIPTNAME=`basename $0`
PATCHDATE="16 Aug 2016"
OMSHOST=`hostname -f`
VERSION="0.8"
FAIL_COUNT=0
FAIL_TESTS=""

RUN_DB_CHECK=0
VERBOSE_CHECKSEC=2

HOST_OS=`uname -s`
HOST_ARCH=`uname -m`

ORAGCHOMELIST="/etc/oragchomelist"
ORATAB="/etc/oratab"
OPENSSL=`which openssl`

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

if [[ ! -r $ORAGCHOMELIST ]]; then # Solaris
ORAGCHOMELIST="/var/opt/oracle/oragchomelist"
fi

if [[ ! -r $ORATAB ]]; then # Solaris
ORATAB="/var/opt/oracle/oratab"
fi

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

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

OPENSSL_PERMIT_FORBID_NON_TLS1_2="Permit"

if [[ $OPENSSL_ALLOW_TLS1_2_ONLY -gt 0 ]]; then
OPENSSL_PERMIT_FORBID_NON_TLS1_2="Forbid"
fi

OMS_HOME=`$GREP -i oms $ORAGCHOMELIST | xargs ls -d 2>/dev/null`

if [[ "$OMS_HOME" == "." ]]; then
OMS_HOME=`cat $ORAGCHOMELIST | head -n 1`
fi

OPATCH="$OMS_HOME/OPatch/opatch"
OPATCHAUTO="$OMS_HOME/OPatch/opatchauto"
OMSPATCHER="$OMS_HOME/OMSPatcher/omspatcher"
OMSORAINST="$OMS_HOME/oraInst.loc"
ORAINVENTORY=`$GREP inventory_loc $OMSORAINST | awk -F= '{print $2}'`

MW_HOME=$OMS_HOME
COMMON_HOME="$MW_HOME/oracle_common"

AGENT_HOME=`$GREP -vi REMOVED $ORAINVENTORY/ContentsXML/inventory.xml | $GREP "HOME NAME=\"agent13c" | awk '{print $3}' | sed -e 's/LOC=\"//' | sed -e 's/"//'`

EM_INSTANCE_BASE=`$GREP GCDomain $MW_HOME/domain-registry.xml | sed -e 's/.*=//' | sed -e 's/\/user_projects.*$//' | sed -e 's/"//'`

EMGC_PROPS="$EM_INSTANCE_BASE/em/EMGC_OMS1/emgc.properties"
EMBIP_PROPS="$EM_INSTANCE_BASE/em/EMGC_OMS1/embip.properties"
#OPMN_PROPS="$EM_INSTANCE_BASE/WebTierIH1/config/OPMN/opmn/ports.prop"
#OHS_ADMIN_CONF="$EM_INSTANCE_BASE/WebTierIH1/config/OHS/ohs1/admin.conf"

PORT_UPL=`$GREP EM_UPLOAD_HTTPS_PORT $EMGC_PROPS | awk -F= '{print $2}'`
PORT_OMS=`$GREP EM_CONSOLE_HTTPS_PORT $EMGC_PROPS | awk -F= '{print $2}'`
PORT_OMS_JAVA=`$GREP MS_HTTPS_PORT $EMGC_PROPS | awk -F= '{print $2}'`
PORT_NODEMANAGER=`$GREP EM_NODEMGR_PORT $EMGC_PROPS | awk -F= '{print $2}'`
PORT_BIP=`$GREP BIP_HTTPS_PORT $EMBIP_PROPS | awk -F= '{print $2}'`
PORT_BIP_OHS=`$GREP BIP_HTTPS_OHS_PORT $EMBIP_PROPS | awk -F= '{print $2}'`
PORT_ADMINSERVER=`$GREP AS_HTTPS_PORT $EMGC_PROPS | awk -F= '{print $2}'`
#PORT_OPMN=`$GREP '/opmn/remote_port' $OPMN_PROPS | awk -F= '{print $2}'`
#PORT_OHS_ADMIN=`$GREP Listen $OHS_ADMIN_CONF | awk '{print $2}'`
PORT_AGENT=`$AGENT_HOME/bin/emctl status agent | $GREP 'Agent URL' | sed -e 's/\/emd\/main\///' | sed -e 's/^.*://' | uniq`

REPOS_DB_CONNDESC=`$GREP EM_REPOS_CONNECTDESCRIPTOR $EMGC_PROPS | sed -e 's/EM_REPOS_CONNECTDESCRIPTOR=//' | sed -e 's/\\\\//g'`
REPOS_DB_HOST=`echo $REPOS_DB_CONNDESC | sed -e 's/^.*HOST=//' | sed -e 's/).*$//'`
REPOS_DB_SID=`echo $REPOS_DB_CONNDESC | sed -e 's/^.*SID=//' | sed -e 's/).*$//'`

if [[ "$REPOS_DB_HOST" == "$OMSHOST" ]]; then
REPOS_DB_HOME=`$GREP "$REPOS_DB_SID:" $ORATAB | awk -F: '{print $2}'`
REPOS_DB_VERSION=`$REPOS_DB_HOME/OPatch/opatch lsinventory -oh $REPOS_DB_HOME | $GREP 'Oracle Database' | awk '{print $4}'`

if [[ "$REPOS_DB_VERSION" == "11.2.0.4.0" ]]; then
RUN_DB_CHECK=1
fi

if [[ "$REPOS_DB_VERSION" == "12.1.0.2.0" ]]; then
RUN_DB_CHECK=1
fi

if [[ "$RUN_DB_CHECK" -eq 0 ]]; then
echo -e "\tSkipping local repository DB patch check, only 11.2.0.4 or 12.1.0.2 supported by this script for now"
fi
fi

sslcheck () {
OPENSSL_CHECK_COMPONENT=$1
OPENSSL_CHECK_HOST=$2
OPENSSL_CHECK_PORT=$3
OPENSSL_CHECK_PROTO=$4
OPENSSL_AVAILABLE_OR_DISABLED="disabled"

if [[ $OPENSSL_CHECK_PROTO == "tls1_1" && $OPENSSL_HAS_TLS1_1 == 0 ]]; then
echo -en "\tYour OpenSSL ($OPENSSL) does not support $OPENSSL_CHECK_PROTO. Skipping $OPENSSL_CHECK_COMPONENT\n"
return
fi

if [[ $OPENSSL_CHECK_PROTO == "tls1_2" && $OPENSSL_HAS_TLS1_2 == 0 ]]; then
echo -en "\tYour OpenSSL ($OPENSSL) does not support $OPENSSL_CHECK_PROTO. Skipping $OPENSSL_CHECK_COMPONENT\n"
return
fi

OPENSSL_RETURN=`echo Q | $OPENSSL s_client -prexit -connect $OPENSSL_CHECK_HOST:$OPENSSL_CHECK_PORT -$OPENSSL_CHECK_PROTO 2>&1 | $GREP Cipher | $GREP -c 0000`

if [[ $OPENSSL_CHECK_PROTO == "tls1" || $OPENSSL_CHECK_PROTO == "tls1_1" || $OPENSSL_CHECK_PROTO == "tls1_2" ]]; then

if [[ $OPENSSL_ALLOW_TLS1_2_ONLY > 0 ]]; then
if [[ $OPENSSL_CHECK_PROTO == "tls1_2" ]]; then
OPENSSL_AVAILABLE_OR_DISABLED="available"
fi
fi

if [[ $OPENSSL_ALLOW_TLS1_2_ONLY == 0 ]]; then
OPENSSL_AVAILABLE_OR_DISABLED="available"
fi

echo -en "\tConfirming $OPENSSL_CHECK_PROTO $OPENSSL_AVAILABLE_OR_DISABLED for $OPENSSL_CHECK_COMPONENT at $OPENSSL_CHECK_HOST:$OPENSSL_CHECK_PORT... "

if [[ $OPENSSL_AVAILABLE_OR_DISABLED == "available" ]]; then
if [[ $OPENSSL_RETURN -eq "0" ]]; then
echo OK
else
echo FAILED
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$OPENSSL_CHECK_COMPONENT @ $OPENSSL_CHECK_HOST:${OPENSSL_CHECK_PORT}:$OPENSSL_CHECK_PROTO protocol connection failed"
fi
fi

if [[ $OPENSSL_AVAILABLE_OR_DISABLED == "disabled" ]]; then
if [[ $OPENSSL_RETURN -ne "0" ]]; then
echo OK
else
echo FAILED
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$OPENSSL_CHECK_COMPONENT @ $OPENSSL_CHECK_HOST:${OPENSSL_CHECK_PORT}:$OPENSSL_CHECK_PROTO protocol connection allowed"
fi
fi

fi

if [[ $OPENSSL_CHECK_PROTO == "ssl2" || $OPENSSL_CHECK_PROTO == "ssl3" ]]; then
echo -en "\tConfirming $OPENSSL_CHECK_PROTO $OPENSSL_AVAILABLE_OR_DISABLED for $OPENSSL_CHECK_COMPONENT at $OPENSSL_CHECK_HOST:$OPENSSL_CHECK_PORT... "
if [[ $OPENSSL_RETURN -ne "0" ]]; then
echo OK
else
echo FAILED
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$OPENSSL_CHECK_COMPONENT @ $OPENSSL_CHECK_HOST:${OPENSSL_CHECK_PORT}:$OPENSSL_CHECK_PROTO protocol connection succeeded"
fi
fi
}

opatchcheck () {
OPATCH_CHECK_COMPONENT=$1
OPATCH_CHECK_OH=$2
OPATCH_CHECK_PATCH=$3

if [[ "$OPATCH_CHECK_COMPONENT" == "ReposDBHome" ]]; then
OPATCH_RET=`$OPATCH_CHECK_OH/OPatch/opatch lsinv -oh $OPATCH_CHECK_OH | $GREP $OPATCH_CHECK_PATCH`
else
OPATCH_RET=`$OPATCH lsinv -oh $OPATCH_CHECK_OH | $GREP $OPATCH_CHECK_PATCH`
fi

if [[ -z "$OPATCH_RET" ]]; then
echo FAILED
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$OPATCH_CHECK_COMPONENT @ ${OPATCH_CHECK_OH}:Patch $OPATCH_CHECK_PATCH not found"
else
echo OK
fi

test $VERBOSE_CHECKSEC -ge 2 && echo $OPATCH_RET

}

opatchautocheck () {
OPATCHAUTO_CHECK_COMPONENT=$1
OPATCHAUTO_CHECK_OH=$2
OPATCHAUTO_CHECK_PATCH=$3

OPATCHAUTO_RET=`$OPATCHAUTO lspatches -oh $OPATCHAUTO_CHECK_OH | $GREP $OPATCHAUTO_CHECK_PATCH`

if [[ -z "$OPATCHAUTO_RET" ]]; then
echo FAILED
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$OPATCHAUTO_CHECK_COMPONENT @ ${OPATCHAUTO_CHECK_OH}:Patch $OPATCHAUTO_CHECK_PATCH not found"
else
echo OK
fi

test $VERBOSE_CHECKSEC -ge 2 && echo $OPATCHAUTO_RET

}

omspatchercheck () {
OMSPATCHER_CHECK_COMPONENT=$1
OMSPATCHER_CHECK_OH=$2
OMSPATCHER_CHECK_PATCH=$3

OMSPATCHER_RET=`$OMSPATCHER lspatches -oh $OMSPATCHER_CHECK_OH | $GREP $OMSPATCHER_CHECK_PATCH`

if [[ -z "$OMSPATCHER_RET" ]]; then
echo FAILED
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$OMSPATCHER_CHECK_COMPONENT @ ${OMSPATCHER_CHECK_OH}:Patch $OMSPATCHER_CHECK_PATCH not found"
else
echo OK
fi

test $VERBOSE_CHECKSEC -ge 2 && echo $OMSPATCHER_RET

}

certcheck () {
CERTCHECK_CHECK_COMPONENT=$1
CERTCHECK_CHECK_HOST=$2
CERTCHECK_CHECK_PORT=$3

echo -ne "\tChecking certificate at $CERTCHECK_CHECK_COMPONENT ($CERTCHECK_CHECK_HOST:$CERTCHECK_CHECK_PORT)... "

OPENSSL_SELFSIGNED_COUNT=`echo Q | $OPENSSL s_client -prexit -connect $CERTCHECK_CHECK_HOST:$CERTCHECK_CHECK_PORT -tls1 2>&1 | $GREP -ci "self signed certificate"`

if [[ $OPENSSL_SELFSIGNED_COUNT -eq "0" ]]; then
echo OK
else
echo FAILED - Found self-signed certificate
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$CERTCHECK_CHECK_COMPONENT @ ${CERTCHECK_CHECK_HOST}:${CERTCHECK_CHECK_PORT} found self-signed certificate"
fi
}

democertcheck () {
DEMOCERTCHECK_CHECK_COMPONENT=$1
DEMOCERTCHECK_CHECK_HOST=$2
DEMOCERTCHECK_CHECK_PORT=$3

echo -ne "\tChecking demo certificate at $DEMOCERTCHECK_CHECK_COMPONENT ($DEMOCERTCHECK_CHECK_HOST:$DEMOCERTCHECK_CHECK_PORT)... "

OPENSSL_DEMO_COUNT=`echo Q | $OPENSSL s_client -prexit -connect $DEMOCERTCHECK_CHECK_HOST:$DEMOCERTCHECK_CHECK_PORT -tls1 2>&1 | $GREP -ci "issuer=/C=US/ST=MyState/L=MyTown/O=MyOrganization/OU=FOR TESTING ONLY/CN"`

if [[ $OPENSSL_DEMO_COUNT -eq "0" ]]; then
echo OK
else
echo FAILED - Found demonstration certificate
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$DEMOCERTCHECK_CHECK_COMPONENT @ ${DEMOCERTCHECK_CHECK_HOST}:${DEMOCERTCHECK_CHECK_PORT} found demonstration certificate"
fi
}

ciphercheck () {
OPENSSL_CHECK_COMPONENT=$1
OPENSSL_CHECK_HOST=$2
OPENSSL_CHECK_PORT=$3

echo -ne "\tChecking LOW strength ciphers on $OPENSSL_CHECK_COMPONENT ($OPENSSL_CHECK_HOST:$OPENSSL_CHECK_PORT)..."

OPENSSL_LOW_RETURN=`echo Q | $OPENSSL s_client -prexit -connect $OPENSSL_CHECK_HOST:$OPENSSL_CHECK_PORT -tls1 -cipher LOW 2>&1 | $GREP Cipher | uniq | $GREP -c 0000`

if [[ $OPENSSL_LOW_RETURN -eq "0" ]]; then
echo -e "\tFAILED - PERMITS LOW STRENGTH CIPHER CONNECTIONS"
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$OPENSSL_CHECK_COMPONENT @ $OPENSSL_CHECK_HOST:${OPENSSL_CHECK_PORT}:Permits LOW strength ciphers"
else
echo -e "\tOK"
fi

echo -ne "\tChecking MEDIUM strength ciphers on $OPENSSL_CHECK_COMPONENT ($OPENSSL_CHECK_HOST:$OPENSSL_CHECK_PORT)..."

OPENSSL_MEDIUM_RETURN=`echo Q | $OPENSSL s_client -prexit -connect $OPENSSL_CHECK_HOST:$OPENSSL_CHECK_PORT -tls1 -cipher MEDIUM 2>&1 | $GREP Cipher | uniq | $GREP -c 0000`

if [[ $OPENSSL_MEDIUM_RETURN -eq "0" ]]; then
echo -e "\tFAILED - PERMITS MEDIUM STRENGTH CIPHER CONNECTIONS"
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$OPENSSL_CHECK_COMPONENT @ $OPENSSL_CHECK_HOST:${OPENSSL_CHECK_PORT}:Permits MEDIUM strength ciphers"
else
echo -e "\tOK"
fi

echo -ne "\tChecking HIGH strength ciphers on $OPENSSL_CHECK_COMPONENT ($OPENSSL_CHECK_HOST:$OPENSSL_CHECK_PORT)..."

OPENSSL_HIGH_RETURN=`echo Q | $OPENSSL s_client -prexit -connect $OPENSSL_CHECK_HOST:$OPENSSL_CHECK_PORT -tls1 -cipher HIGH 2>&1 | $GREP Cipher | uniq | $GREP -c 0000`

if [[ $OPENSSL_HIGH_RETURN -eq "0" ]]; then
echo -e "\tOK"
else
echo -e "\tFAILED - CANNOT CONNECT WITH HIGH STRENGTH CIPHER"
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$OPENSSL_CHECK_COMPONENT @ $OPENSSL_CHECK_HOST:${OPENSSL_CHECK_PORT}:Rejects HIGH strength ciphers"
fi
echo
}

wlspatchcheck () {
WLSDIR=$1
WLSPATCH=$2

WLSCHECK_RETURN=`( cd $MW_HOME/utils/bsu && $MW_HOME/utils/bsu/bsu.sh -report ) | $GREP $WLSPATCH`
WLSCHECK_COUNT=`echo $WLSCHECK_RETURN | wc -l`

if [[ $WLSCHECK_COUNT -ge "1" ]]; then
echo -e "\tOK"
else
echo -e "\tFAILED - PATCH NOT FOUND"
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$WLSDIR:Patch $WLSPATCH not found"
fi

test $VERBOSE_CHECKSEC -ge 2 && echo $WLSCHECK_RETURN

}

javacheck () {
WHICH_JAVA=$1
JAVA_DIR=$2

JAVACHECK_RETURN=`$JAVA_DIR/bin/java -version 2>&1 | $GREP version | awk '{print $3}' | sed -e 's/"//g'`

if [[ "$JAVACHECK_RETURN" == "1.7.0_111" ]]; then
echo -e "\tOK"
else
#echo -e "\tFAILED - Found version $JAVACHECK_RETURN"
echo -e "\tFAILED"
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$WHICH_JAVA Java in ${JAVA_DIR}:Found incorrect version $JAVACHECK_RETURN"
fi
test $VERBOSE_CHECKSEC -ge 2 && echo $JAVACHECK_RETURN
}

paramcheck () {
WHICH_PARAM=$1
WHICH_ORACLE_HOME=$2
WHICH_FILE=$3

PARAMCHECK_PARAM_FOUND=`$GREP $WHICH_PARAM $WHICH_ORACLE_HOME/network/admin/$WHICH_FILE | $GREP -v '^#' | wc -l`

if [[ $PARAMCHECK_PARAM_FOUND == "0" ]]; then
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$WHICH_PARAM in $WHICH_FILE for home ${WHICH_ORACLE_HOME}:parameter not found"
return
fi

PARAMCHECK_RETURN=`$GREP $WHICH_PARAM $WHICH_ORACLE_HOME/network/admin/$WHICH_FILE | $GREP -v '^#' | awk -F= '{print $2}' | sed -e 's/\s//g'`
if [[ "$WHICH_PARAM" == "SSL_VERSION" ]]; then
if [[ "$PARAMCHECK_RETURN" == "1.0" ]]; then
echo -e "OK"
else
echo -e "FAILED - Found $WHICH_PARAM = $PARAMCHECK_RETURN"
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$WHICH_PARAM in $WHICH_FILE for home ${WHICH_ORACLE_HOME}:incorrect parameter value"
fi
test $VERBOSE_CHECKSEC -ge 2 && echo $PARAMCHECK_RETURN
fi

if [[ "$WHICH_PARAM" == "SSL_CIPHER_SUITES" ]]; then
if [[ "$PARAMCHECK_RETURN" == "(SSL_RSA_WITH_AES_128_CBC_SHA,SSL_RSA_WITH_AES_256_CBC_SHA)" ]]; then
echo -e "OK"
else
echo -e "FAILED - Found $WHICH_PARAM = $PARAMCHECK_RETURN"
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$WHICH_PARAM in $WHICH_FILE for home ${WHICH_ORACLE_HOME}:incorrect parameter value"
fi
test $VERBOSE_CHECKSEC -ge 2 && echo $PARAMCHECK_RETURN
fi

if [[ "$WHICH_PARAM" == "SQLNET.ENCRYPTION_SERVER" ]]; then
echo $PARAMCHECK_RETURN | $GREP -iE '(requested|required)' >& /dev/null
PARAM_STATE=$?

if [[ $PARAM_STATE == "0" ]]; then
echo -e "OK"
else
echo -e "FAILED - Found $WHICH_PARAM = $PARAMCHECK_RETURN"
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$WHICH_PARAM in $WHICH_FILE for home ${WHICH_ORACLE_HOME}:incorrect parameter value"
fi
test $VERBOSE_CHECKSEC -ge 2 && echo $PARAMCHECK_RETURN
fi

if [[ "$WHICH_PARAM" == "SQLNET.ENCRYPTION_CLIENT" ]]; then
echo $PARAMCHECK_RETURN | $GREP -iE '(requested|required)' >& /dev/null
PARAM_STATE=$?

if [[ $PARAM_STATE == "0" ]]; then
echo -e "OK"
else
echo -e "FAILED - Found $WHICH_PARAM = $PARAMCHECK_RETURN"
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$WHICH_PARAM in $WHICH_FILE for home ${WHICH_ORACLE_HOME}:incorrect parameter value"
fi
test $VERBOSE_CHECKSEC -ge 2 && echo $PARAMCHECK_RETURN
fi

if [[ "$WHICH_PARAM" == "SQLNET.CRYPTO_CHECKSUM_SERVER" ]]; then
echo $PARAMCHECK_RETURN | $GREP -iE '(requested|required)' >& /dev/null
PARAM_STATE=$?

if [[ $PARAM_STATE == "0" ]]; then
echo -e "OK"
else
echo -e "FAILED - Found $WHICH_PARAM = $PARAMCHECK_RETURN"
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$WHICH_PARAM in $WHICH_FILE for home ${WHICH_ORACLE_HOME}:incorrect parameter value"
fi
test $VERBOSE_CHECKSEC -ge 2 && echo $PARAMCHECK_RETURN
fi

if [[ "$WHICH_PARAM" == "SQLNET.CRYPTO_CHECKSUM_CLIENT" ]]; then
echo $PARAMCHECK_RETURN | $GREP -iE '(requested|required)' >& /dev/null
PARAM_STATE=$?

if [[ $PARAM_STATE == "0" ]]; then
echo -e "OK"
else
echo -e "FAILED - Found $WHICH_PARAM = $PARAMCHECK_RETURN"
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$WHICH_PARAM in $WHICH_FILE for home ${WHICH_ORACLE_HOME}:incorrect parameter value"
fi
test $VERBOSE_CHECKSEC -ge 2 && echo $PARAMCHECK_RETURN
fi

if [[ "$WHICH_PARAM" == "SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER" ]]; then
echo $PARAMCHECK_RETURN | $GREP -iE 'MD5' >& /dev/null
PARAM_STATE=$?

if [[ $PARAM_STATE == "1" ]]; then
echo -e "OK"
else
echo -e "FAILED - Found $WHICH_PARAM = $PARAMCHECK_RETURN"
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$WHICH_PARAM in $WHICH_FILE for home ${WHICH_ORACLE_HOME}:incorrect parameter value (do not use MD5, only use SHA1 and/or SHA256)"
fi
test $VERBOSE_CHECKSEC -ge 2 && echo $PARAMCHECK_RETURN
fi

if [[ "$WHICH_PARAM" == "SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT" ]]; then
echo $PARAMCHECK_RETURN | $GREP -iE 'MD5' >& /dev/null
PARAM_STATE=$?

if [[ $PARAM_STATE == "1" ]]; then
echo -e "OK"
else
echo -e "FAILED - Found $WHICH_PARAM = $PARAMCHECK_RETURN"
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$WHICH_PARAM in $WHICH_FILE for home ${WHICH_ORACLE_HOME}:incorrect parameter value (do not use MD5, only use SHA1 and/or SHA256)"
fi
test $VERBOSE_CHECKSEC -ge 2 && echo $PARAMCHECK_RETURN
fi

if [[ "$WHICH_PARAM" == "SQLNET.ENCRYPTION_TYPES_SERVER" ]]; then
echo $PARAMCHECK_RETURN | $GREP -iE '([(,]des[),]|3des112|rc4|des40)' >& /dev/null
PARAM_STATE=$?

if [[ $PARAM_STATE == "1" ]]; then
echo -e "OK"
else
echo -e "FAILED - Found $WHICH_PARAM = $PARAMCHECK_RETURN"
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$WHICH_PARAM in $WHICH_FILE for home ${WHICH_ORACLE_HOME}:incorrect parameter value (do not use DES, DES40, RC4_40, RC4_56, RC4_128, RC4_256, or 3DES112)"
fi
test $VERBOSE_CHECKSEC -ge 2 && echo $PARAMCHECK_RETURN
fi

if [[ "$WHICH_PARAM" == "SQLNET.ENCRYPTION_TYPES_CLIENT" ]]; then
echo $PARAMCHECK_RETURN | $GREP -iE '([(,]des[),]|3des112|rc4|des40)' >& /dev/null
PARAM_STATE=$?

if [[ $PARAM_STATE == "1" ]]; then
echo -e "OK"
else
echo -e "FAILED - Found $WHICH_PARAM = $PARAMCHECK_RETURN"
FAIL_COUNT=$((FAIL_COUNT+1))
FAIL_TESTS="${FAIL_TESTS}\\n$FUNCNAME:$WHICH_PARAM in $WHICH_FILE for home ${WHICH_ORACLE_HOME}:incorrect parameter value (do not use DES, DES40, RC4_40, RC4_56, RC4_128, RC4_256, or 3DES112)"
fi
test $VERBOSE_CHECKSEC -ge 2 && echo $PARAMCHECK_RETURN
fi
}

### MAIN SCRIPT HERE

echo -e "Performing EM13c security checkup version $VERSION on $OMSHOST at `date`.\n"

echo "Using port definitions from configuration files "
echo -e "\t/etc/oragchomelist"
echo -e "\t$EMGC_PROPS"
echo -e "\t$EMBIP_PROPS"
echo
echo -e "\tAgent port found at $OMSHOST:$PORT_AGENT"
echo -e "\tBIPublisher port found at $OMSHOST:$PORT_BIP"
echo -e "\tBIPublisherOHS port found at $OMSHOST:$PORT_BIP_OHS"
echo -e "\tNodeManager port found at $OMSHOST:$PORT_NODEMANAGER"
echo -e "\tOMSconsole port found at $OMSHOST:$PORT_OMS"
echo -e "\tOMSproxy port found at $OMSHOST:$PORT_OMS_JAVA"
echo -e "\tOMSupload port found at $OMSHOST:$PORT_UPL"
echo -e "\tWLSadmin found at $OMSHOST:$PORT_ADMINSERVER"
echo
echo -e "\tRepository DB version=$REPOS_DB_VERSION SID=$REPOS_DB_SID host=$REPOS_DB_HOST"

if [[ $RUN_DB_CHECK -eq "1" ]]; then
echo -e "\tRepository DB on OMS server, will check patches/parameters in $REPOS_DB_HOME"
fi

echo -e "\n(1) Checking SSL/TLS configuration (see notes 1602983.1, 1477287.1, 1905314.1)"

echo -e "\n\t(1a) Forbid SSLv2 connections"
sslcheck Agent $OMSHOST $PORT_AGENT ssl2
sslcheck BIPublisher $OMSHOST $PORT_BIP ssl2
sslcheck NodeManager $OMSHOST $PORT_NODEMANAGER ssl2
sslcheck BIPublisherOHS $OMSHOST $PORT_BIP_OHS ssl2
sslcheck OMSconsole $OMSHOST $PORT_OMS ssl2
sslcheck OMSproxy $OMSHOST $PORT_OMS_JAVA ssl2
sslcheck OMSupload $OMSHOST $PORT_UPL ssl2
#sslcheck OPMN $OMSHOST $PORT_OPMN ssl2
sslcheck WLSadmin $OMSHOST $PORT_ADMINSERVER ssl2

echo -e "\n\t(1b) Forbid SSLv3 connections"
sslcheck Agent $OMSHOST $PORT_AGENT ssl3
sslcheck BIPublisher $OMSHOST $PORT_BIP ssl3
sslcheck NodeManager $OMSHOST $PORT_NODEMANAGER ssl3
sslcheck BIPublisherOHS $OMSHOST $PORT_BIP_OHS ssl3
sslcheck OMSconsole $OMSHOST $PORT_OMS ssl3
sslcheck OMSproxy $OMSHOST $PORT_OMS_JAVA ssl3
sslcheck OMSupload $OMSHOST $PORT_UPL ssl3
#sslcheck OPMN $OMSHOST $PORT_OPMN ssl3
sslcheck WLSadmin $OMSHOST $PORT_ADMINSERVER ssl3

echo -e "\n\t(1c) $OPENSSL_PERMIT_FORBID_NON_TLS1_2 TLSv1 connections"
sslcheck Agent $OMSHOST $PORT_AGENT tls1
sslcheck BIPublisher $OMSHOST $PORT_BIP tls1
sslcheck NodeManager $OMSHOST $PORT_NODEMANAGER tls1
sslcheck BIPublisherOHS $OMSHOST $PORT_BIP_OHS tls1
sslcheck OMSconsole $OMSHOST $PORT_OMS tls1
sslcheck OMSproxy $OMSHOST $PORT_OMS_JAVA tls1
sslcheck OMSupload $OMSHOST $PORT_UPL tls1
#sslcheck OPMN $OMSHOST $PORT_OPMN tls1
sslcheck WLSadmin $OMSHOST $PORT_ADMINSERVER tls1

echo -e "\n\t(1c) $OPENSSL_PERMIT_FORBID_NON_TLS1_2 TLSv1.1 connections"
sslcheck Agent $OMSHOST $PORT_AGENT tls1_1
sslcheck BIPublisher $OMSHOST $PORT_BIP tls1_1
sslcheck NodeManager $OMSHOST $PORT_NODEMANAGER tls1_1
sslcheck BIPublisherOHS $OMSHOST $PORT_BIP_OHS tls1_1
sslcheck OMSconsole $OMSHOST $PORT_OMS tls1_1
sslcheck OMSproxy $OMSHOST $PORT_OMS_JAVA tls1_1
sslcheck OMSupload $OMSHOST $PORT_UPL tls1_1
#sslcheck OPMN $OMSHOST $PORT_OPMN tls1
sslcheck WLSadmin $OMSHOST $PORT_ADMINSERVER tls1_1

echo -e "\n\t(1c) Permit TLSv1.2 connections"
sslcheck Agent $OMSHOST $PORT_AGENT tls1_2
sslcheck BIPublisher $OMSHOST $PORT_BIP tls1_2
sslcheck NodeManager $OMSHOST $PORT_NODEMANAGER tls1_2
sslcheck BIPublisherOHS $OMSHOST $PORT_BIP_OHS tls1_2
sslcheck OMSconsole $OMSHOST $PORT_OMS tls1_2
sslcheck OMSproxy $OMSHOST $PORT_OMS_JAVA tls1_2
sslcheck OMSupload $OMSHOST $PORT_UPL tls1_2
#sslcheck OPMN $OMSHOST $PORT_OPMN tls1
sslcheck WLSadmin $OMSHOST $PORT_ADMINSERVER tls1_2

echo -e "\n(2) Checking supported ciphers at SSL/TLS endpoints (see notes 2138391.1, 1067411.1)"
ciphercheck Agent $OMSHOST $PORT_AGENT
ciphercheck BIPublisher $OMSHOST $PORT_BIP
ciphercheck NodeManager $OMSHOST $PORT_NODEMANAGER
ciphercheck BIPublisherOHS $OMSHOST $PORT_BIP_OHS
ciphercheck OMSconsole $OMSHOST $PORT_OMS
ciphercheck OMSproxy $OMSHOST $PORT_OMS_JAVA
ciphercheck OMSupload $OMSHOST $PORT_UPL
#ciphercheck OPMN $OMSHOST $PORT_OPMN
ciphercheck WLSadmin $OMSHOST $PORT_ADMINSERVER

echo -e "\n(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)"
certcheck Agent $OMSHOST $PORT_AGENT
democertcheck Agent $OMSHOST $PORT_AGENT
certcheck BIPublisher $OMSHOST $PORT_BIP
democertcheck BIPublisher $OMSHOST $PORT_BIP
certcheck NodeManager $OMSHOST $PORT_NODEMANAGER
democertcheck NodeManager $OMSHOST $PORT_NODEMANAGER
certcheck BIPublisherOHS $OMSHOST $PORT_BIP_OHS
democertcheck BIPublisherOHS $OMSHOST $PORT_BIP_OHS
certcheck OMSconsole $OMSHOST $PORT_OMS
democertcheck OMSconsole $OMSHOST $PORT_OMS
certcheck OMSproxy $OMSHOST $PORT_OMS_JAVA
democertcheck OMSproxy $OMSHOST $PORT_OMS_JAVA
certcheck OMSupload $OMSHOST $PORT_UPL
democertcheck OMSupload $OMSHOST $PORT_UPL
#certcheck OPMN $OMSHOST $PORT_OPMN
#democertcheck OPMN $OMSHOST $PORT_OPMN
certcheck WLSadmin $OMSHOST $PORT_ADMINSERVER
democertcheck WLSadmin $OMSHOST $PORT_ADMINSERVER

echo -e "\n(4) Checking EM13c Oracle home patch levels against $PATCHDATE baseline (see notes 1664074.1, 1900943.1, 822485.1, 1470197.1, 1967243.1)"

if [[ $RUN_DB_CHECK -eq 1 ]]; then

if [[ "$REPOS_DB_VERSION" == "12.1.0.2.0" ]]; then
#echo -ne "\n\t(4a) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) PSU 12.1.0.2.160119 (JAN2016) (21948354)... "
#opatchcheck ReposDBHome $REPOS_DB_HOME 21948354

#echo -ne "\n\t(4a) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) PSU 12.1.0.2.160419 (APR2016) (22291127)... "
#opatchcheck ReposDBHome $REPOS_DB_HOME 22291127

echo -ne "\n\t(4a) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) PSU 12.1.0.2.160719 (JUL2016) (23054246)... "
opatchcheck ReposDBHome $REPOS_DB_HOME 23054246

#echo -ne "\n\t(4a) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) ORACLE JAVAVM COMPONENT 12.1.0.2.160119 DATABASE PSU (JAN2016) (22139226)... "
#opatchcheck ReposDBHome $REPOS_DB_HOME 22139226

#echo -ne "\n\t(4a) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) ORACLE JAVAVM COMPONENT 12.1.0.2.160419 DATABASE PSU (APR2016) (22674709)... "
#opatchcheck ReposDBHome $REPOS_DB_HOME 22674709

echo -ne "\n\t(4a) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) ORACLE JAVAVM COMPONENT 12.1.0.2.160719 DATABASE PSU (JUL2016) (23177536)... "
opatchcheck ReposDBHome $REPOS_DB_HOME 23177536
fi

echo -ne "\n\t(4b) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) sqlnet.ora SQLNET.ENCRYPTION_TYPES_SERVER parameter (76629.1, 2167682.1)... "
paramcheck SQLNET.ENCRYPTION_TYPES_SERVER $REPOS_DB_HOME sqlnet.ora

echo -ne "\n\t(4b) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) sqlnet.ora SQLNET.ENCRYPTION_SERVER parameter (76629.1, 2167682.1)... "
paramcheck SQLNET.ENCRYPTION_SERVER $REPOS_DB_HOME sqlnet.ora

echo -ne "\n\t(4b) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) sqlnet.ora SQLNET.ENCRYPTION_TYPES_CLIENT parameter (76629.1, 2167682.1)... "
paramcheck SQLNET.ENCRYPTION_TYPES_CLIENT $REPOS_DB_HOME sqlnet.ora

echo -ne "\n\t(4b) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) sqlnet.ora SQLNET.ENCRYPTION_CLIENT parameter (76629.1, 2167682.1)... "
paramcheck SQLNET.ENCRYPTION_CLIENT $REPOS_DB_HOME sqlnet.ora

echo -ne "\n\t(4b) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) sqlnet.ora SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER parameter (76629.1, 2167682.1)... "
paramcheck SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER $REPOS_DB_HOME sqlnet.ora

echo -ne "\n\t(4b) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) sqlnet.ora SQLNET.CRYPTO_CHECKSUM_SERVER parameter (76629.1, 2167682.1)... "
paramcheck SQLNET.CRYPTO_CHECKSUM_SERVER $REPOS_DB_HOME sqlnet.ora

echo -ne "\n\t(4b) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) sqlnet.ora SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT parameter (76629.1, 2167682.1)... "
paramcheck SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT $REPOS_DB_HOME sqlnet.ora

echo -ne "\n\t(4b) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) sqlnet.ora SQLNET.CRYPTO_CHECKSUM_CLIENT parameter (76629.1, 2167682.1)... "
paramcheck SQLNET.CRYPTO_CHECKSUM_CLIENT $REPOS_DB_HOME sqlnet.ora

echo -ne "\n\t(4b) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) sqlnet.ora SSL_VERSION parameter (1545816.1)... "
paramcheck SSL_VERSION $REPOS_DB_HOME sqlnet.ora

echo -ne "\n\t(4b) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) sqlnet.ora SSL_CIPHER_SUITES parameter (1545816.1)... "
paramcheck SSL_CIPHER_SUITES $REPOS_DB_HOME sqlnet.ora

echo -ne "\n\t(4b) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) listener.ora SSL_VERSION parameter (1545816.1)... "
paramcheck SSL_VERSION $REPOS_DB_HOME listener.ora

echo -ne "\n\t(4b) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) listener.ora SSL_CIPHER_SUITES parameter (1545816.1)... "
paramcheck SSL_CIPHER_SUITES $REPOS_DB_HOME listener.ora
fi

#echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM-AGENT BUNDLE PATCH 13.1.0.0.160331 (22823268)... "
#opatchcheck Agent $AGENT_HOME 22823268

#echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM-AGENT BUNDLE PATCH 13.1.0.0.160429 (23030165)... "
#opatchcheck Agent $AGENT_HOME 23030165

#echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM-AGENT BUNDLE PATCH 13.1.0.0.160531 (23208577)... "
#opatchcheck Agent $AGENT_HOME 23208577

#echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM-AGENT BUNDLE PATCH 13.1.0.0.160719 (23592254)... "
#opatchcheck Agent $AGENT_HOME 23592254

echo -ne "\n\t(4c) *UPDATED* OMS CHAINED AGENT HOME ($AGENT_HOME) EM-AGENT BUNDLE PATCH 13.1.0.0.160816 (24308442)... "
opatchcheck Agent $AGENT_HOME 24308442

#echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM DB PLUGIN BUNDLE PATCH 13.1.1.0.160331 MONITORING (22920712)... "
#opatchcheck Agent $AGENT_HOME 22920712

#echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM DB PLUGIN BUNDLE PATCH 13.1.1.0.160429 MONITORING (23095221)... "
#opatchcheck Agent $AGENT_HOME 23095221

#echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM DB PLUGIN BUNDLE PATCH 13.1.1.0.160531 MONITORING (23294830)... "
#opatchcheck Agent $AGENT_HOME 23294830

#echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM DB PLUGIN BUNDLE PATCH 13.1.1.0.160719 MONITORING (23697777)... "
#opatchcheck Agent $AGENT_HOME 23697777

echo -ne "\n\t(4c) *UPDATED* OMS CHAINED AGENT HOME ($AGENT_HOME) EM DB PLUGIN BUNDLE PATCH 13.1.1.0.160816 MONITORING (24364560)... "
opatchcheck Agent $AGENT_HOME 24364560

echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM DB PLUGIN BUNDLE PATCH 13.1.1.0.160531 DISCOVERY (23294839)... "
opatchcheck Agent $AGENT_HOME 23294839

#echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM FMW PLUGIN BUNDLE PATCH 13.1.1.0.160331 MONITORING (22936491)... "
#opatchcheck Agent $AGENT_HOME 22936491

#echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM FMW PLUGIN BUNDLE PATCH 13.1.1.0.160429 MONITORING (23095280)... "
#opatchcheck Agent $AGENT_HOME 23095280

#echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM FMW PLUGIN BUNDLE PATCH 13.1.1.0.160531 MONITORING (23294872)... "
#opatchcheck Agent $AGENT_HOME 23294872

echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM FMW PLUGIN BUNDLE PATCH 13.1.1.0.160719 MONITORING (23697781)... "
opatchcheck Agent $AGENT_HOME 23697781

#echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM SI PLUGIN BUNDLE PATCH 13.1.1.0.160331 MONITORING (22823189)... "
#opatchcheck Agent $AGENT_HOME 22823189

#echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM SI PLUGIN BUNDLE PATCH 13.1.1.0.160531 MONITORING (23208587)... "
#opatchcheck Agent $AGENT_HOME 23208587

echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM SI PLUGIN BUNDLE PATCH 13.1.1.0.160719 MONITORING (23697783)... "
opatchcheck Agent $AGENT_HOME 23697783

echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM SI PLUGIN BUNDLE PATCH 13.1.1.0.160531 DISCOVERY (23294895)... "
opatchcheck Agent $AGENT_HOME 23294895

#echo -ne "\n\t(4d) OMS HOME ($OMS_HOME) ENTERPRISE MANAGER FOR OMS PLUGINS 13.1.1.0.160331 (22920724)... "
#omspatchercheck OMS $OMS_HOME 22920724

echo -ne "\n\t(4c) OMS CHAINED AGENT HOME ($AGENT_HOME) EM OH PLUGIN BUNDLE PATCH 13.1.1.0.160429 (23135564)... "
opatchcheck Agent $AGENT_HOME 23135564

#echo -ne "\n\t(4d) OMS HOME ($OMS_HOME) ENTERPRISE MANAGER FOR OMS PLUGINS 13.1.1.0.160429 (23095307)... "
#omspatchercheck OMS $OMS_HOME 23095307

#echo -ne "\n\t(4d) OMS HOME ($OMS_HOME) ENTERPRISE MANAGER FOR OMS PLUGINS 13.1.1.0.160531 (23294904)... "
#omspatchercheck OMS $OMS_HOME 23294904

#echo -ne "\n\t(4d) OMS HOME ($OMS_HOME) ENTERPRISE MANAGER FOR OMS PLUGINS 13.1.1.0.160719 (23697785)... "
#omspatchercheck OMS $OMS_HOME 23697785

echo -ne "\n\t(4d) *UPDATED* OMS HOME ($OMS_HOME) ENTERPRISE MANAGER FOR OMS PLUGINS 13.1.1.0.160816 (24364619)... "
omspatchercheck OMS $OMS_HOME 24364619

#echo -ne "\n\t(4e) ($MW_HOME) WLS PATCH SET UPDATE 12.1.3.0.160419 (22505404)... "
#opatchcheck WLS $MW_HOME 22505404

echo -ne "\n\t(4d) ($MW_HOME) WLS PATCH SET UPDATE 12.1.3.0.160719 (23094292)... "
opatchcheck WLS $MW_HOME 23094292

echo -ne "\n\t(4f) OMS HOME ($OMS_HOME) ENTERPRISE MANAGER BASE PLATFORM PATCH SET UPDATE 13.1.0.0.160719 (23134365)... "
omspatchercheck OMS $MW_HOME 23134365

echo -e "\n(5) Checking EM13c Java patch levels against $PATCHDATE baseline (see notes 1492980.1, 1616397.1)"

echo -ne "\n\t(5a) WLS ($MW_HOME/oracle_common/jdk) JAVA SE JDK VERSION 1.7.0-111 (13079846)... "
javacheck WLSJAVA $MW_HOME/oracle_common/jdk

#echo -ne "\n\t(5a) *NEW* OMS CHAINED AGENT HOME ($AGENT_HOME/oracle_common/jdk) JAVA SE JDK VERSION 1.7.0-111 (13079846)... "
#javacheck AGTJAVA $AGENT_HOME/oracle_common/jdk

echo
echo

if [[ $FAIL_COUNT -gt "0" ]]; then
echo "Failed test count: $FAIL_COUNT - Review output"
test $VERBOSE_CHECKSEC -ge 1 && echo -e $FAIL_TESTS
else
echo "All tests succeeded."
fi

echo
echo "Visit https://pardydba.wordpress.com/2016/04/05/securing-oracle-enterprise-manager-13c/ for the latest version."
echo

exit

EM13c TLS Security Checkup Script Sample Output


Performing EM13c security checkup version 0.8 on omshost.domain.com at Wed Sep 14 12:28:32 EDT 2016.

Using port definitions from configuration files
/etc/oragchomelist
/oracle/oem/gc_inst/em/EMGC_OMS1/emgc.properties
/oracle/oem/gc_inst/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:9851
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
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:9851... 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:9851... 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... FAILED
Confirming tls1 disabled for BIPublisher at omshost.domain.com:9803... FAILED
Confirming tls1 disabled for NodeManager at omshost.domain.com:7403... FAILED
Confirming tls1 disabled for BIPublisherOHS at omshost.domain.com:9851... FAILED
Confirming tls1 disabled for OMSconsole at omshost.domain.com:7802... FAILED
Confirming tls1 disabled for OMSproxy at omshost.domain.com:7301... FAILED
Confirming tls1 disabled for OMSupload at omshost.domain.com:4903... FAILED
Confirming tls1 disabled for WLSadmin at omshost.domain.com:7102... FAILED

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

(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:9851... 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)... 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)... 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)... 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:9851)... OK
Checking MEDIUM strength ciphers on BIPublisherOHS (omshost.domain.com:9851)... OK
Checking HIGH strength ciphers on BIPublisherOHS (omshost.domain.com:9851)... OK

Checking LOW strength ciphers on OMSconsole (omshost.domain.com:7802)... 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)... 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)... 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)... 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)... FAILED - Found self-signed certificate
Checking demo certificate at Agent (omshost.domain.com:3872)... OK
Checking certificate at BIPublisher (omshost.domain.com:9803)... OK
Checking demo certificate at BIPublisher (omshost.domain.com:9803)... OK
Checking certificate at NodeManager (omshost.domain.com:7403)... OK
Checking demo certificate at NodeManager (omshost.domain.com:7403)... OK
Checking certificate at BIPublisherOHS (omshost.domain.com:9851)... OK
Checking demo certificate at BIPublisherOHS (omshost.domain.com:9851)... OK
Checking certificate at OMSconsole (omshost.domain.com:7802)... OK
Checking demo certificate at OMSconsole (omshost.domain.com:7802)... OK
Checking certificate at OMSproxy (omshost.domain.com:7301)... OK
Checking demo certificate at OMSproxy (omshost.domain.com:7301)... OK
Checking certificate at OMSupload (omshost.domain.com:4903)... OK
Checking demo certificate at OMSupload (omshost.domain.com:4903)... OK
Checking certificate at WLSadmin (omshost.domain.com:7102)... OK
Checking demo certificate at WLSadmin (omshost.domain.com:7102)... OK

(4) Checking EM13c Oracle home patch levels against 16 Aug 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) PSU 12.1.0.2.160719 (JUL2016) (23054246)... OK
Patch 23054246 : applied on Wed Jul 20 12:01:53 EDT 2016 Patch description: "Database Patch Set Update : 12.1.0.2.160719 (23054246)"

(4a) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) ORACLE JAVAVM COMPONENT 12.1.0.2.160719 DATABASE PSU (JUL2016) (23177536)... OK
Patch 23177536 : applied on Wed Jul 20 12:03:14 EDT 2016 21566993, 22670413, 19699946, 23177536, 22118835, 22118851, 19895326

(4b) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/12.1.0/db) sqlnet.ora SQLNET.ENCRYPTION_TYPES_SERVER parameter (76629.1, 2167682.1)... OK
(AES256,AES128,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
(AES256,AES128,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
(SHA256,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
(SHA256,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) *UPDATED* OMS CHAINED AGENT HOME (/oracle/oem/agent13cR1/agent_13.1.0.0.0) EM-AGENT BUNDLE PATCH 13.1.0.0.160816 (24308442)... OK
Patch 24308442 : applied on Fri Aug 19 10:08:43 EDT 2016 22384903, 22046370, 22304248, 24308442, 23079593, 23030165, 22823268

(4c) *UPDATED* OMS CHAINED AGENT HOME (/oracle/oem/agent13cR1/agent_13.1.0.0.0) EM DB PLUGIN BUNDLE PATCH 13.1.1.0.160816 MONITORING (24364560)... OK
Patch 24364560 : applied on Fri Aug 19 11:28:46 EDT 2016 23294830, 22503390, 23697777, 21818115, 23614256, 24364560, 21959800

(4c) OMS CHAINED AGENT HOME (/oracle/oem/agent13cR1/agent_13.1.0.0.0) EM DB PLUGIN BUNDLE PATCH 13.1.1.0.160531 DISCOVERY (23294839)... OK
Patch 23294839 : applied on Thu Jun 16 11:13:40 EDT 2016 23523964, 23294839, 23226583

(4c) OMS CHAINED AGENT HOME (/oracle/oem/agent13cR1/agent_13.1.0.0.0) EM FMW PLUGIN BUNDLE PATCH 13.1.1.0.160719 MONITORING (23697781)... OK
Patch 23697781 : applied on Wed Jul 20 10:53:54 EDT 2016 22856346, 23095280, 22644197, 23697781, 22461699, 23280938

(4c) OMS CHAINED AGENT HOME (/oracle/oem/agent13cR1/agent_13.1.0.0.0) EM SI PLUGIN BUNDLE PATCH 13.1.1.0.160719 MONITORING (23697783)... OK
Patch 23697783 : applied on Wed Jul 20 10:53:57 EDT 2016 22128210, 23338028, 23189991, 22823189, 21253819, 23697783, 23208587

(4c) OMS CHAINED AGENT HOME (/oracle/oem/agent13cR1/agent_13.1.0.0.0) EM SI PLUGIN BUNDLE PATCH 13.1.1.0.160531 DISCOVERY (23294895)... OK
Patch 23294895 : applied on Thu Jun 16 11:28:18 EDT 2016 23197299, 23294895

(4c) OMS CHAINED AGENT HOME (/oracle/oem/agent13cR1/agent_13.1.0.0.0) EM OH PLUGIN BUNDLE PATCH 13.1.1.0.160429 (23135564)... OK
Patch 23135564 : applied on Wed May 11 13:21:35 EDT 2016 22521822, 23135564

(4d) *UPDATED* OMS HOME (/oracle/oem/Middleware13cR1) ENTERPRISE MANAGER FOR OMS PLUGINS 13.1.1.0.160816 (24364619)... OK
oracle.sysman.emas.oms.plugin/13.1.1.0.0 Plugin 24364619 24308387 oracle.sysman.db.oms.plugin/13.1.1.0.0 Plugin 24364619 24308374 oracle.sysman.xa.oms.plugin/13.1.1.0.0 Plugin 24364619 24308382

(4d) (/oracle/oem/Middleware13cR1) WLS PATCH SET UPDATE 12.1.3.0.160719 (23094292)... OK
Patch 23094292 : applied on Wed Jul 20 12:27:53 EDT 2016

(4f) OMS HOME (/oracle/oem/Middleware13cR1) ENTERPRISE MANAGER BASE PLATFORM PATCH SET UPDATE 13.1.0.0.160719 (23134365)... OK
oracle.sysman.top.oms/13.1.0.0.0 Core 23134365 23134365

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

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

Failed test count: 17 - Review output

sslcheck:Agent @ omshost.domain.com:3872:tls1 protocol connection allowed
sslcheck:BIPublisher @ omshost.domain.com:9803:tls1 protocol connection allowed
sslcheck:NodeManager @ omshost.domain.com:7403:tls1 protocol connection allowed
sslcheck:BIPublisherOHS @ omshost.domain.com:9851:tls1 protocol connection allowed
sslcheck:OMSconsole @ omshost.domain.com:7802:tls1 protocol connection allowed
sslcheck:OMSproxy @ omshost.domain.com:7301:tls1 protocol connection allowed
sslcheck:OMSupload @ omshost.domain.com:4903:tls1 protocol connection allowed
sslcheck:WLSadmin @ omshost.domain.com:7102:tls1 protocol connection allowed
sslcheck:Agent @ omshost.domain.com:3872:tls1_1 protocol connection allowed
sslcheck:BIPublisher @ omshost.domain.com:9803:tls1_1 protocol connection allowed
sslcheck:NodeManager @ omshost.domain.com:7403:tls1_1 protocol connection allowed
sslcheck:BIPublisherOHS @ omshost.domain.com:9851:tls1_1 protocol connection allowed
sslcheck:OMSconsole @ omshost.domain.com:7802:tls1_1 protocol connection allowed
sslcheck:OMSproxy @ omshost.domain.com:7301:tls1_1 protocol connection allowed
sslcheck:OMSupload @ omshost.domain.com:4903:tls1_1 protocol connection allowed
sslcheck:WLSadmin @ omshost.domain.com:7102:tls1_1 protocol connection allowed
certcheck:Agent @ omshost.domain.com:3872 found self-signed certificate

Visit https://pardydba.wordpress.com/2016/04/05/securing-oracle-enterprise-manager-13c/ for the latest version.

Elderflower Vanilla Fizz

A Friday afternoon on April Fool’s Day seems like a good time to return to my earlier plan to write about liquor as the Data Driven Drinker. So I give you the elderflower vanilla fizz, or the Madonna. This cocktail uses local Vermont ingredients from St Johnsbury and Warren.

The elderflower rum gives a light semisweet flavor up front that compares favorably to a liqueur (e.g. St Germain) . The vanilla rum balances it out and shows on the finish. Vary the amount of ice and club soda according to taste.

Ingredients:

1 ounce Dunc’s Mill Elderflower Rum
1 ounce Mad River Distillers’ Vanilla Rum
1/2 ounce lemon juice
1/2 ounce simple syrup
4 ounces club soda
Ice

Shake rum, lemon juice and simple syrup with ice and strain into a long glass over ice. Top with club soda and garnish with lemon slice.

image

Elderflower Vanilla Fizz

WORKAROUND: Unable to monitor Oracle XE 11gR2 with Oracle Enterprise Manager 13c

I have recently switched to using Oracle Enterprise Manager 13c (EM13c – 13.1), replacing my previous EM12c installation.  I elected to install a clean new environment instead of an upgrade, because my old install had been upgraded repeatedly going back to the initial release of EM12c and I wanted a fresh start.

I encountered only one difficult issue during the process. When I attempted to add one production Oracle XE 11gR2 database target, EM13c could not compute the target’s dynamic properties, leaving the target broken. Since you cannot submit jobs against a broken target, this prevented me from using EM13c to back up this database.  I had no comparable issues with XE as a target under EM12c.

The key metric errors that showed during this process included:
“Metric evaluation error start – Target {oracle_database.SID.domain.com} is broken: Dynamic Category property error,Get dynamic property error,No such metadata – No valid queryDescriptor or executionDescriptor found for target [oracle_database.SID.domain.com$30]”

and for the database system target:

“Metric evaluation error start – Received an exception when evaluating sev_eval_proc for:Target name = SID.domain.com_sys, metric_name = Response, metric_column = Status; Error msg = Target encountered metric erros; at least one member in in metric error”

I enabled debugging for the agent logs and attempted again to add the XE target.  Errors showing up in the logs included:

2016-01-15 12:10:05,905 [1806:4CE3192] DEBUG – Computing of dynamic property: [ComputeVC] is done (1 msec, error=true)

2016-01-15 12:10:06,452 [1806:F917F5F8] DEBUG – Computing of dynamic property: [GetDumpDestination] is done (0 msec, error=true)

2016-01-15 12:10:06,508 [1813:6EEEAC87] DEBUG – Computing of dynamic property: [DeduceAlertLogFile] is done (1 msec, error=true)

2016-01-15 12:11:18,779 [1830:CD3A325D] DEBUG – Error was added to oracle_database.SID.domain.com$23(0|MONITORED|false|true|<UF>): Invalid Input

2016-01-15 12:11:18,779 [1831:3657AE55] DEBUG – abandoning long op “CDProps:oracle_database.SID.domain.com:ComputeVC:GENERIC_TASK:Fri Jan 15 12:11:18 EST 2016”

2016-01-15 12:11:18,780 [1830:CD3A325D] DEBUG – Error during dynamic property ComputeVC calculation: critical=true, missingCatProps=[VersionCategory], missingProps=[VersionCategory] oracle.sysman.emSDK.agent.fetchlet.exception.FetchletException: Invalid Input

2016-01-15 12:35:38,038 INFO – Finished dynamic properties computation (total time 817 ms). Number of DP computed: 19. Number of DP with error: 3. Number of dynamic properties that were added: 132.

After reviewing the logs carefully (and posting this as a question in the MOS Oracle XE forum – https://community.oracle.com/thread/3892946) I eventually narrowed the issue down to a query that EM13c runs against DBA_REGISTRY_HISTORY in a target database when added. For database versions greater than 11.2 but less than 12.1.0.2, EM13c assumes that DBA_REGISTRY_HISTORY contains a BUNDLE_SERIES column.  This column does not exist in Oracle XE 11gR2, which reports a version string of 11.2.0.2.

This bug should eventually get a fix as EM13c gets patched, but in the meantime if you need to monitor an Oracle XE target with EM13c, the following workaround took care of the problem for me: create a new DBA_REGISTRY_HISTORY table containing a BUNDLE_SERIES column in your monitoring user’s schema in XE.  So, as user DBSNMP on XE, I ran:

SQL> create table dba_registry_history (ACTION_TIME TIMESTAMP(6), ACTION VARCHAR2(30), NAMESPACE VARCHAR2(30), VERSION VARCHAR2(30), ID NUMBER, BUNDLE_SERIES VARCHAR2(30), COMMENTS VARCHAR2(255));

Since one cannot patch XE, the real DBA_REGISTRY_HISTORY view has no rows and so you do not need to populate any data into this new table.

After adding the table, force a recalculation of dynamic properties by running the following against the EM13c management agent on the XE server:

$ emctl reload agent dynamicproperties SID.domain.com:oracle_database
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD recompute dynprops completed successfully

Once that completed successfully my XE target started to show the correct status in EM13c and I can submit jobs against the target.  All fixed.  I recommend deleting the DBSNMP.DBA_REGISTRY_HISTORY table once the bug gets fixed in OEM.

[EDIT 20160216: Oracle has documented this issue in MOS note EM13c: Database Target Status Shows “Dynamic Category property error” In 13c Cloud Control (Doc ID 2105001.1) and in bug 22592461 DATABASE TARGET STATUS SHOWS “DYNAMIC CATEGORY PROPERTY ERROR” IN 13C CONSOLE. Users on supported databases (e.g., not Oracle XE) should follow the resolution steps in that document instead to correct the real error.]