Mushfique Khan
asked on
Modify listener port
Task: to identify from where all the connections are coming or better to say, who is coming from where?
Reason: Planning to change the database listening port, earlier it was 1521, the default, but making it 1540
Please advise/share what are our best options, thinking of looking into listener.log and extracting all the HOST=<ip address>.
Please do update/reply, if any guru has any better solution to find all the hosts from where connections are coming.
Thanks in advnace.
Reason: Planning to change the database listening port, earlier it was 1521, the default, but making it 1540
Please advise/share what are our best options, thinking of looking into listener.log and extracting all the HOST=<ip address>.
Please do update/reply, if any guru has any better solution to find all the hosts from where connections are coming.
Thanks in advnace.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the code block in the original post shows how to do it.
audit create session; -- just those 3 words
it's very possible your dba had auditing already turned on, in which case you can simply query the view without doing anything.
then any time someone logs on a record will be created in dba_audit_trail.
then you simply query that view.
select * from dba_audit_trail.
make sure your init parameter audit_trail is set to write to the DB
select value from v$parameter where name = 'audit_trail';
you want either DB or DB, EXTENDED
audit create session; -- just those 3 words
it's very possible your dba had auditing already turned on, in which case you can simply query the view without doing anything.
then any time someone logs on a record will be created in dba_audit_trail.
then you simply query that view.
select * from dba_audit_trail.
make sure your init parameter audit_trail is set to write to the DB
select value from v$parameter where name = 'audit_trail';
you want either DB or DB, EXTENDED
ASKER
it's OS ... :( now what to do?
OS means the audit records are written to files outside of the database. So you can't simply use sql to read the data.
you can still read the files, and try to parse out the userhost values from the file, it'll be a similar effort to what you're doing with listener logs
use this query to find where the audit files are written
select value from v$parameter where name = 'audit_file_dest';
you can still read the files, and try to parse out the userhost values from the file, it'll be a similar effort to what you're doing with listener logs
use this query to find where the audit files are written
select value from v$parameter where name = 'audit_file_dest';
ASKER
yes sdstuber already got the location, but now the same task, how to extract the host info from those log files ... will you be able to assist here?
try something like this
awk '{for (i=1;i<=NF;i++) {if ($i == "USERHOST:") {print $(i+1)}}}' your_audit_file_here.aud
it is important to note that Oracle doesn't document the file format of the OS audit files and has changed the format between versions, so what works now may not work in the future and may not work on older versions.
If you want more consistent content then you might need to switch to XML format, or not use external files, but instead use DB and then you can use the dba_audit_trail as shown above
awk '{for (i=1;i<=NF;i++) {if ($i == "USERHOST:") {print $(i+1)}}}' your_audit_file_here.aud
it is important to note that Oracle doesn't document the file format of the OS audit files and has changed the format between versions, so what works now may not work in the future and may not work on older versions.
If you want more consistent content then you might need to switch to XML format, or not use external files, but instead use DB and then you can use the dba_audit_trail as shown above
ASKER
this didn't work, I've submitted another question over here, how to find, trying that ...
ASKER
thanks again for your quick response :)