1、异常监控命令
--查找同步异常原因 --
select * from dba_logstdby_events order by event_time desc;
说明:如果status报错,event列有详细的触发操作语句,常见的多数是主库有新的DDL语句
--确认同步是否正常
SELECT * FROM v$logstdby_process;
查看日志是否都已经同步完成
select * from v$logstdby_stats;
select * from v$Logstdby_progress;
--备库
select sequence#,applied from dba_logstdby_log order by sequence# desc ;
同步进程不存在,主库数据没有同步到备库
2、特殊dml操作
--进行DML操作前
alter session disable guard;
--操作过后
alter session enable guard;
3、异常处理
说明:查看报错信息和导致报错的事件,进行跳过处理
--停止数据库日志应用
alter database stop logical standby apply;
--跳过DML操作
begin
DBMS_LOGSTDBY.SKIP('DML','JISEN','TMP_SAME_FILE'); --注意,表名要大写哦,否则还会报错的
end;
--跳过DDL
begin
DBMS_LOGSTDBY.SKIP('SCHEMA_DDL','JISEN','TMP_1024');--注意,表名要大写哦,否则还会报错的
end;
--跳过ANALYZE TABLE操作
begin
DBMS_LOGSTDBY.SKIP('ANALYZE TABLE','%','%',NULL);
end;
--跳过ANALYZE INDEX操作
begin
DBMS_LOGSTDBY.SKIP('ANALYZE INDEX','%','%', NULL);
end;
--启动应用日志
alter database start logical standby apply immediate;
2> 同步进程存在,其它表都能同步,个别表无法同步
这种情况,很可能是设置了表级别的对像跳过,可以通过以查查询确认是否手动设置跳过同步
select * from DBA_LOGSTDBY_SKIP;
然后通过DBMS_LOGSTDBY.UNSKIP()重新同步
处理表空间转位
create or replace procedure sys.handle_tbs_ddl
(
old_stmt in varchar2,
stmt_typ in varchar2,
schema in varchar2,
name in varchar2,
xidusn in number,
xidslt in number,
xidsqn in number,
action out number,
new_stmt out varchar2
) as
begin
new_stmt := replace(old_stmt,
'+DATA01',
'+DATA');
action := dbms_logstdby.skip_action_replace;
exception
when others then
action := dbms_logstdby.skip_action_error;
new_stmt := null;
end;
/
--------------------------------------------------------------------
-- LSP has to be stopped before we can do the next step
--------------------------------------------------------------------
alter database stop logical standby apply;
begin
dbms_logstdby.skip (stmt=>'tablespace',proc_name=>'sys.handle_tbs_ddl');
end;
/
alter database start logical standby apply immediate;
4、异常处理 2
--========================================================================
--查询当前正在处理的HANG住的事务
SELECT xidusn, xidslt, xidsqn,COUNT(*) from system.logmnr_spill$ group by xidusn, xidslt, xidsqn;
--跳过当前正在处理的事务
begin
dbms_logstdby.skip_transaction(55,14,139400);
end;
--跳过其它类型对像
begin
DBMS_LOGSTDBY.SKIP('ANALYZE TABLE','%','%',NULL);
end;
begin
exec DBMS_LOGSTDBY.SKIP('ANALYZE INDEX','%','%', NULL);
end;
--PAGEING OUT
begin
DBMS_LOGSTDBY.APPLY_SET('MAX_SGA',4095);
end;
--跳过事务
select event_time,
xidusn,
xidslt,
xidsqn,
status,
status_code,
event,
'exec dbms_logstdby.skip_transaction(' || xidusn || ',' || xidslt || ',' ||
xidsqn || ');' as exec_sql,
a.*
from dba_logstdby_events a
where event_time = (select max(event_time) from dba_logstdby_events where current_scn='13584767423428');
----------------------------------------------------------
begin
DBMS_LOGSTDBY.SKIP('ALTER SYNONYM','%','%',NULL);
end;
--启动应用日志
alter database start logical standby apply immediate;
--主库
select max(sequence#) from v$archived_log;
--空间应用进程个数
SELECT COUNT(*)AS IDLE_APPLIER FROM V$LOGSTDBY_PROCESS WHERE TYPE='APPLIER' and status_code=16166;
--如果ready-applied的值比applier进程数的两倍还要大,则说明你有必要考虑增加applier进程的数目了,反之如果applied与ready的值差不多大,或者其差比applier进程数还小,则说明applier进程数偏多,你有必要考虑适当减小进程的数目
select name,value from v$logstdby_stats where name like'transaction%';