oracle捕捉变化数据

在下面几节中，我将向你演示如何在源系统中捕捉变化数据并将其提供给目标系统。

1.识别源表

数据库用户SALES_OP拥有两个表：PRICE_LIST和SALES_TRAN。这两个表分别保存已售商品的价格清单和每笔销售交易的细节。

SQL> DESC PRICE_LIST

 Name          Null?     Type
 ----------    -------   -------
 PRODUCT_ID    NOT NULL  VARCHAR2(20)
 UOM           NOT NULL  VARCHAR2(20)
 PRICE_PER_UOM NOT NULL  NUMBER(10,2)

SQL> DESC SALES_TRAN

 Name           Null?    Type
 ----------     -------  -------
 PRODUCT_ID     NOT NULL VARCHAR2(20)
 SALE_DATE_TIME NOT NULL TIMESTAMP(6)
 QUANTITY       NOT NULL NUMBER(10,2)

每天早上，这两个表中发生变化的数据必须被提取出来并传送到数据仓库系统。

2.设置发布者

发布者是创建和维护变化表的数据库用户。正如前面提到的，发布者的职责是识别并提取变化数据，并将其提供给订阅者。要做到这一点，必须给发布者分配以下两个数据库角色：

SELECT_CATALOG_ROLE

EXECUTE_CATALOG_ROLE
　

为了简单起见，让用户SALES_OP 充当发布者的角色。因为SALES_OP拥有源表，所以将其指定为发布者会比较简单。不过，你也可以指定任何其他数据库用户为发布者。在由多个数据库用户拥有的表构成源表的情况下，最好指定另一个用户作为发布者。在这种情况下，该用户需要在源表上有SELECT权限。

3.创建变化表

每个源表都需要一个变化表。发布者使用DBMS_LOGMNR_CDC_PUBLISH .CREATE_CHANGE_TABLE过程来创建变化表，如清单 1所示。在这个例子中，与PRICE_LIST和 SALES_TRAN 相对应的变化表分别被命名为CDC_PRICE_LIST和CDC_SALES_TRAN。

CREATE_CHANGE_TABLE 过程将变化集的名字作为一个变量来传递。一个变化集是一个变化表的集合。 在清单 1中，我使用了系统生成的变化集：SYNC_SET。

4.设置订阅者

在我的例子中，目标系统SALES_DW是订阅者。为了能够订阅变化数据，订阅者必须在源表和变化表上有SELECT权限，如下所示：

connect sales_op/sales_op
grant select on PRICE_LIST to sales_dw;
grant select on SALES_TRAN to sales_dw;
grant select on CDC_PRICE_LIST
  to sales_dw;
grant select on CDC_SALES_TRAN
  to sales_dw;

5.订阅源表并激活订阅过程

在典型的CDC环境中会有多个订阅者，每个订阅者只需要订阅自己所需要的表。此外，订阅者可能不希望看到自己所订阅的表的所有字段。

在可以订阅发布的数据之前，你需要得到一个订阅句柄。订阅句柄使订阅者能够管理自己感兴趣的变化表和字段。在Oracle的CDC环境中，一个订阅只能由一个订阅句柄识别，而无论你订阅了多少个表，你只需创建一个订阅句柄。 使用DBMS_LOGMNR_ CDC_SUBSCRIBE.GET_SUBSCRIPTION HANDLE过程就可以得到一个订阅句柄。这个过程以变量的方式返回句柄值。你可以在你的会话中定义一个变量来接收来自过程的句柄值，如下所示：

variable sh_sales number;
EXECUTE -
DBMS_LOGMNR_CDC_SUBSCRIBE
  .GET_SUBSCRIPTION_HANDLE( -
    CHANGE_SET=>'SYNC_SET', -
    DESCRIPTION=>'Change data from
Sales Appln.', -
    SUBSCRIPTION_HANDLE=>:sh_sales);

一旦你建立了句柄，你就可以订阅你想要的变化数据。当订阅变化数据时，需要指定你希望订阅的表和字段，如下所示：

EXECUTE DBMS_LOGMNR_CDC_SUBSCRIBE
  .SUBSCRIBE( -
    SUBSCRIPTION_HANDLE=>:sh_sales, -
    SOURCE_SCHEMA=>'SALES_OP', -
    SOURCE_TABLE=>'PRICE_LIST', -

COLUMN_LIST=>'PRODUCT_ID,UOM,
PRICE_PER_UOM');

EXECUTE DBMS_LOGMNR_CDC_SUBSCRIBE
  .SUBSCRIBE( -
    SUBSCRIPTION_HANDLE=>:sh_sales, -
    SOURCE_SCHEMA=>'SALES_OP', -
    SOURCE_TABLE=>'SALES_TRAN', -
    COLUMN_LIST=>'PRODUCT_ID,SALE_DATE_TIME,
QUANTITY');

当指定了希望订阅的表和字段后，你就可以激活你的订阅。无论订阅多少个表，你都只需要激活订阅一次。一旦激活了一个订阅，你就不能再将任何其他表或字段添加到你的订阅中。为了激活订阅，你可以调用将订阅句柄作为参数调用DBMS_LOGMNR_CDC_SUBSCRIBE.ACTIVATE_ SUBSCRIPTION过程，并将订阅句柄传递给它：EXECUTE - DBMS_LOGMNR_CDC_SUBSCRIBE .ACTIVATE_SUBSCRIPTION( - SUBSCRIPTION_HANDLE=>:sh_sales)。

6. 设置CDC窗口

源表中的数据不断变化(插入、更新或删除) ，而不管对变化数据的订阅。因此，订阅要设置（扩展）一个窗口??CDC窗口，通过该窗口可以看到任何变化。DBMS_LOGMNR_CDC_SUBSCRIBE.EXTEND_WINDOW过程可以被用来设置CDC窗口，如下所示：

EXECUTE -
DBMS_LOGMNR_CDC_SUBSCRIBE.EXTEND_WINDOW( -
    SUBSCRIPTION_HANDLE=>:sh_sales);

当你执行了EXTEND_WINDOW过程后，如果你没有清除窗口并再次对它进行扩展，那么对源表所做的改变在订阅中就无法看到（后面对此有更详细介绍）。

7.准备一个订阅者视图

一旦你设置了CDC窗口，你就需要为订阅者准备一个视图，以便订阅者能够看到变化数据。你需要为订阅者订阅的每个源表准备一个视图。DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW过程可以被用来准备一个订阅者视图。你需要将订阅句柄、源模式名和源表名传递给该过程，该过程就会以变量的方式返回订阅者视图的名字。在本例中，源表PRICE_LIST和SALES_TRAN 的订阅者视图名分别以变量sv_price_list 和sv_sales_tran返回。

variable sv_price_list varchar2(30);

EXECUTE -
DBMS_LOGMNR_CDC_SUBSCRIBE
  .PREPARE_SUBSCRIBER_VIEW( -
    SUBSCRIPTION_HANDLE =>:sh_sales, -
    SOURCE_SCHEMA=>'SALES_OP', -
    SOURCE_TABLE=>'PRICE_LIST', -
    VIEW_NAME => :sv_price_list);

variable sv_sales_tran varchar2(30);

EXECUTE -
DBMS_LOGMNR_CDC_SUBSCRIBE
  .PREPARE_SUBSCRIBER_VIEW( -
    SUBSCRIPTION_HANDLE =>:sh_sales, -
    SOURCE_SCHEMA=>'SALES_OP', -
    SOURCE_TABLE=>'SALES_TRAN', -
    VIEW_NAME => :sv_sales_tran);

你可以从这两个变量中得到视图名。视图名是系统产生的，一般包括一个CDC前缀和一个数字。例如：

SQL> select :sv_price_list from dual;

:SV_PRICE_LIST
----------------
CDC#CV$1430621

SQL> select :sv_sales_tran from dual;

:SV_SALES_TRAN
-----------------
CDC#CV$1430623

8.访问变化表中的数据

订阅者视图包含你所需要的变化数据。它还包含使用变化数据所需的其他一些附加信息：

SQL> desc CDC#CV$1430621

 Name         Null?     Type
----------    -------   --------
 OPERATION$         CHAR(2) 
 CSCN$                  NUMBER
 COMMIT_TIMESTAMP$      DATE
 TIMESTAMP$             DATE
 USERNAME$              VARCHAR2(30)
 PRICE_PER_UOM          NUMBER(10,2)
 PRODUCT_ID             VARCHAR2(20)
 UOM                    VARCHAR2(20)

例如，字段OPERATION$ 表示变化是INSERT、UPDATE还是DELETE。字段CSCN$、COMMIT_TIMESTAMP$、TIMESTAMP$和USERNAME$表示是谁在源表中做的改变以及是何时做的。其他字段是源表中订阅的字段。你可以使用SELECT语句查看订阅者视图中的数据，例如：

SQL> select * from CDC#CV$1430621;

OP PRICE_PER_UOM   PRODUCT_ID   UOM
-- ------------    ------------ ----
I  2.99            P101         pound
I  4.99            P102         pound

9.删除订阅者视图，清除CDC窗口

当你了解并提取了变化数据并且不再需要在订阅者视图中显示变化数据时，你需要删除订阅者视图并清除CDC窗口，这样你才可以建立（扩展）新的窗口来查看新的变化数据。删除已有的订阅者视图，使用DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW过程。在你可以清除旧的CDC窗口并建立新窗口之前，你需要逐个删除子订阅的每个订阅者视图。

EXECUTE -
DBMS_LOGMNR_CDC_SUBSCRIBE
  .DROP_SUBSCRIBER_VIEW( -
    SUBSCRIPTION_HANDLE =>:sh_sales, -
    SOURCE_SCHEMA=>'SALES_OP', -
    SOURCE_TABLE=>'PRICE_LIST');

EXECUTE -
DBMS_LOGMNR_CDC_SUBSCRIBE
  .DROP_SUBSCRIBER_VIEW( -
    SUBSCRIPTION_HANDLE =>:sh_sales, -
    SOURCE_SCHEMA=>'SALES_OP', -
    SOURCE_TABLE=>'SALES_TRAN');

删除订阅者视图后，你就可以清除窗口。使用DBMS_LOGMNR_CDC_SUBSCRIBE. PURGE_WINDOW过程来清除窗口：

EXECUTE -
DBMS_LOGMNR_CDC_SUBSCRIBE.PURGE_WINDOW( -
    SUBSCRIPTION_HANDLE=>:sh_sales);

10.重复第6到第9步以查看新数据

要定期提取变化数据，你需要重复第6到第9步。如果你每日都要提取变化数据，你的每日（最好在晚上）数据提取工作将包括建立订阅窗口(EXTEND_WINDOW)、准备订阅者视图(PREPARE_SUBSCRIBER_VIEW)、从订阅者视图中访问数据、删除订阅者视图(DROP_SUBSCRIBER_VIEW)、清除窗口(PURGE_WINDOW)。

步骤总结

第1步到第5步是配置步骤，对于一个给定的发布者/订阅者环境只需要设置一次。定期执行第6到第9步，以从源系统中提取越来越多的变化数据。

Sanjay Mishra (smishra_tech@yahoo.com) 已经与人合著了3本关于Oracle的书：《Oracle并行处理》《Oracle SQL Loader：权威指南》和《掌握Oracle SQL》，这三本书全部由O'Reilly and Associates出版。

查看本文来源