Data guard broker cascaded standby delay parameter is ignored

cascadedataguardoracle-12cstandby

I have 3 databases: db01 primary, db02 standby cascading, db03 standby cascaded.

I have configured broker parameters by the following way:

DGMGRL> show configuration
Configuration – DB_HQ_DR
Protection Mode: MaxPerformance
Members:
db01 – Primary database
db02 – Physical standby database
db03- Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> edit database ‘db01′ set property RedoRoutes='(LOCAL:db02)(db02:db03)’;
Property “redoroutes” updated

DGMGRL> edit database ‘db02′ set property RedoRoutes='(LOCAL:db01)(db01:db03)’;
Property “redoroutes” updated

–Configured delaymins parameter:

DGMGRL> edit database ‘db03′ set property DelayMins=’21600’;

When selecting log_archive_dest_2 parameter on db02 I see that there is delay parameter 21600 as it should be.

SQL> show parameter log_archive_dest_2

log_archive_dest_2   |  service="db03",  delay=21600 optional compression=disable  max_failure=0 max_connections=1 reopen=300

db_unique_name="db03" net_timeout=30, valid
_for=(standby_logfile,all_roles)

DGMGRL> show configuration
Configuration – DB_HQ_DR
Protection Mode: MaxPerformance
Members:
db01 – Primary database
db02 – Physical standby database
db03- Physical standby database (receiving archived redo)
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

Problem is that apply process is not delayed. It applied as soon as there is generated archive log.
What happens? Why delay parameter is ignored?

Best Answer

If you want delay apply on Cascaded Standby you need to set the DELAY of LOG_ARCHIVE_DEST_n on Primary.

The DELAY value that a cascaded standby uses is the value that was set for the LOG_ARCHIVE_DEST_n parameter on the primary that shipped the redo to the cascading standby.

Reference:DELAY

Related Solutions

Differentiating physical standby to cascaded standby

Why is it that for non-real time apply from cascading to cascaded standby that we have to mention as SYNC as the redo transport mode?

Whether the Real-Time cascade feature is used or not, actually it depends on the attributes in the parameter LOG_ARCHIVE_DEST_n of the cascading standby that control how the redo stream is forwarded to the cascaded database. Beginning with Oracle 12c, the attributes LGWR and ARCH of the LOG_ARCHIVE_DEST_n parameters are deprecated. The only valid values are SYNC, ASYNC or none of them. In particular, for a cascading standby (thus, for a DB in Standby Role), the only valid attribute for the transport is ASYNC, meaning that synchronous transport from a standby database to another is not possible. If ASYNC is specified then Real-Time cascade is used, if no transport attribute is specified, Real-Time cascade is disabled and the redo stream from the cascading to the cascaded database is transported using the archived logs.

Ideally we mention SYNC for real time apply right?

Yes, when we are not using cascading and cascaded standby

should we mention as SYNC NOAFFIRM for non-real time apply?

Maximum Availability mode now allows the LOG_ARCHIVE_DEST_n attributes SYNC and NOAFFIRM to be used together. This enables a synchronous standby database to be deployed at a further distance from the primary site without increasing the impact on primary database performance. (In an Oracle Data Guard broker configuration, this is referred to as FASTSYNC mode.)

So, its not necessary.

Configuring Oracle Data Guard or GoldenGate Standby DB replication while restricting DELETE DMLs

You can not do this with a physical standby, you will need some kind of logical replication.

With a logical standby, you can skip DMLs, but that would skip all DMLs.

With Oracle Goldengate, you can easily skip deletes, as you can specify the IGNOREDELETES option: IGNOREDELETES

Ok, now let's assume you have configured your system to ignore deletes. Imagine the following situation:

insert into mytable (unique_column, other_column) values (1, 1);
commit;

delete from mytable where unique_column = 1;
commit;

insert into mytable (unique_column, other_column) values (1, 2);
commit;

The above will break your replication because of the unique constraint violation. If you ignore the error, you will not have fresh data in the destination database.

It sounds to me you want to build a history table, but there is more to that than just ignoring delete operations, have a look at the below support note:

Oracle GoldenGate Best Practices: Creating History Tables (Doc ID 1314698.1)

Best Answer

Related Solutions

Differentiating physical standby to cascaded standby

Configuring Oracle Data Guard or GoldenGate Standby DB replication while restricting DELETE DMLs

Related Question