GoldenGate12.2從DataGuard備庫同步資料到其他Oracle資料庫

db_wjw發表於2016-06-14
GoOracle資料庫
一、軟體安裝
這個步驟在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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章