Use Corrective Action ( CA ) to Automate Archivelog Backup When FRA Usage is Over Threshold of Metric Extension ( ME ) in OEM

Take advantage of OEM Corrective Action ( CA ) to automate archive logs backup after FRA usage over threshold.

In Previous posts, we have created following related posts:

  1. Create a Metric Extension (ME) to Monitor FRA Usage on OEM
  2. Create an Incident Rule for Metric Extension ( ME ) to Monitor FRA Used in OEM

Now and then, FRA usage is easily over threshold, DBA needs logon system and manually run an archivelog backup.  It is easily to make database hung if FRA usage is 100%.

Here we use “Corrective Actions” to run a RMAN archivelog backup when the FRA usage is over warning / Critical threshold.

Subscribe to get access

Read more of this content when you subscribe today.

10) After a little while, an alert is received about “Corrective action status=Succeed”

Corrective action=RMAN_AUTO_BACKUP_ARCHIVELOGS
Corrective action owner=SYSMAN 
Corrective action status=Succeeded 
Corrective action output=SID: RACTEST1
HOME: /u01/app/oracle/product/11.2.0/dbhome_1
BASE: /u01/app/oracle
SID: RACTEST1
HOME: /u01/app/oracle/product/11.2.0/dbhome_1
BASE: /u01/app/oracle

Host=RACTEST1.ractest 
Target type=Cluster Database 
Target name=RACTEST-CLUSTER 
Categories=Capacity 
Message=The value of Flash Recovery Area % Used for +FRA is 82
Severity=Warning 
Event reported time=Dec 22, 2016 9:01:54 PM EST 
Target Lifecycle Status=Development 
Comment=RAC TEST
Operating System=Linux
Platform=x86_64
Associated Incident Id=209879 
Associated Incident Status=New 
Associated Incident Acknowledged By Owner=No 
Associated Incident Priority=High 
Associated Incident Escalation Level=0 
Event Type=Metric Alert 
Event name=ME$RACRecoveryAreaSpace:FRA_PERCENT_USED 
Metric Group=ME$RACRecoveryAreaSpace
Metric=FRA_PERCENT_USED
Metric value=82
Key Value=+FRA
Key Column 1=FRA_NAME
Rule Name=Flash Recovery Area Percent Used for ME

Create an Incident Rule for Metric Extension ( ME ) to Monitor FRA Used in OEM

Step by step demonstrates how to create an incident rule for a Metric Extension ( ME).

In another post, we created a Metric Extension (ME) to Monitor FRA Usage on OEM. Here we need create an Incident Rule for creating an incident and notification for all events of Warning or Critical.

Subscribe to get access

Read more of this content when you subscribe today.

8) After a couple of minutes, the below alert is received:

Host=ractest1 
Target type=Cluster Database 
Target name=RACTEST
Categories=Capacity 
Message=The value of Flash Recovery Area % Used for +FRA is 80 
Severity=Warning 
Event reported time=Dec 22, 2016 3:08:43 AM EST 
Target Lifecycle Status=TEST
Comment=RACTEST CLuster
Operating System=Linux
Platform=x86_64
Associated Incident Id=209693 
Associated Incident Status=New 
Associated Incident Owner=TESTUSER
Associated Incident Acknowledged By Owner=No 
Associated Incident Priority=High 
Associated Incident Escalation Level=0 
Event Type=Metric Alert 
Event name=ME$RACRecoveryAreaSpace:FRA_PERCENT_USED 
Metric Group=ME$RACRecoveryAreaSpace
Metric=FRA_PERCENT_USED
Metric value=80
Key Value=+FRA
Key Column 1=FRA_NAME
Rule Name=RACTEST Ruleset,Flash Recovery Area Percent Used for ME 
Rule Owner=TESTUSER
Update Details:
The value of Flash Recovery Area % Used for +FRA is 80
Incident created by rule (Name = RACTEST Ruleset, Flash Recovery Area Percent Used for ME; Owner = TESTUSER).

Create a Metric Extension (ME) to Monitor FRA Usage on OEM

OEM Metric Extension (ME) is a great feature for developing customized metrics, which are unavailable by default.

As we know “Archive Area % Used” metric does not work for using FRA as archive destination when the destination is set to USE_DB_RECOVERY_FILE_DEST. We only receive after “Recovery Area % Used” metric triggers when it hits a Warning threshold of 85% full, and Critical of 97% full.

But the fixed thresholds of 85( Warning )/97( Critical) is not good enough for handling FRA full. So we develop a customized more flexible way to  monitor and alert when FRA is used as archive destination.

Subscribe to get access

Read more of this content when you subscribe today.

So, the Metric Extension ( ME) is created successfully, in another post, we will create a Incident Rule which will create an incident and notification for all the over threshold Warning and Critical alerts.

Further more, we will create a Corrective Action against this Metric Extension ( ME ) to to a archive log backup automatically.

ORA-01105 ORA-19808 When Start Up RAC Instance

When tries to start up one instance of the RAC , the following errors occur.

.....
....
ORA-01105: mount is incompatible with mounts by other instances
ORA-19808: recovery destination parameter mismatch
...

Subscribe to get access

Read more of this content when you subscribe today.

How to Install Database Component of a Patch onto ORMB Database

Since de-installation of the patch is not supported, DBA should develop a backup/recovery strategy before applying ORMB patches in case rollback is required.

Be aware of the below warning when applying database component of a patch onto ORMB database.

Warning: De-installation of the patch is not supported. Make sure that you have a backup of the database schema you are installing into. It is recommended that you first install and test patches on a non-production environment.

DBA should develop a backup/recovery strategy before applying ORMB patches in case rollback is required if patching fails. The suggested backup strategy could be either of the below:

The following is a practical example of applying patch 25187694 onot ORMB 2.5.0.3.0 from a different server other than an application server.

1)Create Database Patching Utility

Ask ORMB application server administrator to run “createDBStandlone.sh” to create a jar file called “db_patch_standalone.jar”.

2) Copy and Cecompress Database Patching Utility

D:\dbpatch_tools
 config
         log4j.properties
 bin
         ouafDatabasePatch.sh
         ouafDatabasePatch.cmd
 lib
         commons-cli-1.1.jar
         commons-codec-1.6
         commons-collections-3.2.1
         commons-io-1.3.2
         log4j-1.2.17
         ojdbc7-12.1.0.2
         spl-dbpatch-4.3.0.1.0
         spl-shared-4.3.0.1.0

3) Set Windows Environment Variables

set TNS_ADMIN=D:\oracle
set JAVA_HOME=D:\java
set TOOLSBIN=D:\dbpatch_tools\bin

4)  Apply Database Component of  the Patch

D:\PATCHES\p25187694_25030_PDEV\MultiPlatform\V2.5.0.3.0-25187694_MultiPlatform\database\ORACLE\CDXPatch>ouafDatabasePatch.cmd
"CMDLINE::: d:\java\bin\java.exe -cp d:\dbpatch_tools\lib\*;d:\dbpatch_tools\config com.oracle.ouaf.database.patch.OUAFPatch "

Enter the target database type (O/M/D) [O]: O
Enter the username that owns the schema: cisadm
Enter the password for the cisadm user:
Enter the name of the Oracle Database Connection String: //RACTEST-SCAN.ractest.local:1521/ORMB

Target Schema is a Production Schema
Ready to process patches, Do you want to continue? (Y/N): Y

Working Directory: ORMB001
***********************************
Setting up language file: ORMB001\CDXPatch.lang
exit value: 0

Applying 25187694 ...
Writing to log file: ORMB001\log25187694.log
-----------------------------------------------------------
--Applying patch 25187694 at 12-08-2016 15:31:50using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 25187694 applied successfully at 12-08-2016 15:31:52
---------------------------------------------------------------
Patch applied successfully..
Enter a comma-separated list of Oracle users in which synonyms need to be created (e.g. CISUSER,CISREAD): CISUSER,CISREAD
Enter a database role which has a read-write privilege (e.g. CIS_USER): CIS_USER
Enter a database role which has a read only privilege (e.g. CIS_READ): CIS_READ
Generating Security ...
-----------------------------------------------------------
--Security is successfully applied at 12-08-2016 15:37:54
---------------------------------------------------------------
D:\PATCHES\p25187694_25030_PDEV\MultiPlatform\V2.5.0.3.0-25187694_MultiPlatform\database\ORACLE\CDXPatch>

5)  Review Patch Log File Including Security Generation Log

capture

—  log25187694

--Applying patch 25187694 at 12-08-2016 15:31:50using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information 
insert into ci_ut_instl(instl_log_id,target_ver_id,upg_count,prev_ver_id,start_dttm,run_status_flg,sr_no,owner_flg) values ( 51, 'V2.5.0.3.0', 1, 'V2.5.0.3.0', sysdate, '00', '25187694', 'C1' )
;
--Executed Successfully ...
COMMIT
;

--Executed Successfully ...
INSERT INTO CI_UT_INSTL_DTL(INSTL_LOG_ID,SEQ_NUM,SCRIPT_FILE_NAME,START_DTTM,RUN_STATUS_FLG,OWNER_FLG) VALUES ( 51, 1, '25187694_RS1.SQL', sysdate, '00', 'C1' )
;
--Executed Successfully ...
COMMIT
;

-- RUNNING SCRIPTS HERE
....
...
..
.

--Executed Successfully ...
COMMIT
;

--Executed Successfully ...
UPDATE CI_UT_INSTL_DTL SET END_DTTM=sysdate, RUN_STATUS_FLG='02' WHERE INSTL_LOG_ID=51 AND SEQ_NUM = 1
;
--Executed Successfully ...
COMMIT
;

--Executed Successfully ...
UPDATE CI_UT_INSTL SET END_DTTM=sysdate, RUN_STATUS_FLG='02' WHERE INSTL_LOG_ID=51
;
--Executed Successfully ...
COMMIT
;

--Executed Successfully ...
-----------------------------------------------------------
--Patch 25187694 applied successfully at 12-08-2016 15:31:52
---------------------------------------------------------------

6) Check the Patch has been Applied Successfully

Check the patch status as per How to Check the Successful Installation of a Database Patch Onto ORMB Database

SQL> select sr_no,run_status_flg,END_DTTM 
       from cisadm.ci_ut_instl 
      where sr_no in ( '25187694') 
        and run_status_flg = '02' 
        and END_DTTM IS Not NULL;

SR_NO     RU   END_DTTM
--------- --  -------------------
25187694  02   20161208-15:31:52