I always like inheriting databases, every day brings new surprises. A Data Guard production primary database underwent hardware maintenance. No biggie happens frequently should be easy to catch up. Over the course of the next few days I noticed that two particular query always seemed active:
INSERT INTO STATS$USER_LOG SELECT USER, SYS_CONTEXT('USERENV', 'SESSIONID'), SYS_CONTEXT('USERENV', 'HOST'), NULL, NULL, NULL, SYSDATE, TO_CHAR(SYSDATE, 'hh24:mi:ss'), NULL, NULL, NULL FROM SYS.DUAL WHERE USER NOT IN ('USER1', 'USER2', 'USER3', 'USER4', 'USER5', 'USER5', 'USER6', 'USER7')
UPDATE STATS$USER_LOG SET LAST_ACTION = (SELECT ACTION FROM V$SESSION WHERE SYS_CONTEXT('USERENV', 'SESSIONID') = AUDSID), LAST_PROGRAM = (SELECT PROGRAM FROM V$SESSION WHERE SYS_CONTEXT('USERENV', 'SESSIONID') = AUDSID), LAST_MODULE = (SELECT MODULE FROM V$SESSION WHERE SYS_CONTEXT('USERENV', 'SESSIONID') = AUDSID), LOGOFF_DAY = SYSDATE, LOGOFF_TIME = TO_CHAR(SYSDATE, 'hh24:mi:ss'), ELAPSED_MINUTES = ROUND((SYSDATE - LOGON_DAY) * 1440) WHERE SYS_CONTEXT('USERENV', 'SESSIONID') = SESSION_ID
Based on the statements there were logon and off triggers at work. Why were we experiencing an unusual high number of logons? A review of the stats$user_log showed a particular user PUBLIC with a high frequency of activity and no module associated.
USER_ID SESSION_ID HOST LAST_PROGRAM LAST_ACTION LAST_MODULE LOGON_DAY LOGON_TIME LOGOFF_DAY LOGOFF_TIME ELAPSED_MINUTES
—————————— ———————- —————————— ———————————————— ——————————– ——————————– ————————- ———- ————————- ———– ———————-
PUBLIC 4294967295 STDB1 14-MAR-13 10:28:05
PUBLIC 4294967295 STDB1 14-MAR-13 10:28:00
PUBLIC 4294967295 STDB1 14-MAR-13 10:27:44
PUBLIC 4294967295 STDB1 14-MAR-13 10:27:41
PUBLIC 4294967295 STDB1 14-MAR-13 10:26:37
PUBLIC 4294967295 STDB1 14-MAR-13 10:26:33
PUBLIC 4294967295 STDB1 14-MAR-13 10:25:44
PUBLIC 4294967295 STDB1 14-MAR-13 10:25:40
PUBLIC 4294967295 STDB1 14-MAR-13 10:25:37
PUBLIC 4294967295 STDB1 14-MAR-13 10:25:33
PUBLIC 4294967295 STDB1 14-MAR-13 10:25:30
PUBLIC 4294967295 STDB1 14-MAR-13 10:25:22
PUBLIC 4294967295 STDB1 14-MAR-13 10:24:35
the logons are occurring within seconds and interestingly the host is the standby. Gap resolution is currently occuring due to the maintenance a few days prior.
Data Guard makes use of the internal user PUBLIC within the Gap resolution. In previous versions the alert log of the primary showed the connection being made as Public. I don’t find any indication of the use of PUBLIC within the 11.2 alert log.
Since the PUBLIC user cannot be used to login into the database it serves no purpose to continue to audit these logon/offs. They are actually an overhead at a time when we don’t need the overhead… trying to catch up the standby.
I recompiled the trigger to include excluding the user PUBLIC and all is well.