接前面"oracle 11g streams 传播进程使用示例"
1、查看apply的reader进程的进度信息
--其中state取值如下：
--DEQUEUE MESSAGE：应用进程正的消息正在出队
--IDLE：reader进程闲置，没有消息需要出队
--INITIALIZING：进程正在启动
--PAUSED - WAITING FOR THE DDL TO COMPLETE：应用进程正在等待应用DDL LCR
--SCHEDULE MESSAGE：在将消息组装成事务时，进程正在计算消息之间的依赖关系
--SPILLING：进程正在将没有应用的消息从内存队列溢出到磁盘队列中。当事务太长而不能保持才streams内存的缓存区时，会发生这种情况。
select sid,
serial#,
state,
total_messages_dequeued msgs_dequeued, --启动进程后出队了几条消息
elapsed_dequeue_time dequeue_time,
dequeued_message_number dequeued_scn
from v$streams_apply_reader
where apply_name = 'DBXA_APP';
SID SERIAL# STATE MSGS_DEQUEUED DEQUEUE_TIME DEQUEUED_SCN
---- ------- --------------- ------------- ------------ ------------------
331 61373 DEQUEUE MESSAGE 41741 21629 5662035596739
--其中state取值如下：
--ABORTING：协调进程由于一个应用错误而停止
--APPLYING：组装的事务正在传递给应用服务器
--IDLE：协调进程当前空闲
--INITIALIZING：进程正在启动
--SHUTTING DOWN CLEANLY：进程的一个正常关闭
select sid,
serial#,
state,
total_applied applied,
total_errors errors
from v$streams_apply_coordinator
where apply_name = 'DBXA_APP';
SID SERIAL# STATE APPLIED ERRORS
---- -------- --------------- --------- ----------
221 1825 APPLYING 4857 2
--state取值如下：
--IDLE：Performing no work
--RECORD LOW-WATERMARK：Performing an administrative job that maintains information about the apply progress, which is used in the ALL_APPLY_PROGRESS and DBA_APPLY_PROGRESS data dictionary views
--ADD PARTITION：Performing an administrative job that adds a partition that is used for recording information about in-progress transactions
--DROP PARTITION：Performing an administrative job that drops a partition that was used to record information about in-progress transactions
--EXECUTE TRANSACTION：Applying a transaction
--WAIT COMMIT：Waiting to commit a transaction until all other transactions with a lower commit SCN are applied. This state is possible only if the COMMIT_SERIALIZATION apply process parameter is set to a value other than none and the PARALLELISM apply process parameter is set to a value greater than 1.
--WAIT DEPENDENCY：Waiting to apply a logical change record (LCR) in a transaction until another transaction, on which it has a dependency, is applied. This state is possible only if the PARALLELISM apply process parameter is set to a value greater than 1.
--WAIT FOR NEXT CHUNK：Waiting for the next set of logical change records (LCRs) for a large transaction
select sid,
serial#,
server_id snbr,
state,
total_assigned txn_assigned,
total_messages_applied msgs_applied,
applied_message_number app_scn
from v$streams_apply_server
where apply_name = 'DBXA_APP';
SID SERIAL# SNBR STATE TXN_ASSIGNED MSGS_APPLIED APP_SCN
---- ------- ---- ------------------- ------------ ------------- -------------
330 43749 1 EXECUTE TRANSACTION 93524 238165 5662037203509
--自动创建 APPLY 进程
declare
l_dml_rule_name varchar2(30);
l_ddl_rule_name varchar2(30);
begin
dbms_streams_adm.add_schema_rules (
schema_name => 'SCOTT',
streams_type => 'APPLY',
streams_name => 'DBXA_APP',
queue_name => 'DBXA_APP_Q',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'DBXA.WORLD',
dml_rule_name => l_dml_rule_name,
ddl_rule_name => l_ddl_rule_name,
inclusion_rule => true,
and_condition => ' :lcr.get_compatible() <
dbms_streams.max_compatible()'
);
dbms_output.put_line('DML Rule Name is: ' || l_dml_rule_name);
dbms_output.put_line('DDL Rule Name is: ' || l_ddl_rule_name);
end;
/
DML Rule Name is: "STRMADMIN"."SCOTT66"
DDL Rule Name is: "STRMADMIN"."SCOTT67"
PL/SQL procedure successfully completed.
--查看上面自动创建 APPLY 进程相关信息
select queue_name,
rule_set_name,
apply_user,
status,
apply_captured --取值：YES/NO，是否应用从缓冲区队列中出队的捕获的LCR。
from dba_apply
where apply_name = 'DBXA_APP'
SQL> /
QUEUE_NAME RULE_SET_NAME APPLY_USER STATUS APPLY_CAPTURED
------------ --------------- --------------- -------- ---------------
DBXA_APP_Q RULESET$_68 STRMADMIN DISABLED YES
--查看上面APPLY rule set：RULESET$_68信息
select rule_name,
rule_condition
from dba_streams_rules
where rule_set_name = 'RULESET$_68';
RULE_NAME
------------------------------
RULE_CONDITION
------------------------------------------------------------------
SCOTT67
((((:ddl.get_object_owner() = 'SCOTT'
or :ddl.get_base_table_owner() = 'SCOTT')
and :ddl.is_null_tag() = 'Y'
and :ddl.get_source_database_name() = 'DBXA.WORLD' ))
and ( :ddl.get_compatible() < dbms_streams.max_compatible()))
SCOTT66
((((:dml.get_object_owner() = 'SCOTT')
and :dml.is_null_tag() = 'Y'
and :dml.get_source_database_name() = 'DBXA.WORLD' ))
and ( :dml.get_compatible() < dbms_streams.max_compatible()))
--禁用APPLy进程的规则
begin
dbms_apply_adm.alter_apply(
apply_name => 'DBXA_APP',
remove_rule_set => true
);
end;
/
PL/SQL procedure successfully completed.
--向APPLY进程中添加规则
begin
dbms_apply_adm.alter_apply(
apply_name => 'DBXA_APP',
rule_set_name => 'STRMADMIN.RULESET$_68'
);
end;
/
PL/SQL procedure successfully completed.
connect strmadmin/strmadmin@DBXB.WORLD
Connected.
begin
dbms_apply_adm.create_apply(
queue_name => 'STRMADMIN.DBXA_APP_Q',
apply_name => 'DBXA_APP',
rule_set_name => 'STRMADMIN.RULESET$_68',
apply_captured => TRUE,
apply_tag => HEXTORAW('11'),
source_database => 'DBXA.WORLD'
);
end;
/
PL/SQL procedure successfully completed.
select parameter,
value,
set_by_user
from dba_apply_parameters
where apply_name = 'DBXA_APP'
order by parameter;
PARAMETER VALUE SET
---------------------------- ---------------------- ---
ALLOW_DUPLICATE_ROWS N NO
COMMIT_SERIALIZATION DEPENDENT_TRANSACTIONS NO
DISABLE_ON_ERROR Y NO
DISABLE_ON_LIMIT N NO
MAXIMUM_SCN INFINITE NO
PARALLELISM 4 NO
PRESERVE_ENCRYPTION Y NO
RTRIM_ON_IMPLICIT_CONVERSION Y NO
STARTUP_SECONDS 0 NO
TIME_LIMIT INFINITE NO
TRACE_LEVEL 0 NO
TRANSACTION_LIMIT INFINITE NO
TXN_AGE_SPILL_THRESHOLD 900 NO
TXN_LCR_SPILL_THRESHOLD 10000 NO
WRITE_ALERT_LOG Y NO
15 rows selected.
--查看隐藏参数
SQL> select p.name parameter,
2 p.value,
3 p.user_changed_flag,
4 p.internal_flag
5 from sys.streams$_process_params p,
6 sys.streams$_apply_process a
7 where p.process# = a.apply#
8 and a.apply_name = 'DBXA_APP'
9 and p.name like '\_%' escape '\'
10 order by parameter;
PARAMETER VALUE USER_CHANGED_FLAG INTERNAL_FLAG
------------------------------ ---------- ----------------- -------------
_APPLY_SAFETY_LEVEL 1 0 1
_CMPKEY_ONLY N 0 1
_COMMIT_SERIALIZATION_PERIOD 0 0 1
_DATA_LAYER Y 0 1
_DYNAMIC_STMTS Y 0 1
_HASH_TABLE_SIZE 1000000 0 1
_IGNORE_CONSTRAINTS NO 0 1
_IGNORE_TRANSACTION 0 1
_KGL_CACHE_SIZE 100 0 1
_LCR_CACHE_PURGE_PERIOD 604800 0 1
_LCR_CACHE_PURGE_RATIO 0 0 1
_MIN_USER_AGENTS 0 0 1
_PARTITION_SIZE 10000 0 1
_RECORD_LWM_INTERVAL 1 0 1
_RESTRICT_ALL_REF_CONS Y 0 1
_SGA_SIZE 1 0 1
_TXN_BUFFER_SIZE 320 0 1
_XML_SCHEMA_USE_TABLE_OWNER Y 0 1
18 rows selected.
--触发器只在应用程序二不是应用进程引发触发时间才会触发
begin
dbms_ddl.set_trigger_firing_property(
trig_owner => 'SCOTT',
trig_name => 'EMP_TRIG',
fire_once => TRUE
);
end;
/
PL/SQL procedure successfully completed.
--只有应用进程或错误队列中重新应用事务的存储过程引发触发事件时，触发器的触发属性才设置为触发
begin
dbms_ddl.set_trigger_firing_property(
trig_owner => 'SCOTT',
trig_name => 'EMP_TRIG',
property => dbms_ddl.apply_server_only,
setting => TRUE
);
end;
/
PL/SQL procedure successfully completed.
--DBMS_DDL检查存储过程触发属性
declare
value boolean;
begin
value := dbms_ddl.is_trigger_fire_once (
trig_owner => 'SCOTT',
trig_name => 'EMP_TRIG'
);
if value
then
dbms_output.put_line('Trigger is set to fire once');
else
dbms_output.put_line('Trigger is set to fire always');
end if;
end;
/
Trigger is set to fire once
PL/SQL procedure successfully completed.
--如果仅仅是应用进程服务器引发了触发时间那么APPLY_SERVER_ONLY为YES
col apply_server_only for a20
select apply_server_only
from dba_triggers
where owner = 'SCOTT'
and trigger_name = 'EMP_TRIG';
APPLY_SERVER_ONLY
--------------------
YES
--在表中没有唯一键时，需要设置某个列来代替唯一键
begin
dbms_apply_adm.set_key_columns(
object_name => 'SCOTT.EMP',
column_list => 'ENAME, EMPNO'
);
end;
/
PL/SQL procedure successfully completed.
--当目标数据库没有定义父表和子表之间关系时，可以定义他们的关系。
begin
dbms_apply_adm.create_object_dependency(
object_name => 'SCOTT.EMP',
parent_object_name => 'SCOTT.DEPT'
);
end;
/
PL/SQL procedure successfully completed.
--以下是定义DEPT和EMP表之间的依赖关系
begin
dbms_apply_adm.set_value_dependency(
dependency_name => 'EMP_VAL_DEP',
object_name => 'SCOTT.DEPT',
attribute_list => 'DEPTNO'
);
dbms_apply_adm.set_value_dependency(
dependency_name => 'EMP_VAL_DEP',
object_name => 'SCOTT.EMP',
attribute_list => 'DEPTNO'
);
end;
/
PL/SQL procedure successfully completed.
--如果需要移除他们之间的依赖关系
begin
dbms_apply_adm.set_value_dependency(
dependency_name => 'EMP_VAL_DEP',
object_name => NULL
);
end;
/
PL/SQL procedure successfully completed.
--DML处理示例，覆盖SSNO列
create or replace procedure
mask_ssno_dml_handler (in_any IN SYS.ANYDATA)
is
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
dml_command VARCHAR2(10);
l_ssno number;
l_ssno_chg ANYDATA;
begin
-- Access the LCR.
rc := in_any.GETOBJECT(lcr);
-- Get the DML command.
dml_command := lcr.GET_COMMAND_TYPE();
-- Set the masking value for SSNO.
l_ssno := 999999999;
-- For INSERT change the NEW value for SSNO column.
if dml_command = 'INSERT' then
lcr.set_value('NEW','SSNO', ANYDATA.CONVERTNUMBER(l_ssno));
end if;
-- For DELETE, change the OLD value for SSNO column.
if dml_command = 'DELETE' then
lcr.set_value('OLD','SSNO', ANYDATA.CONVERTNUMBER(l_ssno));
end if;
-- For UPDATE, we need to check if the SSNO was changed.
-- If it was, then we must set the OLD and NEW value in the LCR
-- to the masking value.
l_ssno_chg := null;
if dml_command = 'UPDATE' then
l_ssno_chg := lcr.GET_VALUE('NEW','SSNO','N');
if l_ssno_chg is not null then
lcr.set_value('NEW','SSNO', ANYDATA.CONVERTNUMBER(l_ssno));
lcr.set_value('OLD','SSNO', ANYDATA.CONVERTNUMBER(l_ssno));
end if;
end if;
lcr.execute(true);
end;
/
Procedure created.
--为APPLY进程指定处理程序
REM Set the DML Handler for the INSERT operations
begin
dbms_apply_adm.set_dml_handler(
object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.MASK_SSNO_DML_HANDLER'
);
end;
/
PL/SQL procedure successfully completed.
REM Set the DML Handler for the UPDATE operations
begin
dbms_apply_adm.set_dml_handler(
object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.MASK_SSNO_DML_HANDLER'
);
end;
/
PL/SQL procedure successfully completed.
REM Set the DML Handler for the DELETE operations
begin
dbms_apply_adm.set_dml_handler(
object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.MASK_SSNO_DML_HANDLER'
);
end;
/
PL/SQL procedure successfully completed.
--如果要删除以上的一个APPLY处理程序，如下：
REM Drop the DML Handler for the DELETE operations
begin
dbms_apply_adm.set_dml_handler(
object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => NULL
);
end;
/
PL/SQL procedure successfully completed.
--创建一个 insert 语句DML处理程序（含有一条语句），其中替换了SSNO的值
declare
stmt clob;
begin
stmt := 'insert into scott.emp(
empno, ename,
jobname, mgr,
hiredate, sal,
comm, deptno, ssno)
values(
:new.empno, :new.ename,
:new.jobname, :new.mgr,
:new.hiredate, :new.sal,
:new.comm, :new.deptno, 999999999)';
dbms_apply_adm.add_stmt_handler(
object_name => 'SCOTT.EMP',
operation_name => 'INSERT',
handler_name => 'MODIFY_SSNO_HANDLER',
statement => stmt,
apply_name => 'DBXA_APP',
comment => 'Modifies SSNO when inserting rows into SCOTT.EMP'
);
end;
/
PL/SQL procedure successfully completed.
--创建一个 insert 语句DML处理程序（含有多条语句）
declare
stmt_1 clob;
stmt_2 clob;
begin
stmt_1 := 'insert into scott.emp(
empno, ename,
jobname, mgr,
hiredate, sal,
comm, deptno, ssno)
values(
:new.empno, :new.ename,
:new.jobname, :new.mgr,
:new.hiredate, :new.sal,
:new.comm, :new.deptno, 999999999)';
stmt_2 := 'insert into scott.emp_audit(
empno, insert_date)
values(
:new.empno, sysdate)';
-- Create a Statement Handler
dbms_streams_handler_adm.create_stmt_handler(
handler_name => 'EMP_INSERT_HANDLER',
comment => 'Statement Handler for SCOTT.EMP table'
);
-- Add the SQL statements to the Statement Handler
dbms_streams_handler_adm.add_stmt_to_handler(
handler_name => 'EMP_INSERT_HANDLER',
statement => stmt_1,
execution_sequence => 10
);
dbms_streams_handler_adm.add_stmt_to_handler(
handler_name => 'EMP_INSERT_HANDLER',
statement => stmt_2,
execution_sequence => 20
);
-- Assign the Statement Handler to the apply process
dbms_apply_adm.add_stmt_handler(
object_name => 'SCOTT.EMP',
operation_name => 'INSERT',
handler_name => 'EMP_INSERT_HANDLER',
apply_name => 'DBXA_APP'
);
end;
/
PL/SQL procedure successfully completed.
--创建DDL审计日志表
create table streams_ddl_audit
(
timestamp date,
source_database_name varchar2(60),
command_type varchar2(30),
object_owner varchar2(30),
object_name varchar2(30),
object_type varchar2(20),
ddl_text clob,
logon_user varchar2(30),
current_schema varchar2(30),
base_table_owner varchar2(30),
base_table_name varchar2(30),
streams_tag raw(10),
transaction_id varchar2(30),
scn number,
action varchar2(15)
)
/
Table created.
--创建应用存储过程
create or replace procedure ddl_handler (in_any in sys.anydata)
is
lcr sys.lcr$_ddl_record;
rc pls_integer;
l_command_type varchar2(30);
l_action varchar2(15);
l_ddl_text clob;
begin
rc := in_any.getobject(lcr);
-- Create temp lob for DDL Text
dbms_lob.createtemporary(l_ddl_text, true);
lcr.get_ddl_text(l_ddl_text);
-- Get the DDL Command Type
l_command_type := lcr.get_command_type();
-- Check DDL command type and set action for the LCR
if l_command_type in (
'TRUNCATE TABLE',
'DROP TABLE',
'DROP INDEX'
)
then
l_action := 'IGNORE';
else
l_action := 'EXECUTE';
end if;
-- Log information from the LCR in the audit table
insert into streams_ddl_audit
values ( sysdate,
lcr.get_source_database_name(),
lcr.get_command_type(),
lcr.get_object_owner(),
lcr.get_object_name(),
lcr.get_object_type(),
l_ddl_text,
lcr.get_logon_user(),
lcr.get_current_schema(),
lcr.get_base_table_owner(),
lcr.get_base_table_name(),
lcr.get_tag(),
lcr.get_transaction_id(),
lcr.get_scn(),
l_action
);
-- Execute or Ignore the DDL LCR
if l_action = 'EXECUTE'
then
lcr.execute();
end if;
-- Release the temp lob
dbms_lob.freetemporary(l_ddl_text);
end;
/
Procedure created.
--应用存储过程和DDL应用进程相关联
begin
dbms_apply_adm.alter_apply(
apply_name => 'DBXA_APP',
ddl_handler => 'STRMADMIN.DDL_HANDLER');
end;
/
PL/SQL procedure successfully completed.
--当update发生错误，将装换成delete语句
connect strmadmin/strmadmin@DBXB.WORLD
Connected.
create or replace package error_handler_pkg
as
type emsg_array is table of varchar2(2000) index by binary_integer;
procedure update_to_insert (
message IN ANYDATA,
error_stack_depth IN NUMBER,
error_numbers IN DBMS_UTILITY.NUMBER_ARRAY,
error_messages IN EMSG_ARRAY
);
end error_handler_pkg;
/
Package created.
create or replace package body error_handler_pkg
as
procedure update_to_insert (
message IN ANYDATA,
error_stack_depth IN NUMBER,
error_numbers IN DBMS_UTILITY.NUMBER_ARRAY,
error_messages IN EMSG_ARRAY
)
is
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
old_values SYS.LCR$_ROW_LIST;
new_values SYS.LCR$_ROW_LIST;
err_26787 number;
err_26786 number;
begin
-- Access the row LCR to get data.
rc := message.GETOBJECT(lcr);
err_26787 := 0;
err_26786 := 0;
-- Loop thru the error stack to mark what error occurred.
for i in 1..error_stack_depth
loop
if error_numbers(i) = 26787
then
if err_26787 = 0 then
err_26787 := 1;
end if;
end if;
if error_numbers(i) = 26786
then
if err_26786 = 0 then
err_26786 := 1;
end if;
end if;
end loop;
if err_26787 = 1
then
-- Row not found for UPDATE operation.
-- Change UPDATE to INSERT.
lcr.SET_COMMAND_TYPE('INSERT');
-- NOTE: It is assumed that all columns of the table
-- have unconditional supplemental logging defined.
-- Save existing old and new values from LCR.
old_values := lcr.GET_VALUES('old', 'y');
new_values := lcr.GET_VALUES('new', 'y');
-- Using the old values and updated new values,
-- prepare the LCR with correct new values for the
-- INSERT operation.
for i in 1..old_values.count
loop
for j in 1..new_values.count
loop
if old_values(i).column_name = new_values(j).column_name
then
old_values(i).data := new_values(j).data;
end if;
end loop;
end loop;
-- Set the new values in the LCR.
lcr.SET_VALUES('NEW', old_values);
-- Remove all old values from LCR.
lcr.SET_VALUES('OLD', null);
-- Execute modified LCR.
lcr.execute(true);
end if;
-- Row was found for UPDATE but old values did not match.
if err_26786 = 1
then
-- Re-Execute LCR to create error again.
lcr.execute(true);
end if;
end update_to_insert;
end error_handler_pkg;
/
Package body created.
-- Define the Error Handler to handle apply errors
-- when applying updates to SCOTT.DEPT table.
begin
dbms_apply_adm.set_dml_handler(
object_name => 'SCOTT.DEPT',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => TRUE,
user_procedure => 'STRMADMIN.ERROR_HANDLER_PKG.update_to_insert',
apply_name => 'DBXA_APP'
);
end;
/
PL/SQL procedure successfully completed.
47
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
