1.下载,安装GoldenGate软件(两个节都需要安装)
GoldenGate可从以下信息的得到下载:
点击这个http://www.linuxidc.com/Linux/2013-12/93755.htm 链接关注 Linux公社官方微信,关注后回复数字142936。即可得到网友的分享密码。
如果取消关注Linux公社公众号,即使再次关注,也将无法提供本服务!
链接:http://pan.baidu.com/s/1c25pSPE 密码:获得见上面的方法,地址失效请在下面留言。
------------------------------------------分割线------------------------------------------
也可以到Linux公社1号FTP服务器下载
FTP地址:ftp://ftp1.linuxidc.com
用户名:ftp1.linuxidc.com
密码:www.linuxidc.com
在 2017年LinuxIDC.com/4月/Oracle 11g GoldenGate与Oracle 11g数据同步/
下载方法见 http://www.linuxidc.com/Linux/2013-10/91140.htm
------------------------------------------分割线------------------------------------------
[oracle@oracleogg ~]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip[oracle@oracleogg ~]$ tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/glodengate/[oracle@oracleogg ~]$ cd /u01/glodengate/[oracle@oracleogg glodengate]$ vim ~/.bash_profile export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0.1/db_1export ORACLE_SID=oracle11gexport PATH=$ORACLE_HOME/bin:$PATHexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/glodengate/lib --必须要设置,否则出错
[oracle@oracleogg glodengate]$ ./ggsci Oracle GoldenGate Command Interpreter for OracleVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBOLinux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (oracleogg) 1> create subdirs --安装glodengateCreating subdirectories under current directory /u01/glodengateParameter files /u01/glodengate/dirprm: already existsReport files /u01/glodengate/dirrpt: createdCheckpoint files /u01/glodengate/dirchk: createdProcess status files /u01/glodengate/dirpcs: createdSQL script files /u01/glodengate/dirsql: createdDatabase definitions files /u01/glodengate/dirdef: createdExtract data files /u01/glodengate/dirdat: createdTemporary files /u01/glodengate/dirtmp: createdStdout files /u01/glodengate/dirout: createdGGSCI (oracleogg) 2> [oracle@oracleogg glodengate]$ ls -lF | grep '/$' --查看glodengate创建的目录drwxr-x--- 2 oracle oinstall 4096 Apr 23 2012 cfg/drwxrwxr-x 2 oracle oinstall 4096 Nov 19 19:36 dirchk/ --检查点文件drwxrwxr-x 2 oracle oinstall 4096 Nov 19 19:36 dirdat/ --extract文件drwxrwxr-x 2 oracle oinstall 4096 Nov 19 19:36 dirdef/drwxr-x--- 2 oracle oinstall 4096 Apr 23 2012 dirjar/drwxrwxr-x 2 oracle oinstall 4096 Nov 19 19:36 dirout/drwxrwxr-x 2 oracle oinstall 4096 Nov 19 19:36 dirpcs/drwxr-x--- 2 oracle oinstall 4096 Apr 23 2012 dirprm/ --参数文件目录drwxrwxr-x 2 oracle oinstall 4096 Nov 19 19:36 dirrpt/ --日志文件drwxrwxr-x 2 oracle oinstall 4096 Nov 19 19:36 dirsql/ --sql脚本文件drwxrwxr-x 2 oracle oinstall 4096 Nov 19 19:36 dirtmp/ --临时文件drwxr-x--- 7 oracle oinstall 4096 Apr 23 2012 UserExitExamples/[oracle@oracleogg glodengate]$
2.获取语法帮助信息(1).必须要ggsci根目录进入ggsci终端,否则获取不到帮助信息(2).帮助语法: help
3.在source端修改支持supplemental log日志[oracle@oracle11g ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 20 04:18:26 2016Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> alter database add supplemental log data; Database altered.SQL> alter system switch logfile;System altered.SQL> select supplemental_log_data_min from v$database;SUPPLEME--------YESSQL>
4.在source端和target端创建表空间,用户和测试表(只有source端有测试数据)source节点:
SQL> create tablespace wuhan datafile '/u01/oracle/oradata/orac11g/wuhan.dbf' size 100m;Tablespace created.SQL> create user gguser identified by system default tablespace wuhan quota unlimited on wuhan;User created.SQL> grant dba,resource,connect to gguser;Grant succeeded.SQL> conn gguser/systemConnected.SQL> create table t (a number,b char(10));Table created.SQL> insert into t values(1,'a');1 row created.SQL> insert into t values(2,'b');1 row created.SQL> insert into t values(3,'c');1 row created.SQL> commit;Commit complete.SQL> select * from t; A B---------- ---------- 1 a 2 b 3 cSQL>
target节点:SQL> create tablespace wuhan datafile '/u01/app/oracle/oradata/oracleogg/wuhan.dbf' size 100m;Tablespace created.SQL> create user gguser identified by system default tablespace wuhan quota unlimited on wuhan;User created.SQL> grant dba,resource,connect to gguser;Grant succeeded.SQL> conn gguser/systemConnected.SQL> create table t (a number,b char(10));Table created.SQL>
5.source端和target端启动mgr进程(两个节点都需要做)aource节点:
GGSCI (oracle11g) 3> edit params mgr --设置mgr的端口-- port --这个是注释PORT 7809 --指定的端口GGSCI (oracle11g) 4> start mgr --启动mgrManager started.GGSCI (oracle11g) 6> info mgr --查看mgr的状态Manager is running (IP port oracle11g.7809).GGSCI (oracle11g) 7>
6.配置glodengate需要同步的表(source节点)
GGSCI (oracle11g) 7> dblogin userid gguser, password systemSuccessfully logged into database.GGSCI (oracle11g) 8> add trandata gguser.t --也可以使用通配符(add trandata gguser.*)2016-11-20 05:32:50 WARNING OGG-00869 No unique key is defined for table 'T'. 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 GGUSER.T.GGSCI (oracle11g) 9> info trandata gguser.*Logging of supplemental redo log data is enabled for table GGUSER.T.Columns supplementally logged for table GGUSER.T: A, B.GGSCI (oracle11g) 10>
7.初使化数据(将source的数据导入到target节点中)source节点:
GGSCI (oracle11g) 10> add extract einiaa, sourceistableEXTRACT added.GGSCI (oracle11g) 11> edit params einiaa
EXTRACT EINIAAUSERID GGUSER, PASSWORD "system"RMTHOST 192.168.3.90, MGRPORT 7809RMTTASK REPLICAT, GROUP RINIAATABLE gguser.t;GGSCI (oracle11g) 12>
target节点:GGSCI (oracleogg) 10> add replicat riniaa, specialrunREPLICAT added.GGSCI (oracleogg) 11> edit params riniaaREPLICAT RINIAAASSUMETARGETDEFSUSERID gguser, PASSWORD "system"DISCARDFILE ./dirrpt/RINIAA.dsc, PURGEMAP gguser.*, TARGET gguser.*;GGSCI (oracleogg) 12>
8.启动source节点:GGSCI (oracle11g) 12> start extract einiaaSending START request to MANAGER ...EXTRACT EINIAA starting
GGSCI (oracle11g) 13> view report einiaa
2016-11-20 05:56:55 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, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:42:16 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Starting at 2016-11-20 05:56:55***********************************************************************Operating System Version:LinuxVersion #1 SMP Fri Nov 22 03:15:09 UTC 2013, Release 2.6.32-431.el6.x86_64Node: oracle11gMachine: x86_64 soft limit hard limitAddress Space Size : unlimited unlimitedHeap Size : unlimited unlimitedFile Size : unlimited unlimitedCPU Time : unlimited unlimited
Process id: 8706Description:
************************************************************************* Running with the following parameters *************************************************************************2016-11-20 05:56:55 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.EXTRACT EINIAAUSERID GGUSER, PASSWORD ********
2016-11-20 05:56:55 INFO OGG-03500 WARNING: NLS_LANG environment variable does not match database character set, or not set. Using database character set value of AL32UTF8.RMTHOST 192.168.3.90, MGRPORT 7809RMTTASK REPLICAT, GROUP RINIAATABLE gguser.t;
2016-11-20 05:56:56 WARNING OGG-00869 No unique key is defined for table 'T'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.Using the following key columns for source table GGUSER.T: A, B.
2016-11-20 05:56:56 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/glodengate/dirtmp.
CACHEMGR virtual memory values (may have been adjusted)CACHESIZE: 64GCACHEPAGEOUTSIZE (normal): 8MPROCESS VM AVAIL FROM OS (min): 128GCACHESIZEMAX (strict force to disk): 96G
Database Version:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE11.2.0.1.0ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - Production
Database Language and Character Set:NLS_LANG = ".AL32UTF8" NLS_LANGUAGE = "AMERICAN" NLS_TERRITORY = "AMERICA" NLS_CHARACTERSET = "AL32UTF8"
Processing table GGUSER.T
************************************************************************ ** Run Time Statistics ** ************************************************************************
Report at 2016-11-20 05:57:15 (activity since 2016-11-20 05:56:56)Output to RINIAA:From Table GGUSER.T: # inserts: 3 # updates: 0 # deletes: 0 # discards: 0REDO Log Statistics Bytes parsed 0 Bytes output 252GGSCI (oracle11g) 14>
target节点:GGSCI (oracleogg) 14> view report riniaa --查看日志[oracle@oracleogg glodengate]$ sqlplus gguser/systemSQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 19 22:01:32 2016Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from t; A B---------- ---------- 1 a 2 b 3 cSQL>
8.配置用户数据实时更新source节点:GGSCI (oracle11g) 14> add extract eoraaa, tranlog, begin now, threads 1 EXTRACT added.GGSCI (oracle11g) 15> info extract *EXTRACT EORAAA Initialized 2016-11-20 06:17 Status STOPPEDCheckpoint Lag 00:00:00 (updated 00:00:30 ago)Log Read Checkpoint Oracle Redo Logs 2016-11-20 06:17:37 Thread 1, Seqno 0, RBA 0 SCN 0.0 (0)GGSCI (oracle11g) 16> edit params eoraaaEXTRACT EORAAAUSERID gguser, PASSWORD "system"RMTHOST 192.168.3.90, MGRPORT 7809RMTTRAIL ./dirdat/aaTABLE gguser.t;GGSCI (oracle11g) 18> add rmttrail ./dirdat/aa, extract eoraaa, megabytes 10RMTTRAIL added.GGSCI (oracle11g) 19> info rmttrail * Extract Trail: ./dirdat/aa Extract: EORAAA Seqno: 0 RBA: 0 File Size: 10MGGSCI (oracle11g) 20> start extract eoraaaSending START request to MANAGER ...EXTRACT EORAAA startingGGSCI (oracle11g) 21> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING EORAAA 00:00:00 00:00:09 GGSCI (oracle11g) 22> info extract eoraaa, detailEXTRACT EORAAA Initialized 2016-11-20 06:17 Status STOPPEDCheckpoint Lag 00:00:00 (updated 00:10:50 ago)Log Read Checkpoint Oracle Redo Logs 2016-11-20 06:17:37 Thread 1, Seqno 0, RBA 0 SCN 0.0 (0) Target Extract Trails: Remote Trail Name Seqno RBA Max MB ./dirdat/aa 0 0 10 Extract Source Begin End Not Available * Initialized * 2016-11-20 06:17Current directory /u01/glodengateReport file /u01/glodengate/dirrpt/EORAAA.rptParameter file /u01/glodengate/dirprm/eoraaa.prmCheckpoint file /u01/glodengate/dirchk/EORAAA.cpeProcess file /u01/glodengate/dirpcs/EORAAA.pceStdout file /u01/glodengate/dirout/EORAAA.outError log /u01/glodengate/ggserr.logGGSCI (oracle11g) 23>
target节点:GGSCI (oracleogg) 14> edit params ./GLOBALS --创建GLOBALS参数后必须退出CHECKPOINTTABLE system.ggchecktable GGSCI (oracleogg) 2> dblogin userid system, password systemSuccessfully logged into database.GGSCI (oracleogg) 3> add checkpointtableNo checkpoint table specified, using GLOBALS specification (system.ggchecktable)...Successfully created checkpoint table system.ggchecktable.GGSCI (oracleogg) 4> add replicat roraaa, exttrail ./dirdat/aaREPLICAT added.GGSCI (oracleogg) 5> edit params roraaaREPLICAT RORAAAUSERID gguser, PASSWORD "system"HANDLECOLLISIONSASSUMETARGETDEFSDISCARDFILE ./dirrpt/RORAAA.DSE, PURGEMAP gguser.t, TARGET gguser.t;GGSCI (oracleogg) 8> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING REPLICAT RUNNING RORAAA 00:00:00 00:00:05 GGSCI (oracleogg) 9>
9.验证数据实时更新source节点:SQL> select * from t; A B---------- ---------- 1 a 2 b 3 c 4 dSQL> insert into t values(5,'e');1 row created.SQL> commit;Commit complete.SQL> select * from t; A B---------- ---------- 1 a 2 b 3 c 4 d 5 eSQL>
target节点:SQL> select * from t; A B---------- ---------- 4 d 5 e 1 a 2 b 3 cSQL>
来源: http://www.linuxidc.com/Linux/2017-04/142936.htm