Tag Archives: demo

OEM 12c SSL Certificate Swapping (HowTo)

"This Connection is Untrusted" error message
“This Connection is Untrusted” error message

Oracle Enterprise Manager out of the box, comes with demonstration SSL certificates that are generally okay for getting the basic system up and running, but should not be left as your long-term solution for SSL/HTTPS connections to your Oracle Management Server (OMS).

Similar to how e-Business Suite installations delivered a DEMO Certificate Authority certificate with the bundled Internet Application Server (iAS) installation, OEM packages do the same thing.  But eventually, forced by browser and client workstation OS upgrades, you will eventually need to install “real” certificates by a true trusted Root Certification Authority (RCA) so that your client browsers don’t begin rejecting encrypted connections to your OMS.

If you search for SSL Certificate authorities, there are many well-known public RCA’s  such as, DigiCert, Verisign, Thawte, GeoTrust, and others, or even those available from your domain registrar.  Larger organizations probably have their own Certificate Authority signing server on-premise that allow generation of trusted certificates, as well. The only really important thing is that the CA is actually available in your browser and OS as a Trusted Root Authority, and that the signing chain is verifiable to prevent issues with SSL/HTTPS handshaking.  For each middle-tier OMS host, or virtual host if you are set up for high-availability with multiple WebLogic servers, a certificate request is generated, signed and then imported back into the keychains related to the OMS Weblogic hosts, and the OMS Servicing Agents (the OEM Agents installedo on the OMS middle-tier hosts.)

Once a new certificate is installed to the OMS itself (in WebLogic), you will also need to install the related RCA to the OMS-side OEM Agent servicing all of the connections to the other OEM Agents, so that they too, will be SSL enabled.

# OEM SSL Certificate swapping
#    EM 12c Cloud Control: How to Create a Wallet With Third Party Trusted Certificate that Can Be Imported into the OMS Console application ? (Doc ID 1937457.1)
#     EM 12c: Steps to Create and Import Third Party / Self-Signed SSL Certificates for WebLogic Server in an Enterprise Manager 12c Cloud Control Installation (Doc ID 1527874.1)
STAGE_DIR=/mnt/nfs/FMW/certs
EM_INSTANCE_HOME=/oemgc/Oracle/gc_inst2/em/EMGC_OMS1      #WebTierIH2 OHS/ohs2 on (alternate hostname

oemmgr@(primary hostname) $> cd $STAGE_DIR

oemmgr@(primary hostname) $> cat import.sh
export JAVA_JREBIN=$JAVA_HOME/jre/bin
export CERTS=/mnt/nfs/FMW/certs
$JAVA_JREBIN/keytool -import -file $CERTS/ORGPOLICYCA.cer -trustcacerts -alias ORGROOTCA -storepass changeit -noprompt  -keystore $JAVA_HOME/jre/lib/security/cacerts
$JAVA_JREBIN/keytool -import -file $CERTS/ORGROOTCA.cer -trustcacerts -alias ORGRootPolicyCA -storepass changeit -noprompt   -keystore $JAVA_HOME/jre/lib/security/cacerts
$JAVA_JREBIN/keytool -import -file $CERTS/ORGHOSTISSUECA1.cer -trustcacerts -alias ORGHOSTissueca1 -storepass changeit -noprompt  -keystore  $JAVA_HOME/jre/lib/security/cacerts

oemmgr@(primary hostname) $> . ./import.sh
Certificate was added to keystore
Certificate was added to keystore
Certificate was added to keystore

# Determine keystore directory (found by locating your Oracle Home Service (OHS) installation filesystem)
oemmgr@(primary hostname) $> ps -ef | grep ohs
oemmgr     873  9334  0 Jan13 ?        00:47:48 /oemgc/Oracle/MW3/Oracle_WT/ohs/bin/httpd.worker -DSSL
oemmgr    9334  9305  0 Jan13 ?        00:00:06 /oemgc/Oracle/MW3/Oracle_WT/ohs/bin/httpd.worker -DSSL
oemmgr    9342  9334  0 Jan13 ?        00:00:11 /oemgc/Oracle/MW3/Oracle_WT/ohs/bin/odl_rotatelogs -l /oemgc/Oracle/gc_inst2/WebTierIH1/diagnostics/logs/OHS/ohs1/ohs1-%Y%m%d%H%M%S.log 10M 70M
oemmgr    9344  9334  0 Jan13 ?        00:00:11 /oemgc/Oracle/MW3/Oracle_WT/ohs/bin/odl_rotatelogs /oemgc/Oracle/gc_inst2/WebTierIH1/diagnostics/logs/OHS/ohs1/access_log 10M 100M
oemmgr    9345  9334  0 Jan13 ?        00:00:01 /oemgc/Oracle/MW3/Oracle_WT/ohs/bin/odl_rotatelogs /oemgc/Oracle/gc_inst2/WebTierIH1/diagnostics/logs/OHS/ohs1/em_upload_http_access_log 10M 100M
oemmgr    9346  9334  0 Jan13 ?        00:02:13 /oemgc/Oracle/MW3/Oracle_WT/ohs/bin/odl_rotatelogs /oemgc/Oracle/gc_inst2/WebTierIH1/diagnostics/logs/OHS/ohs1/em_upload_https_access_log 10M 100M
oemmgr    9349  9334  0 Jan13 ?        00:00:07 /oemgc/Oracle/MW3/Oracle_WT/ohs/bin/odl_rotatelogs /oemgc/Oracle/gc_inst2/WebTierIH1/diagnostics/logs/OHS/ohs1/mod_wl_ohs.log 10M 100M
oemmgr    9350  9334  0 Jan13 ?        00:00:00 /oemgc/Oracle/MW3/Oracle_WT/ohs/bin/odl_rotatelogs -l -h:/oemgc/Oracle/gc_inst2/WebTierIH1/config/OHS/ohs1/component_events.xml_ohs1 /oemgc/Oracle/gc_inst2/WebTierIH1/auditlogs/OHS/ohs1/audit-pid9334-%Y%m%d%H%M%S.log 1M 4M
oemmgr    9351  9334  0 Jan13 ?        00:00:28 /oemgc/Oracle/MW3/Oracle_WT/ohs/bin/httpd.worker -DSSL
oemmgr    9352  9334  0 Jan13 ?        00:47:55 /oemgc/Oracle/MW3/Oracle_WT/ohs/bin/httpd.worker -DSSL
oemmgr    9353  9334  0 Jan13 ?        00:47:52 /oemgc/Oracle/MW3/Oracle_WT/ohs/bin/httpd.worker -DSSL

# Confirm settings
oemmgr@(primary hostname) $> grep keystore /oemgc/Oracle/gc_inst2/WebTierIH1/config/OHS/ohs1/ssl.conf
SSLWallet file:/oemgc/Oracle/gc_inst2/WebTierIH1/config/OHS/ohs1/keystores/console

# Stage copy of revised CA wallet                                  #WebTierIH2/config/OHS/ohs2 on (secondary hostname)
oemmgr@(primary hostname) $> cp -r /mnt/nfs/FMW/certs/oemgc.domain /oemgc/Oracle/gc_inst2/WebTierIH1/config/OHS/ohs1/keystores
cp -r /mnt/nfs/FMW/certs/oemgc.domain /oemgc/Oracle/gc_inst2/WebTierIH2/config/OHS/ohs2/keystores

# Check permissions 770 on wallet dir, 600 on wallets
oemmgr@(secondary hostname) $> ls -la /oemgc/Oracle/gc_inst2/WebTierIH2/config/OHS/ohs2/keystores/oemgc.domain
ls -la /oemgc/Oracle/gc_inst2/WebTierIH1/config/OHS/ohs1/keystores/oemgc.domain
total 32
drwxrwx— 2 oemmgr oinstall  4096 Mar  9 12:50 .
drwx—— 7 oemmgr oinstall  4096 Mar  9 12:50 ..
-rw——- 1 oemmgr oinstall 11653 Mar  9 12:50 cwallet.sso
-rw——- 1 oemmgr oinstall 11576 Mar  9 12:50 ewallet.p12

# Primary wallet for the OMS console
oemmgr@(primary hostname) $> cd /oemgc/Oracle/MW3/oracle_common/bin
oemmgr@(primary hostname) $> ./orapki wallet display -wallet /oemgc/Oracle/gc_inst2/WebTierIH1/config/OHS/ohs1/keystores/console
Oracle PKI Tool : Version 11.1.1.7.0
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=oemgc.domain
Trusted Certificates:
Subject:        CN=(primary hostname).domain,C=US,ST=CA,L=EnterpriseManager on (primary hostname).domain,OU=EnterpriseManager on (primary hostname).domain,O=EnterpriseManager on (primary hostname).domain

# Confirm new wallet contents
oemmgr@(primary hostname) $> ./orapki wallet display -wallet /oemgc/Oracle/gc_inst2/WebTierIH1/config/OHS/ohs1/keystores/oemgc.domain
Oracle PKI Tool : Version 11.1.1.7.0
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=*.domain,OU=Information Technology,O=ORG My Org,L=My City,ST=California,C=US
Trusted Certificates:
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        CN=ORG POLICY CA
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Subject:        CN=ORG ROOT CA
Subject:        CN=HOSTISSUECA1,DC=fss,DC=ORG,DC=com
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

#Add certificates to monitoring agent for the OMS:
cd $AGENT_HOME/bin
./emctl stop agent
# Default jks keyring password – welcome
./emctl secure add_trust_cert_to_jks -trust_certs_loc /mnt/nfs/FMW/certs/ORGROOTCA.cer -alias ORGROOTCA
./emctl secure add_trust_cert_to_jks -trust_certs_loc /mnt/nfs/FMW/certs/ORGPOLICYCA.cer -alias ORGPOLICYCA
./emctl secure add_trust_cert_to_jks -trust_certs_loc /mnt/nfs/FMW/certs/ORGHOSTISSUECA1.cer -alias HOSTISSUECA1
./emctl start agent

# Support virtual host ignore hostname verification
export EM_COMMON_JAVA_OPTIONS=”-Dweblogic.security.SSL.ignoreHostnameVerification=true -Djava.security.egd=file:///dev/./urandom -Dweblogic.log.FileName=/oemgc/Oracle/gc_inst2/em/EMGC_OMS1/sysman/log/wls.log”

# Backup the EM_INSTANCE_BASE/em/EMGC_OMS1/emgc.properites file
cd /oemgc/Oracle/gc_inst2/em/EMGC_OMS1                               #OMS2 on (secondary hostname)
cp emgc.properties emgc.properties_selfsign

# Requires SYSMAN password
# (secondary hostname)
$OMS_TOP/bin/emctl secure console -wallet /oemgc/Oracle/gc_inst2/WebTierIH2/config/OHS/ohs2/keystores/oemgc.domain

# Example output
# Oracle Enterprise Manager Cloud Control 12c Release 5
# Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
# Securing Console… Started.
# Enter Enterprise Manager Root (SYSMAN) Password :
# Securing Console… Successful
# Restart OMS

# (primary hostname)
$OMS_TOP/bin/emctl secure console -wallet /oemgc/Oracle/gc_inst2/WebTierIH1/config/OHS/ohs1/keystores/oemgc.domain
$OMS_TOP/bin/emctl stop oms
$OMS_TOP/bin/emctl start oms

# 10-JUN-2016 Addendum – enable emcli login by establishing trust for the new certificate

oemmgr@(primary hostname)$> ./emcli setup -url=https://oemgc.auca.corp:7799/em -username=”SYSMAN” -password=$SYSMAN_PW

Oracle Enterprise Manager 12c Release 5.
Copyright (c) 1996, 2015 Oracle Corporation and/or its affiliates. All rights reserved.

The configuration directory “/home/oemmgr” may not be local. See the “dir” option in the help for the setup command.
Do you want to continue using this directory? [yes/no] yes

Warning: This certificate has not been identified as trusted in the local trust store
————————————–
[<blah – lots of cryptic information about the new certificate>
]
————————————–
Do you trust the certificate chain? [yes/no] yes
Emcli setup successful

# Test emcli connectivity

oemmgr@(primary hostname)$> ./emcli login -username=”SYSMAN” -password=$SYSMAN_PW

Login successful

#Section 4: Rolling back to the Demonstration WLS Certificate
#If you need to switch back the WLS components in the OMS installation to use the default WebLogic Server demonstration certificates, execute the following steps on each OMS.

1.Stop the OMS:

cd <OMS_HOME>/bin
emctl stop oms

2.Run the following command:

cd <OMS_HOME>/bin>
emctl secure wls -use_demo_cert
emctl secure console -self_signed

3.Stop the OMS:

cd <OMS_Home>/bin
emctl stop oms -all

4.Start the OMS:

cd <OMS_Home>/bin
emctl start oms

Advertisements

Using Demo Data for Oracle Data Mining Tools

With the forthcoming (but already available) SQLDeveloper 4.1 edition, an improved version of the Oracle Data Miner tools is incorporated into the SQLDeveloper console.  However, I found that there were a number of steps needed to actually use this new data modeling product other than just responding ‘Yes’ to the “Do you wish to enable the Data Miner Repository on this database?” prompt.

Here’s what I ended up doing to get things up and running (so that I could play with data modeling and visualization using Excel and the new SQLDeveloper DM extensions.)

#In this case, I’m adding back the demonstration data (i.e. EMP, DEPTNO type tables; the SH, OE, HR, et.al. schemas) into an existing R12 e-Business Suite (12.1.3) instance.

# Installing the Oracle Demo data in an R12 instance.

# Use the runInstaller from the R12 $ORACLE_HOME
cd $ORACLE_HOME/oui/bin
export DISPLAY=<workstation IP>:0.0
./runInstaller

# Choose the source products.xml from the staging area – Download and stage the DB Examples CD from OTN
/mnt/nfs/database/11203/examples/stage/products.xml

# Complete the OUI installation through [Finish]

cd $ORACLE_HOME/demo/schema
mkdir -p $ORACLE_HOME/demo/schema/log
echo $ORACLE_HOME/demo/schema/log/     ## used to respond to the Log Directory prompt during mksample.sql

sqlplus “/ as sysdba”
— will need passwords for: SYS/SYSTEM and APPS (used for all of the demo schemas, some of which pre-exist such as, HR, OE (PM, IX, SH and BI were okay for 12.1.3).

— ## Be sure to comment out any DROP USER <HR, OE, etc.) commands in this script (or you will be restoring your EBS instance from a backup because it just dropped your Module schema tables…) ##
— They look like this:
/*
mksample.sql:– DROP USER hr CASCADE;
mksample.sql:– DROP USER oe CASCADE;
mksample.sql:DROP USER pm CASCADE;
mksample.sql:DROP USER ix CASCADE;
mksample.sql:DROP USER sh CASCADE;
mksample.sql:DROP USER bi CASCADE;
*/

–Similarly – if/when you decide you no longer need the data – do NOT just use the $ORACLE_HOME/demo/schema/drop_sch.sql script
–or you just dropped your HR/OE/BI EBS schemas; don’t do that.
/*
drop_sch.sql:PROMPT Dropping Sample Schemas
drop_sch.sql:– DROP USER hr CASCADE;
drop_sch.sql:– DROP USER oe CASCADE;
drop_sch.sql:DROP USER pm CASCADE;
drop_sch.sql:DROP USER ix CASCADE;
drop_sch.sql:DROP USER sh CASCADE;
drop_sch.sql:DROP USER bi CASCADE;

order_entry/oe_main.sql:– Dropping the user with all its objects
order_entry/oe_main.sql:– DROP USER oe CASCADE;
order_entry/oe_main.sql:– ALTER USER oe DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs;

*/
— in this instance the $APPS_PW is synchronized to all application module schemas (i.e. AR, HR, GL, etc.)
— log directory would be the actual path from echo $ORACLE_HOME/demo/schema/log/ (including the trailing slash)
SQL> @mksample.sql

# to additionally create the Data Mining user (DM in this case)

create user &&dmuser identified by &&dmuserpwd
default tablespace &&usertblspc
temporary tablespace &&temptblspc
quota unlimited on &&usertblspc;

GRANT CREATE JOB TO &&dmuser;
GRANT CREATE MINING MODEL TO &&dmuser;       — required for creating models
GRANT CREATE PROCEDURE TO &&dmuser;
GRANT CREATE SEQUENCE TO &&dmuser;
GRANT CREATE SESSION TO &&dmuser;
GRANT CREATE SYNONYM TO &&dmuser;
GRANT CREATE TABLE TO &&dmuser;
GRANT CREATE TYPE TO &&dmuser;
GRANT CREATE VIEW TO &&dmuser;
GRANT EXECUTE ON ctxsys.ctx_ddl TO &&dmuser;
GRANT CREATE ANY DIRECTORY TO &&dmuser;
— Grant the SH Demo table and package objects to the DM user
@?/rdbms/demo/dmshgrants.sql &&dmuser

connect &&dmuser/&&dmuserpwd
— Create the Data Mining Views against the  SH Demo table and package objects
@?/rdbms/demo/dmsh.sql

@?/rdbms/demo/dmabdemo.sql — Builds the Adaptive Baynes Model demo
@?/rdbms/demo/dmaidemo.sql — Builds the Attribute Importance demo
@?/rdbms/demo/dmardemo.sql — Builds the Association Rules demo
@?/rdbms/demo/dmdtdemo.sql — Builds the Decision Tree demo
@?/rdbms/demo/dmdtxvlddemo.sql — Builds the Cross Validation demo
@?/rdbms/demo/dmglcdem.sql — Builds the Generalized Linear model demo
@?/rdbms/demo/dmglrdem.sql — Builds the General Linear Regression model demo
@?/rdbms/demo/dmhpdemo.sql — not a Data Mining program – Hierarchical Profiler
@?/rdbms/demo/dmkmdemo.sql — Builds the K-Means Clustering model demo
@?/rdbms/demo/dmnbdemo.sql — Builds the Naive Baynes Model data
@?/rdbms/demo/dmnmdemo.sql — Builds the Non-negative Matrix Factorization model
@?/rdbms/demo/dmocdemo.sql — Builds the O-Cluster model Demo
@?/rdbms/demo/dmsvcdem.sql — Builds the Support Vector Machine model demo
@?/rdbms/demo/dmsvodem.sql — Builds the One-Class Support Vector Machine model demo
@?/rdbms/demo/dmsvrdem.sql — Builds the Support Vector Regression model demo
@?/rdbms/demo/dmtxtfe.sql — Builds the Oracle Text Term Feature Extractor demo
@?/rdbms/demo/dmtxtnmf.sql — Builds the Text Mining Non-Negative Matrix Factorization model demo
@?/rdbms/demo/dmtxtsvm.sql — Builds the Text Mining Support Vector Machine model demo

## End of Data Mining Demo user (DM) setup and configuration for use of Oracle Demo Data