OraFAQ Forum: Security » problem audit in oracle9i

Home » RDBMS Server » Security » problem audit in oracle9i (oracle9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem audit in oracle9i [message #406037]

Mon, 01 June 2009 20:36

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

Sad

Report message to a moderator

Re: problem audit in oracle9i [message #406038 is a reply to message #406037]

Mon, 01 June 2009 20:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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?

Report message to a moderator

Re: problem audit in oracle9i [message #406050 is a reply to message #406038]

Mon, 01 June 2009 23:46

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

Code: [Select all] [Show/ hide]

conn /as sysdba

Code: [Select all] [Show/ hide]

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

the output

Code: [Select all] [Show/ hide]

no rows selected

in my audit01.sql has

Code: [Select all] [Show/ hide]

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??

Report message to a moderator

Re: problem audit in oracle9i [message #406068 is a reply to message #406050]

Tue, 02 June 2009 00:50

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

what do u mean audit destination??

What is your audit_trail parameter value?

Regards
Michel

Report message to a moderator

Re: problem audit in oracle9i [message #406087 is a reply to message #406068]

Tue, 02 June 2009 03:07

aiznu
Messages: 24
Registered: May 2009

Junior Member

do you mean this?

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: problem audit in oracle9i [message #406096 is a reply to message #406087]

Tue, 02 June 2009 03:53

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No, it is:

Code: [Select all] [Show/ hide]

show parameter audit_trail

Regards
Michel

Report message to a moderator

Re: problem audit in oracle9i [message #406105 is a reply to message #406096]

Tue, 02 June 2009 04:43

aiznu
Messages: 24
Registered: May 2009

Junior Member

do u mean this? at D:\oracle\admin\dba\pfile\init.ora

Code: [Select all] [Show/ hide]

##############################################################################
# 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:

Code: [Select all] [Show/ hide]

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

ebrian
Messages: 2794
Registered: April 2006

Senior Member

You need to restart your database.

Report message to a moderator

Re: problem audit in oracle9i [message #406114 is a reply to message #406105]

Tue, 02 June 2009 05:12

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: problem audit in oracle9i [message #406123 is a reply to message #406114]

Tue, 02 June 2009 06:49

aiznu
Messages: 24
Registered: May 2009

Junior Member

i already to restart the database.but the result is same. Sad

Sad

it's right that i restart

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: problem audit in oracle9i [message #406126 is a reply to message #406123]

Tue, 02 June 2009 07:12

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: problem audit in oracle9i [message #406155 is a reply to message #406126]

Tue, 02 June 2009 10:24

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

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: problem audit in oracle9i [message #406156 is a reply to message #406037]

Tue, 02 June 2009 10:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

Report message to a moderator

Re: problem audit in oracle9i [message #406164 is a reply to message #406156]

Tue, 02 June 2009 10:48

aiznu
Messages: 24
Registered: May 2009

Junior Member

this the result:

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

show parameter audit_trail;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      TRUE

huh..why b4 this i can not this result Surprised

Surprised

Report message to a moderator

Re: problem audit in oracle9i [message #406208 is a reply to message #406164]

Tue, 02 June 2009 17:09

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:	database password changes (merged)
Next Topic:	data masking on xml columns

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 03:50:17 CDT 2025