ORACLE GOLDENGATE DML 双向同步

1. 搭建环境续单实例DML单向同步之后。

2. 修改源库归档。

SQL> alter system set log_archive_dest_1='location=/home/oracle/archivelog' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218292 bytes
Variable Size              83888396 bytes
Database Buffers           75497472 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

3. 源库增加辅助日志

SQL> alter database add supplemental log data;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL>  select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

4. 修改目标端./GLOBALS配置

GGSCI (GOLDENGATE1) 6> edit params ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.chkpoint

目标端添加CHKPOINT表：

GGSCI (GOLDENGATE1) 7> dblogin userid goldengate, password goldengate
Successfully logged into database.

GGSCI (GOLDENGATE1) 9> add checkpointtable goldengate.chkpoint

Successfully created checkpoint table goldengate.chkpoint.

5. 修改MGR配置
源端：
增加一行：

PURGEOLDEXTRACTS /opt/gg/trails/w2*, USECHECKPOINTS, MINKEEPFILES 10

目标端：
增加一行：

PURGEOLDEXTRACTS /opt/gg/trails/w2*, USECHECKPOINTS, MINKEEPFILES 10

6. 增加EXTRACT

 add extract w2ext, tranlog, begin now

修改配置：

EXTRACT w2ext
USERID goldengate, PASSWORD goldengate
EXTTRAIL /home/oracle/gg/trails/w2
TRANLOGOPTIONS EXCLUDEUSER goldengate
IGNOREREPLICATES
DISCARDFILE w1extdsc,APPEND,MEGABYTES 5
TRANLOGOPTIONS ALTARCHIVELOGDEST /home/oracle/archivelog
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbf
FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT
STATOPTIONS REPORTFETCH
WARNLONGTRANS 1H, CHECKINTERVAL 5M
TABLE hr.*

 

添加该extract对应的trail文件，用来存储抽取的数据。单个文件大小设置为100MB

GGSCI (GOLDENGATE2) 5> add exttrail /home/oracle/gg/trails/w2,extract w2ext, megabytes 100
EXTTRAIL added.

7. 修改原源库EXTRAIL参数，增加以下两行：

TRANLOGOPTIONS EXCLUDEUSER goldengate
IGNOREREPLICATES

8. 增加PUMP

add extract w2extdp, exttrailsource /home/oracle/gg/trails/w2, begin now

修改配置：

GGSCI (GOLDENGATE2) 7> edit params w2extdp
EXTRACT w2extdp
USERID goldengate, PASSWORD goldengate
RMTHOST 192.168.102.156, MGRPORT 7809
RMTTRAIL /home/oracle/gg/trails/w2
DISCARDFILE w1extdpdsc,APPEND,MEGABYTES 5
TABLE hr.*;

 

9. 添加远程TRAIL文件：

GGSCI (GOLDENGATE2) 8> add rmttrail /home/oracle/gg/trails/w2, extract w2extdp, megabytes 100
RMTTRAIL added.

10. 增加目标端REP进程

GGSCI (GOLDENGATE1) 11> add replicat w2rep, exttrail /home/oracle/gg/trails/w2, checkpointtable goldengate.chkpoint
REPLICAT added.

修改配置参数：

GGSCI (GOLDENGATE1) 12> edit params w2rep
REPLICAT w2rep
ASSUMETARGETDEFS
USERID goldengate, PASSWORD goldengate
DISCARDFILE w2repdsc,APPEND,MEGABYTES 5
BATCHSQL
DBOPTIONS DEFERREFCONST
DBOPTIONS LOBWRITESIZE 102400
MAP hr.* , TARGET hr.* ;

11. 添加传输表

GGSCI (GOLDENGATE2) 9> dblogin userid goldengate, password goldengate
Successfully logged into database.

GGSCI (GOLDENGATE2) 10> add trandata hr.*

Logging of supplemental redo data enabled for table HR.COUNTRIES.

Logging of supplemental redo data enabled for table HR.DEPARTMENTS.

Logging of supplemental redo data enabled for table HR.EMPLOYEES.

Logging of supplemental redo data enabled for table HR.JOBS.

Logging of supplemental redo data enabled for table HR.JOB_HISTORY.

Logging of supplemental redo data enabled for table HR.LOCATIONS.

Logging of supplemental redo data enabled for table HR.REGIONS.

2012-09-21 01:19:32  WARNING OGG-00869  No unique key is defined for table 'T'. 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.T.

12. 查询源库SCN

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     517519

13， 启动源，目标库所有进程

GGSCI (GOLDENGATE1) 22> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     W1EXT       00:00:00      00:00:09    
EXTRACT     RUNNING     W1EXTDP     01:04:16      00:00:04    
REPLICAT    RUNNING     W2REP       00:00:00      00:00:06    

GGSCI (GOLDENGATE2) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     W2EXT       00:00:00      00:00:02    
EXTRACT     RUNNING     W2EXTDP     00:00:00      00:00:00    
REPLICAT    RUNNING     W1REP       00:00:00      00:00:04  

14， 数据测试：
原端至目标库：

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> insert into t(id) values(122);

1 row created.

SQL> commit;

Commit complete.


SQL> select * from t;

        ID
----------
       122

目标端到源库

SQL> insert into t(id) values(88);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

        ID
----------
       122
        88