主库配置

主库备库创建goldengate目录并授权

mkdir /goldengate
chmod –R 775 /goldengate

chown –R oracle:dba /goldengate

ftp ogg软件至相应的目录进行解压
tar -xvf ggmgr_unix_9i_64bit_v11_1_001.tar

主库与复制库配置参数

$Su - oracle

$vi .profile

LD_LIBRARY_PATH=/goldengate:$LD_LIBRARY_PATH

export LD_LIBRARY_PATH

PATH=/goldengate:$PATH

export PATH

主库与复制库创建goldengate用户，授权

SQL> CREATE USER goldengate

IDENTIFIED BY goldengate

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

QUOTA UNLIMITED ON USERS;

SQL> GRANT RESOURCE, DBA, CONNECT to goldengate;

建立归档目录

mkdir -p /archive_2

chmod -R 775 /archive_2

chown oracle:dba archive_2

修改归档地址

alter system set log_archive_dest_2=’location=/archive_2’ sid=’orcl’ scope=spfile;

alter system reset log_archive_duplex_dest scope=spfile;

alter system reset log_archive_dest scope=spfile;

    alter system switch logfile 切换日志到archive_2有归档

修改日志模式
    alter database force logging;
    alter database add supplemental log data;

安装goldengate，设置全局变量

cd /goldengate

ggsci

GGSCI > Create SubDirs

GGSCI > edit params ./GLOBALS

GGSchema goldengate

CheckpointTable goldengate.checkpoint

UnlockedTrailFiles

$ ggsci

GGSCI> DbLogin UserId goldengate, Password goldengate sysdba

GGSCI> Add CheckpointTable

配置manager进程

Edit params mgr

Port 7809

UserId goldengate, Password goldengate

AUTORESTART ER *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60

创建测试用户与表

SQL> create user testgg IDENTIFIED BY testgg ...

SQL> create table testgg.t (c char(10), n number(10));

GRANT RESOURCE, DBA, CONNECT to testgg;

添加传输对象

add trandata testgg.table (对象需要有唯一值)

#add trandata schema.*

配置exta进程

edit params exta

Extract exta

dynamicresolution

--SetEnv ( NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK" )

--SetEnv ( ORACLE_SID = "BILL" )

--warnlongtrans 12h, checkintervals 10m

--10分钟检查一次长交易，写入ggserr.log

UserId goldengate, Password goldengate

EXTTRAIL ./dirdat/et

TRANLOGOPTIONS EXCLUDEUSER goldengate

Table testgg.*;

添加进程
add extract exta,tranlog,begin now

添加列队
add exttrail ./dirdat/et,extract exta

启动进程
start exta

备注：delete extract exta（删除进程）

Info all（查看所有进程状态）

View report exta（查看进程明细信息）

配置pumpa进程

edit params pumpa

Extract pumpa

dynamicresolution

--SetEnv ( NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK" )

--SetEnv ( ORACLE_SID = "BILL" )

UserId goldengate, Password goldengate

RmtHost 192.168.xxx.xxx, MgrPort 7809

RmtTrail ./dirdat/pt

PASSTHRU

Table testgg.*;

添加进程
add extract pumpa,exttrailsource ./dirdat/et,begin now

添加列队
add rmttrail ./dirdat/pt,extract pumpa

启动进程

start pumpa

备注：到此主库配置完成，通过info all与view report pumpa来查看进程信息

备库配置

*到配置MGR进程之前与主库一致

增加repa进程

输入Edit params repa

Replicat repa

UserId goldengate, Password goldengate

AssumeTargetDefs

Reperror default,discard

DiscardFile ./dirrpt/repa.dsc, append, megabytes 50

dynamicresolution

Map testgg.*, Target testgg.*;

添加进程

add replicat repa ,exttrail ./dirdat/pt, checkpointtable goldengate.checkpoint
备注：repa进程配置完成，不start

进行数据同步，主库获取scn号
select dbms_flashback.get_system_change_number from dual

主库数据导出

expdp system/xxx schemas=xxx dumpfile=xxx.dmp DIRECTORY=dump FLASHBACK_SCN= xxxxxx tables=xxxx

备库数据导入

impdp system/xxxx directory=DATA_PUMP_DIR dumpfile=xxxx.dmp REMAP_SCHEMA=xxxx:dataogg

备库启动repa
start repa,aftercsn xxxx

备注:
OCI Error ORA-01403: no data found 报错可能导致原有为：在add trandata中未添加对应表。

ITPub博客

单机goldengate单向复制搭建

最新文章