RE: question on data guard

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Pap Sent: Thursday, February 04, 2021 3:32 PM To: Mikhail Velikikh
Cc: andrew.kerber_at_gmail.com; 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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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.'





--
http://www.freelists.org/webpage/oracle-l