Saving you 15 minutes: Host OS Configuration Extensions and Compliance Checks Part 3

Following on from the last blog here is a quick way to capture the Oracle home of the database in a OS configuration extension.

echo "ORACLE_HOME = `cat /etc/oratab | grep product | cut -d ":" -f2 | sort -u`"

OSCEXT_1

So now we can get the Oracle Home value we need to do something with it.  This quickly becomes a shell scripting challenge.  So if I want to check the directory permissions of the bin directory for an Oracle Home I need to use the following command:

ls -ld `cat /etc/oratab | grep product | cut -d ":" -f2 | awk '{print $1"/bin"}' | sort -u`

If I do this in the OS Configuration Extension I get the following:

OSCEXT_4

What has occurred is that the java parser has just created the first field as an attribute and the rest as the value.  Not ideal, so if I use the same approach as before I can put in the following:

echo "BIN_DIRECTORY = ls -ld `cat /etc/oratab | grep product | cut -d ":" -f2 | awk '{print $1"/bin"}' | sort -u`"

OSCEXT_5

So now I have the attribute as BIN_DIRECTORY but the value is interpreted incorrectly.  This is because the ls -ld wasn’t interpreted as a command i.e. preceded by the `.  What I need to do is to get the parameter I want and then pass it to ls –ld, for this I use xargs and then a bit of awk as I am only interested in the permissions and not the whole string.

echo "BIN_DIRECTORY = `cat /etc/oratab | grep product | cut -d ":" -f2 | awk '{print $1"/bin"}' | sort -u | xargs ls -ld | awk '{print $1}'`"

Executing this gives me the correct attribute and value.

OSCEXT_6

The key is ensuring that your shell commands return a static ‘attribute’ as the first column and then a single value as a second column.  This will mean any compliance checks will look for the static attribute (as you know what that will always be) and then check the corresponding value.

Tweet Me @pbedba

 

 

 

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

If I was ever confident that this blog will actually save you 15 minutes then it would be the following.

A simple OS configuration extension would be the following:

echo “EXTPROC = `ls -l $ORACLE_HOME/bin/extproc | awk ‘{print $1}’`”

This would in theory would show you the permissions for the extproc binary which you could then compare to a security standard, our security standard says this should be set to 000 by default.

However when you run this as a metric extension it says that the collection fails.  The output is below and one of the first things you should ignore is the \ in front of the $.  That isn’t the reason it is failing.

OSCEXT_2

The problem can be explained by running another metric extension.

echo “ORACLE_HOME = `echo $ORACLE_HOME`”

OSCEXT_3

The output then shows what the issue is.  The environment variables that the configuration extensions will automatically inherit is that of the agent, therefore if you want to do any OS configuration extensions that involve files in the database Oracle Home you need a way to extract and set that as part of the configuration extension.

Tweet Me @pbedba

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.