Thanks for the question, Vinoth kumar.
Asked: February 04, 2025 - 9:06 am UTC
Last updated: February 05, 2025 - 9:46 am UTC
Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Viewed 1000+ times
You Asked
I got a requirement to capture user login logout timestamp of database in any method like login via application, Sql development tool or any source. the purpose is to track all user login/logout information of database.
We tried with logon and log off trigger but issue is log off Triger not fire when kill session or database shutdown.
We tried to enable dba_audit_session but this table not capture session history because after database upgrade from 12c to 19c our DBA disabled audit session not sure about the reason. Now dba not able to enable again to fix this issue dba recommended to re-patch 19c upgrade but re-patch will impact current system behavior.
So apart from these two option do we have any other options to captre user login/ logout timestap in database level. please help on this.
Thanks,
Vinoth kumar.J
and Chris said...
Enabling auditing or using a logon/logoff trigger are the only ways I know to capture this information.
It's unclear why the DBA is unable to reenable auditing of these actions. They should be able to do this with:
CREATE AUDIT POLICY LOG_ON_OFF ACTIONS LOGON, LOGOFF;
AUDIT POLICY LOG_ON_OFF;
If you want further help then please clarify what exactly the DBA did and why they're struggling to reenable this auditing.