Oracle logging - HPUX

[joe_91]

This is what happ today in our HPUX 11.0 Environment runnng oracle 8i. One of our database passwd got reverted back to OLD passwd. (we have mechanism to age passwd - passwd aging happened on Sep 15th succesfully to new random passwd)ths morning when a database user tried to login he failed as the DB passwd was changed..I would like to know if anyone has tried to login to oracle database this morning before and after we bounced the Service Guard package. Could anyone please tell me what files to look into when someone tries logging into database. (they might have used sqlplus commands). It shd be there in some file. Please let me know.

Thanks

Joe.

[A. Clay Stephenson]

The most likely location will be in the listener log. Look in ${ORACLE_HOME}/network/log/XXX.log.

[joe_91]

Clay:

BIG thanks. Now that i have located the LOG file (it is way too big) how do i see the entry for change in the passwd or any relevant info pertaining to the outage? Please help

Thanks

Joe.

[John Wimmer_1]

The listener log will probably give you everything you need to know. How ever, there are some other places you can check, especially if they were to log in to sqlplus locally.

If the user they logged in as sysdba or as SYS, you can look in the audit_file_Dest directory. The default is $ORACLE_HOME/rdbms/audit. There is a .aud file for each login to the database of privileged users.

You can also check the table SYS.AUD$ to see login and failed logins if you have auditing turned on.

Again, the listener log is probbaly what you need, but here's twoo more places to look, just in case.

[Yogeeraj_1]

hi,

the listener.log and the audit files can come to the rescue.

in the audit files, you will have messages like:
Wed Sep 21 08:00:06 2005
ACTION : 'connect sys' OSPRIV : DBA
CLIENT USER: oracle8i
CLIENT TERMINAL:
STATUS: SUCCEEDED ( 0 )


also investigate on users who have such privileges that allow them to change passwords and have root/oracle user (or like) accesses

hope this helps too!

regards
yogeeraj

[Leon Allen]

I have a neat little script which does this.....

-->ceklistener
Recent Database Connections for cis2
On 21-SEP-2005 at 13:50:23 user oracle connected from cis4 using
On 21-SEP-2005 at 13:51:24 user oracle connected from cis4 using
On 21-SEP-2005 at 13:52:24 user oracle connected from cis4 using
On 21-SEP-2005 at 13:53:25 user oracle connected from cis4 using
On 21-SEP-2005 at 13:54:25 user oracle connected from cis4 using
On 21-SEP-2005 at 13:55:26 user oracle connected from cis4 using
On 21-SEP-2005 at 13:56:15 user connected from cis2 using
On 21-SEP-2005 at 13:56:26 user oracle connected from cis4 using
On 21-SEP-2005 at 13:57:27 user oracle connected from cis4 using
On 21-SEP-2005 at 13:58:27 user oracle connected from cis4 using
On 21-SEP-2005 at 13:59:28 user oracle connected from cis4 using
On 21-SEP-2005 at 14:00:28 user oracle connected from cis4 using
On 21-SEP-2005 at 14:01:28 user oracle connected from cis4 using
On 21-SEP-2005 at 14:02:29 user oracle connected from cis4 using
On 21-SEP-2005 at 14:03:29 user oracle connected from cis4 using
On 21-SEP-2005 at 14:04:30 user oracle connected from cis4 using
On 21-SEP-2005 at 14:05:30 user oracle connected from cis4 using
On 21-SEP-2005 at 14:06:14 user connected from cis2 using
On 21-SEP-2005 at 14:06:22 user oracle connected from cis4 using
On 21-SEP-2005 at 14:06:31 user oracle connected from cis4 using
On 21-SEP-2005 at 14:07:32 user oracle connected from cis4 using


The script looks like this:
grep -v service_update /s01/app/oracle/product/920/network/log/listener.log | tail -n 2048 | awk -f logins.awk

logins.awk looks like this:
-->cat logins.awk
BEGIN {
FS=" "
OFS=" "
print "Recent Database Connections for cis2"
}
$0 ~ /USER/ && $0 !~ /techone/ { num=index($0,"USER");
num=num+5;
let=index($0,")))");
tot=let-num;

num2=index($0,"HOST");
num2=num2+5;
let2=index($0,")(USER");
tot2=let2-num2;

num3=index($0,"PROGRAM");
num3=num3+8;
let3=index($0,")(HOST");
tot3=let3-num3;

print "On " $1,"at",$2,"user " substr($0,num,tot), "connected from",substr($0,num2,tot2), "using " substr($0,num3,tot3) ; matches++
}
END { print "A total of "matches" matches."}


oracle@cis2:cscdev in /home/oracle
-->

You will have to change the listener log file name ans path to suite your own environment.

The script, and its output, has been put to many uses.

Good luck!

[Leon Allen]

I hope my cut and pastes above did not get truncated !?!?

[Jean-Luc Oudart]

Joe

could I suggest you setup an Afterlogon trigger (for the future) ?

cf. Metalink Note:70679.1 "How to Audit Logon/Logoff Events with Triggers"
and also :
Note:167293.1 "Some examples about auditing and output of auditing"

Regards
Jean-Luc