[Oracle 11g r2(11.2.0.4.0)]Oracle Golden Gate Training-workshop1

learn by doing,less theory,more results.

install configure:

sourece 源端：

extract：负责读取数据库redolog,archivelog…
trail:本地磁盘文件，主要存储extract 传输过来的数据
data pump：负责从trail 读取数据，传输到远端

target 目标端：

clinct ：负责接收源端发送的数据
trail:目标端的本地磁盘文件，负责存储cliect 接收的数据
replicat:目标端进程，读取trail数据，写到目标端数据库。

env:

source :

ip：10.37.2.133
db:oracle 11g r2(11.2.0.4.0)
db_name:source
ogg:fbo_ggs_Linux_x64_ora11g_64bit.tar

target:

ip:10.37.2.254
db:oracle 11g r2(11.2.0.4.0)
db_name:target
ogg:fbo_ggs_Linux_x64_ora11g_64bit.tar

OGG单向DML 同步：

ogg安装：（source,target都需要执行）

1 、 vi ~/.bash_profile

# .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then        . ~/.bashrcfi# User specific environment and startup programsPATH=$PATH:$HOME/binexport PATHexport TMP=/tmpexport TMPDIR=$TMPexport ORACLE_SID=jhdbexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1export OGG_HOME=/u01/ggexport ORACLE_UNQNAME=devdbexport TNS_ADMIN=$ORACLE_HOME/network/adminexport ORACLE_TERM=xtermexport PATH=/usr/sbin:$PATH:$OGG_HOMEexport PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOMEexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libexport CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibexport EDITOR=viexport LANG=en_USexport NLS_LANG=american_america.AL32UTF8export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'umask 022

2 、创建相关目录

[oracle@localhost gg]$ ggsciOracle 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 (localhost.localdomain) 1> create subdirsCreating subdirectories under current directory /u01/ggParameter files                /u01/gg/dirprm: already existsReport files                   /u01/gg/dirrpt: createdCheckpoint files               /u01/gg/dirchk: createdProcess status files           /u01/gg/dirpcs: createdSQL script files               /u01/gg/dirsql: createdDatabase definitions files     /u01/gg/dirdef: createdExtract data files             /u01/gg/dirdat: createdTemporary files                /u01/gg/dirtmp: createdStdout files                   /u01/gg/dirout: created

3 、因为ogg是基于传输归档日志，所以需要把数据库置为归档模式。

    startup mount;    alter database archivelog;    alter system set log_archive_dest_2='location=/u01/app/oracle/ARCH' scope=both; 

强制force_logging ,supplemental_log_data(理论上只在源端设置就可以了)

SQL>  alter database force logging;Database altered.SQL> alter database add supplemental log data;Database altered.SQL> alter system switch logfile;

4、创建ogg管理用户,用户登录ogg

SQL> create tablespace ogg_s datafile '/u01/app/oracle/oradata/SOURCE/datafile/ogg.dbf' size 10m autoextend on;Tablespace created.SQL> create user ogg identified by oracle123 account unlock default tablespace ogg_s;SQL> grant connect,resource to ggate;Grant succeeded.SQL> grant execute on utl_file to ggate;Grant succeeded.

5、创建业务用户，用于同步数据(可选)

SQL> create user bobo identified by oracle123 account unlock default tablespace users;SQL> grant connect,resource to bobo;Grant succeeded.

6、ogg初始化脚本：

SQL> @role_setup.sqlGGS Role setup scriptThis script will drop and recreate the role GGS_GGSUSER_ROLETo use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)You will be prompted for the name of a schema for the GoldenGate database objects.NOTE: The schema must be created prior to running this script.NOTE: Stop all DDL replication before starting this installation.Enter GoldenGate schema name:oggWrote file role_setup_set.txtPL/SQL procedure successfully completed.Role setup script completeGrant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:GRANT GGS_GGSUSER_ROLE TO <loggedUser>where <loggedUser> is the user assigned to the GoldenGate processes.

7、根据提示赋予GGS_GGSUSER_ROLE角色权限：

SQL> grant GGS_GGSUSER_ROLE to ogg;Grant succeeded.

配置ogg进程参数文件（source端）：

1、指定ogg用户

[oracle@localhost gg]$ ggsciOracle 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 (localhost.localdomain) 1> edit params ./globals          ggschema ogg

2、配置ogg管理进程（manager）

GGSCI (localhost.localdomain) 2> edit params mgrport 7809

3、为指定表添加事务日志

GGSCI (localhost.localdomain) 2> dblogin userid ogg,password oracle123;Successfully logged into database.GGSCI (localhost.localdomain) 2> add trandata bobo.t1;ERROR: No viable tables matched specification.

增加表后面不能带分号';',不然会被当做sql语句执行。

GGSCI (localhost.localdomain) 3> add trandata bobo.t12017-10-30 15:28:38  WARNING OGG-00869  No unique key is defined for table 'T1'. 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 BOBO.T1.

SQL> alter table bobo.t1 add constraint pk_t1 primary key(id);

GGSCI (localhost.localdomain) 42> info trandata bobo.t1Logging of supplemental redo log data is enabled for table BOBO.T1.Columns supplementally logged for table BOBO.T1: ID, NAME

4、配置extract 进程

4.1、配置extract进程参数文件

GGSCI (localhost.localdomain) 4> edit params ext1    extract ext1setenv (nls_lang=american_america.al32utf8)userid ogg,password oracle123exttrail /u01/gg/dirdat/extable bobo.*;

extract：trail文件目录，对于数据量大的，建议放在专业存储上
Extract Ext1：表示此为Extract迚程，当前Group为Ext1
Setenv：设置环境发量字符集
Userid。。。：表示登陆数据库使用的dblogin账号和密码
Exttrail：指定本地Trail文件地址
Table：表示要同步的表，*代表全部

4.1、添加extract 进程

GGSCI (localhost.localdomain) 4> add extract ext1,tranlog,begin nowEXTRACT added.

–注释： 添加一个group为ext1的extract迚程，并且使用的tranlog捕获模式，开始使用时间是now。这里我用的是classic的tranlog捕获模式，而丌是integrated tranlog集成捕获模式，这种模式从11.2.0.3乊后才被支持的。

4.2、添加extract trail文件

GGSCI (localhost.localdomain) 9> add exttrail /u01/gg/dirdat/ex,extract ext1EXTTRAIL added.

–注释：这段命令表示为ext1迚程添加一个remote trail的链接，表明将会把trail传到目标端的/u01/app/oracle/ogg/dirdat目彔下，并以et作为一系列trail的前缀。

4.3、启动

GGSCI (localhost.localdomain) 9>start ext1

4.4、查看状态

GGSCI (localhost.localdomain) 9>info all

5、配置data pump extract

5.1、配置data pump extract参数文件:

添加data pump ,读取trail文件。负责读取本地trail文件发往远端。

GGSCI (localhost.localdomain) 4> edit params dp1extract dp1setenv (nls_lang=american_anerica.al32utf8)userid ogg ,password oracle123passthrurmthost 10.37.2.254,mgrport 7809,compressrmttrail /u01/gg/dirdat/rttable bobo.*;

rmthost:远端 地址信息
rmttrail ：远端 trail文件信息
passthru：绕过数据定义检测，提高datapump 性能。

5.1、添加本地data pump extract 进程

GGSCI (localhost.localdomain) 11> add extract dp1, exttrailsource /u01/gg/dirdat/exEXTRACT added.

exttrailsource：表示这是个data pump extract不本地trail作连接。

5.2、添加远端data pump extract 进程

添加remtrail ，使远端（target）与本地（source）端dp1建立连接

GGSCI (localhost.localdomain) 12> add rmttrail /u01/gg/dirdat/rt,extract dp1RMTTRAIL added

tmttrail：将pump不remote trail做链接。

5.3、启动服务

GGSCI (localhost.localdomain) 12> satrt dp1

5.4、查看状态

GGSCI (localhost.localdomain) 12>info all

ogg目标端配置(target)：

1、配置global参数文件，指定ogg用户，添加checkpointtable

GGSCI (localhost.localdomain) 1> edit params ./globals          ggschema oggcheckpointtable ogg.ckpttbs

2、在目标端创建checkpoint table

GGSCI (localhost.localdomain) 2> dblogin userid ogg,password oracle123;Successfully logged into database.GGSCI (localhost.localdomain) 4> add checkpointtable ogg.ckpttbsSuccessfully created checkpoint table ogg.ckpttbs.

select sid,username,program from v$session where username is not null;
select object_name from user_objects

3、配置ogg管理进程（manager）

GGSCI (localhost.localdomain) 2> edit params mgrport 7809

4、配置replicat

4.1、配置replicat 参数文件

GGSCI (localhost.localdomain) 4> edit params rpt1replicat rpt1userid ogg,password oracle123assumetargetdefs reperror default,discarddiscardfile /u01/gg/discards.dsc ,append,megabytes 50map bobo.*,target bobo.*;

Replicat RPT1：表示Group为RPT1的replicat迚程
Assumetargetdefs：假设Source端和Target端的数据定义是一致的，就丌再查找Source结构的定义 Discardfile：表示如果有丢弃的数据则丢弃到一个指定的文件中，以追加的方式写入新的内容，文件最大50M Map：用来映射Source对象不Target对象的关系，Source和Target的用户是可以相同的。

4.2、添加replicat进程,并指定replicate extract trail文件

 GGSCI (localhost.localdomain) 5> add replicat rpt1,exttrail /u01/gg/dirdat/rt,checkpointtable ogg.ckpttbsREPLICAT added.

–注释：添加一个replicat迚程，group为rpt1，并且链接到相应的trail文件，使用名为ogg.ckpttbs作为checkpointtable，这个ogg.checkpoint_table就是之前创建的。
至此ogg配置完成。

测试：

启动源端管理进程

 GGSCI (localhost.localdomain) 5> start mgr

启动ogg所有进程

 GGSCI (localhost.localdomain) 5> start *

GGSCI (localhost.localdomain) 43> show allParameter settings:SET SUBDIRS    ONSET DEBUG      OFFCurrent directory: /u01/ggUsing subdirectories for all process filesEditor:  viReports (.rpt)                 /u01/gg/dirrptParameters (.prm)              /u01/gg/dirprmStdout (.out)                  /u01/gg/diroutReplicat Checkpoints (.cpr)    /u01/gg/dirchkExtract Checkpoints (.cpe)     /u01/gg/dirchkProcess Status (.pcs)          /u01/gg/dirpcsSQL Scripts (.sql)             /u01/gg/dirsqlDatabase Definitions (.def)    /u01/gg/dirdef

查看ogg进程状态：

GGSCI (localhost.localdomain) 44> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     STOP        DP1         00:00:00      00:00:06    EXTRACT     RUNNING     EXT1        00:00:00      00:00:04    

启动extract进程：

GGSCI (localhost.localdomain) 44> start extract dp1 

删除进程

GGSCI (localhost.localdomain) 44> delete  dp1 

停止进程

GGSCI (localhost.localdomain) 44> stop dp1

在源端bobo.t1表charity一条记录，目标端的bobo.t1也会生产一条记录。
此次试验不支持双向同步，不支持DDL语句，目标端必须有同样的表才能同步数据成功。
针对以上问题，我会在后续继续补充。