oracle 11g streams 捕获(capture)进程使用示例

接前面"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;
/
2、自动创建本地(在source database上创建)capture进程(其实添加rule就是自动创建capture进程)

--如果捕获进程存在就只是添加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.
4、创建下游(downstream)捕获进程(DBXA是source database,DBXB是down stream database)
--指定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.
5、自动创建同步捕获进程
--这里假设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.
6、查看捕获进程参数(和同步捕获进程没关系)

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
8、检查不支持对象的方法

--显示不支持对象的原因
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
9、打开 force logging

--打开数据库或表空间为 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
10、有关supplemental log 信息

--查看 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.
11、有条件supplemental log

--创建有条件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
12、无条件supplemental log
--创建无条件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.
13、禁用supplemental log
--禁用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;
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页