Skip to Main Content
  • Questions
  • Record / Check Login Information for Standby DBs


Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 23, 2024 - 8:39 am UTC

Last updated: February 27, 2024 - 4:21 am UTC

Version: 19C

Viewed 1000+ times

You Asked

We want to housekeep our user accounts and remove unsed and locked accounts. As far as I understand, the information in dba_users is from the primary DB. Users are not allowed to logon to the primary to query data, they must logon the read only standby (regulated by a trigger). When I look in dba_users on the standbys I can see several users that have not or never logged on:

select username, account_status,
nvl(to_char(last_login),'never logged on') "Last Login"
from dba_users where oracle_maintained = 'N'
and username not in ('AAAAAAAAAAA','BBBBBB','CCCCCCC')
and username not like '%READ%'
and username not like '%Exxx%'
order by "Last Login" desc;

------------------------------ --------------- ----------------------------------------
Pxxxxxxx OPEN never logged on
Pxxxxxxx_03 LOCKED never logged on
Pxxxxxxx_05 LOCKED never logged on
Pxxxxxxx_04 LOCKED never logged on
Pxxxxxxx_01 LOCKED never logged on
BRxxxxxxx OPEN never logged on
Pxxxxxxx_02 LOCKED never logged on
Sxxxxxxx EXPIRED never logged on
Jxxxxxxx EXPIRED never logged on
Mxxxxxxx OPEN 2020-09-05:19:48:06 GMT+01:00
Bxxxxx OPEN 2020-09-05:19:19:52 GMT+01:00
Axxxxxx OPEN 2016-05-20:09:17:33 GMT+01:00
Pxxxxxxxxxx_01 OPEN 2016-04-21:10:48:34 GMT+01:00
Kxxxxx OPEN 2016-04-19:13:50:33 GMT+01:00
Pxxxxxxxxxx_01 OPEN 2016-04-13:14:18:17 GMT+01:00

However, this information from dba_users is identical on primary and standby DBs.

The users told me that they have logged to the standby recently. As far as I understand the information in dba_users, also on the standby has been inherited from the primary as normal catalogue tables are not updated on the standby. Is this correct? How can I see last logins on the standby, preferably witthout using auditing which could cause a performance degredation and this is a production system where performance is key.
Many thanks

We are using active dataguard, and our idea at the moment is to record logins to the standby using a trigger which checks if standby or primary and then writes logon data acroos a DB link into a table on primary. Many thanks

and Connor said...

On a (read only) standby, we obviously cannot write to the database, which means standard auditing (to db) cannot occur.

However, this can be resolved by using *unified* auditing. In such case, it will write the audit information to OS files, which can still be queried using v$unified_audit_trail.

If you wanted this information to recorded *in* the database, one option you could then do is:

- copy the OS files back to the primary
- then they are loaded into the database
- those changes will make their way into the standby

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library