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 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.
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.]
[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.]
[LATEST SCRIPT UPDATE: 20151204, VERSION 1.11, covers 20151130 patch release]
Download the script here.
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:
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:
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]:
Performing EM12cR4 security checkup version 1.11 on omshost.domain.com at Fri Dec 4 14:17:40 EST 2015. Using port definitions from configuration files /etc/oragchomelist /oracle/oem/gc_inst1/em/EMGC_OMS1/emgc.properties /oracle/oem/gc_inst1/em/EMGC_OMS1/embip.properties /oracle/oem/gc_inst1/WebTierIH1/config/OPMN/opmn/ports.prop /oracle/oem/gc_inst1/WebTierIH1/config/OHS/ohs1/admin.conf Agent port found at omshost.domain.com:3872 BIPublisher port found at omshost.domain.com:9702 NodeManager port found at omshost.domain.com:7404 OHSadmin port found at omshost.domain.com:9999 OMSconsole port found at omshost.domain.com:7803 OMSproxy port found at omshost.domain.com:7302 OMSupload port found at omshost.domain.com:4902 OPMN port found at omshost.domain.com:6701 WLSadmin found at omshost.domain.com:7103 Repository DB version=11.2.0.4.0 SID=emrep host=omshost.domain.com Repository DB on OMS server, will check patches/parameters in /oracle/oem/product/11.2.0/dbhome_2 (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:9702... OK Confirming ssl2 disabled for NodeManager at omshost.domain.com:7404... OK Confirming ssl2 disabled for OHSadmin at omshost.domain.com:9999... OK Confirming ssl2 disabled for OMSconsole at omshost.domain.com:7803... OK Confirming ssl2 disabled for OMSproxy at omshost.domain.com:7302... OK Confirming ssl2 disabled for OMSupload at omshost.domain.com:4902... OK Confirming ssl2 disabled for OPMN at omshost.domain.com:6701... OK Confirming ssl2 disabled for WLSadmin at omshost.domain.com:7103... 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:9702... OK Confirming ssl3 disabled for NodeManager at omshost.domain.com:7404... OK Confirming ssl3 disabled for OHSadmin at omshost.domain.com:9999... OK Confirming ssl3 disabled for OMSconsole at omshost.domain.com:7803... OK Confirming ssl3 disabled for OMSproxy at omshost.domain.com:7302... OK Confirming ssl3 disabled for OMSupload at omshost.domain.com:4902... OK Confirming ssl3 disabled for OPMN at omshost.domain.com:6701... OK Confirming ssl3 disabled for WLSadmin at omshost.domain.com:7103... OK (1c) Permit TLSv1 connections Confirming tls1 available for Agent at omshost.domain.com:3872... OK Confirming tls1 available for BIPublisher at omshost.domain.com:9702... OK Confirming tls1 available for NodeManager at omshost.domain.com:7404... OK Confirming tls1 available for OHSadmin at omshost.domain.com:9999... OK Confirming tls1 available for OMSconsole at omshost.domain.com:7803... OK Confirming tls1 available for OMSproxy at omshost.domain.com:7302... OK Confirming tls1 available for OMSupload at omshost.domain.com:4902... OK Confirming tls1 available for OPMN at omshost.domain.com:6701... OK Confirming tls1 available for WLSadmin at omshost.domain.com:7103... OK (2) Checking supported ciphers at SSL/TLS endpoints (see notes 1477287.1, 1905314.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:9702)... OK Checking MEDIUM strength ciphers on BIPublisher (omshost.domain.com:9702)... OK Checking HIGH strength ciphers on BIPublisher (omshost.domain.com:9702)... OK Checking LOW strength ciphers on NodeManager (omshost.domain.com:7404)... OK Checking MEDIUM strength ciphers on NodeManager (omshost.domain.com:7404)... OK Checking HIGH strength ciphers on NodeManager (omshost.domain.com:7404)... OK Checking LOW strength ciphers on OHSadmin (omshost.domain.com:9999)... OK Checking MEDIUM strength ciphers on OHSadmin (omshost.domain.com:9999)... OK Checking HIGH strength ciphers on OHSadmin (omshost.domain.com:9999)... OK Checking LOW strength ciphers on OMSconsole (omshost.domain.com:7803)... OK Checking MEDIUM strength ciphers on OMSconsole (omshost.domain.com:7803)... OK Checking HIGH strength ciphers on OMSconsole (omshost.domain.com:7803)... OK Checking LOW strength ciphers on OMSproxy (omshost.domain.com:7302)... OK Checking MEDIUM strength ciphers on OMSproxy (omshost.domain.com:7302)... OK Checking HIGH strength ciphers on OMSproxy (omshost.domain.com:7302)... OK Checking LOW strength ciphers on OMSupload (omshost.domain.com:4902)... OK Checking MEDIUM strength ciphers on OMSupload (omshost.domain.com:4902)... OK Checking HIGH strength ciphers on OMSupload (omshost.domain.com:4902)... OK Checking LOW strength ciphers on OPMN (omshost.domain.com:6701)... OK Checking MEDIUM strength ciphers on OPMN (omshost.domain.com:6701)... OK Checking HIGH strength ciphers on OPMN (omshost.domain.com:6701)... OK Checking LOW strength ciphers on WLSadmin (omshost.domain.com:7103)... OK Checking MEDIUM strength ciphers on WLSadmin (omshost.domain.com:7103)... OK Checking HIGH strength ciphers on WLSadmin (omshost.domain.com:7103)... 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)... OK Checking certificate at Agent (omshost.domain.com:3872)... OK Checking certificate at BIPublisher (omshost.domain.com:9702)... OK Checking certificate at BIPublisher (omshost.domain.com:9702)... OK Checking certificate at NodeManager (omshost.domain.com:7404)... OK Checking certificate at NodeManager (omshost.domain.com:7404)... OK Checking certificate at OHSadmin (omshost.domain.com:9999)... FAILED - Found self-signed certificate Checking certificate at OHSadmin (omshost.domain.com:9999)... OK Checking certificate at OMSconsole (omshost.domain.com:7803)... OK Checking certificate at OMSconsole (omshost.domain.com:7803)... OK Checking certificate at OMSproxy (omshost.domain.com:7302)... OK Checking certificate at OMSproxy (omshost.domain.com:7302)... OK Checking certificate at OMSupload (omshost.domain.com:4902)... OK Checking certificate at OMSupload (omshost.domain.com:4902)... OK Checking certificate at OPMN (omshost.domain.com:6701)... FAILED - Found self-signed certificate Checking certificate at OPMN (omshost.domain.com:6701)... OK Checking certificate at WLSadmin (omshost.domain.com:7103)... OK Checking certificate at WLSadmin (omshost.domain.com:7103)... OK (4) Checking EM12c Oracle home patch levels against 30 Nov 2015 baseline (see notes 1664074.1, 1900943.1, 822485.1, 1470197.1, 1967243.1) (4a) OMS (/oracle/oem/Middleware12cR4/oms) ENTERPRISE MANAGER BASE PLATFORM - OMS 12.1.0.4.5 PSU Patch (21462217)... OK Patch 21462217 : applied on Tue Oct 20 12:13:32 EDT 2015 19055251, 19586898, 20260177, 19323634, 21462217, 19941819, 18725891 (4a) OMS HOME (/oracle/oem/agent12c/core/12.1.0.4.0) JDBC Merge Patch (18502187)... OK Patch 18502187 : applied on Thu Oct 22 10:29:36 EDT 2015 (4b) BI Publisher (/oracle/oem/Middleware12cR4/Oracle_BI1) CPUJAN2015 Patch (19822893)... OK 19822893 19822893 Patch 19822893 : applied on Wed Feb 25 09:16:21 EST 2015 (4b) BI Publisher (/oracle/oem/Middleware12cR4/Oracle_BI1) Merge Patch (20444447)... OK Patch 20444447 : applied on Wed Feb 25 09:21:03 EST 2015 (4c) AS Common (/oracle/oem/Middleware12cR4/oracle_common) CVE-2015-0426 Oracle Help Patch (20075252)... OK Patch 20075252 : applied on Thu Jan 22 14:39:21 EST 2015 (4c) AS Common (/oracle/oem/Middleware12cR4/oracle_common) WEBCENTER PORTAL BUNDLE PATCH 11.1.1.7.1 (16761779)... OK Patch 16761779 : applied on Wed Apr 15 12:18:20 EDT 2015 (4c) AS Common (/oracle/oem/Middleware12cR4/oracle_common) CVE-2015-4742 MERGE REQUEST ON TOP OF 11.1.1.7.1 FOR BUGS 20747356 18274008 (21068288)... OK Patch 21068288 : applied on Thu Sep 17 09:52:53 EDT 2015 (4d) WebLogic Server (/oracle/oem/Middleware12cR4/wlserver_10.3) 10.3.6.0.12 EJUW Patch (20780171)... OK CR(s)..................... 20780171 Jar....................... BUG20780171_1036012.jar Destination............... $WLS_INSTALL_DIR$/bugsfixed/20780171-WLS-10.3.6.0.12_PSU_WebServices-ClientSide-Configuration-README.txt (4d) WebLogic Server (/oracle/oem/Middleware12cR4/wlserver_10.3) SU Patch [GDFA]: WEBLOGIC.STORE.PERSISTENTSTOREEXCEPTION: [STORE:280040] OCCURS EASILEY (16420963)... OK CR(s)..................... 16420963 Jar....................... BUG16420963_1036.jar (4e) WebTier (/oracle/oem/Middleware12cR4/Oracle_WT) OHS SECURITY PATCH UPDATE 11.1.1.7.0 CPUOCT2015 Patch (21640624)... OK Patch 21640624 : applied on Mon Oct 26 13:59:17 EDT 2015 (4e) WebTier (/oracle/oem/Middleware12cR4/Oracle_WT) CVE-2014-4212 OPMN Patch (19345576)... OK Patch 19345576 : applied on Thu Jan 22 13:02:25 EST 2015 (4e) WebTier (/oracle/oem/Middleware12cR4/Oracle_WT) CVE 2015-2658 MERGE REQUEST ON TOP OF 11.1.1.7.0 FOR BUGS 16370190 20310323 20715657 (20807683)... OK Patch 20807683 : applied on Wed Jul 15 12:22:04 EDT 2015 (4e) WebTier (/oracle/oem/Middleware12cR4/Oracle_WT) CVE-2013-0169,CVE-2011-3389 OSS SECURITY PATCH UPDATE 11.1.1.7.0 CPUOCT2013 (17337741)... OK Patch 17337741 : applied on Wed Apr 15 10:36:26 EDT 2015 (4e) WebTier (/oracle/oem/Middleware12cR4/Oracle_WT) WLSPLUGINS (OHS) SECURITY PATCH UPDATE 11.1.1.7.0 CPUJUL2014 (18423831)... OK Patch 18423831 : applied on Wed Apr 15 12:45:02 EDT 2015 (4f) *UPDATED* OMS (/oracle/oem/Middleware12cR4/oms) DB PLUGIN BUNDLE PATCH 12.1.0.7.10 (22062307)... OK 22062307;EM DB PLUGIN BUNDLE PATCH 12.1.0.7.10 21744966,21745018,21972104,22062375,22062307 (4g) *UPDATED* OMS (/oracle/oem/Middleware12cR4/oms) FMW PLUGIN BUNDLE PATCH 12.1.0.7.10 (22062375)... OK 22062375;EM FMW PLUGIN BUNDLE PATCH 12.1.0.7.10 21744966,21745018,21972104,22062375,22062307 (4h) OMS (/oracle/oem/Middleware12cR4/oms) MOS PLUGIN BUNDLE PATCH 12.1.0.6.8 (21745018)... OK 21745018;EM MOS PLUGIN BUNDLE PATCH 12.1.0.6.8 21744966,21745018,21972104,22062375,22062307 (4i) *UPDATED* OMS (/oracle/oem/Middleware12cR4/oms) EXADATA PLUGIN BUNDLE PATCH 12.1.0.6.11 (21744966)... OK 21744966;EM EXADATA PLUGIN BUNDLE PATCH 12.1.0.6.11 21744966,21745018,21972104,22062375,22062307 (4j) *UPDATED* OMS (/oracle/oem/Middleware12cR4/oms) CFW PLUGIN BUNDLE PATCH 12.1.0.2.4 (21972104)... OK 21972104;EM CFW Plugin Bundle Patch 12.1.0.2.4 21744966,21745018,21972104,22062375,22062307 (4k) *UPDATED* OMS CHAINED AGENT HOME (/oracle/oem/agent12c/core/12.1.0.4.0) EM-AGENT BUNDLE PATCH 12.1.0.4.14 (21913823)... OK Patch 21913823 : applied on Fri Dec 04 09:16:23 EST 2015 17438375, 18936726, 21913823, 20496804, 21325110, 20701411, 21565489 (4k) OMS CHAINED AGENT HOME (/oracle/oem/agent12c/core/12.1.0.4.0) Merge Patch (18502187)... OK Patch 18502187 : applied on Fri Apr 03 09:45:56 EDT 2015 (4k) OMS CHAINED AGENT HOME (/oracle/oem/agent12c/core/12.1.0.4.0) JDBC Security Patch (18721761)... OK Patch 18721761 : applied on Fri Apr 03 09:45:52 EDT 2015 (4k) OMS CHAINED AGENT HOME (/oracle/oem/agent12c/core/12.1.0.4.0) CVE 2012-3137 EM Agent only: Instant Client Security Patch (20114054)... OK Patch 20114054 : applied on Fri May 01 10:01:01 EDT 2015 20114054 (4l) *UPDATED* OMS CHAINED AGENT DB PLUGIN (/oracle/oem/agent12c/core/12.1.0.4.0/../../plugins/oracle.sysman.db.agent.plugin_12.1.0.7.0) DB PLUGIN BUNDLE 12.1.0.7.10 AGENT-SIDE MONITORING (22140476)... OK Patch 22140476 : applied on Fri Dec 04 11:54:20 EST 2015 15837598, 21907123, 21460951, 20765041, 20844888, 22140476, 21806804 (4l) OMS CHAINED AGENT DB PLUGIN (/oracle/oem/agent12c/core/12.1.0.4.0/../../plugins/oracle.sysman.db.discovery.plugin_12.1.0.7.0) DB PLUGIN BUNDLE 12.1.0.7.4 AGENT-SIDE DISCOVERY (21065239)... OK Patch 21065239 : applied on Thu Jun 04 11:15:02 EDT 2015 18413892, 21065239 (4m) *UPDATED* OMS CHAINED AGENT FMW PLUGIN (/oracle/oem/agent12c/core/12.1.0.4.0/../../plugins/oracle.sysman.emas.agent.plugin_12.1.0.7.0) FMW PLUGIN BUNDLE 12.1.0.7.9 AGENT-SIDE MONITORING (21941290)... OK Patch 21941290 : applied on Fri Dec 04 12:01:35 EST 2015 20644295, 21894243, 20677020, 21888856, 21527296, 21941290, 21415166 (4m) OMS CHAINED AGENT FMW PLUGIN (/oracle/oem/agent12c/core/12.1.0.4.0/../../plugins/oracle.sysman.emas.discovery.plugin_12.1.0.7.0) FMW PLUGIN BUNDLE 12.1.0.7.7 AGENT-SIDE DISCOVERY (21611921)... OK Patch 21611921 : applied on Tue Sep 01 13:34:27 EDT 2015 21611921, 20644315, 20677038, 21199835, 21229841, 21610843 (4n) *UPDATED* OMS CHAINED AGENT BEACON PLUGIN (/oracle/oem/agent12c/core/12.1.0.4.0/../../plugins/oracle.sysman.beacon.agent.plugin_12.1.0.4.0) EM-BEACON BUNDLE PATCH 12.1.0.4.2 (21928148)... OK Patch 21928148 : applied on Fri Dec 04 12:35:11 EST 2015 21928008, 21928148, 20466772, 20397739 (4o) OMS CHAINED AGENT EM-OH BUNDLE PATCH 12.1.0.4.1 (20855134)... OK Patch 20855134 : applied on Thu Apr 30 15:54:47 EDT 2015 15985793, 20855134 (4p) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/11.2.0/dbhome_2) PSU 11.2.0.4.8 (OCT2015) (21352635)... OK Patch 21352635 : applied on Thu Oct 22 09:39:55 EDT 2015 Patch description: "Database Patch Set Update : 11.2.0.4.8 (21352635)" (4p) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/11.2.0/dbhome_2) ORACLE JAVAVM COMPONENT 11.2.0.4.5 DATABASE PSU (OCT2015) (21555791)... OK Patch 21555791 : applied on Thu Oct 22 09:41:22 EDT 2015 (4q) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/11.2.0/dbhome_2) sqlnet.ora SSL_VERSION parameter (1545816.1)... OK 1.0 (4q) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/11.2.0/dbhome_2) sqlnet.ora SSL_CIPHER_SUITES parameter (1545816.1)... OK (SSL_RSA_WITH_AES128_CBC_SHA,SSL_RSA_WITH_AES256_CBC_SHA) (4q) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/11.2.0/dbhome_2) listener.ora SSL_VERSION parameter (1545816.1)... OK 1.0 (4q) OMS REPOSITORY DATABASE HOME (/oracle/oem/product/11.2.0/dbhome_2) listener.ora SSL_CIPHER_SUITES parameter (1545816.1)... OK (SSL_RSA_WITH_AES128_CBC_SHA,SSL_RSA_WITH_AES256_CBC_SHA) (5) Checking EM12c Java versions against baseline (see notes 1506916.1, 1492980.1) (5a) MW (/oracle/oem/Middleware12cR4/jdk16/jdk) Java version 1.6.0_95 (9553040)... OK 1.6.0_95 (5b) WebTier (/oracle/oem/Middleware12cR4/Oracle_WT/jdk) Java version 1.6.0_95 (9553040)... OK 1.6.0_95 Failed test count: 2 - Review output certcheck:OHSadmin @ omshost.domain.com:9999 found self-signed certificate certcheck:OPMN @ omshost.domain.com:6701 found self-signed certificate Visit https://pardydba.wordpress.com/2015/03/09/em12c-r4-ssl-security-checkup-script/ for the latest version.
Body of script:
#!/bin/bash
#
# This script should examine your EM12c R4 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 also contains a patch
# check currently comparing against the latest recommended patches
# and flags the use of self-signed certificates. Further checks include
# EM12c Java JDK version.
#
# Added in v1.0: Repository database patch check
# Added in v1.1: EM12c Java JDK version check
# Change in v1.2: Removed patch 19948000 recommendation for OHS.
# Change in v1.3: Update for 30 Apr 2015 patches, add EM-OH plugin home
# restored GDFA/16420963 for WLS
# added 20114054 for Agent - only applicable for Linux x86-64
# Change in v1.4: Add datestamp/hostname to output header
# Update for 31 May 2015 patches, add EM-DB-DISC plugin home
# Change in v1.5: Add repo DB check for SSL_VERSION and SSL_CIPHER_SUITES
# Add VERBOSE_CHECKSEC variable:
# Set to 0 for quiet run.
# Set to 1 to see failed check summary after run.
# Set to 2 for failed check summary and patch details.
# Change in v1.6: Add PSU4 for EM12cR4, complete VERBOSE_CHECKSEC work
# Add 14 July 2015 patches
# Change in v1.7: Update for 31 Jul 2015 patches
# Change in v1.8: Update for 31 Aug 2015 patches
# Change in v1.9: Add 17714229 for OMS home
# Add 21068288 CVE-2015-4742 for oracle_common home
# Add check for usage of demonstration SSL certificates
# Change in v1.10: Update for 1 Oct 2015 patches, PSU5, CPUOCT2015
# Added 18502187 for OMS home
# Change in v1.11: Update for 30 Nov 2015 patches
#
# From: @BrianPardy on Twitter
#
# Known functional on Linux x86-64, Solaris, AIX.
#
# Run this script as the Oracle EM12c software owner, with your environment
# fully up and running.
#
# Thanks to Dave Corsar, who tested on Solaris and let me know the
# changes needed to make an earlier version work on Solaris.
#
# Thanks to opa tropa who confirmed AIX functionality and noted the
# use of GNU extensions to grep, which I have since removed.
#
# Dedicated to our two Lhasa Apsos:
# Lucy (6/13/1998 - 3/13/2015)
# Ethel (6/13/1998 - 7/31/2015)
#
#
SCRIPTNAME=`basename $0`
PATCHDATE="30 Nov 2015"
OMSHOST=`hostname -f`
VERSION="1.11"
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"
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
OMS_HOME=`$GREP -i oms $ORAGCHOMELIST | xargs ls -d 2>/dev/null`
OPATCH="$OMS_HOME/OPatch/opatch"
OPATCHAUTO="$OMS_HOME/OPatch/opatchauto"
OMSORAINST="$OMS_HOME/oraInst.loc"
ORAINVENTORY=`head -n 1 $OMSORAINST | awk -F= '{print $2}'`
MW_HOME=`dirname $OMS_HOME`
BIP_HOME=`$GREP -vi REMOVED $ORAINVENTORY/ContentsXML/inventory.xml | $GREP "HOME NAME=\"Oracle_BI" | awk '{print $3}' | sed -e 's/LOC=\"//' | sed -e 's/"//'`
COMMON_HOME=`$GREP -vi REMOVED $ORAINVENTORY/ContentsXML/inventory.xml | $GREP "HOME NAME=\"common" | awk '{print $3}' | sed -e 's/LOC=\"//' | sed -e 's/"//'`
WEBTIER_HOME=`$GREP -vi REMOVED $ORAINVENTORY/ContentsXML/inventory.xml | $GREP "HOME NAME=\"webtier" | awk '{print $3}' | sed -e 's/LOC=\"//' | sed -e 's/"//'`
AGENT_HOME=`$GREP -vi REMOVED $ORAINVENTORY/ContentsXML/inventory.xml | $GREP "HOME NAME=\"agent12c" | awk '{print $3}' | sed -e 's/LOC=\"//' | sed -e 's/"//'`
AGENT_DB_PLUGIN_HOME="$AGENT_HOME/../../plugins/oracle.sysman.db.agent.plugin_12.1.0.7.0"
AGENT_DB_PLUGIN_DISC_HOME="$AGENT_HOME/../../plugins/oracle.sysman.db.discovery.plugin_12.1.0.7.0"
AGENT_FMW_PLUGIN_HOME="$AGENT_HOME/../../plugins/oracle.sysman.emas.agent.plugin_12.1.0.7.0"
AGENT_FMW_PLUGIN_DISC_HOME="$AGENT_HOME/../../plugins/oracle.sysman.emas.discovery.plugin_12.1.0.7.0"
AGENT_BEACON_PLUGIN_HOME="$AGENT_HOME/../../plugins/oracle.sysman.beacon.agent.plugin_12.1.0.4.0"
AGENT_OH_PLUGIN_HOME="$AGENT_HOME/../../plugins/oracle.sysman.oh.agent.plugin_12.1.0.4.0"
EM_INSTANCE_BASE=`$GREP GCDomain $MW_HOME/domain-registry.xml | sed -e 's/.*=//' | sed -e 's/\/user_projects.*$//' | sed -e 's/"//'`
WL_HOME=`$GREP wlserver $MW_HOME/domain-registry.xml | sed -e 's/.*=//' | sed -e 's/\/samples.*$//' | sed -e 's/"//' | uniq`
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_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_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" ]]; then
echo -en "\tConfirming $OPENSSL_CHECK_PROTO available for $OPENSSL_CHECK_COMPONENT at $OPENSSL_CHECK_HOST:$OPENSSL_CHECK_PORT... "
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_CHECK_PROTO == "ssl2" || $OPENSSL_CHECK_PROTO == "ssl3" ]]; then
echo -en "\tConfirming $OPENSSL_CHECK_PROTO 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
}
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 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 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 2>&1 | $GREP -ci "issuer=/C=US/ST=MyState/L=MyTown/O=MyOrganization/OU=FOR TESTING ONLY/CN=CertGenCAB"`
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.6.0_95" ]]; 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_RETURN=`$GREP $WHICH_PARAM $WHICH_ORACLE_HOME/network/admin/$WHICH_FILE | 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_AES128_CBC_SHA,SSL_RSA_WITH_AES256_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
}
### MAIN SCRIPT HERE
echo -e "Performing EM12cR4 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 -e "\t$OPMN_PROPS"
echo -e "\t$OHS_ADMIN_CONF"
echo
echo -e "\tAgent port found at $OMSHOST:$PORT_AGENT"
echo -e "\tBIPublisher port found at $OMSHOST:$PORT_BIP"
echo -e "\tNodeManager port found at $OMSHOST:$PORT_NODEMANAGER"
echo -e "\tOHSadmin port found at $OMSHOST:$PORT_OHS_ADMIN"
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 "\tOPMN port found at $OMSHOST:$PORT_OPMN"
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 OHSadmin $OMSHOST $PORT_OHS_ADMIN 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 OHSadmin $OMSHOST $PORT_OHS_ADMIN 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) Permit TLSv1 connections"
sslcheck Agent $OMSHOST $PORT_AGENT tls1
sslcheck BIPublisher $OMSHOST $PORT_BIP tls1
sslcheck NodeManager $OMSHOST $PORT_NODEMANAGER tls1
sslcheck OHSadmin $OMSHOST $PORT_OHS_ADMIN 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(2) Checking supported ciphers at SSL/TLS endpoints (see notes 1477287.1, 1905314.1, 1067411.1)"
ciphercheck Agent $OMSHOST $PORT_AGENT
ciphercheck BIPublisher $OMSHOST $PORT_BIP
ciphercheck NodeManager $OMSHOST $PORT_NODEMANAGER
ciphercheck OHSadmin $OMSHOST $PORT_OHS_ADMIN
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 OHSadmin $OMSHOST $PORT_OHS_ADMIN
democertcheck OHSadmin $OMSHOST $PORT_OHS_ADMIN
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 EM12c Oracle home patch levels against $PATCHDATE baseline (see notes 1664074.1, 1900943.1, 822485.1, 1470197.1, 1967243.1)"
#echo -ne "\n\t(4a) OMS ($OMS_HOME) PSU2 Patch 19830994... "
#opatchcheck OMS $OMS_HOME 19830994
#echo -ne "\n\t(4a) OMS ($OMS_HOME) ENTERPRISE MANAGER BASE PLATFORM - OMS 12.1.0.4.3 PSU Patch (20392036)... "
#opatchcheck OMS $OMS_HOME 20392036
#echo -ne "\n\t(4a) OMS ($OMS_HOME) ENTERPRISE MANAGER BASE PLATFORM - OMS 12.1.0.4.4 PSU Patch (20870437)... "
#opatchcheck OMS $OMS_HOME 20870437
echo -ne "\n\t(4a) OMS ($OMS_HOME) ENTERPRISE MANAGER BASE PLATFORM - OMS 12.1.0.4.5 PSU Patch (21462217)... "
opatchcheck OMS $OMS_HOME 21462217
echo -ne "\n\t(4a) OMS HOME ($AGENT_HOME) JDBC Merge Patch (18502187)... "
opatchcheck OMS $OMS_HOME 18502187
#echo -ne "\n\t(4a) OMS ($OMS_HOME) DO NOT CREATE INCIDENT WHEN A COMMAND IS OVER RUN IN JOB WORKER (17714229)... "
#opatchcheck OMS $OMS_HOME 17714229
echo -ne "\n\t(4b) BI Publisher ($BIP_HOME) CPUJAN2015 Patch (19822893)... "
opatchcheck BIP $BIP_HOME 19822893
echo -ne "\n\t(4b) BI Publisher ($BIP_HOME) Merge Patch (20444447)... "
opatchcheck BIP $BIP_HOME 20444447
#echo -ne "\n\t(4b) BI Publisher ($BIP_HOME) ORACLE BI PUBLISHER PATCH BUG FOR PRIVATE EMCC PS3 MANDATORY INSTALL PATCH (17888172)... "
#opatchcheck BIP $BIP_HOME 17888172
echo -ne "\n\t(4c) AS Common ($COMMON_HOME) CVE-2015-0426 Oracle Help Patch (20075252)... "
opatchcheck COMMON $COMMON_HOME 20075252
#echo -ne "\n\t(4c) AS Common ($COMMON_HOME) ADF MERGE REQUEST ON TOP OF 11.1.1.7.1 FOR BUGS 20465665 18820382 20645397 (20747356)... "
#opatchcheck COMMON $COMMON_HOME 20747356
echo -ne "\n\t(4c) AS Common ($COMMON_HOME) WEBCENTER PORTAL BUNDLE PATCH 11.1.1.7.1 (16761779)... "
opatchcheck COMMON $COMMON_HOME 16761779
# Replaced 20747356, commented out above
echo -ne "\n\t(4c) AS Common ($COMMON_HOME) CVE-2015-4742 MERGE REQUEST ON TOP OF 11.1.1.7.1 FOR BUGS 20747356 18274008 (21068288)... "
opatchcheck COMMON $COMMON_HOME 21068288
#echo -ne "\n\t(4d) WebLogic Server ($WL_HOME) 10.3.6.0.10 12UV Patch (19637463)... "
#wlspatchcheck $WL_HOME 19637463
#echo -ne "\n\t(4d) WebLogic Server ($WL_HOME) 10.3.6.0.11 YUIS Patch (20181997)... "
#wlspatchcheck $WL_HOME 20181997
echo -ne "\n\t(4d) WebLogic Server ($WL_HOME) 10.3.6.0.12 EJUW Patch (20780171)... "
wlspatchcheck $WL_HOME 20780171
echo -ne "\n\t(4d) WebLogic Server ($WL_HOME) SU Patch [GDFA]: WEBLOGIC.STORE.PERSISTENTSTOREEXCEPTION: [STORE:280040] OCCURS EASILEY (16420963)... "
wlspatchcheck $WL_HOME 16420963
# Commented this patch out 4/17/2015, as Oracle no longer recommends it for EM12c installations.
# This patch still appears in note 1664074.1 for EM12c.
# Per personal communication w/Oracle I do NOT recommend using it.
#echo -ne "\n\t(4e) WebTier ($WEBTIER_HOME) CPUJAN2015 Patch (19948000)... "
#opatchcheck WebTier $WEBTIER_HOME 19948000
echo -ne "\n\t(4e) WebTier ($WEBTIER_HOME) OHS SECURITY PATCH UPDATE 11.1.1.7.0 CPUOCT2015 Patch (21640624)... "
opatchcheck WebTier $WEBTIER_HOME 21640624
echo -ne "\n\t(4e) WebTier ($WEBTIER_HOME) CVE-2014-4212 OPMN Patch (19345576)... "
opatchcheck WebTier $WEBTIER_HOME 19345576
#echo -ne "\n\t(4e) WebTier ($WEBTIER_HOME) CVE-2013-3836 PLACEHOLDER FOR SECURITY PATCH FOR WEBCACHE 11.1.1.7.0 WITH OCT2013 CPU (17306880)... "
#opatchcheck WebTier $WEBTIER_HOME 17306880
echo -ne "\n\t(4e) WebTier ($WEBTIER_HOME) CVE 2015-2658 MERGE REQUEST ON TOP OF 11.1.1.7.0 FOR BUGS 16370190 20310323 20715657 (20807683)... "
opatchcheck WebTier $WEBTIER_HOME 20807683
echo -ne "\n\t(4e) WebTier ($WEBTIER_HOME) CVE-2013-0169,CVE-2011-3389 OSS SECURITY PATCH UPDATE 11.1.1.7.0 CPUOCT2013 (17337741)... "
opatchcheck WebTier $WEBTIER_HOME 17337741
echo -ne "\n\t(4e) WebTier ($WEBTIER_HOME) WLSPLUGINS (OHS) SECURITY PATCH UPDATE 11.1.1.7.0 CPUJUL2014 (18423831)... "
opatchcheck WebTier $WEBTIER_HOME 18423831
#echo -ne "\n\t(4f) OMS ($OMS_HOME) DB PLUGIN BUNDLE 12.1.0.7.2 (20613714)... "
#opatchautocheck OMS $OMS_HOME 20613714
#echo -ne "\n\t(4f) OMS ($OMS_HOME) DB PLUGIN BUNDLE PATCH 12.1.0.7.3 (20804122)... "
#opatchautocheck OMS $OMS_HOME 20804122
#echo -ne "\n\t(4f) OMS ($OMS_HOME) DB PLUGIN BUNDLE PATCH 12.1.0.7.4 (20950048)... "
#opatchautocheck OMS $OMS_HOME 20950048
#echo -ne "\n\t(4f) OMS ($OMS_HOME) DB PLUGIN BUNDLE PATCH 12.1.0.7.5 (21167937)... "
#opatchautocheck OMS $OMS_HOME 21167937
#echo -ne "\n\t(4f) OMS ($OMS_HOME) DB PLUGIN BUNDLE PATCH 12.1.0.7.6 (21324654)... "
#opatchautocheck OMS $OMS_HOME 21324654
#echo -ne "\n\t(4f) OMS ($OMS_HOME) DB PLUGIN BUNDLE PATCH 12.1.0.7.7 (21506301)... "
#opatchautocheck OMS $OMS_HOME 21506301
#echo -ne "\n\t(4f) OMS ($OMS_HOME) DB PLUGIN BUNDLE PATCH 12.1.0.7.8 (21744938)... "
#opatchautocheck OMS $OMS_HOME 21744938
echo -ne "\n\t(4f) *UPDATED* OMS ($OMS_HOME) DB PLUGIN BUNDLE PATCH 12.1.0.7.10 (22062307)... "
opatchautocheck OMS $OMS_HOME 22062307
#echo -ne "\n\t(4g) OMS ($OMS_HOME) FMW PLUGIN BUNDLE 12.1.0.7.2 (20613870)... "
#opatchautocheck OMS $OMS_HOME 20613870
#echo -ne "\n\t(4g) OMS ($OMS_HOME) FMW PLUGIN BUNDLE PATCH 12.1.0.7.3 (20804213)... "
#opatchautocheck OMS $OMS_HOME 20804213
#echo -ne "\n\t(4g) OMS ($OMS_HOME) FMW PLUGIN BUNDLE PATCH 12.1.0.7.4 (20950040)... "
#opatchautocheck OMS $OMS_HOME 20950040
#echo -ne "\n\t(4g) OMS ($OMS_HOME) FMW PLUGIN BUNDLE PATCH 12.1.0.7.5 (21167980)... "
#opatchautocheck OMS $OMS_HOME 21167980
#echo -ne "\n\t(4g) OMS ($OMS_HOME) FMW PLUGIN BUNDLE PATCH 12.1.0.7.6 (21324861)... "
#opatchautocheck OMS $OMS_HOME 21324861
#echo -ne "\n\t(4g) OMS ($OMS_HOME) FMW PLUGIN BUNDLE PATCH 12.1.0.7.7 (21506335)... "
#opatchautocheck OMS $OMS_HOME 21506335
#echo -ne "\n\t(4g) OMS ($OMS_HOME) FMW PLUGIN BUNDLE PATCH 12.1.0.7.8 (21744989)... "
#opatchautocheck OMS $OMS_HOME 21744989
echo -ne "\n\t(4g) *UPDATED* OMS ($OMS_HOME) FMW PLUGIN BUNDLE PATCH 12.1.0.7.10 (22062375)... "
opatchautocheck OMS $OMS_HOME 22062375
#echo -ne "\n\t(4h) OMS ($OMS_HOME) MOS PLUGIN BUNDLE PATCH 12.1.0.6.4 (20613886)... "
#opatchautocheck OMS $OMS_HOME 20613886
#echo -ne "\n\t(4h) OMS ($OMS_HOME) MOS PLUGIN BUNDLE PATCH 12.1.0.6.5 (20822914)... "
#opatchautocheck OMS $OMS_HOME 20822914
#echo -ne "\n\t(4h) OMS ($OMS_HOME) MOS PLUGIN BUNDLE PATCH 12.1.0.6.6 (21167991)... "
#opatchautocheck OMS $OMS_HOME 21167991
#echo -ne "\n\t(4h) OMS ($OMS_HOME) MOS PLUGIN BUNDLE PATCH 12.1.0.6.7 (21506428)... "
#opatchautocheck OMS $OMS_HOME 21506428
echo -ne "\n\t(4h) OMS ($OMS_HOME) MOS PLUGIN BUNDLE PATCH 12.1.0.6.8 (21745018)... "
opatchautocheck OMS $OMS_HOME 21745018
#echo -ne "\n\t(4i) OMS ($OMS_HOME) EXADATA PLUGIN BUNDLE 12.1.0.6.6 (20613853)... "
#opatchautocheck OMS $OMS_HOME 20613853
#echo -ne "\n\t(4i) OMS ($OMS_HOME) EXADATA PLUGIN BUNDLE PATCH 12.1.0.6.7 (20822866)... "
#opatchautocheck OMS $OMS_HOME 20822866
#echo -ne "\n\t(4i) OMS ($OMS_HOME) EXADATA PLUGIN BUNDLE PATCH 12.1.0.6.8 (20962507)... "
#opatchautocheck OMS $OMS_HOME 20962507
#echo -ne "\n\t(4i) OMS ($OMS_HOME) EXADATA PLUGIN BUNDLE PATCH 12.1.0.6.9 (21167953)... "
#opatchautocheck OMS $OMS_HOME 21167953
#echo -ne "\n\t(4i) OMS ($OMS_HOME) EXADATA PLUGIN BUNDLE PATCH 12.1.0.6.10 (21324852)... "
#opatchautocheck OMS $OMS_HOME 21324852
echo -ne "\n\t(4i) *UPDATED* OMS ($OMS_HOME) EXADATA PLUGIN BUNDLE PATCH 12.1.0.6.11 (21744966)... "
opatchautocheck OMS $OMS_HOME 21744966
#echo -ne "\n\t(4j) OMS CHAINED AGENT HOME ($AGENT_HOME) EM-AGENT BUNDLE 12.1.0.4.7 (20613931)... "
#opatchcheck Agent $AGENT_HOME 20613931
#echo -ne "\n\t(4j) OMS ($OMS_HOME) CFW PLUGIN BUNDLE PATCH 12.1.0.2.1 (20385040)... "
#opatchautocheck OMS $OMS_HOME 20385040
#echo -ne "\n\t(4j) OMS ($OMS_HOME) CFW PLUGIN BUNDLE PATCH 12.1.0.2.2 (21167573)... "
#opatchautocheck OMS $OMS_HOME 21167573
#echo -ne "\n\t(4j) OMS ($OMS_HOME) CFW PLUGIN BUNDLE PATCH 12.1.0.2.3 (21324632)... "
#opatchautocheck OMS $OMS_HOME 21324632
echo -ne "\n\t(4j) *UPDATED* OMS ($OMS_HOME) CFW PLUGIN BUNDLE PATCH 12.1.0.2.4 (21972104)... "
opatchautocheck OMS $OMS_HOME 21972104
#echo -ne "\n\t(4k) OMS CHAINED AGENT HOME ($AGENT_HOME) EM-AGENT BUNDLE PATCH 12.1.0.4.9 (20950034)... "
#opatchcheck Agent $AGENT_HOME 20950034
#echo -ne "\n\t(4k) OMS CHAINED AGENT HOME ($AGENT_HOME) EM-AGENT BUNDLE PATCH 12.1.0.4.10 (21168025)... "
#opatchcheck Agent $AGENT_HOME 21168025
#echo -ne "\n\t(4k) OMS CHAINED AGENT HOME ($AGENT_HOME) EM-AGENT BUNDLE PATCH 12.1.0.4.11 (21325110)... "
#opatchcheck Agent $AGENT_HOME 21325110
#echo -ne "\n\t(4k) OMS CHAINED AGENT HOME ($AGENT_HOME) EM-AGENT BUNDLE PATCH 12.1.0.4.12 (21506284)... "
#opatchcheck Agent $AGENT_HOME 21506284
#echo -ne "\n\t(4k) OMS CHAINED AGENT HOME ($AGENT_HOME) EM-AGENT BUNDLE PATCH 12.1.0.4.13 (21759280)... "
#opatchcheck Agent $AGENT_HOME 21759280
echo -ne "\n\t(4k) *UPDATED* OMS CHAINED AGENT HOME ($AGENT_HOME) EM-AGENT BUNDLE PATCH 12.1.0.4.14 (21913823)... "
opatchcheck Agent $AGENT_HOME 21913823
echo -ne "\n\t(4k) OMS CHAINED AGENT HOME ($AGENT_HOME) Merge Patch (18502187)... "
opatchcheck Agent $AGENT_HOME 18502187
echo -ne "\n\t(4k) OMS CHAINED AGENT HOME ($AGENT_HOME) JDBC Security Patch (18721761)... "
opatchcheck Agent $AGENT_HOME 18721761
if [[ "$HOST_OS" == "Linux" && "$HOST_ARCH" == "x86_64" ]]; then
echo -ne "\n\t(4k) OMS CHAINED AGENT HOME ($AGENT_HOME) CVE 2012-3137 EM Agent only: Instant Client Security Patch (20114054)... "
opatchcheck Agent $AGENT_HOME 20114054
fi
#echo -ne "\n\t(4k) OMS CHAINED AGENT DB PLUGIN ($AGENT_DB_PLUGIN_HOME) DB PLUGIN BUNDLE 12.1.0.7.2 AGENT-SIDE 20676926... "
#opatchcheck AgentDBPlugin $AGENT_DB_PLUGIN_HOME 20676926
#echo -ne "\n\t(4l) OMS CHAINED AGENT DB PLUGIN ($AGENT_DB_PLUGIN_HOME) DB PLUGIN BUNDLE 12.1.0.7.4 AGENT-SIDE MONITORING (21065223)... "
#opatchcheck AgentDBPlugin $AGENT_DB_PLUGIN_HOME 21065223
#echo -ne "\n\t(4l) OMS CHAINED AGENT DB PLUGIN ($AGENT_DB_PLUGIN_HOME) DB PLUGIN BUNDLE 12.1.0.7.5 AGENT-SIDE MONITORING (21229731)... "
#opatchcheck AgentDBPlugin $AGENT_DB_PLUGIN_HOME 21229731
#echo -ne "\n\t(4l) OMS CHAINED AGENT DB PLUGIN ($AGENT_DB_PLUGIN_HOME) DB PLUGIN BUNDLE 12.1.0.7.6 AGENT-SIDE MONITORING (21415075)... "
#opatchcheck AgentDBPlugin $AGENT_DB_PLUGIN_HOME 21415075
#echo -ne "\n\t(4l) OMS CHAINED AGENT DB PLUGIN ($AGENT_DB_PLUGIN_HOME) DB PLUGIN BUNDLE 12.1.0.7.7 AGENT-SIDE MONITORING (21603371)... "
#opatchcheck AgentDBPlugin $AGENT_DB_PLUGIN_HOME 21603371
#echo -ne "\n\t(4l) OMS CHAINED AGENT DB PLUGIN ($AGENT_DB_PLUGIN_HOME) DB PLUGIN BUNDLE 12.1.0.7.8 AGENT-SIDE MONITORING (21806804)... "
#opatchcheck AgentDBPlugin $AGENT_DB_PLUGIN_HOME 21806804
echo -ne "\n\t(4l) *UPDATED* OMS CHAINED AGENT DB PLUGIN ($AGENT_DB_PLUGIN_HOME) DB PLUGIN BUNDLE 12.1.0.7.10 AGENT-SIDE MONITORING (22140476)... "
opatchcheck AgentDBPlugin $AGENT_DB_PLUGIN_HOME 22140476
echo -ne "\n\t(4l) OMS CHAINED AGENT DB PLUGIN ($AGENT_DB_PLUGIN_DISC_HOME) DB PLUGIN BUNDLE 12.1.0.7.4 AGENT-SIDE DISCOVERY (21065239)... "
opatchcheck AgentDBPlugin $AGENT_DB_PLUGIN_DISC_HOME 21065239
#echo -ne "\n\t(4l) OMS CHAINED AGENT FMW PLUGIN ($AGENT_FMW_PLUGIN_HOME) FMW PLUGIN BUNDLE 12.1.0.7.2 AGENT-SIDE MONITORING (20677020)... "
#opatchcheck AgentFMWPlugin $AGENT_FMW_PLUGIN_HOME 20677020
#echo -ne "\n\t(4m) OMS CHAINED AGENT FMW PLUGIN ($AGENT_FMW_PLUGIN_HOME) FMW PLUGIN BUNDLE 12.1.0.7.4 AGENT-SIDE MONITORING (21065760)... "
#opatchcheck AgentFMWPlugin $AGENT_FMW_PLUGIN_HOME 21065760
#echo -ne "\n\t(4m) OMS CHAINED AGENT FMW PLUGIN ($AGENT_FMW_PLUGIN_HOME) FMW PLUGIN BUNDLE 12.1.0.7.5 AGENT-SIDE MONITORING (21229821)... "
#opatchcheck AgentFMWPlugin $AGENT_FMW_PLUGIN_HOME 21229821
#echo -ne "\n\t(4m) OMS CHAINED AGENT FMW PLUGIN ($AGENT_FMW_PLUGIN_HOME) FMW PLUGIN BUNDLE 12.1.0.7.6 AGENT-SIDE MONITORING (21415166)... "
#opatchcheck AgentFMWPlugin $AGENT_FMW_PLUGIN_HOME 21415166
#echo -ne "\n\t(4m) OMS CHAINED AGENT FMW PLUGIN ($AGENT_FMW_PLUGIN_HOME) FMW PLUGIN BUNDLE 12.1.0.7.7 AGENT-SIDE MONITORING (21603497)... "
#opatchcheck AgentFMWPlugin $AGENT_FMW_PLUGIN_HOME 21603497
#echo -ne "\n\t(4m) OMS CHAINED AGENT FMW PLUGIN ($AGENT_FMW_PLUGIN_HOME) FMW PLUGIN BUNDLE 12.1.0.7.8 AGENT-SIDE MONITORING (21806984)... "
#opatchcheck AgentFMWPlugin $AGENT_FMW_PLUGIN_HOME 21806984
#echo -ne "\n\t(4m) OMS CHAINED AGENT FMW PLUGIN ($AGENT_FMW_PLUGIN_HOME) FMW PLUGIN BUNDLE 12.1.0.7.8 AGENT-SIDE MONITORING (21806984)... "
#opatchcheck AgentFMWPlugin $AGENT_FMW_PLUGIN_HOME 21806984
echo -ne "\n\t(4m) *UPDATED* OMS CHAINED AGENT FMW PLUGIN ($AGENT_FMW_PLUGIN_HOME) FMW PLUGIN BUNDLE 12.1.0.7.9 AGENT-SIDE MONITORING (21941290)... "
opatchcheck AgentFMWPlugin $AGENT_FMW_PLUGIN_HOME 21941290
#echo -ne "\n\t(4m) OMS CHAINED AGENT FMW PLUGIN ($AGENT_FMW_PLUGIN_DISC_HOME) FMW PLUGIN BUNDLE 12.1.0.7.2 AGENT-SIDE DISCOVERY (20677038)... "
#opatchcheck AgentFMWPlugin $AGENT_FMW_PLUGIN_DISC_HOME 20677038
#echo -ne "\n\t(4m) OMS CHAINED AGENT FMW PLUGIN ($AGENT_FMW_PLUGIN_DISC_HOME) FMW PLUGIN BUNDLE 12.1.0.7.5 AGENT-SIDE DISCOVERY (21229841)... "
#opatchcheck AgentFMWPlugin $AGENT_FMW_PLUGIN_DISC_HOME 21229841
echo -ne "\n\t(4m) OMS CHAINED AGENT FMW PLUGIN ($AGENT_FMW_PLUGIN_DISC_HOME) FMW PLUGIN BUNDLE 12.1.0.7.7 AGENT-SIDE DISCOVERY (21611921)... "
opatchcheck AgentFMWPlugin $AGENT_FMW_PLUGIN_DISC_HOME 21611921
#echo -ne "\n\t(4n) OMS CHAINED AGENT BEACON PLUGIN ($AGENT_BEACON_PLUGIN_HOME) EM-BEACON BUNDLE PATCH 12.1.0.4.1 (20466772)... "
#opatchcheck AgentBeaconPlugin $AGENT_BEACON_PLUGIN_HOME 20466772
echo -ne "\n\t(4n) *UPDATED* OMS CHAINED AGENT BEACON PLUGIN ($AGENT_BEACON_PLUGIN_HOME) EM-BEACON BUNDLE PATCH 12.1.0.4.2 (21928148)... "
opatchcheck AgentBeaconPlugin $AGENT_BEACON_PLUGIN_HOME 21928148
echo -ne "\n\t(4o) OMS CHAINED AGENT EM-OH BUNDLE PATCH 12.1.0.4.1 (20855134)... "
opatchcheck AgentOHPlugin $AGENT_OH_PLUGIN_HOME 20855134
if [[ $RUN_DB_CHECK -eq 1 ]]; then
# if [[ "$REPOS_DB_VERSION" == "11.2.0.4.0" ]]; then
# echo -ne "\n\t(4m) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) PSU 11.2.0.4.5 19769489... "
# opatchcheck ReposDBHome $REPOS_DB_HOME 19769489
#
# echo -ne "\n\t(4m) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) ORACLE JAVAVM COMPONENT 11.2.0.4.2 DATABASE PSU (JAN2015) 19877440... "
# opatchcheck ReposDBHome $REPOS_DB_HOME 19877440
# fi
if [[ "$REPOS_DB_VERSION" == "11.2.0.4.0" ]]; then
#echo -ne "\n\t(4p) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) PSU 11.2.0.4.6 (APR2015) (20299013)... "
#opatchcheck ReposDBHome $REPOS_DB_HOME 20299013
echo -ne "\n\t(4p) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) PSU 11.2.0.4.8 (OCT2015) (21352635)... "
opatchcheck ReposDBHome $REPOS_DB_HOME 21352635
#echo -ne "\n\t(4p) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) ORACLE JAVAVM COMPONENT 11.2.0.4.3 DATABASE PSU (APR2015) (20406239)... "
#opatchcheck ReposDBHome $REPOS_DB_HOME 20406239
echo -ne "\n\t(4p) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) ORACLE JAVAVM COMPONENT 11.2.0.4.5 DATABASE PSU (OCT2015) (21555791)... "
opatchcheck ReposDBHome $REPOS_DB_HOME 21555791
fi
# if [[ "$REPOS_DB_VERSION" == "12.1.0.2.0" ]]; then
# echo -ne "\n\t(4m) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) Required Patch 20243268... "
# opatchcheck ReposDBHome $REPOS_DB_HOME 20243268
#
# echo -ne "\n\t(4m) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) PSU 12.1.0.2.2 19769480... "
# opatchcheck ReposDBHome $REPOS_DB_HOME 19769480
#
# echo -ne "\n\t(4m) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) ORACLE JAVAVM COMPONENT 12.1.0.2.2 ORACLE JAVAVM COMPONENT 12.1.0.2.2 DATABASE PSU (JAN2015) 19877336... "
# opatchcheck ReposDBHome $REPOS_DB_HOME 19877336
# fi
if [[ "$REPOS_DB_VERSION" == "12.1.0.2.0" ]]; then
echo -ne "\n\t(4p) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) Required Patch (20243268)... "
opatchcheck ReposDBHome $REPOS_DB_HOME 20243268
#echo -ne "\n\t(4p) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) PSU 12.1.0.2.3 (APR2015) (20299023)... "
#opatchcheck ReposDBHome $REPOS_DB_HOME 20299023
echo -ne "\n\t(4p) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) PSU 12.1.0.2.5 (OCT2015) (21359755)... "
opatchcheck ReposDBHome $REPOS_DB_HOME 21359755
#echo -ne "\n\t(4p) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) ORACLE JAVAVM COMPONENT 12.1.0.2.3 DATABASE PSU (APR2015) (20415564)... "
#opatchcheck ReposDBHome $REPOS_DB_HOME 20415564
echo -ne "\n\t(4p) OMS REPOSITORY DATABASE HOME ($REPOS_DB_HOME) ORACLE JAVAVM COMPONENT 12.1.0.2.5 DATABASE PSU (OCT2015) (21555660)... "
opatchcheck ReposDBHome $REPOS_DB_HOME 21555660
fi
echo -ne "\n\t(4q) 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(4q) 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(4q) 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(4q) 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
echo -e "\n(5) Checking EM12c Java versions against baseline (see notes 1506916.1, 1492980.1)"
echo -ne "\n\t(5a) MW ($MW_HOME/jdk16/jdk) Java version 1.6.0_95 (9553040)... "
javacheck MW $MW_HOME/jdk16/jdk 1.6.0_95
echo -ne "\n\t(5b) WebTier ($WEBTIER_HOME/jdk) Java version 1.6.0_95 (9553040)... "
javacheck WebTier $WEBTIER_HOME/jdk 1.6.0_95
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/2015/03/09/em12c-r4-ssl-security-checkup-script/ for the latest version."
echo
exit
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.
Good luck and happy compliance audits!
Oracle Enterprise Manager 12cR4 introduces the new “AWR Warehouse (AWRW)” feature, permitting administrators to consolidate AWR statistics from many individual databases managed by OEM into a single AWRW repository database.
As with all Oracle features, you must pay attention to licensing here. I will not discuss licensing other than to point you to the relevant documents which you must read and understand yourself: Oracle Database Licensing Information 12c Release 1.
At the moment official documentation appears limited to MOS note 1907335.1 and one section of the Oracle Database 2-Day Database + Performance Tuning Guide. Get familiar with it.
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.
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).
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.
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.
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.
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.
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.
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.
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.
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.
See these other fine posts for more information.
Like so many other Oracle DBAs, I need a script to query the total disk space used by an individual table, including the data, indexes and LOBs, that works whether or not the table uses partitioning. I also wanted a script to monitor the progress of DBMS_REDEFINITION actions. Here I provide a single script that does both.
Sample output during a DBMS_REDEFINITION run, with my SAP system name redacted:
SQL> @s
Enter value for segment: reposrc
ACTION TARGET REMAINS PROGRESS
--------------- ----------------------------------- -------- ---------------
Table Scan SAP***.REPOSRC 00:08:45 4.89%
SEGTYPE SEGMENT SIZEMB TABLESPACE
--------------- ----------------------------------- -------- ---------------
1-TABLE SAP***.REPOSRC 3230 PSAP***702
SAP***.REPOSRC#$ 160 PSAP***702
*************** --------
sum 3390
2-INDEX SAP***.REPOSRC^0 136 PSAP***702
SAP***.REPOSRC^SPM 136 PSAP***702
*************** --------
sum 272
3-LOBDATA DATA:SAP***.REPOSRC 3365 PSAP***702
DATA:SAP***.REPOSRC#$ 192 PSAP***702
*************** --------
sum 3557
4-LOBINDEX DATA:SAP***.REPOSRC 0 PSAP***702
DATA:SAP***.REPOSRC#$ 0 PSAP***702
*************** --------
sum 0
--------
sum 7219
The first result block shows the current action (a table scan, in this instance), the name of the table, time remaining in hours:minutes:seconds format and the completion percentage from V$SESSION_LONGOPS. As a side benefit, if you run this against a table that has some other long operation running against it, you will see that here as well. It works for more than just table redefinitions.
The second result block displays the space used by the original table (REPOSRC) and the intermediate table used during DBMS_REDEFINITION (REPOSRC#), along with all segment types in use by both tables (table data, indexes, LOB data and LOB indexes). For the LOB data and indexes, the “SEGMENT” column shows the LOB column name followed by the table name.
Another example of output from the same script, this time for a partitioned table with no LOBs and no redefinition running, from my EM12c repository database:
SQL> @s
Enter value for segment: em_metric_values_daily
SEGTYPE SEGMENT SIZEMB TABLESPACE
--------------- ----------------------------------- -------- ---------------
1-TABLE SYSMAN.EM_METRIC_VALUES_DAILY 327 MGMT_TABLESPACE
*************** --------
sum 327
2-INDEX SYSMAN.EM_METRIC_VALUES_DAILY_PK 48 MGMT_TABLESPACE
*************** --------
sum 48
--------
sum 375
The script:
SET PAGES 30
SET VERIFY OFF
SET FEEDBACK OFF
COLUMN ACTION FORMAT A15
COLUMN TARGET FORMAT A35
COLUMN PROGRESS FORMAT A15
COLUMN REMAINS FORMAT A8
SELECT
OPNAME ACTION,
TARGET,
TO_CHAR(TO_DATE(TIME_REMAINING, 'sssss'), 'hh24:mi:ss') REMAINS,
TO_CHAR(TRUNC(ELAPSED_SECONDS/(ELAPSED_SECONDS+TIME_REMAINING)*100,2))
|| '%' PROGRESS
FROM
V$SESSION_LONGOPS
WHERE
TIME_REMAINING != 0
AND TARGET LIKE UPPER('%&&segment%');
COLUMN SEGTYPE FORMAT A15
COLUMN SEGMENT FORMAT A35
COLUMN SIZEMB FORMAT 9999999
COLUMN TABLESPACE FORMAT A15
BREAK ON SEGTYPE SKIP 1 ON REPORT
COMPUTE SUM OF SIZEMB ON SEGTYPE
COMPUTE SUM OF SIZEMB ON REPORT
SELECT
SEGTYPE,
SEG SEGMENT,
SIZEMB,
TABLESPACE_NAME TABLESPACE
FROM
(
SELECT
'1-TABLE' SEGTYPE,
S.OWNER
|| '.'
|| S.SEGMENT_NAME SEG,
TRUNC(SUM(BYTES)/1024/1024) SIZEMB,
S.TABLESPACE_NAME
FROM
DBA_SEGMENTS S
WHERE
(
S.SEGMENT_NAME = UPPER('&&segment')
OR S.SEGMENT_NAME LIKE UPPER('&&segment#%')
)
AND S.SEGMENT_TYPE LIKE 'TABLE%'
GROUP BY
S.OWNER
|| '.'
|| SEGMENT_NAME,
TABLESPACE_NAME
UNION
SELECT
'2-INDEX' SEGTYPE,
S.OWNER
|| '.'
|| S.SEGMENT_NAME SEG,
TRUNC(SUM(S.BYTES)/1024/1024) SIZEMB,
S.TABLESPACE_NAME
FROM
DBA_SEGMENTS S,
DBA_INDEXES I
WHERE
S.SEGMENT_NAME = I.INDEX_NAME
AND S.SEGMENT_TYPE LIKE 'INDEX%'
AND S.OWNER = I.OWNER
AND
(
I.TABLE_NAME = UPPER('&&segment')
OR I.TABLE_NAME LIKE UPPER('&&segment#%')
)
GROUP BY
S.OWNER
|| '.'
|| S.SEGMENT_NAME,
S.TABLESPACE_NAME
UNION
SELECT
'3-LOBDATA' SEGTYPE,
L.COLUMN_NAME
|| ':'
|| S.OWNER
|| '.'
|| L.TABLE_NAME SEG,
TRUNC(SUM(S.BYTES)/1024/1024) SIZEMB,
S.TABLESPACE_NAME
FROM
DBA_SEGMENTS S,
DBA_LOBS L
WHERE
S.SEGMENT_NAME = L.SEGMENT_NAME
AND
(
S.SEGMENT_TYPE = 'LOBSEGMENT'
OR S.SEGMENT_TYPE LIKE 'LOB %'
)
AND S.OWNER = L.OWNER
AND
(
L.TABLE_NAME = UPPER('&&segment')
OR L.TABLE_NAME LIKE UPPER('&&segment#%')
)
GROUP BY
L.COLUMN_NAME
|| ':'
|| S.OWNER
|| '.'
|| L.TABLE_NAME,
S.TABLESPACE_NAME
UNION
SELECT
'4-LOBINDEX' SEGTYPE,
L.COLUMN_NAME
|| ':'
|| S.OWNER
|| '.'
|| L.TABLE_NAME SEG,
TRUNC(SUM(S.BYTES)/1024/1024) SIZEMB,
S.TABLESPACE_NAME
FROM
DBA_SEGMENTS S,
DBA_LOBS L
WHERE
S.SEGMENT_NAME = L.INDEX_NAME
AND S.SEGMENT_TYPE = 'LOBINDEX'
AND S.OWNER = L.OWNER
AND
(
L.TABLE_NAME = UPPER('&&segment')
OR L.TABLE_NAME LIKE UPPER('&&segment#%')
)
GROUP BY
L.COLUMN_NAME
|| ':'
|| S.OWNER
|| '.'
|| L.TABLE_NAME,
S.TABLESPACE_NAME
)
ORDER BY
SEGTYPE,
SEG ;
UNDEFINE segment;
I based this on a script I initially found at stackoverflow.
I recently reviewed SAP note 740897 and discovered that the application-specific full use license SAP customers receive when they purchase the Oracle database through SAP includes the Database Lifecycle Management Pack. This means I can make use of, among other things, the compliance checking capabilities provided by Oracle Enterprise Manager 12c.
Many of the posts I put up here serve as “how to” documents, explaining how I do something so that others can decide how they would like to do something. This post is slightly different. I will be describing how I currently use the compliance rules, but in addition to simply providing a “how to”, this is more of a plea for anyone who finds this to tell me how this can be done more easily and efficiently. The compliance functionality in EM12c appears to be much more configurable than that provided by EM11g, but one key piece that existed in EM11g appears to be gone. That key piece is the ability to ignore/suppress a particular key value from a compliance check. I would love to have someone tell me that I’m just not finding that function in EM12c.
As I recall, in EM11g, when you had compliance checks enabled you could ignore a single key value. As an example, perhaps you had the rule to flag users with access to select from DBA_* views. That is great, except that my account has the DBA role, so my account appeared as a violation. But I had the ability to ignore any violations on that rule where the key value was my account name. This does not seem to be the case with EM12c. Hence this post, where I describe how I’m achieving similar functionality in a very different way, hoping someone else knows a better way to do it.
The first step to using the EM12c compliance functionality for your databases is to have a license for the Database Lifecycle Management Pack. If you don’t have one already, contact your Oracle sales representative. Note that if you purchased your licenses before Oracle 11g was released, you may have a license to some retired management packs such as the Configuration Management Pack, Change Management Pack, or the Provisioning and Patch Automation Pack. These three legacy packs combined seem to provide most/all of the functionality included in the Database Lifecycle Management Pack and according to the EM12c documentation grant you a license to use the functionality provided by the Database Lifecycle Management Pack. Don’t take my word for it, review the Oracle Enterprise Manager Licensing Information document, particularly sections 2.3, 2.6, 2.7 and 2.8, then consult with your sales contact if you have questions.
Once you have confirmed your entitlement to use this feature, enable the Database Lifecycle Management Pack in EM12c as follows:
Management Pack Access screen
This setup step enables the compliance functionality, but to make use of it you will need to first enable collection of some additional information about your databases, then “attach” your database targets to a “compliance standard”.
Presumably to reduce load on systems where people don’t use the compliance functionality, EM12c does not collect the information needed to make full use of the compliance standards out of the box. You need to enable this collection. To do so:
Applying out-of-box templates to enable security configuration metrics
EM12c uses a three-tier approach to compliance monitoring. For a full understanding of how this works you should read the Oracle Enterprise Manager Cloud Control Oracle Database Compliance Standards documentation, but to summarize it briefly a compliance rule checks a particular compliance item (like permissions on a certain file, or a specific database role), while a compliance standard groups multiple compliance rules into a set to which you then attach the targets you want to have monitored. A compliance framework then groups multiple compliance standards into a superset for reporting/auditing purposes. This gives you a single view of your overall compliance when you have multiple compliance standards applying to different target types, as a compliance standard only applies to one target type — that is, you use a separate compliance standard for your listeners than for your databases, but you then include both standards in your compliance framework for a view of your entire environment. EM12c comes with a large number of pre-built compliance rules, standards and frameworks which you can use as-is if you wish, but read on to find out why I prefer to customize them.
To get started with compliance standards, click the Enterprise menu, then the Compliance submenu, and then click on Library. This will take you to a screen with tabs to move between compliance frameworks, standards and rules. For your first foray into compliance checking, start with one of the simpler Oracle-provided templates, like the “Storage Best Practices for Oracle Database” applicable to Database Instance targets. To find it, click on the Compliance Standards tab, then the little triangle next to the word “Search” at the top of the screen. Type “Storage Best Practices” into the Compliance Standard field, and select Database Instance from the Applicable To drop down, then click the Search button. Once you see that standard on your screen, click on that row of the table (NOT the name of the standard), then click the “Associate Targets” button. This will bring up a screen where you can then click the ‘Add’ button to select one or more of your database instances to attach to the standard. After adding a target, click the OK button. One more pop up window will appear asking you to confirm that you are ready to deploy the association, go ahead and click Yes on this screen.
Searching for a compliance standard and associating targets
You now have at least one target associated to a compliance standard. So what now?
Once you have a target associated to a compliance standard, the main Enterprise Summary page will show an overview of the compliance check results along with a list of your least compliant targets.
Compliance region on Enterprise Summary page
The Compliance Summary region also has a Compliance Frameworks tab which provides another way of viewing the same information — further down I will cover how to set up a framework.
Compliance Summary region, Compliance Framework tab on Enterprise Summary page
For another view, you can also use the Compliance Dashboard, through the Enterprise Menu, Compliance sub-menu, and then clicking on Dashboard.
Compliance Dashboard
Compliance violations are grouped into minor warnings, warnings, and critical violations, based on the configuration of each compliance rule contained in a standard. Depending on your needs, you can change the significance of a violation as appropriate for your environment. I will cover this later as well.
To get some more information about the specific violations Enterprise Manager has found, click on the name of your compliance standard from one of those screens and you will see some more details about what is contained in the compliance standard and the status of your targets. For further detail, click on the name of a compliance rule on the left-hand side. Pardon the blurred text in these images, I have already customized some rules and standards and included my employer name, which I highly recommend doing to distinguish your customizations from the out-of-the-box configuration.
View of compliance standard check details
Drill down into compliance rule details
This page shows that of the three database instances I have associated with this compliance standard, I have only one violation, and that violation is a minor warning associated with the “Non-System Data Segments in System Tablespaces” compliance rule. Because SAP requires that users create some particular segments in the SYSTEM tablespace, this is a good one to work through as an example to show how to customize compliance monitoring to fit your environment.
There are a few different ways to customize your compliance monitoring beyond the high-level decision of which specific targets you associate to each specific standard. One way is to create your own compliance standards, selecting and excluding the compliance rules that are not relevant in your environment — this way, for example, you can complete disable the check for “Non-System Data Segments in System Tablespaces” if you choose to (I wouldn’t, but you might want to). Another way is to customize the specific compliance rules contained in your compliance standards. I do both.
I highly recommend not attempting to edit any of the Oracle-provided compliance frameworks, standards, or rules. The “Create Like” button in the compliance library will be very helpful to you here.
The “Create Like…” button is your friend
First create your own compliance standard by selecting an existing one (I’ll continue to demonstrate this with the “Storage Best Practices for Oracle Database” standard) and clicking on the “Create Like…” button. EM will prompt you to provide a name for the new standard. For simplicity I prefer to use some indicator like my employer’s name followed by the name of the original standard. Click Continue once you have named your new standard and you will proceed to the compliance standard editing page.
Here you specify the rules to include or exclude from your compliance standard
From this page you can add or remove compliance rules from your newly-created compliance standard. To remove a rule, right-click on it in the region on the left and choose “Remove Rule Reference”, then click OK.
You can remove individual rules or groups of rules from this screen
The rules in the predefined standards are grouped into “rule folders”. Instead of removing a single rule, you can remove an entire rule folder if you wish by right-clicking and selecting “Remove Rule Folder” and then clicking OK. You can also create a new rule folder by right-clicking on the name of the compliance standard on the left and selecting “Create Rule Folder”, providing a name, then clicking OK.
Add or remove rule folders to group compliance rules
The compliance standard we’re working with has only a few rules. If you wish, you can add one of the many other rules that are contained in other compliance standards. Right-click on the compliance standard name or a rule folder, and select “Add Rules”. A screen will appear allowing you to select one or more rules to add to the standard. You can scroll through to select your rules or search by name or keyword. Once you click OK, the selected rule(s) will be added to your compliance standard.
Select as many rules to add to your standard as you wish
The compliance standard editing screen is also where you can change the importance of a compliance rule violation. To change the importance of the “Insufficient Redo Log Size” rule from “Normal” to “High”, click on that rule, then the drop-down box next to “Importance” and select a new value.
I guess “Low”, “Normal” and “High” correspond to “Minor Warning”, “Warning” and “Critical”
Finally, click the Save button to save your new compliance standard. At this point your new standard will not have any targets associated with it, so you should click on it and then on the “Associate Targets” button to do so. You may also wish to remove the association of those targets with the original standard you used to create this new standard. Once you finish in this screen, you can return to the Enterprise Summary or Compliance Dashboard, refresh the page, and you should see the results of the checks run by this new rule.
That is all useful, but what if you want to change the actual details behind a rule? I want to get eliminate the complaints about non-system data segments in the system tablespace so that I don’t see any more violations for the SAP-required segments I have in there, but I don’t want to remove the entire rule because I do want to be notified if other segments show up in there that I wasn’t aware of. The solution is create a new rule based on the rule you want to change, edit it (finally we get to write some SQL) and then remove the old rule from your compliance standard and replace it with the new rule.
Go back to the Compliance Dashboard and click the Compliance Standard Rules tab. Open up the search widget and search for “Non-System Data Segments” for target type “Database Instance”. Click on the offending rule and then the “Create Like” button.
The lock icon shows that you can’t edit the default rules but you can duplicate them
Provide a title for your new rule following whatever scheme you like. I will call it “DEMO Non-System Data Segments in System Tablespaces”. Click Continue and you will see the edit screen for Compliance Standard Rules.
You can change the text here if you wish, or add keywords
Click Next to go to step 2 where you can edit the rule SQL.
Finally, SQL!
This screen allows you to edit the rule SQL. If you aren’t familiar with the EM12c repository, this can be difficult. I recommend pulling up a SQL*Plus window connected to your repository database as SYSMAN, then copy/pasting the SQL text into the query window so that you can see the results that it returns. In my case I want to exclude violations for the “SAPUSER” table that SAP requires us to create in the SYSTEM tablespace, so I just add the text “and OBJECT_NAME not like ‘%SAPUSER%’” to the end of the SELECT statement.
Anything you can do in SQL, you can do here
Click Next once you have edited the SQL to your liking. This will bring you to a new screen where you specify the key values and violation conditions. This is one of the clunky parts of working with compliance rules, because the predefined violation condition is lost when you “Create Like” on a built in rule.
What now?
If you just proceed with finishing the rule from here, you’ll have a problem. Every single segment in the SYSTEM and SYSAUX tablespaces will be flagged as a violation. You need a where clause. But what should it be? What was it in the original rule? Here I typically open up a second browser window, navigate to the original rule in the Compliance Library, click the “Show Details” button and then scroll down to the bottom, which brings up the following screen:
At least there’s a way to get the configuration of the original rule
The lucky part here is that, even though the area is grayed out, you can select and copy the text from the original rule’s where clause, then paste that into your new rule’s where clause, as shown below. I’ve also checked the “Key” checkboxes for TABLESPACE_NAME, OBJECT_OWNER, and OBJECT_TYPE, because I suspect (but haven’t yet confirmed) that these key values determine how many individual violation events you will receive.
You can always re-edit this later if you don’t get it perfectly right the first time
Once you click Next on that screen you’ll be presented with step 4, where you can test your new compliance rule against a specific target. You can type in the target’s name or click the magnifying glass to select the target, as with the other target selection screens in EM12c. Click Run Test after you have selected and target and confirm that the results you see are the results you wanted.
Run tests against all your targets one at a time to see what will happen when your rule goes live
If you are satisfied with the test results, click Next. Otherwise click Back and try again with your SQL code and where clause. Once you click Next you will see step 5, which is just a summary page displaying your rule’s details. Click Finish when you are done.
All done, can I go home now?
Now that you clicked Finish, your new compliance standard rule is saved in the repository and available for use. You will need to attach it to a compliance standard, as described above, before it will do anything useful, and you probably want to detach the original rule that you used as the source to create this one.
Repeat these steps for every rule you wish to edit. This is the part I referred to at the beginning of the post where I hoped someone can suggest a better way. As I recall, in EM Grid Control 11g, an admin could simply select a specific compliance violation and choose to suppress it for that key value with a couple of clicks, as compared to this long process needed to duplicate and edit a rule. EM12c compliance rules are very customizable, just not quite as easy to work with — sort of like incident rules and notifications. You need to learn a new way of doing things, but it can do a lot.
Finally, you should create a custom compliance framework. This follows essentially the same process as creating a standard and attaching rules, but instead you create a framework and attach standards. Go to the Compliance Frameworks tab on the Compliance Library page and click “Create”. Give your framework a name and click Continue, and the Compliance Framework edit screen should look familiar.
Where have I seen this before?
Right-click on the compliance framework’s name in the left bar, and select “Add Standards”. A screen will pop up from which you can select the standards you created previously, just like when you add a rule. You can also add standard subgroups, which work much like rule folders. Click on your new standards and then OK.
Easy enough, right?
Click Save and you’ll be returned to the framework tab. At this point your new framework is in “Development” state, and you will NOT see it in the Enterprise Summary page. Click on the framework, then click “Edit”. Change the Compliance Framework State to Production and click Save.
Finally done!
You’re done! You now have a custom compliance framework, one or more custom compliance standards within that framework, and several rules in your standards, including some you have edited to meet your needs. Go back to the Enterprise Summary page, wait a minute or two, click the refresh button and then admire your work.
Time for a cold beer…
The compliance functions in EM12c are extremely customizable and capable. There are a some rough spots where I prefer EM11g’s functionality, and a couple spots where I need to open another browser window or SQL*Plus connection to get things set up the way I want, but that’s a small inconvenience compared to their power.
So now that you have these compliance evaluations staring you in the face every time you visit the Enterprise Summary page, get to work fixing those violations!
(EDITED: 20130903, typos fixed)
This post will cover using EM12cR2 to create a Data Guard configuration including one primary database and one physical standby server, making use of the Data Guard broker. The application software we use does not support logical standby databases so I have not attempted to do so and will not document that here.
As this post focuses specifically on creating a new Data Guard configuration, I will assume you have an existing functional EM12c environment in place along with two servers to use for Data Guard and an existing database which you wish to protect running on one of those servers.
Both servers should exist as promoted targets within EM12c. The existing database (along with its listener and ORACLE_HOME) should exist as promoted targets within EM12c. The standby server should have a software-only installation of the same version and patch level of the Oracle Database (Enterprise Edition only) as exists on the primary server. For simplicity I suggest using the same filesystem paths on both servers, although Data Guard does allow rewrite rules if necessary.
Note that Data Guard is a feature included with an Enterprise Edition license but running a physical standby will require a license for the database software on the standby server. Contact your sales representative for full details. For the purposes of this post I will assume you are using a copy of the database downloaded from OTN for prototyping purposes.
Configure the database as you wish. One point I recommend is to make sure that your redo logs are appropriately sized and that you have enough of them, as adding or resizing redo logs after Data Guard is operational requires some special care.
Now that your environment is set up with a working EM12c installation and one active database that you wish to protect with a Data Guard physical standby, you can proceed. Start by going to the database home page and select ‘Add Standby Database’ from the drop-down menu under ‘Availability’.
Step 1
On the next page, select ‘Create a new physical standby database’ and click continue.
On the next page you select a method to instantiate the physical standby database. Select ‘Online Backup’ and ‘Use Recovery Manager (RMAN) to copy database files’, then click Next.
On the next page you specify the degree of parallelism for the RMAN backup, provide operating system credentials for the user owning the Oracle installation on the primary server (oradgd, in my case) and define the locations of the standby redo logs. The degree of parallelism is up to you and depends on how many CPUs you have and how quickly you need the backup to run. I specify new named credentials here and save them as preferred database host credentials. I recommend clicking the ‘Test’ button to validate the supplied credentials. I do not use Oracle-Managed Files so I have unchecked the box to use them for standby redo log files, which allows me to specify a location for the standby redo logs if I do not like the default. I left these locations at their default. After making your entries on this page, click Next.
On the next page you will specify configuration details for the standby database. All entries on this page relate to the STANDBY server, not the primary. Enter the hostname of the standby server and the path to the Oracle home installation you will use for the standby database. Enter credentials for the Oracle home’s software owner, and again I recommend saving them as preferred credentials and clicking the Test button. The instance name you provide must not already exist on the standby server. I used the same instance name on the standby as on the primary. Click Next after entering all required information.
The next page allows you to select the file locations for the standby database and define the listener parameters. I want to keep things simple with my standby using the same file paths as the primary so I select the radio button labeled “Keep file names and locations the same as the primary database“. If you wish, you can click the ‘Customize’ button and specify alternate file locations for data files, control files, temp files, directories and external files, but keeping everything identical between the two servers will simplify things greatly. I will also use the default listener name and port. Click Next once you have made your selections here.
On this page you specify some final parameters for the standby database such as the DB_UNIQUE_NAME, the name EM12c will use for the standby database target, the location for archived redo log files received from the primary, the size of your FRA and the deletion policy for archived redo log files. For the best monitoring experience, check the ‘Use SYSDBA monitoring credentials’ box. I also suggest you leave the option checked to use the Data Guard Broker. Click Next once you have made your selections here.
The final page you see here will show a review of the settings you have selected through the process. You can see here that I am setting up a standby on Oracle Enterprise Linux while my primary runs on SUSE; this is not a problem for Data Guard. Double check everything to make sure it is all correct, and once you are satisfied, click the Finish button.
As soon as you click Finish on the previous screen, EM12c will start setting up your standby database. You should quickly see a screen like the one below showing that a job has been submitted to create the Data Guard configuration.
If you click on the ‘View Job’ text, you will see the execution log from the job run.
To monitor the job as it proceeds, you can click on the ‘Expand All’ text and then set an auto-refresh interval from the drop-down at the top right. Depending on the size of your database and your server performance, and assuming everything went well, you should soon see that the job has completed successfully.
Once you see the setup job has succeeded, your Data Guard physical standby is now up and running, actively processing redo from your source database. You can verify this by returning to the primary database’s home page and clicking the ‘Availability’ menu, which now has additional options such as ‘Data Guard Administration’, ‘Data Guard Performance’ and ‘Verify Data Guard Configuration’. Click on ‘Data Guard Administration’
The Data Guard administration page shows a summary of your setup. You can see the host running the primary database, the status of your standby(s) and various metrics like the current and last-applied archived log numbers. The various links on this page can then be used to change the protection mode, enable/disable fast start failover and so on. You can also use the ‘Failover’ and ‘Switchover’ buttons to initiate a role transition. Read the documentation so that you understand the difference and know which to use in which situations.
To help convince yourself that all is working properly, set the auto-refresh interval to 30 seconds and leave this page up. Open a sqlplus session on your primary database as sysdba and run “alter system switch logfile”. You should see the log numbers increment once the refresh interval has passed, as shown below.
As a final test, attempt a switchover operation. This will leave your current primary database running as a standby, while your current standby database takes over the primary role. Click on the ‘Switchover’ button. Here you are prompted for credentials on the standby database, which is why I suggested saving them as preferred credentials during the setup process. If you did not do so then, provide appropriate credentials now, then click Continue.
Next you’ll be prompted for credentials for the primary server. Provide those credentials, if necessary, and then click Continue.
Next you will have one final screen to click through to start the switchover process. There is a checkbox to choose whether or not you want to swap monitoring settings between the primary and standby databases. I check the box as this is a good thing, but as the text says you have the option to NOT swap the monitoring settings and instead use your own detailed monitoring templates for each system and apply them after the switchover. I prefer to keep it simple. Once you are ready to go, click Yes, but be aware this will disconnect any sessions active in your primary database.
You will see a progress screen as the switchover occurs.
Once the switchover completes EM12c will return you to the Data Guard Administration page, where you should see that your primary and standby servers have switched roles.
If you have been following along, you now have a functional Data Guard system with a physical standby and have successfully completed one switchover operation. You can repeat this process to add another physical standby database on a third server if you wish. As you look around you’ll also notice a few other changes, such as the additional targets that EM12c added for the standby database, or that the Databases list view has some extra text added that indicates which instance is running as primary and which is running as a standby. Now it’s time to research your needs for Data Guard and get all the remaining bits configured to best support your users. Good luck!
UPDATE 20140227: I am leaving this post here for historical reference, but the version of SLOB I used while writing on it is now fully deprecated. Please go directly to the distribution page for SLOB2 and use the information there to retrieve the latest version of SLOB and learn how to use it, as the usage and functionality have changed. For additional tips, consider Yury’s “SLOB2 Kick start“. Please do not attempt to use the scripts, instructions or techniques I have described here unless you are still using the old version of SLOB. If you are still using the old version, you should not be.
This post covers usage of my SLOB.R script, used to analyze SLOB results from repeated test runs. The contents of the SLOB.R script are at the bottom of this post, but first I will show the contents of a sample SLOB.R session, followed by how you can produce similar results.
The first step is to start R. On Windows you’ll double-click an icon, on Linux you’ll just type R and hit enter. Once R has started, you will be in the interactive R interface and it will display some introductory text standard in R.
R version 2.15.2 (2012-10-26) -- "Trick or Treat" Copyright (C) 2012 The R Foundation for Statistical Computing ISBN 3-900051-07-0 Platform: i386-w64-mingw32/i386 (32-bit) R is free software and comes with ABSOLUTELY NO WARRANTY. You are welcome to redistribute it under certain conditions. Type 'license()' or 'licence()' for distribution details. Natural language support but running in an English locale R is a collaborative project with many contributors. Type 'contributors()' for more information and 'citation()' on how to cite R or R packages in publications. Type 'demo()' for some demos, 'help()' for on-line help, or 'help.start()' for an HTML browser interface to help. Type 'q()' to quit R. >
Once you are in R, you need to load the SLOB.R script and then load your SLOB data. SLOB data is produced by running SLOB repeatedly, saving the awr.txt output in between each run, then running the SLOB/misc/awr_info.sh script to summarize the collected AWR reports. I have saved the SLOB.R script in my Cygwin home directory, and saved the awr_info.sh output in a file called “awrinfo” in the same directory.
> source('C:/cygwin/home/brian/SLOB.R')
> SLOB <- loadSLOB('C:/cygwin/home/brian/awrinfo')
Now you have the contents of your awr_info.sh output in an R variable called SLOB. You can call this variable anything you wish, but I used SLOB.
To use SLOB.R you need to tell it a little bit about your AWR data: specifically which variables you are trying to test and how many sessions you used. In this example I am comparing performance of the XFS and EXT3 filesystems using the SLOB physical read, redo, and physical write models. The variables to be tested (EXT3 and XFS) are embedded in the filenames I used when saving the awr.txt report between SLOB runs, and the session counts are the numbers I used when running SLOB’s runit.sh script to put load on the database. We tell SLOB.R about this by setting a few R variables containing this data. You can call them anything you wish, you just need to know their names.
> SLOB.sessions <- c(8, 16, 32)
> SLOB.read.vars <- c('XFS', 'EXT3')
> SLOB.redo.vars <- c('XFS', 'EXT3')
> SLOB.readwrite.vars <- c('XFS', 'EXT3')
As you can see, variable assignment in R uses the <- operator. I’m using the R built-in c() function (concatenate) to create vector variables that contain multiple values (you can think of them as like an array for now). The SLOB.sessions variable contains three integer values: 8, 16, and 32; the other three variables each contain two string values: ‘XFS’ and ‘EXT3’. For this demo I am only including two variables but it works fine with many more than that. I have been using about 7 of them. I am comparing filesystems, but you might be comparing storage vendor 1 vs storage vendor 2, or fibre channel vs dNFS, or Oracle 11.2.0.1 vs Oracle 11.2.0.3. As long as the variables are identifiable from your AWR filenames, they will work.
You can view the contents of any R variable just by typing its name.
> SLOB.sessions [1] 8 16 32 > SLOB.read.vars [1] "XFS" "EXT3"
With these variables set up you can now use the main SLOB.R driver functions to summarize the contents of your SLOB AWR reports. First I’ll call the SLOBreads() function to summarize physical read performance. This function summarizes the PREADS column from awr_info.sh output, by variable and session count. To produce a better average it discards the lowest value and highest value from each combination of variable and session count. Other SLOB.R driver functions are SLOBredo() and SLOBreadwrite().
> SLOBreads(SLOB, SLOB.reads.vars, SLOB.sessions)
8 16 32 Overall
XFS 27223.89 46248.05 61667.21 44886.22
EXT3 30076.77 49302.59 59113.00 46094.39
So this indicates that for 8 reader sessions, the XFS filesystem gave me an average of 27,223.89 physical reads per second, while EXT3 gave me 30,076.77 physical reads per second. The columns for 16 and 32 sessions have the same meaning as the 8 session column. The ‘Overall’ column is an average of ALL the data points, regardless of session count.
You don’t have to use variables when calling the SLOB.R driver functions. You can specify the variables or session counts directly in the call. This is part of how R works. Another example, showing how you can receive the same output by calling it with a variable or not:
> SLOBredo(SLOB, SLOB.redo.vars, SLOB.sessions)
8 16 32 Overall
XFS 326480426 336665385 321823426 326425272
EXT3 304188997 325307026 326618609 317991362
> SLOBredo(SLOB, c('XFS', 'EXT3'), c(8, 16, 32))
8 16 32 Overall
XFS 326480426 336665385 321823426 326425272
EXT3 304188997 325307026 326618609 317991362
The numbers above would indicate that when storing my redo logs on XFS, SLOB could push 326,480,426 bytes of redo per second with 8 sessions. On EXT3 with 8 sessions I saw 304,188,977 bytes of redo per second. The 16, 32 and Overall columns have meanings similar to what I showed before.
The SLOBreadwrite() function reports the sum of physical reads and physical writes, with the columns all meaning the same as they do for the other functions.
> SLOBreadwrite(SLOB, SLOB.readwrite.vars, SLOB.sessions) 8 16 32 Overall XFS 18520.44 20535.41 20728.07 19823.37 EXT3 19568.04 21730.94 22641.14 21203.78
SLOB.R is smart enough to figure out which of your runs are testing reads, which are testing redo, and which are testing readwrite performance. But for this to work you have to follow the naming convention defined in the SLOB/misc/README file when renaming your awr.txt files for processing by awr_info.sh: [whatever].[number of SLOB writers].[number of SLOB readers] — SLOB.R expects your variables to be uniquely identifiable strings in the ‘whatever‘ part.
I recommend using scripts to run SLOB repeatedly and save the awr.txt output in between. I provided some scripts in a prior post, but you can use your own scripts as long as your filenames match the required format.
Once you have your AWR output collected, run SLOB/misc/awr_info.sh on all the files, and save its output. This is the file you will load into R.
Save this as SLOB.R. You may find it easier to use the pastebin version.
# SLOB.R version 0.6
# BJP 2013 - Twitter @BrianPardy - https://pardydba.wordpress.com/
# See http://wp.me/p2Jp2m-4i for more information
#
#
# Assumes three possible SLOB test models: READS, REDO, WRITES
# READS are readers-only
# REDO and WRITES are writers only, differing in size of buffer_cache (small = REDO, large = WRITES)
#
# Reports PREADS in SLOB.R READS model
# Reports REDO in SLOB.R REDO model
# Reports PWRITES in SLOB.R WRITES model
# Use SLOB.R meta-model READWRITE to report PREADS+PWRITES in WRITES model
#
# Setup:
# Run SLOB as usual, with at least three runs for each variable tested for each model
# Save awr.txt between runs in filename matching [something]-variable.writers.readers
# Example: AWR-FCSAN-run1.8.0, AWR-FCSAN-run2.8.0 (...) AWR-FCSAN-run10.8.0
# AWR-ISCSINAS-run1.8.0, AWR-ISCSINAS-run2.8.0 (...) AWR-ISCSINAS-run10.8.0
# (In this case, the variables would be "FCSCAN" and "ISCSINAS", comparing fibrechannel SAN to NAS)
# Run awr_info.sh from SLOB distribution against all AWR files at the end and save the output
# Load awr_info.sh output into R with: SLOB <- loadSLOB("filename")
#
# Hints:
# Best results achieved with more SLOB runs - myavg() drops high and low values per set, averages remaining
#
# Detailed example usage:
# Testing SLOB read, redo and readwrite models 10 times each with 8, 16, and 32 sessions on EXT3 vs EXT4
# Used a tablespace on EXT3 for EXT3 testing and a tablespace on EXT4 for EXT4 testing
# Used redo logs on EXT3 for EXT3 REDO testing and redo logs on EXT4 for EXT4 REDO testing
# Ran SLOB/misc/awr_info.sh on all awr.txt reports generated from these 60 SLOB runs
# Saved awr_info.sh output as filename "awrinfo"
#
# (Start R)
# > source("SLOB.R")
# > SLOB <- loadSLOB("awrinfo")
# > SLOB.sesscounts < c(8, 16, 32) # Specify the number of sessions used in tests
# > SLOB.read.vars <- c('EXT3', 'EXT4') # Specify the variables for READ testing: EXT3 vs EXT4
# > SLOB.redo.vars <- SLOB.read.vars # Same variables for REDO testing as for READ testing
# > SLOB.readwrite.vars <- SLOB.read.vars # Same variables for READWRITEtesting as for READ testing
# > SLOB.reads <- SLOBreads(SLOB, SLOB.reads.vars, SLOB.sesscounts)
# > SLOB.redo <- SLOBredo(SLOB, SLOB.redo.vars, SLOB.sesscounts)
# > SLOB.readwrite <- SLOBreadwrite(SLOB, SLOB.readwrite.vars, SLOB.sesscounts)
### Previous three lines populate SLOB.reads, SLOB.redo and SLOB.readwrite variables with AWR results
### You can then interrogate those variables by typing their names
# > SLOB.reads
# 8 16 32 Overall
# XFS 27223.89 46248.05 61667.21 44886.22
# EXT3 30076.77 49302.59 59113.00 46094.39
#
#
# Usage variants for more detailed investigation. Consider this advanced usage.
# Most people should just use SLOBreads(), SLOBredo() and SLOBreadwrite()
#
#
# Get average REDO bytes for variable 'foo' across all sessions:
# avg(SLOB, 'REDO', 'foo')
# redoavg(SLOB, 'foo')
#
# Get average REDO bytes for variable 'foo' with 8 sessions:
# avg(SLOB, 'REDO', 'foo', 8)
# redoavg(SLOB, 'foo', 8)
#
# Get average PREADS (physical read) for variable 'foo' across all sessions:
# avg(SLOB, 'READS', 'foo')
# readavg(SLOB, 'foo')
#
# Get average PWRITES (physical writes) for variable 'foo' with 16 sessions:
# avg(SLOB, 'WRITES', 'foo', 16)
# writeavg(SLOB, 'foo', 16)
#
# Get sum of PREADS and PWRITES for variable 'foo' with 32 sessions:
# avg(SLOB, 'READWRITE', 'foo', 32)
# readwriteavg(SLOB, 'foo', 32)
#
# Get average REDO bytes for multiple variables ('foo' and 'bar') across all sessions:
# sapply(c('foo', 'bar'), redoavg, dat=SLOB)
# or for 16 sessions:
# sapply(c('foo', 'bar'), redoavg, dat=SLOB, sessioncount=16)
# alternate: sapply(c('foo', 'bar'), avg, dat=SLOB, sessioncount=16, model='READS')
# (Note: This returns separate results for each variable, it does not combine and average them)
#
# Get sum of PREADS and PWRITES for multiple variables ('XFS' and 'EXT3') across 16 sessions:
# sapply(c('XFS', 'EXT3'), avg, dat=SLOB, sessioncount=16, model='READWRITE')
#
# View the top waits in XFS READ model with 16 sessions:
# waits(SLOB, str='XFS', model='READS', sessioncount=16)
#
# View all data for a particular model with a specific session count:
# SLOB[intersect(REDO(SLOB), sessions(16, SLOB)),]
#
#
getruns <- function(dat, model, str, sessioncount) {
tmp <- intersect(grep(str, dat$FILE), eval(parse(text=paste(model, '(dat)', sep=''))))
if(missing(sessioncount)) { return(tmp)}
else { intersect(tmp, sessions(sessioncount, dat))}
}
loadSLOB <- function(filename) {
read.table(file=filename, sep="|", header=TRUE)
}
# heuristically identify REDO model runs - if this sucks for you, comment it out
# and uncomment the alternate REDO function below. It expects your filenames to include
# the string '-REDO-' when testing REDO performance.
REDO <- function(dat) {
setdiff(which(dat$REDO/(dat$PWRITES*8192) > 2), READS(dat))
}
#REDO <- function(dat) {
# grep('-REDO-', dat$FILE)
#}
WRITES <- function(dat) {
setdiff(which(dat$WRITERS > 0), REDO(dat))
}
READS <- function(dat) {
which(dat$READERS > 0)
}
READWRITE <- function(dat) {
WRITES(dat)
}
sessions <- function(n, dat) {
union(which(dat$WRITERS == n), which(dat$READERS == n))
}
myavg <- function(z) {
mean(z[!z %in% c(min(z), max(z))])
}
getavg <- function(mode, dat) {
myavg(eval(parse(text=paste('dat$', mode, sep=''))))
}
redoavg <- function(dat, ...) {
getavg('REDO', dat[getruns(dat, 'REDO', ...),])
}
readavg <- function(dat, ...) {
getavg('PREADS', dat[getruns(dat, 'READS', ...),])
}
writeavg <- function(dat, ...) {
getavg('PWRITES', dat[getruns(dat, 'WRITES', ...),])
}
readwriteavg <- function(dat, ...) {
getavg('PWRITES', dat[getruns(dat, 'WRITES', ...),]) + getavg('PREADS', dat[getruns(dat, 'WRITES', ...),])
}
avg <- function(dat, model, ...) {
if(model=='REDO') {
getavg('REDO', dat[getruns(dat, 'REDO', ...),])
} else if(model=='READS') {
getavg('PREADS', dat[getruns(dat, 'READS', ...),])
} else if (model=='WRITES') {
getavg('PWRITES', dat[getruns(dat, 'WRITES', ...),])
} else if (model=='READWRITE') {
getavg('PWRITES', dat[getruns(dat, 'WRITES', ...),]) + getavg('PREADS', dat[getruns(dat, 'WRITES', ...),])
}
}
waits <- function(dat, ...) {
as.character(dat[getruns(dat, ...), 'TOP.WAIT'])
}
testdata <- function(dat, model, str, ...) {
if(model=='REDO') {
sapply(str, avg, dat=dat, model='REDO', ...)
} else if(model=='READS') {
sapply(str, avg, dat=dat, model='READS', ...)
} else if(model=='WRITES') {
sapply(str, avg, dat=dat, model='WRITES', ...)
} else if (model=='READWRITE') {
sapply(str, avg, dat=dat, model='READWRITE', ...)
}
}
readdata <- function(dat, str, ...) {
sapply(str, avg, dat=dat, model='READS', ...)
}
redodata <- function(dat, str, ...) {
sapply(str, avg, dat=dat, model='REDO', ...)
}
readwritedata <- function(dat, str, ...) {
sapply(str, avg, dat=dat, model='READWRITE', ...)
}
SLOBreads <- function(dat, strs, sessioncounts) {
z <- data.frame()
for(i in sessioncounts) {
z <- rbind(z, readdata(dat, strs, i))
}
z <- rbind(z, readdata(dat, strs))
z <- t(z)
colnames(z) <- c(sessioncounts, 'Overall')
rownames(z) <- strs
return(z)
}
SLOBredo <- function(dat, strs, sessioncounts) {
z <- data.frame()
for(i in sessioncounts) {
z <- rbind(z, redodata(dat, strs, i))
}
z <- rbind(z, redodata(dat, strs))
z <- t(z)
colnames(z) <- c(sessioncounts, 'Overall')
rownames(z) <- strs
return(z)
}
SLOBreadwrite <- function(dat, strs, sessioncounts) {
z <- data.frame()
for(i in sessioncounts) {
z <- rbind(z, readwritedata(dat, strs, i))
}
z <- rbind(z, readwritedata(dat, strs))
z <- t(z)
colnames(z) <- c(sessioncounts, 'Overall')
rownames(z) <- strs
return(z)
}
That’s it for now. I welcome comments and questions.
UPDATE 20140227: I am leaving this post here for historical reference, but the version of SLOB I used while writing on it is now fully deprecated. Please go directly to the distribution page for SLOB2 and use the information there to retrieve the latest version of SLOB and learn how to use it, as the usage and functionality have changed. For additional tips, consider Yury’s “SLOB2 Kick start“. Please do not attempt to use the scripts, instructions or techniques I have described here unless you are still using the old version of SLOB. If you are still using the old version, you should not be.
This post contains a collection of various tips and tools I have found useful for running SLOB. Where possible I’ve provided attribution for where I first learned of each tip, but some items are simply common sense or have already been posted by multiple people, so I can’t be sure that I have linked every possible source. If you have a write up out there that I’ve missed that you would like linked, please let me know in the comments. I have only focused on running SLOB to test physical read performance, redo generation performance, and mixed workload read/write performance so these tips will only cover those use cases, not other potential uses like testing logical I/O performance. I would be happy to include LIO testing tips if anyone shares some in a comment.
UPDATE 20140227: I am leaving this post here for historical reference, but the version of SLOB I used while writing on it is now fully deprecated. Please go directly to the distribution page for SLOB2 and use the information there to retrieve the latest version of SLOB and learn how to use it, as the usage and functionality have changed. For additional tips, consider Yury’s “SLOB2 Kick start“. Please do not attempt to use the scripts, instructions or techniques I have described here unless you are still using the old version of SLOB. If you are still using the old version, you should not be.
Update 20130212: Please consider this a work in progress. I am re-running most of these tests now using the same LUN for every filesystem to exclude storage-side variance. I am also using some additional init parameters to disable Oracle optimizations to focus the read tests more on db file sequential reads and avoid db file parallel reads. My posts so far document the setup I’ve used and I’m already noting flaws in it. Use this and others’ SLOB posts as a guide to run tests on YOUR system with YOUR setup, for best results.
Update 20130228: I have re-run my tests using the same storage LUN for each test. I have updated the charts, text, and init parameters to reflect my latest results. I have also used a 4k BLOCKSIZE for all redo log testing. Further, instead of running each test three times and taking the average, I have run each test ten times, dropped the highest and lowest values, and averaged the remaining eight, in hopes of gaining a more accurate result. Where I have changed my setup compared to the original post, I have left the old text in, struck out like this.
In Part 1 and Part 1.5 I covered the setup and scripts I’ve used to benchmark performance of various filesystems (and raw devices) using SLOB for Oracle datafiles and redo logs. In this post I will provide more details, including the results and database init parameters in use for my tests. This is an SAP shop so I have several event and _fix_control parameters in place that may have performance impacts. SAP says “use them” so I wanted my tests to reflect the performance I could hope to see through the SAP applications,
I do not intend these tests to demonstrate the maximum theoretical I/O that our hardware can push using each filesystem. I intend them to demonstrate what performance I can expect if I were to change the databases to use each listed filesystem while making minimal (preferably zero) other changes to our environment. I know, for example, that adding more network paths or faster ethernet or splitting the bonded paths will improve Oracle direct NFS performance, but I am testing with what we have available at the moment, not what we COULD have.
Now that 11g is certified on Oracle Linux 6, we are building out an OL6.3 box and I will be running identical tests on that server in a couple weeks. I’m expecting to see some significant improvement in BTRFS performance there.
With that said, some information on the test environment:
Some notes about each test:
Oracle’s standard license prohibits publication of customer publication of benchmark results without express written consent. To avoid any confusion that these results represent a benchmark, I am NOT publishing any of the absolute numbers. Instead I have scaled all results such that the performance of a RAW device is equal to 1, and all other results are reported relative to RAW. I make no claims that these results represent the best possible performance available from my software/hardware configuration. What they should accurately reflect, though, is what someone with a similar setup could expect to see if they were to create redo logs or datafiles on the indicated filesystem without performing any tuning to optimize the database, OS or filesystem.
I am disappointed with the performance of BTRFS. Our OS vendor is deprecating ext4 in favor of BTRFS so if we’re going to abandon ext3 due to fsck issues BTRFS is the path of least resistance. Ext4 appears to provide performance similar to ext3 and should reduce fsck runtimes so if we stay on cooked devices that looks like the way to go. Raw performance won overall (though storage write caching appears to have made ext3 look better than raw) but it has management issues such as the 255-device limit and inability to extend a datafile on a raw device. ASM should provide the same overall performance as raw without the limitations, but adds additional management overhead with the need to install Grid Infrastructure and that just gives one more target to keep up to date on patches. XFS had poor performance for redo logs in my environment but good performance elsewhere, and it should entirely avoid fsck-on-boot problems.
Direct NFS seemed to deliver the most consistent performance from test to test, with a smaller standard deviation than any of the others. This might be relevant for anyone who requires consistent performance more than maximal performance.
Scaled read performance
Scaled redo generation performance
Scaled read/write performance
Below are the init parameters used for each test. Note that filesystemio_options had to be set to none for the XFS redo generation testing, but other than that these parameters are accurate for all the tests.
SLOB.__db_cache_size=34359738368 SLOB.__oracle_base='/oracle/SLOB'#ORACLE_BASE set from environment SLOB.__shared_pool_size=704643072 *._db_block_prefetch_limit=0 *._db_block_prefetch_quota=0 *._db_file_noncontig_mblock_read_count=0 *._disk_sector_size_override=TRUE *._fix_control='5099019:ON','5705630:ON','6055658:OFF','6399597:ON','6430500:ON','6440977:ON','6626018:ON','6972291:ON','8937971:ON','9196440:ON','9495669:ON','13077335:ON'#SAP_112031_201202 RECOMMENDED SETTINGS *._mutex_wait_scheme=1#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *._mutex_wait_time=10#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *._optim_peek_user_binds=FALSE#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *._optimizer_adaptive_cursor_sharing=FALSE#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *._optimizer_extended_cursor_sharing_rel='NONE'#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *._optimizer_use_feedback=FALSE#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *.audit_file_dest='/oracle/SLOB/saptrace/audit' *.compatible='11.2.0.2' *.control_file_record_keep_time=30 *.control_files='/oracle/SLOB/cntrl/cntrlSLOB.dbf','/oracle/SLOB/cntrlm/cntrlSLOB.dbf' *.cpu_count=1 *.db_block_size=8192 *.db_cache_size=33554432 *.db_files=500 *.db_name='SLOB' *.db_recovery_file_dest_size=8589934592 *.db_recovery_file_dest='/oracle/SLOB/fra' *.db_writer_processes=2 *.diagnostic_dest='/oracle/SLOB/saptrace' *.event='10027','10028','10142','10183','10191','10995 level 2','38068 level 100','38085','38087','44951 level 1024'#SAP_112030_201112 RECOMMENDED SETTINGS *.filesystemio_options='SETALL' *.local_listener='LISTENER_SLOB' *.log_archive_dest_1='LOCATION=/oracle/SLOB/arch/SLOBarch' *.log_archive_format='%t_%s_%r.dbf' *.log_buffer=14221312 *.log_checkpoints_to_alert=TRUE *.max_dump_file_size='20000' *.open_cursors=1600 *.optimizer_dynamic_sampling=6 *.parallel_execution_message_size=16384 *.parallel_max_servers=0 *.parallel_threads_per_cpu=1 *.pga_aggregate_target=10737418240 *.processes=800 *.query_rewrite_enabled='FALSE' *.recyclebin='off' *.remote_login_passwordfile='EXCLUSIVE' *.remote_os_authent=TRUE#SAP note 1431798 *.replication_dependency_tracking=FALSE *.resource_manager_plan='' *.sessions=800 *.sga_max_size=10737418240 *.shared_pool_size=5242880000 *.star_transformation_enabled='TRUE' *.undo_retention=432000 *.undo_tablespace='PSAPUNDO'
SLOB.__db_cache_size=34359738368 SLOB.__oracle_base='/oracle/SLOB'#ORACLE_BASE set from environment SLOB.__shared_pool_size=704643072 *._db_block_prefetch_limit=0 *._db_block_prefetch_quota=0 *._db_file_noncontig_mblock_read_count=0 *._disk_sector_size_override=TRUE *._fix_control='5099019:ON','5705630:ON','6055658:OFF','6399597:ON','6430500:ON','6440977:ON','6626018:ON','6972291:ON','8937971:ON','9196440:ON','9495669:ON','13077335:ON'#SAP_112031_201202 RECOMMENDED SETTINGS *._mutex_wait_scheme=1#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *._mutex_wait_time=10#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *._optim_peek_user_binds=FALSE#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *._optimizer_adaptive_cursor_sharing=FALSE#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *._optimizer_extended_cursor_sharing_rel='NONE'#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *._optimizer_use_feedback=FALSE#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *.audit_file_dest='/oracle/SLOB/saptrace/audit' *.compatible='11.2.0.2' *.control_file_record_keep_time=30 *.control_files='/oracle/SLOB/cntrl/cntrlSLOB.dbf','/oracle/SLOB/cntrlm/cntrlSLOB.dbf' *.cpu_count=1 *.db_block_size=8192 *.db_cache_size=34359738368 *.db_files=500 *.db_name='SLOB' *.db_recovery_file_dest_size=8589934592 *.db_recovery_file_dest='/oracle/SLOB/fra' *.db_writer_processes=2 *.diagnostic_dest='/oracle/SLOB/saptrace' *.event='10027','10028','10142','10183','10191','10995 level 2','38068 level 100','38085','38087','44951 level 1024'#SAP_112030_201112 RECOMMENDED SETTINGS *.filesystemio_options='SETALL' *.local_listener='LISTENER_SLOB' *.log_archive_dest_1='LOCATION=/oracle/SLOB/arch/SLOBarch' *.log_archive_format='%t_%s_%r.dbf' *.log_buffer=268427264 *.log_checkpoint_timeout=99999999 *.log_checkpoints_to_alert=TRUE *.max_dump_file_size='20000' *.open_cursors=1600 *.optimizer_dynamic_sampling=6 *.parallel_execution_message_size=16384 *.parallel_max_servers=0 *.parallel_threads_per_cpu=1 *.pga_aggregate_target=10737418240 *.processes=800 *.query_rewrite_enabled='FALSE' *.recyclebin='off' *.remote_login_passwordfile='EXCLUSIVE' *.remote_os_authent=TRUE#SAP note 1431798 *.replication_dependency_tracking=FALSE *.resource_manager_plan='' *.sessions=800 *.sga_max_size=68719476736 *.shared_pool_size=5242880000 *.star_transformation_enabled='TRUE' *.undo_retention=432000 *.undo_tablespace='PSAPUNDO'
SLOB.__db_cache_size=34359738368 SLOB.__oracle_base='/oracle/SLOB'#ORACLE_BASE set from environment SLOB.__shared_pool_size=704643072 *._db_block_prefetch_limit=0 *._db_block_prefetch_quota=0 *._db_file_noncontig_mblock_read_count=0 *._disk_sector_size_override=TRUE *._fix_control='5099019:ON','5705630:ON','6055658:OFF','6399597:ON','6430500:ON','6440977:ON','6626018:ON','6972291:ON','8937971:ON','9196440:ON','9495669:ON','13077335:ON'#SAP_112031_201202 RECOMMENDED SETTINGS *._mutex_wait_scheme=1#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *._mutex_wait_time=10#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *._optim_peek_user_binds=FALSE#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *._optimizer_adaptive_cursor_sharing=FALSE#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *._optimizer_extended_cursor_sharing_rel='NONE'#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *._optimizer_use_feedback=FALSE#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798 *.audit_file_dest='/oracle/SLOB/saptrace/audit' *.compatible='11.2.0.2' *.control_file_record_keep_time=30 *.control_files='/oracle/SLOB/cntrl/cntrlSLOB.dbf','/oracle/SLOB/cntrlm/cntrlSLOB.dbf' *.cpu_count=1 *.db_block_size=8192 *.db_cache_size=134217728 *.db_files=500 *.db_name='SLOB' *.db_recovery_file_dest_size=8589934592 *.db_recovery_file_dest='/oracle/SLOB/fra' *.db_writer_processes=2 *.diagnostic_dest='/oracle/SLOB/saptrace' *.event='10027','10028','10142','10183','10191','10995 level 2','38068 level 100','38085','38087','44951 level 1024'#SAP_112030_201112 RECOMMENDED SETTINGS *.filesystemio_options='SETALL' *.local_listener='LISTENER_SLOB' *.log_archive_dest_1='LOCATION=/oracle/SLOB/arch/SLOBarch' *.log_archive_format='%t_%s_%r.dbf' *.log_buffer=268427264 *.log_checkpoint_timeout=99999999 *.log_checkpoints_to_alert=TRUE *.max_dump_file_size='20000' *.open_cursors=1600 *.optimizer_dynamic_sampling=6 *.parallel_execution_message_size=16384 *.parallel_max_servers=0 *.parallel_threads_per_cpu=1 *.pga_aggregate_target=10737418240 *.processes=800 *.query_rewrite_enabled='FALSE' *.recyclebin='off' *.remote_login_passwordfile='EXCLUSIVE' *.remote_os_authent=TRUE#SAP note 1431798 *.replication_dependency_tracking=FALSE *.resource_manager_plan='' *.sessions=800 *.sga_max_size=34359738368 *.shared_pool_size=5242880000 *.star_transformation_enabled='TRUE' *.undo_retention=432000 *.undo_tablespace='PSAPUNDO'
I have removed the content of this section on 20130228 as it related to my previous results and not the updated results now contained in this posting.
UPDATE 20140227: I am leaving this post here for historical reference, but the version of SLOB I used while writing on it is now fully deprecated. Please go directly to the distribution page for SLOB2 and use the information there to retrieve the latest version of SLOB and learn how to use it, as the usage and functionality have changed. For additional tips, consider Yury’s “SLOB2 Kick start“. Please do not attempt to use the scripts, instructions or techniques I have described here unless you are still using the old version of SLOB. If you are still using the old version, you should not be.
Minor update 20130207: Adjusted read-write testing script for three redo logs, not two.
In Part 1 I described a starter methodology for using SLOB to benchmark filesystem performance for Oracle. After many SLOB runs and much valuable advice from Kevin Closson, I now have an improved method. My next post will contain the results of these tests, with all numbers scaled against RAW performance for the sake of comparison. Since my hardware and network don’t match yours, my specific numbers aren’t very relevant; but for a DBA or SA considering changing the filesystem they use for Oracle without a priori knowledge of how to best tune it, this will hopefully present a useful baseline.
Previously I was bouncing the database, dropping and recreating the SLOB users before every test run. Now I am initializing the test batches for each filesystem/volume by bouncing the database, creating the users and executing a throwaway SLOB run to prime the database.
Below are the scripts I am using to test read-only, redo generation, and read-write performance. If you use any of them, edit the scripts to reflect your base directory (the oracle user’s home), SLOB install directory and desired location for SLOB AWR output. Before running any of the scripts, prepare your environment by creating a tablespace on each filesystem/device type that you wish to test. Name the tablespace something that will make it clear to you which filesystem you are testing. I used a single 16GB datafile for each tablespace. Each script runs SLOB setup.sh to create the users and then SLOB runit.sh to execute the tests. After an initial throwaway run to warm things up, they will run SLOB three times each with 8, 16, and 32 sessions. You can adjust the session count by changing the for loops. At the end of each script the awr.txt file generated by SLOB is renamed and moved to the $SLOBAWR directory, using the filename convention expected by the awr_info.sh script (found in the latest SLOB release.) All tests are performed in NOARCHIVELOG mode, don’t do that on a database that you need. The scripts assume that you are executing them from within the SLOB directory.
Before running the read-only test, I created two 32G redo logs on raw devices. The read-only test should not hit redo at all so where you place them doesn’t really matter. For read-only testing you should use a very small db_cache_size, I used 64M. The small cache will make sure that reads are fulfilled through physical I/O rather than cached.
#!/bin/sh
#
# Usage: ./testread.sh FS RUNCOUNT
#
# Script assumes a tiny SGA to force physical reads
# Script will create SLOB schema users in tablespace named $FS, and run each test $RUNCOUNT times
TYPE=$1
NUMRUNS=$2
BASEDIR=/oracle/SLOB
SLOBDIR=$BASEDIR/SLOB
DROPUSERS=$SLOBDIR/drop_users.sql
AWRDIR=$SLOBDIR/SLOBAWR
COUNTER=1
if [ -z "$TYPE" -o -z "$NUMRUNS" ]; then
echo "Usage: $0 FS RUNCOUNT"
exit 1
fi
mkdir $AWRDIR >& /dev/null
echo "Starting SLOB read-only performance testing for $TYPE ($NUMRUNS runs)"
echo "Dropping existing users and bouncing database"
sqlplus -s / as sysdba<<EOF
@$DROPUSERS;
shutdown immediate;
startup;
exit;
EOF
echo "Setting up SLOB user schemata"
$SLOBDIR/setup.sh $TYPE 32
echo "Performing N+1 logswitch"
sqlplus -s / as sysdba<<EOF2
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
exit;
EOF2
echo "Throwaway SLOB run to prime database"
$SLOBDIR/runit.sh 0 8
for i in 8 16 32; do (
while [ $COUNTER -le $NUMRUNS ]; do
echo "Running SLOB for $i readers (run #$COUNTER)"
$SLOBDIR/runit.sh 0 $i
echo "Renaming AWR report"
mv awr.txt $AWRDIR/SLOB-AWR-$TYPE-$COUNTER.0.$i
COUNTER=$((COUNTER+1))
done )
done
Before running the redo generation test, I created two 32G redo logs the filesystem whose performance I wish to test. For redo testing you should use a large db_cache_size; I used 32G. I also increased log_buffer to reduce log buffer waits, but I have not been able to eliminate them entirely. SAP requires specific log_buffer settings so I don’t want to deviate from them too much, as I want this test to have at least some similarity to our production performance. Prior to each test run I performed N+1 log switches (where N is the number of configured redo logs) to clear out any pending redo.
One possible improvement others may wish to consider when testing redo generation performance would be to configure your UNDO tablespace to use the same filesystem type as your redo logs. I did not do so, so my max performance is somewhat constrained by writes to UNDO. Each test should be similarly constrained so I am not concerned about that at the moment.
#!/bin/sh
#
# Usage: ./testredo.sh FS RUNCOUNT
#
# Script assumes you have pre-created exactly two redo logs on the filesystem/device to be tested
# Script will create SLOB schema users in tablespace named $FS, and run each test $RUNCOUNT times
TYPE=$1
NUMRUNS=$2
BASEDIR=/oracle/SLOB
SLOBDIR=$BASEDIR/SLOB
DROPUSERS=$SLOBDIR/drop_users.sql
AWRDIR=$SLOBDIR/SLOBAWR
COUNTER=1
if [ -z "$TYPE" -o -z "$NUMRUNS" ]; then
echo "Usage: $0 FS RUNCOUNT"
exit 1
fi
mkdir $AWRDIR >& /dev/null
echo "Starting SLOB redo generation performance testing for $TYPE ($NUMRUNS runs)"
echo "Assuming two redo logs exist"
echo "Dropping existing users and bouncing database"
sqlplus -s / as sysdba<<EOF
@$DROPUSERS;
shutdown immediate;
startup;
exit;
EOF
echo "Setting up SLOB user schemata"
$SLOBDIR/setup.sh $TYPE 32
echo "Throwaway SLOB run to prime database"
$SLOBDIR/runit.sh 8 0
for i in 8 16 32; do (
while [ $COUNTER -le $NUMRUNS ]; do
echo "Running SLOB for $i writers (run #$COUNTER)"
echo "Performing N+1 logswitch"
sqlplus -s / as sysdba<<EOF2
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
exit;
EOF2
$SLOBDIR/runit.sh $i 0
echo "Renaming AWR report"
mv awr.txt $AWRDIR/SLOB-AWR-$TYPE-$COUNTER.$i.0
COUNTER=$((COUNTER+1))
done )
done
Before running the read-write performance test, I replaced my redo logs with three 64M files on raw devices. I also decreased db_cache_size to 128M to help make sure reads are fulfilled from physical disk instead of cache. I left log_buffer as-is from the redo testing.
#!/bin/sh
#
# Usage: ./testrw.sh FS RUNCOUNT
#
# Script assumes you have pre-created exactly three small (64M) redo logs
# Script will create SLOB schema users in tablespace named $FS, and run each test $RUNCOUNT times
TYPE=$1
NUMRUNS=$2
BASEDIR=/oracle/SLOB
SLOBDIR=$BASEDIR/SLOB
DROPUSERS=$SLOBDIR/drop_users.sql
AWRDIR=$SLOBDIR/SLOBAWR
COUNTER=1
if [ -z "$TYPE" -o -z "$NUMRUNS" ]; then
echo "Usage: $0 FS RUNCOUNT"
exit 1
fi
mkdir $AWRDIR >& /dev/null
echo "Starting SLOB read/write performance testing for $TYPE ($NUMRUNS runs)"
echo "Assuming three redo logs exist"
echo "Dropping existing users and bouncing database"
sqlplus -s / as sysdba<<EOF
@$DROPUSERS;
shutdown immediate;
startup;
exit;
EOF
echo "Setting up SLOB user schemata"
$SLOBDIR/setup.sh $TYPE 32
echo "Throwaway SLOB run to prime database"
$SLOBDIR/runit.sh 4 4
for i in 8 16 32; do (
while [ $COUNTER -le $NUMRUNS ]; do
echo "Running SLOB for $i read/write sessions (run #$COUNTER)"
echo "Performing N+1 logswitch"
sqlplus -s / as sysdba<<EOF2
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
exit;
EOF2
READERS=$((i/2))
WRITERS=$READERS
$SLOBDIR/runit.sh $WRITERS $READERS
echo "Renaming AWR report"
mv awr.txt $AWRDIR/SLOB-AWR-$TYPE-$COUNTER.$WRITERS.$READERS
COUNTER=$((COUNTER+1))
done )
done
According to MOS note 1304727.1, Oracle Database 11.2.0.3 and up is now certified on Oracle Linux 6.x! I’ve been waiting for this.
UPDATE 20140227: I am leaving this post here for historical reference, but the version of SLOB I used while writing on it is now fully deprecated. Please go directly to the distribution page for SLOB2 and use the information there to retrieve the latest version of SLOB and learn how to use it, as the usage and functionality have changed. For additional tips, consider Yury’s “SLOB2 Kick start“. Please do not attempt to use the scripts, instructions or techniques I have described here unless you are still using the old version of SLOB. If you are still using the old version, you should not be.
Update: Please see Part 1.5 for updated scripts and part 2 for results.
This post will cover techniques I’ve used to run SLOB (see http://kevinclosson.wordpress.com/2012/02/06/introducing-slob-the-silly-little-oracle-benchmark/) to benchmark the performance of various filesystems and raw devices when used for Oracle datafiles and redo logs. I will not write about the actual results, since they’re only relevant to my specific hardware and software setup (spoiler alert: raw wins), but instead discuss the methodology and database parameters and configurations I used in hopes that they will help others to run their own tests, or that others will notice flaws I can remedy to produce more correct results. Likewise, this post is not about specific tuning parameters for individual filesystems, but instead about a way to run the tests to compare performance from one set of tuning parameters to another.
After you have downloaded and installed SLOB, you need to get a good idea about exactly what you want to test and which metrics you will look at to determine the results. In my case we have been having some OS issues resulting in unclean shutdowns that lead to long fsck times on our Oracle data volumes, so I am investigating alternative filesystems to try to find something with performance matching or beating ext3 that won’t be subject to fsck on boot. I also chose to run tests to compare the redo logging subsystem’s performance when redo is stored on various filesystems or raw devices.
So I am running three different tests:
For each test I first needed to identify an appropriate metric. For read-only performance the obvious choice is physical reads per second. For concurrent read-write performance I measured the sum of physical reads per second and physical writes per second. For redo performance I measured redo generated per second.
After selecting your metrics you next need to determine how to configure the database to make sure you are testing what you wanted to test. To that end, I configured the database as described below. If you want to use SLOB to test other aspects of performance you need to monitor the wait events noted in your AWR reports to be sure that the database isn’t doing something you don’t really want to test. For example, if you are running a ton of SLOB tests overnight and the autotask window kicks in and the SQL Tuning Advisor or Segment Advisor start running, those will skew your results so you may wish to disable those tasks.
Each test requires a different set of initialization parameters and other configuration to isolate the desired variable (filesystem or raw device, in my case). I think the parameters I used are valid for the tests I ran, but I am very interested in any comments from others. For each of the various filesystems I wanted to test, the storage admin created a LUN, mounted it to the server, and created the filesystem (or configured the raw device). I put a separate tablespace on each LUN, each containing a single 10GB datafile.
This is the easiest item to force. I want the database to fulfill read requests from disk rather than from the buffer cache, so I simply took my existing baseline configuration (as used for our SAP systems) and set db_cache_size to 64M. With such a small buffer cache only minimal data will be cached, and the majority of reads will come from disk. You can confirm this in the SLOB-generated AWR report by verifying that the number of physical reads per second is relatively close to the number of logical reads per second. For example, if you show 20,000 logical reads per second and only 100 physical reads per second, you haven’t done it right as most gets are coming from cache. You may need to lie to Oracle about your CPU_COUNT to get your buffer cache small enough to force physical read I/O.
To run these tests in SLOB, I used 8, 16, and 32 concurrent user read sessions and zero writers.
This one took a lot of effort for me to come up with a setup that measured what I wanted. Initial efforts showed significant waits on log buffer space or cache buffers chains and a lot of redo generation without corresponding physical writes so I had to tweak things until I found a setup that (mostly) produced AWR reports with “db file sequential read”, “db file parallel read”, “write complete waits” and “DB CPU” within the top waits.
I eventually settled on a db_cache_size of 128M to force read I/O to physical while allowing writes to occur without waiting on cache buffer chains. I set log_buffer=1073741824 to reduce log buffer waits on writes, though Oracle seems to have limited the log buffer to 353,560K as reported in the AWR reports. I created three 64M redo logs on raw devices and ran the database in NOARCHIVELOG mode to force frequent checkpoints and corresponding physical writes to datafiles and exclude any I/O spent on archiving redo logs.
To run these tests in SLOB, I used 8, 16, and 32 concurrent user sessions, with half of them as readers and half of them as writers.
For this test I wanted to purely measure the amount of redo generated while excluding datafile checkpoints. I set db_cache_size to 32G to allow reads to come from cache and created three 4G redo log groups on each tested filesystem, intending the full test block to run without causing a log switch that would force a checkpoint.
To run these tests in SLOB, I used 8, 16, and 32 concurrent user write sessions, with zero readers.
Setting up each SLOB run involves running the provided setup.sh script to create the users, schemata and data for each test, then running the provided runit.sh script with parameters to indicate the desired number of readers and writers. For example, to use 32 different users with their data in tablespace TABLESPACENAME, and then run SLOB with 16 writers and 16 readers, you would run:
./setup.sh TABLESPACENAME 32 ; ./runit.sh 16 16
After the run SLOB will produce an AWR report which you should review to see the results of your test run. SLOB also produces a drop_users.sql script to clear out the users generated by setup.sh, and you should run that script and re-create your users anytime you change the user count. A benchmark run only once has no validity, and a benchmark with nothing to compare to is useless, so you’ll want to create some scripts to run SLOB repeatedly, saving the AWR reports in between so that you can review the overall results.
Here is the content of the script I used to automate individual SLOB runs, with discussion after:
#!/bin/sh
# harness to run SLOB
FS=$1
NUMUSERS=$2
NUMWRITERS=$3
NUMREADERS=$4
RUNNUM=$5
RUNDIR="run${RUNNUM}"
echo "Dropping users and bouncing DB"
sqlplus -s / as sysdba<<EOF
@drop_users
alter system switch logfile;
alter system checkpoint;
shutdown immediate;
exit;
EOF
rm /oracle/SLOB/arch/SLOB*dbf
sqlplus -s / as sysdba<<EOF2
startup;
exit;
EOF2
echo "Setting up users for $FS $NUMUSERS"
./setup.sh $FS $NUMUSERS
echo "Running SLOB: $FS $NUMUSERS $NUMWRITERS $NUMREADERS (run $RUNNUM)"
./runit.sh $NUMWRITERS $NUMREADERS
echo "Renaming AWR report"
mv awr.txt $RUNDIR/SLOB-AWR-$FS-$NUMUSERS-$NUMWRITERS-$NUMREADERS-$RUNDIR.txt
To provide a consistent environment for each test, I drop the previous test’s users, force a redo log switch and checkpoint, and then bounce the database. I also remove any archived logs generated by the previous run so I don’t run out of space (this is a test DB I don’t need, don’t do that on any DB you can’t afford to lose). This script takes five arguments: the tablespace name, the number of concurrent users, the number of writers, the number of readers, and the run number for each combination. I’ve named my tablespaces after the filesystem (“BTRFS”, “EXT3”, “EXT4”, “XFS”, “RAWTS”, and so on) so when it calls setup.sh the user data is created within the appropriate tablespace on the filesystem to be tested. The last line renames SLOB’s awr.txt to reflect the filesystem, user count, number of readers/writers and run number.
I save this script as mydoit.sh and create another script to call it repeatedly. This example is for the read-only testing:
#!/bin/sh echo "Starting runs for 8 readers" ./mydoit.sh EXT3 8 0 8 1 ./mydoit.sh EXT4 8 0 8 1 ./mydoit.sh BTRFS 8 0 8 1 ./mydoit.sh XFS 8 0 8 1 ./mydoit.sh RAWTS 8 0 8 1 ./mydoit.sh EXT3 8 0 8 2 ./mydoit.sh EXT4 8 0 8 2 ./mydoit.sh BTRFS 8 0 8 2 ./mydoit.sh XFS 8 0 8 2 ./mydoit.sh RAWTS 8 0 8 2 ./mydoit.sh EXT3 8 0 8 3 ./mydoit.sh EXT4 8 0 8 3 ./mydoit.sh BTRFS 8 0 8 3 ./mydoit.sh XFS 8 0 8 3 ./mydoit.sh RAWTS 8 0 8 3 echo "Starting runs for 16 readers" ./mydoit.sh EXT3 16 0 16 1 ./mydoit.sh EXT4 16 0 16 1 ./mydoit.sh BTRFS 16 0 16 1 ./mydoit.sh XFS 16 0 16 1 ./mydoit.sh RAWTS 16 0 16 1 ./mydoit.sh EXT3 16 0 16 2 ./mydoit.sh EXT4 16 0 16 2 ./mydoit.sh BTRFS 16 0 16 2 ./mydoit.sh XFS 16 0 16 2 ./mydoit.sh RAWTS 16 0 16 2 ./mydoit.sh EXT3 16 0 16 3 ./mydoit.sh EXT4 16 0 16 3 ./mydoit.sh BTRFS 16 0 16 3 ./mydoit.sh XFS 16 0 16 3 ./mydoit.sh RAWTS 16 0 16 3 # and so on for 32 concurrent sessions...
After this script runs I have a pile of AWR reports to review and compare their read performance. For each metric I’m interested in I saved the results from each run into a spreadsheet and generated average results for each set. That gives me an average number across three runs for each filesystem’s physical read I/O per second for 8 concurrent users, 16 concurrent users, and 32 concurrent users. Similar scripts run for the redo-only testing and for the read/write testing, with the results logged in the same way. I then generate charts within the spreadsheet to visually compare the results. Some performance trends are very obvious visually and we are already following up with our OS vendor for some more information.
Pardy DBA 