EM 12c, EM 13c: Why does Agent execute "BEGIN Dbms_lock.sleep(60)" statement Having SQL ID "g0bggfqrddc4w" in the Target Database ? (Doc ID 2366263.1)

To BottomTo Bottom

In this Document

  Goal
  Solution
  References

 

APPLIES TO:

Enterprise Manager for Oracle Database - Version 12.1.0.6.0 and later
Information in this document applies to any platform.

GOAL

 When target database is monitored via Cloud control agent, it is found that the below SQL is frequently executed at the database:

select s.inst_id,S.USERNAME,to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS')"LOGIN_TIME",s.sid,s.serial#,s.event,s.status,p.spid,s.process,S.SQL_ID,s.program,s.machine,s.module,S.LAST_CALL_ET
from gv$session s,gv$process p
where p.addr=s.paddr and s.status='ACTIVE'

INST_ID DB User LOGIN_TIME DB SID Serial# EVENT Status Process Process SQL_ID Program Machine MODULE LAST_CALL_ET
---------- ---------- -------------------- ------ ------- -------------------- -------- -------- ----------- ------------- -------------------- -------------------- ---------------------------------------------------------------- ------------
................
1 DBSNMP 20-FEB-2018 17:27:42 86 16687 PL/SQL lock timer ACTIVE 12174 1234 g0bggfqrddc4w JDBC Thin Client <agenthostname.domain> JDBC Thin Client
...

- The session is started by the user configured as the Monitoring configuration user, in the above case it is 'dbsnmp'.

- If the agent is stopped, these sessions are cleaned up.

This document explains the purpose of the above statement executed by the agent.

SOLUTION

The reported statement is executed by the Agent as part of the "DB Real Time Monitoring" feature. Agent maintains a persistent connection with the target DB by executing the below statement:
  


BEGIN dbms_lock.sleep(60); END;


This is executed "every 1 minute" and allows the agent to know immediately if the DB goes down. If this statement is not executed, the DB status would be determined by the agent only when the Response metric is collected.

The "DB real time monitoring" feature requires that the monitoring configuration user has execute privileges on the dbms_lock package in the target DB: 

1. If the monitoring configuration username is set to 'sys as sysdba' or any user which has execute privileges on the dbms_lock package, the statement gets executed successfully.

2. If the monitoring configuration user does not have execute privileges on the dbms_lock package:

-  Prior to 13.2 agent release, the statement would fail silently and these errors can be identified only if auditing is enabled at the DB. The SQL execution would fail with: 

ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_LOCK' must be declared

 

- From 13.2 Agent onwards, this will report errors such as below in the <AGENT_INST>/sysman/log/emagent_sdk.trc file:
 

[320:6947BF8:FetchletExecutor(Sql)oracle_database.] WARN - Persistance Response monitoring is disabled for database target ''. DBSNMP user for this database does not have execute privilege on 'dbms_lock' packge. Please provide the required privilege, set '_enableSqlPersistance. = true' and then reload the agent 'emctl reload agent'


The error would be reported only once after the agent startup, after which the agent does not attempt to re-run the dbms_lock.sleep procedure again till the agent is re-started.
To avoid these errors, execute the below as a DBA user in the target database


GRANT EXECUTE ON sys.dbms_lock TO dbsnmp;

 

 


 

REFERENCES

BUG:22088515 - AGENT CAUSING ORA-06550 IN DATABASE EVERY 60 SECONDS