Re: Force users to use Active DG for reporting
Date: Wed, 2 Apr 2014 08:10:03 +0100
Message-ID: <CAFGV9unv6=puYeja-VC4J5J9sJTrV66fWF9FMDgC9CUbgUxhAQ_at_mail.gmail.com>
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_at_gmail.com> 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_at_gmail.com> 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_at_tdameritrade.com> 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_at_gmail.com]
>>> *Sent:* Tuesday, April 01, 2014 11:02 AM
>>> *To:* Miller, Jay
>>> *Cc:* ricard.martinez_at_gmail.com; 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_at_tdameritrade.com> 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_at_freelists.org [mailto:
>>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Ricard Martinez
>>> *Sent:* Tuesday, April 01, 2014 4:47 AM
>>> *To:* Andrew Kerber
>>> *Cc:* keyantech_at_gmail.com; oracle-l_at_freelists.org
>>> *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_at_gmail.com>
>>> 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_at_gmail.com>
>>> 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_at_gmail.com> 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
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>>
>>> --
>>> http://www.freelists.org/webpage/oracle-l<https://urldefense.proofpoint.com/v1/url?u=http://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.'
>>>
>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 02 2014 - 09:10:03 CEST