ogg


 

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

gc1:解压GoldenGate安装包

[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

 

gc2:解压GoldenGate安装包

[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

 

gc1:配置环境变量、建立相关子目录

[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

 

 

gc2:配置环境变量、建立相关子目录

[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

 

 

gc1:建立GoldenGate用户、授权

[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

 

gc2: 建立GoldenGate用户、授权

[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

 

gc1:开启补充日志

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

 

gc1:开启补充日志

SQL>SELECT force_loggingFROM v$database;  

FOR

---

NO

SQL>alter database forcelogging;        --开启强制日志模式

SQL>SELECT force_loggingFROM v$database;

FOR

---

YES

 

gc1:开启测试表补充日志

[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

 

gc1:配置MGR

[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).

 

gc2:配置MGR

[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个可用端口的动态列表,当指定的端口不可用时,管理进程将会从列表中选择一个可用的端口,源端和目标段的CollectorReplicatGGSCI进程通信也会使用这些端口;

COMMENT注释行,也可以用--来代替;

AUTOSTART指定在管理进程启动时自动启动哪些进程;

AUTORESTART自动重启参数设置:本处设置表示每3分钟尝试重新启动所有EXTRACT进程,共尝试5次;

PURGEOLDEXTRACTS定期清理trail文件设置:本处设置表示对于超过3天的trail文件进行删除。

LAGREPORTLAGINFOLAGCRITICAL

定义数据延迟的预警机制:本处设置表示MGR进程每隔1小时检查EXTRACT的延迟情况,如果超过了30分钟就把延迟作为信息记录到错误日志中,如果延迟超过了45分钟,则把它作为警告写到错误日志中。

GGSCI(gc2)2> START MGR

Manager started.

GGSCI(gc2)3> INFO MGR

Manager isrunning (IP port gc2.7809).

1.配置数据初始化

gc1:配置Extract进程

[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指定写入到本地的哪个队列;

SQLEXECextract进程运行时首先运行一个SQL语句;

PASSTHRU禁止extract进程与数据库交互,适用于Data Pump传输进程;

REPORT定义自动定时报告;

STATOPTIONS定义每次使用stat时统计数字是否需要重置;

REPORTCOUNT报告已经处理的记录条数统计数字;

TLTRACE打开对于数据库日志的跟踪日志;

DISCARDFILE定义discardfile文件位置,如果处理中油记录出错会写入到此文件中;

DBOPTIONS指定对于某种特定数据库所需要的特殊参数;

TRANLOGOPTIONS指定在解析数据库日志时所需要的特殊参数,例如:对于裸设备,可能需要加入以下参数 rawdeviceoggset 0

WARNLONGTRANS指定对于超过一定时间的长交易可以在gsserr.log里面写入警告信息,本处配置为每隔3分钟检查一次场交易,对于超过2小时的进行警告;

 

gc2:配置Replicat进程

[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

 

gc1:验证数据加载

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

 

gc2:验证数据加载

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

 

gc2:确认初始化数据(由gc1传输而来)

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

 

gc1:检查Extract进程状态

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

 

gc2:检查Replicat进程状态

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.实时同步数据配置 

gc1:配置Extract进程

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

 

gc1:配置Pump进程

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

 

gc2:验证gc1Pump进程配置

[oracle@gc2ogg]$ lldirdat/    

total 0

-rw-rw-rw- 1oracle oinstall 0 Jun 18 10:36pa000000

 

gc2:配置Checkpoint

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.

 

gc2:配置Replicat进程

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

 

gc1 DML操作:insert

SQL>conn scott/tiger

Connected.

SQL>insert into tcustmerVALUES('HYL','HUANGCO.','BEIJING','AU');

1 row created.

SQL>commit;

Commit complete.

 

gc2:验证insert操作是否同步

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

 

gc1DML操作:update

SQL>update tcustmer setcity = 'SHANGHAI', state = 'CN' wherecust_code='HYL';

1 row updated.

SQL>commit;

Commit complete

gc2:验证update操作是否同步

SQL>select * fromtcustmer;

CUSTNAME                           CITY                 ST

---------------------------------- ----------------------

HYL  HUANGCO.                      SHANGHAI             CN

WILL BG SOFTWARECO.                SEATTLE              WA

JANE ROCKY FLYERINC.               DENVER               CO

 

gc1DML操作:delete

SQL>delete from tcustmerwhere CUST_CODE='HYL';

1 row deleted.

SQL>commit;

Commit complete.

 

gc2:验证delete操作是否同步

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住,oraclebug

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值