Saving you 15 minutes: Host OS Configuration Extensions and Compliance Checks in 12c OEM Part 1

Unlike the database configuration extensions the host OS configuration extensions in 12c OEM seem to be slightly more difficult to get to grips with.  I think this is due to the various different parsers that are packaged with the OS configuration extension.

If you create a OS configuration extension you will most likely be looking for some ‘default’ parser.  That is; you want to check just something on your host which requires either a grep, or a ps –ef type of check.  Oracle were helpful in suggesting that if you want a ‘default’ parser for a check that isn’t covered by any of the existing parsers. is to use ‘Java Properties’ parser.  This may seem strange at first but it actually works well and oddly you don’t need to know anything about Java.

A simple example for an OS configuration extension would be to find out the name of the OS user running the database / agent software.  From a command line on a server it would simply be the command: whoami

If you want to push this into a configuration extension then you use:

echo “user= `whoami`”

Here is the configuration extension and a preview of it being executed:

OSCEXT

The key thing to notice is that the command has the ` quote which contains and executes the OS command and the user = becomes the stored attribute.

This is a simple starting point for OS configuration extensions and unfortunately not all your compliance checks will be that easy.  The next blog will build on this example.

Tweet Me @pbedba

Saving you 15 minutes: Checking Metric Collections when using 12c Compliance Rule

When you are using the Lifecycle Management Pack and 12c Compliance Standards you need to ensure that the relevant metric collections are in place.  My compliance standard uses a mixture of custom rules and standard rules which I assume will be similar for most people using this functionality.

When you are using custom compliance rules ONLY and you go and apply a compliance standard to a target, it will generate an error if the relevant configuration extensions aren’t applied and therefore the compliance rules will not be evaluated correctly.  If you have a mixture of standard and custom compliance rules and your configuration extensions have been applied then you will not get a warning.  HOWEVER if the standard oracle_security collections haven’t been applied via the monitoring template (other collected items) then you will not be evaluating correctly your compliance standard and rules but more importantly you won’t know that as it won’t give you an error.

This raises the question about how can you ensure that all your collections have been applied to a target to ensure the compliance rules are being evaluated.

If you look at the table MGMT$ECM_CURRENT_SNAPSHOTS you can see the custom compliance collections and standard collections.  This is seen in SNAPSHOT_TYPE

TARGETNAME  SNAPSHOT_TYPE
DB1         ccs_c_FF69ABA6C57800C4E0431EDFF569E26D  
DB1         ccs_c_FF6B79CBED760078E0431EDFF569E869
DB1         oracle_security

The first two rows in this table are for configuration extensions, I have about 43 individual sql statements that are the backbone for my compliance standard and due to the limitations with maximum amount of characters stored in a configuration extension I needed to create two configuration extensions.  The third row is the standard oracle_security collection which is required for standard compliance rules.  Therefore for my environment I can run the query below.

select target_name, count(*) from
MGMT$ECM_CURRENT_SNAPSHOTS
where snapshot_type in ('ccs_c_FF69ABA6C57800C4E0431EDFF569E26D','ccs_c_FF6B79CBED760078E0431EDFF569E869','oracle_security')
and target_type='oracle_database'
group by target_name
TARGETNAME        COUNT(*)
DB1                 3
DB2                 2

Any environment which doesn’t have the core 3 collections enabled will therefore not be able to fully evaluate all the compliance rules in my security standard.

Tweet Me @pbedba

 

Saving you 15 minutes: WIDTH_BUCKETS(what?), 12c OEM and understanding the spread of database sizes across your estate.

I was looking the other day to get a general view about the sizes of databases within my estate.  To do this I started to write a query which basically summed up the sizes of the tablespaces and then did a count based on a minimum and maximum value.  The query looked a bit like this:

select
‘Size 1TB to 1.2TB’ , count(*) from (
select sum(tablespace_size/1024/1024/1024) 
from mgmt$db_tablespaces
group by target_name
having sum(tablespace_size/1024/1024/1024) 
between 1000 and 1200);
UNION ALL
select ‘Size 0.8TB to 1TB’, count(*) from (
select sum(tablespace_size/1024/1024/1024) 
from mgmt$db_tablespaces
group by target_name
having sum(tablespace_size/1024/1024/1024) 
between 800 and 1000);

As I was writing this I did think there must be a better way to generate this without having to specify specific boundaries and repeat the query over and over again, maybe generate some sort of histogram to see the spread of data.  With a bit of digging I came across this:

http://www.oralytics.com/2013/04/part-1getting-started-with-statistics.html

http://www.oralytics.com/2013/04/part-2getting-start-with-statistics-for.html

http://www.oralytics.com/2014/07/bucketwidth-calculating-size-of-bucket.html

These are definitely worth a read and were the basis for this blog, thank you @brendantierney

This lead me to the SQL function WIDTH_BUCKET.  The next thing I needed to do was translate the example given to calculating the spread of database sizes within 12c OEM.

WIDTH_BUCKET requires 4 inputs:

  • Expression
  • Min value of the first bucket
  • Max value of the last bucket
  • Number of buckets

Easiest way to do this is build up the query in parts:

The expression is the size of the databases:

sum(tablespace_size/1024/1024/1024)

So for the min and max values I can use this:

select min(sum(tablespace_size/1024/1024/1024)) from mgmt$db_tablespaces group by target_name
select max(sum(tablespace_size/1024/1024/1024)) from mgmt$db_tablespaces group by target_name

Number of buckets is:

10

So the first iteration of the query is:

select
target_name, 
sum(tablespace_size/1024/1024/1024),
width_bucket(sum(tablespace_size/1024/1024/1024),
(select min(sum(tablespace_size/1024/1024/1024)) from mgmt$db_tablespaces group
by target_name),
(select max(sum(tablespace_size/1024/1024/1024)) from mgmt$db_tablespaces group
by target_name),10)
from 
mgmt$db_tablespaces
group by target_name

The output is as follows (not all of it):

Target Name      Size GB                         Width Bucket
DB1              249.8046875                         1
DB2              906.9814453125                      1
DB3              2091.9165802001953125               2
DB4              8.25                                1
DB5              82.8408050537109375                 1

So this tells us the size of the database and which bucket in terms of size that fits into (between bucket 1 and 10, 1 being the lowest).  So the next thing I need to do is look at the estate as a whole and look at the numbers of databases in each bucket.  The width_bucket column is given an alias and we simply count that alias for each width bucket.  The output is below.

select intvl, count(*) freq from 
(select width_bucket(sum(tablespace_size/1024/1024/1024),(select min(sum(tablespace_size/1024/1024/1024)) from mgmt$db_tablespaces group by target_name),(select max(sum(tablespace_size/1024/1024/1024)) from mgmt$db_tablespaces group by target_name),10) intvl
from 
mgmt$db_tablespaces
group by target_name)
group by intvl
order by 1
Bucket   Count
1        695
2        15
3        2
4        1
5        2
6        5
7        3
8        4
9        1
10       3
11       1

Where did 11 come from?  The 11th bucket is for anything that doesn’t fit in the maximum and minimum values.  (but you specified the max and min values based on the data!!!).  This is due to the way that the data is rounded up or down by the width_bucket function.  Hence if you change width_bucket expression from:

sum(tablespace_size/1024/1024/1024)

To:

round(sum(tablespace_size/1024/1024/1024),0)

All the data falls into the relevant buckets.

The final piece of the puzzle is to provide some idea on what those bucket sizes are (note that we round all values to 0 apart from the max size to ensure everything falls within our 10 bucket values):

select bucket, round(max(db_size),0) MAX_SIZE_GB, count(*) from (
select target_name, 
sum(tablespace_size/1024/1024/1024) db_size,
width_bucket(round(sum(tablespace_size/1024/1024/1024),0),
(select round(min(sum(tablespace_size/1024/1024/1024)),0) from mgmt$db_tablespaces group by target_name),
(select round(max(sum(tablespace_size/1024/1024/1024)),1) from mgmt$db_tablespaces group by target_name),10) bucket
from 
mgmt$db_tablespaces
group by target_name)
group by bucket
order by 1

 

Bucket   Database Max Size GB Count(*)
1        1207                 695
2        2425                 15
3        3404                 2
4        4768                 1
5        5590                 2
6        6301                 5
7        7859                 3
8        9716                 4
9       10328                 1
10      12168                 4

So it looks like most of my databases are under 1.2TB.  Now I know my general spread of data I can re-run the query and input my own value:

select bucket, round(max(db_size),0) MAX_SIZE_GB, count(*) from (
select target_name, 
sum(tablespace_size/1024/1024/1024) db_size,
width_bucket(round(sum(tablespace_size/1024/1024/1024),0),
(select round(min(sum(tablespace_size/1024/1024/1024)),0) from mgmt$db_tablespaces group by target_name),
1207,10) bucket
from 
mgmt$db_tablespaces
group by target_name)
group by bucket
order by 1
Bucket   Database Max Size GB  Count(*)
1        120                   607
2        234                    33
3        326                    18
4        419                     7
5        584                     4
6        704                    10
7        798                     4
8        907                     2
9       1038                     4
10      1158                     5
11     12168                    38

Ok so further analysis makes it look like most of my databases are less than 120GB, this is getting embarrassing!  Also notice that the max bucket now stops just under 1207 GB and everything above this is captured in 11th bucket.  I am going to stop there but hopefully you can see the benefit of this v cool SQL Function.

 

 

Saving you 15 minutes: Export and Import Configuration Extensions

Migrating configuration extensions from one system to another was a relatively straight forward process.  What was also re-assuring is that the METADATA_SNAP_TYPE is kept consistent between the two systems.  This (I believe) is what uniquely identifies a configuration extension which means that if you are migrating compliance rules as well which uses the configuration extensions they should continue to work (I’m about to try that now).

The only thing that isn’t migrated when you migrate the configuration extension is the database credentials that you select for a configuration extension, which means when you deploy the imported configuration extension it won’t actually gather any results until that is set.  Screenshot is below.

IMCONFIGEXT

Saving you 15 minutes: SQL Configuration Extensions and Different Compliance Scores

I recently blogged about configuration extensions and compliance rules in 12.1.0.3 OEM and the posts are below (if you’re interested).  However something that I didn’t go into is how identical configuration extensions can potentially provide different compliance scores.

http://blog.e-dba.com/blog/2014/06/03/saving-you-15-minutes-configuration-extensions-and-the-lifecycle-management-pack-part-1/

http://blog.e-dba.com/blog/2014/06/06/saving-you-15-minutes-configuration-extensions-and-the-lifecycle-management-pack-part-2/

http://blog.e-dba.com/blog/2014/06/10/saving-you-15-minutes-configuration-extensions-and-the-lifecycle-management-pack-part-3/

For example take this configuration extension:

select username, account_status from dba_users where username in ('SYSTEM')

That configuration extension gets translated and stored in OEM as TWO separate rows.

TARGET_GUID                       INFO          VALUE  ATTR
F979D2C8CACE0032E0431EDFF569BEE5  attrvalue     OPEN   ACCOUNT_STATUS 
F979D2C8CACE0032E0431EDFF569BEE5  attrvalue     SYSTEM USERNAME

If we create a custom compliance rule; then test it, we get a compliance score of:

CRCS1

If we then change the query to the following:

select account_status from dba_users where username in ('SYSTEM')

That configuration extension gets translated and stored in OEM as ONE row.

TARGET_GUID                       INFO          VALUE  ATTR
F979D2C8CACE0032E0431EDFF569BEE5  attrvalue     OPEN   ACCOUNT_STATUS

If we create a custom compliance rule; then test it, we get a compliance score of:

CRCS

So both queries are checking for the same violation however we get two different compliance scores.  The reason for this is that when the compliance score is evaluated it is evaluated against every row in the table for that configuration extension.  This combined with the way OEM stores that configuration extension by breaking down each column into a ROW means that you could subtly alter your compliance scores depending on how those configuration extensions are written.  So if you’re writing configuration extensions make sure they are consistent for each violation in terms of the number of columns they select as this will get translated into rows when the compliance score is evaluated.