安装前准备工作
Oracle OGG 11g安装用户和组设置
1.创建OGG操作系统用户
这里直接使用ORACLE用户安装GoldenGate，而不创建新用户。
2.创建OGG数据库用户及授权
源端数据库:
sqlplus / as sysdba
create user ggs identified by ggs default tablespace ts_ogg temporary tablespace TEMP1 quota unlimited on ts_ogg;
grant CONNECT, RESOURCE to ggs;
grant CREATE SESSION, ALTER SESSION to ggs;
grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ggs;
grant ALTER ANY TABLE to ggs;
grant FLASHBACK ANY TABLE to ggs;
grant EXECUTE on DBMS_FLASHBACK to ggs;
目标端数据库:
sqlplus / as sysdba
create user ggs identified by ggs default tablespace ts_ogg temporary tablespace TEMP1 quota unlimited on ts_ogg;
grant CONNECT, RESOURCE to ggs;
grant CREATE SESSION, ALTER SESSION to ggs;
grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ggs;
grant CREATE TABLE to ggs;
? 数据库启用日志功能
1.在源端数据库启用附件日志
检查是否开启附加日志:
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
如果没有，使用以下命令开启
SQL> alter database add supplemental log data;
SQL> alter system switch logfile;
2.在源端数据库启用归档。
启用归档模式:
sqlplus / as sysdba
SQL> alter system set log_archive_dest='+FRA;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
检查数据库归档信息:
sqlplus / as sysdba
SQL> alter system archive log current;
SQL> archive log list;
3.在源端数据库启用强制日志模式
检查日志模式:
SQL> SELECT force_logging FROM v$database;
FORCE_LOG
---------
NO
启用强制日志模式:
SQL> alter database force logging;
3）软件安装及配置
? 在源端和目标端系统创建安装目录并解压安装文件
源端操作系统:
mkdir -p /home/oracle/ogg
cp ogg.zip /home/oracle/ogg
cd /home/oracle/ogg
unzip ogg.zip
tar –xvf ggs_AIX_x86_ora11g_64bit_v11_1_1_0_0_078.tar
目标端操作系统:
mkdir -p /home/oracle/ogg
cp ogg.zip /home/oracle/ogg
cd /home/oracle/ogg
unzip ogg.zip
tar –xvf ggs_AIX_x86_ora11g_64bit_v11_1_1_0_0_078.tar
? 为创建OGG子工作目录
同时在源端和目标端系统执行:
[oracle@db] ./ggsci
GGSCI (db.cn.oracle.com) 1> create subdirs
Logging of supplemental redo log data is enabled for table SCOTT.DEPT_OGG
? 配置 Goldengate 管理进程
[oracle@db ogg] ./ggsci
GGSCI (db.cn.oracle.com) 1> EDIT PARAMS MGR
PORT 7809
PURGEOLDEXTRACTS /dirdat, USECHECKPOINTS
GGSCI (db.cn.oracle.com) 2> START MGR
? 配置 Goldengate 抽取进程
在源端系统配置 EXTRACT进程：
[oracle@db ogg] ./ggsci
add extract ejj_cc4, tranlog,begin now
add exttrail ./dirdat/ag, extract ejc_cx4, megabytes 100
edit params ejj_cc4
配置内容示例：
extract ejj_cc4
SETENV (ORACLE_SID="orcl")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Userid ggs,PASSWORD BBCLLLDEDDAIAUCOJGDDFPCCCHEGCPD, BLOWFISH, ENCRYPTKEY DEFAULT
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
EXTTRAIL ./dirdat/ag
NUMFILES 3000
EOFDELAYCSECS 30
GETTRUNCATES
TRANLOGOPTIONS DBLOGREADER
DYNAMICRESOLUTION
BR BRINTERVAL 2H , BRDIR BR
GETUPDATEBEFORES
NOCOMPRESSDELETES
WARNLONGTRANS 3H, CHECKINTERVAL 3M
table SCOTT.*;
table HR.WORK;
? 配置 Goldengate 投递进程
在源端系统配置 EXTRACT进程：
[oracle@db ogg] ./ggsci
add extract pjj_cc4,exttrailsource ./dirdat/ag
add rmttrail ./dirdat/bg, extract pjj_cc4, megabytes 500
edit params pjj_cc4
配置内容示例：
extract pjj_cc4
SETENV (ORACLE_SID="gdstjcpt1")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Userid ggs,PASSWORD BBCLLLDEDDAIAUCOJGDDFPCCCHEGCPD, BLOWFISH, ENCRYPTKEY DEFAULT
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
FLUSHCSECS 30
NUMFILES 3000
EOFDELAYCSECS 30
RMTHOST 192.168.72.6,MGRPORT 7809, TCPBUFSIZE 100000, TCPFLUSHBYTES 300000
RMTTRAIL ./dirdat/bg
GETTRUNCATES
PASSTHRU
DYNAMICRESOLUTION
GETUPDATEBEFORES
NOCOMPRESSDELETES
table SCOTT.*;
table HR.WORK;
? 配置 Goldengate 复制进程
在目标端系统添加checkpoint表：
GGSCI (grid.cn.oracle.com) 12> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE ggs.ggs_checkpoint
GGSCI (grid.cn.oracle.com) 1> DBLOGIN USERID ogg, PASSWORD ogg
Successfully logged into database.
GGSCI (grid.cn.oracle.com) 2> ADD CHECKPOINTTABLE
No checkpoint table specified, using GLOBALS specification (ggs.ggs_checkpoint...
Successfully created checkpoint table OGG.GGSCHKPT.
在目标端系统配置 REPLIC进程：
[oracle@db ogg] ./ggsci
dblogin userid ggs,password register
add replicat rjj_cc4,exttrail ./dirdat/bg, checkpointtable ggs.ggs_checkpoint
edit params rjj_cc4
配置内容示例：
replicat rjj_cc4
SETENV (ORACLE_SID="orcl")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Userid ggs,PASSWORD ogg
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
REPERROR DEFAULT,ABEND
DISCARDFILE ./dirrpt/rjc_cx4.dsc,APPEND,MEGABYTES 100
DISCARDROLLOVER AT 06:00
GETTRUNCATES
NUMFILES 3000
EOFDELAYCSECS 30
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
DYNAMICRESOLUTION
GETUPDATEBEFORES
NOCOMPRESSDELETES
MAP SCOTT.*, TARGET SCOTT.*;
MAP HR.WORK, TARGET HR.WORK;
? 为表添加附加日志
在源端系统添加：
GGSCI (db.cn.oracle.com) 3> DBLOGIN USERID ogg, PASSWORD Ogg
Successfully logged into database.
添加示例：
GGSCI (db.cn.oracle.com) 5> ADD TRANDATA scott.EMP_OGG
Logging of supplemental redo data enabled for table SCOTT.EMP_OGG.
GGSCI (db.cn.oracle.com) 6> ADD TRANDATA scott.DEPT_OGG
Logging of supplemental redo data enabled for table SCOTT.DEPT_OGG.
Verify that supplemental logging has been turned on for these tables.
GGSCI (cdcjp63vm3.cn.oracle.com) 7> INFO TRANDATA scott.emp*
Logging of supplemental redo log data is enabled for table SCOTT.EMP_OGG
Logging of supplemental redo log data is enabled for table SCOTT.DEPT_OGG