Re: Force users to use Active DG for reporting
- From: Gaja Krishna Vaidyanatha <gajav@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 31 Mar 2014 15:16:39 -0700 (PDT)
Hi Ricard,
If your application/users uses a web/app-tier for both transactions and reports
and if your organization has network load balancers (like F5), what you are
trying to achieve can be done. But it will take a bit of work:
1) You need to first create 2 Fully Qualified Domain Names (FQDNs) -
i.e.,https://trx.acme.com for transactions and https://rpt.acme.com for
reports.
2) All transactions from the app use the trx URL and reports use the rpt URL.
3) Create the relevant VIPs within F5, so that the traffic at the network layer
is transported to either trx(RW) or rpt(RO).
4) Configure F5 to do periodic health checks (this validates whether RW and RO
are both up and running).
5) Configure DataGuard Broker to do the switching, as relevant (i.e., if RW
goes down, RO needs to be made RW etc.)
6) If RW goes down, F5 marks the old trx VIPs as invalid and stops sending
traffic to it.
7) After a certain waiting period (could be seconds to minutes...you decide),
DG Broker kicks in and modifies RO to RW.
8) F5 now sends trx traffic to the "new RW" (old RO)
9) When the old RW is repaired and is back up, it becomes the new RO. (this can
be true if both systems are sized and configured similar)
With the above you solve the problem of workload balancing at the app (URL) and
network layers rather than depending on a user to choose the correct
tnsnames.ora entry. But if your environment does not qualify the above
conditions, then it will be difficult to enforce what you are trying to
achieve. Hope this gives you some ideas.
Cheers,
Gaja
Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC
http://www.dbperfman.com
http://www.dbcloudman.com
Phone - +1 (650) 743-6060
LinkedIn - http://www.linkedin.com/in/gajakrishnavaidyanatha
Co-author: Oracle Insights:Tales of the Oak Table -
http://www.apress.com/9781590593875
Primary Author: Oracle Performance Tuning 101 - http://www.amzn.com/0072131454
Enabling Exadata, Big Data and Cloud Deployment & Management for Oracle
________________________________
From: Ricard Martinez <ricard.martinez@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Monday, March 31, 2014 8:55 AM
Subject: Force users to use Active DG for reporting
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
Other related posts:
