ogg单向DML同步配置

最新推荐文章于 2023-06-29 14:13:15 发布

上海阿丽

最新推荐文章于 2023-06-29 14:13:15 发布

阅读量1.9k

点赞数

分类专栏： Oracle OGG 文章标签：容灾 oracle OGG oracle goldengate 数据库容灾单向DML同步

本文链接：https://blog.csdn.net/u010587433/article/details/41862299

版权

Oracle OGG 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

接触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 sysdba
SQL> select supplemental_log_data_min from v$database;
     SUPPLEME
    ---------------
	NO
SQL> 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 immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list;
Database log mode             Archive Mode
Automatic archival            Enabled
Archive destination           /u01/archivelog
SQL> alter database open;

3.开启强制写日志

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

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

1.源端

[oracle@db ~]$ sqlplus / as sysdba
SQL> 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 sysdba
SQL> 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 subdirs

Creating subdirectories under current directory /ogg
Parameter files                /ogg/dirprm: already exists
Report files                   /ogg/dirrpt: created
Checkpoint files               /ogg/dirchk: created
Process status files           /ogg/dirpcs: created
SQL script files               /ogg/dirsql: created
Database definitions files     /ogg/dirdef: created
Extract data files             /ogg/dirdat: created
Temporary files                /ogg/dirtmp: created
Stdout files                   /ogg/dirout: created

目标端操作同上。

2.配置MGR进程

源端：

<pre name="code" class="sql">GGSCI (db) 2> dblogin userid ogg
	Password: 
	Successfully logged into database.
GGSCI (db) 3> add trandata scott.*
GGSCI (db) 4> exit
[oracle@db ogg] ./ggsci
GGSCI (db) 1> edit params mgr
PORT 7809
PURGEOLDEXTRACTS /ogg/dirdat, USECHECKPOINTS
GGSCI (db) 2> start mgr
	Manager started.
GGSCI (db) 3> info mgr
	Manager is running (IP port db.7809).
GGSCI (db) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

目标端：

<pre name="code" class="sql">[oracle@db1 ogg] ./ggsci
GGSCI (db1) 1> edit params mgr
PORT 7809
PURGEOLDEXTRACTS /ogg/dirdat, USECHECKPOINTS
GGSCI (db1) 2> start mgr
	Manager started.
GGSCI (db1) 3> info mgr
	Manager is running (IP port db1.7809).
GGSCI (db1) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     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]$ ./ggsci
GGSCI (db) 12> edit param extscott
EXTRACT EXTSCOTTSETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg, PASSWORD ogg
EXTTRAIL ./dirdat/aaTABLE scott.*;
GGSCI (db) 13> add extract extscott,tranlog,begin now
EXTRACT added.
GGSCI (db) 15> add exttrail ./dirdat/aa,extract extscott,megabytes 5 
EXTTRAIL added.
GGSCI (db) 16> start extscott
Sending START request to MANAGER ...
EXTRACT EXTSCOTT starting

源端pump进程

<pre name="code" class="sql">GGSCI (db) 21> edit param pumscott
EXTRACT PUMSCOTT
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
PASSTHRU
RMTHOST db1, MGRPORT 7809
RMTTRAIL ./dirdat/pa
TABLE scott.*;

GGSCI (db) 22> add extract pumscott,exttrailsource ./dirdat/aa
EXTRACT added.
GGSCI (db) 23> add rmttrail ./dirdat/pa,extract pumscott,megabytes 5
RMTTRAIL added.
GGSCI (db) 24> start pumscott

Sending START request to MANAGER ...
EXTRACT PUMSCOTT starting

目标端

GGSCI (db1) 3> edit params ./GLOBALS
CHECKPOINTTABLE ogg.ggschkpt
GGSCI (db1) 4> exit
[oracle@db1 ogg]$ggsci
GGSCI (db1) 1> dblogin userid ogg
Password: 
Successfully logged into database.
GGSCI (db1) 2> add checkpointtable

replicat进程

<pre name="code" class="sql">GGSCI (db1) 3> add replicat repscott,exttrail ./dirdat/pa
GGSCI (db1) 4> edit param repscott
REPLICAT REPSCOTT
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg, PASSWORD ogg
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/repscott_aa.DSC, PURGE
MAP scott.*, TARGET scott.*;
GGSCI (db1) 5> start repscott

Sending START request to MANAGER ...
REPLICAT REPSCOTT starting

5.查看运行情况

源端：

GGSCI (db) 15> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXTSCOTT    00:00:00      00:00:02    
EXTRACT     RUNNING     PUMSCOTT    00:00:00      00:00:10

目标端：

GGSCI (db1) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REPSCOTT    00:00:00      00:00:07

四、测试是否同步

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