Re: Populating Oracle v$session.program from Tomcat Context.xml
We'd like to be able to tell apart database sessions from the 15 or so tomcat applications we have running on our web-servers. Most of them use similar logins, so we can't query the username from gv$session. We were hoping to instead query to program field to tell them apart. We were able to make this functionality work with the thin client, but we'd rather use the OCI client because it allows our web-apps to reconnect to the database service after a loss of connectivity (say during a cluster node reboot). So far though we've had no luck in getting it to work with OCI. Most of the suggestions and info out on the web imply that the best way to do it with OCI is programmatically from the java app. That's the dilemma. Do we have to take the time to change our apps to populate the program field, or can we do it from the context.xml file from our app? On Tue, Mar 15, 2011 at 8:23 PM, Jorge Medina cerebrotecnolog...@gmail.com wrote: What is the problem that you are trying to solve? On Mon, Mar 14, 2011 at 4:25 PM, Dan random.da...@gmail.com wrote: On Mon, Mar 14, 2011 at 11:25 AM, Dan random.da...@gmail.com wrote: On Mon, Mar 14, 2011 at 10:57 AM, chris derham ch...@derham.me.uk wrote: We have some working tomcat 6 instances that we'd like to identify Can you use the combination of machine and schema name to identify the instance? You didn't detail your environment, but if you have a cluster, then the machine name would uniquely identify the instance. If you have multiple different instances on the same machine, then surely the schema name would allow you to identify which user it is? This covers all possibilities unless you have different apps on the same machine in different tomcat instances talking to the same schema. Chris We are running all of our web-applications from two machines, and they all use the same schema/username, so unfortunately I need the program, client_info, module, etc field to identify them. We are running a RAC, and I'm querying gv$session which should get me all cluster member connections. As David said, this does work with the thin driver, but I need the service/load balancing functionality from OCI. Any more suggestions are welcome! Does anyone else have any additional thoughts on this? I'd sure appreciate more input. TIA, Dan - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Populating Oracle v$session.program from Tomcat Context.xml
-Original Message- From: Dan Sent: Wednesday, March 16, 2011 14:34 To: Tomcat Users List Subject: Re: Populating Oracle v$session.program from Tomcat Context.xml We'd like to be able to tell apart database sessions from the 15 or so tomcat applications we have running on our web-servers. Most of them use similar logins, so we can't query the username from gv$session. We were hoping to instead query to program field to tell them apart. We were able to make this functionality work with the thin client, but we'd rather use the OCI client because it allows our web-apps to reconnect to the database service after a loss of connectivity (say during a cluster node reboot). I want to put a fram of reference on this. Remember the OCI client uses OS resources to connect, as such it is going to pull the process name from the OS and java applications are not OS level precesses, you do not see them in the output of /bin/ps or taskmgr.exe. So far though we've had no luck in getting it to work with OCI. Most of the suggestions and info out on the web imply that the best way to do it with OCI is programmatically from the java app. That's the dilemma. Do we have to take the time to change our apps to populate the program field, or can we do it from the context.xml file from our app? On Tue, Mar 15, 2011 at 8:23 PM, Jorge Medina cerebrotecnolog...@gmail.com wrote: What is the problem that you are trying to solve? On Mon, Mar 14, 2011 at 4:25 PM, Dan random.da...@gmail.com wrote: On Mon, Mar 14, 2011 at 11:25 AM, Dan random.da...@gmail.com wrote: On Mon, Mar 14, 2011 at 10:57 AM, chris derham ch...@derham.me.uk wrote: We have some working tomcat 6 instances that we'd like to identify Can you use the combination of machine and schema name to identify the instance? You didn't detail your environment, but if you have a cluster, then the machine name would uniquely identify the instance. If you have multiple different instances on the same machine, then surely the schema name would allow you to identify which user it is? This covers all possibilities unless you have different apps on the same machine in different tomcat instances talking to the same schema. Chris We are running all of our web-applications from two machines, and they all use the same schema/username, so unfortunately I need the program, client_info, module, etc field to identify them. We are running a RAC, and I'm querying gv$session which should get me all cluster member connections. As David said, this does work with the thin driver, but I need the service/load balancing functionality from OCI. Any more suggestions are welcome! Does anyone else have any additional thoughts on this? I'd sure appreciate more input. TIA, Dan -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Principal Consultant 10 West 24th Street #100- - +1 (443) 269-1555 x333Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is copyright PD Inc, subject to license 20080407P00. - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Populating Oracle v$session.program from Tomcat Context.xml
We'd like to be able to tell apart database sessions from the 15 or so tomcat applications we have running on our web-servers. Most of them use similar logins, so we can't query the username from gv$session. We were hoping to instead query to program field to tell them apart. We were able to make this functionality work with the thin client, but we'd rather use the OCI client because it allows our web-apps to reconnect to the database service after a loss of connectivity (say during a cluster node reboot). You seem to be restating the original question. Did you try using dbcp connection pool, and executing a Oracle command as the connection is created to specify some context parameter? Without any feedback, its kind of hard to help further. I assume that it didn't work as you have seem to have asked the original question again. So what problems did you encounter? Chris
RE: Populating Oracle v$session.program from Tomcat Context.xml
-Original Message- From: chris derham Sent: Wednesday, March 16, 2011 15:41 To: Tomcat Users List Subject: Re: Populating Oracle v$session.program from Tomcat Context.xml We'd like to be able to tell apart database sessions from the 15 or so tomcat applications we have running on our web-servers. Most of them use similar logins, so we can't query the username from gv$session. We were hoping to instead query to program field to tell them apart. We were able to make this functionality work with the thin client, but we'd rather use the OCI client because it allows our web-apps to reconnect to the database service after a loss of connectivity (say during a cluster node reboot). You seem to be restating the original question. Did you try using dbcp connection pool, and executing a Oracle command as the connection is created to specify some context parameter? Without any feedback, its kind of hard to help further. I assume that it didn't work as you have seem to have asked the original question again. So what problems did you encounter? The problem he appears to be encountering is that he is unable to customize the value in v$session.program. The reason for the problem is that he does not know how to do it either when using the OCI jdbc driver (it is likely not possible). -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Principal Consultant 10 West 24th Street #100- - +1 (443) 269-1555 x333Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is copyright PD Inc, subject to license 20080407P00. - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Populating Oracle v$session.program from Tomcat Context.xml
The problem he appears to be encountering is that he is unable to customize
the
value in v$session.program. The reason for the problem is that he does not
know
how to do it either when using the OCI jdbc driver (it is likely not
possible).
Jason,
So oci or thin driver is largely irrelevant. For thin you configure the dbcp
connection pool with
jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE
or if using oci use
jdbc:oracle:oci:@myhost:1521:orcl
You can use the following in the connectionInitSqls to make sure a suitable
variable it set
exec dbms_application_info.set_action('INSERTING');
The site http://psoug.org/reference/sys_context.html references a bucket
load of different context parameters that can be set - CLIENT_IDENTIFIER and
CLIENT_INFO seem likely candidates. Then each time a connection is added to
the pool, suitable context parameters are set, and you can view them when
querying gv$session.
This seems like a suitable fix for the question originally posed.l I would
like to learn if this won't work, what is wrong with it. Without feedback,
hard to understand what is wrong
Chris
Re: Populating Oracle v$session.program from Tomcat Context.xml
Chris,
You can use the following in the connectionInitSqls to make sure a suitable
variable it set
exec dbms_application_info.set_action('INSERTING');
The site http://psoug.org/reference/sys_context.html references a bucket
load of different context parameters that can be set - CLIENT_IDENTIFIER and
CLIENT_INFO seem likely candidates. Then each time a connection is added to
the pool, suitable context parameters are set, and you can view them when
querying gv$session.
This seems like a suitable fix for the question originally posed.l I would
like to learn if this won't work, what is wrong with it. Without feedback,
hard to understand what is wrong
I plan to try this technique soon. It would really help us understand our
oracle load from tomcat vs. applications.
Thanks!
Regards,
Dave
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Populating Oracle v$session.program from Tomcat Context.xml
We'd like to be able to tell apart database sessions from the 15 or so tomcat applications we have running on our web-servers. Most of them use similar logins, so we can't query the username from gv$session. We were hoping to instead query to program field to tell them apart. We were able to make this functionality work with the thin client, but we'd rather use the OCI client because it allows our web-apps to reconnect to the database service after a loss of connectivity (say during a cluster node reboot). I believe that it is possible to do Fast Connection Failover using the Thin driver. We were on the verge of testing, but have been waiting months of some required Solaris patches to be applied by our overworked Sysadmin. But it ought to be possible. You have write your own ConnectionManager class. We plan to try these jars - ojdbc6.jar, ons.jar and ucp.jar from Oracle. Your connection string gets complex, but essentially ONS is contacted by the Oracle Grid Infrastructure to reset connections. Here is how we understand it for our aborted tests. 1) FCF helps do auto switch over in the java thin client 2) ONS passes to FCF the message sent to Data Guard from Oracle Restart during an auto switchover or fail over. It also passes the message from Data Guard during a manual switchover. 3) Data Guard does the database switch over. 4) Oracle Restart - monitors the data guard observer and triggers auto switch or fail over via data guard. It also manages restarting other needed oracle applications in the right order. Listener, dbconsole, agents, etc... I'm interested if anyone has experience with it? Regards, Dave So far though we've had no luck in getting it to work with OCI. Most of the suggestions and info out on the web imply that the best way to do it with OCI is programmatically from the java app. That's the dilemma. Do we have to take the time to change our apps to populate the program field, or can we do it from the context.xml file from our app? On Tue, Mar 15, 2011 at 8:23 PM, Jorge Medina cerebrotecnolog...@gmail.com wrote: What is the problem that you are trying to solve? On Mon, Mar 14, 2011 at 4:25 PM, Dan random.da...@gmail.com wrote: On Mon, Mar 14, 2011 at 11:25 AM, Dan random.da...@gmail.com wrote: On Mon, Mar 14, 2011 at 10:57 AM, chris derham ch...@derham.me.uk wrote: We have some working tomcat 6 instances that we'd like to identify Can you use the combination of machine and schema name to identify the instance? You didn't detail your environment, but if you have a cluster, then the machine name would uniquely identify the instance. If you have multiple different instances on the same machine, then surely the schema name would allow you to identify which user it is? This covers all possibilities unless you have different apps on the same machine in different tomcat instances talking to the same schema. Chris We are running all of our web-applications from two machines, and they all use the same schema/username, so unfortunately I need the program, client_info, module, etc field to identify them. We are running a RAC, and I'm querying gv$session which should get me all cluster member connections. As David said, this does work with the thin driver, but I need the service/load balancing functionality from OCI. Any more suggestions are welcome! Does anyone else have any additional thoughts on this? I'd sure appreciate more input. TIA, Dan - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Populating Oracle v$session.program from Tomcat Context.xml
We have some working tomcat 6 instances that we'd like to identify
As David said, this does work with the thin driver, but I need the
service/load balancing functionality from OCI. Any more suggestions
are welcome!
You didn't say what connection pool you are using, but if you setup DBCP,
you could use the connectionInitSqls property to execute some proprietary
set_context call in Oracle e.g.
dbms_session.set_context('vpd_new','loc_code', pLocCode)
This might allow you to set a flag on a per connection pool basis for all
connections coming from that instance.
I've never done it personally, but sounds like what you are after
Chris
Re: Populating Oracle v$session.program from Tomcat Context.xml
What is the problem that you are trying to solve? On Mon, Mar 14, 2011 at 4:25 PM, Dan random.da...@gmail.com wrote: On Mon, Mar 14, 2011 at 11:25 AM, Dan random.da...@gmail.com wrote: On Mon, Mar 14, 2011 at 10:57 AM, chris derham ch...@derham.me.uk wrote: We have some working tomcat 6 instances that we'd like to identify Can you use the combination of machine and schema name to identify the instance? You didn't detail your environment, but if you have a cluster, then the machine name would uniquely identify the instance. If you have multiple different instances on the same machine, then surely the schema name would allow you to identify which user it is? This covers all possibilities unless you have different apps on the same machine in different tomcat instances talking to the same schema. Chris We are running all of our web-applications from two machines, and they all use the same schema/username, so unfortunately I need the program, client_info, module, etc field to identify them. We are running a RAC, and I'm querying gv$session which should get me all cluster member connections. As David said, this does work with the thin driver, but I need the service/load balancing functionality from OCI. Any more suggestions are welcome! Does anyone else have any additional thoughts on this? I'd sure appreciate more input. TIA, Dan - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Populating Oracle v$session.program from Tomcat Context.xml
We have some working tomcat 6 instances that we'd like to identify Can you use the combination of machine and schema name to identify the instance? You didn't detail your environment, but if you have a cluster, then the machine name would uniquely identify the instance. If you have multiple different instances on the same machine, then surely the schema name would allow you to identify which user it is? This covers all possibilities unless you have different apps on the same machine in different tomcat instances talking to the same schema. Chris
Re: Populating Oracle v$session.program from Tomcat Context.xml
Hello all... We have some working tomcat 6 instances that we'd like to identify by querying the v$session.program field the oracle database they connect to. While there are no errors on startup for the tomcat instance, and we can connect to the database, nothing gets populated in v$session. This functionality seems to work for the oracle thin driver, but when I try using OCI nothing happens. The original field values persist. I've also tried the module and client_info fields, also with no luck. Here’s a sample from my resource block minus the extra stuff. Resource name=jdbc/resource auth=Container type=javax.sql.DataSource driverClassName=oracle.jdbc.OracleDriver connectionProperties=v$session.program=MyApp; url=jdbc:oracle:oci:@MyService/ I’ve posted this question to the oracle support forums with no luck, but I’m assuming more people here are running oracle than people there running tomcat. Any help would be greatly appreciated. We've used both OCI from Fortran and Thin Drivers from Java for a long time. As far as whether V$SESSION.PROGRAM is filled. It is not consistent for the Thin Driver, it depends on proper matching of the thin driver version with the Oracle. For example version 9 JDBC does not populate PROGRAM on Oracle 8 database. I have never seen the OCI set the PROGRAM value, but then we have been using the version 6 driver (code is from the early 90s). We are upgrading to Oracle 11 and I still haven't ever seen the OCI driver populate PROGRAM. So, I am also interested. Regards, Dave - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Populating Oracle v$session.program from Tomcat Context.xml
On Mon, Mar 14, 2011 at 10:57 AM, chris derham ch...@derham.me.uk wrote: We have some working tomcat 6 instances that we'd like to identify Can you use the combination of machine and schema name to identify the instance? You didn't detail your environment, but if you have a cluster, then the machine name would uniquely identify the instance. If you have multiple different instances on the same machine, then surely the schema name would allow you to identify which user it is? This covers all possibilities unless you have different apps on the same machine in different tomcat instances talking to the same schema. Chris We are running all of our web-applications from two machines, and they all use the same schema/username, so unfortunately I need the program, client_info, module, etc field to identify them. We are running a RAC, and I'm querying gv$session which should get me all cluster member connections. As David said, this does work with the thin driver, but I need the service/load balancing functionality from OCI. Any more suggestions are welcome! - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Populating Oracle v$session.program from Tomcat Context.xml
On Mon, Mar 14, 2011 at 11:25 AM, Dan random.da...@gmail.com wrote: On Mon, Mar 14, 2011 at 10:57 AM, chris derham ch...@derham.me.uk wrote: We have some working tomcat 6 instances that we'd like to identify Can you use the combination of machine and schema name to identify the instance? You didn't detail your environment, but if you have a cluster, then the machine name would uniquely identify the instance. If you have multiple different instances on the same machine, then surely the schema name would allow you to identify which user it is? This covers all possibilities unless you have different apps on the same machine in different tomcat instances talking to the same schema. Chris We are running all of our web-applications from two machines, and they all use the same schema/username, so unfortunately I need the program, client_info, module, etc field to identify them. We are running a RAC, and I'm querying gv$session which should get me all cluster member connections. As David said, this does work with the thin driver, but I need the service/load balancing functionality from OCI. Any more suggestions are welcome! Does anyone else have any additional thoughts on this? I'd sure appreciate more input. TIA, Dan - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Populating Oracle v$session.program from Tomcat Context.xml
We have some working tomcat 6 instances that we'd like to identify Can you use the combination of machine and schema name to identify the instance? You didn't detail your environment, but if you have a cluster, then the machine name would uniquely identify the instance. If you have multiple different instances on the same machine, then surely the schema name would allow you to identify which user it is? This covers all possibilities unless you have different apps on the same machine in different tomcat instances talking to the same schema. Chris We are running all of our web-applications from two machines, and they all use the same schema/username, so unfortunately I need the program, client_info, module, etc field to identify them. We are running a RAC, and I'm querying gv$session which should get me all cluster member connections. As David said, this does work with the thin driver, but I need the service/load balancing functionality from OCI. Any more suggestions are welcome! Does anyone else have any additional thoughts on this? I'd sure appreciate more input. I did some googling and I think that you will have to actually set this value from your code somehow. This thread is found in multiple places: http://www.rhinocerus.net/forum/databases-oracle-misc/569644-setting-v-session-program.html a The attribute is OCI_ATTR_MEMPOOL_APPNAME. The package used is dbms_application_info. Google jdbc api dbms_application_info and this link http://stackoverflow.com/questions/53379/using-dbms-application-info-with-jboss may help you ... I do wonder if using module and action from V$session might be more appropriate and fine grained. Regards, Dave TIA, Dan - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Populating Oracle v$session.program from Tomcat Context.xml
Subject: Re: Populating Oracle v$session.program from Tomcat Context.xml From: dfis...@jmlafferty.com Date: Mon, 14 Mar 2011 18:08:53 -0700 To: users@tomcat.apache.org We have some working tomcat 6 instances that we'd like to identify Can you use the combination of machine and schema name to identify the instance? You didn't detail your environment, but if you have a cluster, then the machine name would uniquely identify the instance. If you have multiple different instances on the same machine, then surely the schema name would allow you to identify which user it is? This covers all possibilities unless you have different apps on the same machine in different tomcat instances talking to the same schema. Chris We are running all of our web-applications from two machines, and they all use the same schema/username, so unfortunately I need the program, client_info, module, etc field to identify them. We are running a RAC, and I'm querying gv$session which should get me all cluster member connections. As David said, this does work with the thin driver, but I need the service/load balancing functionality from OCI. Any more suggestions are welcome! Does anyone else have any additional thoughts on this? I'd sure appreciate more input. I did some googling and I think that you will have to actually set this value from your code somehow. This thread is found in multiple places: http://www.rhinocerus.net/forum/databases-oracle-misc/569644-setting-v-session-program.html a The attribute is OCI_ATTR_MEMPOOL_APPNAME. The package used is dbms_application_info. Google jdbc api dbms_application_info and this link http://stackoverflow.com/questions/53379/using-dbms-application-info-with-jboss may help you ... I do wonder if using module and action from V$session might be more appropriate and fine grained. Regards, Dave MGcontext.xml are for the most part static declrataions loaded at webapp initialisation MGso if i follow your path..read in the base attributes from Tomcat Context MGlater when the webapp is loaded ..override Oracle specific attributes from Oracle V$session MGfrom my experience you cannot call OCI from (thin) type4 JDBC Oracle Drivers MGbe aware OCI loads native binaries (so and .a libs on unix) and dll on windows.. be prepared to make (Oracle) version specific calls for OCI functions MGMartin- TIA, Dan - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
