DBMS_LOGSTDBY包的应用
点击(此处)折叠或打开
- ------------------------------------- 逻辑备库
- 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;
原文地址：11g 逻辑备库简单故障处理 作者：liiinuuux
模拟同步失败
备库创建唯一性索引
SQL> create unique index idx_scott_t_1 on scott.t(user_id);
Index created.
Index created.
主库插入重复数据
SQL> insert into scott.t select * from scott.t;
36 rows created.
SQL> commit;
Commit complete.
备库自动停止同步
Mon Sep 29 17:22:32 2014
LOGSTDBY: SQL Apply about to stop with ORA-1
LOGSTDBY: SQL Apply finished logging error information
LOGSTDBY Apply process AS02 server id=2 pid=41 OS id=2535 stopped
Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl/trace/orcl_as02_2535.trc:
ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated
Mon Sep 29 17:22:36 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl/trace/orcl_lsp0_2507.trc:
ORA-26808: Apply process AS02 died unexpectedly.
ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated
Mon Sep 29 17:22:36 2014
LOGSTDBY Analyzer process AS00 server id=0 pid=39 OS id=2531 stopped
Mon Sep 29 17:22:36 2014
LOGSTDBY Apply process AS01 server id=1 pid=40 OS id=2533 stopped
Mon Sep 29 17:22:36 2014
LOGSTDBY Apply process AS03 server id=3 pid=42 OS id=2537 stopped
Mon Sep 29 17:22:36 2014
LOGSTDBY Apply process AS04 server id=4 pid=43 OS id=2539 stopped
Mon Sep 29 17:22:36 2014
LOGSTDBY Apply process AS05 server id=5 pid=44 OS id=2541 stopped
Mon Sep 29 17:22:40 2014
LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=36 OS id=2518 sid=46 stopped
Mon Sep 29 17:22:40 2014
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=34 OS id=2514 sid=44 stopped
Mon Sep 29 17:22:40 2014
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=35 OS id=2516 sid=34 stopped
LOGSTDBY status: ORA-16222: automatic Logical Standby retry of last action
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
LOGMINER: SpillScn 1114702, ResetLogScn 995548
LOGMINER: summary for session# = 1
LOGMINER: StartScn: 0 (0x0000.00000000)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 1114646 (0x0000.00110216)
LOGMINER: session_flag: 0x1
LOGMINER: Read buffers: 16
Mon Sep 29 17:22:43 2014
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=34 OS id=2554 sid=34 started
Mon Sep 29 17:22:43 2014
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=35 OS id=2556 sid=44 started
Mon Sep 29 17:22:43 2014
LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=36 OS id=2558 sid=45 started
LOGMINER: Turning ON Log Auto Delete
Mon Sep 29 17:22:45 2014
LOGSTDBY Analyzer process AS00 started with server id=0 pid=40 OS id=2560
Mon Sep 29 17:22:45 2014
LOGSTDBY Apply process AS01 started with server id=1 pid=41 OS id=2562
Mon Sep 29 17:22:45 2014
LOGSTDBY Apply process AS04 started with server id=4 pid=44 OS id=2568
Mon Sep 29 17:22:45 2014
Mon Sep 29 17:22:45 2014
LOGSTDBY Apply process AS05 started with server id=5 pid=45 OS id=2570
Mon Sep 29 17:22:45 2014
LOGSTDBY Apply process AS03 started with server id=3 pid=43 OS id=2566
LOGSTDBY Apply process AS02 started with server id=2 pid=42 OS id=2564
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 42, /u01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_4_b2l8vov0_.log
LOGSTDBY: SQL Apply about to stop with ORA-1
LOGSTDBY: SQL Apply finished logging error information
LOGSTDBY Apply process AS01 server id=1 pid=41 OS id=2562 stopped
Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl/trace/orcl_lsp0_2507.trc:
ORA-26808: Apply process AS01 died unexpectedly.
ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated
LOGSTDBY Analyzer process AS00 server id=0 pid=40 OS id=2560 stopped
LOGSTDBY Apply process AS02 server id=2 pid=42 OS id=2564 stopped
LOGSTDBY Apply process AS03 server id=3 pid=43 OS id=2566 stopped
LOGSTDBY Apply process AS04 server id=4 pid=44 OS id=2568 stopped
LOGSTDBY Apply process AS05 server id=5 pid=45 OS id=2570 stopped
Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl/trace/orcl_as01_2562.trc:
ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=34 OS id=2554 sid=34 stopped
LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=36 OS id=2558 sid=45 stopped
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=35 OS id=2556 sid=44 stopped
LOGSTDBY: SQL Apply about to stop with ORA-1
LOGSTDBY: SQL Apply finished logging error information
LOGSTDBY Apply process AS02 server id=2 pid=41 OS id=2535 stopped
Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl/trace/orcl_as02_2535.trc:
ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated
Mon Sep 29 17:22:36 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl/trace/orcl_lsp0_2507.trc:
ORA-26808: Apply process AS02 died unexpectedly.
ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated
Mon Sep 29 17:22:36 2014
LOGSTDBY Analyzer process AS00 server id=0 pid=39 OS id=2531 stopped
Mon Sep 29 17:22:36 2014
LOGSTDBY Apply process AS01 server id=1 pid=40 OS id=2533 stopped
Mon Sep 29 17:22:36 2014
LOGSTDBY Apply process AS03 server id=3 pid=42 OS id=2537 stopped
Mon Sep 29 17:22:36 2014
LOGSTDBY Apply process AS04 server id=4 pid=43 OS id=2539 stopped
Mon Sep 29 17:22:36 2014
LOGSTDBY Apply process AS05 server id=5 pid=44 OS id=2541 stopped
Mon Sep 29 17:22:40 2014
LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=36 OS id=2518 sid=46 stopped
Mon Sep 29 17:22:40 2014
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=34 OS id=2514 sid=44 stopped
Mon Sep 29 17:22:40 2014
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=35 OS id=2516 sid=34 stopped
LOGSTDBY status: ORA-16222: automatic Logical Standby retry of last action
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
LOGMINER: SpillScn 1114702, ResetLogScn 995548
LOGMINER: summary for session# = 1
LOGMINER: StartScn: 0 (0x0000.00000000)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 1114646 (0x0000.00110216)
LOGMINER: session_flag: 0x1
LOGMINER: Read buffers: 16
Mon Sep 29 17:22:43 2014
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=34 OS id=2554 sid=34 started
Mon Sep 29 17:22:43 2014
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=35 OS id=2556 sid=44 started
Mon Sep 29 17:22:43 2014
LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=36 OS id=2558 sid=45 started
LOGMINER: Turning ON Log Auto Delete
Mon Sep 29 17:22:45 2014
LOGSTDBY Analyzer process AS00 started with server id=0 pid=40 OS id=2560
Mon Sep 29 17:22:45 2014
LOGSTDBY Apply process AS01 started with server id=1 pid=41 OS id=2562
Mon Sep 29 17:22:45 2014
LOGSTDBY Apply process AS04 started with server id=4 pid=44 OS id=2568
Mon Sep 29 17:22:45 2014
Mon Sep 29 17:22:45 2014
LOGSTDBY Apply process AS05 started with server id=5 pid=45 OS id=2570
Mon Sep 29 17:22:45 2014
LOGSTDBY Apply process AS03 started with server id=3 pid=43 OS id=2566
LOGSTDBY Apply process AS02 started with server id=2 pid=42 OS id=2564
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 42, /u01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_4_b2l8vov0_.log
LOGSTDBY: SQL Apply about to stop with ORA-1
LOGSTDBY: SQL Apply finished logging error information
LOGSTDBY Apply process AS01 server id=1 pid=41 OS id=2562 stopped
Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl/trace/orcl_lsp0_2507.trc:
ORA-26808: Apply process AS01 died unexpectedly.
ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated
LOGSTDBY Analyzer process AS00 server id=0 pid=40 OS id=2560 stopped
LOGSTDBY Apply process AS02 server id=2 pid=42 OS id=2564 stopped
LOGSTDBY Apply process AS03 server id=3 pid=43 OS id=2566 stopped
LOGSTDBY Apply process AS04 server id=4 pid=44 OS id=2568 stopped
LOGSTDBY Apply process AS05 server id=5 pid=45 OS id=2570 stopped
Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl/trace/orcl_as01_2562.trc:
ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=34 OS id=2554 sid=34 stopped
LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=36 OS id=2558 sid=45 stopped
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=35 OS id=2556 sid=44 stopped
trace文件/u01/app/oracle/diag/rdbms/orcl2/orcl/trace/orcl_as02_2535.trc里有这个事务相关信息
ERROR INFORMATION:
PRIMARY TXN xid: 0x0003.003.0000033b ( XIDUSN = 3, XIDSLT = 3, XIDSQN = 827)
start scn: 0x0000.0011024e (1114702) commit scn: 0x0000.00110250 (1114704)
PRIMARY TXN xid: 0x0003.003.0000033b ( XIDUSN = 3, XIDSLT = 3, XIDSQN = 827)
start scn: 0x0000.0011024e (1114702) commit scn: 0x0000.00110250 (1114704)
视图里有可读性强的错误信息
SQL> select event, status from DBA_LOGSTDBY_EVENTS;
EVENT STATUS
-------------------------------- -----------------------------------------------------------
insert into "SCOTT"."T" ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated
values
"USERNAME" = 'SYS',
"USER_ID" = 0,
"CREATED" = '17-SEP-11'
EVENT STATUS
-------------------------------- -----------------------------------------------------------
insert into "SCOTT"."T" ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated
values
"USERNAME" = 'SYS',
"USER_ID" = 0,
"CREATED" = '17-SEP-11'
。。。。。。。。。。。
根据上面的xidusn，xidslt，xidsqn跳过事务
SQL> exec dbms_logstdby.skip_transaction (
3,
3,
827);
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.
Database altered.
或者直接从视图里拼出语句
SQL> select event, status,
'exec dbms_logstdby.skip_transaction ('||XIDUSN||', '||XIDSLT||', '||XIDSQN||');' from dba_logstdby_events where XIDUSN is not null;
EVENT STATUS
-------------------------------------------------- ----------------------------------------------------------------------------------------------------
'EXECDBMS_LOGSTDBY.SKIP_TRANSACTION('||XIDUSN||','||XIDSLT||','||XIDSQN||');'
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "SCOTT"."T" ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated
values
"USERNAME" = 'SYS',
"USER_ID" = 0,
"CREATED" = '17-SEP-11'
exec dbms_logstdby.skip_transaction (3, 3, 827);
EVENT STATUS
-------------------------------------------------- ----------------------------------------------------------------------------------------------------
'EXECDBMS_LOGSTDBY.SKIP_TRANSACTION('||XIDUSN||','||XIDSLT||','||XIDSQN||');'
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "SCOTT"."T" ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated
values
"USERNAME" = 'SYS',
"USER_ID" = 0,
"CREATED" = '17-SEP-11'
exec dbms_logstdby.skip_transaction (3, 3, 827);
。。。。。。。。。。。。。
还有一个办法，就是全同步单表
1 在备库上创建到主库的dblink，要求dblink在主库那边的用户能够读表、锁表及SELECT_CATALOG_ROLE
create database link dg_orcl connect to system identified by oracle using 'dg_orcl';
2 调用存储过程
exec dbms_logstdby.instantiate_table('SCOTT', 'T', 'DG_ORCL');
86 DBMS_LOGSTDBY
