Solved

Modify listener port

Posted on 2015-01-27
8
147 Views
Last Modified: 2015-02-11
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.
0
Comment
Question by:mkhandba
  • 4
  • 4
8 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40572933
turn on auditing for your connections  and look at the userhost column of dba_audit_trail
audit create session;

Open in new window



an advantage of going to the listener log though is that your can also find attempted but failed connections.  The database audit trail will only capture activity that actually made it to the db.
0
 

Author Comment

by:mkhandba
ID: 40572990
thanks sdstuber, but can you please share how to accomplish it, how to enable this auditing or either can direct me to any link/doc, from where I can get something step by step.

thanks again for your quick response :)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40573025
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
0
 

Author Comment

by:mkhandba
ID: 40573341
it's OS ... :( now what to do?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 40573380
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';
0
 

Author Comment

by:mkhandba
ID: 40574230
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?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40575100
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
0
 

Author Comment

by:mkhandba
ID: 40582471
this didn't work, I've submitted another question over here, how to find, trying that ...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now