The keyword being “temporary.”
Now, prior to 12.?not_sure?, you could stand up a different database, enable
“active” dataguard on the actual standby so that you can read from it, and use
the extra database on the standby machine as the query engine, including using
gtt and any other temporary aggregates you want to make understanding they will
never flow back to the production database. The “between databases” network
overhead on the same physical machine can be dramatically less than the between
databases overhead between databases on different machines.
The strategy for doing this from ancient history was to pause recovery on the
standby, clone the whole thing, start up the clone with the new name, and then
resume recovery (still being the same thread, ie. same database, and still a
recovery database of the original). The clone is then frozen in that point in
time until the next time you clone it, rather than being dynamically updated. A
number of my clients found that useful and the most common method was to do the
clone once per night (after the generate receivables process was complete and
flushed to the standby), and sometimes an additional copy at month end. The
accounting folks tended to like it that things were NOT dynamically changing
under them and the flash sales folks enjoyed their jobs NOT competing with the
yesterday, last week, and last month reports on the DSS machine.
Other folks prefer the data up to the minute for all their queries, but I have
never successfully gotten an answer to what their goal was considering the data
should not be changing for most of the reports they were running and that there
was plenty of horsepower on the primary machine for any flash reports and all
the transactions.
Since you’re only talking about temp DML servicing read queries, a lot of
things open up for you prior to 12.x, and I think the thread already has the
gtt temp as a by-product of queries and destination “reflection” to the primary
stuff in later revisions. I believe the intent of that is so that you can run
things like the concurrent manager on the standby marking little things like
report complete on the job schedule and results tracking tables back on the
primary.
Finally, if you’re building this in to use idle capacity on the standby,
remember that if you need to do a production switchover for even preventive
maintenance and planned on the primary, you may need to have a job triage plan,
because your actual horsepower no longer includes some things being done on the
standby machine. This can be a big friggin’ deal, so be careful.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Pap
Sent: Thursday, February 04, 2021 3:32 PM
To: Mikhail Velikikh
Cc: andrew.kerber@xxxxxxxxx; Oracle L
Subject: Re: question on data guard
I saw a few blogs stating the DML on standby possible on the global temporary
table on a physical standby if temp_undo_enabled set to TRUE and it's on 12.1.
Not seeing any such in doc though for 11.2., but is it also possible on
11.2.0.4 to perform DML/INSERT into a global temporary table on DR or its
strictly in 12.1+ versions?
On Thu, Feb 4, 2021 at 7:28 PM Mikhail Velikikh <mvelikikh@xxxxxxxxx> wrote:
Oracle introduced Active Data Guard DML Redirection in 19c:
https://docs.oracle.com/en/database/oracle/oracle-database/19/newft/new-features.html#GUID-E2F2E0EE-D58B-4F9C-A7F8-301D9AEA34D8
Take a look if it fits the bill.
I could even work in 18c per Active Data Guard DML Redirection 19c (Doc ID
2465016.1)
On Thu, 4 Feb 2021 at 13:41, Andrew Kerber <andrew.kerber@xxxxxxxxx> wrote:
If you want to run updates on the standby, it is not longer a standby. The only
way to do this is with Golden Gate or similar technology (shareplex).
On Thu, Feb 4, 2021 at 12:20 AM Pap <oracle.developer35@xxxxxxxxx> wrote:
We are using data guard configuration and it's a physical standby for disaster
recovery. The version is 11.2.0.4 of Oracle. My questions, we do use DR for
querying many times, but is there any way we can use/open physical standby for
DML also and so that both primary and DR will be in sync bi-directionally and
we can utilize the DR/Standby database resources for our application queries?
Thanks and Regards
Pap
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
