Home » RDBMS Server » Server Administration » Connection log for bequeath connection (Oracle 10g)
Connection log for bequeath connection [message #438610] Mon, 11 January 2010 03:25 Go to next message
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
Re: Connection log for bequeath connection [message #438615 is a reply to message #438610] Mon, 11 January 2010 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is none.

Regards
Michel
Re: Connection log for bequeath connection [message #438616 is a reply to message #438615] Mon, 11 January 2010 03:42 Go to previous messageGo to next message
John Watson
Messages: 6580
Registered: January 2010
Location: Global Village
Senior Member
You could audit "create session", I suppose.
Re: Connection log for bequeath connection [message #438618 is a reply to message #438616] Mon, 11 January 2010 04:00 Go to previous messageGo to next message
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
Re: Connection log for bequeath connection [message #438621 is a reply to message #438618] Mon, 11 January 2010 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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


Re: Connection log for bequeath connection [message #438646 is a reply to message #438610] Mon, 11 January 2010 06:55 Go to previous messageGo to next message
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
Re: Connection log for bequeath connection [message #438647 is a reply to message #438646] Mon, 11 January 2010 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Connection log for bequeath connection [message #438696 is a reply to message #438647] Mon, 11 January 2010 22:20 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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'
Re: Connection log for bequeath connection [message #438699 is a reply to message #438698] Mon, 11 January 2010 22:41 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Connection log for bequeath connection [message #438859 is a reply to message #438752] Tue, 12 January 2010 11:55 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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
Re: Connection log for bequeath connection [message #438862 is a reply to message #438859] Tue, 12 January 2010 12:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This explains your audit files. 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 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Michel Cadot wrote on Tue, 12 January 2010 10:11
This explains your audit files. Wink


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

& satisfies OP's request
Re: Connection log for bequeath connection [message #438866 is a reply to message #438863] Tue, 12 January 2010 12:30 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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

Regards
Michel
Previous Topic: trigger logon on database
Next Topic: About REDO LOG FILE
Goto Forum:
  


Current Time: Fri Dec 09 06:15:04 CST 2016

Total time taken to generate the page: 0.12853 seconds