ogg单向DML同步配置

来源：互联网发布：node.js是前端还是后端编辑：程序博客网时间：2024/05/16 02:50

接触ogg很长时间了，但是最近一直没有进一步深入的研究。前两天客户打电话让帮忙解决ogg不能同步的问题，不过j最后发现客户源端归档缺失，而且没有备份。那只能重做同步了。

操作过程明显生疏不少，借这个机会再熟悉起来，先从安装配置熟悉。
OGG介质下载： http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html?ssSourceSiteId=ocomen

ogg同步，当然至少两台db，源端和目标端：

源端：linux 5.5 + oracle 11.2.0.1 32bit + ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip

目标端：linux 5.8 + oracle 11.2.0.3 64bit + ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

一、源端，必须开启附加日志、归档和强制写日志

1.开启附加日志

[oracle@db ~]$ sqlplus / as sysdbaSQL> select supplemental_log_data_min from v$database;     SUPPLEME    ---------------NOSQL> alter database add supplemental log data;SQL> select supplemental_log_data_min from v$database;     SUPPLEME    ---------------YES

2.开户归档

SQL> alter system set log_archive_dest_1='location=/u01/archivelog';SQL> shutdown immediateSQL> startup mountSQL> alter database archivelog;SQL> archive log list;Database log mode             Archive ModeAutomatic archival            EnabledArchive destination           /u01/archivelogSQL> alter database open;

3.开启强制写日志

SQL> select force_logging from v$database;FOR------NOSQL> alter database force logging;SQL> select force_logging from v$database;FOR------YES

二、创建用户并授相关权限

1.源端

[oracle@db ~]$ sqlplus / as sysdbaSQL> create tablespace ggtbs datafile '/u01/oradata/db1/ggtbs01.dbf' size 200m autoextend on;SQL> create user ogg identified by ogg default tablespace ggtbs quota unlimited on ggtbs;SQL> grant connect,resource,create session,alter session to ogg;SQL> grant select any dictionary,select any table, alter any table, create any table,flashback any table to ogg;SQL> grant execute on dbms_flashback to ogg;

2.目标端

[oracle@db1 ~]$ sqlplus / as sysdbaSQL> create tablespace ggtbs datafile '/u01/oradata/db1/ggtbs01.dbf' size 200m autoextend on;SQL> create user ogg identified by ogg default tablespace ggtbs quota unlimited on ggtbs;SQL> grant connect,resource,create session,alter session to ogg;SQL> grant select any dictionary,select any table, alter any table to ogg;SQL> grant create table to ogg;

同步的表为scott下所有表，所以目标端ogg用户有对scott下的表有dml权限，否则开启的同步的表在目标端应用时报权限错误。可使用下面执行的结果批量授权

SQL> SELECT 'GRANT SELECT,DELETE,UPDATE,INSERT ON '|| table_name || ' TO ogg;' FROM USER_TABLES;

三、ogg配置

1.将Ogg介质分别上传到两台服务器的/ogg目录下，并解压

源端：

[root@db ~]# mkdir /ogg[root@db ~]# chown oracle:oinstall /ogg[oracle@db ~]$ cd /ogg[oracle@db ogg]$ unzip ogg112101_fbo_ggs_Linux_x86_db1_32bit.zip[oracle@db ogg]$ tar xvf fbo_ggs_Linux_x86_db1_32bit.tar[oracle@db ogg]$ ./ggsci GGSCI (db) 1> create subdirsCreating subdirectories under current directory /oggParameter files                /ogg/dirprm: already existsReport files                   /ogg/dirrpt: createdCheckpoint files               /ogg/dirchk: createdProcess status files           /ogg/dirpcs: createdSQL script files               /ogg/dirsql: createdDatabase definitions files     /ogg/dirdef: createdExtract data files             /ogg/dirdat: createdTemporary files                /ogg/dirtmp: createdStdout files                   /ogg/dirout: created

目标端操作同上。

2.配置MGR进程

源端：

<pre name="code" class="sql">GGSCI (db) 2> dblogin userid oggPassword: Successfully logged into database.GGSCI (db) 3> add trandata scott.*GGSCI (db) 4> exit[oracle@db ogg] ./ggsciGGSCI (db) 1> edit params mgrPORT 7809PURGEOLDEXTRACTS /ogg/dirdat, USECHECKPOINTSGGSCI (db) 2> start mgrManager started.GGSCI (db) 3> info mgrManager is running (IP port db.7809).GGSCI (db) 4> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING

目标端：

<pre name="code" class="sql">[oracle@db1 ogg] ./ggsciGGSCI (db1) 1> edit params mgrPORT 7809PURGEOLDEXTRACTS /ogg/dirdat, USECHECKPOINTSGGSCI (db1) 2> start mgrManager started.GGSCI (db1) 3> info mgrManager is running (IP port db1.7809).GGSCI (db1) 4> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING

3.数据初始化

目标端数据先要与源端初步一致，即数据初始化。这个操作可选择多种方式，包括ogg配置、导入导出/数据泵等。

对于测试环境没有关系，使用ogg自带的配置可以慢慢初始化（http://blog.csdn.net/u010587433/article/details/41864321）；

对于数据量比较大可选择数据泵等方式。

这里选择数据泵，源端：

<pre name="code" class="sql">SQL> create directory dump_dir as ‘/u01/dump/'SQL> grant read,write on directory dump_dir to scott;[oracle@db ~]$ expdp scott/scott directory=dump_dir dumpfile=scott_20141210.dmp logfile=scott_20141210.log[oracle@db ~]$ scp /u01/dump/scott_20141210.dmp 192.168.1.81:/u01/dump/

目标端：

SQL> create directory dump_dir as ‘/u01/dump/'SQL> grant read,write on directory dump_dir to scott;[oracle@db1 ~]$ impdp scott/scott directory=dump_dir dumpfile=scott_20141210.dmp logfile=scott_20141210.log

4.配置三个进程，首先实现DML同步

源端extract进程

<pre name="code" class="sql">[oracle@db ogg]$ ./ggsciGGSCI (db) 12> edit param extscottEXTRACT EXTSCOTTSETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)USERID ogg, PASSWORD oggEXTTRAIL ./dirdat/aaTABLE scott.*;GGSCI (db) 13> add extract extscott,tranlog,begin nowEXTRACT added.GGSCI (db) 15> add exttrail ./dirdat/aa,extract extscott,megabytes 5 EXTTRAIL added.GGSCI (db) 16> start extscottSending START request to MANAGER ...EXTRACT EXTSCOTT starting

源端pump进程

<pre name="code" class="sql">GGSCI (db) 21> edit param pumscottEXTRACT PUMSCOTTSETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)PASSTHRURMTHOST db1, MGRPORT 7809RMTTRAIL ./dirdat/paTABLE scott.*;GGSCI (db) 22> add extract pumscott,exttrailsource ./dirdat/aaEXTRACT added.GGSCI (db) 23> add rmttrail ./dirdat/pa,extract pumscott,megabytes 5RMTTRAIL added.GGSCI (db) 24> start pumscottSending START request to MANAGER ...EXTRACT PUMSCOTT starting

目标端

GGSCI (db1) 3> edit params ./GLOBALSCHECKPOINTTABLE ogg.ggschkptGGSCI (db1) 4> exit[oracle@db1 ogg]$ggsciGGSCI (db1) 1> dblogin userid oggPassword: Successfully logged into database.GGSCI (db1) 2> add checkpointtable

replicat进程

<pre name="code" class="sql">GGSCI (db1) 3> add replicat repscott,exttrail ./dirdat/paGGSCI (db1) 4> edit param repscottREPLICAT REPSCOTTSETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)USERID ogg, PASSWORD oggHANDLECOLLISIONSASSUMETARGETDEFSDISCARDFILE ./dirrpt/repscott_aa.DSC, PURGEMAP scott.*, TARGET scott.*;GGSCI (db1) 5> start repscottSending START request to MANAGER ...REPLICAT REPSCOTT starting

5.查看运行情况

源端：

GGSCI (db) 15> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     RUNNING     EXTSCOTT    00:00:00      00:00:02    EXTRACT     RUNNING     PUMSCOTT    00:00:00      00:00:10

目标端：

GGSCI (db1) 8> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           REPLICAT    RUNNING     REPSCOTT    00:00:00      00:00:07

四、测试是否同步

在源端对scott用户下表进行dml操作，在目标端查看是否同步。

0 0