OraFAQ Forum: Server Administration » Connection log for bequeath connection

Home » RDBMS Server » Server Administration » Connection log for bequeath connection (Oracle 10g)

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

Connection log for bequeath connection [message #438610]

Mon, 11 January 2010 03:25

suresh_gau@hotmail.com
Messages: 4
Registered: January 2010
Location: Kathmandu, Nepal

Junior Member

Hello All,
We are known for that listener will no longer be used for oracle database connection if the connection is being requested from the server itself, it's called "Bequeath" connection.

I am searching such a log(something like listener log) which is maintained for "Bequeath" connection.
Can anybody suggest me how can I get the log of each "Bequeath" connection in a particular oracle database server.

Thanks in advance and Best Regards,
Suresh Gautam

Report message to a moderator

Re: Connection log for bequeath connection [message #438615 is a reply to message #438610]

Mon, 11 January 2010 03:41

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

Senior Member
Account Moderator

There is none.

Regards
Michel

Report message to a moderator

Re: Connection log for bequeath connection [message #438616 is a reply to message #438615]

Mon, 11 January 2010 03:42

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

You could audit "create session", I suppose.

Report message to a moderator

Re: Connection log for bequeath connection [message #438618 is a reply to message #438616]

Mon, 11 January 2010 04:00

suresh_gau@hotmail.com
Messages: 4
Registered: January 2010
Location: Kathmandu, Nepal

Junior Member

Hello John,
Thanks a lot for your quick response. We need to consider something like trigger based solution for auditing "create session", which will add additional overhead to the database. Listener log can give you details of connection establish through the listener log. Adding trigger based solution only for logging bequeath connection is no longer suitable for me.
Thanks and Best Regards,
Suresh Gautam

Report message to a moderator

Re: Connection log for bequeath connection [message #438621 is a reply to message #438618]

Mon, 11 January 2010 04:36

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

Senior Member
Account Moderator

Quote:

We need to consider something like trigger based solution for auditing "create session",

If you activate audit, it is internal triggers with far less overhead than custom trigger.
Anyway, it adds an overhead but if you really need it you have to activate it.
Note that if there was a file logging it would also add an overhead.

Regards
Michel

Report message to a moderator

Re: Connection log for bequeath connection [message #438646 is a reply to message #438610]

Mon, 11 January 2010 06:55

suresh_gau@hotmail.com
Messages: 4
Registered: January 2010
Location: Kathmandu, Nepal

Junior Member

Michel,
I can't disable to log listener log for some other reasons.
Do we have any option to disable bequeath connection in the database server?
If we disable bequeath connection, user will be enforced to use service name even if they are requesting the connection from the same server.

Thanks and Best Regards,
Suresh Gautam

Report message to a moderator

Re: Connection log for bequeath connection [message #438647 is a reply to message #438646]

Mon, 11 January 2010 07:00

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

Senior Member
Account Moderator

I don't think there is any option to prevent from local connection.
If you do not create user accounts on the server then they can't locally connect.
Why end users have account on the database server? Only administrators should have (and possibly a couple of batch accounts but with no interactive access).

Regards
Michel

Report message to a moderator

Re: Connection log for bequeath connection [message #438696 is a reply to message #438647]

Mon, 11 January 2010 22:20

suresh_gau@hotmail.com
Messages: 4
Registered: January 2010
Location: Kathmandu, Nepal

Junior Member

Thank you very much for responses, I am fully agree with these ideas. Now, I can conclude for my solution based on my experiments and your feedback.
Best Regards,
Suresh Gautam

[Updated on: Mon, 11 January 2010 22:21]

Report message to a moderator

Re: Connection log for bequeath connection [message #438698 is a reply to message #438696]

Mon, 11 January 2010 22:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

below is what happens for me.

cat /u01/app/oracle/admin/v102/adump/ora_26446.aud
Audit file /u01/app/oracle/admin/v102/adump/ora_26446.aud
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle
System name:	Linux
Node name:	bcm-laptop
Release:	2.6.28-17-generic
Version:	#58-Ubuntu SMP Tue Dec 1 18:57:07 UTC 2009
Machine:	i686
Instance name: v102
Redo thread mounted by this instance: 1
Oracle process number: 16
Unix process pid: 26446, image: oracle@bcm-laptop (TNS V1-V3)

Mon Jan 11 16:49:03 2010
LENGTH : '134'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[3] 'bcm'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'

Report message to a moderator

Re: Connection log for bequeath connection [message #438699 is a reply to message #438698]

Mon, 11 January 2010 22:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I then did
SQL> CONNECT SCOTT/TIGER
which produced following audit log file

cat /u01/app/oracle/admin/v102/adump/ora_2278.aud
Audit file /u01/app/oracle/admin/v102/adump/ora_2278.aud
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle
System name:	Linux
Node name:	bcm-laptop
Release:	2.6.28-17-generic
Version:	#58-Ubuntu SMP Tue Dec 1 18:57:07 UTC 2009
Machine:	i686
Instance name: v102
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 2278, image: oracle@bcm-laptop (TNS V1-V3)

Mon Jan 11 20:37:59 2010
LENGTH: "247"
SESSIONID:[7] "3261340" ENTRYID:[1] "1" STATEMENT:
[1] "1" USERID:[5] "SCOTT" USERHOST:[10] "bcm-laptop" TERMINAL:[5] "pts/2" ACTION:[3] "100" RETURNCODE:
[1] "0" COMMENT$TEXT:[26] "Authenticated by: DATABASE" OS$USERID:[3] "bcm" PRIV$USED:[1] "5"

The listener is down because I do all my connections to local DB

[Updated on: Tue, 12 January 2010 12:27] by Moderator

Report message to a moderator

Re: Connection log for bequeath connection [message #438752 is a reply to message #438699]

Tue, 12 January 2010 01:23

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

Senior Member
Account Moderator

What are your audit parameters (show parameter audit)?
Whar are your audit options (dba_priv_audit_opts, dba_stmt_audit_opts)?

Regards
Michel

Report message to a moderator

Re: Connection log for bequeath connection [message #438859 is a reply to message #438752]

Tue, 12 January 2010 11:55

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter audit

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest 		     string	 /u01/app/oracle/admin/v102/adu
						 mp
audit_sys_operations		     boolean	 FALSE
audit_syslog_level		     string
audit_trail			     string	 OS
SQL> select * from dba_priv_audit_opts;

USER_NAME		       PROXY_NAME
------------------------------ ------------------------------
PRIVILEGE				 SUCCESS    FAILURE
---------------------------------------- ---------- ----------

ALTER USER				 BY ACCESS  BY ACCESS


CREATE SESSION				 BY ACCESS  BY ACCESS


SQL> select * from dba_stmt_audit_opts;

USER_NAME		       PROXY_NAME
------------------------------ ------------------------------
AUDIT_OPTION				 SUCCESS    FAILURE
---------------------------------------- ---------- ----------

CREATE SESSION				 BY ACCESS  BY ACCESS


ALTER USER				 BY ACCESS  BY ACCESS

Report message to a moderator

Re: Connection log for bequeath connection [message #438862 is a reply to message #438859]

Tue, 12 January 2010 12:11

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

Senior Member
Account Moderator

This explains your audit files. Wink

Wink

Regards
Michel

[Updated on: Tue, 12 January 2010 12:12]

Report message to a moderator

Re: Connection log for bequeath connection [message #438863 is a reply to message #438862]

Tue, 12 January 2010 12:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Michel Cadot wrote on Tue, 12 January 2010 10:11

This explains your audit files.

>Can anybody suggest me how can I get the log of each "Bequeath" connection in a particular oracle database server.

& satisfies OP's request

Report message to a moderator

Re: Connection log for bequeath connection [message #438866 is a reply to message #438863]

Tue, 12 January 2010 12:30

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

Senior Member
Account Moderator

But I don't think it satisfies the following.

Quote:

Adding trigger based solution only for logging bequeath connection is no longer suitable for me.

OP wants something that is free. Wink

Wink

Regards
Michel

Report message to a moderator

Previous Topic:	trigger logon on database
Next Topic:	About REDO LOG FILE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 11:43:23 CST 2024