Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 20, 2017 - 8:50 am UTC

Last updated: October 06, 2017 - 5:33 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi Tom,
I want to capture the IP address of any client who has shutdown the db. Support currently in my db 5 clients are connected, one client shutdown the db, then I want to capture the IP address of client who has been shutdown the db. How to solve the requirement, please help me.

and Connor said...

If someone connected as SYSDBA, we will have an audit record captured, eg

SQL> show parameter audit_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/db122/adump
SQL> 


Audit file /u01/app/oracle/admin/db122/adump/db122_ora_4720_20170929143241319057143795.aud
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label:    RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME:    /u01/app/oracle/product/12.2/db_2
System name:    Linux
Node name:      vbgeneric
Release:        4.1.12-61.1.27.el7uek.x86_64
Version:        #2 SMP Fri Feb 3 12:31:56 PST 2017
Machine:        x86_64
Instance name: db122
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 4720, image: oracle@vbgeneric

Fri Sep 29 14:32:41 2017 -04:00
LENGTH : '324'
ACTION :[7] 'CONNECT'
DATABASE USER:[3] 'SYS'
PRIVILEGE :[4] 'NONE'
CLIENT USER:[6] 'hamcdc'
CLIENT TERMINAL:[5] 'XPS13'
STATUS:[5] '28009'
DBID:[9] '871653213'
SESSIONID:[10] '4294967295'
USERHOST:[15] 'WORKGROUP\XPS13'
CLIENT ADDRESS:[55] '(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.1)(PORT=52683))'  <<====== the pc I connected from
ACTION NUMBER:[3] '100'


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Audit files

Rajeshwaran Jeyabal, October 03, 2017 - 2:37 pm UTC

Team:

I am on 12.2 and dont see any files in the ADUMP directory.

demo@ORA12C> show parameter audit_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      D:\APP\VNAMEIT\VIRTUAL\ADMIN\O
                                                 RA12C\ADUMP
demo@ORA12C> $dir D:\APP\VNAMEIT\VIRTUAL\ADMIN\ORA12c\adump
 Volume in drive D is DATA
 Volume Serial Number is 50D3-A8AC

 Directory of D:\APP\VNAMEIT\VIRTUAL\ADMIN\ORA12c\adump

03/18/2017  01:30 PM    <DIR>          .
03/18/2017  01:30 PM    <DIR>          ..
               0 File(s)              0 bytes
               2 Dir(s)  239,924,244,480 bytes free

demo@ORA12C>


do i need to set something to get this audited?
Connor McDonald
October 05, 2017 - 4:13 pm UTC

On windows, by default, they will go into the Windows event log.

Other possibilities

- Do you have unified audit ?
- audit_sys_operations not set

You can change audit trail to "XML" if you want to override this.

Alex, October 05, 2017 - 8:28 pm UTC

What a crazy situation/requirement this is. Tom is rolling over in his retirement hammock. I would look at what accounts have permission to shutdown (it should be no one....), and change the SYS password.

Man talk about the wild west.
Connor McDonald
October 06, 2017 - 5:33 am UTC

Yes I was biting my tongue :-)

Sadly we got lots of questions along these lines (I should blog about it)

"Who dropped this table"
"Who shut my database"
"Who modified this data"

which ultimately all lead to ...

"Who hacked my database"


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.