ENV:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

在维护logical standby时,有时候需要手工skip掉一些transaction,如果重复的情况很多,就偷懒一下了:)

CREATE OR REPLACE

PROCEDURE sys.fnd_skip_logstdby_trans

IS

CURSOR csr

IS

SELECT xidusn, xidslt, xidsqn

FROM sys.dba_logstdby_events

WHERE event_time = (SELECT MAX (event_time)

FROM sys.dba_logstdby_events);

cursor1 INTEGER;

v_xidusn sys.dba_logstdby_events.xidusn%TYPE;

v_xidslt sys.dba_logstdby_events.xidslt%TYPE;

v_xidsqn sys.dba_logstdby_events.xidsqn%TYPE;

BEGIN

OPEN csr;

cursor1 := dbms_sql.open_cursor;

dbms_sql.parse(cursor1,'alter database stop logical standby apply',dbms_sql.v7);

LOOP

FETCH csr INTO v_xidusn,v_xidslt,v_xidsqn;

DBMS_LOGSTDBY.SKIP_TRANSACTION(v_xidusn, v_xidslt, v_xidsqn);

EXIT WHEN csr%NOTFOUND;

END LOOP;

dbms_sql.parse(cursor1,'alter database start logical standby apply immediate',dbms_sql.v7);

DBMS_OUTPUT.put_line ('Total :' || csr%ROWCOUNT);

CLOSE csr;

END;

/

SQL>exec find_skip_logstdby_trans;

记录.

ITPub博客

skip transaction on logical standby