EM12C: How to create a PL/SQL-based Metric Extension

Problem :- Oracle Enterprise Manager 12.1.0.1 Cloud Control. How to create a PL/SQL-based Metric Extension?

Solution: – You can use the following steps to create a PL/SQL-based Metric Extension:

 

1- Log into OEM Cloud Control –> from the ‘Enterprise’ menu, select ‘Monitoring’, then, ‘Metric Extensions’.

 

2- Click ‘Create’.

 

3- In the Create New: Generic Properties page, provide the following information:

* Target Type: Database Instance

* Name: <Type a valid name for the ME>

* Display Name: <The name to be displayed for the ME>

* Adapter: SQL

 

4- In the Create New : Adapter page, provide the following information:

* SQL Query:

for example:

DECLARE

v_testvalue VARCHAR2(32767):=’/tnsnames/dev’;

v_actualvalue VARCHAR2(32767);

v_testresult NUMBER;

BEGIN

sys.dbms_system.get_env(‘TNS_ADMIN’,v_actualvalue);

OPEN :1 FOR SELECT

CASE v_actualvalue

WHEN v_testvalue THEN

0

ELSE

1

END INTO v_testresult FROM dual;

END;

* Out Parameter Position: 1 –> this is the bind variable and must be numeric

* Out Parameter Type: SQL_CURSOR –> this is the only valid type

 

5- In the Create New : Columns page, click ‘Add’ and provide the following information:

 

* Name: v_testresult

* Column Type: Data Column

* Value Type: Number