Home » RDBMS Server » Security » problem audit in oracle9i (oracle9i)
problem audit in oracle9i [message #406037] Mon, 01 June 2009 20:36 Go to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
hi

i have a problem. why when i query select * from dba_audit_session; i get no rows selected? it is my step is wrong.
my step is i conn /as sysdba
audit session;
conn user1/user1@bso;
conn user2/user2@bso;
conn /as sysdba;
and run this statement @D:\Book\audit01.sql;
in audit01.sql
COLUMN os_username FORMAT a15
COLUMN username FORMAT a15
COLUMN terminal FORMAT a15
COLUMN LOGON_TIME FORMAT a22
COLUMN LOGOFF_TIME FORMAT a22
SELECT os_username, username, terminal, returncode,
TO_CHAR(timestamp, 'DD-MON-YYYY HH24:MI:SS')
LOGON_TIME,
TO_CHAR(logoff_time, 'DD-MON-YYYY HH24:MI:SS')
LOGOFF_TIME
FROM dba_audit_session;
n i got no rows selected.

please help me.. Sad
Re: problem audit in oracle9i [message #406038 is a reply to message #406037] Mon, 01 June 2009 20:57 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.


What is Audit Destination?
Re: problem audit in oracle9i [message #406050 is a reply to message #406038] Mon, 01 June 2009 23:46 Go to previous messageGo to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
i have a problem. why when i query select the dba_audit_session, i get no rows selected? it is my step is wrong.
my step is
conn /as sysdba

audit session;
conn user1/user1@bso;
conn user2/user2@bso;
conn /as sysdba;
@D:\Book\audit01.sql;


the output
no rows selected


in my audit01.sql has
COLUMN os_username FORMAT a15
COLUMN username FORMAT a15
COLUMN terminal FORMAT a15
COLUMN LOGON_TIME FORMAT a22
COLUMN LOGOFF_TIME FORMAT a22
SELECT os_username, username, terminal, returncode,
TO_CHAR(timestamp, 'DD-MON-YYYY HH24:MI:SS')
LOGON_TIME,
TO_CHAR(logoff_time, 'DD-MON-YYYY HH24:MI:SS')
LOGOFF_TIME
FROM dba_audit_session;


help me Sad

Quote:
What is Audit Destination?

what do u mean audit destination??
Re: problem audit in oracle9i [message #406068 is a reply to message #406050] Tue, 02 June 2009 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 58855
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what do u mean audit destination??

What is your audit_trail parameter value?

Regards
Michel
Re: problem audit in oracle9i [message #406087 is a reply to message #406068] Tue, 02 June 2009 03:07 Go to previous messageGo to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
do you mean this?
DESC dba_audit_session;
 Name         Null?    Type

 OS_USERNAME          VARCHAR2(255)
 USERNAME             VARCHAR2(30)
 USERHOST             VARCHAR2(128)
 TERMINAL             VARCHAR2(255)
 TIMESTAMP   NOT NULL DATE
 ACTION_NAME          VARCHAR2(27)
 LOGOFF_TIME          DATE
 LOGOFF_LREAD         NUMBER
 LOGOFF_PREAD         NUMBER
 LOGOFF_LWRITE        NUMBER
 LOGOFF_DLOCK         VARCHAR2(40)
 SESSIONID   NOT NULL NUMBER
 RETURNCODE  NOT NULL NUMBER
 CLIENT_ID            VARCHAR2(64)
 SESSION_CPU          NUMBER
Re: problem audit in oracle9i [message #406096 is a reply to message #406087] Tue, 02 June 2009 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 58855
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, it is:
show parameter audit_trail

Regards
Michel
Re: problem audit in oracle9i [message #406105 is a reply to message #406096] Tue, 02 June 2009 04:43 Go to previous messageGo to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
do u mean this? at D:\oracle\admin\dba\pfile\init.ora
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
 
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=25165824
db_file_multiblock_read_count=16
 
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
 
###########################################
# Database Identification
###########################################
db_domain=""
db_name=dba
 
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=D:\oracle\admin\dba\bdump
core_dump_dest=D:\oracle\admin\dba\cdump
timed_statistics=TRUE
user_dump_dest=D:\oracle\admin\dba\udump
 
###########################################
# File Configuration
###########################################
control_files=("D:\oracle\oradata\dba\CONTROL01.CTL", "D:\oracle\oradata\dba\CONTROL02.CTL", "D:\oracle\oradata\dba\CONTROL03.CTL")
 
###########################################
# Instance Identification
###########################################
instance_name=dba
 
###########################################
# Job Queues
###########################################
job_queue_processes=10
 
###########################################
# MTS
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=dbaXDB)"
 
###########################################
# Miscellaneous
###########################################
aq_tm_processes=1
compatible=9.2.0.0.0
 
###########################################
# Optimizer
###########################################
hash_join_enabled=TRUE
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE
 
###########################################
# Pools
###########################################
java_pool_size=33554432
large_pool_size=8388608
shared_pool_size=50331648
 
###########################################
# Processes and Sessions
###########################################
processes=150
 
###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300
 
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE
audit_trail = true
 
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=25165824
sort_area_size=524288
 
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1
 

i already add audit_trail=true or do you mean this:
show parameter audit_trail;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      NONE

[Updated on: Tue, 02 June 2009 04:47]

Report message to a moderator

Re: problem audit in oracle9i [message #406109 is a reply to message #406105] Tue, 02 June 2009 04:55 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You need to restart your database.
Re: problem audit in oracle9i [message #406114 is a reply to message #406105] Tue, 02 June 2009 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 58855
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i already add audit_trail=true or do you mean this:

Yes I meant this and as you can see currently audit is inactive, you have to restart your database in order to activate it with the new value of audit_trail from your init.ora file.

Regards
Michel
Re: problem audit in oracle9i [message #406123 is a reply to message #406114] Tue, 02 June 2009 06:49 Go to previous messageGo to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
i already to restart the database.but the result is same. Sad
it's right that i restart
shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

startup
Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
Re: problem audit in oracle9i [message #406126 is a reply to message #406123] Tue, 02 June 2009 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 58855
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does "show parameter audit_trail;" give now?
If it is the same then you didn't start with the init.ora file you think or mayb eyouhave a spfile.
You can force the startup with the file you want on "startup" command.

Regards
Michel
Re: problem audit in oracle9i [message #406155 is a reply to message #406126] Tue, 02 June 2009 10:24 Go to previous messageGo to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
i got the same result.i don't understand how to startup the pfile.because when i startup, i got this result
startup pfile='D:\oracle\ora92\database\initDBA.ora';
ORA-01081: cannot start already-running ORACLE - shut it down first

i don't know what step to do because i tried many step but the result is same.please help me to explain step by step.thanks
Re: problem audit in oracle9i [message #406156 is a reply to message #406037] Tue, 02 June 2009 10:27 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
sqlplus
/ as sysdba
shutdown abort
startup pfile='D:\oracle\ora92\database\initDBA.ora';
SELECT * FROM V$VERSION
EXIT



COPY & PASTE above into command window.
COPY & PASTE results back here
Re: problem audit in oracle9i [message #406164 is a reply to message #406156] Tue, 02 June 2009 10:48 Go to previous messageGo to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
this the result:
Connect / AS SYSDBA
Connected.
shutdown abort;
ORACLE instance shut down.
ORACLE instance started.
startup pfile='D:\oracle\ora92\database\initDBA.ora';
Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE	9.2.0.1.0	Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production


ok now i get
show parameter audit_trail;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      TRUE


huh..why b4 this i can not this result Surprised
Re: problem audit in oracle9i [message #406208 is a reply to message #406164] Tue, 02 June 2009 17:09 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Because D:\oracle\admin\dba\pfile\init.ora is different than D:\oracle\ora92\database\initDBA.ora. Plus you may have an spfile in D:\oracle\ora92\database.

[Updated on: Tue, 02 June 2009 17:10]

Report message to a moderator

Previous Topic: AUDIT tracking for ROLLBACK and COMMIT
Next Topic: data masking on xml columns
Goto Forum:
  


Current Time: Thu Aug 21 07:35:00 CDT 2014

Total time taken to generate the page: 0.08343 seconds