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 1000+ 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 Tom 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