Oracle 灾备 -- DG逻辑跳过事务

发现应用进程进程已停(APPLIED_TIME为空):

16:07:26 SYS@WDSTD.LK SQL> select LATEST_SCN,MINING_SCN,APPLIED_SCN,LATEST_TIME,MINING_TIME,APPLIED_TIME from V$LOGSTDBY_PROGRESS;
LATEST_SCN MINING_SCN APPLIED_SCN LATEST_TIME MINING_TIME APPLIED_TIME

8960978329 8955048386 2010-05-20 16:07:48

查询导致应用进程停止的DDL/DML语句:

16:09:04 SYS@WDSTD.LK SQL> col EVENT_TIMESTAMP format a30
16:10:35 SYS@WDSTD.LK SQL> col EVENT format a60
16:10:35 SYS@WDSTD.LK SQL> col EVENT_STATUS format a85
16:10:35 SYS@WDSTD.LK SQL> SELECT EVENT_TIMESTAMP, xidusn, xidslt, xidsqn, EVENT, STATUS event_status FROM DBA_LOGSTDBY_EVENTS WHERE event_time = (SELECT MAX (event_time) FROM dba_logstdby_events);
EVENT_TIMESTAMP XIDUSN XIDSLT XIDSQN EVENT EVENT_STATUS

20-MAY-10 04.05.06.508699 PM 26 14 152015 grant sysdba to u_test ORA-01031: insufficient privileges

原来是一条grant语句, 现在先把该事务跳过, 让sql apply继续前进:

16:11:01 SYS@WDSTD.LK SQL> alter database stop logical standby apply;

Database altered.

16:11:05 SYS@WDSTD.LK SQL> exec dbms_logstdby.skip_transaction(2,40,293);

PL/SQL procedure successfully completed.

16:12:03 SYS@WDSTD.LK SQL> alter database start logical standby apply immediate;

Database altered.

查询当前都有哪些事务被跳过了:

16:12:24 SQL> select * from DBA_LOGSTDBY_SKIP_TRANSACTION;
XIDUSN XIDSLT XIDSQN

26 14 152015

再次查询应用进程执行情况, 发现APPLIED_TIME又开始变化了:

16:12:43 SYS@WDSTD.LK SQL> select LATEST_SCN,MINING_SCN,APPLIED_SCN,LATEST_TIME,MINING_TIME,APPLIED_TIME from V$LOGSTDBY_PROGRESS;
LATEST_SCN MINING_SCN APPLIED_SCN LATEST_TIME MINING_TIME APPLIED_TIME

8961069092 8955228937 8955187511 2010-05-20 16:13:12 2010-05-20 11:58:00 2010-05-20 11:55:57
16:13:17 SYS@WDSTD.LK SQL> select LATEST_SCN,MINING_SCN,APPLIED_SCN,LATEST_TIME,MINING_TIME,APPLIED_TIME from V$LOGSTDBY_PROGRESS;
LATEST_SCN MINING_SCN APPLIED_SCN LATEST_TIME MINING_TIME APPLIED_TIME

8961069092 8955245411 8955231545 2010-05-20 16:13:12 2010-05-20 11:59:00 2010-05-20 11:58:09

EXECUTE DBMS_LOGSTDBY.UNSKIP(stmt=>'CREATE TABLESPACE', schema_name=>'SYS', object_name=>'%');
EXECUTE DBMS_LOGSTDBY.UNSKIP(stmt=>'DROP TABLESPACE', schema_name=>'SYS', object_name=>'%');
EXECUTE DBMS_LOGSTDBY.UNSKIP(stmt=>'ALTER TABLESPACE', schema_name=>'SYS', object_name=>'%');

在logical standby:
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(stmt=>'CREATE TABLESPACE', schema_name=>'SYS', object_name=>'%');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGSTDBY.SKIP(stmt=>'DROP TABLESPACE', schema_name=>'SYS', object_name=>'%');

PL/SQL procedure successfully completed.

EXECUTE DBMS_LOGSTDBY.SKIP(stmt=>'ALTER TABLESPACE', schema_name=>'SYS', object_name=>'%');