Monthly Archives: July 2013

How to connect to the default EM12c R3 self-signed WebLogic SSL port with WLST

After upgrading to Oracle Enterprise Manager 12c R3, I decided it was time to get roles configured properly for BI Publisher so that I can use it under my regular account rather than only permitting SYSMAN to access it.  Adeesh Fulay (@AdeeshF) helpfully provided me with a link to the documentation about setting up BI Publisher for EM12c.  The first step to perform the configuration involves connecting to the secured WebLogic adminserver via wlst.sh, but I immediately encountered an error:

wls:/offline> connect('weblogic', 'password', 't3s://host.domain.com:7103')
Connecting to t3s://host.domain.com:7103 with userid weblogic ...
<Jul 19, 2013 9:41:15 AM EDT> <Warning> <Security> <BEA-090542> <Certificate chain received from host.domain.com - x.x.x.x was not trusted causing SSL handshake failure. Check the certificate chain to determine if it should be trusted or not. If it should be trusted, then update the client trusted CA configuration to trust the CA certificate that signed the peer certificate chain. If you are connecting to a WLS server that is using demo certificates (the default WLS server behavior), and you want this client to trust demo certificates, then specify -Dweblogic.security.TrustKeyStore=DemoTrust on the command line for this client.> 
Traceback (innermost last):
  File "<console>", line 1, in ?
  File "<iostream>", line 22, in connect
  File "<iostream>", line 648, in raiseWLSTException
WLSTException: Error occured while performing connect : Error getting the initial context. There is no server running at t3s://host.domain.com:7103 
Use dumpStack() to view the full stacktrace

I could not find any obvious reference in the documentation on how to add the “-Dweblogic.security.TrustKeyStore=DemoTrust” options on the command line.  I attempted to just run wlst.sh with that parameter but I also received an error.

After a little searching I found a fix and figured I would post it.

In the documentation for the WebLogic 10.3.6 Oracle WebLogic Scripting Tool, section “Invoking WLST”, an example is included where it shows how to provide a different command line option to the WLST tool, by setting the environment variable CONFIG_JVM_ARGS. (EDITED 20130719: Adeesh has let me know that the preferred environment variable to use for this string is WLST_PROPERTIES, not CONFIG_JVM_ARGS.  Both work at the moment, but the documentation will be updated to refer to WLST_PROPERTIES so I advise you to use that one.)

I tried that before making my wlst.sh call, and everything worked successfully:

oracle@host:~> export WLST_PROPERTIES=-Dweblogic.security.TrustKeyStore=DemoTrust
oracle@host:~> /oracle/oem/Middleware12cR3/oracle_common/common/bin/wlst.sh 
[...]
Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

wls:/offline> connect('weblogic', 'password', 't3s://host.domain.com:7103')
[...]
Successfully connected to Admin Server 'EMGC_ADMINSERVER' that belongs to domain 'GCDomain'.wls:/GCDomain/serverConfig>

So if you are having trouble connecting to your WebLogic admin server using the default self-signed certificate via wlst.sh, this environment variable is the answer.  I was now able to proceed with granting my account access to BI Publisher, and now I am able to access BI Publisher features as needed without using the SYSMAN account.

wls:/GCDomain/serverConfig> grantAppRole(appStripe="obi",appRoleName="EMBIPViewer",principalClass="weblogic.security.principal.WLSUserImpl",principalName="USERNAME")    
Location changed to domainRuntime tree. This is a read-only tree with DomainMBean as the root. 
For more help, use help(domainRuntime)

wls:/GCDomain/serverConfig> grantAppRole(appStripe="obi",appRoleName="EMBIPAdministrator",principalClass="weblogic.security.principal.WLSUserImply", principalName="USERNAME")                                                
Already in Domain Runtime Tree

wls:/GCDomain/serverConfig> grantAppRole(appStripe="obi",appRoleName="EMBIPScheduler",principalClass="weblogic.security.principal.WLSUserImply", principalName="USERNAME")
Already in Domain Runtime Tree

wls:/GCDomain/serverConfig> grantAppRole(appStripe="obi",appRoleName="EMBIPAuthor",principalClass="weblogic.security.principal.WLSUserImply", principalName="USERNAME")
Already in Domain Runtime Tree

wls:/GCDomain/serverConfig> exit()

Exiting WebLogic Scripting Tool.
Advertisements

My Production EM12c Upgrade From R2 (12.1.0.2) to R3 (12.1.0.3)

This post covers my production upgrade from EM12c R2 to EM12c R3 on Linux x86-64 (SLES11 SP2).  To stress test the upgrade and keep it interesting, this system also has BI Publisher integrated into OEM, and also has the plugin from NetApp (version 12.1.0.1.0), the VMware plugin from BlueMedora (version 12.1.0.5.0), and the MySQL plugin from Pythian (version 12.1.0.1.1).  The repository database is running on version 11.2.0.3.

I’m feeling lucky today.  So this is just going straight into production.  Famous last words…

Preparation

  1. Go to edelivery and search for the Oracle Enterprise Manager product pack and platform Linux x86-64
  2. Follow the link titled “Oracle Enterprise Manager Cloud Control 12c Release 3 (12.1.0.3) Media Pack for Linux x86-64
  3. Download the three files needed for EM12c R3: V38641-01.zip, V38642-01.zip, and V38643-01.zip
  4. Download patch 13349651 for WebLogic, you will need it during the post-upgrade steps
  5. View the digest and run md5sum against each downloaded file to confirm that the files downloaded correctly
  6. Transfer the EM12c R3 files to a staging area on your Oracle Enterprise Manager server and unzip all three of them
  7. Delete the three downloaded .zip files if you are short on space (but don’t just “rm *.zip” or you’ll remove the necessary WT.zip file at the top level of your staging directory)
  8. Review the upgrade guide
  9. Create backups of your current OMS home, agent home, software library and Oracle inventory
  10. Create a backup of your current repository database that you can restore from if necessary
  11. If you use a dedicated filesystem for your Oracle Management Agents, make sure that dedicated filesystem has enough free space.  I used a small, 2GB filesystem, and this was barely large enough, except for the one sandbox server where it was too small and I was not able to complete the agent upgrade without adding space
  12. Stop the BI Publisher WebLogic Server (if you have it installed) via the WebLogic Admin Console
  13. Make sure your repository database does not have snapshots created on any tables by running “select master, log_table from all_mview_logs where log_owner = ‘SYSMAN'” — if any snapshots are found, follow the instructions in the upgrade guide to drop them
  14. If your repository database is version 11.1.0.7 or 11.2.0.1, follow the steps in the upgrade guide to apply the prerequisite patches needed to proceed
  15. Copy the emkey from the existing OMS to the existing management repository by running “$OMS_HOME/bin/emctl config emkey -copy_to_repos” and enter the SYSMAN password when prompted
  16. Confirm the emkey was copied by running “$OMS_HOME/bin/emctl status emkey” and enter the SYSMAN password when prompted
  17. Stop every OMS in your environment by running “$OMS_HOME/bin/emctl stop oms -all”
  18. Stop the management agent monitoring the management services and repository target by running “$AGENT_HOME/bin/emctl stop agent”

Running The Upgrade

Due to some issues I had with the upgrade from EM12c R1 to R2, I highly recommend that you do NOT use Cygwin to ssh to your OMS host and display the installer over ssh using Cygwin’s X server.  Use VNC instead.  I’m not going to try Cygwin this time through.

First, I start vncserver on the OMS host.  Then I connect to it to using TightVNC from my desktop machine.

  1. Navigate to the staging directory where you unzipped the EM12c R3 distribution files and run “./runInstaller” as your oracle software owner
  2. As an SAP customer, we are not allowed to use OCM, so I skipped the steps involving entering my My Oracle Support credentialsStep 1
  3. I also skipped the search for updates as this is a new enough release at the moment there should not be any necessaryStep 2
  4. The prerequisite checks run.  I received a warning about libstdc++43-4.3 not being found, but libstdc++43-devel-4.3.3-x86_64 fulfills this need, so I click ignore and then Next to continueStep 3
  5. Only the one system upgrade is supported when upgrading from EM12cR2, so I selected “Upgrade an existing system”, “One system upgrade”, and my existing middleware home.  Click NextStep 4
  6. This out of place upgrade will go into a new middleware home.  I am using /oracle/oem/Middleware12cR3.  Click Next and the installer will confirm that you have enough free space availableStep 5
  7. Here I had to pause and request more space from my storage admin, as the installer wants at least 14.0GB of free space.  Once that was done, I proceeded
  8. Enter the SYS and SYSMAN passwords and check the box to confirm that you have backed up the repository (you should have your OMS, agent, etc all backed up as well), then click NextStep 6
  9. The installer will check various parameters on your repository database and offer the chance to fix them if any need to be changed.  I accept the fixes and click YesStep 6(b)
  10. The installer checks some additional settings and notes that they should be reviewed after the installation or fixed now.  I explicitly granted execute on DBMS_RANDOM to DBSNMP and then clicked OKStep 6(c)
  11. The installer lists the plugin versions that will change and the plugins that will migrate. Confirm this all looks right and then click NextStep 7
  12. The installer lists additional plugins you can choose to deploy at install time.  I do not use any of these so I left them all unchecked and clicked NextStep 8
  13. The installer requests the password for your WebLogic adminserver and confirmation of the hostname, port and username.  Provide the password and click Next.  You may be able to change the OMS instance directory here but I do not suggest doing soStep 9
  14. You now have a chance to review your settings, then click Install to proceed with the upgradeStep 10
  15. Installation proceedsStep 11
  16. You are then prompted to run the allroot.sh file.  Login to the server and execute it as root or via sudoallroot.sh
  17. Once the install/upgrade is complete, the installer will display an installation summary.  Review it, save the URLs it gives you for the OMS and adminserver, then click CloseUpgrade Summary Report

Overall, the upgrade installation steps took 1 hour and 15 minutes in my environment.  This was on a physical server with 126GB RAM, 16 dual core processors and 200 managed targets.  This does not include the post-upgrade steps shown below.

Post Upgrade Steps

Now that the upgrade is complete, return to the upgrade guide to complete post upgrade steps.  Your environment may differ from mine, but these are the steps I had to follow.

  1. Start your central agent by running “$AGENT_HOME/bin/emctl start agent”.  At this point the load on my system went up very high and began responding very slowly.  I walked away for 10 minutes to let things settle down
  2. Open your web browser and go to the URL provided at the end of the installation and login as SYSMAN.  When I first tried to do so using Firefox I received an error indicating an invalid certificate.  I had to delete the old certificates and authorities from my previous installation and restart Firefox before it would allow me in.  MSIE worked fine though
  3. Update the central agent (the management agent installed on the OMS host) by clicking on the Setup menu, then Manage Cloud Control, then Upgrade Agents.  Click the Add button and select your central agent.  I choose “Override preferred credentials” since I have not configured sudo.  Click Submit to continue, then OK when warned that you may have to run root.sh manuallyUpgrade Central Agent
  4. My first upgrade attempt on the central agent failed due to a prerequisite check for package libstdc++-43.  The easy thing to do here is expand the Additional Inputs region and provide “-ignorePrereqs” as an additional parameter, but I chose to complete this agent upgrade using emcli and describe that process in the next two steps
  5. First run “$OMS_HOME/bin/emcli login -username=sysman” and enter the SYSMAN password when prompted.  Then run “$OMS_HOME/bin/emcli sync”
  6. Upgrade the agent by running “$OMS_HOME/bin/emcli upgrade_agents -additional_parameters=”-ignorePrereqs” -agents=”hostname.domain.com:3872″
  7. Wait while the upgrade proceeds.  You can view upgrade progress by running “$OMS_HOME/bin/emcli get_agent_upgrade_status”, or in the GUI by clicking on “Agent Upgrade Results” in the “Upgrade Agents” pageAgent Upgrade Progress
  8. Click Done once the central agent upgrade completes.  Go to the new agent home and run root.sh as root or via sudo
  9. Then repeat this process for the rest of your agents.  Try to install them first WITHOUT using the “-ignorePrereqs” flag, because if there are missing prerequisites you need to identify the issue and find out if it is something that it is appropriate to ignore, as the libstdc++ version was in my case.  Execute root.sh for each agent afterwards, unless you have sudo configured in which case it will happen automatically
  10. Two of my agents that run on different platforms could not be upgraded right away.  The new versions of the agent software needed to be downloaded from Self Update.  I am skipping them for now
  11. Next, apply patch 13349651 to WebLogic, following the instructions in the README file.  I attempted to do so, but the patch was already installed so I skipped this step
  12. There are a few other optional, post-upgrade steps like deleting obsolete targets.  These are documented in the upgrade guide and I will not note them here
  13. As a final step, make sure you update your Oracle user’s environment variables to reflect the new middleware home, OMS home, agent home, and so on

Conclusion

At this point my EM12cR3 production upgrade is complete!  Everything I have checked so far appears fully functional.  The only problem I had was a small filesystem for the management agent on my sandbox server causing the agent upgrade to run out of space, forcing manual intervention to resolve.  Don’t be stingy with space like I am and you should be fine.

I haven’t taken any time to investigate the new features yet, but I will be now.

(EDITED TO ADD: I forgot to mention the steps to get BI Publisher working again.  Please refer to the EM12cR3 Advanced Installation and Configuration Guide, chapter 15.  Essentially you will need to perform a software-only installation into the new middleware home, then execute the configureBIP script with an -upgrade flag to complete the BIP setup.)

Using EM12c Compliance Rules, Standards, and Frameworks

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.

Getting Started

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:

  1. Login to EM12c as the repository owner (SYSMAN)
  2. Navigate to the Management Pack Access screen via the Setup menu, then the Management Packs submenu
  3. If not selected already, select the “Target Based” Pack Access radio button
  4. If not selected already, select “Database” from the search drop-down
  5. Click the Go button
  6. Check the box in the Database Lifecycle Management Pack column for each database where you have this pack licensed and then click the Apply button
Management Pack Access screen

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

Collecting Data Needed For Compliance Monitoring

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:

  1. Click on the Enterprise menu, then the Monitoring submenu, then Monitoring Templates
  2. Check the box next to “Display Oracle Certified templates”
  3. Click the Go button
  4. Select the radio button next to “Oracle Certified-Enable Database Security Configuration Metrics”
  5. Click the Apply button
  6. On the next page, click the Add button to select the database targets for which you will use the compliance functionality
  7. Click the OK button
  8. Repeat these steps for the “Oracle Certified-Enable Listener Security Configuration Metrics” and your listener targets if you intend to monitor listener compliance
Applying out-of-box templates to enable security configuration metrics

Applying out-of-box templates to enable security configuration metrics

Compliance Frameworks vs Compliance Standards vs Compliance Rules

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.

Working With Compliance Standards

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

Searching for a compliance standard and associating targets

You now have at least one target associated to a compliance standard.  So what now?

Viewing Compliance Results

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

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

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

View of compliance standard check details

Drill down into compliance rule 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.

Customizing Compliance Monitoring

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

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

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

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

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

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", "Medium" and "High" correspond to "Minor Warning", "Warning" and "Critical"

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.

Changing A Compliance 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

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

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!

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

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?

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

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

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

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?

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.

Creating A Compliance Framework

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?

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?

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!

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

Time for a cold beer…

Conclusion

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)