PowerExchange培训教材

Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,35,Click to edit Master title style,Informatica PowerExchange for Oracle,培训,夏 凯,2009-01,电话：,13439473732,MSN,：,xiakaicd,体系结构,安装介绍,注册数据源,PWX,与,PC,集成,学 习 内 容,PowerExchange Batch,：,用于数据源到目标之间的大批量数据迁移，适用于,Unix,、,PC,、,MVS,和,AS/400,数据格式的数据。,PowerExchange Change,：,用于从创建的压缩变更文件（,condensed changes files,）中周期性地获取更新。,PowerExchange Real-time,：,从日志信息中直接捕获更新，实时性较高。对于,Oracle,的数据增量抽取，只能使用,PowerExchange Real-time,。,PowerExchange Change,和,PowerExchange Real-time,只能捕获到所需的更新数据，将数据加载到目标数据库的任务需要由,PowerCenter,或其他第三方工具来完成。,PowerExchange,抽取模式,Tools,(ETL, EAI, BI),Data Maps for Non-Relational Access,Informatica,PowerCenter,Informatica,Data Analyzer,Other Tools,DataRecords,Listener,PowerExchange,Batch Data Movement,SQL,Informatica PowerExchange,User Applications,Operating Environment,Target,Remote Data,Standards and,Messaging,Relational and,Flat Files,PackagedApplications,Mainframe and,Mid-Range,Native DB,Request,Native DB,Response,1,、,PowerCenter,发布一系列标准,SQL,请求。,2,、,PowerExchange,转换,SQL,通过,Native,方式,给数据源发送请求。,3,、数据源通过,Native,方式做出反馈。,4,、,PowerExchange,发送经过翻译后的数据记录给,PowerCenter,。,Informatica PowerExchange,Operating Environment,User Application,Tools,(ETL, EAI, BI),Registration,Maps,Oracle,Informatica,PowerCenter,Informatica,PowerAnalyzer,Other Tools,Data Records,Listener,SQL,PowerExchange,“Change” Architecture for Oracle,LogMiner,Oracle,Logs,PWX Condense Job,Checkpoint files,Condense files,Target,1,、通过,Oracle,的,LogMiner,搜集,Redo Logs,变化信息。,2,、被收集的变化数据会被提交给,PWX Condense Job,4,、,PowerCenter,发布一系列标准,SQL,请求给,PowerExchange,的侦听程序来访问变化数据,6,、,PowerExchange,发送经过翻译后的数据记录给,PowerCenter,。,3,、,Condense Job,将通过,LogMiner,接口捕捉来的变化数据，并基于外部配置文件产生,Condense,文件。,5,、,Listener,从,Condense,文件抽取变化数据。,Oracle Redo Log,是,PowerExchange Oracle capture,流程的必要部分，被监测数据必须在,Archive Log,模式下运行。,LogMiner,读取,Redo Log,，使,PowerExchange,能够从指定的时间点抽取数据，这些时间点由,Oracle SCN,（,System Change Numbers,）分隔。,Navigator,被用做,创建变更捕获注册信息，这些注册信息定义了用户需要实时监测的数据。,CAPI,连接,PowerExchange REGISTRATION,，获取需要监测的表和列信息，然后连接,Oracle,和,Log Miner,，根据,restart information,确定应该返回哪些变更记录。,UOW Cleanser (Unit of Work),保证工作单元能够按照其提交的顺序被依次处理，同时去除回滚的事务 。,PWX,Condense,Job,接收变化信息并创建,Condense,文件。,当,PowerCenter Workflow,被调用后，通过,Listenter,请求变化信息。,OracleLogMiner,Application,PowerCenterServer,Oracle9.2.0.7+,CAPIUOW Cleanser,Navigator,Condense files,Repository,(Registrations),Oracle Logs,PWXPC,PWX Listener,PowerExchange,“Change” Architecture for Oracle,Informatica PowerExchange,Operating Environment,User Application,Tools,(ETL, EAI, BI),Registration,Data Maps,Oracle,Informatica,PowerCenter,Informatica,PowerAnalyzer,Other Tools,DataRecords,Listener,SQL,PowerExchange,“Real time” Architecture for Oracle,LogMiner,Collector,Virtual Change Stream,Oracle,Logs,Target,1,、通过,Oracle,的,LogMiner,搜集,Redo Logs,变化信息。,2,、被收集的变化数据会被存储在,change Stream,或者,Virtual Change Stream,队列中,3,、,PowerCenter,发布一系列标准,SQL,请求给,PowerExchange,的侦听程序来访问变化数据,4,、,PowerExchange,的侦听程序根据请求，发送经过“翻译”后的变化数据给,PowerCenter,，从而实现变化数据的实时捕获,Native DB,Request,Native DB,Response,PowerExchange Listener,Agent,接收变化信息。,(,如果,Oracle,和,PowerCenter Server,在同一台机器就没有必要,),PowerExchange,客户端提供,PowerExchange,和,PowerCenter,的连接。,P,owerCenter,调用一个持续的,Workflow,应用不断将变化数据迁移到目标。,OracleLogMiner,Application,CAPIUOW Cleanser,Navigator,Listener Agent,PWXPC,PowerCenterServer,Target,Oracle9.2.0.7+,Repository,(Registrations),Oracle Logs,PowerExchange,“Real time” Architecture for Oracle,体系结构,安装介绍,注册数据源,PWX,与,PC,集成,学 习 内 容,操作系统支持情况,Windows 2000, XP, 2003,AIX 5.1 and above,Solaris 2.7 and above,HPUX 11 and above,Linux versions (Redhat AS 2.1-3.0 ) with kernel 2.4,Suse Linux ES 8-9,系 统 必 要 需 求,Oracle,必要条件（最好在,DBA,的配合下操作）,Oracle,V9.2.0.6,或以上；,Real Application Clusters,不支持,Oracle 9i,版本，在,Oracle 10g,上支持。,Oracle,必须运行在归档模式下；,Oracle 9i,版本,Supplemental Logging,必须打开,(,设置为,true),；,Oracle LogMiner,必须配置完成；,Oracle catalog,被拷贝到,Oracle Redo Log,；,A template SQLPlus script is provided to perform the above functions,局 限 性 说 明,支持的数据类型,NUMBER,CHAR,VARCHAR2,RAW,DATE,TIMESTAMP(,目前能捕获到微秒级别,),不支持的数据类型,LONG,LONG RAW,LOB types,TIMESTAMP WITH LOCAL TIME ZONE,LogMiner,不支持的类型,安装的几类环境,所有的组成部分都在统一台,windows,机器上。,(,DB+PC+PWX+Client,),服务端和客户端单独部署，,PowerCenter Server,和,Oracle Server,在同一台主机。,(DB+PC+PWX,和,Client,分开,),PowerCenter Server,和,Oracle Server,分别部署在两台主机,PowerExchange,安装在,PowerCenter Server,端，通过,SQL*Net,访问,Oracle,数据库。,(DB,和,PC+PWC+Client,分开,),PowerExchange,安装在,Oracle Server,端，本地连接,Oracle,数据库。,(DB+PWX,和,PC+Client,分开,),PowerExchange,采用的是,Server/Client,模式，在被监控的服务器上安装,server,（,Listener,），获取源数据的变更，在数据抽取服务器上安装,client,（,Navigator,），定义所要监控的数据。,Registration,Data Maps,Informatica,PowerCenter,PWXPC,Oracle,Oracle,Logs,LogMiner,Informatica,PowerExchange,PowerCenter Client ,PWX,PWXPC,所有组件都在同一台,Windows,机器上,(PowerCenter,，,PowerExchange,和,Oracle),。因为所有的访问都是“,Local”,的，因此，不需要配置,PWX,的,Listener,。,环境一：所有组件部署在同一台,Windows,主机,PowerCenter,首先被安装。,安装,PowerExchange,。,更新,dbmover.cfg,文件，增加,CAPI,信息和,Oracle,信息。,ORACLEID=(,Collection-Id,OracleSID,Connect_string,Capture_connect_string),CAPT_XTRA=C:Program FilesInformaticaInformatica,PowerExchangecapturecamaps,“,CAPT_PATH=C:Program FilesInformaticaInformatica PowerExchangecapture“,CAPI_CONN_NAME=,CAPIUOWC,CAPI_CONNECTION=(NAME=,CAPIUOWC,TYPE=(UOWC,CAPINAME=,CAPIORA,),CAPI_CONNECTION=(NAME=,CAPIORA,TYPE=(ORCL,REPNODE=,local,ORACOLL,=,Collection-Id,),注：,1,、,ORACLEID=(,collection_id,oracle_sid,connect_string,capture_connect_string,),ORACLEID,：最大为,20,个字符。,collection_id,：在注册捕获定义时会被引用，两者保持必须一致。,oracle_sid,：被监测数据库的数据库实例名。,connect_string,：被监测数据库的服务访问别名。,capture_connect_string,：可选项。,2,、其中，,ORACOLL,的参数值,Collection_id,应与,ORACLEID,中的,Collection_id,一致。,3,、目录,capture,和,camap,需要手动创建。,所有组件部署在同一台,Windows,主机安装配置要点,重启机器，完成安装！,安 装 过 程,Server_A: 10.1.1.100,Informatica,PowerCenter,Oracle,Oracle,Logs,LogMiner,PWXPC,Informatica,PowerExchange,PowerCenter Client ,PWX, PWXPC,主机,Server_A,安装,PowerCenter,，,PWX,和,Oracle,。,PWX Listener,是必需的。,Registration,Data Maps,Listener,环境二：服务端和客户端分开部署,PowerCenter,首先被安装。,终端上安装,PWX Navigator,。安装,PWX,软件，修改终端上的,dbmover.cfg,。,NODE=(Server_A,TCPIP,10.1.1.100,2480) /*,连接,Server_A,其中，,Server_A,是节点名称，在注册捕获定义时需要引用该节点名称。,10.1.1.100,是被监测数据库所在主机的,IP,地址。,2480,是监听端口号。,Server_A,上安装,PWX,软件，修改,Server_A,上的,dbmover.cfg,。,ORACLEID=,(Collection-Id,OracleSID,Connect_string,Capture_connect_string),CAPT_XTRA=C:Program FilesInformaticaInformatica PowerExchangecapturecamaps,CAPT_PATH=C:Program FilesInformaticaInformatica PowerExchangecapture,CAPI_CONN_NAME=,CAPIUOWC,CAPI_CONNECTION=(NAME=,CAPIUOWC,TYPE=(UOWC,CAPINAME=,CAPIORA,),CAPI_CONNECTION=(NAME=,CAPIORA,TYPE=(ORCL,REPNODE=local,ORACOLL=,Collection-Id,),注：,1,、,ORACOLL,的参数值,Collection_id,应与,ORACLEID,中的,Collection_id,一致。,2,、目录,capture,和,camap,需要手动创建。,服务端和客户端分开部署安装配置要点,Registrations,Informatica,PowerCenter,Informatica,PowerExchange,PWXPC,Oracle,Oracle,Logs,LogMiner,PowerCenter Client ,PWX, PWXPC,PWX Listener,主机,Server_B,是,PowerCenter Server,。需安装,PWX,。,PWX Listener,是必需的。,主机,Server_A,为,Oracle Server,。,CDCRecords,SQL*Net,Server_A: 10.1.1.100,Server_B: 10.2.2.200,环境 三,(1),：服务分开部署用,SQL*Net,访问远程,Oracle,PowerCenter,首先被安装。,终端上安装,PWX Navigator,。安装,PWX,软件，修改终端上的,dbmover.cfg,。,NODE=(Server_B,TCPIP,10.2.2.200,2480) /*,连接,Server_B,Server_B,上安装,PWX,软件，修改,Server_B,上的,dbmover.cfg,。,ORACLEID=(,Collection-Id,OracleSID,Connect_string,Capture_connect_string),CAPT_XTRA=C:Program FilesInformaticaInformatica,PowerExchangecapturecamaps,CAPT_PATH=C:Program FilesInformaticaInformatica,PowerExchange,capture,CAPI_CONN_NAME=,CAPIUOWC,CAPI_CONNECTION=(NAME=,CAPIUOWC,TYPE=(UOWC,CAPINAME=,CAPIORA,),CAPI_CONNECTION=(NAME=,CAPIORA,TYPE=(ORCL,REPNODE=,local,ORACOLL,=,Collection-Id,),注：,1,、,ORACOLL,的参数值,Collection_id,应与,ORACLEID,中的,Collection_id,一致。,2,、目录,capture,和,camap,需要手动创建。,服务分开部署,SQL*Net,访问远程,Oracle,安装配置要点,环境 三,(2),：服务分开部署用,PWX Listener,访问远程,Oracle,Registration,Data Maps,Informatica,PowerCenter,Informatica,PowerExchange,PWX,Listener,PWXPC,Oracle,Oracle,Logs,LogMiner,DataRecords,PowerCenter Client ,PWX, PWXPC,主机,Server_A,为,Oracle Server,。,PWX Listener,必需安装。,主机,Server_B,是,PowerCenter Server,。,PWX,必需安装,(listener,不需要配置,),Server_A: 10.1.1.100,Server_B: 10.2.2.200,PowerCenter,首先被安装。,终端上安装,PWX Navigator,。安装,PWX,软件，修改终端上的,dbmover.cfg,。,NODE=(Server_A,TCPIP,10.1.1.100,2480) /*,连接,Server_A,Server_A,上安装,PWX,软件，修改,Server_A,上的,dbmover.cfg,。,ORACLEID=(,Collection-Id,OracleSID,Connect_string,Capture_connect_string),CAPT_XTRA=,C:Program FilesInformaticaInformatica PowerExchangecapturecamaps,CAPT_PATH=,C:Program FilesInformaticaInformatica PowerExchangecapture,CAPI_CONN_NAME=,CAPIUOWC,CAPI_CONNECTION=(NAME=,CAPIUOWC,TYPE=(UOWC,CAPINAME=,CAPIORA,),CAPI_CONNECTION=(NAME=,CAPIORA,TYPE=(ORCL,REPNODE=local,ORACOLL=,Collection-Id,),Server_B,上安装,PWX,软件，修改,Server_B,上的,dbmover.cfg,NODE=(Server_A,TCPIP,10.1.1.100,2480) /*,连接,Server_A,服务分开部署,PWX Listener,访问远程,Oracle,安装配置要点,创建和指定归档日志存放路径,创建针对,PowerExchange,使用的,oralce,用户并赋指定权限,设置,oracle,的,Supplemental logging,创建,LogMiner,表空间,编译,LogMiner,系统包,将,Oracle catalog,复制到,redo logs,CONNECT SYS/CHANGE_ON_INSTALL AS SYSDBA;,STARTUP;,ALTER SYSTEM SET log_archive_dest_1 =,location=C:oracle,archlogs,SCOPE=SPFILE;,CREATE USER ORACAPT PROFILE DEFAULT IDENTIFIED BY oracapt ACCOUNT UNLOCK;,GRANT CONNECT TO ORACAPT;,GRANT SELECT ON PUBLIC.V$INSTANCE TO ORACAPT;,GRANT SELECT ON PUBLIC.V$DATABASE TO ORACAPT;,GRANT SELECT ON SYS.DBA_LOG_GROUPS TO ORACAPT;,GRANT SELECT ON SYS.DBA_LOG_GROUP_COLUMNS TO ORACAPT;,GRANT SELECT ON PUBLIC.V$TRANSACTION TO ORACAPT;,GRANT SELECT ON PUBLIC.V$ARCHIVED_LOG TO ORACAPT;,GRANT SELECT ON PUBLIC.V$LOGMNR_CONTENTS TO ORACAPT;,GRANT SELECT ON PUBLIC.V$PARAMETER TO ORACAPT;,- The following grant is required for Oracle V10 or greater:,- GRANT SELECT ANY TRANSACTION TO ORACAPT;,GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO ORACAPT;,GRANT EXECUTE ON SYS.DBMS_LOGMNR_D TO ORACAPT;,GRANT EXECUTE ON SYS.DBMS_LOGMNR TO ORACAPT;,GRANT ALTER ANY TABLE TO ORACAPT;,COMMIT;,ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;,COMMIT;,CREATE TABLESPACE LOGMNRTS NOLOGGING,DATAFILE,C:oracleproduct10.2.0oradataorclLOGMNRTS.ora,SIZE 50M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 100M EXTENT MANAGEMENT LOCAL;,EXECUTE SYS.DBMS_LOGMNR_D.SET_TABLESPACE(LOGMNRTS);,ALTER PACKAGE SYS.DBMS_LOGMNR_D COMPILE BODY;,begin,SYS.DBMS_LOGMNR_D.BUILD(options = sys.dbms_logmnr_d.store_in_redo_logs);,end;,Oracle,设置要点,体系结构,安装介绍,注册数据源,PWX,与,PC,集成,学 习 内 容,Resources Data Capture,Data Capture,只能被用做实现,Change Data Capture,Application Groups,显示被抽取的信息，并支持用户,reset,下次抽取进程的时间。（数据库连接信息，抽取开始、结束时间等。）,Extraction Groups,包含被指定注册表的中被抽取的表,/,字段。,Registration Groups,所包含仅为,Change Data Capture,的注册表的信息。,说明,:,增加,Registration Group,会自动创建,Extraction,和,Application groups,。,输入,Registration Group,逻辑名,选择要在,Registration Group,中注册的服务器。,Location,下拉框中列出的是,Client,端,dbmover.cfg,配置文件中配置的所有节点,。,添加,Registration Group,选择数据源类型,输入被选数据源的用户名和密码,输入在,DBMOVER.CFG,中定义的连接名。,必须与,Listener,端,dbmover.cfg,中的,collection_id,一致。,如果想在添加,Registration Group,之后马上添加,Capture Registration,，则勾选复选框。如果清除复选框的勾选，“下一步”按钮会自动变为“完成”按钮。一个,Registration Group,下可以有多个,Capture Registration,。,添加,Capture Registration-,表和字段信息,输入表的逻辑名,输入表的,owner,输入表名,双击表名得到表字段,list,勾选希望监控的列。其中带有,key,标志的列是该表的索引列。,如果被监测的表没有任何索引列，则所有的列都需捕获。,添加,Capture Registration-CDC,信息,选择,CDC,状态。,Acitve,，,Registration,会立即生效；,Inactive,，所定义的,Registration,不会被,Collection agent,立即使用。,Condense,：设置是否生成压缩文件保留对源库数据行提交的所有更新。,None = Real-time;,Part = Change,设置,Supplemental Log,的名称，在本步骤中，系统会自动生成设置,Supplemental Logging,的,DDL,。,勾选,，系统会在当前步骤完成时立即执行所产生的,DDL,。,清除勾选,，用户需要手动执行相应的,DDL,文件。在执行实时监控之前，必须运行该,DDL,以设置,Supplemental Logging,，否则实时捕获将不起作用。,在本地保存执行的,DDL,。,一旦保存,DDL,后,该,DDL,将被执行在指定表上启动,Supplement Logging,。,添加,Capture Registration-,执行,DDL,Extraction Definition-Row Test,使用,Row Test,功能可以随机查询出一个,Extraction Group,的变化数据。,DTL_,字段提供一组信息，如什么时间，什么地方，被谁执行过怎么的操作等信,息，和实际数据一起显示。,Extraction Definition-Row Test,DTL_CAPXACTION,值显示记录是否被,Insert,、,Update,、,Delete,。,该选项影响被数据抽取的方式。,说明被抽取的信息。,Extraction Definition-Advanced Options,设置执行,Logminer,的超时时间，不是,Logminer,的初始化时间。,从定义,oracle,信息：,Oracle Instance:,Oracle Connection String:,Oracle Schema:,Extract,：,SL,、,RS,。,SL,：抽取从上次抽取之后的所有数据。,RS,：重复抽取上一次的抽取。,Image,：,BA,、,AI,、,TU,。,BA,：以,Update,、,Delete,、,Insert,记录的方,式捕获修改前和修改后的值。,AI,：只捕获最近一次修改的修改后的值。,TU,：捕获修改前和修改后的值，,用,indicator,标识变更类型。,体系结构,安装介绍,注册数据源,PWX,与,PC,集成,学 习 内 容,PowerExchange,与,PowerCenter,的集成是通过,PowerExchange Interfaces for PowerCenter,完成。在运行,PowerCenter,的机器上安装,PowerExchange,之后，,PowerExchange Interfaces for PowerCenter,会被自动安装，无需单独安装。,概 述,PowerExchange Interfaces for PowerCenter,分为两种：,PowerExchange Client for PowerCenter(PWXPC),：是完全集成到,PowerCenter,中的本地插件。,PowerExchange (PWX) ODBC,：,PowerCenter,对,PowerExchange,的,Original,Interface,，使用的是,ODBC,连接。,PWXPC,和,ODBC,接口的功能对比,功 能,PWXPC,ODBC,Relational connections,Yes,Yes,Application connections,Yes,No,Can use the same mapping for batch and CDC (Change or Real-time),Yes,No,Automatically processes change indicator (Insert/Update/Delete) without an Update Strategy,Yes,No,定义变更数据源和,Mapping,配置信息描述：,Location,：,列出,Client,端配置文件中配置的所有节,点，从中选择要监控的数据库所在的,节点；,User name,和,Password,：,非必输项，连接被监测数,据库的用户名和密码；,Source Type,：,选择“,Oracle”,；,CDC Datamaps,：,必须勾选；,Schema,和,Map Name,：,非必输项，与,Navigator,中,Extraction Definition,的,Schema,和,Map Name,相对,应，输入后可以过滤出符,合条件的,Extraction,Definition,，支持通配符“,?”,和“*”。,定义连接和,Workflow,在,Connection Object Definition,界面中，需要对,Connection,的如下属性进行定义：,Name,：必输项，为连接指定唯一的名称；,Code Page,：必输项，所连接数据库的代码页；,Location,：必输项，所连接数据库的节点名，即在,PowerExchange,配置文件,dbmover.cfg,中设置,的,node,的名称；,User Name,、,Password,：可选项，连接数据库的用户名和,密码；,Image Type,：可选项，提供的选项有：,AI,、,BA,。,AI,：只抽取修改后的值；,BA,：抽取修改前和修改后的值。缺省为,AI,。,Encryption Type,：必输项，加密类型，提供的选项有：,None,、,RC2,、,DES,。缺省为,None,。,Pacing Size,：必输项。,Pacing Size,决定了从,Listener,传递到,Source,或,Target,的数据量。在,session,中，如果,外部应用、数据库或,PowerCenter,的,Integration Service,节点是性能瓶颈，则需要,设置,Pacing Size,来降低数据传递的速率。,缺省为,0,。,点击“,OK”,保存配置。,Thank you !,Q & A,