Wednesday, July 5, 2017

Creating an OEM12C Corrective Action to add a datafile

I've known about corrective actions in OEM for a while, but have been reluctant to implement them for reasons I can't really remember - maybe because I feel it's better to have a bit of control.

However, after getting woken up in the middle of the night only to have to add a datafile, decided it was about time to do this.

So, a quick google, and I found...nothing. Went onto MOS, did a search...one entry for a comment that didn't explain how to do it.

So, here we are.

OEM13C has a pre-defined "Add Datafile" corrective action that looks really neat, you can add by percentage and has lots of options.

But we aren't on OEM13C yet, and still prone to getting woken up.

In order to create this corrective action, you need a couple of things first:

1. You need to set the "db_create_file_dest" parameter.
2. You need to not care what the database datafiles are called.

So if you insist on your files being called "/dat/oradata/PRODDB/tbsname_data01.dbf" then this isn't for you, because from now on the files are going to be called something like "/dat/oradata/PRODDB/datafile/o1_mf_alert_te_dotwl2l7_.dbf"

So if you are OK with that...

Set the db_create_file_dest:

SQL> alter system set db_create_file_dest='/dat/oradata/"

Log into OEM and create the corrective action - in this example I've done it on one database as a test, but you can do it at the template level.

From the database home page, select  ' Oracle Database / Monitoring / Metric and Collection Settings'




















 Scroll down to the "Tablespace Full" metric and select the pencil icon at the right of the page alongside "Tablespace Space Used (%)





 Click on the 'Edit' button here:






Check the 'Allow only one...' box, and then click on the 'Add' button next to 'Warning'





 Use the drop-down to select 'SQL Script'






 Then click the 'Continue' button (right of the page)

Add a suitable name and description





 Click on the 'Parameters' tab

Add this to the SQL:

alter tablespace %key_value_name% add datafile;






 Click 'Continue'

The corrective action will be saved.





 Change the Warning Threshold to 50%.



Click 'Continue', then 'OK' at the next page where you see this:



To test it, create a test tablespace in the target database, change the file so that it will fill up, and add data.

Create the tablespace:

SQL> create tablespace alert_test;

Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name='ALERT_TEST';

FILE_NAME
--------------------------------------------------------------------------------
/dat/oradata/DBAPT/datafile/o1_mf_alert_te_dotwl2l7_.dbf


Shrink the file:
SQL> alter database datafile '/dat/oradata/DBAPT/datafile/o1_mf_alert_te_dotwl2l7_.dbf' resize 10m;

Database altered.

Turn off autoextend:
SQL> alter database datafile '/dat/oradata/DBAPT/datafile/o1_mf_alert_te_dotwl2l7_.dbf' AUTOEXTEND OFF;

Database altered.

List the file(s):
SQL> select file_name from dba_data_files where tablespace_name='ALERT_TEST';

FILE_NAME
--------------------------------------------------------------------------------
/dat/oradata/DBAPT/datafile/o1_mf_alert_te_dotwl2l7_.dbf

Create a table and generate data to push it over the warning threshold:

SQL> create table test_data (col1 number,col2 number,col3 number) tablespace alert_test;

Table created.
insert into test_data
select rownum, mod(rownum,100), trunc(rownum/10)
from sys.source$
where rownum < 320000;
commit;
319999 rows created.

Commit complete.

Force the OEM agent collection (it only scans every 30 minutes):
(DBAPT)/userhome/oracle/mga/akh> . oraenv
ORACLE_SID = [DBAPT] ? AGENT12
The /app/oracle/product/agent12c/core/12.1.0.5.0/bin/orabase binary does not exist
You can set ORACLE_BASE manually if it is required.
(AGENT12)/userhome/oracle/mga/akh> emctl control agent runCollection DBAPT:oracle_database problemTbsp_10i_Loc
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD runCollection completed successfully

Alert and email will be generated:

Host=testhost
Target type=Database Instance
Target name=DBAPT
Categories=Capacity
Message=Tablespace [ALERT_TEST] is [80 percent] full
Severity=Warning


Go back to the database and check the files:

SQL> select file_name from dba_data_files where tablespace_name='ALERT_TEST';

FILE_NAME
--------------------------------------------------------------------------------
/dat/oradata/DBAPT/datafile/o1_mf_alert_te_dotwl2l7_.dbf
/dat/oradata/DBAPT/datafile/o1_mf_alert_te_dotwr5gy_.dbf

A file has been added by the corrective action.

I then wanted to test what happened if it filled again.

Shrink the new file and turn off autoextend:
SQL> alter database datafile '/dat/oradata/DBAPT/datafile/o1_mf_alert_te_dotwr5gy_.dbf' resize 20m;

Database altered.

SQL> alter database datafile '/dat/oradata/DBAPT/datafile/o1_mf_alert_te_dotwr5gy_.dbf' autoextend off;

Database altered.

Check the space:



Insert more data:
SQL> @insert_data

319999 rows created.







Force the collection:
(AGENT12)/userhome/oracle/mga/akh> emctl control agent runCollection DBAPT:oracle_database problemTbsp_10i_Loc
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD runCollection completed successfully


Check the files:


SQL> select file_name from dba_data_files where tablespace_name='ALERT_TEST';

FILE_NAME
--------------------------------------------------------------------------------
/dat/oradata/DBAPT/datafile/o1_mf_alert_te_dotwl2l7_.dbf
/dat/oradata/DBAPT/datafile/o1_mf_alert_te_dotwr5gy_.dbf


There was no Warning alert triggered - even though it's set for 50%.


This may be because I set the corrective action to only allow one so that it didn't keep adding files ad infintum:



or because the number of occurrences is set to 1 within a given period to prevent flooding.
I will need to test it to make sure that if there is a runaway transaction OEM doesn't just keep adding files until the disk fills up.

However, at least the file add worked, which should mean I can stay in bed.