SQL Apply Filtering on Logical Standby Database

SQL Apply Filtering on Logical Standby Database

Sometimes, we may not want to reflect the operations performed on the Primary database to the Logical Standby database. It can be ensured that DML operations performed in Primary are not applied in Logical Standby Database as follows;

Step1:

Stop SQL Apply in Logical Standby

First we should stop applying sql in logical standby.

Step2:

Oracle Logical Standby Skip Schema

To skip applying DMLs performed in a specific schema to the Logical Standby

Step3:

Start SQL Apply in Logical Standby

Step4:

Make sure archives are transported to Logical Standby and Applied

Step5:

Check if DML changes in Schema are Applied to Logical Standby.

Perform a deletion on a table of the related schema in Primary and check if it has been deleted from Logical Standby.

Step6:

Check a table without DML Filtering

Query the data of a table without DML Filtering from Primary and Logical Standby.

Step7:

Check again by deleting from Primary

Step8:

Error and Transaction Filtering in Logical Standby

Apart from DML, Filtering can also be applied for errors and transaction specifics.

XIDUSN: Specifies the Undo Segment Number of Transaction.
XIDSLT: Specifies the Slot Number of Transaction.
XIDSQN: Specifies the Sequence Number of Transaction.

Step9:

Check the existing SQL Apply Filtering

Note: Note that there are also INTERNAL SCHEMAs above. We have already mentioned that INTERNAL SCHEMAs will not be transferred.

Step10:

Delete SQL Apply Filtering

Stop SQL Apply.

Delete SQL Apply Filter.

Start SQL Apply.

Query the data of the table of the deleted filter from both sides.

Delete data from the table and check it from the Logical Standby side.

Leave a Reply