CDC不同模式在ODI体现系列之二 异步模式

 

异步模式需要在数据库中做一些准备工作:

 

改数据为归档并启用logminer:

 

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE例程已经关闭。

SQL> startup mount

ORACLE例程已经启动。

 

Total System Global Area 599785472 bytes

Fixed Size                 1288820 bytes

Variable Size            171967884 bytes

Database Buffers         423624704 bytes

Redo Buffers               2904064 bytes

数据库装载完毕。

SQL> alter database force logging;

 

数据库已更改。

 

SQL> alter database archivelog;

 

数据库已更改。

 

SQL> alter database add supplemental log data;

 

数据库已更改。

 

 

设置权限:

 

发布用户必须有管理Oracle Stream的权限.

用DMBS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE赋予权限.

授权管理权限给用户户,允许用户户改实例参数和创建对象.

SQL> grant select_catalog_role to scott;

 

Grant succeeded.

 

SQL> grant execute_catalog_role to scott;

 

Grant succeeded.

 

SQL> grant create table to scott;

 

Grant succeeded.

 

SQL> grant create session to scott;

 

Grant succeeded.

 

SQL> grant dba to scott;

 

Grant succeeded.

 

SQL> grant execute on dbms_cdc_publish to scott;

 

Grant succeeded.

 

SQL> grant select on change_sets to scott;

 

Grant succeeded.

 

SQL> grant create tablespace to scott;

 

Grant succeeded.

 

SQL> grant create sequence to scott;

 

Grant succeeded.

 

SQL> grant unlimited tablespace to scott;

 

Grant succeeded.

 

SQL> execute dbms_streams_auth.grant_admin_privilege(grantee=>'scott');

 

PL/SQL procedure successfully completed.

 

 

工作对象(Work schema)必须有select any table权限

 

SQL> grant select any table to scott;

 

Grant succeeded.

 

设置数据库参数,因为要用logminer和stream,设置相应内存参数;

 

SQL> alter system set java_pool_size=50m;

 

系统已更改。

 

SQL> alter system set streams_pool_size=100m;

 

系统已更改。

 

 

需要把table添加到日志组(后来测试,这步骤在KM已经有,而且做了以后反而导致错误)

 

SQL> conn scott/tiger

Connected.

SQL> alter table dept add supplemental log data(all) columns;

 

Table altered.

 

这个时候要确认数据库打开了Streams Capture功能

SQL> select * from v$option where parameter =’Streams Capture’;

PARAMETER           VALUE

-------------------- --------

Streams Capture     TRUE

 

标准版不支持Advanced Replication和Stream.
1.不支持Streams

2.不支持Advanced Replication的multimaster方式

 

 

解决需：1.安装企业版  2.安装Advanced Replication组件  3.执行CATREP.SQL。

fact: Oracle Server - Standard Edition
symptom: ORA-00439: feature not enabled: %s

fix:
1. Install Enterprise Edition (see Note 117048.1)
2. Install Advanced Replication Option using installer
3. Run CATREP.SQL script.

UNIX: $ORACLE_HOME/rdbms/admin/catrep.sql
NT: ORACLE_HOME/rdbms/admin/catrep.sql

 

导入JKM的知识模块:

 

 

这里,我们把三种模式的JKM都导入,然后引入时候,我们做一个对比,看看JKM的差别

 

 

编辑模型,点击日志记录TAB页,选择相应的日志记录模式和JKM:

 

 

我们选择一致性设置,可以看到我们刚才引入的三个JKM,都属于一致性读

 

 

每个JKM具体是怎么实现,差别是什么呢?我们通过编辑JKM来看他们具体做什么?

 

选择项目栏:

 

 

 

我们可以看到这个JKM的顺序180的命令,删除触发器,通过下面知道,它还是利用触发器实现.

 

我们那么再看JKM Oracle consistent (update date)

 

 

看到顺序500创建了一个删除后的触发器,双机这个命令,可以看到相应的创建语法,这些JKM虽然是是一致的模式,但不是异步CDC的概念.

 

我们看JKM Oracle consistent (LOGMINER)

 

 

 

 

我们可以看到它是通过强制数据库记录日志实现的,这个是异步CDC的方法.这时,我们在模型编辑的时候,选择这个JKM.

 

 

选择JKM Oracle10gconsistent (LOGMINER).CDC,可以看到有3个选项,一个是异步模式,就是我们提到的HotLog模式,还有一个是自动配置,是下一节要介绍的AUTOLOG模式.

最后一个参数是定义日志表的存储表空间,手工输入就可以,这里输入USERS.

 

看下面的说明:如果选择异步模式,JKM最小化在源系统上的负载,用Oracle CDC HotLog模式,.这个选项要求归档模式,强制日志.

 

启动日志报错,查看operator

 

SQL> l

 1 BEGIN

 2    DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(

 3         change_set_name   => 'CDC',

 4         description       => 'Sunopsis change set for model : CDC',

 5

 6         change_source_name => 'HOTLOG_SOURCE',

 7         begin_date => sysdate

 8

 9    );

 10* END;

SQL> /

*

1031 : 42000 : java.sql.SQLException: ORA-01031: insufficient privileges

ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 705

ORA-06512: at line 1

 

因为没有select any change_sets权限,查了10多分钟.

 

再次启动日志又报错:

 

查看描述:

30567 : 99999 : java.sql.BatchUpdateException: ORA-30567: name already used by an existing log group

30567 : 99999 : java.sql.SQLException: ORA-30567: name already used by an existing log group

 

java.sql.BatchUpdateException: ORA-30567: name already used by an existing log group

 

因为我们在前面做了下面动作

 

alter table dept add supplemental log data(all) columns;

 

删除这个日志数据

SQL> alter table dept drop supplemental log data(all) columns;

 

Table altered.

 

检查日志组

SQL> select owner,table_name,log_group_name from all_log_groups;

OWNER     TABLE_NAME LOG_GROUP_NAME

---------- ---------- ------------------------------

SYS       ENC$      ENC$_LOG_GRP

SCOTT     EMP       LOG_GROUP_EMP

SCOTT     DEPT      LOG_GROUP_DEPT

SCOTT     EMP       SYS_C0011135

SCOTT     EMP       SYS_C0011136

SCOTT     EMP       SYS_C0011137

SCOTT     DEPT      SYS_C0011175

SCOTT     DEPT      SYS_C0011176

SCOTT     DEPT      SYS_C0011177

SCOTT     DEPT      DEPT

SCOTT     DEPT      SYS_C0011442

 

11 rows selected.

 

删除同名日志组

SQL> alter table dept drop supplemental log group dept;

 

Table altered.

 

 

重新启动 operator

 

 

 

又报了一个新错误

 

BEGIN

      DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(

          owner         => 'SCOTT',

          change_table_name     => 'J$DEPT',

          change_set_name=> 'CDC',

          source_schema   => 'SCOTT',

          source_table => 'DEPT',

          column_type_list => 'DEPTNO NUMBER(2) ',

          capture_values    => 'new',

          rs_id           => 'n',

          row_id        => 'n',

          user_id        => 'n',

          timestamp           => 'y',

          object_id             => 'n',

          source_colmap    => 'n',

          target_colmap     => 'n',

          ddl_markers => 'n',

          options_string     => 'users'

      );

END;

 

ERROR at line 1:

ORA-00922: missing or invalid option

ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 783

ORA-06512: at line 2

 

做10046跟踪:

 

SQL> conn scott/tiger

Connected.

SQL> alter session set events '10046 trace name context forever, level 8';

 

Session altered.

 

SQL> BEGIN

 2    DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(

 3         owner             => 'SCOTT',

            change_table_name => 'J$DEPT',

 4   5            change_set_name   => 'CDC',

            source_schema     => 'SCOTT',

            source_table      => 'DEPT',

 6   7   8       column_type_list  => 'DEPTNO NUMBER(2) ',

            capture_values    => 'new',

            rs_id             => 'n',

            row_id            => 'n',

 9  10  11  12          user_id           => 'n',

            timestamp         => 'y',

 13  14            object_id         => 'n',

            source_colmap     => 'n',

            target_colmap     => 'n',

 15  16  17       ddl_markers       => 'n',

 18         options_string    => 'users'

 19    );

 20 END;

 21 /

BEGIN

*

ERROR at line 1:

ORA-00922: missing or invalid option

ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 783

ORA-06512: at line 2

 

 

SQL> alter session set events '10046 trace name context off';

 

Session altered.

 

 

查看trace发现是创建J$DEPT表时出现的错误

PARSE ERROR #7:len=253 dep=1 uid=81 ct=1 lid=81 tim=1241053855723263 err=922

CREATE TABLE SCOTT.J$DEPT ( OPERATION$ CHAR(2), CSCN$ NUMBER, COMMIT_TIMESTAMP$ DATE, XIDUSN$ NUMBER, XIDSLT$ NUMBER, XIDSEQ$ NUMBER

, TIMESTAMP$ DATE, DEPTNO NUMBER(2) ) USERS PARTITION BY RANGE (cscn$) (PARTITION p1 VALUES LESS THAN (281474976710656))

EXEC #3:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1241053855731599

Akkkkkkkkkkkkkkkk

FETCH #3:c=0,e=514,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=1241053855732165

=====================

PARSING IN CURSOR #6 len=83 dep=1 uid=0 ct=3 lid=0 tim=1241053855735395 hv=1936881461 ad='2fa86004' sqlid='9gbtbxttr4xtp'

select jarname from javajarobjects$ where bjname=:1 and owner#=:2 and namespace=:3

END OF STMT

PARSE #6:c=2000,e=1106,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1241053855735387

EXEC #6:c=999,e=1650,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1241053855737156

FETCH #6:c=1000,e=280,p=0,cr=4,cu=0,mis=0,r=0,dep=1,og=4,tim=1241053855737494

STAT #6 id=1 cnt=0 pid=0 pos=1 bj=12468 p='TABLE ACCESS FULL JAVAJAROBJECTS$ (cr=4 pr=0 pw=0 time=0 us cost=2 size=44 card=1)'

oracle.jdbc.driver.OracleSQLException: ORA-00922: missing or invalid option

       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java)

       at oracle.jdbc.driver.T2SConnection.check_error(T2SConnection.java)

       at oracle.jdbc.driver.T2SStatement.checkError(T2SStatement.java)

       at oracle.jdbc.driver.T2SStatement.executeForRows(T2SStatement.java)

       at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)

       at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java)

       at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java)

       at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java)

       at oracle.CDC.ChangeTable.createTable(ChangeTable.java:1657)

       at oracle.CDC.ChangeTable.create(ChangeTable.java:1527)

       at oracle.CDC.PublishApi.createChangeTable(PublishApi.java:427)

 

 

 

我们到数据库执行发现,原来是创建表时,语法有错,没有加tablespace参数.

SQL> CREATE TABLE SCOTT.J$DEPT (

 2 OPERATION$ CHAR(2), CSCN$ NUMBER, COMMIT_TIMESTAMP$ DATE, XIDUSN$ NUMBER,

 3 XIDSLT$ NUMBER, XIDSEQ$ NUMBER,

 4 TIMESTAMP$ DATE, DEPTNO NUMBER(2) )

 5 users

 6 PARTITION BY RANGE (cscn$) (PARTITION p1 VALUES LESS THAN (281474976710656));

users

*

ERROR at line 5:

ORA-00922: missing or invalid option

 

 

SQL> l5

 5* users

SQL> c/users/tablespace users

 5* tablespace users

SQL> l

 1 CREATE TABLE SCOTT.J$DEPT (

 2 OPERATION$ CHAR(2), CSCN$ NUMBER, COMMIT_TIMESTAMP$ DATE, XIDUSN$ NUMBER,

 3 XIDSLT$ NUMBER, XIDSEQ$ NUMBER,

 4 TIMESTAMP$ DATE, DEPTNO NUMBER(2) )

 5 tablespace users

 6* PARTITION BY RANGE (cscn$) (PARTITION p1 VALUES LESS THAN (281474976710656))

SQL> /

 

Table created.

 

到此我们已经找到原因是ODI JKM的参数定义有问题

 

这个时候,我们进行修改,再次执行

SQL> BEGIN

 2    DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(

 3         owner             => 'SCOTT',

 4         change_table_name => 'J$DEPT',

            change_set_name   => 'CDC',

            source_schema     => 'SCOTT',

 5   6   7       source_table      => 'DEPT',

 8         column_type_list  => 'DEPTNO NUMBER(2) ',

 9         capture_values    => 'new',

            rs_id             => 'n',

            row_id            => 'n',

 10  11  12       user_id           => 'n',

 13         timestamp         => 'y',

 14         object_id         => 'n',

            source_colmap     => 'n',

 15  16            target_colmap     => 'n',

 17         ddl_markers       => 'n',

 18         options_string    => 'tablespace users'

       );

 19  20 END;

 21 /

 

PL/SQL procedure successfully completed.

可以看到执行成功.

 

 

修改JKM,加入tablespace

 

 

 

 

重新启动日志,需要做下面动作

 

SQL> alter table dept drop supplemental log group dept;

 

Table altered.

 

SQL> BEGIN

 2 DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE(

 3 owner             => 'SCOTT',

 4 change_table_name => 'J$DEPT',

 5 FORCE_FLAG        => 'Y');

 6 end;

 7 /

 

PL/SQL procedure successfully completed.

 

BEGIN

  DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE(

  owner             => 'SCOTT',

  change_table_name => 'J$DEPT',

   FORCE_FLAG        => 'Y');

  end;

   /

删除创建CDC对象表的数据

SQL> delete from SNP_CDC_OBJECTS;

 

3 rows deleted.

 

SQL> commit;

 

Commit complete.

 

 

Operator创建订阅又出现错误

 

 

继续跟踪错误,后来测试发现订阅名称不能和变化集名称一致.

 

查了1天时间,耗时啊.

 

SQL> alter session set events '10046 trace name context forever, level 12';

 

Session altered.

 

SQL> oradebug setmypid

Statement processed.

SQL> BEGIN

 2    DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(

 3         change_set_name   => 'CDC',

 4         description       => 'Common subscription to be used to manage journalized data',

            subscription_name => 'CDC'

       );

 5   6   7 END;

 8 /

BEGIN

*

ERROR at line 1:

ORA-29532: Java call terminated by uncaught Java exception:

java.lang.StringIndexOutOfBoundsException: String index out of range: 4

ORA-06512: at "SYS.DBMS_CDC_SUBSCRIBE", line 12

ORA-06512: at line 2

 

 

SQL> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/devdb/devdb/trace/devdb_ora_5895.trc

 

 

修改JKM:

 

 

 

把订阅名称多加个C.

 

再次启动日志,发现执行成功,数据源出现绿色图标.

 

查看operator