oracle 11g streams 捕获（capture）进程使用示例

来源：互联网发布：赛鸽记录软件编辑：程序博客网时间：2024/05/29 16:05

接前面"oracle 11g streams rule和rule set 使用示例"

1、列出带有数据字典信息的 archive log 的 first scn

set numwidth 18set lines 132select first_change#,namefrom v$archived_logwhere dictionary_begin = 'YES';-- 如果没有结果 则需要提取数据字典并获得相应的 scnset serveroutput ondeclarescn number;begindbms_capture_adm.build(first_scn => scn);dbms_output.put_line('First SCN = ' || scn);end;/

2、自动创建本地（在source database上创建）capture进程（其实添加rule就是自动创建capture进程）

--如果捕获进程存在就只是添加rule   declarel_dml_rule_name varchar2(30);l_ddl_rule_name varchar2(30);begindbms_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 typefrom dba_capturewhere 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_conditionfrom dba_streams_ruleswhere 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)begindbms_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_scncheckpoint_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.WORLDConnected.-- Create downstream capture process begindbms_capture_adm.create_capture (capture_name => 'DBXA_CAP',queue_name => 'DBXA_APP_Q',use_database_link => true,--指定是down stream capturesource_database => 'DBXA.WORLD',checkpoint_retention_time => 2);end;/PL/SQL procedure successfully completed.--指定use_database_link => null的方法-- Acquire Data Dictionary build SCN from source databaseconnect strmadmin/strmadmin@DBXA.WORLDConnected.set serveroutput ondeclarescn number;begindbms_capture_adm.build(first_scn => scn);dbms_output.put_line('First SCN Value = ' || scn);end;/First SCN Value = 13722837PL/SQL procedure successfully completed.-- Connect to Downstream Databaseconnect strmadmin/strmadmin@DBXB.WORLDConnected.-- Create downstream capture process.-- Enter the First SCN obtained in the previous step.begindbms_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: 13722837old 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.WORLDConnected.begindbms_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_userfrom 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,enabledfrom dba_sync_capture_tables;TABLE_OWNER        TABLE_NAME       ENABLED------------------------------ ------------------------------ ----------SCOTT        DEPT       YES--手动创建同步capture进程begindbms_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_parameterswhere capture_name = 'DBXA_CAP'order by parameter;PARAMETER                      VALUE      SET_BY_USER------------------------------ ---------- ---------------DISABLE_ON_LIMIT               N          NODOWNSTREAM_REAL_TIME_MINE      Y          NOMAXIMUM_SCN                    INFINITE   NOMERGE_THRESHOLD                60         NOMESSAGE_LIMIT                  INFINITE   NOMESSAGE_TRACKING_FREQUENCY     2000000    NOPARALLELISM                    1          NOSKIP_AUTOFILTERED_TABLE_DDL    Y          NOSPLIT_THRESHOLD                1800       NOSTARTUP_SECONDS                0          NOTIME_LIMIT                     INFINITE   NOTRACE_LEVEL                    0          NOWRITE_ALERT_LOG                Y          NO13 rows selected.--查看捕获进程隐藏参数（最好不要修改）select p.name parameter,    p.value,    p.user_changed_flag,    p.internal_flagfrom sys.streams$_process_params p,    sys.streams$_capture_process cwhere 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             124 rows selected.--当出现ora-1341或ora-1280是要考虑增加 _SGA_SIZE Logminer内存总大小：_SGA_SIZE * PARALLELISM7、修改捕获进程参数（同步捕获进程不允许修改参数）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_filteredfrom dba_streams_unsupportedwhere 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_columnswhere 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.2DEPT         DNAME            11.1                        9.2DEPT         LOC              11.1                        9.2EMP_ROWID    EMPNO            11.1                        9.2EMP_ROWID    ROW_ID                 rowid column              rowid column

9、打开 force logging

--打开数据库或表空间为 force loggingalter database force logging;Database altered.alter tablespace data_1 force logging;Tablespace altered.--打开表 force loggingcol logging for a10alter table scott.dept logging;Table altered.select loggingfrom dba_tableswhere 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 groupalter table scott.emp add supplemental log data(unique, foreign key) columns;Table altered.select table_name,    log_group_name,    log_group_type,    always,    generatedfrom dba_log_groupswhere owner='SCOTT';TABLE_NAME  LOG_GROUP_NAME  LOG_GROUP_TYPE      ALWAYS      GENERATED----------- --------------- ------------------- ----------- --------------EMP         SYS_C006447     UNIQUE KEY LOGGING  CONDITIONAL GENERATED NAMEEMP         SYS_C006448     FOREIGN KEY LOGGING CONDITIONAL GENERATED NAME--为某列创建有条件 supplemental log groupalter 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 logalter 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 logalter 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 logalter 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_empalter table scott.emp drop supplemental log group slg_emp;Table altered.--禁用某个表的（primary、unique index、foreign key、or all） supplemental logalter table scott.emp drop supplemental log data (primary key) columns;

0 0