1安装OGG
1.1上传OGG压缩包
解压压缩包
1.2配置环境变量
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
1.3创建OGG工作目录
GGSCI (rac01) 3> create subdirs
Creating subdirectories under current directory /home/oracle/ogg
Parameter files /home/oracle/ogg/dirprm: already exists
Report files /home/oracle/ogg/dirrpt: created
Checkpoint files /home/oracle/ogg/dirchk: created
Process status files /home/oracle/ogg/dirpcs: created
sql script files /home/oracle/ogg/dirsql: created
Database definitions files /home/oracle/ogg/dirdef: created
Extract data files /home/oracle/ogg/dirdat: created
Temporary files /home/oracle/ogg/dirtmp: created
Stdout files /home/oracle/ogg/dirout: created
2为OGG配置数据库
2.1源端、目标端创建GGS用户
sql> create tablespace ggs_tbs datafile size 50m;
Tablespace created.
sql> create user ggs identified by ggs default tablespace ggs;
User created.
sql> grant dba to ggs;
Grant succeeded.
2.2源端、目标端开启force logging和辅助日志
sql> alter database force logging;
Database altered.
sql> alter database add supplemental log data;
Database altered.
2.3源端、目标端支持sequence
GGSCI (rac01) 1> edit params ./globals
输入:ggschema ggs
sql> @sequence
Please enter the name of a schema for the GoldenGate database objects:
ggs
2.4源端、目标端支持ddl复制
sql> alter system set recyclebin=off deferred scope=both;
System altered.
Marker setup script
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:ggs
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source,the system recycle bin must be disabled. For Oracle 11g and later,it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:ggs
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:ggs
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
sql> @?/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
sql> @ddl_pin ggs;
3源端配置OGG
3.1配置manager
GGSCI (rac01) 3> edit params mgr (空文件直接保存退出即可,除非默认端口7809被占用)
GGSCI (rac01) 4> start mgr
Manager started.
3.2添加表级trandata
GGSCI (rac01) 8> DBLOGIN USERID ggs
Password:
Successfully logged into database.
GGSCI (rac01) 9> add trandata hr.test (表名test可以为*通配符)
2016-10-25 08:04:43 WARNING OGG-00869 No unique key is defined for table 'TEST'. 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 HR.TEST.
3.3添加抽取进程
GGSCI (rac01) 10> add extract ext01,tranlog,begin now,threads 2
EXTRACT added.
GGSCI (rac01) 12> add exttrail ./dirdat/ex,extract ext01
EXTTRAIL added.
GGSCI (rac01) 13> edit params ext01
EXTRACT ext01
USERID ggs,PASSWORD ggs
TRANlogoPTIONS ASMUSER sys@asm,ASMPASSWORD oracle
DISCARDFILE ./dirdat/ex.dsc,purge
EXTTRAIL ./dirdat/ex
ddlinclude all
TABLE hr.*;
3.4添加传输进程
GGSCI (rac01) 17> add extract pump01,exttrailsource ./dirdat/ex
EXTRACT added.
GGSCI (rac01) 18> add rmttrail /home/oracle/ogg/dirdat/ex,EXTRACT pump01
RMTTRAIL added.
GGSCI (rac01) 19> edit params pump01
EXTRACT pump01
USERID ggs,PASSWORD ggs
PASSTHRU
RMTHOST 192.168.56.203,MGRPORT 7809,TCPBUFSIZE 100000,TCPFLUSHBYTES 300000
RMTTRAIL /home/oracle/ogg/dirdat/ex
TABLE hr.*;
4目标端配置OGG
4.1创建checkpoint table
GGSCI (hadoop03) 1> dblogin userid ggs,password ggs
Successfully logged into database.
GGSCI (hadoop03) 2> add checkpointtable ggs.checkpoint
Successfully created checkpoint table ggs.checkpoint.
GGSCI (hadoop03) 3> edit params ./GLOBALS
CHECKPOINTTABLE ggs.checkpoint
4.2创建复制进程
GGSCI (hadoop03) 1> add replicat rep01,exttrail /home/oracle/ogg/dirdat/ex,checkpointtable ggs.checkpoint
REPLICAT added.
GGSCI (hadoop03) 14> edit params rep01
REPLICAT repnd
USERID ggs,PASSWORD ggs
BATCHsql
PURGEOLDEXTRACTS
HANDLECOLLISIONS
ASSUMetaRGETDEFS
INSERTAPPEND
DISCARDFILE ./dirdat/r1.dsc,purge
ddl include all
map hr.*,target hr.*;
5启动抽取和复制进程
5.1源端
GGSCI (rac01) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT01 00:00:00 00:20:23
EXTRACT STOPPED TRANS01 00:00:00 00:11:37
GGSCI (rac01) 2> start EXT01
Sending START request to MANAGER ...
EXTRACT EXT01 starting
GGSCI (rac01) 3> start TRANS01
Sending START request to MANAGER ...
EXTRACT TRANS01 starting
GGSCI (rac01) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT01 00:00:00 00:20:31
EXTRACT RUNNING TRANS01 00:00:00 00:11:45
5.2目标端
GGSCI (hadoop03) 13> start repnd
Sending START request to MANAGER ...
REPLICAT REPND starting