Hello
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;USERNAME ACCOUNT_STATUS Last Login
------------------------------ --------------- ----------------------------------------
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
Alison
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
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
- call DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES on the primary
- then they are loaded into the database
- those changes will make their way into the standby