ORA-00001: unique constraint (ORA.BLOG_TAGLINE_PK) violated

Tag Archives: oms

IMPORTANT UPDATE 20190404: If you use, or have considered using, the EMCLI integration in this script, please take note of the comment posted by Christian Lehnert recently. Christian checked with Oracle ACS who reported that the repository views queried by the EMCLI integration in this script are licensed views and require the Lifecycle Management Pack. If you run the script without using the EMCLI integration, this code path is not reached, so you do not have any licensing implications. If however you do use the EMCLI integration by logging in to EMCLI before running the script, please take this information under advisement. I intend to modify the script going forward to avoid using these repository views, but that will have the side effect of dramatically slowing down the script in EMCLI mode as agent patch checks will have to rely on EM jobs instead of direct repository queries.

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

Acknowledgements for previous release, November 28, 2017, version 2.21: This release includes many improvements provided by Jan Schnackenberg: combining the demo and self-signed certificate checks, adding a more robust multi-dot version string check, and many bugfixes that prevented the script from running correctly on AIX. This release includes the 20171031 bundle patches and latest OPatch, but please note the warning at the end of the script about open bugs with the latest OPatch release. You may wish NOT to install OPatch 13.9.2.1.0 or the DB plugin bundle patch that requires it. Further, due to some changes in the EMCLI implementation to use “emcli list” instead of “emcli execute_sql”, if you use the optional EMCLI integration your EMCLI user will now require the ACCESS_EMCLI_SQL_LIST_VERB privilege. I have updated the create_user_for_checksec13R2.sh script to include this privilege for newly created CHECKSEC user accounts.
Latest release, Oct 18, 2018, version 2.40: This release covers the Oct 16, 2018 critical patch updates.

EMCLI

If you have used this script for a while, you can download the latest release and just run it. It will continue to work the way it always has. If you would like to enable additional, optional functionality, enable the checksec13R2.sh EMCLI integration by logging in to EMCLI with an OEM administrator account before running checksec13R2.sh. The script will use EMCLI and attempt to check for plugin bundle patches on ALL of your OEM agents, not only the chained agent as it used to. It will also use EMCLI to attempt to validate the Java versions on all of your agents. This functionality requires that the EMCLI user account has access to run the execute_sql and execute_hostcmd, and also requires that the EMCLI user account has preferred credentials set for the repository database (normal and sysdba), repository database host, and for every host with a management agent.

To simplify the process, I have created a script to create a CHECKSEC user account in your OEM environment. The script will prompt you for the named credentials that the new account should use to access your repository database and each host. If you run this script after logging in to EMCLI as SYSMAN, it will create the new OEM user, grant acccess to all specified credentials, and grant EM_ALL_OPERATOR and VIEW_ANY_TARGET privileges so that the new account will have all the access needed to run all the optional checksec13R2.sh checks. I have included sample output from the user creation script at the end of this post. You can download the user creation script at create_user_for_checksec13R2.sh.

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

(1e) 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

Checking TLSv1.2 on all agents

Confirming tls1_2 available for Agent at host01.domain.com:3872… OK
Confirming tls1_2 available for Agent at host02.domain.com:3872… OK
Confirming tls1_2 available for Agent at host04.usa.domain.com:3872… OK
Confirming tls1_2 available for Agent at host03.domain.com:3872… OK
Confirming tls1_2 available for Agent at host05.domain.com:3872… OK
Confirming tls1_2 available for Agent at host06.domain.com:1830… OK
Confirming tls1_2 available for Agent at host07.domain.com:3872… OK
Confirming tls1_2 available for Agent at host08.domain.com:3872… OK
Confirming tls1_2 available for Agent at host09.domain.com:1830… OK
Confirming tls1_2 available for Agent at host10.domain.com:3872… OK
Confirming tls1_2 available for Agent at host11.domain.com:3872… OK
Confirming tls1_2 available for Agent at host12.domain.com:3872… OK
Confirming tls1_2 available for Agent at host13.domain.com:3872… OK
Confirming tls1_2 available for Agent at host14.domain.com:3872… OK
Confirming tls1_2 available for Agent at host15.domain.com:3872… OK
Confirming tls1_2 available for Agent at host16.domain.com:3872… OK
Confirming tls1_2 available for Agent at omshost.domain.com:3872… OK
Confirming tls1_2 available for Agent at host17.domain.com:3872… OK
Confirming tls1_2 available for Agent at host18.domain.com:3872… OK

This script exists to supplement checksec13R2.sh and enable additional checks. When run, this
script will create a user named CHECKSEC in your EM13cR2 environment and give that user a
random password, which gets printed to the screen at the end of the script. The script then
grants CHECKSEC VIEW_ANY_TARGET and EM_ALL_OPERATOR privilege, and then prompts you to supply
the names of credentials existing in your EM13cR2 environment. The script validates the names of
credentials supplied, grants VIEW access to CHECKSEC for each credential, and assigns those
credentials as preferred credentials for CHECKSEC for each relevant target.

Providing credentials for the repository database enables the following additional checks in
checksec13R2.sh:
* Check for presence/absence of plugin bundle patches on all agents

Providing host credentials for every monitored host running an agent enables the following
additional checks in checksec13R2.sh:
* Check for presence/absence of the latest Java version on all agents

Login to EMCLI as SYSMAN before running this script. If you already have an CHECKSEC account,
running this script will delete and recreate it with a new password.

Continue? [y/n]
Continuing…

Synchronized successfully
User “CHECKSEC” deleted successfully

User “CHECKSEC” created successfully

Created user CHECKSEC with password: [redacted]

Now CHECKSEC needs preferred credentials for the repository DB and repository DB host.
Your repository DB target name is oemdb.domain.com
Enter the credential name for the repository DB Normal Database Credentials: DB-OEMDB-SYSTEM
Enter the credential name for the repository DB SYSDBA Database Credentials: DB-OEMDB-SYS
Enter the credential name for the repository DB Database Host Credentials: HOST-OMSHOST-ORACLE

[20170822 NOTE: Oracle released the last set of bundle patches for the Oracle Enterprise Manager 13c version in April of 2017. See MOS note 2124038.1 for more details. You really should upgrade to EM13cR2 if you can. My security checkup script for EM13cR2 contains much added functionality and continues to receive updates. I do not expect to release any further updates to this script unless Oracle releases any further key patches or someone who uses it reports a fixable bug.]
[20170418 NOTE: I have upgraded the patches referenced in this script to reflect the latest (20170418) PSU patch for EM13cR1. I no longer have an EM13cR1 environment available with which to test this script, so please feel free to report issues or to submit a git pull request. I have now placed this script on github: https://raw.githubusercontent.com/brianpardy/em13c/master/checksec13.sh.]
[20161013 NOTE: I have upgraded to EM13cR2, and this script still works as expected. If you attempt to run it on an EM13cR2 environment please take note that all of the patch recommendations listed apply to the older EM13cR1 release and will provide incorrect results on 13.2. The TLS, certificate, and cipher strength tests all function correctly on 13.2.]

Download final version

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 EM13cR2 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.

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.

Replacement of EM12c certificates with custom certificates making use of the SHA-2 hashing algorithm appeared to work fine until we tried to apply patches: EM12c opatchauto, SHA256, and you

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.)

[PRIOR 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.

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

(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

This post serves to document an issue I encountered after replacing expired SSL/TLS certificates on the server I use for Oracle Enterprise Manager 12c. To put it simply, using opatchauto to apply EM12c PSUs does not work if your WebLogic adminserver has a certificate installed that uses the SHA256 hashing algorithm.

[UPDATED 20151012: Please see this comment and this comment below, from Adam Robinson, who has provided a workaround that may work for you involving editing the opatchauto script to enable JSSE. As always, please consider workarounds requiring you to edit files as unsupported and at your own risk, but I would consider this fix superior to reverting back to the demo certificate every time you need to patch. You will need to repeat this fix every time you update OPatch in your OMS_HOME, though. Adam’s workaround does succeed in my environment.]

Error message

Expect to see the following error when running “opatchauto apply -analyze” or “opatchauto apply” against an installation with an SHA256-hashed certificate on the WLS adminserver:

Confirmation of the issue

To confirm this issue in your environment after receiving the preceding error message, check the hashing algorithm used on your adminserver certificate. I prefer to use the openssl commandline tool for this. If you don’t know the port used for your adminserver, you can retrieve it from the $EM_INSTANCE_BASE/em/EMGC_OMS1/emgc.properties file under AS_HTTPS_PORT. If your certificate does not show the usage of SHA256 (or another hash algorithm from the SHA-2 family) as in my example below, you may have a different problem.

Workaround

To work around this issue, you need to (temporarily!) replace the certificate on your WLS adminserver. Now, whenever I need to apply a PSU release, I resecure WLS using the default demonstration certificate, apply the PSU, then replace my original certificate.

[Final update: I have migrated to EM13c and no longer have an EM12c installation available on which to further develop this script. Please stay tuned for something similar for EM13c once patches become available.]

With all the recent news on companies getting hacked and attacks on encryption techniques, you need to act proactively to secure your Oracle Enterprise Manager Cloud Control 12c environment. Do not wait for your employer’s auditor to come around and send you a report of all the flaws in your system.

To put it in very simple terms, if you do not do the following across EVERY EM12c component, you should consider your setup vulnerable:

Disable SSLv2 and SSLv3

Enable TLSv1

Disable weak ciphersuites such as those using the MD5 or RC4 algorithms, or those previously designed for export outside the USA back in the 1990s, or those that do not use enough key bits for encryption.

Eliminate the use of self-signed and demonstration certificates.

Stay current on EM12c base releases (currently EM12c R5 but I have not yet upgraded)

Stay current on PSU updates to EM12c (PSU5 as of October 2015)

Stay current on monthly system patch bundles

Stay current on quarterly critical patch update alerts for all EM12c components – note that you have to pay attention to, for example, Oracle HTTP Server (OHS) critical patch updates, as EM12c distributes and relies on OHS. See MOS note 1664074.1 for a good, but incomplete list of patches needed.

Stay current on repository database patch set updates

Stay current on EM12c Java versions [EDIT: 20150415: Added Java check to script] [EDIT: 20150818: Java 1.6_101 caused the Node Manager to fail to start on my system. Therefore I have kept the Java version check at 1.6_95.]

Yes, this takes a lot of work. Yes, the documentation sometimes leaves the process as clear as mud. Yes, you can contact Oracle support for assistance.

Yes, you do need to deal with EVERY endpoint for the SSL configuration. That includes:

OMS console

OMS upload port

OMS console proxy port

Management agents

EM Node Manager

WebLogic Server administration console

OHS administration port

OPMN port

BI Publisher

In the meantime, though, you need to have a good idea of where your system has flaws so that you know where to spend your time fixing it. To help with this, I have created a script that will examine your EM12c environment, find all the ports in use, check for SSLv2, SSLv3, and TLSv1, validate the cipher suites in use, check to make sure you have current patches installed, check for the usage of self-signed certificates on SSL/TLS endpoints, and check for current Java JDK versions in EM12c components. [EDIT: 20150311: Added self-signed certificate check]. [EDIT: 20150313: Added patch check for repository databases on same host as OMS server. I have only tested this on an 11.2.0.4 repository, but I believe it will work for the 12.1.0.2 repository just recently re-certified. If it fails for you please let me know.] [EDIT: 20150415: Added check for Java JDK versions.] [EDIT: 20150630: Added check for SSL_VERSION and SSL_CIPHER_SUITES parameters in repository database sqlnet.ora and listener.ora.]

This script does not require any arguments or configuration. I have tested it ONLY on EM12c R4 and on Linux x86-64 and only on single-host OMS environments. To run this script, copy it from the end of this post (or from the pastebin link above, and execute it as the Oracle software owner on your OMS host, with your environment fully up and running. [EDIT: 20150311: Updated script incorporating feedback from Dave Corsar and opa tropa to support Solaris and AIX.]

The script will not make any changes to your system. Mostly it crawls your configuration files to identify ports, then tests them with the openssl s_client command and various command line arguments to identify protocol and cipher suite usage, and whether or not it can find self-signed certificates. At the end it runs OPatch checks for current needed security and functionality patches.

As of the version 1.1 release, I will mark newly checked patches with “*NEW*” in the script output and updated patches with “*UPDATED*”. For example, when a new PSU patch comes out, I will mark it as an update, but I will mark new (or previously not checked) patches as new. [EDIT: 20150415: This paragraph added.]

Example output from my fully patched and secured system [EDIT: 20150311: Unfortunately I still have self-signed certificates for OPMN and the OHS administration port, so my sample output now includes some failed checks]:

If you try this script, please leave me a comment. If you can share any changes you’ve made that allow it to run on other operating systems, I and others will appreciate it. I spent a lot of time making it so the user does not have to specify any directory locations or port settings, so if you have code changes to offer please let me know. If enough people use this I may learn how to put it on github or something.

[EDIT 20170227: The process for configuring third party certificates for EM13c works about the same as for EM12c. If you have access to Oracle support, I suggest you review notes 2220788.1 and 2213661.1 for the most up-to-date documentation directly from Oracle.]

By default, when an administrator configures Oracle Enterprise Manager 12c to use SSL, the system will use a default self-signed certificate, provided for demo purposes only. The documentation states repeatedly that users should not use these certificates in a production environment, as they represent a security risk. This blog post documents, step by step, a process to replace these demo certificates with custom third party certificates, across the OMS console, OMS upload port, agents, and WebLogic Server. I will follow this process on a single-OMS configuration; if you have more than one OMS please consult the documentation for more details, as your process will vary and the steps I have provided may break your system.

I have tested these instructions on Linux x86-64 (SLES11 SP3) with EM12c R4 PSU2 (12.1.0.4).

Official Documentation

The official documentation for this process resides in the following My Oracle Support notes:

Using ORAPKI Utility to Create a Wallet with Third Party Trusted Certificate and Import into OMS (Doc ID 1367988.1)

EM 12c Cloud Control How to Create a Wallet With Third Party Trusted Certificate that Can Be Imported into the OMS For SSL Comunication ? (Doc ID 1399293.1)

Why Should I Do This?

You may not fully understand the mechanics of SSL/TLS certificates and the chain of trust. I cannot fully explain this complex topic in a blog post, but if you need a reason to make this change other than demands from your organizational security/compliance team, please take Oracle’s word for it, and notice this text that appears in your GCDomain.log file when you run your system with the provided default demo certificates:

#### <[hostname redacted]> <> <> <>

Read that again if you didn’t catch it the first time through: “The system is vulnerable to security attacks, since it trusts certificates signed by the demo trusted CA.” This text comes from code in WebLogic, not from me. Here Oracle tells you very explicitly that your system currently contains a severe vulnerability.

You will also notice that when using the EM12c console, or accessing an agent URL, or accessing the WebLogic Server administration console may show warnings in your browser about untrusted certificates. Once you replace your certificates as described in the documentation above or my steps below, you will no longer have those issues.

Using 3rd Party SSL/TLS Certificates With EM12c

Overview

You will follow 7 high level steps to complete the process of securing your EM12c environment with custom third party SSL/TLS certificates.

Create an Oracle wallet for the OMS.

Secure the OMS console using the OMS wallet.

Secure the OMS upload port using the OMS wallet.

Re-secure all agents.

Create Oracle wallets for agents.

Configure the agents to use their wallets.

Secure WebLogic with the OMS wallet.

Create an Oracle wallet for the OMS

First we follow steps 1a through 1h from document 1367988.1. All these steps occur on the OMS host.

Disable shell history (optional but recommended)

While following these steps, you will repeatedly have to type passphrases on the command line. To avoid having these passphrases stored in your Oracle user’s shell history, disable history saving. In the bash shell that I use, I accomplish this by unsetting the HISTFILE variable. You may need to use another mechanism in another shell.

$ unset HISTFILE

Use the correct ORAPKI command

You should use the ORAPKI command from your middleware home’s oracle_common/bin directory. I will refer to this as $MW_HOME/oracle_common/bin/orapki in the following instructions.

Create an Oracle wallet

The documentation specified that we should create an auto-login wallet, but in my single-OMS setup, I believe that I will achieve better security with an auto-login-local wallet, as the auto-login feature will only function on this specific host. You will need to select a base directory for your OMS wallet. I used $ORACLE_BASE/oemwallet. ORAPKI will prompt you for a password. Use a secure one, and note it down somewhere safe. You will use it many times during this process.

Create a key within the wallet. Make sure you replace omshost.domain.com with the fully qualified domain name of your OMS host. I highly recommend using a 2048 bit keysize, as shown below. Include the wallet password you specified earlier on the commandline as the -pwd argument, contained in single quotes. Display the wallet again afterward.

Export a certificate signing request based on this key. Make sure the -dn you specify exactly matches the -dn specified earlier. Provide a filename in the -request argument in which to store the certificate signing request (CSR).

Submit this CSR file to your signing authority. Inform them that you MUST have a single-host certificate with your OMS host’s fully qualified domain name in the CN field. Subject Alternate Name (SAN) certificates or wildcard certificates will not work at all. Your signing authority should then provide you with a root certificate, an intermediate certificate, and a user certificate.

Import the root, intermediate, and user certificates into the OMS wallet. Note that you must import the root and intermediate certificates using -trusted_cert, and the user certificate using -user_cert. I used DigiCert, and I can confirm that their certificates function correctly in EM12c and recommend their service.

Secure the OMS console

Now, using emctl from the $OMS_HOME, tell EM12c to secure the OMS console using the certificate contained in your wallet. The system will prompt you for the SYSMAN password and inform you to restart the entire OMS once complete.

Now access your OMS console with your favorite browser and confirm that your new certificate appears. Your certificate should show a trusted path back to a root certificate, and your browser should produce no warnings.

At this point, you have secured communication between your browser and the EM12c OMS console with your custom certificate. You still have more work to do though. Your agents upload monitoring data to the OMS upload port, and it still uses the demo certificate. Fix that in the next step.

Secure the OMS upload port

Secure the OMS upload port. Expect to receive email or pager alerts after this step, as once you restart the OMS, none of your agents can communicate with it, as they expect to see the demo certificates on the upload port. You will need to provide the SYSMAN password as well as an agent registration password.

Re-secure all agents

Now you must re-secure all of your agents so that they can resume uploading data to the OMS console and monitoring your systems. Execute the following steps on every agent, using emctl from the agent home. You will need to provide an agent registration password to complete this process.

It may take a little while for the OMS to process the new agents and their uploads, but once you have run this process on every agent they should all communicate successfully with the OMS and appear as OK from the agent management screen.

Create Oracle wallets for agents

Next we secure the agent URLs. The OMS connects to the agents at this URL to submit management requests. At the moment, the agents still use self-signed certificates to secure this URL. For this process we create an Oracle wallet, on the OMS host, using the same ORAPKI command as for the OMS wallet. We will generate a certificate signing request from each agent wallet, submit those CSRs to a certificate authority, and import the received certificates.

As with the OMS, the agents must use single-host certificates, not wildcard or subject alternate name (SAN) certificates. To determine the correct fully qualified domain name for each agent, execute emctl status agent from the agent home.

Create a directory to store the agent wallet, and an agent wallet. This time do NOT use -auto_login_local, use only -auto_login, as you will distribute these wallets to the agent hosts after generating them on the OMS host. Use a strong password, and save it for later, as you will reuse it many times.

As before, submit this certificate signing request to your certificate authority, and receive back three files containing a root certificate, an intermediate certificate, and a user certificate. Import these into the agent wallet, and display the wallet afterwards to confirm everything imported successfully.

You have finished creating this agent’s wallet. Repeat this for every agent.

Configure the agents to use their wallets

Inside the agent wallets you’ve just created, you will find a cwallet.sso file. Take this file from each agent’s wallet and copy it to the agent server. Stop the agent, then place the file into $AGENT_INSTANCE_DIR/sysman/config/server/ and set the permissions to 640, then start the agent.

Next, visit the agent URL in your favorite web browser and examine the certificate it uses. You should now see that it uses the 3rd party SSL/TLS certificate that you installed.

Secure WebLogic with the OMS wallet

Now the OMS (both console and upload ports) and agents will use your new certificates. This leaves WebLogic as the one remaining component needing your new certificates. Please note in following the below directions that securing WebLogic with a wallet only works as of EM12c R3, earlier versions must use a Java keystore. See note 1527874.1 for more information.

[NOTE: 20150910: If you secure WebLogic with a certificate that uses the SHA256 hashing algorithm, future attempts to apply EM12c PSU patches using ‘opatchauto’ will fail. Some piece of opatchauto does not support SHA256 usage in certificates. If you run into this issue, revert your WLS to the demonstration certificate using emctl secure wls -use_demo_cert, then apply the PSU, then resecure WLS using these steps with your desired certificate. I intend to write a full blog post about this later.]

First import the root and intermediate certificates to the keystore on the OMS host’s agent. Use the default password welcome for the agent keystore, and alias names rootcacert and intercacert.

Back up some WLS configuration files, just in case, before securing WLS with your certificate. If you have problems in this step, make sure you have stopped all WLS processes, then restore these files from backup.

Documentation

Prerequisites

Repository Database

You must use Enterprise Edition for the AWRW repository database. You must use version 12.1.0.2 or higher, or version 11.2.0.4 with patch 18547891 applied. Oracle recommends you use a database not used for any other purpose. I strongly agree with that recommendation. Do not use your OEM repository. Note that I had to enable the diagnostic and tuning packs on the AWRW repository database by setting the control_management_pack_access initialization parameter to “DIAG+TUNING” before EM12c would allow me to select it for the repository. I cannot reiterate enough how much I wish Oracle would explicitly state that users may enable management packs on their limited-use repository databases that support EM12c, RMAN catalogs and AWRW, but only a sucker expects license clarity out of Oracle.

I have selected 11.2.0.4 with patch 18547891 for my AWRW repository.

Oracle Enterprise Manager

You must use Oracle Enterprise Manager 12cR4 (12.1.0.4), and your OMS must have at least the August 31, 2014 bundle patch (19391521, or a later bundle patch) applied. Your agents must run version 12.1.0.4.2 or later (requiring patch 19051570).

Licensing

Double check your licensing one more time. Do not use features you have not licensed or you will pay a lot of money once you get audited, and you will get audited.

Configuration

For the purposes of this post I will skip the database installation and configuration steps. If you have not yet gained proficiency with base installation and configuration tasks, you should probably gain some experience there before diving in to the AWR Warehouse. Install a database of the appropriate version and register it with EM12c.

Planning

Think about your architecture. With the recent release of AWRW functionality, some rough edges still exist. These will probably get cleaned up over the next few releases but they took me by surprise and I have not seen them documented anywhere.

Oracle Enterprise Manager Agent Considerations

Do you use a separate dedicated user account on your servers to run the OEM Agent? I do. Your AGENT_INSTANCE_DIR will get used by AWRW as a place to hold Data Pump output containing each source database’s AWR data. I had to make this directory group writable by the dba group. You also need to make sure the volume where this directory resides has enough free space to hold AWR extracts, which end up quite large on a busy system. You may need to add more space if you keep your agent on a dedicated filesystem, as I do.

Do you run multiple instances under isolated accounts that don’t share a group membership? You will probably need to create a group they all share that can write to the AGENT_INSTANCE_DIR.

Preferred Credential Considerations

AWRW strongly depends on the preferred credentials set for a database instance by the user that adds the database to AWRW. If you already heavily use preferred credentials and want to use a different preferred database login for AWRW extraction compared to your usual DBA activities, you may elect to create a dedicated EM12c administrator to maintain AWRW to avoid conflicts.

The AWRW extraction user in the target database must have the DBA role and must also have an explicit execute grant on package SYS.DBMS_SWRF_INTERNAL. I have chosen to use the SYSTEM account, to match my other preferred credential usage, but a more secure setup would use an account dedicated to this task.

Space Considerations

Take a look at how much space AWR consumes in your SYSAUX tablespaces already. Your AWRW repository will need at least this much space, multiplied by however long you plan to keep these AWR snapshots around. This will get very large, very quickly.

Added 20140912: I highly recommend that you disable data file autogrowth on your AWRW repository database. I experienced repeated hangs until I determined that my jobs continually got stuck when SYSTEM or SYSAUX nearly filled and they sat there waiting on data file operations I/O as the system failed to resize the data files or identify a deadlock. Do not rely on data file autogrowth, at least when using an 11.2.0.4 AWRW repository.

Initialize The AWR Warehouse

To begin configuring the AWR Warehouse, you must login using an EM12c super administrator account, like SYSMAN. Once logged in, go to the Databases target list screen. Unfortunately for this purpose you must use the “Database Load Map” form of the screen and not the infinitely more useful “Deprecated Search List (with Metrics)” that I have up on screen 99.9% of the time. Click the Targets menu, select Databases from the submenu that appears, and then if you do not see a “Performance” menu, enable the “Database Load Map” radio button.

Click the Performance menu and select the “AWR Warehouse” item.

This button makes things happen

At this point, if you used a super administrator account, you should see a summary screen that briefly describes the feature with a link to click to begin configuration. If you don’t, log out and come back with the SYSMAN account.

Click Configure to continue

The next screen offers a search box to select the database to use as your AWRW repository and the usual credential selector. Select the correct database, choose a database credential (I first selected SYSTEM, which failed, so use SYS as SYSDBA) and provide host credentials.

Rough edge: no warning that you must use SYSDBA

Once you click Next, the tool will pop up a dialog box warning you to make sure that your repository database has the necessary patch installed, and then asks you to select how long the system should keep AWRW data. You can also select a staging location for AWR data extract storage prior to data loading.

Diamonds and AWR Warehouses are forever

Click Submit on this screen and OEM will submit a job to initialize the AWRW repository. To find this job later, if needed, go to the advanced job activity page and search for jobs of type “dbSetupCAW”. The job should complete successfully if you have done everything correct so far. On my system it only took six seconds, so just wait a moment and then reload the page, which should now look like this.

That was easy

Click on the database icon at the upper left to switch away from the repository configuration tab to the database selection tab.

No data yet

As of this point you no longer need to use the SYSMAN account. I switched back to my regular account, then returned to this screen.

Click the Add button to begin adding your first database(s). OEM will prompt you with the usual target selection screen. Choose one or more databases and then click the Select button. AWRW will NOT prompt you for credentials at this time. Instead it will silently use the database host and normal database user preferred credentials you have established for the database target. Another rough edge I expect to work better in future versions. AWRW will perform some initial validations of those credentials to make sure that the database user has the DBA role and the previously mentioned execute grant on SYS.DBMS_SWRF_INTERNAL. If you have missed any of these steps OEM will tell you about it and prevent you from adding the database. Again, later I expect this to include an automated setup that will fix those issues.

I can’t show you the name

At this point you can just walk away and within about 24 hours you should have AWR data loaded into the warehouse. If you feel impatient, click on one of the lines for a database to select it, then choose “Upload Snapshots Now” from the Actions menu. This will submit a job to extract and load the AWR data, which you can find later under the job type “Run AWR Extract, Transfer and Load”. In the background, this job extracts AWR data to the AGENT_INSTANCE_DIR on the target database’s server, compresses the data, transfers it to the staging area defined during AWRW repository setup, then loads the transferred data into the consolidated AWR Warehouse repository.

One database in there. So many to go.

Summary

The size of and load on your selected database, along with the amount of AWR history you keep online, will influence how long each load takes. My first load appeared to hang, with the AWRW repository database full of sessions waiting on enq: HW contention and buffer busy waits. I ended up doing a shutdown abort and following the workaround instructions in MOS note 1912230.1. I do not know if I truly needed to do this or not, but the symptoms sounded similar. I’ve also noticed that some limits appear to exist. I keep 42 days worth of hourly snapshots in each AWR, and my initial load only picked up 20 days / 500 snapshots. This may represent rate-limiting as a way to trickle-load the AWRW, or it may mean AWRW does not yet play nicely with large AWR data. Time will tell, and I fully expect future versions to shake out any bugs and to hold the DBA’s hand a bit more through the process.

I hope to cover using AWRW for performance tuning in a later post and I look forward to comments.

(EDIT 20130917: If you simply need to change the IP address of your OEM server, please review MOS note 1562029.1. The procedure in that note may allow you to change your OEM server’s IP address without following the lengthy process I describe below.)

In order to save power in our data center, I need to migrate my EM12c R3 environment from the host where it currently runs to a new host. I have a simple configuration, with a single OMS, no load balancer, and the repository database runs on the same host as EM12c R3 itself. I also have BI Publisher installed and integrated with EM12c, and a few third party plugins as I’ve detailed elsewhere on this blog. If you use an OS other than Linux x86-64 I suggest you research thoroughly as this procedure may or may not apply to your environment. Further, if you have a multi-OMS setup or use a load balancer, you must read the documentation and adapt the process accordingly to match your system’s needs. Note that I wrote this as I did the migration, live, on my production system, so I have text in a few places showing where I would have done things differently if I knew what to expect in the first place. It all ended up working, but it could have been simpler.

Oracle documents the procedure for this migration in the EM12c Administrator’s Guide, Part VII, section 29, “Backing Up and Recovering Enterprise Manager“. As a first step, my system administrator installed SLES 11 SP3 on the new server and created an account for me along with the ‘oracle’ account for EM12c. I have a 70GB volume to use for the database and OEM binaries, a 1GB volume for the DB control files and a 2GB volume for redo logs supplemented with a 15GB FRA volume to support flashback. Due to our tape backup strategy I use the FRA only for flashback, which we don’t wish to backup, and use a separate volume for RMAN backupsets. To avoid a backup/restore cycle, the volumes holding the database datafiles will just be moved over to the new host on the storage side.

First I will relocate the management repository database to the new host, then complete the process by relocating the OMS.

Relocating the Management Repository Database

I run Oracle Database 11.2.0.3, Enterprise Edition, plus PSU Jul 2013. Rather than installing the database software from scratch and patching it, I will clone the existing Oracle home to the new server. Unfortunately I cannot use EM12c to do the cloning, as cloning via EM12c requires a management agent on the new host. The software-only install of EM12c that I will run later installs a management agent as part of the process and I do not wish these two to conflict, so I do not want to install an agent on the new host at this time.

I will clone the database home according to the procedure in Appendix B of the 11gR2 database documentation. You should review the documentation for full details.

Cloning the Database Home

Stop the OMS, database and management agent before cloning the existing Oracle home.

Create a zip file of the existing database home. Run this step as root (or using sudo) to make sure that you get all the files.

oracle$ sudo zip -r dbhome_1.zip /oracle/oem/product/11.2.0/dbhome_1

Now I will start the original database back up so that OEM continues running while I prepare the cloned Oracle home. I will perform this migration over a few days, as I have time, so I need to keep OEM up and running as much as possible to support and manage my other databases.

Unfortunately this creates an oraInventory directory in the oracle user’s home directory. I prefer to keep oraInventory under ORACLE_BASE, so I moved it and edited the generated files to change the path from /home/oracle/oraInventory to /oracle/oem/oraInventory. Most likely some environment variable, or a previously existing /etc/oraInst.loc would have prevented this optional step.

Start Management Repository Database On New Host

At this point you will probably use RMAN to create a backup of your original repository database, then restore that backup onto the new host. Instead, I will cheat a bit, shut down OEM and the database, and ask my sysadmin to move the repository database’s datafile LUN over to the new host and mount it at the same location.

Before moving the LUN, create directories that the database needs for a successful startup. These include the admin/SID/adump directory, and in my case, the /oracle/mirror/SID/cntrl and /oracle/mirror/SID/log directories where I keep the multiplexed copies of my redo logs and controlfiles.

As a sanity check, you should try starting up the listener on the new server and starting the database in NOMOUNT mode before proceeding. This will help catch any issues that may exist in your environment before you start the outage on your original server. Investigate and resolve any issues found before proceeding.

Login to OEM and confirm proper operation of the system. I had a lot of alerts for failed backup jobs since my repository database hosts my RMAN catalog. These can wait for now. Also expect your repository target to show as down, since you have not yet updated the monitoring configuration. Reconfigure it now, providing the SYSMAN password when prompted.

At this point you have successfully moved your repository database. Don’t worry about any errors for now, though if you rely on an RMAN catalog and stored scripts for your backups, and these all live in your OEM repository database, you should go through now and update the monitoring configuration for the repository database and listener so that backups of your other databases do not fail. I had to edit the recovery catalog and specify the host, port, and SID manually, since for some reason when I told it to use the repository database it kept trying to use the old hostname. I will fix this after I complete the rest of the migration.

IMPORTANT NOTE: Since you have not yet migrated the repository database target to an agent local to that machine, backups of your repository database may not run. Monitor your archived log directory on this system until you complete the rest of the migration, and manually run backups when necessary.

Installing OMS On A New Host

To install the OMS on a new host, perform a software-only installation from the same EM12c R3 installer that was used to install on the original host. You will need to identify and retrieve all of the plugins that you have installed on the current OMS, as well as any patches that are currently installed on the OMS. You must also make sure to use the same directory layout as on the original OMS.

This patch gets installed by the EM12c R3 installer, so no need to bother with it any further. If you have other patches installed, go fetch them, and install them after you have completed the plugin installation (see below).

Identifying Installed Plugins

Identify all plugins installed on your system using the query provided in the documentation, run as SYSMAN against your repository database.

Oracle-provided plugins will show a URL from which you must download the plugin. Third-party plugins will not; you will need to make sure you have the appropriate downloaded plugin install .opar file from when you initially installed it. Gather up all of these plugin files into a single directory on your NEW OMS host, changing the “.zip” filename extension to “.opar” for the Oracle-provided plugins. You need EVERY plugin returned by this query or else your installation will NOT work. I placed mine in /oracle/oem/migration/plugins.

You also need to copy over the three .zip files containing the OEM 12cR3 distribution: V38641-01.zip, V38642-01.zip and V38643-01.zip. Save them into a convenient staging area on the new server (I use /oracle/oem/stage).

Perform Software-Only Installation Of EM12c R3

Go to the staging area on the new server and extract the three .zip files containing the EM12c R3 distribution, then start the installer.

You can follow my previous post about upgrading EM12c R2 to R3 for more information about the installation process, just make sure you run it as a software only install and use the exact same path names as configured on the original OMS. In my case this means a middleware home of /oracle/oem/Middleware12cR3 and an agent base directory of /oracle/oem/agent12c.

While the software installation proceeds, you should run an exportconfig on your current OMS to produce the configuration backup file you will need to use to reconfigure the new one. Enter the SYSMAN password when prompted.

oracle$ $OMS_HOME/bin/emctl exportconfig oms
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
ExportConfig started...
Machine is Admin Server host. Performing Admin Server backup...
Exporting emoms properties...
Exporting secure properties...
Export has determined that the OMS is not fronted
by an SLB. The local hostname was NOT exported.
The exported data can be imported on any host but
resecure of all agents will be required. Please
see the EM Advanced Configuration Guide for more
details.
Exporting configuration for pluggable modules...
Preparing archive file...
Backup has been written to file: /oracle/oem/gc_inst/em/EMGC_OMS1/sysman/backup/opf_ADMIN_20130828_120424.bka
The export file contains sensitive data.
You must keep it secure.
ExportConfig completed successfully!

After running allroot.sh, you need to run the PluginInstall.sh script with the path where you saved the .opar files. Make sure you select every plugin listed when you ran the query to retrieve the plugin list earlier, then hit install.

Start the agent on the old OMS server. You should not need to do this, but I could not update the WebLogic Domain monitoring configuration without doing so first. Also re-secure this agent to point to the new OMS.

Login to the OEM GUI running on the new server and navigate to the WebLogic Domain target for the Cloud Control domain. In the Target Setup -> Monitoring Credentials section, update the Administration server host value to the new server name, then hit OK. Then execute a Refresh WebLogic Domain, selecting Add/Update Targets, to move all WebLogic targets to the new central agent.

I use third-party plugins to monitor VMWare targets, NetApp storage and MySQL servers. I had many of them set up to run from the OMS agent (except for the VMWare ones, since Blue Medora helpfully advised not to use the OMS agent for this — great advice). I now need to relocate each of these targets to the new central agent using emcli. You won’t need to do this step unless you also have things set up this way. If I had to do this again, I would not use the OMS agent for these targets, since I would not need to change anything if I just had these on some other agent.

Final Cleanup Steps

By now you have completed the bulk of the work necessary to migrate your EM12c stack to a new server. Only a few steps remain. If you use any utility scripts on the old server, go ahead and copy those over now. I have scripts to automate starting/stopping the OMS and agent, so I’ve copied those over. Also make sure the oracle user on the new server has all the environment variables set up in their shell initialization files.

oracle$ scp ~/bin/CCstart ~/bin/CCstop oracle@newhost:bin/

The GCDomain Oracle WebLogic Domain target did not get moved to my new agent. If this happened to you, go to the target home page and select the Modify Agents menu item. Click Continue, then find GCDomain in the list, scroll to the right, and assign the new OMS server’s agent as the monitoring agent for this target, then click the Modify Agents button.

Reinstall BI Publisher

Since I had BI Publisher installed on the old server, I need to install it again on the new one. Retrieve the 11.1.1.6.0 BI Publisher installation files used previously, and copy them to your staging area. Run the “runInstaller” program from bishiphome/Disk1, and perform a software-only installation with the middleware home set to your EM12c installation middleware home, and leave the Oracle home as Oracle_BI1.

Instead of running the configureBIP script as you normally would to integrate BI Publisher with EM12c, just go to the WebLogic administration console after the software-only install completes, and navigate to the BIP server configuration page. Lock the configuration for editing, and edit the configuration to change the listen address to reference the new server’s hostname and change the machine to the machine name where the admin server runs (in my case it showed up as EMGC_MACHINE2). Save and activate the changes, then start the BIP server.

After the server has started, return to the WebLogic Domain page and re-run the Refresh WebLogic Domain step, again with Add/Update targets, to move BIP to your new OMS agent.

I actually had to do the Refresh WebLogic Domain step here twice. I may have simply not waited long enough after starting BIP before I ran it, but I do not know for sure.

Update EM Console Service

I have only one target showing down at this point, the EM Console Service. Go to the target, and click on the Monitoring Configuration tab. Click on Service Tests and Beacons. Select the EM Console Service Test, and click the Edit button. Make sure you have the “Access Login page” step selected, and click Edit. Change the URL to reflect your new OEM server, and save the changes.

Remove Previous OMS Server From OEM

Stop the agent on your original OMS server.

oracle$ $AGENT_HOME/bin/emctl stop agent

Remove the host target where your original OMS ran. Then remove the agent target.

One Last Bounce

Finally, bounce the whole thing one last time, then start it back up. All green.

Conclusion

I would prefer a simpler process to migrate the EM12c stack to a new server, but this works. If you find yourself in a similar position to mine, I hope this helps you. I’ve spent a lot of time working in EM12c so I feel capable to diagnose and resolve issues encountered during the process, but if you run into problems do not hesitate to contact Oracle Support and file a service requests. If you want your system to stay supportable, stick with the experts and just use blogs as a guide to get started. Good luck.