Improving security in your web browsers: Firefox

(Update 20140110: Removed recommendation to enable security.ssl.enable_false_start, as it appears to be unsafe.)

(Update 20140107: Added recommendation to disable security.ssl3.rsa_des_ede3_sha.)

(Update 20131212:  I have revised my recommendations.  I now recommend Adblock Edge instead of Adblock Plus, and Disconnect instead of Ghostery, with the reasons noted inline below.

At this time Firefox has released version 26 which includes click-to-play functionality for Java and other plugin content.  This change will greatly enhance your security.  Upgrade now!)

Your web browsers implement poor security by default.  They do this, in large part, for interoperability reasons; if your just-downloaded new browser can’t connect to the sites you like to use, you either won’t use the browser or you’ll complain to the developers, and they don’t want to spend the time walking you through how to disable the specific security settings keeping you from using some random website that hasn’t upgraded their SSL implementation since 2002.

With effort and testing, you can significantly improve your security.  Don’t hold me responsible if this breaks your favorite site or eats all the food in your fridge, but if you want to step up and accept that security and convenience don’t go together, consider trying some or all of these steps to secure your Firefox browser.  I have Windows in front of me at the moment, but if you use a real operating system you can figure out how to perform the appropriate changes there.  Consider the fact that using Windows represents a greater security threat than almost anything else you can do.

Do note that even if you follow every suggestion I make on this page, you have not guaranteed security for yourself.  These steps cannot protect you from foolish decisions.  If, after doing all of this, you then proceed to visit some shady site and download a cracked version of some commercial software product, then execute it, you will get hacked, you will get compromised, you will get malware.

Why Security?

Only you know the adversaries you may have.  The malware spewed across the internet presents a risk to us all and these steps can help protect you from it.  But beyond that point, if you want to protect yourself from a determined adversary, then please only consider the steps I describe as a start.  If you work with confidential corporate documents, or if you work to promote human rights in repressive countries, or if you write news articles disclosing secret government projects, or if you run a hidden site selling drugs for bitcoins, you have a threat model much more complex than the average user.

Security Defined

One could write a book to define the word security.  Many have.  For the purposes of this post, I define security as protection against your own accidental mistakes, protection against common malware techniques and protection against an attacker with access to your network or the internet path between you and the sites you visit.  Further, I consider security to include not leaking unnecessary information about yourself or your browsing habits to third parties that want that information, such as advertisers.

Run A Current Browser

Using an old browser begs for trouble.  Just don’t do it.  For now I have Firefox 25 installed and everything I write here applies to this version and hopefully future versions.  Go to the Tools menu, select Options, then click on Advanced and select the Update tab.  Enable the radio button next to “Automatically install updates”.

Simple Steps

The steps described here shouldn’t significantly degrade your web browser experience but will improve your security quite a bit.  Everything in this section lives in the Tools->Options dialog box.  Open it up now.

Options: Tabs

If checked, uncheck the box next to “Show tab previews in the Windows taskbar”.  Windows has a history of buffer overflows in graphics handlers, and a specially crafted tab preview could potentially exploit this.  I do not know of this ever happening but no need to take the risk simply for some eye candy.

Options: Content

Check the box next to “Block pop-up windows”.  Compromised or otherwise malicious sites love to put up confusing pop-up windows saying “your computer has a virus” and other such nonsense.  The next time you go to a site that attempts to raise a pop-up window, Firefox will ask if you wish to allow an exception for that site.  If this happens on a site you need, allow the exception.  If a bad site can’t pop up a window to attempt to fool you, you won’t click on their shady links.

Click the “Choose…” button next to “Choose your preferred language for displaying pages”.  Make sure the contents of the language dialog box reflect only those languages you wish to read.

Options: Applications

Click through every row of this screen and use the drop-down menu on the right-hand side to select “Always ask”, so that Firefox will prompt to ask how (and more importantly, if) you wish to access embedded content like videos, music, PDF documents, etc.  This may get inconvenient over time if you access a lot of media, so later on, when prompted to select an application to view media, you may choose to select the “Do this automatically for files like this from now on” checkbox in the prompt but know that this reduces your overall security slightly.

Options: Privacy

Enable the radio button next to “Tell sites that I do not want to be tracked”.  This will cause your browser to send the Do-Not-Track header. Few webservers will respect this setting, but some will, so you get some small value here.

In the History section, select “Use custom settings for history” from the “Firefox will:” dropdown menu.  For the sake of convenience, go ahead and leave the checkboxes enabled for “Remember my browsing and download history” and “Remember search and form history”.  I recommend disabling them, but the convenience of having recently visited sites available outweighs the risk of having to search for a site repeatedly and possibly clicking on a malicious search engine result.

Go ahead and leave the checkbox enabled for “Accept cookies from sites”, or very few websites will work.  Set the “Accept third-party cookies” dropdown menu to “From visited”, NOT to “Always”.  Many sites will not work if you set it to “Never”, nearly every site will still work fine with it set to “From visited”.  “Always”, in this case, begs to be tracked by marketers.

In the “Keep until:” dropdown menu, select “they expire”.  Some people would recommend deleting cookies every time the browser closes, but you will lose the convenience of having sites recognize you when you want them to.  If you can tolerate that loss of convenience go ahead and select “I close Firefox”.

Check out the “Exceptions…” button near the “Accept cookies from sites” checkbox.  Here you can add exceptions to specify sites always allowed to set cookies, or never allowed to set cookies.  I love this feature.  I coded this feature into the text-based Lynx web browser back in 1999 and it pleases me that the GUI browsers picked it up.

Options: Security

Check the checkboxes next to “Warn me when sites try to install add-ons”, “Block reported attack sites” and “Block reported web forgeries”.

Uncheck the “Remember passwords for sites” checkbox.  If you permit the browser to store your passwords, anyone with access to your browser can retrieve your passwords.  I suggest only enabling this if you have taken the further step of encrypting your hard drive.  If you do enable it, make sure you also enable the “Use a master password” option and select a strong password.

Options: Sync

Do not use Firefox Sync.  This will simply spread your information out over more devices, increasing your risk.

Options: Advanced

On the “General” tab, check the box next to “Warn me when websites try to redirect or reload the page”.

On the “Data Choices” tab, uncheck everything.  All of these options share information with Mozilla and you do not want that to happen.

On the “Network” tab, check the box next to “Tell me when a website asks to store data for offline use”.  Most likely you do not actually want any sites to do this.

On the “Certificates” tab, click the “Validation” button and enable the checkboxes to use the Online Certificate Status Protocol to confirm certificate validity and to treat certificates as invalid when an OCSP server connection fails.  While not foolproof, this can help protect against invalid or compromised server certificates.

Intermediate Steps

If you have followed everything so far, you have improved your browser security.  Not enough, in my opinion, but perhaps enough if you plan to hand this browser off to your tech-challenged grandparents to use to look up recipes and email pictures of their grandkids.  If you have a decent comfort level with basic internet and browser concepts, continue on.

Install Add-Ons

Numerous add-ons available for Firefox can further enhance your security.  Here I will list the ones I consider most critical, along with some comments on configuration/usage for each of them.

Disconnect

Install Disconnect. This add-on identifies and blocks various web trackers embedded throughout the sites you visit.  Mostly analytics and marketing, rather than anything truly security related, but you don’t want any part of those either.  The developers have released the source code and development supported by donations.  It takes note of sites that host trackers but also host page elements that may cause a page to function incorrectly if blocked.

I previously recommended Ghostery for this purpose.  However, after witnessing a recent Twitter conversation involving one of Ghostery’s developers, I felt he represented the product poorly and lost faith in it.  Further, the company behind Ghostery includes many former ad-agency employees, providing another strike against it on top of their opt-in data collection.

Adblock Plus Adblock Edge

Install Adblock Plus Adblock Edge. Ads on webpages may not represent an obvious security issue, but I still consider blocking them appropriate for a secured browser.  When your browser loads an ad from a page the advertiser will know that somebody from your IP address viewed a page containing that ad, and depending on how the ad gets served up they may also learn the page you intended to view at the same time.  Further, traffic analysis of specially placed ads may reveal information about the sites you visit as ads typically do not use https connections, and if somebody with access to your network sees that you repeatedly load some specific ad that only appears on a particular site, they would then have strong evidence that you visit that site repeatedly.

Within the Adblock Plus Edge options, subscribe to EasyList EasyPrivacy+EasyList, Fanboy’s Social Blocking List and Malware Domains., and uncheck the “Allow some non-intrusive advertising” checkbox.  If you live outside the USA, subscribe to some of the additional filter lists dedicated to your region.

I have changed my recommendation as of December 12, 2013. Adblock Edge performs better and does not receive money from Internet advertisers to permit “some non-intrusive advertising”.

BetterPrivacy

Install BetterPrivacy. This add-on removes persistent Flash cookies, for which browsers generally provide no control mechanism.  Within the options screen, select the radio button for “Delete Flash cookies on Firefox exit”.  Select the checkboxes for “Auto protect LSO sub-folders” and “Notify if new LSO is stored”.  Check the box for “Disable Ping Tracking”.

Certificate Patrol

Install Certificate Patrol. This add-on stores all SSL certificates you encounter when accessing https sites, and notifies you when a site you connect to has changed certificates since your last visit.  A changed certificate may indicate an attempted man-in-the-middle attack that would compromise your encrypted session.  I receive a lot of false positives with this add-on, which defeats its utility somewhat, but I review every single change.  If you want to skip one of these add-ons, make it this one.  I haven’t convinced myself that I take enough care to actually identify a man-in-the-middle attack, and I can’t exactly call someone at Google every time their cert changes to confirm they meant to do so.

Ghostery

Install Ghostery. This add-on identifies and blocks various web trackers embedded throughout the sites you visit.  Mostly analytics and marketing, rather than anything truly security related, but you don’t want any part of those either.  Unfortunately some sites will not function properly with Ghostery installed, but it provides options to whitelist those sites or temporarily pause blocking so that you can easily determine if Ghostery has caused the page to fail.  I end up having to whitelist bank sites, WordPress, a few others, but for just clicking through search results, I love it.  It also has the ability to block advertising cookies.

I have changed my recommendation to use Ghostery as of December 12, 2013.  Please see the “Disconnect” section above for details on why I no longer recommend Ghostery.

Long URL Please Mod

Install Long URL Please Mod.  Shortened URLs suck.  You don’t know where they will lead, and if you take security seriously you probably won’t click on them.  This add-on expands short URLs for you so that you know where they lead and can make an educated decision as to whether or not you want to follow that link.

NoScript

Install NoScript. Perhaps the most important add-on to use. This add-on provides the ability to permit or reject active scripting to run on a per-domain or per-host basis.  It will, initially, block all JavaScript on every site, which will break large portions of the web for you.  In this case, as you find sites that don’t work, you use the button it adds to the browser bar to enable scripting (temporarily or permanently) for that particular site, reload the page, and everything should then function as intended.  Sites get classified into trusted (whitelisted), untrusted, and those that you haven’t yet evaluated.

As a bonus, it also provides protection against cross-site-scripting and clickjacking (where a malicious site overlays an invisible object over a page element, intercepting a click on that element as a click directed at the malicious site, allowing it to load a page/code/etc).

NoScript has numerous configuration options.  I recommend the following:

Do NOT check the “Scripts Globally Allowed” box, as this essentially disables the add-on and leaves you back in the usual situation of freely running all JavaScript submitted to your browser.

On the “Embeddings” tab, you can specify restrictions for untrusted sites that do not apply to whitelisted sites.  This gives you a chance to use paranoid settings, as you can always whitelist a site later.  I don’t want to make them so restrictive that I end up whitelisting every other site, so I don’t block frames, but I do block: Java, Flash, Silverlight, other plugins, audio/video tags, and font-face, and I also block every object coming from sites marked as untrusted.  I also enable “Show placeholder icon”, “No placeholder for objects coming from sites marked as untrusted”, “Ask for confirmation before temporarily unblocking an object” and “Collapse blocked objects”.  I also check the box for ClearClick (clickjacking) protection on untrusted pages.  Some whitelisted pages don’t work if I enable ClearClick protection for trusted pages, so I leave that one off.

In the “Advanced” tab, on the “Untrusted” sub-tab, check “Forbid <a ping…>”, “Forbid META redirections inside <NOSCRIPT> elements”, “Forbid XSLT” and “Attempt to fix JavaScript links”.  On the “XSS” tab, I check “Sanitize cross-site suspicious requests” and “Turn cross-site POST requests into data-less GET requests”.

NoScript can do even more than this, and you should look into the other options.  The configuration set I have described works well for my browsing habits.

WOT

Install WOT. This add-on uses a crowdsourced set of website rankings to provide you with a simple red (bad) / yellow (maybe bad) / green (good) ranking for every site you visit and all sites that appear in search results from Google.  It further takes advantage of blacklists published by anti-virus vendors and other independent sources to identify malicious sites.  You do not have to do so, but if you choose to create an account with them you can submit your own ratings.  WOT uses a complex reputation mechanism to determine how much weight to give your ratings when compiling them with others’ to determine a site’s overall rating; this helps prevent malicious individuals from installing the add-on and voting up a bunch of malware infested sites.

Expert Steps

Doing everything, or even some of the things, that I’ve listed to this point will greatly improve your browser security.  But you can do more.  At this point I will get into the weeds a bit and make some significant changes to browser operation.  These changes may (and probably will) cause problems accessing poorly configured sites, but if you use sites configured so poorly, maybe you shouldn’t.  I recommend, if you follow these suggestions, that you implement them one at a time, and test all the sites you consider most important.  If you change a dozen things and suddenly some page stops working, you won’t know what to undo to restore it to functionality.  As an example, while writing up this post I noticed that addons.mozilla.org started to throw intermittent SSL errors when I tried to connect to it.  Hitting reload would usually load the page just fine.  It turned out that disabling RC4 cipher suites for SSL negotiation caused that problem: apparently not all of the servers behind their load balancer have the same configuration, and some of them just don’t work if the client browser does not accept RC4.

about:config

Everything else happens in the about:config screen.  If you haven’t used it before, type “about:config” into your address bar and hit enter.  Click through the warning that says it might break stuff, but recognize they put it there for a reason.

Disable RC4

The RC4 symmetric cipher contains significant failings.  You should not use it.  In fact, if you admin any webservers, leave this blog now and go figure out how to disable RC4 on them.  Then come back and finish securing your browser.  If you need convincing, read this: “Attack of the week: RC4 is kind of broken in TLS“.

In the about:config page, type “rc4″ into the search bar and press enter.  You will see several cipher suites listed (with names like “security.ssl3.rsa_rc4_128_sha”).  Double-click on each of them so that the value field on the right reads “false”.  Your browser will no longer advertise willingness to accept RC4 as a component in an SSL connection.

Require TLS

Type “tls” into the about:config search bar and press enter.  Find the “security.tls.version.min” key, which defaults to 0, and change it to 1.  Set the “security.tls.version.max” key, which defaults to 1, to 3. [EDIT 20131112: I previously recommended 2 here, for TLS 1.1, thinking it would cause fewer connection failures than 3 for TLS 1.2. This won't be a problem once Firefox has fallback code from TLS 1.2. But if you are following these steps you should know how to debug and fix any connection problems you have.] For more information on these settings and what they do, see this link.

Disable additional insecure cipher suites

(Added 20140107) Type “rsa_des_ede3″ into the about:config search bar and press enter.  Find the “security.ssl3.rsa_des_ede3_sha” key and double-click it to set the value to false.  This will remove SSL_RSA_FIPS_WITH_3DES_EDE_CBC_SHA from the cipher suites for which your client will advertise support.  Thanks to Jeff Hodges for creating howsmyssl.com through which I noticed this item.

Other Settings

(This section edited on 20140110, after the comment below from Ismail Dönmez.  Please see that comment for a link to the Firefox bug database entry concerning security.ssl.enable_false_start.)

Type “security” into the about:config search bar and press enter.  Find the “security.ssl.enable_false_start” key and double-click it to set the value to true.  Do the same for “security.ssl.false_start.require-forward-secrecy”, “security.ssl.require_safe_negotiation”, and “security.ssl.treat_unsafe_negotiation_as_broken”.  Read this link for more information about these settings.

Conclusion

If most of your web browsing still works after configuring all this stuff, congratulations.  You probably browse safely enough that you don’t have much to worry about.  If you run into sites that don’t work with these settings, consider whether or not you really need to visit them.  Good luck!

SQL to query table size and DBMS_REDEFINITION progress

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.

How to migrate EM12c R3 OMS and repository to a new host

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

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

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

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

Relocating the Management Repository Database

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

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

Cloning the Database Home

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

oracle$ $OMS_HOME/bin/emctl stop oms -all ; $AGENT_HOME/bin/emctl stop agent ; $ORACLE_HOME/bin/dbshut $ORACLE_HOME

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

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

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

oracle$ $ORACLE_HOME/bin/dbstart $ORACLE_HOME ; sleep 10 ; $OMS_HOME/bin/emctl start oms ; sleep 10 ; $AGENT_HOME/bin/emctl start agent

Copy this zip file to the new host.

oracle$  scp dbhome_1.zip oracle@newhost:/oracle/oem

On the new host, extract this zip file to the target directory.

oracle@newhost$ unzip -d / dbhome_1.zip

Remove all “*.ora” files from the extracted $ORACLE_HOME/network/admin directory.

oracle@newhost$  rm /oracle/oem/product/11.2.0/dbhome_1/network/admin/*.ora

Execute clone.pl from $ORACLE_HOME/clone/bin.

oracle@newhost$ export ORACLE_HOME=/oracle/oem/product/11.2.0/dbhome_1
oracle@newhost$ $ORACLE_HOME/perl/bin/perl clone.pl ORACLE_BASE="/oracle/oem" ORACLE_HOME="/oracle/oem/product/11.2.0/dbhome_1" OSDBA_GROUP=dba OSOPER_GROUP=oper -defaultHomeName

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

oracle@newhost$ cp -a ~/oraInventory /oracle/oem
oracle@newhost$ cd /oracle/oem/oraInventory
oracle@newhost$ perl -pi.bak -e 's#/home/oracle#/oracle/oem#' oraInst.loc orainstRoot.sh

Complete the cloning steps by running the orainstRoot.sh and root.sh scripts.

oracle@newhost$ sudo /oracle/oem/oraInventory/orainstRoot.sh
Changing permissions of /oracle/oem/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /oracle/oem/oraInventory to dba.
The execution of the script is complete.
oracle@newhost$ sudo /oracle/oem/product/11.2.0/dbhome_1/root.sh
Check /oracle/oem/product/11.2.0/dbhome_1/install/root_newhost_2013-08-27_13-04-51.log for the output of root script

I do not want to use netca to configure the listener, so I will just copy the $ORACLE_HOME/network/admin/*.ora files back over from the original server to the new server, and edit them accordingly.

oracle$ scp *.ora oracle@newhost:/oracle/oem/product/11.2.0/dbhome_1/network/admin/ 

oracle@newhost$ cd $ORACLE_HOME/network/admin
oracle@newhost$ perl -pi.bak -e 's#oldhost#newhost#' *.ora

This completes the database cloning.

Start Management Repository Database On New Host

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

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

oracle@newhost$ mkdir -p /oracle/oem/admin/emrep/adump
oracle@newhost$ mkdir -p /oracle/mirror/emrep/cntrl ; mkdir -p /oracle/mirror/emrep/log

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

Shutdown the OMS, agent and database on the original server.

oracle$ $OMS_HOME/bin/emctl stop oms -all ; $AGENT_HOME/bin/emctl stop agent ; $ORACLE_HOME/bin/dbshut $ORACLE_HOME

Copy the controlfiles and redo logs from the original server to the new server.

oracle$ scp /oracle/oem/cntrl/control01.ctl oracle@newhost:/oracle/oem/cntrl/control01.ctl
oracle$ scp /oracle/mirror/emrep/cntrl/control02.ctl oracle@newhost:/oracle/mirror/emrep/cntrl/control02.ctl
oracle$ scp /oracle/oem/log/redo* oracle@newhost:/oracle/oem/log
oracle$ scp /oracle/mirror/emrep/log/redo* oracle@newhost:/oracle/mirror/emrep/log

Back on the new server, start up the listener, then the database.  I probably should have disabled flashback first.

oracle@newhost$ lsnrctl start LISTENER
oracle@newhost$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 28 10:09:01 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 9620525056 bytes
Fixed Size                  2236488 bytes
Variable Size            6241128376 bytes
Database Buffers         3355443200 bytes
Redo Buffers               21716992 bytes

Database mounted.
ORA-38760: This database instance failed to turn on flashback database
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database flashback off;

Database altered.

SQL> alter database open;

Database altered.

Reconfigure Existing OMS For New Repository Database

Start the OMS and agent on the original server.  OMS startup will fail, as you have not yet reconfigured the repository.

oracle$ $OMS_HOME/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier Successfully Started
Oracle Management Server is not functioning because of the following reason:
Failed to connect to repository database. OMS will be automatically restarted once it identifies that database and listener are up.
Check EM Server log file for details: /oracle/oem/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/logs/EMGC_OMS1.out
oracle$ $AGENT_HOME/bin/emctl start agent

Reconfigure the OMS repository database connection.  Provide SYSMAN’s password when prompted.

oracle$ $OMS_HOME/bin/emctl config oms -store_repos_details -repos_conndesc "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=newhost)(PORT=1521)))(CONNECT_DATA=(SID=emrep)))" -repos_user sysman
Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Enter Repository User's Password : 
Successfully updated datasources and stored repository details in Credential Store.
If there are multiple OMSs in this environment, run this store_repos_details command on all of them.
And finally, restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.
It is also necessary to restart the BI Publisher Managed Server.

Stop, then restart the OMS.

oracle$ $OMS_HOME/bin/emctl stop oms -all ; sleep 5 ; $OMS_HOME/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down
Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up

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

oracle$ $OMS_HOME/bin/emctl config emrep -conn_desc "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=newhost)(PORT=1521)))(CONNECT_DATA=(SID=emrep)))"
Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Please enter repository password:                                    Enter password :                                               Login successful
Target "Management Services and Repository:oracle_emrep" modified successfully
Command completed successfully!

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

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

Installing OMS On A New Host

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

Identifying Installed Patches

oracle$ $OMS_HOME/OPatch/opatch lsinv -oh $OMS_HOME
[...]
Interim patches (1) :

Patch  13983293     : applied on Thu Jul 11 09:56:16 EDT 2013
Unique Patch ID:  14779750
   Created on 25 Apr 2012, 02:18:06 hrs PST8PDT
   Bugs fixed:
     13587457, 13425845, 11822929

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

Identifying Installed Plugins

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

SELECT epv.display_name, epv.plugin_id, epv.version, epv.rev_version,decode(su.aru_file, null, 'Media/External', 'https://updates.oracle.com/Orion/Services/download/'||aru_file||'?aru='||aru_id||chr(38)||'patch_file='||aru_file) URL
FROM em_plugin_version epv, em_current_deployed_plugin ecp, em_su_entities su
WHERE epv.plugin_type NOT IN ('BUILT_IN_TARGET_TYPE', 'INSTALL_HOME')
AND ecp.dest_type='2'
AND epv.plugin_version_id = ecp.plugin_version_id
AND su.entity_id = epv.su_entity_id;

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

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

Perform Software-Only Installation Of EM12c R3

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

oracle@newhost$ unzip V38641-01.zip ; unzip V38642-01.zip ; unzip V38643-01.zip 
[...]
oracle@newhost$ ./runInstaller

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

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

oracle$ $OMS_HOME/bin/emctl exportconfig oms
Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password : 
ExportConfig started...
Machine is Admin Server host. Performing Admin Server backup...
Exporting emoms properties...
Exporting secure properties...

Export has determined that the OMS is not fronted 
by an SLB. The local hostname was NOT exported. 
The exported data can be imported on any host but 
resecure of all agents will be required. Please 
see the EM Advanced Configuration Guide for more 
details.

Exporting configuration for pluggable modules...
Preparing archive file...
Backup has been written to file: /oracle/oem/gc_inst/em/EMGC_OMS1/sysman/backup/opf_ADMIN_20130828_120424.bka

The export file contains sensitive data. 
 You must keep it secure.

ExportConfig completed successfully!

Copy that backup file to the new server.

oracle$  scp /oracle/oem/gc_inst/em/EMGC_OMS1/sysman/backup/opf_ADMIN_20130828_120424.bka oracle@newhost:/oracle/oem

Once the software-only install finishes, it will prompt you to run allroot.sh.  Do so.

oracle@newhost$ sudo /oracle/oem/Middleware12cR3/oms/allroot.sh 

Starting to execute allroot.sh ......... 

Starting to execute /oracle/oem/Middleware12cR3/oms/root.sh ......
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /oracle/oem/Middleware12cR3/oms

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: 
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: 
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: 

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
/etc exist

Creating /etc/oragchomelist file...
/oracle/oem/Middleware12cR3/oms
Finished execution of  /oracle/oem/Middleware12cR3/oms/root.sh ......

Starting to execute /oracle/oem/agent12c/core/12.1.0.3.0/root.sh ......
Finished product-specific root actions.
/etc exist
/oracle/oem/agent12c/core/12.1.0.3.0
Finished execution of  /oracle/oem/agent12c/core/12.1.0.3.0/root.sh ......

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

oracle@newhost$ /oracle/oem/Middleware12cR3/oms/sysman/install/PluginInstall.sh -pluginLocation /oracle/oem/migration/plugins
This must match the list you generated previously

This must match the list you generated previously

Prepare the Software Library

Go to the original server, and copy the contents of the software library to the new server.

oracle$ scp -r /oracle/oem/software_library/ oracle@newhost:/oracle/oem

Recreate the OMS with OMSCA

Shut everything down on your old server.

oracle$ $OMS_HOME/bin/emctl stop oms -all ; sleep 5 ; $AGENT_HOME/bin/emctl stop agent

Run OMSCA using the exportconfig backup file you generated earlier.  Enter the administration server, node manager, repository database user and agent registration passwords when prompted.

oracle@newhost$ $OMS_HOME/bin/omsca recover -as -ms -nostart -backup_file /oracle/oem/opf_ADMIN_20130828_120424.bka
Oracle Enterprise Manager Cloud Control 12c Release 12.1.0.3.0
Copyright (c) 1996, 2013, Oracle. All rights reserved.

OS check passed.
OMS version check passed.
Performing Admin Server Recovery...
Retrieved Admin Server template.
Source Instance Host name where configuration is exported : [deleted]
Populated install params from backup...
Enter Administration Server user password:
Confirm Password:
Enter Node Manager Password:
Confirm Password:
Enter Repository database user password:
Enter Agent Registration password:
Confirm Password:
Doing pre requisite checks ......
Pre requisite checks completed successfully

Checking Plugin software bits
Proceed to recovery
Setting up domain from template...
Setup EM infrastructure succeeded!
Admin Server recovered from backup.
Now performing cleanup of OMS EMGC_OMS1...
Now launching DeleteOMS...
OMS Deleted successfully

Delete finished successfully
Now launching AddOMS...
Infrastructure setup of EM completed successfully.

Doing pre deployment operations ......
Pre deployment of EM completed successfully.

Deploying EM ......
Deployment of EM completed successfully.

Configuring webtier ......
Configuring webTier completed successfully.

Importing OMS configuration from recovery file...

If you have software library configured 
please make sure it is functional and accessible 
from this OMS by visiting:
 Setup->Provisioning and Patching->Software Library

Securing OMS ......
Adapter already exists: emgc_USER
Adapter already exists: emgc_GROUP
Post "Deploy and Repos Setup" operations completed successfully.

Performing Post deploy operations ....
Total 0 errors, 78 warnings. 0 entities imported.
pluginID:oracle.sysman.core
Done with csg import
pluginID:oracle.sysman.core
Done with csg import
No logging has been configured and default agent logging support is unavailable.
Post deploy operations completed successfully.

EM configuration completed successfully.
EM URL is:https://newhost:7803/em

Add OMS finished successfully
Recovery of server EMGC_OMS1 completed successfully
OMSCA Recover completed successfully

Start the OMS on the new server.

oracle@newhost$ $OMS_HOME/bin/emctl start oms

Configure the central agent on the new server, then run the root.sh script.

oracle@newhost$ /oracle/oem/agent12c/core/12.1.0.3.0/sysman/install/agentDeploy.sh AGENT_BASE_DIR=/oracle/oem/agent12c AGENT_INSTANCE_HOME=/oracle/oem/agent12c/agent_inst AGENT_PORT=3872 -configOnly OMS_HOST=newhost EM_UPLOAD_PORT=4902 AGENT_REGISTRATION_PASSWORD=password
[...]
oracle@newhost$ sudo /oracle/oem/agent12c/core/12.1.0.3.0/root.sh

Relocate the oracle_emrep target to the new OMS host.

oracle@newhost$ $OMS_HOME/bin/emcli login -username=sysman
Enter password : 

Login successful
oracle@newhost$ $OMS_HOME/bin/emcli sync
Synchronized successfully
oracle@newhost$ $OMS_HOME/bin/emctl config emrep -agent newhost:3872
Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Please enter repository password: 
Enter password :                                                               
Login successful
Moved all targets from oldhost:3872 to newhost:3872
Command completed successfully!
Enter password :                                                               
Login successful
Moved all targets from oldhost:3872 to newhost:3872
Command completed successfully!

Step through each of your existing agents to re-secure them against the new OMS.  Provide the OMS HTTP port (not HTTPS) in this command, and enter the agent registration password when prompted.

$ $AGENT_INSTANCE_DIR/bin/emctl secure agent -emdWalletSrcUrl "http://newhost:4890/em"
Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Agent successfully stopped...   Done.
Securing agent...   Started.
Enter Agent Registration Password : 
Agent successfully restarted...   Done.
EMD gensudoprops completed successfully
Securing agent...   Successful.

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

oracle$ $AGENT_HOME/bin/emctl start agent
oracle$ $AGENT_INSTANCE_DIR/bin/emctl secure agent -emdWalletSrcUrl "http://newhost:4890/em"

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

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

oracle@newhost$ ./emcli relocate_targets -src_agent=oldhost:3872 -dest_agent=newhost:3872 -copy_from_src -target_name=nameoftarget -target_type=typeoftarget

Final Cleanup Steps

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

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

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

Reinstall BI Publisher

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

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

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

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

Update EM Console Service

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

Remove Previous OMS Server From OEM

Stop the agent on your original OMS server.

oracle$ $AGENT_HOME/bin/emctl stop agent

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

One Last Bounce

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

Conclusion

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

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.
I'm Feeling Lucky

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)

How to get started with genetic genealogy

This is a departure from what I usually write about, but technically it’s also about databases: GEDCOMs and genetic ones. This post will cover a general strategy to get started doing your own genetic genealogy work. I appreciate any comments you may have. If anyone is interested, I may write future posts on suggested tools and other tips.

Briefly, genetic genealogy is the act of supplementing traditional paper genealogy with genetic information. By doing so you can extend your family tree further, find distant (sometimes extremely distant) relatives and help confirm the details found in your genealogy research. If you were adopted or have known NPEs in your line back a few generations, this may be the only way to track down your real ancestors.

Overview

  1. Do as much genealogy as you can on paper
  2. Get yourself, and possibly other close relatives tested, by one of the well known companies whose tests enable this work
  3. Make contact with your matches as identified by those companies
  4. Compare family trees with your matches
  5. Share your genetic ancestry data in other places to broaden the scope of potential matches
  6. Extend your tree with the results of research done by your matches on your shared lines
  7. Make more contacts and use your previously confirmed ancestors to triangulate on your unknown matches

Step 1: Do Genealogy

So many others have written so much about getting started with and getting better at genealogy that I’m not going to cover this step in very much detail here. Do a few web searches, read what others have to say, and check for “how to” articles on any commercial genealogy sites you join.

The best way, in my opinion, to get started with genealogy is to stand on the shoulders of giants. Someone in your family, maybe a grandparent or second cousin probably already does genealogy research and would be happy to share their data. But in case you can’t find someone like that or just want to get started on your own, here’s a little advice.

Make an account on ancestry.com. They simply have one of the best, easiest to use archives of vital records, wills, immigrant entries, military records, newspaper articles and so on. You can start with a 14 day free full access subscription and try to nail down as much as possible, then choose to subscribe or not depending on how much progress you’re making.

The mid term goal of this genealogy work is to produce a GEDCOM file, which is a database of people, their relationships, and source citations back to primary documents that confirm the relationship claims made in the file. You will then upload this file to various sites to share your research and help others find their match to you. You can optionally privatize the file so that people born after 1900 have their names hidden to avoid revealing information about other people that may not share your enthusiasm for finding your roots.

While you work on your genealogy, proceed with DNA testing, the next step, because it takes a while and you’ll be spending a while waiting for your results.

Step 2: Get tested

You have several choices for testing. The big three companies are 23andMe, FamilyTreeDNA and AncestryDNA, but several other options exist for specialized use. I highly recommend 23andMe, for reasons I’ll explain below, but I’ll give some information about each. All three are based in the USA so the longer your family has been in the US, the more matches you will find (see digression below).

23andMe

Simply your best choice. For the same price, $99, you will receive genetic information about your health at the same time you receive information useful for genetic genealogy. 23andMe has busy community forums covering health, ancestry and genealogy, but the best part for our purposes is that they test more markers than the other options (since the other companies specifically do not test anything implicated in human health) and you can download your raw genetic data and have it processed by FamilyTreeDNA for a lower fee than having FTDNA test you directly.

The 23andMe test is a saliva test. They will send you a kit including a tube, into which you spit about a teaspoon of saliva, close the top, snap the paraffin seal to release the stabilization/lysing buffer solution and then send it back in a prepaid package. Totally painless unless you have trouble producing saliva or you are trying to test an infant.

FamilyTreeDNA

The strong point of FTDNA is 23andMe’s weak point. You only sign up for FTDNA if you are interested in genealogy, but many of 23andMe’s users are only there for health information and have zero interest in genealogy or helping you to research yours. The other strong point is their “transfer family finder” service which allows you to upload your 23andMe data file to FTDNA for a better price than testing directly with them. You’ll still receive all the same matches and benefits as if you had tested there directly.

Further, FTDNA has some test offerings the others don’t provide. While 23andMe will test enough single nucleotide polymorphisms (SNPs) on your Y-DNA and mitochondrial DNA to assign a high level haplogroup, FTDNA provides full mitochondrial sequencing and Y-DNA short terminal repeat (STR) testing. The Y-DNA test can help confirm genealogy along your direct male ancestor line, but the mitochondrial sequence is relatively useless for this kind of genealogy. I’ve had a 67-marker Y-DNA STR test done along with a full mitochondrial sequence, plus the family finder transfer of my 23andMe data.

FamilyTreeDNA does provide a way for you to download your raw test results. Their test is done by scraping a cotton swab on the inside of your cheek.

AncestryDNA

They are the most recent new provider of these tests. I have not used their testing service so I have no first hand knowledge of it. As I understand it they will scan your tree to find genealogical matches with your DNA matches and simplify the process of identifying your common ancestors. This sounds great, and it may be the best choice for those who can’t invest much time in this work, but the downside is that Ancestry has many users who aren’t as careful about validating and sourcing the data in their trees as a serious genealogist needs to do. You really have to doublecheck your match’s work more carefully than on other sites. Being new, their database is currently the smallest of the big three, but it is growing rapidly.

AncestryDNA does support user download of their raw test result data file. As with 23andMe, their test is performed with a saliva sample.

A Digression

The quality and number of matches you will find on any of these sites depends significantly on your family background and the backgrounds of others who have elected to test. The majority of users on these sites are American, so if you are the second generation of an immigrant family, new to the US, you will find only a few matches. But if you can trace your lines to ancestors in the early US, you’re going to have hundreds or even thousands of matches. Or if you come from a highly endogenous population like the Ashkenazi Jews, you will have a lot of matches but they will be so far back in time you’ll have a lot difficulty finding on-paper genealogical links.

Step 3: Make contact

I should call this step “wait”, since no matter which company you use, it will take a few weeks or months to get your results back. Use this time to work on your family tree some more.

Once you do receive your results, the fun starts. If you don’t check your email very frequently or your results have been in a while, you may already have matches starting to contact you. FTDNA contacts are generally made directly through email to the address you share when signing up. For 23andMe users, you can send or receive a “sharing request”, which if accepted allows you and your match to compare your results to each other and your other matches with whom you have an accepted sharing request.

How do you find your matches? On FTDNA you go to the Family Finder Matches tool and review the list of names, their family trees, and the significance of your match. I’ll cover significance later. On 23andMe you go to the DNA Relatives tool and do the same thing, except most of your matches will have chosen not to reveal their name and family tree, so you’ll need to send them one of the sharing requests I mentioned and hope they accept. I imagine the process on AncestryDNA is both similar to and different from the way it works elsewhere.

Discuss your background with your matches and find out what surnames, locations, or other details your families may have in common. You may find a connection immediately, or there may be nothing obvious. File all this information away for later because you never know when you or they will update their family tree and your connection will suddenly be staring you in the face.

What does a match mean anyway?

The simple answer is that they share a portion of your DNA, based on both of you having inherited that portion from a common ancestor. The significance of the match is generally evaluated in terms of four variables:

  1. How many segments? A person with you match five segments on five different chromosomes is likely to be a much closer relative than someone with whom you match one segment on one chromosome.
  2. How long is the match? You measure the length of a match by examining the start and end positions on the chromosome where a segment matches. A match may be, for example, from position 16 million to position 50 million on chromosome 12. The longer the match, the closer it generally is, but see below.
  3. How densely tested is the match region? This is reported as a SNP count, the number of consecutive polymorphisms you share with your match on a segment. The more SNPs tested on a matching segment, the closer it generally is, but see below.
  4. How variable is the genomic region where you matching segment exists? Fortunately you don’t have to calculate this yourself. 23andMe and FTDNA will give you a number to represent this value for your matches. The variability of the region, combined with the length of a match and the number of tested SNPs all combine to give you a number of centiMorgans (cM) representing the significance of your match. Researchers disagree on how many cM a matching segment should have to be useful for genealogy, but bigger is definitely better. 5cM and 7cM are common minimum cutoffs. Anything larger than 10cM is quite useful in my opinion.

Long Technical Digression

The detailed answer is much more complex. Feel free to skip this part. I’m skipping over some details but what I’ve described below is accurate enough for genetic genealogy.

Each of our DNA sequences is unique, unless you have an identical twin. Our DNA is composed of 23 chromosomes, and we all have two of each (except in cases like trisomy where an individual has a third copy of a chromosome). One copy of each chromosome is inherited from your father and the other copy is inherited from your mother. Chromosomes 1-22 are the autosomes, while chromosome 23 is the sex chromosome. Women have two copies of the X sex chromosome, designated XX, while men have one copy of the X and one copy of the Y chromosome, designated XY.

Now, when you inherit one copy of each autosome from your two parents, you don’t inherit an exact copy. The autosomes split and recombine. To give an example, you have two copies of chromosome 1. One copy may have only one third of the genetic sequence come from your father’s chromosome 1, with two thirds of your mother’s chromosome 1. But your other copy of chromosome 1 may then have one third from your mother and two thirds from your father. Which of those two copies your child inherits will determine how much they received on chromosome 1 from your mother versus your father. Repeat this over many generations, and sequences break up and rejoin repeatedly over time. Because of this, the fundamental unit of genetic genealogy is the “half IBD segment”, which means “half identical by descent”. The half signifies that half of the segment — the half from one of your chromosomes, but not the other — is identical to one of someone else’s chromosomes, and that the segments being identical is due to both of you having inherited them from a common ancestors. The alternative is an “IBS”, or “identical by state” segment, in which case you and this other individual happened to randomly inherit sequences that match, but did NOT come from a common ancestor. You can’t easily identify these false positives in advance, so some proportion of your matches will be type 1 errors like this. You won’t ever find that match.

It gets even more complicated though. The commercial testing companies generally do not phase your genetic data. Instead they report the results of your SNP test at a position from both copies of your chromosomes, but they cannot tell if a given sequence of consecutive SNPs came from copy A or copy B of your chromosome. This will also contribute to false positive matches. There are ways around this, and if you phase your data you will have much better results with genetic genealogy. To phase your data you need to have both of your parents tested with the same test you take. That will allow comparison of your father’s DNA to yours, and your mother’s to yours, and you will have a much more accurate vision of your DNA. There are tools online to automate the process for you (such as GEDMatch), but you need to have at least one parent tested. Two are even better.

Unlike the autosomes, the sex chromosomes (X and Y) are inherited nearly unchanged from each parent. With detailed Y-DNA testing you can compare your direct male ancestor line back thousands of years. My Y-DNA test helped confirm that my male line descends from Pierre Paradis (1604 – 1675), of Montagne-au-Perche, France, who immigrated to Quebec in 1651, even though my genealogy on that line hits a brick wall with my fifth great grandfather Henry H Paradis, born around 1847 in Riviere-du-Loupe, Quebec. See this link on Paradis history if you’re interested in the line.

For various reasons, particularly the fact that women inherit one X from their mother and one X from their father, the X chromosome is not as useful for genetic genealogy as the Y chromosome. It does not travel an unbroken line of the same sex like the Y does.

Mitochondrial DNA on the other hand is passed only along the maternal line. Whether male or female, you inherited it from your mother. Unfortunately mitochondrial DNA changes so slowly that even if someone has an exact match to your full mitochondrial sequence, that could still be 20 generations back and extremely difficult to find. My mitochondrial haplotype, U2e1* points to early European ancestry and then further back to the Indian subcontinent but this is somewhere along the lines of 5000+ years ago and not useful for what I’m trying to do.

Complicating this further, we’re all related to each other somewhere. The hope is that you find people related closely enough that you can identify your genealogical link. But if, for example, you are of European descent, there’s a better than 95% chance that you descend from Charlemagne, probably along several lines (he was my 38th, 39th, and 40th great grandfather — yours too). Or if you trace back to early Quebec settlers, then you are probably related to 95% of French-Canadians.

Step 4: Compare family trees

I believe AncestryDNA does this for you automatically which is a huge point in their favor. Otherwise you need to review your matches’ surname lists and compare them to yours to find your common link. Sometimes this is easy, if you’ve both done a lot of genealogy work, and sometimes it’s difficult, like if one of you was adopted or has large gaps in their tree, or simply hasn’t done much genealogical research. There are some third party ways to simplify this process which I will get to later.

Step 5: Share your ancestry information

The easiest thing to do here is make sure you fully fill out your user profile on the testing site you use. This will help your matches to do some of the matching work for you, and make them more likely to get in contact with you.

The best thing you can do, though, is upload your raw data to GEDMatch. This is a third party tool run by volunteers for free (they accept donations if you find it useful) that allows users from 23andMe, FTDNA and AncestryDNA to all put their data in one place so that you can compare across vendors. Otherwise you can never be sure if this one guy on FTDNA that you match also matches this one woman from 23andMe and so on.

I can’t reiterate enough how useful GEDMatch is, and how much you’ll help other genetic genealogists by uploading your data there. The service they provide is in many ways superior to that offered by the commercial testing companies. They also support uploading your GEDCOM and doing the family tree matching for you, but that feature is unavailable for now due to the huge influx of data submitted recently. It will be back someday. Once you’ve used it it is tough to do this work without it.

Step 6: Extend your tree

If you’re lucky you’ve been able to identify common ancestors with some of your matches by now. Look through their trees, and if they have any details about your ancestors that you don’t, add them to your tree. If they have the line traced back farther, extend the line in your tree. Add the other descendants of your common ancestors to your tree. You’re related to them, if only distantly, and having those surnames in your tree may help you track down your other matches.

I’ve confirmed via paper genealogy matches as close as third cousins and as far back as ninth cousins. I have documented ancestors going back to early New World settlers so that means I have a LOT of matches and finding the link with other people that have old confirmed lineages eventually gets quite easy. But there are many more people who descend from these early settlers than there are people that can document their ancestry back to them, so sometimes it can be frustrating.

My easiest matches go back to colonial days in the US, particularly some of the early Connecticut settlers like Eleazer Beecher and Phebe Prindle. Early Quebec settlers like Nicholas Pelletier and Jean de Vouzy are another great source for confirmed matches. I also have some large clusters from early French settlers in Louisiana, as well as Quebec French who immigrated to Louisiana later.

As a reference point, I am sharing with nearly 100 matches on 23andMe. I have confirmed genealogical ancestry with somewhere around ten of them. Your results will vary. One of my most recent matches had a detailed family tree and I found our ancestors in 1780s Louisiana after only about ten minutes of work. I was the first person she shared with, so while I only have a 10% success rate she’s at 100%.

Step 7: Triangulate!

The only way to do this is to share with as many people as possible on 23andMe, manually collate your matches from FTDNA or use GEDMatch. Share with people even if you see no obvious connection besides your matching segment. As you accumulate matches, you will eventually discover multiple people that you match in the same region of the same chromosome.

Once you have a list of two or more people you match in the same region, compare them to each other. If you match person A at a particular region, and you match person B at the same spot, compare A to B. If they match each other at the same spot, congratulations. All three of you very likely share a common ancestor. If A and B do not match each other, then most likely you match A on the copy of the chromosome you inherited from your mother and you match B on the other copy, inherited from your father, so that can help you track down the common ancestor you have with each, even though A and B are not related.

Where it gets really interesting is when you have a cluster of several people that all match you and each other but stubbornly resists identification. Then you find a new match who matches all of them, and you find your common ancestor with this new match based on the quality of their genealogical research. That allows you to positively assign a spot in history to the rest of your cluster and may help with future identification. This was the case for me with the recent Louisiana match I mentioned. This match was on a cluster including a woman in Italy that had only one known ancestor who went to the US. We were quite sure our match was somewhere along this American immigrant’s line, but since my new match places a portion of this segment in 1788 Louisiana, that means my match with the Italian woman is back older than that, likely somewhere in France, Germany or Luxembourg in the 1600s or earlier, based on the ancestors of this specific Louisiana settler family.

I’m planning another blog post later on ways to leverage the clusters you’ve identified using 23andMe’s Ancestry Finder tool and GEDMatch. The method will be obvious to anyone who has done this a while but I haven’t seen anybody wrote it up yet.

Additional Resources

Here are links to the companies and sites I’ve mentioned along with a few other reference materials on genetic genealogy.

Disclaimer

Other than the 23andMe referral link, I have no employment relationship with any of the sites mentioned or linked, nor have I received any compensation for this post. I am a happy user/member/reader of many of the sites and I will get only the indirect benefit of having your DNA tested and potentially matched to mine.