-----------------------------
归档和REDO清理
-----------------------------
1.超过CHECKPOINT_RETENTION_TIME保留条件,可以删除的归档
DBA_REGISTERED_ARCHIVED_LOG.PURGEABLE='YES’
DBA_LOGMNR_PURGED_LOG
2.绝对不能删除的redo log,apply启动时需要
>= DBA_CAPTURE.REQUIRED_CHECKPOINT_SCN 的归档
****************
PS:10gR2后,需要用如下存过判断
Minimum Archived Log Necessary to Restart 10g and 11g Streams Capture Process [ID 290143.1]
----- Begin ckpt_scn_query_10GR2_above.sql -----
prompt ++ Minimum Archive Log Necessary to Restart Capture ++
prompt Note: This query is valid for databases where the capture processes exist for the same source database.
prompt
set serveroutput on
DECLARE
hScn number := 0;
lScn number := 0;
sScn number;
ascn number;
alog varchar2(1000);
begin
select min(start_scn), min(applied_scn) into sScn, ascn
from dba_capture;
DBMS_OUTPUT.ENABLE(2000);
for cr in (select distinct(a.ckpt_scn)
from system.logmnr_restart_ckpt$ a
where a.ckpt_scn <= ascn and a.valid = 1
and exists (select * from system.logmnr_log$ l
where a.ckpt_scn between l.first_change# and l.next_change#)
order by a.ckpt_scn desc)
loop
if (hScn = 0) then
hScn := cr.ckpt_scn;
else
lScn := cr.ckpt_scn;
exit;
end if;
end loop;
if lScn = 0 then
lScn := sScn;
end if;
dbms_output.put_line('Capture will restart from SCN ' || lScn ||' in the following file:');
for cr in (select name, first_time
from DBA_REGISTERED_ARCHIVED_LOG
where lScn between first_scn and next_scn order by thread#)
loop
dbms_output.put_line(cr.name||' ('||cr.first_time||')');
end loop;
end;
/
----- End ckpt_scn_query_10GR2_above.sql -----
****************
3.CHECKPOINT_RETENTION_TIME
默认:60天
查询:DBA_CAPTURE.CHECKPOINT_RETENTION_TIME
修改:DBMS_CAPTURE_ADM.ALTER_CAPTURE
4.local capture process
源必须开启归档
使用RMAN的backup archivelog all delete input,可以支持upstream需要的日志保留,只删除可以删除的归档
5.real-time downstream capture process
源和下流捕获端必须开启归档
6.archived-log downstream capture process
源必须开启归档
下流捕获,传递到挖掘端的主库日志,只有通过脚本删除
-----------------------------
异常处理
-----------------------------
默认遇到错误,立刻中断;通过修改Apply进程的DISABLE_ON_ERROR参数,可以遇错误继续执行
一些常见的数据一致性问题导致的Apply进程错误,可以修改:ALLOW_DUPLICATE_ROWS,COMPARE_KEY_ONLY
错误的ORA-代码,在dba_apply中查询
错误事务的信息,在dba_apply_errors中查询,使用print_transaction / print_error_id / print_errors 显示报错数据
重启apply进程后,会自动跳过发生错误的事务。
使用dbms_apply_adm.delete_error / delete_all_errors 删除错误的事务
使用dbms_apply_adm.execute_error / execute_all_errors 重新运行报错的事务
错误事务不影响CHECKPOINT的推进,也不允许日志清理
错误事务保存在STREAMS$_APPLY_SPILL_MSGS_PART中,如何清理还需要观察观察
如果Apply/Propagation进程异常没有启动,那么Capture将WAITING FOR INACTIVE DEQUEUERS,停止入队。那么logmnr已将停止,检查点推进已将停止
归档和REDO清理
-----------------------------
1.超过CHECKPOINT_RETENTION_TIME保留条件,可以删除的归档
DBA_REGISTERED_ARCHIVED_LOG.PURGEABLE='YES’
DBA_LOGMNR_PURGED_LOG
2.绝对不能删除的redo log,apply启动时需要
>= DBA_CAPTURE.REQUIRED_CHECKPOINT_SCN 的归档
****************
PS:10gR2后,需要用如下存过判断
Minimum Archived Log Necessary to Restart 10g and 11g Streams Capture Process [ID 290143.1]
----- Begin ckpt_scn_query_10GR2_above.sql -----
prompt ++ Minimum Archive Log Necessary to Restart Capture ++
prompt Note: This query is valid for databases where the capture processes exist for the same source database.
prompt
set serveroutput on
DECLARE
hScn number := 0;
lScn number := 0;
sScn number;
ascn number;
alog varchar2(1000);
begin
select min(start_scn), min(applied_scn) into sScn, ascn
from dba_capture;
DBMS_OUTPUT.ENABLE(2000);
for cr in (select distinct(a.ckpt_scn)
from system.logmnr_restart_ckpt$ a
where a.ckpt_scn <= ascn and a.valid = 1
and exists (select * from system.logmnr_log$ l
where a.ckpt_scn between l.first_change# and l.next_change#)
order by a.ckpt_scn desc)
loop
if (hScn = 0) then
hScn := cr.ckpt_scn;
else
lScn := cr.ckpt_scn;
exit;
end if;
end loop;
if lScn = 0 then
lScn := sScn;
end if;
dbms_output.put_line('Capture will restart from SCN ' || lScn ||' in the following file:');
for cr in (select name, first_time
from DBA_REGISTERED_ARCHIVED_LOG
where lScn between first_scn and next_scn order by thread#)
loop
dbms_output.put_line(cr.name||' ('||cr.first_time||')');
end loop;
end;
/
----- End ckpt_scn_query_10GR2_above.sql -----
****************
3.CHECKPOINT_RETENTION_TIME
默认:60天
查询:DBA_CAPTURE.CHECKPOINT_RETENTION_TIME
修改:DBMS_CAPTURE_ADM.ALTER_CAPTURE
4.local capture process
源必须开启归档
使用RMAN的backup archivelog all delete input,可以支持upstream需要的日志保留,只删除可以删除的归档
5.real-time downstream capture process
源和下流捕获端必须开启归档
6.archived-log downstream capture process
源必须开启归档
下流捕获,传递到挖掘端的主库日志,只有通过脚本删除
-----------------------------
异常处理
-----------------------------
默认遇到错误,立刻中断;通过修改Apply进程的DISABLE_ON_ERROR参数,可以遇错误继续执行
一些常见的数据一致性问题导致的Apply进程错误,可以修改:ALLOW_DUPLICATE_ROWS,COMPARE_KEY_ONLY
错误的ORA-代码,在dba_apply中查询
错误事务的信息,在dba_apply_errors中查询,使用print_transaction / print_error_id / print_errors 显示报错数据
重启apply进程后,会自动跳过发生错误的事务。
使用dbms_apply_adm.delete_error / delete_all_errors 删除错误的事务
使用dbms_apply_adm.execute_error / execute_all_errors 重新运行报错的事务
错误事务不影响CHECKPOINT的推进,也不允许日志清理
错误事务保存在STREAMS$_APPLY_SPILL_MSGS_PART中,如何清理还需要观察观察
如果Apply/Propagation进程异常没有启动,那么Capture将WAITING FOR INACTIVE DEQUEUERS,停止入队。那么logmnr已将停止,检查点推进已将停止
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8242091/viewspace-763046/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8242091/viewspace-763046/