RE: Force users to use Active DG for reporting
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <kibeha@xxxxxxxxx>, <ricard.martinez@xxxxxxxxx>
- Date: Wed, 2 Apr 2014 10:01:54 -0400
If the reports are bundled as PL/SQL or some such prefix can be injected at
the beginning of scripts, then you can check the current instance name
IF the current instance name is RW, THEN if the value in some single row
table is NOT the value that says reports on RW are ok (which you would set
when RO is unavailable), then the logic of the report produces: You ran xyz
report on the RW instance of database SPLAT on <date>. To get the desired
output please run this report on RO <further instructions>.
OR if it is within your technical tool set fork the report spec to the RO
for them. (This is the much more politically correct solution. If it changes
the spooled report pickup location, a nice thing to do would be inform them
of the url or network file address of the spooled report. This does require
maintenance if someone injects some DML into something that was formerly
just selects, but that should run through change control, right?)
IF the current instance name is RO, just run it.
IF the current instance name is RW, but your lookup value says RO is not
available, just run it.
Something like that. Noticing that the logic is not reversible for a switch
over, so if you are planning to run normally during a switchover you'll need
a slightly more complex logic (but it is still tenable).
mwf
+1 on users tending to do the easiest thing at the time.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Kim Berg Hansen
Sent: Wednesday, April 02, 2014 3:54 AM
To: ricard.martinez@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Force users to use Active DG for reporting
So basically your users are normally lazy like most people :-)
Rather than having two connections and switching back and forth, they prefer
just having their RW connection and can't see "what's in it for me to use
RO, that's just more trouble and not worth it to me - I'll just keep doing
what I always have done, that's easier." Perfectly normal human reaction.
So education - demonstrate that there are more free CPU cycles available on
RO and their reporting will go twice as fast (if it does :-)
Make it a game - perhaps you can create some monitoring that shows you
resource consumption on RW and RO instances by client IP-address or machine
name?
Then you can give a prize to the user that has highest percentage of RO
resource consumption compared with RW consumption in a given week?
Or maybe rather than individually, give a bonus to the group with the best
RO / RW percentage, so peer pressure within the group will kick in?
(Well, maybe have to filter the results a bit to avoid users just firing a
lot of reporting on the RO without really needing it ;-)
Until you can change the schema and separate reporting app from the rest, I
think your best bet is the carrot rather than the stick ;-)
Use psychological "tricks" to make users want to do it right and use RO for
reporting.
You cannot often rely on the user thinking of "the best for the company" -
humans nature will almost always think of "the best for me..."
Regards
Kim Berg Hansen
http://dspsd.blogspot.com
kibeha@xxxxxxxxx
@kibeha
On Tue, Apr 1, 2014 at 5:49 PM, 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!
Other related posts:
