| Item | Source System | Target System |
| Platform | Red Hat Enterprise Linux Server release 5.4 | Red Hat Enterprise Linux Server release 5.4 |
| Hostname | gc1 | gc2 |
| Database | Oracle 10.2.0.1 | Oracle 11.2.0.1 |
| Character Set | ZHS16GBK | ZHS16GBK |
| ORACLE_SID | PROD | EMREP |
| Listener Name/Port | LISTENER/1521 | LISTENER/1521 |
| Goldengate User | ogg | ogg |
单向复制配置
exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib
[oracle@gc1~]$ mkdir -p/u01/app/ogg
[oracle@gc1~]$ cd /u01/app/ogg
[oracle@gc1ogg]$ ls
V18156-01-linux.zip
[oracle@gc1ogg]$ unzip V18156-01-linux.zip
[oracle@gc1ogg]$ tar -xvf ggs_redhatAS40_x86_ora10g_32bit_v10.4.0.19_002.tar
[oracle@gc2~]$ mkdir –p /u01/app/ogg
[oracle@gc2~]$ cd /u01/app/ogg
[oracle@gc2ogg]$ ls
V18156-01-linux.zip
[oracle@gc2ogg]$ unzip V18156-01-linux.zip
[oracle@gc2ogg]$ tar -xvf ggs_redhatAS40_x86_ora10g_32bit_v10.4.0.19_002.tar
[oracle@gc1~]$ vi .bash_profile
添加exportLD_LIBRARY_PATH=$ORACLE_HOME/lib
[oracle@gc1~]$ source .bash_profile
[oracle@gc1ogg]$ ./ggsci
Copyright(C)1995, 2009, Oracle and/or its affiliates. All rights reserved.
GGSCI(gc1)1> create subdirs
[oracle@gc2~]$ vi .bash_profile
添加export LD_LIBRARY_PATH=$ORACLE_HOME/lib
[oracle@gc2~]$ source .bash_profile
[oracle@gc2db_1]$ cd $ORACLE_HOME/lib
[oracle@gc2lib]$ ln -s libnnz11.solibnnz10.so
[oracle@gc2ogg]$ ./ggsci
Copyright (C)1995, 2009, Oracle and/or itsaffiliates. All rights reserved.
GGSCI (gc2)1> create subdirs
[oracle@gc1~]$ mkdir -p /u01/app/oracle/oradata/soraeuc/
SQL>create tablespacetbs_gguser datafile'/u01/app/oracle/oradata/soraeuc/gguser.dbf' size 50Mautoextend on; --创建表空间
SQL>create user oggidentified by Ogg default tablespacetbs_gguser temporary tablespace TEMPTSquota unlimited on tbs_gguser;
SQL>grantCONNECT,RESOURCE to ogg;
SQL>grant CREATESESSION, ALTER SESSION to ogg;
SQL>grant SELECT ANYDICTIONARY, SELECT ANY TABLE to ogg;
SQL>grant ALTER ANYTABLE to ogg;
SQL>grant FLASHBACK ANYTABLE to ogg;
SQL>grant EXECUTE onDBMS_FLASHBACK to ogg;
SQL>@/u01/app/ogg/demo_ora_create --创建模拟同步的表
Table dropped.
Table created.
Table dropped.
Table created.
SQL>@/u01/app/ogg/demo_ora_insert --向模拟同步的表中插入数据
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.
SQL>select * fromtcustmer;
CUSTNAME CITY ST
--------------------------------------------------------------------------------------
WILL BG SOFTWARECO. SEATTLE WA
JANE ROCKY FLYERINC. DENVER CO
SQL>select * fromtcustord;
CUSTORDER_DATE PRODUCT_ ORDER_IDPRODUCT_PRICEPRODUCT_AMOUNT TRANSACTION_ID
---------------- -------- ----------------------- -------------- --------------
WILL30-SEP-94 CAR 144 17520 3 100
JANE11-NOV-95 PLANE 256 133300 1 100
[oracle@gc2~]$ mkdir -p/u01/app/oracle/oradata/soraeuc/
SQL>create tablespacetbs_gguser datafile'/u01/app/oracle/oradata/soraeuc/gguser.dbf' size 50Mautoextend on; --创建与源库相同的表空间
SQL>create user oggidentified by Ogg default tablespacetbs_gguser temporary tablespace TEMPTSquota unlimited on tbs_gguser;
SQL>grant CONNECT,RESOURCE to ogg;
SQL>grant CREATESESSION, ALTER SESSION to ogg;
SQL>grant SELECT ANYDICTIONARY, SELECT ANY TABLE to ogg;
SQL>grant CREATE TABLEto ogg;
SQL>conn scott/tiger
SQL>@/u01/app/ogg/demo_ora_create --创建表，但不插入数据
Table dropped.
Table created.
Table dropped.
Table created.
SQL>grant INSERT,UPDATE, DELETE on scott.tcustmer to ogg; --把需要同步表的DML操作授权给ogg
SQL>grant INSERT,UPDATE, DELETE on scott.tcustord to ogg; --把需要同步表的DML操作授权给ogg
SQL>selectSUPPLEMENTAL_LOG_DATA_MIN from v$database; --检查源端是否开启补充日志
SUPPLEME
--------
NO
SQL>alter database addsupplemental log data; --开启补充日志
SQL>alter system switchlogfile; --切归档
gc1：开启归档
SQL>conn /as sysdba
SQL>alter system setlog_archive_dest_1=' location=/u01/app/oracle/oradata/soraeuc/arch';
SQL>shutdown immediate
SQL>startup mount
SQL>alter databasearchivelog;
SQL>alter database open;
SQL>alter system archivelog current; --检查归档日志信息
SQL>archive log list;
Database logmode ArchiveMode
Automaticarchival Enabled
Archivedestination /u01/app/oracle/oradata/soraeuc/arch
Oldest onlinelog sequence 14
Next logsequence to archive 16
Current log sequence 16
SQL>SELECT force_loggingFROM v$database;
FOR
---
NO
SQL>alter database forcelogging; --开启强制日志模式
SQL>SELECT force_loggingFROM v$database;
FOR
---
YES
[oracle@gc1ogg]$ ./ggsci
GGSCI(gc1)1> DBLOGIN USERID ogg, PASSWORD Ogg
Successfullylogged into database.
GGSCI(gc1)2> ADD TRANDATA scott.TCUSTMER
Logging ofsupplemental redo data enabled fortable SCOTT.TCUSTMER.
GGSCI(gc1)3> ADD TRANDATA scott.TCUSTORD
Logging ofsupplemental redo data enabled fortable SCOTT.TCUSTORD.
GGSCI(gc1)4> INFO TRANDATA scott.TCUST*
Logging ofsupplemental redo log data is enabledfor table SCOTT.TCUSTMER
Logging ofsupplemental redo log data is enabledfor table SCOTT.TCUSTORD
[oracle@gc1ogg]$ ./ggsci
GGSCI(gc1) 1> EDIT PARAMS MGR
添加如下：
PORT 7809
DYNAMICPORTLIST 7840-7890
AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS, MINKEEPDAYS 7 --建立dirdat文件夹用于追踪
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
GGSCI(gc1) 2> START MGR
Manager started.
GGSCI(gc1) 3> INFO MGR --验证MGR已开启
Manager is running (IP port gc1.7809).
[oracle@gc2ogg]$ ./ggsci
GGSCI(gc2)1> EDIT PARAMS MGR
添加如下
PORT 7809
DYNAMICPORTLIST 7840-7890
AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS, MINKEEPDAYS7 --建立dirdat文件夹用于追踪
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
以下是MANAGER进程参数配置说明：
PORT：指定服务监听端口；这里以7839为例，默认端口为7809
DYNAMICPORTLIST：动态端口：可以制定最大256个可用端口的动态列表，当指定的端口不可用时，管理进程将会从列表中选择一个可用的端口，源端和目标段的Collector、Replicat、GGSCI进程通信也会使用这些端口；
COMMENT：注释行，也可以用--来代替;
AUTOSTART：指定在管理进程启动时自动启动哪些进程；
AUTORESTART：自动重启参数设置：本处设置表示每3分钟尝试重新启动所有EXTRACT进程，共尝试5次；
PURGEOLDEXTRACTS：定期清理trail文件设置：本处设置表示对于超过3天的trail文件进行删除。
LAGREPORT、LAGINFO、LAGCRITICAL：
定义数据延迟的预警机制：本处设置表示MGR进程每隔1小时检查EXTRACT的延迟情况，如果超过了30分钟就把延迟作为信息记录到错误日志中，如果延迟超过了45分钟，则把它作为警告写到错误日志中。
GGSCI(gc2)2> START MGR
Manager started.
GGSCI(gc2)3> INFO MGR
Manager isrunning (IP port gc2.7809).
1.配置数据初始化
[oracle@gc1ogg]$ ./ggsci
GGSCI(gc1)1> ADD EXTRACT EINI_1, SOURCEISTABLE
EXTRACT added.
GGSCI(gc1)2> INFO EXTRACT *, TASKS
EXTRACT EINI_1 Initialized 2014-06-1809:54 Status STOPPED
CheckpointLag Not Available
Log ReadCheckpoint Not Available
FirstRecord Record 0
Task SOURCEISTABLE
GGSCI(gc1)3> EDIT PARAMS EINI_1
-- GoldenGateInitial Data Capture
-- for TCUSTMERand TCUSTORD
--
EXTRACT EINI_1
SETENV(NLS_LANG= AMERICAN_AMERICA.ZHS16GBK)
USERID ogg,PASSWORD Ogg
RMTHOST gc2,MGRPORT 7809
RMTTASKREPLICAT, GROUP RINI_1
TABLEscott.TCUSTMER;
TABLEscott.TCUSTORD;
~
~
"dirprm/eini_1.prm"[New] 10L, 253Cwritten
EXTRACT进程参数配置说明：
SETENV：配置系统环境变量
USERID/ PASSWORD：指定OGG连接数据库的用户名和密码，这里使用3.4部分中创建的数据库用户OGG;
COMMENT：注释行，也可以用--来代替;
TABLE：定义需复制的表，后面需以；结尾
TABLEEXCLUDE：定义需要排除的表，如果在TABLE参数中使用了通配符，可以使用该参数指定排除掉得表。
GETUPDATEAFTERS|IGNOREUPDATEAFTERS：
是否在队列中写入后影像，缺省复制
GETUPDATEBEFORES| IGNOREUPDATEBEFORES：
是否在队列中写入前影像，缺省不复制
GETUPDATES|IGNOREUPDATES：
是否复制UPDATE操作，缺省复制
GETDELETES|IGNOREDELETES：
是否复制DELETE操作，缺省复制
GETINSERTS|IGNOREINSERTS：
是否复制INSERT操作，缺省复制
GETTRUNCATES|IGNORETRUNDATES：
是否复制TRUNCATE操作，缺省不复制；
RMTHOST：指定目标系统及其GoldengateManager进程的端口号，还用于定义是否使用压缩进行传输，本例中的compress为压缩传输；
RMTTRAIL：指定写入到目标断的哪个队列；
EXTTRAIL：指定写入到本地的哪个队列；
SQLEXEC：在extract进程运行时首先运行一个SQL语句；
PASSTHRU：禁止extract进程与数据库交互，适用于Data Pump传输进程；
REPORT：定义自动定时报告；
STATOPTIONS：定义每次使用stat时统计数字是否需要重置；
REPORTCOUNT：报告已经处理的记录条数统计数字；
TLTRACE：打开对于数据库日志的跟踪日志；
DISCARDFILE：定义discardfile文件位置，如果处理中油记录出错会写入到此文件中；
DBOPTIONS：指定对于某种特定数据库所需要的特殊参数；
TRANLOGOPTIONS：指定在解析数据库日志时所需要的特殊参数，例如：对于裸设备，可能需要加入以下参数 rawdeviceoggset 0
WARNLONGTRANS：指定对于超过一定时间的长交易可以在gsserr.log里面写入警告信息，本处配置为每隔3分钟检查一次场交易，对于超过2小时的进行警告；
[oracle@gc2ogg]$ ./ggsci
OracleGoldenGate Command Interpreter for Oracle
Version10.4.0.19 Build 002
Linux, x86,32bit (optimized), Oracle 10 on Sep17 2009 23:49:42
Copyright (C)1995, 2009, Oracle and/or itsaffiliates. All rights reserved.
GGSCI(gc2)1> ADD REPLICAT RINI_1, SPECIALRUN
REPLICAT added.
GGSCI(gc2)2> INFO REPLICAT *, TASKS
REPLICAT RINI_1 Initialized 2014-06-1810:03 Status STOPPED
CheckpointLag 00:00:00 (updated 00:00:09 ago)
Log ReadCheckpoint Not Available
Task SPECIALRUN
GGSCI(gc2)3> EDIT PARAMS RINI_1
-- GoldenGateInitial Load Delivery
--
REPLICAT RINI_1
SETENV(NLS_LANG= AMERICAN_AMERICA.ZHS16GBK)
ASSUMETARGETDEFS
USERID ogg,PASSWORD Ogg
DISCARDFILE./dirrpt/RINIaa.dsc, PURGE
MAP scott.*,TARGET scott.*;
"dirprm/rini_1.prm"[New] 8L, 210Cwritten
REPLICAT进程参数配置说明：
ASSUMETARGETDEFS：假定两端数据结构一致使用此参数；
SOURCEDEFS：假定两端数据结构不一致，使用此参数指定源端的数据结构定义文件，该文件需要由GlodenGate工具产生。
MAP:用于指定源端与目标端表的映射关系；
MAPEXCLUDE：用于使用在MAP中使用*匹配时排除掉指定的表；
REPERROR：定义出错以后进程的响应，一般可以定义为两种：
ABEND，即一旦出现错误即停止复制，此为缺省配置；
DISCARD，出现错误后继续复制，只是把错误的数据放到discard文件中。
DISCARDFILE：定义discardfile文件位置，如果处理中油记录出错会写入到此文件中；
SQLEXEC：在进程运行时首先运行一个SQL语句；
GROUPTRANSOPS：将小交易合并成一个大的交易进行提交，减少提交次数，降低系统IO消耗。
MAXTRANSOPS：将大交易拆分，每XX条记录提交一次。
gc1:开启Extract进程（目标端Replicate进程会自动开启）
GGSCI(gc1)11> START EXTRACT EINI_1
Sending STARTrequest to MANAGER ...
EXTRACT EINI_1starting
GGSCI(gc1)12> VIEW REPORT EINI_1
2014-06-1810:13:43 GGSINFO 414 Wildcardresolution set to IMMEDIATE because SOURCEISTABLE is used.
......
......
Output toRINI_1:
From TableSCOTT.TCUSTMER:
# inserts: 2
# updates: 0
# deletes: 0
# discards: 0
From TableSCOTT.TCUSTORD:
# inserts: 2
# updates: 0
# deletes: 0
# discards: 0
GGSCI(gc2)6> VIEW REPORT RINI_1
......
......
Report at2014-06-18 10:13:57 (activity since2014-06-18 10:13:50)
From TableSCOTT.TCUSTMER to SCOTT.TCUSTMER:
# inserts: 2
# updates: 0
# deletes: 0
# discards: 0
From TableSCOTT.TCUSTORD to SCOTT.TCUSTORD:
# inserts: 2
# updates: 0
# deletes: 0
# discards: 0
SQL>conn scott/tiger
Connected.
SQL>select * from tcustmer;
CUSTNAME CITY ST
------------------------------------------------------ --
WILL BG SOFTWARECO. SEATTLE WA
JANE ROCKY FLYERINC. DENVER CO
SQL>set linesize 200
SQL>select * fromtcustord
CUSTORDER_DATE PRODUCT_ ORDER_IDPRODUCT_PRICEPRODUCT_AMOUNT TRANSACTION_ID
---------------- -------- ----------------------- -------------- --------------
WILL30-SEP-94 CAR 144 17520 3 100
JANE11-NOV-95 PLANE 256 133300 1 100
GGSCI(gc1)13> INFO EXTRACT EINI_1
EXTRACT EINI_1 LastStarted 2014-06-1810:13 Status STOPPED
CheckpointLag Not Available
Log ReadCheckpoint Table SCOTT.TCUSTORD
2014-06-1810:13:53 Record 2
Task SOURCEISTABLE
GGSCI(gc2)1> INFO REPLICAT RINI_1
REPLICAT RINI_1 Initialized 2014-06-1810:03 Status STOPPED
CheckpointLag 00:00:00 (updated 00:21:01 ago)
Log ReadCheckpoint Not Available
Task SPECIALRUN
2.实时同步数据配置
GGSCI(gc1) 14> EDIT PARAMSEORA_1
-- Change Capture parameter file to capture
-- TCUSTMER and TCUSTORD changes
EXTRACT EORA_1
SETENV (NLS_LANG= AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD Ogg
EXTTRAIL ./dirdat/aa
TABLE scott.TCUSTMER;
TABLE scott.TCUSTORD;
~
"dirprm/eora_1.prm" [New] 8L, 228Cwritten
GGSCI(gc1) 15> ADD EXTRACTEORA_1, TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI(gc1) 16> ADD EXTTRAIL./dirdat/aa, EXTRACT EORA_1, MEGABYTES 5
EXTTRAIL added.
GGSCI(gc1) 17> START EXTRACTEORA_1
Sending START request to MANAGER ...
EXTRACT EORA_1 starting
GGSCI(gc1) 18> INFO EXTRACTEORA_1
EXTRACT EORA_1 LastStarted 2014-06-1810:29 Status RUNNING
CheckpointLag 00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint Oracle Redo Logs
2014-06-1810:29:19 Seqno 16, RBA 1328640
[oracle@gc1ogg]$ ll/u01/app/ogg/dirdat/
total 4
-rw-rw-rw- 1 oracle oinstall 893 Jun 18 10:29 aa000000
GGSCI(gc1)1> EDIT PARAMS PORA_1
-- Data Pumpparameter file to read the local
-- trail ofTCUSTMER and TCUSTORD changes
--
EXTRACT PORA_1
SETENV(NLS_LANG= AMERICAN_AMERICA.ZHS16GBK)
PASSTHRU
RMTHOST gc2,MGRPORT 7809
RMTTRAIL./dirdat/pa
TABLEscott.TCUSTMER;
TABLEscott.TCUSTORD;
~
~
"dirprm/pora_1.prm"[New] 10L, 253Cwritten
GGSCI(gc1)2> ADD EXTRACT PORA_1, EXTTRAILSOURCE ./dirdat/aa
EXTRACT added.
GGSCI(gc1)3> INFO EXTRACT PORA_1
EXTRACT PORA_1 Initialized 2014-06-1810:35 Status STOPPED
CheckpointLag 00:00:00 (updated 00:00:14 ago)
Log ReadCheckpoint File ./dirdat/aa000000
FirstRecord RBA 0
GGSCI(gc1)4> ADD RMTTRAIL ./dirdat/pa, EXTRACT PORA_1, MEGABYTES 5
RMTTRAIL added.
GGSCI(gc1)5> START EXTRACT PORA_1
Sending STARTrequest to MANAGER ...
EXTRACT PORA_1starting
GGSCI(gc1)6> INFO EXTRACT PORA_1
EXTRACT PORA_1 LastStarted 2014-06-1810:36 Status RUNNING
Checkpoint Lag 00:00:00(updated 00:00:04 ago)
Log ReadCheckpoint File ./dirdat/aa000000
FirstRecord RBA 0
[oracle@gc2ogg]$ lldirdat/
total 0
-rw-rw-rw- 1oracle oinstall 0 Jun 18 10:36pa000000
GGSCI(gc2)1> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLEogg.ggschkpt
~
"./GLOBALS"[New] 1L, 29C written
[oracle@gc2ogg]$ llGLOBALS --检查参数已添加
-rw-rw-rw- 1oracle oinstall 29 Jun 18 10:42GLOBALS
GGSCI(gc2)1> DBLOGIN USERID ogg, PASSWORD Ogg
Successfullylogged into database.
GGSCI(gc2)2> ADD CHECKPOINTTABLE
No checkpointtable specified, using GLOBALSspecification (ogg.ggschkpt)...
Successfullycreated checkpoint tableOGG.GGSCHKPT.
GGSCI(gc2) 3> ADD REPLICATRORA_1, EXTTRAIL ./dirdat/pa
REPLICAT added.
GGSCI(gc2) 4> EDIT PARAMRORA_1
--
-- Change Delivery parameter file to apply
-- TCUSTMER and TCUSTORD Changes
--
REPLICAT RORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD Ogg
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE
MAP scott.tcustmer, TARGET scott.tcustmer;
MAP scott.tcustord, TARGET scott.tcustord;
~
~
~
"dirprm/rora_1.prm" [New] 12L, 327Cwritten
GGSCI(gc2) 5> START REPLICATRORA_1
Sending START request to MANAGER ...
REPLICAT RORA_1 starting
GGSCI(gc2) 6> INFO REPLICATRORA_1
REPLICAT RORA_1 LastStarted 2014-06-1810:48 Status RUNNING
CheckpointLag 00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint File ./dirdat/pa000000
FirstRecord RBA 0
SQL>conn scott/tiger
Connected.
SQL>insert into tcustmerVALUES('HYL','HUANGCO.','BEIJING','AU');
1 row created.
SQL>commit;
Commit complete.
SQL>conn scott/tiger
Connected.
SQL>select * fromtcustmer;
CUSTNAME CITY ST
------------------------------------------------------ --
HYL HUANGCO. BEIJING AU
WILL BG SOFTWARECO. SEATTLE WA
JANE ROCKY FLYERINC. DENVER CO
SQL>update tcustmer setcity = 'SHANGHAI', state = 'CN' wherecust_code='HYL';
1 row updated.
SQL>commit;
Commit complete
SQL>select * fromtcustmer;
CUSTNAME CITY ST
---------------------------------- ----------------------
HYL HUANGCO. SHANGHAI CN
WILL BG SOFTWARECO. SEATTLE WA
JANE ROCKY FLYERINC. DENVER CO
SQL>delete from tcustmerwhere CUST_CODE='HYL';
1 row deleted.
SQL>commit;
Commit complete.
SQL>select * fromtcustmer;
CUSTNAME CITY ST
------------------------------------------------------ --
WILL BG SOFTWARECO. SEATTLE WA
JANE ROCKY FLYERINC. DENVER CO
双向复制配置操作：
gc2：授权
SQL>grantCONNECT, RESOURCE to ogg;
SQL>grantCREATE SESSION, ALTER SESSION to ogg;
SQL>grantSELECT ANY DICTIONARY, SELECT ANY TABLE to ogg;
SQL>grantALTER ANY TABLE to ogg;
SQL>grantFLASHBACK ANY TABLE to ogg;
SQL>grantEXECUTE on DBMS_FLASHBACK to ogg;
gc1：授权
SQL>grantCONNECT, RESOURCE to ogg;
SQL>grantCREATE SESSION, ALTER SESSION to ogg;
SQL>grantSELECT ANY DICTIONARY, SELECT ANY TABLE to ogg;
SQL>grantCREATE TABLE to ogg;
SQL>grantINSERT, UPDATE, DELETE on scott.tcustmer to ogg;--把需要同步表的DML操作授权给ogg
SQL>grantINSERT, UPDATE, DELETE on scott.tcustord to ogg;--把需要同步表的DML操作授权给ogg
gc2：开启补充日志
SQL>alterdatabase add supplemental log data;
SQL>altersystem switch logfile;
SQL>alterdatabase force logging;
gc2：测试表添加到补充日志
GGSCI(gc2) 1> DBLOGIN USERID ogg, PASSWORD Ogg
Successfully logged into database.
GGSCI(gc2) 2> ADD TRANDATA scott.TCUSTMER
Logging of supplemental redo data enabled fortable SCOTT.TCUSTMER.
GGSCI(gc2) 3> ADD TRANDATA scott.TCUSTORD
Logging of supplemental redo data enabled fortable SCOTT.TCUSTORD.
gc2：配置Extract进程
GGSCI(gc2) 4> EDIT PARAMS EORA_1
-- Change Capture parameter file to capture
-- TCUSTMER and TCUSTORD changes
EXTRACT EORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD Ogg
TRANLOGOPTIONS EXCLUDEUSER ogg
EXTTRAIL ./dirdat/aa
TABLE scott.TCUSTMER;
TABLE scott.TCUSTORD;
~
~
"dirprm/eora_1.prm" [New] 9L, 257Cwritten
GGSCI(gc2) 5> ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI(gc2) 6> ADD EXTTRAIL ./dirdat/aa, EXTRACT EORA_1, MEGABYTES 5
EXTTRAIL added.
GGSCI(gc2) 7> START EXTRACT EORA_1
Sending START request to MANAGER ...
EXTRACT EORA_1 starting
GGSCI(gc2) 8> INFO EXTRACT EORA_1
EXTRACT EORA_1 Last Started2014-06-1811:28 Status RUNNING
Checkpoint Lag 00:00:19 (updated 00:00:08ago)
Log Read Checkpoint Oracle Redo Logs
2014-06-18 11:27:42 Seqno 6, RBA 35344
gc2：配置Pump进程
GGSCI(gc2) 9> EDIT PARAMS PORA_1
添加以下内容：
-- Data Pump parameter file to read thelocal
-- trail of TCUSTMER and TCUSTORDchanges
--
EXTRACT PORA_1
SETENV(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
PASSTHRU
RMTHOST gc1, MGRPORT 7809
RMTTRAIL ./dirdat/pa
TABLE scott.TCUSTMER;
TABLE scott.TCUSTORD;
~
~
~
"dirprm/pora_1.prm" [New] 10L, 250Cwritten
GGSCI(gc2) 10> ADD EXTRACT PORA_1, EXTTRAILSOURCE ./dirdat/aa
EXTRACT added.
GGSCI(gc2) 11> ADD RMTTRAIL ./dirdat/pa, EXTRACT PORA_1, MEGABYTES 5
RMTTRAIL added.
GGSCI(gc2) 12> START EXTRACT PORA_1
Sending START request to MANAGER ...
EXTRACT PORA_1 starting
gc1：配置Replicat进程
GGSCI(gc1)1> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLEogg.ggschkpt
~
~
"./GLOBALS"[New] 1L, 29C written
GGSCI(gc1)2> quit
[oracle@gc1ogg]$ llGLOBALS --验证
-rw-rw-rw- 1oracle oinstall 29 Jun 18 11:33GLOBALS
GGSCI(gc1)1> DBLOGIN USERID ogg, PASSWORD Ogg
Successfully logged into database.
GGSCI(gc1) 2> ADD CHECKPOINTTABLE
No checkpoint table specified, using GLOBALSspecification(ogg.ggschkpt)...
Successfully created checkpoint tableOGG.GGSCHKPT.
gc1：配置Replicate进程
GGSCI(gc1) 3> EDIT PARAM RORA_1
--
-- Change Delivery parameter file to apply
-- TCUSTMER and TCUSTORD Changes
--
REPLICAT RORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD Ogg
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE
MAP scott.tcustmer, TARGET scott.tcustmer;
MAP scott.tcustord, TARGET scott.tcustord;
~
~
"dirprm/rora_1.prm" [New] 12L, 327Cwritten
GGSCI(gc1) 4> ADD REPLICAT RORA_1, EXTTRAIL ./dirdat/pa
REPLICAT added.
GGSCI(gc1) 5> START REPLICAT RORA_1
Sending START request to MANAGER ...
REPLICAT RORA_1 starting
gc1：查看进程状态
GGSCI(gc1) 6> info all
Program Status Group Lag Time SinceChkpt
MANAGER RUNNING
EXTRACT RUNNING EORA_1 00:00:00 00:00:08
EXTRACT RUNNING PORA_1 00:00:00 00:00:01
REPLICAT RUNNING RORA_1 00:00:00 00:00:06
gc2：查看进程状态
GGSCI(gc2) 13> info all
Program Status Group Lag Time SinceChkpt
MANAGER RUNNING
EXTRACT RUNNING EORA_1 00:00:00 00:00:09
EXTRACT RUNNING PORA_1 00:00:00 00:00:06
REPLICAT RUNNING RORA_1 00:00:00 00:00:01
验证insert操作双向同步
gc1： gc1→gc2，DML操作：insert操作
SQL>insertinto tcustmer VALUES('HYL','HUANG DBA.','HARBIN','CN');
1 row created.
SQL>commit;
Commit complete.
gc2：验证insert操作同步
SQL>select* from tcustmer;
CUSTNAME CITY ST
---- -------------------------------------------------- --
HYL HUANGDBA. HARBIN CN
WILL BG SOFTWARECO. SEATTLE WA
JANE ROCKY FLYERINC. DENVER CO
gc2：gc2→gc1，DML操作：insert操作
SQL>insertinto tcustmer VALUES('WT','WANGDBA.','QINGDAO','CN');
1 row created.
SQL>commit;
Commit complete.
gc1：gc1→gc2，DML操作：update操作
SQL>select* from tcustmer;
CUSTNAME CITY ST
---- -------------------------------------------------- --
HYL HUANGDBA. HARBIN CN
WILL BG SOFTWARECO. SEATTLE WA
JANE ROCKY FLYERINC. DENVER CO
WT WANGDBA. QINGDAO CN
gc1：update操作
SQL>updatetcustmer set city = 'BEIJING', state = 'CN' wherecust_code='HYL';
1 row updated.
SQL>commit;
Commit complete.
gc2：验证update操作同步
SQL>select* from tcustmer;
CUSTNAME CITY ST
---- ------------------------------ ----------------------
HYL HUANGDBA. BEIJING CN
WILL BG SOFTWARECO. SEATTLE WA
JANE ROCKY FLYERINC. DENVER CO
WT WANGDBA. QINGDAO CN
gc2：gc2→gc1，DML操作：update操作
SQL>updatetcustmer set city = 'BEIJING', state = 'CN' wherecust_code='WT';
1 row updated.
SQL>commit;
Commit complete.
gc1：验证update操作同步
SQL>select* from tcustmer;
CUSTNAME CITY ST
---- -------------------------------------------------- --
HYL HUANGDBA. BEIJING CN
WILL BG SOFTWARECO. SEATTLE WA
JANE ROCKY FLYERINC. DENVER CO
WT WANGDBA. BEIJING CN
gc1：gc1→gc2，DML操作：delete操作
SQL>deletefrom tcustmer where CUST_CODE='WT';
1 row deleted.
SQL>commit;
Commit complete.
gc2：验证delete操作同步
SQL>select* from tcustmer;
CUSTNAME CITY ST
---- -------------------------------------------------- --
HYL HUANGDBA. BEIJING CN
WILL BG SOFTWARECO. SEATTLE WA
JANE ROCKY FLYERINC. DENVER CO
gc2：gc2→gc1，DML操作：delete操作
SQL>deletefrom tcustmer where CUST_CODE='HYL';
1 row deleted.
SQL>commit;
Commit complete.
gc1：验证delete操作同步
SQL>select* from TCUSTMER;
CUSTNAME CITY ST
---- -------------------------------------------------- --
WILL BG SOFTWARECO. SEATTLE WA
JANE ROCKY FLYERINC. DENVER CO
--至此，GoldenGate双向同步复制完成
DDL复制配置
gc1：执行脚本（注意：要到/u01/app/ogg目录下执行，否则会被hang住，oracle的bug）
SQL>@marker_setup.sql; --到/u01/app/ogg目录后登陆sqlplus执行
Marker setupscript
You will beprompted for the name of a schema forthe GoldenGate database objects.
NOTE: Theschema must be created prior to runningthis script.
NOTE: Stopall DDL replication before startingthis installation.
EnterGoldenGate schema name:ogg
Marker setuptable script complete, runningverification script...
Please enterthe name of a schema for theGoldenGate database objects:
Settingschema name to OGG
MARKER TABLE
-------------------------------
OK
MARKERSEQUENCE
-------------------------------
OK
Scriptcomplete.
SQL> altersystem set recyclebin=off scope=spfile; --关闭回收站
SQL> startupforce; --重启库
SQL> @ddl_setup
GoldenGateDDL Replication setup script
Verifyingthat current user has privileges to install DDL Replication...
You willbe prompted for the name of a schema for the GoldenGate databaseobjects.
NOTE:The schema must be created prior to running this script.
NOTE: OnOracle 10g and up, system recycle bin must be disabled.
NOTE:Stop all DDL replication before starting this installation.
EnterGoldenGate schema name:ogg
You willbe prompted for the mode of installation.
Toinstall or reinstall DDL replication, enter INITIALSETUP
Toupgrade DDL replication, enter NORMAL
Entermode of installation:INITIALSETUP
Working,please wait ...
Spoolingto file ddl_setup_spool.txt
UsingOGG as a GoldenGate schema name, INITIALSETUP as a mode ofinstallation.
Working,please wait ...
RECYCLEBINmust be empty.
Thisinstallation will purge RECYCLEBIN for all users.
To proceed,enter yes. To stop installation, enter no.
Enteryes or no:yes
SQL>@role_setup
GGS Role setup script
This script will drop and recreate the roleGGS_GGSUSER_ROLE
To use a different role name, quit this scriptand then edit theparams.sql script to change the gg_role parameter to thepreferred name. (Do notrun the script.)
You will be prompted for the name of a schema forthe GoldenGate databaseobjects.
NOTE: The schema must be created prior to runningthis script.
NOTE: Stop all DDL replication before startingthis 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 theExtract, GGSCI, and Managerprocesses, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
where <loggedUser> is the user assigned tothe GoldenGateprocesses.
SQL> grant GGS_GGSUSER_ROLE to ogg;
SQL> @ddl_enable
Trigger altered.
gc2：执行脚本（同gc1上操作）
SQL>altersystem set recyclebin=off scope=spfile; --关闭回收站
SQL>startupforce; --重启库
SQL>@marker_setup
SQL>@ddl_setup
SQL>@role_setup
SQL>grant GGS_GGSUSER_ROLE to ogg;
SQL>@ddl_enable
gc1：配置Extract进程
GGSCI(gc1)3> STOP EORA_1
Sending STOPrequest to MANAGER ...
RequestProcessed.
GGSCI(gc1)4> EDIT PARAMS EORA_1
添加红字部分:
-- ChangeCapture parameter file to capture
-- TCUSTMERand TCUSTORD changes
EXTRACTEORA_1
SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg,PASSWORD Welcome1
EXTTRAIL./dirdat/aa
DDLINCLUDE OBJNAME "scott.*"
TABLEscott.TCUSTMER;
TABLEscott.TCUSTORD;
GGSCI(gc1)5> START EORA_1
Sending STARTrequest to MANAGER ...
EXTRACTEINI_1 starting
gc2：配置extract
GGSCI(gc2) 3> stopEORA_1 --关闭EORA_1进程
Sending STOPrequest to MANAGER ...
RequestProcessed.
GGSCI(gc2)4> EDIT PARAMS EORA_1 --添加红字部分
-- ChangeCapture parameter file to capture
-- TCUSTMERand TCUSTORD changes
EXTRACTEORA_1
SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg,PASSWORD Welcome1
EXTTRAIL./dirdat/aa
DDLINCLUDE OBJNAME "scott.*"
TABLEscott.TCUSTMER;
TABLEscott.TCUSTORD;
GGSCI(gc2)5> START EORA_1
Sending STARTrequest to MANAGER ...
EXTRACTEINI_1 starting
gc1:配置Replicat进程
GGSCI(gc1)9> stop RORA_1
Sending STOPrequest to REPLICAT RORA_1 ...
Requestprocessed.
GGSCI(gc1)10> info all
Program Status Group Lag Time SinceChkpt
MANAGER RUNNING
EXTRACT RUNNING EORA_1 00:00:00 00:00:07
EXTRACT RUNNING PORA_1 00:00:00 00:00:02
REPLICAT STOPPED RORA_1 00:00:00 00:00:31
GGSCI(gc1)11> EDIT PARAMS RORA_1 --添加红色部分
DDLERRORDEFAULT IGNORE RETRYOP
-- ChangeDelivery parameter file to apply
-- TCUSTMERand TCUSTORD Changes
REPLICATRORA_1
SETENV(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg,PASSWORD Ogg
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE./dirrpt/RORA_aa.DSC, PURGE
DDLINCLUDE ALL
DDLERRORDEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DDLERRORDEFAULT DISCARD
DDLERRORDEFAULT IGNORE RETRYOP
MAPscott.tcustmer, TARGET scott.tcustmer;
MAPscott.tcustord, TARGET scott.tcustord;
"dirprm/rora_1.prm"16L, 458C written
GGSCI(gc1)12> start RORA_1
Sending STARTrequest to MANAGER ...
REPLICATRORA_1 starting
GGSCI(gc1)13> info all
Program Status Group Lag Time SinceChkpt
MANAGER RUNNING
EXTRACT RUNNING EORA_1 00:00:00 00:00:06
EXTRACT RUNNING PORA_1 00:00:00 00:00:02
REPLICAT RUNNING RORA_1 00:00:00 00:00:02
gc2:配置Replicate进程
配置过程，同gc1
验证ddl:create操作
gc1：
SQL>create table test1 as select * from emp;
Tablecreated.
gc2：
SQL>select * from test1;
EMPNOENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------------------- --------- ---------------------- ---------- ---------- ----------
7369SMITH CLERK 7902 17-DEC-80 800 20
7499ALLEN SALESMAN 769820-FEB-81 1600 300 30
......
7902FORD ANALYST 756603-DEC-81 3000 20
7934MILLER CLERK 7782 23-JAN-82 1300 10
14 rowsselected.
gc2:
SQL>create table test2 as select * from emp;
Tablecreated.
gc1:
SQL>select * from test2;
EMPNOENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------------------- --------- ---------------------- ---------- ---------- ----------
7369SMITH CLERK 790217-DEC-80 800 20
7499 ALLEN SALESMAN 769820-FEB-81 1600 300 30
......
7902FORD ANALYST 756603-DEC-81 3000 20
7934MILLER CLERK 778223-JAN-82 1300 10
14 rowsselected.
验证ddl:alter操作
gc1:
SQL>alter table test1 rename column mgr to manager;
Tablealtered.
gc2:
SQL>select * from test1;
EMPNOENAME JOB MANAGER HIREDATE SAL COMM DEPTNO
-------------------- --------- ---------------------- ---------- ---------- ----------
7369SMITH CLERK 790217-DEC-80 800 20
7499ALLEN SALESMAN 769820-FEB-81 1600 300 30
7521WARD SALESMAN 769822-FEB-81 1250 500 30
......
gc2:
SQL>alter table test1 rename column manager to mgr;
Tablealtered.
gc1:
SQL>select * from test1;
EMPNOENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------------------- --------- ---------------------- ---------- ---------- ----------
7369SMITH CLERK 790217-DEC-80 800 20
7499ALLEN SALESMAN 769820-FEB-81 1600 300 30
7521WARD SALESMAN 769822-FEB-81 1250 500 30
7566JONES MANAGER 783902-APR-81 2975 20
......
验证ddl:drop操作
gc2:
SQL>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
TCUSTMER TABLE
TCUSTORD TABLE
TEST1 TABLE
TEST2 TABLE
8 rows selected.
gc1:
SQL> drop table test1;
Table dropped.
gc2:
SQL>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
TCUSTMER TABLE
TCUSTORD TABLE
TEST2 TABLE
7 rows selected.
gc1:
SQL>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TCUSTMER TABLE
TCUSTORD TABLE
TEST2 TABLE
7 rows selected.
gc2:
SQL>drop table test2;
Table dropped.
SQL>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TCUSTMER TABLE
TCUSTORD TABLE
扫一扫
1470
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
