Re: Automation: DG Broker

From: Neil Chandler <>
Date: Thu, 22 Aug 2019 17:32:55 +0000
Message-ID: <DB7PR10MB2090C8997B9EA9C3C4D9C27585A50_at_DB7PR10MB2090.EURPRD10.PROD.OUTLOOK.COM>


With all due respect, what has that got to do with giving incorrect advice? You were wrong, and so have changed the subject, so lets go with that.

Using Oracle on AWS costs twice as much as on-prem or Oracle Cloud per-processor (due to Oracle licensing policy). I think you'll find buying a bigger box to cope with flashback is probably somewhat cheaper than migrating to AWS. Hardware is (relatively) cheap and can be hard partitioned cheaply and easily with OVM.

NOCACHE LOB? Increase the memory on a server (which you mostly can't do on AWS without increasing the CPU count and therefore license cost) and use CACHE LOB's.

General question. Does anyone use NOCACHE LOBs, and why? The re-reads can be killer - especially if you're storing something like JSON in a BLOB.



From: Mladen Gogala <> Sent: 22 August 2019 18:18
To: Neil Chandler <>; <> Subject: Re: Automation: DG Broker

Oracle does recommend flashback, but their recommendation should be taken with a grain of salt. Flashback can be very expensive in terms of performance. Flashback works similarly to redo logs with "alter database begin backup". There are flashback logs and every committed block must be written to flashback logs in order for database to be able to flash back. You don't have any control over the size of flashback logs. And yes, the switch of flashback logs causes a checkpoint. There are cases in which the end user has to wait for that checkpoint to complete. I was hit by case of NOCACHE CLOB which was updated by application. NOCACHE means that blocks are read in PGA, process local memory, and not SGA. Since Oracle has to write all committed blocks to FRA logs, the commit will not complete until the modified LOB blocks are written to FRA logs. If the whole lot of users are doing the same thing, there is a huge probability that the user will also wait for FRA log switch checkpoint.

In my opinion, that recommendation is partly modified by desire to sell more machines and storage systems. Oracle is now a hardware vendor, too. Considering the motivation for Oracle advice is justified. The guys in Jaws needed a bigger boat. The guys who turn flashback on need a bigger box and faster storage.

My preferred solution includes AWS RDS and multiple zones. It's much cheaper than the traditional standby and fail-over and equally as reliable. Oracle too has Paas offering with redundancy included and from my experience, it performs rather well. I no longer work for Commvault so my experience can be somewhat dated.

On 8/22/19 12:40 PM, Neil Chandler wrote: Automated failover is a bad idea only if your app can't handle it, or if you have too much instability in your network, but I have implemented it successfully with a couple of clients (and argued against with a couple of others due to the first 2 issues.) It works very well. I do know of other clients (not mine) who have removed RAC and implemented DG/FSFO instead, too.

You can reinstantiate the (former) primary as a standby if you have flashback enabled. There is no need to fully recreate: it's basically flashback the primary to the SCN of failover, start recovering. There's plenty of blogs out there describing how to do this.

Oracle recommend having flashback on for all Primary and Standby databases, otherwise you will potentially need to rebuild both the primary and any tertiary standbys [DB's not the Failover Target] if you failover.



From:<> <><> on behalf of Mladen Gogala <><> Sent: 22 August 2019 16:12
To:<> <><> Subject: Re: Automation: DG Broker

Of course, using automated fail-over is a terrible ide. Any power outage will trigger a fail-over. And contrary to what people are told, minor power outages do happen. After a fail-over you have to rebuild because fail-over does resetlogs.


On 8/22/19 8:53 AM, Dba DBA wrote:
If you dont want to use oracle services or a trigger to handle this, you will need a shell script on primary/standby to poll the db to see if its primary/standby and use that to notify the server.

On Mon, Aug 19, 2019 at 4:41 AM Sourav Biswas <<>> wrote: Hi Everybody,

This is a client requirement related to Oracle Dataguard.

DG Broker is configured to perform "Failover". Which works fine and fails over to Physical Standby when the conditions are met. Now, the client wants, that once DG Broker performs "Failover" its should also notify the Application DNS Server to redirect its traffic to new Primary Database.

This is part of automation workflow, where client wants to ensure there is no manual intervention with Database Failover as well as Application switch to new Primary Database.

Please suggest.

Thanks and Regards,
Sourav Biswas

Received on Thu Aug 22 2019 - 19:32:55 CEST

Original text of this message