OracleGoldenGate學(xué)習(xí)之--基本概念和配置（3）
Oracle GoldenGate學(xué)習(xí)之--基本概念和配置（3）
網(wǎng)站設(shè)計(jì)、成都網(wǎng)站設(shè)計(jì)的開發(fā)，更需要了解用戶，從用戶角度來建設(shè)網(wǎng)站，獲得較好的用戶體驗(yàn)。創(chuàng)新互聯(lián)多年互聯(lián)網(wǎng)經(jīng)驗(yàn)，見的多，溝通容易、能幫助客戶提出的運(yùn)營建議。作為成都一家網(wǎng)絡(luò)公司，打造的就是網(wǎng)站建設(shè)產(chǎn)品直銷的概念。選擇創(chuàng)新互聯(lián)，不只是建站，我們把建站作為產(chǎn)品，不斷的更新、完善，讓每位來訪用戶感受到浩方產(chǎn)品的價(jià)值服務(wù)。
系統(tǒng)架構(gòu)：
OGG基本配置（單向傳輸）
1、數(shù)據(jù)庫配置（Source DB)
建立Tablespace： 11:58:56 SYS@ prod >create tablespace ogg_tbs 11: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. 用戶授權(quán)： 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 -------- NO 12:20:53 SYS@ prod >alter database add supplemental log data; Database altered. 數(shù)據(jù)庫應(yīng)為歸檔模式： 12:21:10 SYS@ prod >archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /dsk4/arch_prod Oldest online log sequence 78 Next log sequence to archive 80 Current log sequence 80 13:22:45 SYS@ prod >select force_logging from v$database; FOR --- NO 13: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建議給所有要復(fù)制的表建立主鍵或唯一鍵
2、數(shù)據(jù)庫配置（Target DB)
建立Tablespace： 12:07:50 SYS@ prod >create tablespace ogg_tbs 12: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. 用戶授權(quán)： 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; 創(chuàng)建測試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]$ ./ggsci Oracle GoldenGate Command Interpreter 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:09:25 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. 添加用于capture data change的table GGSCI (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對符號(hào)比較敏感，在add trandata時(shí)不要用分號(hào) GGSCI (ogg) 4> add trandata scott.emp_ogg 2014-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后重新add GGSCI (ogg) 8> delete trandata scott.emp_ogg Logging of supplemental redo log data disabled for table SCOTT.EMP_OGG. GGSCI (ogg) 9> add trandata scott.emp_ogg Logging of supplemental redo data enabled for table SCOTT.EMP_OGG. 查看在table上是否啟用了supplemental log GGSCI (ogg) 11> info trandata scott.emp_ogg Logging 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 mgr port 7809 dynamicportlist 7800-8000 autorestart extract *,waitminutes 2,resetminutes 5 編輯mgr參數(shù)文件，并寫入以上信息！ GGSCI (ogg) 14> start mgr Manager started. GGSCI (ogg) 16> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING
配置Extract： 添加extract服務(wù)，并啟動(dòng)extract服務(wù)： GGSCI (ogg) 11> add extract eini_1,sourceistable EXTRACT added. 校驗(yàn)Extract process GGSCI (ogg) 14> info extract *,tasks EXTRACT EINI_1 Initialized 2014-09-10 14:28 Status STOPPED Checkpoint Lag Not Available Log Read Checkpoint Not Available First Record Record 0 Task SOURCEISTABLE 編輯extract參數(shù)文件 GGSCI (ogg) 18>EDIT PARAMS EINI_1 EXTRACT EINI_1 SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) USERID ogg, PASSWORD ogg RMTHOST 192.168.8.249,MGRPORT 7809 RMTTASK REPLICAT, GROUP RINI_1 TABLE scott.EMP_OGG; GGSCI (ogg) 15> start eini_1 Sending START request to MANAGER ... EXTRACT EINI_1 starting GGSCI (ogg) 16> info eini_1 EXTRACT EINI_1 Initialized 2014-09-10 14:28 Status RUNNING Checkpoint Lag Not Available Log Read Checkpoint Not Available First Record Record 0 Task SOURCEISTABLE GGSCI (ogg) 17> view report eini_1 2014-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:38 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. Starting at 2014-09-10 14:30:34 *********************************************************************** Operating System Version: Linux Version #1 SMP Wed Sep 1 01:26:34 EDT 2010, Release 2.6.32-71.el6.i686 Node: ogg Machine: i686 soft limit hard limit Address Space Size : unlimited unlimited Heap Size : unlimited unlimited File Size : unlimited unlimited CPU Time : unlimited unlimited Process id: 6159 Description: *********************************************************************** ** 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_1 SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) USERID ogg,PASSWORD *** RMTHOST 192.168.8.249,MGRPORT 7809 RMTTASK REPLICAT,GROUP RINI_1 TABLE 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: 2G CACHEPAGEOUTSIZE (normal): 4M PROCESS VM AVAIL FROM OS (min): 2.75G CACHESIZEMAX (strict force to disk): 2.47G Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production Database 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 refus Source 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) [0 x1d755c]] : [/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.
注意：出現(xiàn)以上tcp/ip錯(cuò)誤，是因?yàn)槟繕?biāo)端未配置mgr，無法通訊！
源端配置extract進(jìn)程，用于數(shù)據(jù)同步： GGSCI (ogg) 25> edit params eora_1 EXTRACT EORA_1 SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) USERID ogg,PASSWORD ogg EXTTRAIL ./dirdat/aa TABLE scott.emp_ogg; 添加extract primary group GGSCI (ogg) 27> add extract eora_1,tranlog,begin now EXTRACT added. 定義goldengate local trail 注意：‘a(chǎn)a’ is prefix for local trail file GGSCI (ogg) 29> add exttrail ./dirdat/aa,extract eora_1,megabytes 5 EXTTRAIL added. GGSCI (ogg) 30> start extract eora_1 Sending START request to MANAGER ... EXTRACT EORA_1 starting GGSCI (ogg) 31> info extract eora_1 EXTRACT EORA_1 Initialized 2014-09-10 14:47 Status STOPPED Checkpoint 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進(jìn)程，用于數(shù)據(jù)同步： GGSCI (ogg) 41> edit params pora_1 EXTRACT PORA_1 SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) PASSTHRU RMTHOST 192.168.8.249,MGRPORT 7809 RMTTRAIL ./dirdat/pa TABLE scott.emp_ogg; 添加pump extract group GGSCI (ogg) 35> add extract pora_1,exttrailsource ./dirdat/pa EXTRACT added. 添加remote trail file在source system ggsci> ADD RMTTRAIL ./dirdat/pa,EXTRACT PORA_1, MEGABYTES 5 RMTTRAIL added. GGSCI (ogg) 36> info extract pora_1 EXTRACT PORA_1 Initialized 2014-09-10 15:01 Status STOPPED Checkpoint 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_1 Sending START request to MANAGER ... EXTRACT PORA_1 starting GGSCI (ogg) 43> info extract pora_1 EXTRACT PORA_1 Initialized 2014-09-10 15:01 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:03:35 ago) Log Read Checkpoint File ./dirdat/pa000000 First Record RBA 0 以上eora_1和pora_1服務(wù)都處于stop狀態(tài)，正常應(yīng)為running，通過以下方式進(jìn)行糾正； 解決方法： GGSCI (rh7.cuug.net) 7> delete extract eora_1,tranlog,begin now 2014-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 ogg Successfully logged into database. GGSCI (rh7.cuug.net) 9> delete extract eora_1,tranlog,begin now ERROR: EXTRACT EORA_1 does not exist. GGSCI (rh7.cuug.net) 10> add extract eora_1,tranlog,begin now EXTRACT added. GGSCI (rh7.cuug.net) 11> add exttrail ./dirdat/aa ,extract eora_1,megabytes 5 EXTTRAIL added. GGSCI (rh7.cuug.net) 12> start extract eora_1 Sending START request to MANAGER ... EXTRACT EORA_1 starting GGSCI (rh7.cuug.net) 13> info extract eora_1 EXTRACT EORA_1 Last Started 2014-09-10 16:17 Status RUNNING Checkpoint 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_1 RMTTRAIL EXTRACT PORA_1 SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) PASSTHRU RMTHOST 192.168.8.249,MGRPORT 7809 RMTTRAIL ./dirdat/pa TABLE scott.emp_ogg; ~ GGSCI (rh7.cuug.net) 28> start extract pora_1 Sending START request to MANAGER ... EXTRACT PORA_1 starting GGSCI (rh7.cuug.net) 29> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EORA_1 00:00:00 00:00:04 EXTRACT RUNNING PORA_1 00:00:00 00:03:18
@至此，Source端，Extract、Pump服務(wù)配置基本完畢 ！
4、OGG配置（Target）
配置MGR GGSCI (rh7.cuug.net) 4> edit params mgr port 7809 dynamicportlist 7800-8000 autorestart extract *,waitminutes 2,resetminutes 5 ~ GGSCI (rh7.cuug.net) 5> start mgr Manager started. GGSCI (rh7.cuug.net) 6> info mgr Manager is running (IP port rh7.cuug.net.7809). 配置Replicate服務(wù) [oracle@rh7 ogg]$ ./ggsci Oracle GoldenGate Command Interpreter 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:09:25 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (rh7.cuug.net) 1> add replicat rini_1,specialrun REPLICAT added. GGSCI (rh7.cuug.net) 2> info replicat *,tasks REPLICAT RINI_1 Initialized 2014-09-10 14:33 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:00:13 ago) Log Read Checkpoint Not Available Task SPECIALRUN GGSCI (rh7.cuug.net) 3> edit params rini_1 REPLICAT RINI_1 SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) ASSUMETARGETDEFS USERID ogg,PASSWOR ogg DISCARDFILE ./dirrpt/RINIaa.dsc,PURGE MAP scott.emp_ogg,TARGET scott.emp_ogg; 注意：在源端啟動(dòng)eini_1 process，目標(biāo)端rini_1 process將會(huì)被自動(dòng)啟動(dòng) 添加replicate checkpoint table GGSCI (rh7.cuug.net) 1> edit params ./GLOBALS CHECKPOINTTABLE ogg.oggchkpt ~ GGSCI (rh7.cuug.net) 2> exit 必須退出ggsci，checkpoint table 才會(huì)生效 [oracle@rh7 ogg]$ ./ggsci Oracle GoldenGate Command Interpreter 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:09:25 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (rh7.cuug.net) 1> dblogin userid ogg,password ogg Successfully logged into database. GGSCI (rh7.cuug.net) 2> add checkpointtable No checkpoint table specified, using GLOBALS specification (ogg.oggchkpt)... Successfully created checkpoint table ogg.oggchkpt. 添加replicate group GGSCI (rh7.cuug.net) 4> add replicat rora_1,exttrail ./dirdat/pa REPLICAT added. 編輯replicate parameterfile GGSCI (rh7.cuug.net) 5> edit param rora_1 REPLICAT RORA_1 SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) USERID ogg,PASSWORD ogg HANDLECOLLISIONS ASSUMETARGETDEFS DISCARDFILE ./dirrpt/RORA_aa.DSC,PURGE MAP scott.oem_ogg,TARGET scott.emp_ogg; 啟動(dòng)replicate process GGSCI (rh7.cuug.net) 7> start replicat rora_1 Sending START request to MANAGER ... REPLICAT RORA_1 starting GGSCI (rh7.cuug.net) 10> info replicat rora_1 REPLICAT RORA_1 Last Started 2014-09-10 15:53 Status RUNNING Checkpoint 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 all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT 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 10 14 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 selected 16: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 10 14 rows selected.
@至此，目標(biāo)端同源端的數(shù)據(jù)同步成功，OGG的單向傳輸基本配置完成！
新聞名稱：OracleGoldenGate學(xué)習(xí)之--基本概念和配置（3）
網(wǎng)頁路徑：http://muchs.cn/article36/gppesg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站策劃、企業(yè)網(wǎng)站制作、Google、企業(yè)建站、域名注冊、網(wǎng)站制作
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)
