RE: Force users to use Active DG for reporting
Date: Tue, 1 Apr 2014 12:05:35 -0400
Message-ID: <04e301cf4dc4$3887b3e0$a9971ba0$_at_gmail.com>
I may have missed something in all the emails, but I'm going to ask.
Who is connecting? How are they connecting? Is the application using ODBC to connect to the RW side and then the users are using a tool like SQL Developer to connect via ODBC to RO? Is the ODBC connection using the correct naming for the DBs names? It just sounds like it is to wide open and there should be some sort of logic for directing who to where.
Like I said, I may have missed something in previous emails, if these questions have been answered, please ignore.
Thanks
Bobby
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Ricard Martinez
Sent: Tuesday, April 01, 2014 11:50 AM
To: Jay.Miller_at_tdameritrade.com
Cc: oracle-l_at_freelists.org
Subject: Re: Force users to use Active DG for reporting
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 <mailto: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
<mailto:andrew.kerber_at_gmail.com> ]
Sent: Tuesday, April 01, 2014 11:02 AM
To: Miller, Jay
Cc: ricard.martinez_at_gmail.com <mailto: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 <mailto: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 <tel:201.369.8355>
From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org>
[mailto: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 <mailto:keyantech_at_gmail.com> ;
oracle-l_at_freelists.org <mailto: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 <mailto: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 <mailto: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
<mailto: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=DUCASJ0xPpeOmtQ4idyytbAD3iZK 7MOOurZhynw7YaA%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 Tue Apr 01 2014 - 18:05:35 CEST