Skip to Main Content
  • Questions
  • How to skip delete operations with data guard ?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Dirk.

Asked: August 25, 2004 - 9:04 am UTC

Last updated: August 25, 2004 - 9:04 am UTC

Version: 9.2.0.4

Viewed 100+ times

You Asked

Hi Tom,

we are using data guard's feature logical standby database to get a backup/archiving/reporting system of our primary database.
Primary site holds data from the last year, standby from the past 5+ years.
Now we`d like to delete some rows in some tables periodically (e.g. every month) in our primary database but standby should preserve them.
Now, how to filter just these rows (identified by specific keys)?

The "normal" skip operation
DBMS_LOGSTDBY.SKIP('DML', 'FDBDATA', 'VEHICLE_DELETE',null);
works fine but filters inserts/updates too.
I tried
DBMS_LOGSTDBY.SKIP('DELETE','FDBDATA',VEHICLE_DELETE','FDBDATA.PROTECT_DELETE');
with a procedure like this
CREATE OR REPLACE PROCEDURE PROTECT_DELETE (statement IN VARCHAR2,
statement_type IN VARCHAR2,
schema IN VARCHAR2,
name IN VARCHAR2,
xidusn IN NUMBER,
xidslt IN NUMBER,
xidsqn IN NUMBER,
skip_action OUT NUMBER,
new_statement OUT VARCHAR2) AS
BEGIN
-- Init
new_statement := NULL;


IF upper(statement) LIKE '%DELETE%'
THEN
-- Skip the statement
skip_action := DBMS_LOGSTDBY.SKIP_ACTION_SKIP;

ELSE
-- Apply the statement
skip_action := DBMS_LOGSTDBY.SKIP_ACTION_APPLY;

END IF;
END protect_delete;
it compiled, but it didn`t work.

How to do this??

Regards, Dirk



and we said...

'delete' is not valid input to skip.

the entire goal of the logical standby is "100% syncronization".

for what you want to do, the appropriate approach is going to be streams itself. There you can decide to apply only inserts and updates. It will not be a standby, but that is OK cause what you are asking for is *not* a standby at all.

so, you'll not be using data guard, you would be using streams


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library