接前面"oracle 11g streams rule和rule set 使用示例"
1、列出带有数据字典信息的 archive log 的 first scn
set numwidth 18
set lines 132
select first_change#,
name
from v$archived_log
where dictionary_begin = 'YES';
-- 如果没有结果 则需要提取数据字典并获得相应的 scn
set serveroutput on
declare
scn number;
begin
dbms_capture_adm.build(first_scn => scn);
dbms_output.put_line('First SCN = ' || scn);
end;
/--如果捕获进程存在就只是添加rule
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 => 'CAPTURE',
streams_name => 'DBXA_CAP',
queue_name => 'DBXA_CAP_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"."SCOTT131"
DDL Rule Name is: "STRMADMIN"."SCOTT132"
PL/SQL procedure successfully completed.
--查看创建capture进程的信息（可以看到capture进程和rule set：RULESET$_133自动关联）
select queue_name,
rule_set_name,
first_scn,
start_scn,
status,
capture_type type
from dba_capture
where capture_name = 'DBXA_CAP';
QUEUE_NAME RULE_SET_NAME FIRST_SCN START_SCN STATUS TYPE
------------- -------------- --------- ---------- -------- ------
DBXA_CAP_Q RULESET$_133 2323481 2323481 DISABLED LOCAL
--查看rule set：RULESET$_133中的DML和DDL的rule
select rule_name,
rule_condition
from dba_streams_rules
where rule_set_name = 'RULESET$_133';
RULE_NAME
------------------------------
RULE_CONDITION
-----------------------------------------------------------------------
SCOTT132
((((: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()))
SCOTT131
((((: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()))
3、手动创建capture进程(p85详细介绍——oracle 11g streams)
begin
dbms_capture_adm.create_capture(
queue_name => 'DBXA_CAP_Q',
capture_name => 'DBXA_CAP',
rule_set_name => 'RULESET$_250',
start_scn => 2333429, --可以和first_scn一样(两个值越近越好在 1 中有介绍查看first_scn值)
source_database => 'DBXA.WORLD',
first_scn => 2333429, --要<=start_scn
checkpoint_retention_time => .50 --代表保留12个小时
);
end;
/
PL/SQL procedure successfully completed.--指定use_database_link => ture 的方法
connect strmadmin/strmadmin@DBXB.WORLD
Connected.
-- Create downstream capture process
begin
dbms_capture_adm.create_capture (
capture_name => 'DBXA_CAP',
queue_name => 'DBXA_APP_Q',
use_database_link => true, --指定是down stream capture
source_database => 'DBXA.WORLD',
checkpoint_retention_time => 2
);
end;
/
PL/SQL procedure successfully completed.
--指定use_database_link => null的方法
-- Acquire Data Dictionary build SCN from source database
connect strmadmin/strmadmin@DBXA.WORLD
Connected.
set serveroutput on
declare
scn number;
begin
dbms_capture_adm.build(first_scn => scn);
dbms_output.put_line('First SCN Value = ' || scn);
end;
/
First SCN Value = 13722837
PL/SQL procedure successfully completed.
-- Connect to Downstream Database
connect strmadmin/strmadmin@DBXB.WORLD
Connected.
-- Create downstream capture process.
-- Enter the First SCN obtained in the previous step.
begin
dbms_capture_adm.create_capture (
capture_name => 'DBXA_CAP',
queue_name => 'DBXA_CAP_Q',
use_database_link => NULL,
source_database => 'DBXA.WORLD',
first_scn => &First_SCN_Value,
checkpoint_retention_time => 2
);
end;
/
Enter value for first_scn_value: 13722837
old 7: first_scn => &First_SCN_Value,
new 7: first_scn => 13722837,
PL/SQL procedure successfully completed.--这里假设streams_name => 'DBXA_S_CAP'已经创建过
conn strmadmin/strmadmin@DBXA.WORLD
Connected.
begin
dbms_streams_adm.add_table_rules (
table_name => 'SCOTT.DEPT',
streams_type => 'SYNC_CAPTURE', --指定是同步捕获进程
streams_name => 'DBXA_S_CAP',
queue_name => 'DBXA_S_CAP_Q',
include_dml => true,
inclusion_rule => true,
source_database => 'DBXA.WORLD'
);
end;
/
PL/SQL procedure successfully completed.
--查看同步进程信息
select capture_name,
queue_name,
queue_owner,
rule_set_name,
rule_set_owner,
capture_user
from dba_sync_capture;
CAPTURE_NAME QUEUE_NAME QUEUE_OWNER RULE_SET_NAME RULE_SET_OWNER CAPTURE_USER
------------- ------------- ----------- ------------- -------------- ------------
DBXA_S_CAP DBXA_S_CAP_Q STRMADMIN RULESET$_135 STRMADMIN STRMADMIN
--查看同步进程允许捕获的表
select table_owner,
table_name,
enabled
from dba_sync_capture_tables;
TABLE_OWNER TABLE_NAME ENABLED
------------------------------ ------------------------------ ----------
SCOTT DEPT YES
--手动创建同步capture进程
begin
dbms_capture_adm.create_sync_capture(
capture_name => 'DBXA_S_CAP',
queue_name => 'DBXA_S_CAP_Q',
rule_set_name => 'RULESET$_146' --需要指定rule set
);
end;
/
PL/SQL procedure successfully completed.select parameter,
value,
set_by_user --如果为YES代表修改过1次以上
from dba_capture_parameters
where capture_name = 'DBXA_CAP'
order by parameter;
PARAMETER VALUE SET_BY_USER
------------------------------ ---------- ---------------
DISABLE_ON_LIMIT N NO
DOWNSTREAM_REAL_TIME_MINE Y NO
MAXIMUM_SCN INFINITE NO
MERGE_THRESHOLD 60 NO
MESSAGE_LIMIT INFINITE NO
MESSAGE_TRACKING_FREQUENCY 2000000 NO
PARALLELISM 1 NO
SKIP_AUTOFILTERED_TABLE_DDL Y NO
SPLIT_THRESHOLD 1800 NO
STARTUP_SECONDS 0 NO
TIME_LIMIT INFINITE NO
TRACE_LEVEL 0 NO
WRITE_ALERT_LOG Y NO
13 rows selected.
--查看捕获进程隐藏参数（最好不要修改）
select p.name parameter,
p.value,
p.user_changed_flag,
p.internal_flag
from sys.streams$_process_params p,
sys.streams$_capture_process c
where p.process# = c.capture#
and c.capture_name = 'DBXA_CAP'
and p.name like '\_%' escape '\'
order by parameter;
PARAMETER VALUE USER_CHANGED_FLAG INTERNAL_FLAG
------------------------------ ---------- ----------------- -------------
_ACK_INTERVAL 5 0 1
_APPLY_BUFFER_ENTRIES 10000 0 1
_APPLY_UNRESPONSIVE_SECS 300 0 1
_CHECKPOINTS_PER_DAY 4 0 1
_CHECKPOINT_FORCE N 0 1
_CHECKPOINT_FREQUENCY 1000 0 1
_CKPT_FORCE_FREQ 1800 0 1
_CKPT_RETENTION_CHECK_FREQ 21600 0 1
_DIRECT_APPLY AUTO 0 1
_DISABLE_PGAHC N 0 1
_EXPOSE_UNSUPPORTED NO 0 1
_FILTER_PARTIAL_ROLLBACK AUTO 0 1
_FLUSH_TIMEOUT 2 0 1
_IGNORE_TRANSACTION 0 1
_IGNORE_UNSUPERR_TABLE 0 1
_LCR_CACHE_PURGE_PERIOD 604800 0 1
_LCR_CACHE_PURGE_RATIO 0 0 1
_LOGMINER_IDLE_READ_POLL_FREQ 0 0 1
_MIN_APPLY_BUFFER_ENTRIES 1000 0 1
_MIN_DAYS_KEEP_ALL_CKPTS 1 0 1
_SEND_STREAMS_DICTIONARY 0 0 1
_SGA_SIZE 10 0 1
_SKIP_LCR_FOR_ASSERT 0 1
_TURN_OFF_LIMIT_READ N 0 1
24 rows selected.
--当出现ora-1341或ora-1280是要考虑增加 _SGA_SIZE Logminer内存总大小：_SGA_SIZE * PARALLELISM
7、修改捕获进程参数（同步捕获进程不允许修改参数）
begin
dbms_capture_adm.set_parameter(
capture_name => 'DBXA_CAP',
parameter => 'PARALLELISM',
value => '2'
);
end;
/
PL/SQL procedure successfully completed.
-- Querying the DBA_CAPTURE_PARAMETERS view will show:
PARAMETER VALUE SET_BY_USER
------------------------------ ---------- ---------------
PARALLELISM 2 YES--显示不支持对象的原因
select owner, table_name, reason, auto_filtered
from dba_streams_unsupported
where owner = 'SCOTT';
OWNER TABLE_NAME REASON AUT
------- ------------------- ---------------------------- ---
SCOTT EMP_ROWID unsupported column exists NO
--查看不支持的列和同步不或进程、应用进程的版本
select table_name,
column_name "COL_NAME",
sync_capture_version "S_CAP_VER",
sync_capture_reason "S_CAP_REASON",
apply_version "APP_VER",
apply_reason "APP_REASON"
from dba_streams_columns
where owner = 'SCOTT'
and table_name in ('DEPT','EMP_ROWID')
order by table_name, column_name;
TABLE_NAME COL_NAME S_CAP_VER S_CAP_REASON APP_VER APP_REASON
------------ ---------- ---------- --------------- ---------- ----------
DEPT DEPTNO 11.1 9.2
DEPT DNAME 11.1 9.2
DEPT LOC 11.1 9.2
EMP_ROWID EMPNO 11.1 9.2
EMP_ROWID ROW_ID rowid column rowid column--打开数据库或表空间为 force logging
alter database force logging;
Database altered.
alter tablespace data_1 force logging;
Tablespace altered.
--打开表 force logging
col logging for a10
alter table scott.dept logging;
Table altered.
select logging
from dba_tables
where owner = 'SCOTT'
and table_name = 'DEPT';
LOGGING
----------
YES--查看 supplemental log 配置
select supplemental_log_data_min "MIN",
supplemental_log_data_pk "PK",
supplemental_log_data_ui "UI",
supplemental_log_data_fk "FK",
supplemental_log_data_all "ALL"
from v$database;
MIN PK UI FK ALL
-------- --- --- --- ---
YES NO NO NO NO
--开启各种 supplemental log 方法
alter database add supplemental log data (primary key) columns;
Database altered.
alter database add supplemental log data (foreign key) columns;
Database altered.
alter database add supplemental log data (unique index) columns;
Database altered.
alter database add supplemental log data (all) columns;
Database altered.
select supplemental_log_data_min "MIN",
supplemental_log_data_pk "PK",
supplemental_log_data_ui "UI",
supplemental_log_data_fk "FK",
supplemental_log_data_all "ALL"
from v$database;
MIN PK UI FK ALL
-------- --- --- --- ---
YES YES YES YES YES
--也能用以下方法开启 supplemental log
alter database add supplemental log data(primary key, unique index, foreign key) columns;
Database altered.--创建有条件supplemental log group
alter table scott.emp add supplemental log data(unique, foreign key) columns;
Table altered.
select table_name,
log_group_name,
log_group_type,
always,
generated
from dba_log_groups
where owner='SCOTT';
TABLE_NAME LOG_GROUP_NAME LOG_GROUP_TYPE ALWAYS GENERATED
----------- --------------- ------------------- ----------- --------------
EMP SYS_C006447 UNIQUE KEY LOGGING CONDITIONAL GENERATED NAME
EMP SYS_C006448 FOREIGN KEY LOGGING CONDITIONAL GENERATED NAME
--为某列创建有条件 supplemental log group
alter table scott.emp add supplemental log group slg_emp (empno, sal);
Table altered.
TABLE_NAME LOG_GROUP_NAME LOG_GROUP_TYPE ALWAYS GENERATED
------------ --------------- ------------------- ----------- --------------
EMP SLG_EMP USER LOG GROUP CONDITIONAL USER NAME
--创建无条件supplemental log
alter table scott.emp add supplemental log data (primary key) columns;
Table altered.
TABLE_NAME LOG_GROUP_NAME LOG_GROUP_TYPE ALWAYS GENERATED
------------ --------------- ------------------- ----------- --------------
EMP SYS_C006451 PRIMARY KEY LOGGING ALWAYS GENERATED NAME
--为某列创建无条件supplemental log（唯一列）
alter table scott.emp add supplemental log group slg_emp (empno) always;
Table altered.
TABLE_NAME LOG_GROUP_NAME LOG_GROUP_TYPE ALWAYS GENERATED
--------------- --------------- ------------------- ----------- -----------
EMP SLG_EMP USER LOG GROUP ALWAYS USER NAME
--为非唯一列创建无条件supplemental log
alter table scott.emp add supplemental log data (all) columns;
Table altered.--禁用primary supplemental log（其中primary可以被unique index、foreign key、or all）
alter database drop supplemental log data (primary key) columns;
Database altered.
--禁用最小 supplemental log
alter database drop supplemental log data;
Database altered.
select supplemental_log_data_min "MIN",
supplemental_log_data_pk "PK",
supplemental_log_data_ui "UI",
supplemental_log_data_fk "FK",
supplemental_log_data_all "ALL"
from v$database;
MIN PK UI FK ALL
-------- --- --- --- ---
NO NO NO NO NO
--禁用supplemental log group：slg_emp
alter table scott.emp drop supplemental log group slg_emp;
Table altered.
--禁用某个表的（primary、unique index、foreign key、or all） supplemental log
alter table scott.emp drop supplemental log data (primary key) columns;
扫一扫
194
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
