合 【DG】DBMS_LOGSTDBY包的应用--逻辑DG跳过某些操作
Tags： OracleDBMS_LOGSTDBY
👉 本文共约16380个字，系统预计阅读时间或需62分钟。
目录
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | ------------------------------------- 逻辑备库 ALTER DATABASE STOP LOGICAL STANDBY APPLY; ---停止应用，等待事务完成 ALTER DATABASE ABORT LOGICAL STANDBY APPLY;--不等待事务完成就停止 ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; ---实时 ALTER DATABASE START LOGICAL STANDBY APPLY; --非实时 ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE SKIP FAILED TRANSACTION; ---是否启用实时应用 ps -ef|grep -i ora_lsp 11g：select RECOVERY_MODE from v$archive_dest_status; ---通用： col REALTIME_APPLY format a15 col STATE format a20 select * from v$Logstdby_state; ------------日志应用情况 set line 9999 pagesize 9999 col FILE_NAME format a120 select sequence#, file_name, applied, TIMESTAMP from dba_logstdby_log d order by d.sequence#; SET LINE 9999 PAGESIZE 9999 COL FILE_NAME FORMAT A120 SELECT SEQUENCE#, FILE_NAME, APPLIED, TIMESTAMP FROM DBA_LOGSTDBY_LOG D WHERE D.SEQUENCE# >=(SELECT MAX(SEQUENCE#)-3 FROM DBA_LOGSTDBY_LOG NB WHERE NB.APPLIED='YES' ) ORDER BY D.SEQUENCE#; --查看备库是否有任何DDL/DML语句未成功应用 COL EVENT_TIMESTAMP FORMAT A30 COL EVENT FORMAT A40 COL EVENT_STATUS FORMAT A80 SELECT A.EVENT_TIME, A.CURRENT_SCN, A.COMMIT_SCN, XIDUSN, XIDSLT, XIDSQN, TO_CHAR(EVENT) EVENT, A.STATUS_CODE, STATUS EVENT_STATUS FROM DBA_LOGSTDBY_EVENTS A WHERE A.EVENT_TIME >= SYSDATE - 10 / 1660 ORDER BY A.EVENT_TIME ; EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'VIEW'); EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'PROFILE'); EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DATABASE LINK'); EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'CREATE VIEW'); EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DROP VIEW'); EXECUTE DBMS_LOGSTDBY.SKIP(STMT=>'SCHEMA_DDL', SCHEMA_NAME=>'%', OBJECT_NAME=>'%', PROC_NAME=>NULL); EXECUTE DBMS_LOGSTDBY.SKIP(STMT=>'SCHEMA_DDL', SCHEMA_NAME=>'LHR', OBJECT_NAME=>'%', PROC_NAME=>NULL); EXECUTE DBMS_LOGSTDBY.SKIP(STMT=>'SCHEMA_DDL', SCHEMA_NAME=>'MDSYS', OBJECT_NAME=>'%', PROC_NAME=>NULL); EXEC DBMS_LOGSTDBY.SKIP_TRANSACTION (3, 3, 827); --(XIDUSN = 3, XIDSLT = 3, XIDSQN = 827) SELECT EVENT, STATUS,'EXEC DBMS_LOGSTDBY.SKIP_TRANSACTION ('||XIDUSN||', '||XIDSLT||', '||XIDSQN||');' FROM DBA_LOGSTDBY_EVENTS A WHERE XIDUSN IS NOT NULL AND A.EVENT_TIME >= SYSDATE - 60 / 1660; SELECT 'EXEC DBMS_LOGSTDBY.SKIP_TRANSACTION ('||XIDUSN||', '||XIDSLT||', '||XIDSQN||');' FROM DBA_LOGSTDBY_EVENTS A WHERE XIDUSN IS NOT NULL AND A.EVENT_TIME >= SYSDATE - 10 / 1660; SELECT * FROM DBA_LOGSTDBY_SKIP; SELECT * FROM DBA_LOGSTDBY_SKIP_TRANSACTION; |
模拟同步失败
备库创建唯一性索引
SQL> create unique index idx_scott_t_1 on scott.t(user_id);
Index created.
主库插入重复数据
SQL> insert into scott.t select * from scott.t;
36 rows created.
SQL> commit;
Commit complete.
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
