Oracle GoldenGate学习之--基本概念和配置（3）

Oracle GoldenGate学习之--基本概念和配置（3）

系统架构：

OGG基本配置（单向传输）

1、数据库配置（Source DB)

建立Tablespace：11:58:56 SYS@ prod >create tablespace ogg_tbs11:59:12   2  datafile '/u01/app/oracle/oradata/prod/oggtbs1.dbf' size 100m ;建立ogg用户：12:01:17 SYS@ prod >create user ogg identified by ogg default tablespace ogg_tbs temporary tablespace temp quota unlimited on ogg_tbs;User created.用户授权：12:01:39 SYS@ prod >grant connect,resource to ogg;Grant succeeded.12:01:55 SYS@ prod >grant create session,alter session to ogg;Grant succeeded.12:02:18 SYS@ prod >grant select any dictionary,select any table to ogg;Grant succeeded.12:02:42 SYS@ prod >grant alter any table to ogg;Grant succeeded.12:02:53 SYS@ prod >grant flashback any table to ogg;Grant succeeded.12:03:03 SYS@ prod >grant execute on dbms_flashback to ogg;Grant succeeded.在Database上启用附加日志功能：12:19:26 SYS@ prod >select SUPPLEMENTAL_LOG_DATA_MIN from v$database;SUPPLEME--------NO12:20:53 SYS@ prod >alter database add supplemental log data;Database altered.数据库应为归档模式：12:21:10 SYS@ prod >archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            /dsk4/arch_prodOldest online log sequence     78Next log sequence to archive   80Current log sequence           8013:22:45 SYS@ prod >select force_logging from v$database;FOR---NO13:24:22 SYS@ prod >alter database force logging;Database altered.13:24:30 SYS@ prod >select force_logging from v$database;FOR---YES建立用于传输测试的Table：12:12:26 SCOTT@ prod >create table emp_ogg as select * from  emp;Table created.12:12:40 SCOTT@ prod >alter table emp_ogg add constraint pk_ogg primary key(empno);注意：Oracle建议给所有要复制的表建立主键或唯一键

2、数据库配置（Target DB)

建立Tablespace：12:07:50 SYS@ prod >create tablespace ogg_tbs12:08:09   2  datafile '/u01/app/oracle/oradata/prod/oggtbs1.dbf' size 100m;建立ogg用户：12:08:47 SYS@ prod >create user ogg identified by ogg default tablespace ogg_tbs temporary tablespace temp quota unlimited on ogg_tbs;User created.用户授权：12:09:44 SYS@ prod >grant connect,resource to ogg;Grant succeeded.12:10:06 SYS@ prod >grant create session ,alter session to ogg;Grant succeeded.12:10:20 SYS@ prod >grant select any table,select any dictionary to ogg;Grant succeeded.12:10:45 SYS@ prod >grant create table to ogg;创建测试Table：12:13:47 SCOTT@ prod >create table emp_ogg as select * from  emp;Table created.12:14:04 SCOTT@ prod >grant all on emp_ogg to ogg；Grant succeeded.

3、OGG配置（source）

配置MGR:[oracle@ogg ogg]$ ./ggsciOracle GoldenGate Command Interpreter for OracleVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBOLinux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.添加用于capture data change的tableGGSCI (ogg) 2> dblogin userid ogg,password ogg;Successfully logged into database.GGSCI (ogg) 3> add trandata scott.emp_ogg;ERROR: No viable tables matched specification.goldengate add trandata 提示找不到表  ERROR: No viable tables matched specification goldengate对符号比较敏感，在add trandata时不要用分号 GGSCI (ogg) 4> add trandata scott.emp_ogg2014-09-10 14:00:25  WARNING OGG-00869  No unique key is defined for table 'EMP_OGG'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.Logging of supplemental redo data enabled for table SCOTT.EMP_OGG.注意：提示table：emp_ogg未建立primary key，建立primary key后重新addGGSCI (ogg) 8> delete trandata scott.emp_oggLogging of supplemental redo log data disabled for table SCOTT.EMP_OGG.GGSCI (ogg) 9> add trandata scott.emp_oggLogging of supplemental redo data enabled for table SCOTT.EMP_OGG.查看在table上是否启用了supplemental logGGSCI (ogg) 11> info trandata scott.emp_oggLogging of supplemental redo log data is enabled for table SCOTT.EMP_OGG.Columns supplementally logged for table SCOTT.EMP_OGG: EMPNO.GGSCI (ogg) 13> edit params mgrport 7809dynamicportlist 7800-8000autorestart extract *,waitminutes 2,resetminutes 5编辑mgr参数文件，并写入以上信息！GGSCI (ogg) 14> start mgrManager started.GGSCI (ogg) 16> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING

配置Extract：添加extract服务，并启动extract服务：GGSCI (ogg) 11> add extract eini_1,sourceistableEXTRACT added.校验Extract processGGSCI (ogg) 14> info extract *,tasksEXTRACT    EINI_1    Initialized   2014-09-10 14:28   Status STOPPEDCheckpoint Lag       Not AvailableLog Read Checkpoint  Not Available                     First Record         Record 0Task                 SOURCEISTABLE编辑extract参数文件GGSCI (ogg) 18>EDIT PARAMS EINI_1EXTRACT EINI_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)USERID ogg, PASSWORD oggRMTHOST 192.168.8.249,MGRPORT 7809RMTTASK REPLICAT, GROUP RINI_1TABLE scott.EMP_OGG;GGSCI (ogg) 15> start eini_1Sending START request to MANAGER ...EXTRACT EINI_1 startingGGSCI (ogg) 16> info eini_1EXTRACT    EINI_1    Initialized   2014-09-10 14:28   Status RUNNINGCheckpoint Lag       Not AvailableLog Read Checkpoint  Not Available                     First Record         Record 0Task                 SOURCEISTABLEGGSCI (ogg) 17> view report eini_12014-09-10 14:30:34  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.***********************************************************************                 Oracle GoldenGate Capture for Oracle    Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO   Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:20:38Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.                    Starting at 2014-09-10 14:30:34***********************************************************************Operating System Version:LinuxVersion #1 SMP Wed Sep 1 01:26:34 EDT 2010, Release 2.6.32-71.el6.i686Node: oggMachine: i686                         soft limit   hard limitAddress Space Size   :    unlimited    unlimitedHeap Size            :    unlimited    unlimitedFile Size            :    unlimited    unlimitedCPU Time             :    unlimited    unlimitedProcess id: 6159Description:*************************************************************************            Running with the following parameters                  *************************************************************************2014-09-10 14:30:34  INFO    OGG-03035  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.EXTRACT EINI_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)USERID ogg,PASSWORD ***RMTHOST 192.168.8.249,MGRPORT 7809RMTTASK REPLICAT,GROUP RINI_1TABLE scott.EMP_OGG;Using the following key columns for source table SCOTT.EMP_OGG: EMPNO.2014-09-10 14:30:36  INFO    OGG-01815  Virtual Memory Facilities for: COM    anon alloc: mmap(MAP_ANON)  anon free: munmap    file alloc: mmap(MAP_SHARED)  file free: munmap    target directories:    /u01/ogg/dirtmp.CACHEMGR virtual memory values (may have been adjusted)CACHESIZE:                                2GCACHEPAGEOUTSIZE (normal):                4MPROCESS VM AVAIL FROM OS (min):        2.75GCACHESIZEMAX (strict force to disk):   2.47GDatabase Version:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE    11.2.0.1.0      ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - ProductionDatabase Language and Character Set:NLS_LANG         = "AMERICAN_AMERICA.ZHS16GBK"NLS_LANGUAGE     = "AMERICAN"NLS_TERRITORY    = "AMERICA"NLS_CHARACTERSET = "ZHS16GBK"2014-09-10 14:30:36  WARNING OGG-01223  TCP/IP error 111 (Connection refusSource Context :  SourceModule            : [er.idlx]  SourceID                : [/net/ap1109nap-vlan900/vol/ifarm_ports/ifarm_views/aime_oggcore_210874/oggcore/OpenSys/src/app/er/idlx.c]  SourceFunction          : [idlx_tcp_error]  SourceLine              : [1414]  ThreadBacktrace         : [10] elements                          : [/u01/ogg/libgglog.so(CMessageContext::AddThreadContext()+0x26) [0x1f9166]]                          : [/u01/ogg/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x2c5) [0x1f5125]]                          : [/u01/ogg/libgglog.so(_MSG_ERR_TCP_GENERIC(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x36) [0x1d755c]]                          : [/u01/ogg/extract() [0x8148d0c]]                          : [/u01/ogg/extract(IDLX_tcp_send_data+0xf4) [0x8149ae0]]                          : [/u01/ogg/extract(IDLX_send_session_begin_cmd+0xa6) [0x8149e12]]                          : [/u01/ogg/extract(process_source_files()+0x584) [0x819662e]]                          : [/u01/ogg/extract(main+0x5fc) [0x819820c]]                          : [/lib/libc.so.6(__libc_start_main+0xe6) [0x8aecc6]]                          : [/u01/ogg/extract(__gxx_personality_v0+0x3ad) [0x81144e1]]2014-09-10 14:32:35  ERROR   OGG-01224  TCP/IP error 111 (Connection refused); retries exceeded.2014-09-10 14:32:35  ERROR   OGG-01668  PROCESS ABENDING.

注意：出现以上tcp/ip错误，是因为目标端未配置mgr，无法通讯！

源端配置extract进程，用于数据同步：GGSCI (ogg) 25> edit params eora_1EXTRACT EORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)USERID ogg,PASSWORD ogg EXTTRAIL./dirdat/aaTABLE scott.emp_ogg;添加extract primary groupGGSCI (ogg) 27> add extract eora_1,tranlog,begin nowEXTRACT added.定义goldengate local trail注意：'aa' is prefix for local trail fileGGSCI (ogg) 29> add exttrail ./dirdat/aa,extract eora_1,megabytes 5EXTTRAIL added.GGSCI (ogg) 30> start extract eora_1Sending START request to MANAGER ...EXTRACT EORA_1 startingGGSCI (ogg) 31> info extract eora_1EXTRACT    EORA_1    Initialized   2014-09-10 14:47   Status STOPPEDCheckpoint Lag       00:00:00 (updated 00:02:06 ago)Log Read Checkpoint  Oracle Redo Logs                     2014-09-10 14:47:22  Seqno 0, RBA 0                     SCN 0.0 (0)                     在源端配置pump进程，用于数据同步：                    GGSCI (ogg) 41> edit params pora_1EXTRACT PORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)PASSTHRURMTHOST 192.168.8.249,MGRPORT 7809RMTTRAIL./dirdat/paTABLE scott.emp_ogg;添加pump extract groupGGSCI (ogg) 35> add extract pora_1,exttrailsource ./dirdat/paEXTRACT added.添加remote trail file在source systemggsci> ADD RMTTRAIL ./dirdat/pa,EXTRACT PORA_1, MEGABYTES 5RMTTRAIL added.GGSCI (ogg) 36> info extract pora_1EXTRACT    PORA_1    Initialized   2014-09-10 15:01   Status STOPPEDCheckpoint Lag       00:00:00 (updated 00:00:22 ago)Log Read Checkpoint  File ./dirdat/pa000000                     First Record  RBA 0                     GGSCI (ogg) 42> start extract pora_1Sending START request to MANAGER ...EXTRACT PORA_1 startingGGSCI (ogg) 43> info extract pora_1EXTRACT    PORA_1    Initialized   2014-09-10 15:01   Status STOPPEDCheckpoint Lag       00:00:00 (updated 00:03:35 ago)Log Read Checkpoint  File ./dirdat/pa000000                     First Record  RBA 0以上eora_1和pora_1服务都处于stop状态，正常应为running，通过以下方式进行纠正；解决方法：GGSCI (rh7.cuug.net) 7> delete extract eora_1,tranlog,begin now2014-09-10 16:16:15  WARNING OGG-01753  Cannot unregister EXTRACT EORA_1 from database because no database login was provided. You can manually unregister this group later with the UNREGISTER EXTRACT command with LOGRETENTION. Issue DBLOGIN first.Deleted EXTRACT EORA_1.GGSCI (rh7.cuug.net) 8> dblogin userid ogg,password oggSuccessfully logged into database.GGSCI (rh7.cuug.net) 9>  delete extract eora_1,tranlog,begin nowERROR: EXTRACT EORA_1 does not exist.GGSCI (rh7.cuug.net) 10> add extract eora_1,tranlog,begin nowEXTRACT added.GGSCI (rh7.cuug.net) 11> add exttrail ./dirdat/aa ,extract eora_1,megabytes 5EXTTRAIL added.GGSCI (rh7.cuug.net) 12> start extract eora_1Sending START request to MANAGER ...EXTRACT EORA_1 startingGGSCI (rh7.cuug.net) 13> info extract eora_1EXTRACT    EORA_1    Last Started 2014-09-10 16:17   Status RUNNINGCheckpoint Lag       00:00:54 (updated 00:00:07 ago)Log Read Checkpoint  Oracle Redo Logs                     2014-09-10 16:16:51  Seqno 80, RBA 42201104                     SCN 0.0 (0)                     GGSCI (rh7.cuug.net) 27> edit params pora_1RMTTRAILEXTRACT PORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)PASSTHRURMTHOST 192.168.8.249,MGRPORT 7809RMTTRAIL ./dirdat/paTABLE scott.emp_ogg;~GGSCI (rh7.cuug.net) 28> start extract pora_1Sending START request to MANAGER ...EXTRACT PORA_1 startingGGSCI (rh7.cuug.net) 29> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNINGEXTRACT     RUNNING     EORA_1      00:00:00      00:00:04EXTRACT     RUNNING     PORA_1      00:00:00      00:03:18

@至此，Source端，Extract、Pump服务配置基本完毕 ！

4、OGG配置（Target）

配置MGRGGSCI (rh7.cuug.net) 4> edit params mgrport 7809dynamicportlist 7800-8000autorestart extract *,waitminutes 2,resetminutes 5~GGSCI (rh7.cuug.net) 5> start mgrManager started.GGSCI (rh7.cuug.net) 6> info mgrManager is running (IP port rh7.cuug.net.7809).配置Replicate服务[oracle@rh7 ogg]$ ./ggsciOracle GoldenGate Command Interpreter for OracleVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBOLinux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.GGSCI (rh7.cuug.net) 1> add replicat rini_1,specialrunREPLICAT added.GGSCI (rh7.cuug.net) 2> info replicat *,tasksREPLICAT   RINI_1    Initialized   2014-09-10 14:33   Status STOPPEDCheckpoint Lag       00:00:00 (updated 00:00:13 ago)Log Read Checkpoint  Not AvailableTask                 SPECIALRUNGGSCI (rh7.cuug.net) 3> edit params rini_1REPLICAT RINI_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)ASSUMETARGETDEFSUSERID ogg,PASSWOR oggDISCARDFILE ./dirrpt/RINIaa.dsc,PURGEMAP scott.emp_ogg,TARGET scott.emp_ogg;注意：在源端启动eini_1 process，目标端rini_1 process将会被自动启动添加replicate checkpoint tableGGSCI (rh7.cuug.net) 1> edit params ./GLOBALSCHECKPOINTTABLE ogg.oggchkpt~GGSCI (rh7.cuug.net) 2> exit必须退出ggsci，checkpoint table 才会生效[oracle@rh7 ogg]$ ./ggsciOracle GoldenGate Command Interpreter for OracleVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBOLinux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.GGSCI (rh7.cuug.net) 1> dblogin userid ogg,password oggSuccessfully logged into database.GGSCI (rh7.cuug.net) 2> add checkpointtableNo checkpoint table specified, using GLOBALS specification (ogg.oggchkpt)...Successfully created checkpoint table ogg.oggchkpt.添加replicate groupGGSCI (rh7.cuug.net) 4> add replicat rora_1,exttrail ./dirdat/paREPLICAT added.编辑replicate parameterfileGGSCI (rh7.cuug.net) 5> edit param rora_1REPLICAT RORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)USERID ogg,PASSWORD oggHANDLECOLLISIONSASSUMETARGETDEFSDISCARDFILE ./dirrpt/RORA_aa.DSC,PURGEMAP scott.oem_ogg,TARGET scott.emp_ogg;启动replicate processGGSCI (rh7.cuug.net) 7> start replicat rora_1Sending START request to MANAGER ...REPLICAT RORA_1 startingGGSCI (rh7.cuug.net) 10> info replicat rora_1REPLICAT   RORA_1    Last Started 2014-09-10 15:53   Status RUNNINGCheckpoint Lag       00:00:00 (updated 00:00:05 ago)Log Read Checkpoint  File ./dirdat/pa000000                     First Record  RBA 0                     GGSCI (rh7.cuug.net) 9> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNINGREPLICAT    RUNNING     RORA_1      00:00:00      00:00:00

5、同步测试

source：16:27:33 SCOTT@ prod >select * from emp_ogg;     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------      7369 SMITH      CLERK           7902 17-DEC-80        800                    20      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30      7566 JONES      MANAGER         7839 02-APR-81       2975                    20      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20      7839 KING       PRESIDENT            17-NOV-81       5000                    10      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20      7900 JAMES      CLERK           7698 03-DEC-81        950                    30      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20      7934 MILLER     CLERK           7782 23-JAN-82       1300                    1014 rows selected.16:27:41 SCOTT@ prod >update emp_ogg set deptno=40 where empno=7788;1 row updated.16:28:06 SCOTT@ prod >commit;Commit complete.

target：16:26:31 SCOTT@ prod >truncate table emp_ogg;Table truncated.16:26:44 SCOTT@ prod >select * from emp_ogg;no rows selected16:28:23 SCOTT@ prod >/     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------      7369 SMITH      CLERK           7902 17-DEC-80        800                    20      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30      7566 JONES      MANAGER         7839 02-APR-81       2975                    20      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    40      7839 KING       PRESIDENT            17-NOV-81       5000                    10      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20      7900 JAMES      CLERK           7698 03-DEC-81        950                    30      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20      7934 MILLER     CLERK           7782 23-JAN-82       1300                    1014 rows selected.

@至此，目标端同源端的数据同步成功，OGG的单向传输基本配置完成！