[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 functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_SID=jhdb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export OGG_HOME=/u01/gg
export ORACLE_UNQNAME=devdb
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH:$OGG_HOME
export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export EDITOR=vi
export LANG=en_US
export NLS_LANG=american_america.AL32UTF8
export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'
umask 022

####2 、创建相关目录

[oracle@localhost gg]$ ggsci

Oracle GoldenGate Command Interpreter 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:32:14

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



GGSCI (localhost.localdomain) 1> create subdirs

Creating subdirectories under current directory /u01/gg

Parameter files                /u01/gg/dirprm: already exists
Report files                   /u01/gg/dirrpt: created
Checkpoint files               /u01/gg/dirchk: created
Process status files           /u01/gg/dirpcs: created
SQL script files               /u01/gg/dirsql: created
Database definitions files     /u01/gg/dirdef: created
Extract data files             /u01/gg/dirdat: created
Temporary files                /u01/gg/dirtmp: created
Stdout 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.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To 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:ogg
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.


Role setup script complete

Grant 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]$ ggsci

Oracle GoldenGate Command Interpreter 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:32:14

Copyright (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 mgr


port 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.t1

2017-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.t1

Logging 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 ext1
setenv (nls_lang=american_america.al32utf8)；
userid ogg,password oracle123；
exttrail /u01/gg/dirdat/ex；
table 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 now
EXTRACT 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 ext1
EXTTRAIL 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 dp1
extract dp1
setenv (nls_lang=american_anerica.al32utf8)
userid ogg ,password oracle123
passthru
rmthost 10.37.2.254,mgrport 7809,compress
rmttrail /u01/gg/dirdat/rt
table bobo.*;

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

####5.1、添加本地data pump extract 进程

GGSCI (localhost.localdomain) 11> add extract dp1, exttrailsource /u01/gg/dirdat/ex
EXTRACT 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 dp1
RMTTRAIL 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 ogg
checkpointtable 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.ckpttbs

Successfully 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 mgr

port 7809

4、配置replicat

4.1、配置replicat 参数文件

GGSCI (localhost.localdomain) 4> edit params rpt1

replicat rpt1
userid ogg,password oracle123
assumetargetdefs 
reperror default,discard
discardfile /u01/gg/discards.dsc ,append,megabytes 50
map 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.ckpttbs
REPLICAT 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 all

Parameter settings:

SET SUBDIRS    ON
SET DEBUG      OFF

Current directory: /u01/gg

Using subdirectories for all process files

Editor:  vi

Reports (.rpt)                 /u01/gg/dirrpt
Parameters (.prm)              /u01/gg/dirprm
Stdout (.out)                  /u01/gg/dirout
Replicat Checkpoints (.cpr)    /u01/gg/dirchk
Extract Checkpoints (.cpe)     /u01/gg/dirchk
Process Status (.pcs)          /u01/gg/dirpcs
SQL Scripts (.sql)             /u01/gg/dirsql
Database Definitions (.def)    /u01/gg/dirdef

####查看ogg进程状态：

GGSCI (localhost.localdomain) 44> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     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语句，目标端必须有同样的表才能同步数据成功。
针对以上问题，我会在后续继续补充。