Wrapping "repvfy" to Make Your OEM Life Easier

For those of you who manage Oracle Enterprise Manager environments, you know how important the metadata is.  Fortunately, Oracle provides a utility called "repvfy" that, as the abbreviation implies, helps you verify the repository.  In this blog I'd like to share how you can wrap a bit of code around the execution of "repvfy" to make your life a bit easier.

Before proceeding, note that:

• I'll be using the variable $EMDIAG_HOME to refer to where the "repvfy" utility has been extracted.
• Our OEM environments that "repvfy" and its associated wrapper code run against are 11.1.0.1, 12.1.0.4 and 12.1.0.5.
• The wrapper itself can be written in a variety of languages but since it doesn't require complex coding nor has strict elapsed time requirements and all members of our DBA team are proficient in bash, it was decided to use bash.

We created crontab entries to run "repvfy" on a daily basis against each of our OEM environments, including the "-fix" argument so that in case the found issue is simple to resolve, "repvfy" can take care of it.  But, periodically issues are found that require more effort so we decided it would helpful to receive an email with the output from each "repvfy" execution.  Reviewing these emails isn't too difficult as output is similar to the following:

verifyAGENTS
1008. NMO not setuid-root (Unix-only): 2
6002. Blocked Agents: 2
8005. Broken Agents: 1
verifyASLM
verifyAVAILABILITY
8001. Composite availability errors: 2
verifyBLACKOUTS
7001. Expired blackout windows: 9
      Fix: 9
verifyCA
verifyCAT
 ...
verifyTARGETS
1017. Platform ID mismatch between host and ORACLE_HOME: 25
2006. Targets with missing ORACLE_HOME target: 4
2013. CRS clusters with nodes not discovered: 8
3002. DB Systems linked to multiple databases: 2
6004. Targets not uploading: 33
7008. Orphan source target associations: 57
7010. Systems without members: 12
7013. Composite targets without metric dependency details: 4
8002. Broken targets: 3
verifyTEMPLATES
verifyUPGRADE
verifyUSERS
1003. Custom super user admins.: 11

But some of these we know can be skipped, the BLACKOUTS test 7001 alert can be ignored since all issues were fixed, etc.  So reviewing output like this involves some repetitive work.

Here's where the "make your life a bit easier" part comes in.  We can wrap a bit of code around "repvfy" to avoid some of the repetitive work.  This starts by parsing output from "repvfy".

By default, "repvfy" creates a logfile of each execution under $EMDIAG_HOME/log.  If you run a "verify" then the logfile will have a format similar to "verify_<yyyy>_<mm>_<dd>_<hh24miss>.log".  To obtain the most recent logfile, code like the following can be used:

% ls -lt $EMDIAG_HOME/log/verify_* | head -1 | awk '{print $NF}'

Parsing the logfile is relatively straight-forward as each module begins with "verify" followed by 0 to many tests, each of which are prefixed by a test 4-digit number.  An example of a loop for parsing out the MODULE sections is:

for REPVFY_LINE in `grep -E '(^verify|^[0-9]*\. |^ *Fix)' $LOGFILE | awk '{if ($1 ~ /verify/)  PREFIX=$1; else print PREFIX" "$0}' | grep -vE '(^verifyEMDIAG 6001\.|^verifyEXADATA 6006\.|^verifyREPOSITORY 6039\.|^verifyREPOSITORY 7001\.|verifyJOBS 707\.|verifyTARGETS 605\.)' | sed "s/ /~/g"`

The above line does the following:

1.       Start a loop containing all lines that begin with "verify" (the module header), a set of digits following by a period then a space (test output lines) or any line whose first characters are "Fix" -> grep -E '(^verify|^[0-9]*\. |^ *Fix)

2.       Use "awk" to prefix each module's test or "Fix" lines with the module header -> awk '{if ($1 ~ /verify/)  PREFIX=$1; else print PREFIX" "$0}'

3.       Skip several test results that we aren't concerned with -> grep -vE '(^verifyEMDIAG 6001 ... verifyTARGETS 605\.)'.  We've chosen to skip these alerts because either we know of the situation and don't see it as an issue.  For example, test 707 in module JOBS concerns active executions reaching job purge time.  This is typical archive activity and a non-issue.

4.       Last, each line's spaces are changed to "~" for easier work with delimiting fields.

The previously listed "repvfy" output now looks like:

verifyAGENTS~1008.~NMO~not~setuid-root~(Unix-only):~2
verifyAGENTS~6002.~Blocked~Agents:~2
verifyAGENTS~6006.~Deployed~Agent~plugins~lower~than~OMS~plugin:~8
verifyAGENTS~8005.~Broken~Agents:~1
verifyAVAILABILITY~8001.~Composite~availability~errors:~2
verifyBLACKOUTS~7001.~Expired~blackout~windows:~9
verifyBLACKOUTS~~~~~~~Fix:~9
verifyTARGETS~1017.~Platform~ID~mismatch~between~host~and~ORACLE_HOME:~25
verifyTARGETS~2006.~Targets~with~missing~ORACLE_HOME~target:~4
verifyTARGETS~2013.~CRS~clusters~with~nodes~not~discovered:~8
verifyTARGETS~3002.~DB~Systems~linked~to~multiple~databases:~2
verifyTARGETS~6004.~Targets~not~uploading:~33
verifyTARGETS~7008.~Orphan~source~target~associations:~57
verifyTARGETS~7010.~Systems~without~members:~12
verifyTARGETS~7013.~Composite~targets~without~metric~dependency~details:~4
verifyTARGETS~8002.~Broken~targets:~3
verifyUSERS~1003.~Custom~super~user~admins.:~11

At this point further logic is applied based on the module and test to try and fix some issues.  One example is test 6004 in the TARGETS module.  This test concerns targets that aren't uploading data.  What we found, though, is that clusters/scans only upload data every 24hrs so we rerun this test *manually* by querying the metadata directly, filtering out object types of 'cluster'.

How did we determine what the best cursor to use for this test?  Another great feature of "repvfy" is that the cursors on which tests are based are saved in a .sql file when the "-details" argument is passed.  In the example of test 6004 in the TARGETS module we ran:

% $EMDIAG_HOME/bin/repvfy verify -module TARGETS -test 6004 -details

… and then reviewed the generated .sql file under $EMDIAG_HOME/log to see the cursor that the tool used.

Before emailing the finalized output we still need to deal with test alerts that are automatically fixed.  Those are the lines that had begun with "Fix" as its first characters.  For these we compare the total alerts reported with the total reported as fixed.  If the totals match, we skip this alert, otherwise we report it.  Here's a code snippet showing this logic:

if [ -n "$REPVFY_PREVLINE" ]; then
   if [ `echo $REPVFY_LINE | grep -c "Fix:"` -eq 1 -a $ERROR_TOTAL -eq $ERROR_PREVTOTAL ]; then
      ERROR_PREVTOTAL=
      ERROR_TOTAL=
      REPVFY_PREVLINE=
      REPVFY_LINE=
   elif [ `echo $REPVFY_LINE | grep -c "Fix:"` -eq 0 -o $ERROR_TOTAL -ne $ERROR_PREVTOTAL ]; then
      echo "$REPVFY_PREVLINE" >>$PARSE_LOGFILE
      ERROR_PREVTOTAL=$ERROR_TOTAL
      REPVFY_PREVLINE=$REPVFY_LINE
   fi
else
   ERROR_PREVTOTAL=$ERROR_TOTAL
   REPVFY_PREVLINE=$REPVFY_LINE
fi

The last step is to email lines of output that, through the logic in this wrapper, only pertain to what our team should be concerned with.  In the example above the final list is:

verifyAGENTS 1008. NMO not setuid-root (Unix-only): 2
verifyAGENTS 6002. Blocked Agents: 2
verifyAGENTS 6006. Deployed Agent plugins lower than OMS plugin: 8
verifyAGENTS 8005. Broken Agents: 1
verifyTARGETS 1017. Platform ID mismatch between host and ORACLE_HOME: 25
verifyTARGETS 2006. Targets with missing ORACLE_HOME target: 4
verifyTARGETS 2013. CRS clusters with nodes not discovered: 8
verifyTARGETS 3002. DB Systems linked to multiple databases: 2
verifyTARGETS 6004. Targets not uploading: 32
verifyTARGETS 7008. Orphan source target associations: 57
verifyTARGETS 7010. Systems without members: 12
verifyTARGETS 7013. Composite targets without metric dependency details: 4
verifyTARGETS 8002. Broken targets: 3
verifyUSERS 1003. Custom super user admins.: 11

With this output we know that any issues that could have been automatically fixed have been addressed and that ones we don't need to be concerned with aren't listed, making the alert more meaningful for us.

Hopefully this showed how you can easily wrap a bit of additional functionality around the "repvfy" utility to remove alerts you don't care about and add a bit of code to try to automatically address other issues.

Additional Notes

Although a full explanation of how to use the "repvfy" utility is beyond this post, a good article in MOS for further research is " EMDIAG Repvfy 12c /13c Kit - How to Use the Repvfy 12c/13c kit (Doc ID 1427365.1".

It can be helpful to know what all the possible tests are for a given module.  This can be achieved using the "list" parameter.  For example, to see all tests for the AGENTS module use:

% $EMDIAG_HOME/bin/repvfy -module AGENTS list

Physical Standby Wait Event Alerting

I recently had a request to send alerts to our DBA team whenever a particular read-only standby database has high waits on "library cache*" events.  I could see the need as we just had an issue where a number of different "library cache*" events were causing a performance problem on the standby database.  I figured this would be pretty easy to do in Enterprise Manager 12c - set thresholds on metric(s) for this event, update any relevant Incident Rules and we're all set.

Unfortunately there are no metrics available for specific "library cache*" wait events in EM12c.  There is a metric for a higher level, on the wait class, so in this case that'd be "Concurrency".  But thinking this through I realized alerting on "Concurrency" wouldn't work for this case.  The idea is to perform regular checks for "library cache*" waits and if repeated checks (at least 2) find x number of sessions waiting on this event then an alert should be raised.  If we check for "Concurrency", we could run into a situation where at timestamp#1 there were 10 sessions waiting on "library cache lock" and at timestamp#2 there were 10 sessions waiting on "enq: HV - contention".  2 consecutive checks found 10 or more sessions waiting on the metric so an alert would be raised, even though in my contrived example it's not what we want.

Another option would be either to use a temporary table to store snapshots of current session waits or to query AWR data.  A temporary table wouldn't work because this is a read-only standby.  AWR data wouldn't work because 1, monitored would be in the past, the earliest being 1 snapshot ago.  Plus, again this is a standby database so any AWR present would be a copy of the primary.

Our solution was to go with a Metric Extension.  The ME was based on the cursor:

SELECT COUNT(*) total_sessions

  FROM v$session

 WHERE state = 'WAITING'

   AND event LIKE 'library cache%';

 ... set at the instance-level.  Thresholds were set for 2 (or more) consecutive checks at 10 (warning) or 20 (critical).  After updating the appropriate Incident Rule everything worked as expected.

EVENT vs. EVENTS

I figure it's "a must" to blog any time I learn something new.  I've set events on databases many times over the years but for the most part I've never paid that much attention to the syntax.  I knew about SESSION vs. SYSTEM, that CONTEXT lets you define how long the event should be active, etc., but I never paid attention to the impact of EVENT vs. EVENTS.

Using "ALTER SYSTEM SET EVENT '…';" changes the parameter EVENT and overrides previous settings (more on that later), "ALTER SYSTEM SET EVENTS '…';" makes the listed event active immediately but not persistent across database restarts.  I guess that should be obvious, because without specifying SCOPE the change would only be in memory.  What makes this different, though, is that using EVENT gives you the ability to define SCOPE (and SID) while EVENTS does not.  Using EVENTS actually changes a memory structure (see Tanel Poder's blog on "Why doesn't ALTER SYSTEM SET EVENTS set the events or tracing immediately?": http://tinyurl.com/kh852ea).

Back to EVENT, setting it to a value overrides previous setting(s), just as it would for any other instance/database parameter.  This means it's important to first check for any existing values for EVENT before setting new one(s).

An Unexpected Impact of LANG

In the process of trying to sort a delimited character string for a bash script I came across a situation where the setting of LANG at the OS-level impacted the results.  In my situation I was working on a script for monitoring Oracle services by comparing "srvctl configure" output with "srvctl status" output.  Since services that cross multiple instances may list those instances in various orders, I wanted to make sure the listing was always sorted for a proper comparison.

While a simple stream of "echo | tr ' ' '\n' | sort | tr '\n' ' ' | sed 's/,$/\n/'" works just fine, I found that when testing the monitor script across all our servers I was getting different results.  Looking closer I found that the difference was related to running the script locally vs. via SSH.

After a bit of debugging I came up with a simple example to show the issue:

Create a file with 4 rows of numbers, with varying leading spaces.

% echo "1

2

3

   4" >x.dat

Next, sort the file locally.

% sort x.dat

1

2

3

   4

 Last, sort the file on the same server (I'm connect to lnx261) but pretend it's a remote operation via ssh.

% ssh oracle@`hostname -s` 'sort x.dat'

   4

2

1

3

For both sorts the same file was used and run on the same server.  What would there be a difference?

LANGUAGE!  Your OS LANG setting affects sorts, just like NLS* settings do in Oracle.  Most session-level settings are not set when you SSH.  So either sourcing the appropriate profile (/etc/profile, ~/.bash_profile, etc.) or setting LC_ALL within your SSH session will give you the expected results:

% ssh oracle@`hostname -s` 'export LANG=en_US.UTF-8; sort x.dat'

1

2

3

   4

Although I knew issues related to NLS_DATE_FORMAT not being set I had never previously run into issues with LANG.  Fortunately Enterprise Manager's agent (as part of $ORACLE_HOME/bin/nmo) runs through a full process creation so that your various profile scripts get sourced when you execute EM Jobs or simply run "Execute Host Command".

Mining EM Metadata - Password Lookup

As I've posted before, Enterprise Manager's (EM) metadata presents a wealth of information that can come in handy for your day-to-day DBA work.  In this article I'd like to share how stored passwords in EM (Preferred Credentials) can  be used to your advantage.

For an EM agent to interact with a server, the agent needs that server's password.  To work without human interaction that agent needs to store the server's password.  It also can store other target passwords, like that for databases.  The passwords are obviously stored in an encrypted format, but with the right SQL and function combinations you can retrieve any password, in clear text, for any username programmatically.  It all starts with MGMT_CREDENTIALS2.

Each set of credentials is stored in MGMT_CREDENTIALS2, where a "credential set" is made up of the username and password.  Each credential set is identified by a CREDENTIAL_GUID, which maps to MGMT_TARGET_CREDENTIALS.  This table is used to map credential sets to their targets for each administrator within EM, as you can save credentials for multiple targets for multiple EM administrators.

The following is an example of putting this together in a script (pass_lookup.sql):

SET ECHO off FEEDBACK off HEADING off PAGESIZE 10000 TAB off VERIFY off

DEFINE USERNAME='&1';

DEFINE TARGET_NAME='&2';

DEFINE TARGET_TYPE='&3';

SELECT DISTINCT DECRYPT(c.credential_value)

  FROM mgmt_target_credentials tc, mgmt_credentials2 c, mgmt_targets t

 WHERE UPPER(c.key_value) = '&USERNAME'   -- Username whose password we're retrieving

   AND c.credential_set_column = 'password'

   AND t.target_guid = tc.target_guid

   AND tc.credential_guid = c.credential_guid

   AND t.target_type = '&TARGET_TYPE'

   AND t.target_name = '&TARGET_NAME';

EXIT

The reason C.CREDENTIAL_SET_COLUMN is filtered on "password" is that in this case we only want the password value, not the value of the username.

Taking this a step further, you can see how retrieval of the password can now be used within code, such as:

USER="DBA_USER"

PASS=`sqlplus -S sysman/sysman_pass @pass_lookup.sql "$USER" "dbname" "rac_database" | grep -v "^$"`

Those lines set the variable $PASS with the password value for a given target and username.  Now obviously you first need to know the password for SYSMAN of the repository, but that's a relatively small detail to code for and the above should give you a good start if you want to use the EM repository as a place to store and retrieve passwords.