ITPub博客

首页 > 数据库 > Oracle > GoldenGate12.2从DataGuard备库同步数据到其他Oracle数据库

GoldenGate12.2从DataGuard备库同步数据到其他Oracle数据库

原创 Oracle 作者:db_wjw 时间:2016-06-14 19:24:45 0 删除 编辑

一、软件安装
这个步骤在DG主库和备库,以及要同步的目标库上都操作
1、环境变量配置:
vi .bash_profile
export ORACLE_SID=ncf
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=/u02/ogg:$PATH
export LD_LIBRARY_PATH=/u02/ogg:$LD_LIBRARY_PATH

2、安装
将安装介质上传到/u02/ogg_software目录下
chown -R oracle:oinstall /u02/ogg_software/
创建安装目录:
# mkdir -p /u02/ogg
# chown oracle:oinstall /u02/ogg
解压安装文件:
# cd /u02/ogg_software
# unzip V100692-01.zip
# chown -R oracle:oinstall fbo_ggs_Linux_x64_shiphome
编辑静默安装文件:
$ cd /u02/ogg_software/fbo_ggs_Linux_x64_shiphome/Disk1/response
$ cp oggcore.rsp installogg.rsp
$ vi installogg.rsp
第一行不要修改,剩下根据自己实际情况修改
内容如下:
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2
INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=/u02/ogg
START_MANAGER=true
MANAGER_PORT=7809
DATABASE_LOCATION=/u02/app/oracle/product/11.2.0/dbhome_1
INVENTORY_LOCATION=/u02/app/oraInventory
UNIX_GROUP_NAME=oinstall
静默安装:
$ cd /u02/ogg_software/fbo_ggs_Linux_x64_shiphome/Disk1
$ ./runInstaller -silent -nowait -responseFile /u02/ogg_software/fbo_ggs_Linux_x64_shiphome/Disk1/response/installogg.rsp

如果提示Checking swap space: 0 MB available, 150 MB required.    Failed <<<<
解决方法:可以修改/u02/ogg_software/fbo_ggs_Linux_x64_shiphome/Disk1/install下的oraparam.ini文件,将SWAP_SPACE=150行注释掉。

3、oracle数据库配置(该步骤在主库上操作)
$ sqlplus / as sysdba;
SQL> alter database force logging;
SQL> alter database add supplemental log data;
SQL> select supplemental_log_data_min, force_logging from v$database;
SUPPLEME FOR
-------- ---
YES     YES
SQL> alter system switch logfile;
使数据库支持goldengate复制:
SQL> alter system set enable_goldengate_replication=true scope=both;

4、创建goldengate用户(该步骤在主库上操作)
create tablespace goldengate datafile '/u02/app/oracle/oradata/ncf/goldengate01.dbf' size 100m autoextend on maxsize unlimited;
create user goldengate identified by goldengate default tablespace goldengate;
grant dba to goldengate;

5、安全认证相关:
$ cd /u02/ogg
$ ./ggsci
注意下面两个语句末尾不能有分号,否则报ERROR: Invalid command.
GGSCI> add credentialstore
GGSCI> alter credentialstore add user goldengate,password goldengate
GGSCI> dblogin useridalias goldengate
Successfully logged into database.

二、在DG主库上添加trandata
$ ./ggsci
GGSCI> dblogin useridalias goldengate
GGSCI> add trandata wjw.T
GGSCI> add trandata wjw.TCUSTMER
GGSCI> add trandata wjw.TCUSTORD
GGSCI> info trandata wjw.*


三、源端配置

1、配置manager进程:
$ cd /ywogg/oggbj
$ ./ggsci
GGSCI> edit params mgr
内容如下:
port 7800
DYNAMICPORTLIST 7801-7809
--AUTORESTART ER *,RETRIES 5,WAITMINUTES 7
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45


2、配置抽取进程:
GGSCI> add extract extncf, tranlog, begin now
GGSCI> add exttrail ./dirdat/et extract extncf, megabytes 100
GGSCI> edit params extncf
内容如下:
EXTRACT extncf
SETENV (ORACLE_HOME="/u02/app/oracle/product/11.2.0/dbhome_1")
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
setenv (ORACLE_SID="ncf")
useridalias goldengate
GETTRUNCATES
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/extncf.dsc,APPEND,MEGABYTES 1000

WARNLONGTRANS 2h,CHECKINTERVAL 10m
EXTTRAIL ./dirdat/et

TRANLOGOPTIONS  CONVERTUCS2CLOBS
TRANLOGOPTIONS EXCLUDEUSER goldengate
TRANLOGOPTIONS MINEFROMACTIVEDG
DBOPTIONS ALLOWUNUSEDCOLUMN
DYNAMICRESOLUTION

FETCHOPTIONS FETCHPKUPDATECOLS

--table
table WJW.T;
table WJW.TCUSTMER;
table WJW.TCUSTORD;


3、配置datapump进程:
GGSCI> add extract dpencf, exttrailsource ./dirdat/et
GGSCI> add rmttrail ./dirdat/rt, extract dpencf, megabytes 100
GGSCI> edit params dpencf
内容如下:
EXTRACT dpencf
RMTHOST 10.44.155.139, MGRPORT 7800, compress
PASSTHRU
RMTTRAIL ./dirdat/rt
DYNAMICRESOLUTION

--table
table WJW.T;
table WJW.TCUSTMER;
table WJW.TCUSTORD;

启动进程:
$ ./ggsci
GGSCI> start mgr
GGSCI> start extncf


四、目标端配置

1、目标端GLOBALS配置:
GGSCI> edit params ./GLOBALS
内容如下:
CHECKPOINTTABLE goldengate.oggchkpt
GGSCI> exit
Shell> ggsci
(重新登陆以激活GLOBALS参数)
GGSCI> dblogin useridalias goldengate
GGSCI> add checkpointtable


2、配置目标端manager进程:
GGSCI> edit params mgr
内容如下:
port 7800
DYNAMICPORTLIST 7801-7809
--AUTORESTART ER *,RETRIES 5,WAITMINUTES 7
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

3、配置目标端复制进程:
GGSCI> add replicat repncf, exttrail ./dirdat/rt
GGSCI> edit params repncf
内容如下:
REPLICAT repncf
SETENV (ORACLE_HOME="/u02/app/oracle/product/11.2.0/dbhome_1")
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
setenv (ORACLE_SID="ncfc")
useridalias goldengate
--SQLEXEC "ALTER SESSION SET CONSTRAINTS=DEFERRED"
REPORT AT 01:59
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
--numfiles 5000

--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt/repncf.dsc, APPEND, MEGABYTES 1000
GETTRUNCATES
ALLOWNOOPUPDATES

--table
map WJW.T, target WJW.T;
map WJW.TCUSTMER, target WJW.TCUSTMER;
map WJW.TCUSTORD, target WJW.TCUSTORD;


五、初始化数据:
源端:
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
        19436335

导出数据:
需要在主库上操作:
expdp \'/ as sysdba\' directory=data_pump_dir dumpfile=wjw.dmp logfile=wjw.log tables=wjw.t,wjw.tcustmer,wjw.tcustord  flashback_scn=19436335

目标端:
cd /u02/app/oracle/admin/ncf/dpdump
scp oracle@10.51.110.38:/u02/app/oracle/admin/ncf/dpdump/wjw.dmp .
impdp \'/ as sysdba\' directory=data_pump_dir dumpfile=wjw.dmp logfile=wjw.log

六、启动进程:
源端:
GGSCI> start extncf
目标端:
GGSCI> alter replicat repncf extseqno 0, extrba 0
此处的extseqno后面的0代表trail文件的序号,需要到ogg根目录下的dirdat中检查,如果初始配置的话,就是0
GGSCI> start repncf aftercsn 19436335


七、测试数据同步:
源端对此三张表更改数据,发现目标端的数据也同步更新。



报错汇总:
extract启动:
2016-04-22 16:59:45  WARNING OGG-10173  (extncf.prm) line 16: Parsing error, [DYNAMICRESOLUTION] is deprecated.
2016-04-22 16:59:45  ERROR   OGG-10175  (extncf.prm) line 13: Parsing error, [convertucs2clobs] is obsolete.
2016-04-22 17:03:05  WARNING OGG-02810  A relative timestamp, such as NOW, was used as starting position for Extract on an Oracle Active Data Guard standby database.

datapump启动:
2016-04-22 16:59:57  WARNING OGG-10173  (dpencf.prm) line 5: Parsing error, [DYNAMICRESOLUTION] is deprecated.


问题1:启动抽取进程报错:
2016-04-22 17:03:05  ERROR   OGG-00868  The number of Oracle redo threads (2) is not the same as the number of checkpoint threads (1). EXTRACT groups on RAC
systems should be created with the THREADS parameter (e.g., ADD EXT <group name>, TRANLOG, THREADS 2, BEGIN...).

原因:
参见Doc ID 2004661.1
For classic extract on ADG (and physical dataguard), we only support that when primary and standby have same number of threads.
to check dataguard thread number, check v$standby_log.
SQL> select group#,thread# from v$standby_log;

    GROUP#    THREAD#
---------- ----------
    11        1
    12        1
    13        0
    14        0
There are 2 thread# for dataguard logs.

解决办法:
Please check if hread# 0 may be dropped. If yes, it may be dropped as following:

alter database recover managed standby database cancel;
alter database drop standby logfile group 13;
alter database drop standby logfile group 14;
alter database recover managed standby database using current logfile disconnect;

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

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-08-21

  • 博文量
    96
  • 访问量
    452142