With Enterprise Manager 13c, we have the possibility to define corrective actions. Let me show you how to use it.
From the Enterprise à Monitoring select Corrective Actions :
Choose Add Space to Tablespace in the Create Library Corrective Action :
Enter the name, a description and choose Metric Alert as event:
Finally choose to Save to Library
You have to publish your corrective action as it has been created as a draft version, you select Publish.
You can edit the Add Space corrective action parameters to adapt it to your environment:
Now we create a tablespace and we affect the Corrective Action to the Metric Alert Tablespace Used. In the Oracle Database à Monitoring à Metric and Collection Settings
We edit the metric Tablespace Full, Tablespace Space Used (%):
In the Monitored Objects, we select Edit:
We select Add in the Warning Corrective Actions:
Enter a name, database and host credentials and choose the Add Space to Tablepace corrective actions.
Your corrective action is applied in the Tablespace Space Used. So if we insert a lot of data in our 5M size tablespace, the datafile must increase automatically. Let’s make the test:
Connected as a user with the default tablespace PSI, we insert some values :
SQL> create table test as select * from sys.dba_segments where rownum < 100;
Table created.
After some inserted values, the PSI tablespace is full, the corrective action has not yet run because its collection scheduled run every 30 minutes, we modify it to 5 minutes:
You also have the possibility to submit the job if you do not want to wait:
You can display how the job is running:
The job is done, the tablespace PSI has been increased automatically :=)
This corrective action allows to increase a tablespace size, but if we consider that in many Oracle production environment the data files are in auto extent mode on, this feature might not be very useful.
But we could use this corrective action to back up the database archive log when the archive log directory is filled more than the 80 % threshold.
In the Monitoring à Corrective Actions you select RMAN script and choose Go:
You enter the name , a shot description and you choose the event type Metric Alert
In the parameter tab, you enter your script to backup the archive logs:
Then for my test database in the Metric and Collection Settings, we select the metric Archive Area Used, and we add the corrective action backup archive log:
Now I modify the warning threshold for test purpose, I generate activity on the database to generate archive logs, the job is successfully launched:
This functionality is quite easy to implement and quite more practical for Oracle DBAs, specially to backup archive logs.