Remove OEM 13c Management Repository

Background

Removing the SYSMAN schema and other OEM-related objects from a database consisted of disabling constraints and running a series of DDL scripts in earlier versions of OEM.  Always use RepManager to perform this cleanup for OEM 13c.

RepManager ‘-drop’ command drops the SYSMAN, SYSMAN_MDS, SYSMAN_OPSS, SYSMAN_RO, and SYSMAN_BIPLATFORM schemas and removes their artifacts, including tablespaces and datafiles.

Procedure

Note:  The following run-time example has been simplified from actual stdout and uses a reference to $MW_HOME to make this example a little easier to read.

The required command line options are straight-forward, starting with the three components of a connect string.  Notice that these values are not preceded by identifier flags.  Then you define the action to perform (“drop), and then give it the user name and database role for the connection.  You could pass the password on the command line but there’s no need to store that in plain text on your operating system history.

 

> cd $MW_HOME/sysman/admin/emdrep/bin

> RepManager demohost.demo.com 1521 OMR -action drop -dbUser sys -dbRole sysdba

processing arguments
compiling arguments for validation
 Enter sys user password :
Verify :
Checking if OMS is up...
Performing PreDropAll action...
Done PreDropAll action...
Dropping BIP schema...
Action on BIP schema succeed.
Dropping APM schema...
Successfully dropped schema
Dropping OPSS schema...
Processing command line ....
Repository Creation Utility - Checking Prerequisites
Checking Global Prerequisites
Repository Creation Utility - Checking Prerequisites
Checking Component Prerequisites
Repository Creation Utility - Drop
Repository Drop in progress.
Repository Creation Utility: Drop - Completion Summary

Database details:
Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=demohost.demo.com)(Port=1521)))(CONNECT_DATA=(SID=OMR)))
Connected As : sys
Prefix for (prefixable) Schema Owners : SYSMAN
RCU Logfile : $MW_HOME/cfgtoollogs/cfgfw/emsecrepmgr.log
Component schemas dropped:
Component Status Logfile
Oracle Platform Security Services Success $MW_HOME/cfgtoollogs/cfgfw/opss.log
Repository Creation Utility - Drop : Operation Completed
Successfully dropped schema
Dropping SYSMANUPGR_OPSS schema...
Processing command line ....
Repository Creation Utility - Checking Prerequisites
Checking Global Prerequisites
Repository Creation Utility - Checking Prerequisites
Checking Component Prerequisites
Repository Creation Utility - Drop
Repository Drop in progress.
Repository Creation Utility: Drop - Completion Summary

Database details:
Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=demohost.demo.com)(Port=1521)))(CONNECT_DATA=(SID=OMR)))
Connected As : sys
Prefix for (prefixable) Schema Owners : SYSMANUPGR
RCU Logfile : $MW_HOME/cfgtoollogs/cfgfw/emsecrepmgr.log
Component schemas dropped:
Component Status Logfile
Oracle Platform Security Services Success $MW_HOME/cfgtoollogs/cfgfw/opss.log

Repository Creation Utility - Drop : Operation Completed
Successfully dropped schema
Dropping STB schema...
Processing command line ....
Repository Creation Utility - Checking Prerequisites
Checking Global Prerequisites
Repository Creation Utility - Checking Prerequisites
Checking Component Prerequisites
Repository Creation Utility - Drop
Repository Drop in progress.
Repository Creation Utility: Drop - Completion Summary

Database details:
Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=demohost.demo.com)(Port=1521)))(CONNECT_DATA=(SID=OMR)))
Connected As : sys
Prefix for (prefixable) Schema Owners : SYSMAN
RCU Logfile : $MW_HOME/cfgtoollogs/cfgfw/emsecrepmgr.log
Component schemas dropped:
Component Status Logfile
Common Infrastructure Services Success $MW_HOME/cfgtoollogs/cfgfw/stb.log

Repository Creation Utility - Drop : Operation Completed
Successfully dropped schema
Dropping MDS schema...
Action on MDS schema succeed.
Processing command line ....
Repository Creation Utility - Checking Prerequisites
Checking Global Prerequisites
Repository Creation Utility - Checking Prerequisites
Checking Component Prerequisites
Repository Creation Utility - Drop
Repository Drop in progress.

< Long wait for SYSMAN to be dropped >

Repository Creation Utility: Drop - Completion Summary
Database details:
Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=demohost.demo.com)(Port=1521)))(CONNECT_DATA=(SID=OMR)))
Connected As : sys
RCU Logfile : $MW_HOME/log/schemamanager/m_031317_0128_PM/m_031317_0128_PM.DROP/rcu.log
Component schemas dropped:
Component Status Logfile
EM Repository Drop Success $MW_HOME/sysman/log/schemamanager/m_031317_0128_PM/m_031317_0128_PM.DROP/em_repos_drop.log

Repository Creation Utility - Drop : Operation Completed
Performing PostDropAll action...
drop completed successfully

Add a CA Cert to OEM Admin Server

Tags

CA cert, OEM, Oracle Enterprise Manager, WLS

Outbound CA Certificates

Certificate files from certifying authorities are widely used for authentication.  Oracle has already loaded an exhaustive set of CA certs inside your OMS server.

Outward bound communication, like connections to an LDAP server, may require a CA cert to authenticate your connection.  Someone in your company is responsible for issuing and managing those certificates.  This procedure shows how easy it is to add your local certs.

Working with keytool

Certificate files are very simple text files that contain strings of nonsense text.

You can only read and edit the cacerts file using keytool (available on all hosts).  It is strongly recommended that you make edits to a copy of the cacerts file, verify the changes, and then deploy it.

Command	Discussion
keytool –list –v –alias MY_CACERT -keystore cacerts	Lists all CA certs in the keystore You set the alias name The java keystore filename in OEM is ‘cacerts’.  There is no jks suffix.
keytool -import -keystore cacerts -alias MY_CACERT -file /tmp/newcert.crt	You can add a new certificate to the keystore using this command
Note about hyphens: After you paste these commands into your terminal you must manually replace each hyphen before executing the command. Otherwise it throws an error.

Installing cacerts

Task	Description or Illustration	Click stream or command
Backup your config.xml All config details for your admin server are stored in this file		cd ../gc_inst1/user_projects/domains/GCDomain/config cp config.xml config.xml_before_cacert
Create a working copy of the existing cacerts file, import your cert, then verify it	You can’t change the location the cacerts file Copy the updated cacerts file to all OMS servers in your cluster	cd ../MW13200/oracle_common/jdk/jre/lib/security mkdir work cp cacerts work/cacerts cd work #Execute the keytool import to add your certificate to the work copy of cacerts. #Password for cacerts file can be found in MOS by searching for cacerts. #The execute keytool list command to verify the import cp cacerts ../cacerts
Bounce all OMS servers to load updated file
Rollback if required	The admin server will not start if it encounters errors	emctl stop oms -all cd ../gc_inst1/user_projects/domains/GCDomain/config cp -f config.xml_before_cacert config.xml emctl start oms -admin_only

Resolve PDP Error for Named Credentials

Tags

Exec of /usr/bin/pb_sudo failed: Operation not permitted, pbrun8.5.1-01[112628]: 3201.07, PDP execution may have failed 3430 Insecure operation

Background

Some of our named credentials use a privileged account to perform root actions via sudo. That account is not the same as the OEM agent binary owner and does not belong to the binary owner’s o/s groups for security reasons.

Sometime that causes problems, like this:

PDP execution may have failed 3430 Insecure operation – please consult your administrator pbrun8.5.1-01[112628]: 3201.07 Exec of /usr/bin/pb_sudo failed: Operation not permitted

The Powerbroker error is a symptom and not the real problem.  The real issue is that the privileged account lacks access to directories in the EM agent home.

Solution

Log into the host as the OEM binary owner and change the permissions as shown:

cd $AGENT_BASE
cd ../

 chmod 755 agent
 cd agent
 chmod 755 agent_inst

cd agent_inst
 chmod 775 diag
 chmod 755 bin install sysman
 chmod 740 internal
 
cd sysman
 chmod 755 ApplicationsState/ config/ emd/ log/ opmn/ recv/
 ls -las

Notice that we’re not changing any file permissions and we are not altering contents of the core/release directories, just agent_inst.

By the way:  This solution makes a very simple and convenient OEM Job.

 

Verification

In the console click through to Setup | Security | Named Credentials and highlight the privileged credential you need to test.  Select the previously broken host name from the Target Name list and hit the Test button.

Catalog your Named Credentials

Tags

EMCLI, emcli get_named_credential, emcli list_named_credentials, Named Credentials, Oracle Enterprise Manager

You can catalog your named credentials quickly and easily with EM CLI.

emcli login -username=SYSMAN

emcli sync

NCCATALOG=/u01/oem/backups/named_credential_catalog.lst
[ $NCCATALOG ] && rm -f ${NCCATALOG}
touch ${NCCATALOG}

for thisNC in `emcli list_named_credentials | awk '{ print $1 }' | grep -v "Credential"`; do
emcli get_named_credential -cred_name=${thisNC} >>${NCCATALOG}
done

cat ${NCCATALOG}

 

Your results will look like this:

Credential Name:CRED_SYSDBA
Credential Owner:SYSMAN
Credential Type:DBCreds
Credential Target Type:oracle_database
Credential Username:sys
Credential Scope:global
Credential Guid:<string>
Credential Stripe:TARGETS
Credential Columns:
 DBPassword=******
 DBRole=sysdba
 DBUserName=sys

Credential Name:CRED_SUPERMAN
Credential Owner:SYSMAN
Credential Type:HostCreds
Credential Target Type:host
Credential Username:superman
Credential Description:Superman administrator account
Credential Scope:global
Credential Guid:<string>
Credential Stripe:TARGETS
Credential Columns:
 HostPassword=******
 HostUserName=superman
 PDPTYPE=SUDO
 RUNAS=oracle

You Can Not Outsmart a SYSMAN Password Change

Tags

emctl config oms -change_repos_pwd, sysman password, sysman_apm, sysman_biplatform, sysman_mds, sysman_opss, sysman_ro

There are no circumstances where manually changing the passwords for the SYSMAN-named database accounts will end happily.  Never attempt to change the passwords for SYSMAN, SYSMAN_APM, SYSMAN_BIPLATFORM, SYSMAN_MDS, SYSMAN_OPSS, or SYSMAN_RO from within the database.

OEM security is managed by the WebLogic admin server.  The database passwords are only part of the puzzle.  Your WLS relies on wallets and other encrypted files to keep it all straight.  When you do the password change in the repository OEM, security validations against those files will fail and OEM will be hopelessly broken.

When you invoke this simple emctl command a slew of activities occur

emctl config oms -change_repos_pwd

 

That triggers this chain of actions (from .. sysman/log.secure.log):

oms.AdminCredsWalletUtil setInstanceHome.177 – Getting credentials from wallet
oms.AdminCredsWalletUtil setInstanceHome.192 – Read the credentials from wallet
util.EmctlUtil logp.251 – Connecting over t3s to: oms01.demo.com/7103 using id: weblogic
oms.ChangeReposPwd logp.251 – Getting repos conn as user sys as sysdba
oms.ChangeReposPwd logp.251 – Getting repos conn as user sys as sysdba
oms.ChangeReposPwd logp.251 – SYSMAN password changed in the backend successfully.
oms.ChangeReposPwd logp.251 – Getting lock on table EM_UPDATE_DATASOURCES_LOCK
oms.ChangeReposPwd logp.251 – Getting repos conn as user SYSMAN
oms.ChangeReposPwd logp.251 – Successfully obtained lock on table EM_UPDATE_DATASOURCES_LOCK
oms.ChangeReposPwd logp.251 – Start change SYSMAN_MDS password
oms.ChangeReposPwd logp.251 – Changed SYSMAN_MDS password
oms.ChangeReposPwd logp.251 – Changing OPSS admin user’s pwd
oms.ChangeReposPwd logp.251 – Changed OPSS admin pwd
oms.ChangeReposPwd logp.251 – Changing APM admin user’s pwd
oms.ChangeReposPwd logp.251 – Changed APM admin pwd
oms.ChangeReposPwd logp.251 – Getting repos conn as user SYSMAN
oms.AdminCredsWalletUtil setInstanceHome.177 – Getting credentials from wallet
oms.AdminCredsWalletUtil setInstanceHome.192 – Read the credentials from wallet
util.EmctlUtil logp.251 – Connecting over t3s to: oms01.demo.com/7103 using id: weblogic
util.EmctlUtil logp.251 – Updating datasource : emgc-sysman-pool
util.EmctlUtil logp.251 – Updating WLS datasource :emgc-sysman-pool: Done startEditSession()
util.EmctlUtil logp.251 – Updating WLS datasource : DB datasource :emgc-sysman-pool: IS found
util.EmctlUtil logp.251 – Updating password
util.EmctlUtil logp.251 – Updating WLS datasource :emgc-sysman-pool: Invoked save
util.EmctlUtil logp.251 – Updating WLS datasource :emgc-sysman-pool: Done activate()
oms.ChangeReposPwd logp.251 – Updating OWSM DataSource
util.EmctlUtil logp.251 – Updating datasource : mds-owsm
util.EmctlUtil logp.251 – DataSource URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= …
util.EmctlUtil logp.251 – Updating WLS datasource :mds-owsm: Done startEditSession()
util.EmctlUtil logp.251 – Updating WLS datasource : DB datasource :mds-owsm: IS found
util.EmctlUtil logp.251 – Updating URL
util.EmctlUtil logp.251 – Updating password
util.EmctlUtil logp.251 – Updating userName
util.EmctlUtil logp.251 – Updating WLS datasource :mds-owsm: Invoked save
util.EmctlUtil logp.251 – Updating WLS datasource :mds-owsm: Done activate()
oms.ChangeReposPwd logp.251 – Updating APM DataSource
util.EmctlUtil logp.251 – Updating datasource : apm-DBDS
util.EmctlUtil logp.251 – DataSource URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= …
util.EmctlUtil logp.251 – Updating WLS datasource :apm-DBDS: Done startEditSession()
util.EmctlUtil logp.251 – Updating WLS datasource : DB datasource :apm-DBDS: IS found
util.EmctlUtil logp.251 – Updating URL
util.EmctlUtil logp.251 – Updating password
util.EmctlUtil logp.251 – Updating userName
util.EmctlUtil logp.251 – Updating WLS datasource :apm-DBDS: Invoked save
util.EmctlUtil logp.251 – Updating WLS datasource :apm-DBDS: Done activate()
oms.ChangeReposPwd logp.251 – Updating APM-MDS DataSource
util.EmctlUtil logp.251 – Updating datasource : mds-ApplicationMDSDB
util.EmctlUtil logp.251 – DataSource URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= …
util.EmctlUtil logp.251 – Updating WLS datasource :mds-ApplicationMDSDB: Done startEditSession()
util.EmctlUtil logp.251 – Updating WLS datasource : DB datasource :mds-ApplicationMDSDB: IS found
util.EmctlUtil logp.251 – Updating URL
util.EmctlUtil logp.251 – Updating password
util.EmctlUtil logp.251 – Updating userName
util.EmctlUtil logp.251 – Updating WLS datasource :mds-ApplicationMDSDB: Invoked save
util.EmctlUtil logp.251 – Updating WLS datasource :mds-ApplicationMDSDB: Done activate()
oms.ChangeReposPwd logp.251 – Updating OPSS DataSource
util.EmctlUtil logp.251 – Destroying datasource sysman-opss-ds
util.EmctlUtil logp.251 – destroyDataSource completed with status = true
util.EmctlUtil logp.251 – Creating datasource sysman-opss-ds
util.EmctlUtil logp.251 – Datasource created. Targetting to 5 servers.
util.EmctlUtil logp.251 – Targeting to EMGC_ADMINSERVER
util.EmctlUtil logp.251 – Targeted to EMGC_ADMINSERVER
util.EmctlUtil logp.251 – Targeting to EMGC_OMS1
util.EmctlUtil logp.251 – Targeted to EMGC_OMS1
util.EmctlUtil logp.251 – Targeting to EMGC_OMS2
util.EmctlUtil logp.251 – Targeted to EMGC_OMS3
util.EmctlUtil logp.251 – Targeting to EMGC_OMS3
util.EmctlUtil logp.251 – Targeted to EMGC_OMS3
util.EmctlUtil logp.251 – Targeting to EMGC_OMS4
util.EmctlUtil logp.251 – Targeted to EMGC_OMS4
util.EmctlUtil logp.251 – Targeting to cluster BIP_cluster
util.EmctlUtil logp.251 – Targeted to BIP_cluster
util.EmctlUtil logp.251 – createDataSource completed with status = true
oms.ChangeReposPwd logp.251 – Updating mds password in domain…
util.EmctlUtil logp.251 – Updating datasource : mds-sysman_mds
util.EmctlUtil logp.251 – Updating WLS datasource :mds-sysman_mds: Done startEditSession()
util.EmctlUtil logp.251 – Updating WLS datasource : DB datasource :mds-sysman_mds: IS found
util.EmctlUtil logp.251 – Updating password
util.EmctlUtil logp.251 – Updating WLS datasource :mds-sysman_mds: Invoked save
util.EmctlUtil logp.251 – Updating WLS datasource :mds-sysman_mds: Done activate()
oms.ChangeReposPwd logp.251 – MDS password in domain updated successfully.
oms.ChangeReposPwd logp.251 – Waiting for jps/opss re-initialization to complete
oms.ChangeReposPwd logp.251 – Updating repository password in Credential Store. Try#1
mas.CredStoreUtil logp.251 – deleteCredential : Exit mapName -EM keyName – REPOS_DETAILS status = true
mas.CredStoreUtil logp.251 – setGenericCredential : Exit mapName -EM keyName – REPOS_DETAILS
mas.CredStoreUtil logp.251 – getCredential : Got creds for mapName -EM_BIP keyName – EM_BIP_DETAILS
oms.ChangeReposPwd logp.251 – Getting repos conn as user sys as sysdba
oms.ChangeReposPwd logp.251 – Changing :SYSMAN_BIPLATFORM: in back-end
oms.ChangeReposPwd logp.251 – Changed :SYSMAN_BIPLATFORM: user in back-end
util.EmctlUtil logp.251 – Updating datasource : bip_datasource
util.EmctlUtil logp.251 – Updating WLS datasource :bip_datasource: Done startEditSession()
util.EmctlUtil logp.251 – Updating WLS datasource : DB datasource :bip_datasource: IS found
util.EmctlUtil logp.251 – Updating password
util.EmctlUtil logp.251 – Updating WLS datasource :bip_datasource: Invoked save
util.EmctlUtil logp.251 – Updating WLS datasource :bip_datasource: Done activate()
oms.ChangeReposPwd logp.251 – Commiting the getLockConn to release lock on EM_UPDATE_DATASOURCES_LOCK
oms.ChangeReposPwd logp.251 – Done commiting the getLockConn

 

Don’t Despair

The  emctl change_repos_password command will clean up any mess you’ve made.

It starts by making a clean password change on the backend (the repository), updates the local reference files, and then propogates the change to all OMS’s in your environment.

I deliberately messed up one of our lab servers and within minutes of invoking emctl change_repos_password all my handiwork was fixed and I was back in business.

I’m Not Waiting – Collect my Metric Extensions Now!

Tags

emcli argfile, emcli collect_metric, OEM 12c; EMCLI; verbs, OEM; Metric Extension; 12c Cloud Control, Oracle Enterprise Manager

We use OEM to populate other system management systems at our company by providing configuration and metric data of all kinds.  When we get requests that require data that OEM doesn’t collect out of the box, we build metric extensions.

If you’ve worked with 12c metric extensions you know that the agents appear to perform these special metric collections whenever OEM feels like it.  At least that’s the way it seems.

I need to be able to collect these metrics at-will in response to user complaints or requests, so I put together a SQL query that builds the EM CLI commands to force a collection using the collect_metric verb.

In this example I want to collect a metric named ME$DEMO01 from all the members of an OEM group named rays_demo_hosts.

SELECT 'emcli collect_metric -target_type=host -target_name='
                || target_name
                || ' -metric_name="ME\$DEMO01"'
 AS cli_command
 FROM sysman.mgmt$group_members
 WHERE target_type = 'host' AND group_name = 'rays_demo_hosts';

That query results several lines like this:

emcli collect_metric -target_type=host -target_name=hostinmind01.madeup.com -metric_name="ME\$DEMO01"

Deployment Method 1 – Cut and Paste

The simplest means of executing this script is to execute the query in a tool like TOAD (or even at the command line) and copy and paste each line into an emcli session.

Deployment Method 2 – Spool into an argfile

If you decide to spool the results to a file, remove the keyword emcli from the query first and then execute the block using the emcli argfile verb.

SET echo OFF
SET heading OFF feedback OFF

spool /tmp/me_demo.lst
 SELECT 'collect_metric -target_type=host -target_name='
                || target_name
                || ' -metric_name="ME\$DEMO01"'
 AS cli_command
 FROM sysman.mgmt$group_members
 WHERE target_type = 'host' AND group_name = 'rays_demo_hosts';
spool off

Then:

emcli login -username=SYSMAN

emcli sync

emcli argfile /tmp/demo.lst

Deployment Method 3 – OEM Job System

The two steps from Deployment Method 2 can be built into a simple OEM Job to run on any host with SQL+ and EM CLI client installed (like your OEM management server), using this kind of logic:

#!/bin/bash

export ORACLE_HOME=/x/home/oracle/product/em/MW12105/oms
export EMCLI=${ORACLE_HOME}/bin/emcli
SQL_CONNECT=<sysman connect string to OMR>
SYSMAN_PWD=<   >
export SPOOLFILE=/tmp/me_demo.lst

# -------------------------------------------------------------------------

${ORACLE_HOME}/bin/sqlplus -S ${SQL_CONNECT} <<EOF
SET echo OFF
SET pages 999 lines 32767 trimspool ON head OFF feedback OFF
spool ${SPOOLFILE}

SELECT 'collect_metric -target_type=host -target_name='
 || target_name
 || ' -metric_name="ME\$DEMO01"'
 AS cli_command
 FROM sysman.mgmt$group_members
 WHERE target_type = 'host' AND group_name = 'rays_demo_hosts';

spool off
exit
EOF

$EMCLI logout
$EMCLI login -username=SYSMAN -password=${SYSMAN_PWD} -force
$EMCLI sync
$EMCLI argfile ${SPOOLFILE}
$EMCLI logout

export SQL_CONNECT=" "
export SYSMAN_PWD=" "

exit 0

I generally create two versions of the same Job – one with a scheduled recurring time and a second to run immediately, with names like update_me_demo01 and update_me_demo01_immediate, respectively.

Plugin versions on agent does not support target type rac_database

Tags

Oracle Enterprise Manager, plugin mismatch, plugin version on agent does not support target type, sysman

I wasn’t able to promote database targets after I applied the July bundle patch.  I quickly opened an SR and received the following excellent, if scary, advice.   The solution, as you’ll see, involves directly manipulating data in the SYSMAN schema.

The Error Message

“Plugin versions on agent https://xyz123.demo.org:1874/emd/main/ does not support target type rac_database”.

In this case the agents appeared to have a later release of the database plugin than the management server.   A quick check of the OMS and agents through the EM console showed that I was fully deployed with 12.0.8.  Everything’s fine, it just doesn’t work.

Evaluating the Problem

It seems my production OEM environment had entered some sort of quantum state, but I’m confident quantum computing wasn’t in this bundle patch.  Something must be seriously wrong.  That’s where Oracle Support came to the rescue.

The SYSMAN schema contains all sort of data and database objects behind the OEM application, including a detailed listing of acceptable plugin ranges – the min and max version your system will allow you to manage.

select * from em_target_types where type_resource_bundle like 'oracle.sysman.db.rsc';

There’s my problem.  The max plugin version for RAC database was set to 12.1.0.7!

Action Plan (Thanks to Oracle Support)

0. Take a complete backup of repos DB  [ I chose to do a guaranteed restore point ].  The fix involves recompiling a hand-edited version of three packaged procedures, their dependent SQL, and several other objects.  Backup your OMR.

1. Navigate to <MW_HOME>/plugins/oracle.sysman.db.oms.plugin_12.1.0.8.0/sql/db_mds/12.1.0.8.0/targetType/ path on the OMS server.

i.e. ls -lrt has_data_upgrade_*

ls -lrt cluster_data_upgrade_

ls -lrt rac_database_data_upgrade_*

2. Find the last data upgrade sql file for has, cluster and rac_database.  In this case there were six SQL files for each plugin, so I edited has_data_upgrade_6.sql, etc.

 

3. Make a back up those each of those files.  We’ll edit and run these copies.

i.e. cp cluster_data_upgrade_6.sql cluster_data_upgrade_6_bkp.sql

4. Edit each backup file from step 3.  The two edits per file are shown in red below:  correct the plugin version ID and add a commit statement at the bottom of each file.

 DECLARE
 l_type_property_list SMP_EMD_NVPAIR_ARRAY;
 l_type_property SMP_EMD_NVPAIR;
 BEGIN

l_type_property_list := SMP_EMD_NVPAIR_ARRAY();
 l_type_property := SMP_EMD_NVPAIR( 'inherit_cred_metadata',
 '1');
 l_type_property_list.extend(1);
 l_type_property_list(l_type_property_list.count) :=
 l_type_property;
 l_type_property := SMP_EMD_NVPAIR(
 'inherit_cred_target_type',
 'oracle_database');
 l_type_property_list.extend(1);
 l_type_property_list(l_type_property_list.count) :=
 l_type_property;
 l_type_property := SMP_EMD_NVPAIR( 'use_parent_creds',
 '1');
 l_type_property_list.extend(1);
 l_type_property_list(l_type_property_list.count) :=
 l_type_property;
 l_type_property := SMP_EMD_NVPAIR( 'TargetVersion',
 'Version');
 l_type_property_list.extend(1);
 l_type_property_list(l_type_property_list.count) :=
 l_type_property;

mgmt_target.register_target_type(
 p_target_type_in => 'oracle_pdb',
 p_type_meta_ver_in => '1.4',
 p_parent_target_type_in => NULL,
 p_parent_type_meta_ver_in => NULL,
 p_type_property_list_in => l_type_property_list,
 p_type_display_name_in => 'Pluggable Database',
 p_type_display_nlsid_in => 'oracle_pdb_title',
 p_type_resource_bundle_in => 'oracle.sysman.db.rsc',
 p_monitored_by => '2',
 p_plugin_id => 'oracle.sysman.db',
 p_plugin_version_id => '12.1.0.8.0', 
 p_category_prop_defs => NULL,
 p_ocm_gc_merged => '1',
 p_type_relationship => 1,
 p_client => 'OTHERS',
 p_plugin_min_version_id => '12.1.0.1.0',
 p_product_id => NULL);
END;
.. lots of other stuff
 commit; 
 /

5. Run back up scripts from step 4 on the repository database as the sysman user:

@<MW_HOME>/plugins/oracle.sysman.db.oms.plugin_12.1.0.8.0/sql/db_mds/12.1.0.8.0/targetType/cluster_data_upgrade_6_bkp.sql

6. Running the following query on the repository database as the sysman user, verify that the Cluster, Cluster Database and Oracle High Availability Service targets MAX_PLUGIN_VERSION is 12.1.0.8.0:

 select * from em_target_types where type_resource_bundle like 'oracle.sysman.db.rsc';

Turn OEM Job Output into Lists

Tags

OEM 12c, OEM Job Results, OEM Jobs, Oracle Enterprise Manager, sysman, sysman.mgmt$job_step_history

Context

We have an OEM job that we run after each system-wide Unix password change to verify success on all hosts.

The OEM Job is run against Dynamic Groups of hosts.  In this example I’ve limited it to the group DBHosts.

The job consists of a very simple call for id

The Credentials for the job contain the new password, of course.

The job quickly tries to connect to each host with that named named credential and either succeeds or throws an error.  The Job’s screen output provides immediate feedback to the operator, but I need to share the list of exceptions with Operations so they can correct it.

Gathering Data

Job results are stored in sysman.mgmt$job_step_history. We can run a simple query to find our exceptions.  The list I sent to Operations excluded the end_time and status.

SELECT target_name,
       end_time,
       status
FROM sysman.mgmt$job_step_history
WHERE job_name LIKE 'CHECK ORACLE PASSWORD%'
 AND  status NOT IN ( 'Succeeded' )
 AND  end_time > SYSDATE - 7
ORDER BY target_name;

SELinux blocked my .Xauthority

Tags

.ssh/config, .Xauthority, /usr/sbin/getenforce, /usr/sbin/setenforce, port forwarding, SElinux

I was attempting to install an OEM management server on a new host in the lab using runInstaller.  Of course the installer is an X-windows app so I need to configure port forwarding to get the display back to MacBook.

I added the new host and its bastion to my ~/.ssh/config file to set up port forwarding:

Host 10.123.45.678
ConnectTimeout 60
StrictHostKeyChecking ask
ProxyCommand none
UserKnownHostsFile ~/.ssh/known_Hosts
User oracle

Host newlaboms.raysdemo.com
ProxyCommand ssh -W %h:%p 10.123.45.678
StrictHostKeyChecking no
UserKnownHostsFile ~/.ssh/known_Hosts
VisualHostKey no
ForwardX11 yes
ForwardAgent yes
User oracle

Pretty straight-forward and it’s worked plenty of times before, so I expected no problems.

Frustration

When I ssh’d to newlaboms I was hit with an xauth error:

xauth:  timeout in locking authority file /home/oracle/.Xauthority

Quick solutions include ensuring proper ownership of my home directory (no problem), that I could write a new file there (touch temp.file — ok), and adequate space on the home’s file system (no problem).

My ‘id’ line looked wrong:

uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

All that “context=” stuff was added by SELinux.  This is a new host and SELinux is enabled by default and disabled by my S/A’s as part of the build process.  They’d missed it this time – and this is how you check:

> /usr/sbin/getenforce;
Enforcing

Enhanced security is being enforced by SELinux!

Temporary Fix

So try this:

> sudo /usr/sbin/setenforce 0 ;
> /usr/sbin/getenforce;
Permissive

Now log out and back in to notice that your .Xauthority file has been created and port forwarding will work!

Run ‘id’ and you’ll see the simple results you expect.

Permanent Change

The setenforce command does not require a server reboot but it’s also not going to survive a reboot.  To make the change permanent, ask your system admin to edit /etc/selinux/config to set “SELINUX=permissive”

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#       enforcing - SELinux security policy is enforced.
#       permissive - SELinux prints warnings instead of enforcing.
#       disabled - No SELinux policy is loaded.
SELINUX=enforcing
# SELINUXTYPE= can take one of these two values:
#       targeted - Targeted processes are protected,
#       mls - Multi Level Security protection.
SELINUXTYPE=targeted

 

 

 

 

 

User Defined Target Properties

Tags

EMCLI, OEM12c, Oracle Enterprise Manager, sysman, target properties, udtp, User defined target properties

We can create our own target property classifications using EM CLI.  In this example we’ll create a new property named “Product Type”.  In my shop we’ll use that property to identify Oracle RAC and single instance databases, but also noSQL targets that we’re adding to our EM environment.

Here’s the syntax for creating the new target property:

emcli add_target_property -target_type=”*” -property=”Product Type”
Property “Product Type” added successfully

The new target property can now be applied with EM CLI using the syntax:

emcli set_target_property_value -property_records=”target_name:target_type:property_name:property_value”

emcli set_target_property_value property_records=”SPECIAL_DEMO_2:rac_database:Product Type:OracleRAC”;
Properties updated successfully

That’s cool, but now I need to apply it to >500 targets, right?  Let’s build the CLI statement from a SQL query against the SYSMAN schema:

SELECT DISTINCT
’emcli set_target_property_value -property_records=”‘
|| target_name
|| ‘:’
|| target_type
|| ‘:Product Type:OracleRAC”;’
FROM sysman.mgmt$target_properties
WHERE target_type LIKE ‘rac_database’
AND target_name NOT IN ( SELECT target_name
FROM sysman.mgmt$target_properties
WHERE property_name = ‘udtp_1’
AND property_value = ‘OracleRAC’ );

Notice that the query is filtered by the property_value (OracleRAC) and a property_name of udtp_1!   Oracle has assigned a generic-looking name to our custom user defined target property name, just as they use orcl_gtp* for OEM-defined properties.  These abstractions are only used in the repository and within the OEM application itself.  The user-readable property name appears in the console.

Test the new property by running the CLI command for a single target and then execute the subquery  (containing the udtp_1 property_name above) against the OMR.   Confirm that it’s performing as expected by looking up the target’s properties in the console.

Hang onto that SQL query after your deployment to periodically label new targets as they’re added to your environment.  I have several SQL queries like this posted to a single Information Publisher report (surprise!) that I mail to myself nightly.  You can also set up an OEM Job to execute the CLI commands for you.