安装配置ogg单向DML复制

-安装ogg

源端目标端都要安装配置

unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip -d /u01/ogg

tar -zxvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/ogg/ogg

配置环境变量

export PATH=$ORACLE_HOME/bin:/u01/ogg/ogg:$PATH

export GGATE=/u01/ogg/ogg

--验证ogg

ggsic

--创建ogg工作目录

create subdirs

GGSCI (oracle) 1>  create subdirs

Creating subdirectories under current directory /home/oracle

Parameter files                /home/oracle/dirprm: created

Report files                   /home/oracle/dirrpt: created

Checkpoint files               /home/oracle/dirchk: created

Process status files           /home/oracle/dirpcs: created

SQL script files               /home/oracle/dirsql: created

Database definitions files     /home/oracle/dirdef: created

Extract data files             /home/oracle/dirdat: created

Temporary files                /home/oracle/dirtmp: created

Stdout files                   /home/oracle/dirout: created

--在源端创建专用的表空间、schema、并授权。

create tablespace goldengate datafile '/u01/app/oracle/oradata/ogg/goldengate01.dbf' size 100m autoextend on;

create user goldengate identified by goldengate default tablespace goldengate temporary tablespace temp;

grant connect to goldengate;

grant alter any table to goldengate;

grant alter session to goldengate;

grant create session to goldengate;

grant flashback any table to goldengate;

grant select any dictionary to goldengate;

grant select any table to goldengate;

grant resource to goldengate;

grant select any transaction to goldengate;

--源端检查附加日志是否开启

select supplemental_log_data_min from v$database;

alter database add supplemental log data;

alter system switch logfile;

--源端目标端配置管理进程

edit params mgr

port 7839 

DYNAMICPORTLIST 7840-7850

AUTOSTART EXTRACT *

AUTORESTART EXTRACT *

PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

--同步hr用户的表，查看要复制的表的日志信息是完整的，确保是logging。把nologing变成logging。

select owner||'.'||table_name table_name,logging from dba_tables where owner='HR';

SQL> select owner||'.'||table_name table_name,logging                

  2  from dba_tables

  3  where owner='HR';

TABLE_NAME                                                    LOG

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

HR.REGIONS                                                    NO

HR.LOCATIONS                                                  NO

HR.DEPARTMENTS                                                NO

HR.JOBS                                                       NO

HR.EMPLOYEES                                                  NO

HR.JOB_HISTORY                                                NO

HR.COUNTRIES

7 rows selected.

alter table HR.REGIONS logging;

alter table HR.LOCATIONS logging;

alter table HR.DEPARTMENTS logging;

alter table HR.JOBS     logging;

alter table HR.EMPLOYEES logging;

alter table HR.JOB_HISTORY logging;

alter table HR.COUNTRIES logging;

--源端 

--配置hr下面表的表级附加日志。配置登陆用户。并检查日志是否添加成功。

--以goldengate这个schema登陆数据库。

dblogin userid goldengate, password goldengate

info trandata hr.*

add trandata hr.*

--配置源端的抓取进程。

add extract ext_demo, tranlog, begin now, threads 1

--添加源端的队列文件。

add EXTTRAIL ./dirdat/r1, extract ext_demo,megabytes 100

--编辑我们刚刚在源端配置的抓取进程的参数：

edit param ext_demo

EXTRACT ext_demo

setenv (ORACLE_SID=oracle)

setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

userid goldengate,password goldengate

REPORTCOUNT EVERY 1 MINUTES, RATE

numfiles 5000

DISCARDFILE ./dirrpt/ext_demo.dsc,APPEND,MEGABYTES 1000

DISCARDROLLOVER AT 3:00

exttrail ./dirdat/r1,megabytes 100

dynamicresolution

TRANLOGOPTIONS EXCLUDEUSER goldengate

TRANLOGOPTIONS convertucs2clobs

TABLE HR.*;

 start ext_demo

--配置datapump进程，将抓取数据传到目标主机。负责TCPIP通讯。

add extract dpe_demo, exttrailsource ./dirdat/r1

add rmttrail ./dirdat/t1,EXTRACT dpe_demo,MEGABYTES 100

edit param dpe_demo

extract dpe_demo

dynamicresolution

passthru

rmthost 192.168.56.5, mgrport 7839, compress

rmttrail ./dirdat/t1

numfiles 5000

TABLE HR.*;

start dpe_demo

--目标端 

--创建golengate软件使用的schema，并授权一些必要的特殊权限，注意与源端有所区别，保证我们能DML。

create tablespace goldengate datafile '/u01/app/oracle/oradata/ogg/goldengate01.dbf' size 100m autoextend on;

create user goldengate identified by goldengate default tablespace goldengate temporary tablespace temp;

grant connect to goldengate;

grant alter any table to goldengate;

grant alter session to goldengate;

grant create session to goldengate;

grant flashback any table to goldengate;

grant select any dictionary to goldengate;

grant select any table to goldengate;

grant resource to goldengate;

grant insert any table to goldengate;

grant update any table to goldengate;

grant delete any table to goldengate;

grant create any index to goldengate;

grant select any transaction to goldengate;

--为replicat进程创建checkpoint表：

dblogin userid goldengate,password goldengate

add checkpointtable goldengate.rep_demo_ckpt

--配置目标端replicate进程

add replicat rep_demo,exttrail ./dirdat/t1,checkpointtable goldengate.rep_demo_ckpt

--配置目标端replicate参数

edit param rep_demo

REPLICAT rep_demo

SETENV (ORACLE_SID=oracle)

SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")

USERID goldengate,PASSWORD goldengate

REPORTCOUNT EVERY 30 MINUTES, RATE

REPERROR DEFAULT, ABEND

numfiles 5000

--HANDLECOLLISIONS

assumetargetdefs

DISCARDFILE ./dirrpt/rep_demo.dsc, APPEND, MEGABYTES 1000

ALLOWNOOPUPDATES

MAP HR.*, TARGET HR.*;

start rep_demo

--测试

--源端;

select salary from hr.employees;

update hr.employees set salary=5000;

commit;

--目标端

select salary from hr.employees;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24626757/viewspace-2121273/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24626757/viewspace-2121273/