No, that's the main problem. Same-same. I agree app segregation its the way to go. On Apr 1, 2014 10:38 PM, "Seth Miller" <sethmiller.sm@xxxxxxxxx> wrote: > Ricard, > > Are the tables they are reporting against different from those on which > they are doing DML? > > Are there specific key words or predicates they are using in their > reporting select statements that don't exist in the DML? > > Seth Miller > > > On Tue, Apr 1, 2014 at 10:49 AM, Ricard Martinez < > ricard.martinez@xxxxxxxxx> wrote: > >> Yes, there is only one schema. So they connect to odbc connection RW or >> RO with the same schema, that have the same privileges on both databases >> (as they are primary and dataguard all is replicated between them). So we >> cant prevent them from connecting to the RW as they need to in order to do >> updates/insert. >> We discarded resource limit because it will be up for the same schema in >> primary and dataguard, so will prevent running reports on both databases. >> The brute force kill is what we had though already, but is not very >> clean, so just checking for more ideas before implementing it. >> >> Thanks again guys! >> >> >> On Tue, Apr 1, 2014 at 4:05 PM, <Jay.Miller@xxxxxxxxxxxxxxxx> wrote: >> >>> If I understand correctly (please correct me if I'm wrong) the problem >>> is that the same user needs to be able to log into both databases using the >>> ODBC connection and the problem is getting them to connect to the correct >>> database depending on which functionality they will be using? >>> >>> >>> >>> >>> >>> >>> >>> Jay Miller >>> >>> Sr. Oracle DBA >>> >>> >>> >>> *From:* Andrew Kerber [mailto:andrew.kerber@xxxxxxxxx] >>> *Sent:* Tuesday, April 01, 2014 11:02 AM >>> *To:* Miller, Jay >>> *Cc:* ricard.martinez@xxxxxxxxx; keyantech; ORACLE-L >>> >>> *Subject:* Re: Force users to use Active DG for reporting >>> >>> >>> >>> I think you can identify an odbc connection from the sys context. You >>> could set up a log on trigger based on that to disconnect when they try to >>> log into the rw service. >>> >>> >>> >>> On Tue, Apr 1, 2014 at 10:00 AM, <Jay.Miller@xxxxxxxxxxxxxxxx> wrote: >>> >>> One brute force option is to monitor the database for long running >>> SELECTs and kill the session. If they learn that there's a good chance any >>> reports against the main database will be killed halfway through that will >>> change their behavior eventually. >>> >>> >>> >>> If the app work never has any long running DML then you could set a >>> resource limit for their profile but you'd have to be very sure that there >>> is no legitimate non-reporting work that would take more time than the >>> resource limit you set. >>> >>> >>> >>> Jay Miller >>> >>> Sr. Oracle DBA >>> >>> 201.369.8355 >>> >>> >>> >>> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto: >>> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Ricard Martinez >>> *Sent:* Tuesday, April 01, 2014 4:47 AM >>> *To:* Andrew Kerber >>> *Cc:* keyantech@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx >>> *Subject:* Re: Force users to use Active DG for reporting >>> >>> >>> >>> Thanks for all the ideas. >>> >>> Im afraid the main problem we have is how to prevent them from using RW >>> for all work. They use MS Access odbc (ye...i know) to connect to the >>> databases, so they just connect to the RW and run their updates and reports >>> for it. Without using a web/app-tier (as they should and we are trying to >>> force) its difficult that service or load balancing help us on that. Anyway >>> thanks again for all the ideas. >>> >>> >>> >>> >>> >>> On Mon, Mar 31, 2014 at 11:25 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx> >>> wrote: >>> >>> Yup, and you can set up a service like this: >>> >>> srvctl add service -d dbname -s service_name -r instname -l >>> PHYSICAL_STANDBY >>> >>> to create a service that is only running when the role is physical >>> standby. >>> >>> >>> >>> On Mon, Mar 31, 2014 at 5:21 PM, Karth Panchan <keyantech@xxxxxxxxx> >>> wrote: >>> >>> If I am not wrong, Active Data Guard enables read-only access to >>> Primary standby from Oracle 11g. >>> >>> This will enable to run for Reporting. >>> >>> Karth >>> >>> >>> Sent from my IPhone >>> >>> >>> > On Mar 31, 2014, at 11:55 AM, Ricard Martinez < >>> ricard.martinez@xxxxxxxxx> wrote: >>> > >>> > Hi list, >>> > >>> > We got an environment formed by one standalone 11g database (lets call >>> it RW) with an active dataguard on another server (lets call it RO) >>> > We have provided to the users 2 tnsnames, one pointing to RW and the >>> other to the RO, and we have told them to use the first one for DML, etc >>> and the RO only for runing the reporting statments. >>> > >>> > As good users, they just ignore us and run all on the RW database, so >>> we want to force them to use the RO for reporting. >>> > Meanwhile they got the 2 tnsnames entries, i see no real options to >>> force them to use the RW, unless we separate the schema in two (one with >>> insert/update, the other only with select), and we kill any session of the >>> select schema on the RO (using a cron maybe, forcing them to use RW to be >>> able to end their reports) >>> > >>> > Has any of you found in a similar situation, or can think in other >>> options? >>> > >>> > Thanks >>> > >>> > >>> > >>> > >>> > >>> > >>> >>> -- >>> //www.freelists.org/webpage/oracle-l<https://urldefense.proofpoint.com/v1/url?u=//www.freelists.org/webpage/oracle-l&k=%2FJMyfAnQZOhZ4dnr8BYv6w%3D%3D%0A&r=DUCASJ0xPpeOmtQ4idyytbAD3iZK7MOOurZhynw7YaA%3D%0A&m=lVeKIXQGo2Db7c%2FPW1%2BRhJMd4s%2BGJkjgeZ7q7OOvK8g%3D%0A&s=49b1c99e0413c30a8e00ede0fe7fe1f0b338d0ab61e97bb1101f7df049f95815> >>> >>> >>> >>> >>> -- >>> Andrew W. Kerber >>> >>> 'If at first you dont succeed, dont take up skydiving.' >>> >>> >>> >>> >>> >>> >>> -- >>> Andrew W. Kerber >>> >>> 'If at first you dont succeed, dont take up skydiving.' >>> >> >> >