ogg單機同步復制操作手冊

1、一、 概要信息1.1文檔簡介該文檔主要用于指導利用OGG來進行數(shù)據(jù)的同步復制，其中源數(shù)據(jù)庫為單機，目標數(shù)據(jù)庫為單機。OGG相關(guān)安裝軟件版本為12c，主要包括如下：1. OGG，主要用于源庫與目標庫的數(shù)據(jù)同步2. OGG Veridata，主要用于同步數(shù)據(jù)校驗溫馨提醒：1. 請仔細閱讀該文檔注意事項！1.2機器環(huán)境1.2.1源機器信息操作系統(tǒng)版本Redhat 6.4數(shù)據(jù)庫版本主機名oggsrcIP地址1.2.2目標機器信息操作系統(tǒng)版本Redhat 6.4集群數(shù)據(jù)庫版本主機名oggtgtIP地址1.3軟件環(huán)境軟件名稱軟件版本部署節(jié)點備注Oracle GoldenGateoggsrc（源庫） ogg

2、tgt（目標庫）Oracle ADRoggsrc（源庫） oggtgt（目標庫）JDK1.8oggsrc（源庫） oggtgt（目標庫）1.4軟件包信息軟件名稱軟件包名稱Oracle GoldenGate 12fbo_ggs_Linux_x64_shiphome.zipOracle Application Development Runtime Infrastructure（ADR）JDKjdk-8u112-linux-x64.rpm1.5注意事項-軟件安裝：對于RAC的OGG，有三種方式選擇:1.oracle推薦直接安裝在共享文件系統(tǒng)上（共享存儲/OCFS/AFS）；2.也可以安裝在RAC所

3、有節(jié)點的本地目錄，但必需保證所有節(jié)點的安裝路徑一致；3.還可以只安裝其中一個節(jié)點，但必需保證安裝OGG的節(jié)點可以訪問其它節(jié)點的歸檔目錄(本文檔使用)-關(guān)于歸檔本文檔中，源庫歸檔目錄為本地目錄-關(guān)于IP配置：RAC上的VIP必需為固定IP且與管理IP是同一網(wǎng)段，不可以是DHCP獲取-關(guān)于主機hosts表安裝過程中涉及到的主機名及IP對應(yīng)關(guān)系都配置到每臺主機的hosts表中-關(guān)于時間同步：所有節(jié)點必需配有時間同步功能保證所有節(jié)點的操作系統(tǒng)時間一致，因為OGG的EXTRACT進程是通過對比commit時間（即操作系統(tǒng)時間）來決定是否傳輸相關(guān)的數(shù)據(jù)-關(guān)于COMPATIBLE：確保所有節(jié)點的數(shù)據(jù)庫COM

4、PATIBLE參數(shù)已經(jīng)設(shè)置-OGG數(shù)據(jù)存放路徑將OGG數(shù)據(jù)存放路徑mount在單邊，防止相同的進程在其它節(jié)點啟動引起進程沖突-源庫數(shù)據(jù)對象是否支持OGG對數(shù)據(jù)對象支持有一定的限制，可以通過腳本提前檢查-關(guān)于數(shù)據(jù)遷移方式本文檔推薦使用數(shù)據(jù)泵方式進行數(shù)據(jù)遷移，因此需要提前將目標數(shù)據(jù)庫建好二、 OGG搭建環(huán)境準備2.1源庫操作2.1.1開啟歸檔-歸檔目錄為所有節(jié)點共享目錄-節(jié)點SQL> alter system set log_archive_dest_1='location=/arch' scope=spfile sid='*' System altered.

5、SQL> alter system set recyclebin=off scope=spfile;-For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.System altered. SQL> shutdown immediate OGGSRC:/home/oracle$export ORACLE_SID=oggsrcOGGSRC:/home/oracle$sqlplus / as sysdba SQL*Plus

6、: Release 11.2.0.1.0 - Production on Mon Sep 19 16:29:11 2016 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 4275781632 bytes Fixed Size 2160600 bytes Variable Size 2365589544 bytes Databa

7、se Buffers 1895825408 bytes Redo Buffers 12206080 bytes Database mounted. SQL> alter database archivelog;Database altered.SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /archOldest online log sequence 4Next log sequence to archive 7Current log

8、 sequence 7 SQL> alter database open;Database altered.SQL> alter system archive log current;System altered.2.1.2開啟附加日志功能和force_logging-節(jié)點SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;SUPPLEME FOR- -NO NOSQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;Database altered

9、.SQL> alter database force logging;Database altered.SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;SUPPLEME FOR- -YES YESSQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;System altered.2.2目標庫操作2.2.1開啟歸檔（可不必打開）-節(jié)點，歸檔目錄/arch非共享SQL> alter system se

10、t log_archive_dest_1='location=/arch' scope=spfile sid='*' System altered. $export ORACLE_SID=oggtgt $sqlplus / as sysdba SQL> startup mount; ORACLE instance started. Total System Global Area 4275781632 bytes Fixed Size 2160600 bytes Variable Size 2365589544 bytes Database Buffers

11、 1895825408 bytes Redo Buffers 12206080 bytes Database mounted. SQL> alter database archivelog;Database altered.SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /archOldest online log sequence 4Next log sequence to archive 7Current log sequence

12、7 SQL> alter database open;Database altered.SQL> alter system archive log current;System altered.2.2.2開啟參數(shù)ENABLE_GOLDENGATE_REPLICATION版本的必須設(shè)置該參數(shù)為trueSQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;System altered.三、 OGG搭建3.1 OGG軟件安裝-此處只在源庫節(jié)點xxxx和目標庫節(jié)點xxxx上操作，其它節(jié)點不操作！此處

13、只以xxx為例！創(chuàng)建OGG目錄-節(jié)點rootoggsrc media# mkdir -p /u01/ogg/dirdatrootoggsrc media# chown -R oracle:oinstall /u01/oggrootoggsrc media# chmod -R 775 /u01/ogg rootoggsrc media# su - oracle3.1.2配置OGG用戶環(huán)境變量-此處用oracle用戶安裝OGG添加或修改oracle用戶環(huán)境變量export OGG_HOME=/u01/oggexport PATH=.:$PATH:$HOME/bin:$ORACLE_HOME/bin

14、:$ORA_CRS_HOME/bin:$OGG_HOMEexport LIBPATH=$ORACLE_HOME/lib:$OGG_HOME3.1.3安裝OGG軟件oracleoggsrc $ cd /u01/media/ oracleoggsrc media$ ls -lrttotal 4616920drwxr-xr-x 7 oracle oinstall 4096 Aug 26 2013 database-rw-r-r-. 1 oracle oinstall 1151304589 Jul 18 20:43 p13390677_112040_Linux-x86-64_2of7.zip-rw-r

15、-r-. 1 oracle oinstall 1395582860 Jul 18 20:44 p13390677_112040_Linux-x86-64_1of7.zip-rw-r-r- 1 oracle oinstall-rw-r-r- 1 oracle oinstall 475611228 Jul 19 00:00 fbo_ggs_Linux_x64_shiphome.zip-rw-r-r- 1 oracle oinstall 167741674 Jul 19 00:04 jdk-8u112-linux-x64.rpm-rw-r-r- 1 oracle oinstall-rw- 1 ora

16、cle oinstall 4194304 Jul 19 06:10 coreoracleoggsrc media$ unzip -q fbo_ggs_Linux_x64_shiphome.ziporacleoggsrc media$ cd fbo_ggs_Linux_x64_shiphome$cd Disk1$lsinstall response runInstaller stageOGGSRC:/u01/media/fbo_ggs_AIX_ppc_shiphome/Disk1$./runInstaller3.2源庫OGG配置-配置OGG全部用oracle用戶去操作！3.2.1創(chuàng)建OGG應(yīng)用子

17、目錄OGGSRC:/home/oracle$cd /u01/oggOGGSRC:/u01/ogg$ggsciOracle GoldenGate Command Interpreter for OracleAIX 6, ppc, 64bit (optimized), Oracle 11g on Jan 21 2016 09:52:07Operating system character set identified as ISO-8859-1.Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.G

18、GSCI (OGGSRC) 1> create subdirsCreating subdirectories under current directory /u01/oggParameter files /u01/ogg/dirprm: already existsReport files /u01/ogg/dirrpt: createdCheckpoint files /u01/ogg/dirchk: createdProcess status files /u01/ogg/dirpcs: createdSQL script files /u01/ogg/dirsql: create

19、dDatabase definitions files /u01/ogg/dirdef: createdExtract data files /u01/ogg/dirdat: createdTemporary files /u01/ogg/dirtmp: createdStdout files /u01/ogg/dirout: createdGGSCI (OGGSRC) 2> exit3.2.2創(chuàng)建OGG用戶OGGSRC:/u01/ogg$sqlplus / as sysdbaSQL> create tablespace ogg datafile '/oradata/ogg

20、src/ogg01.dbf' size 2G ;Tablespace created.SQL> create user ogg identified by ogg default tablespace ogg temporary tablespace temp account unlock;User created.SQL> grant dba to ogg;Grant succeeded.3.2.3授權(quán)OGG用戶OGGSRC:/u01/ogg$sqlplus / as sysdbaSQL> grant execute on utl_file to ogg;Grant

21、 succeeded.=SQL> /u01/ogg/marker_setup.sqlEnter Oracle GoldenGate schema name:oggScript complete.=SQL> /u01/ogg/ddl_setup.sqlEnter Oracle GoldenGate schema name:oggSTATUS OF DDL REPLICATION-SUCCESSFUL installation of DDL Replication software componentsScript complete.=SQL> /u01/ogg/role_set

22、up.sqlEnter GoldenGate schema name:oggRole setup script completeGRANT GGS_GGSUSER_ROLE TO <loggedUser>=SQL> grant ggs_ggsuser_role to oggtgt;Grant succeeded.=SQL> /u01/ogg/ddl_enable.sqlTrigger altered.=SQL> /u01/ogg/marker_status.sqlPlease enter the name of a schema for the GoldenGat

23、e database objects:oggSetting schema name to OGGMARKER TABLE-OKMARKER SEQUENCE-OK=SQL> ?/rdbms/admin/dbmspoolPackage created.Grant succeeded.=SQL> /u01/ogg/ddl_pin.sql oggPL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.=SQ

24、L> /u01/ogg/sequence.sqlPlease enter the name of a schema for the GoldenGate database objects:Setting schema name to OGGSTATUS OF SEQUENCE SUPPORT-SUCCESSFUL installation of Oracle Sequence Replication support=SQL> grant execute on oggtgt.updatesequence to oggtgt;Grant succeeded.3.2.4創(chuàng)建GLOBALS

25、文件OGGSRC:/u01/ogg/dirdat$cd $OGG_HOMEOGGSRC:/u01/ogg$ggsciOracle GoldenGate Command Interpreter for OracleAIX 5L, ppc, 64bit (optimized), Oracle 11g on Apr 23 2012 05:03:51Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.GGSCI (OGGSRC) 20> edit params ./GLOBALS#輸入如下內(nèi)容#G

26、GSCHEMA ogg3.2.5創(chuàng)建Manager配置文件#編輯MGR配置文件，GGSCI (OGGSRC) 1> edit params mgr #輸入如下內(nèi)容#PORT 7809DYNAMICPORTLIST 7810-7820, 7830AUTOSTART EXTRACT *AUTORESTART EXTRACT *, RETRIES 4, WAITMINUTES 2STARTUPVALIDATIONDELAY 5PURGEOLDEXTRACTS /u01/ogg/dirdat/*, USECHECKPOINTS, minkeepdays 7創(chuàng)建primary Extract配置文

27、件GGSCI (OGGSRC) 9> edit params exee#輸入如下內(nèi)容#EXTRACT exeeSETENV (ORACLE_SID = "oggsrc")SETENV (ORACLE_HOME= )SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")USERID ogg, PASSWORD oggWARNLONGTRANS 2h, CHECKINTERVAL 300sFETCHOPTIONS NOUSESNAPSHOTexttrail /u01/ogg/dirdat/ltdiscardfil

28、e /u01/ogg/dirrpt/ee.dsc,append, megabytes 500gettruncatesddl &include objname MESIF.* &include objname AHBIUSR.* &include objtype 'USER'ddloptions addtrandatatable MESIF.*;sequence MESIF.*;table AHBIUSR.*;sequence AHBIUSR.*;#檢查確認EXEE配置文件內(nèi)容無誤OGGSRC:/u01/ogg$/u01/ogg/checkprm /u01

29、/ogg/dirprm/exee.prm -C extract -m Classic -V3.2.7創(chuàng)建data pump配置文件GGSCI (OGGSRC) 9> edit params dpee#輸入如下內(nèi)容#EXTRACT dpeeSETENV (ORACLE_SID = "oggsrc")SETENV (ORACLE_HOME= )SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK"), MGRPORT 7809, compressRMTTRAIL /u01/ogg/dirdat/rtgettrunc

30、atestable MESIF.*;sequence MESIF.*;table AHBIUSR.*;sequence AHBIUSR.*;#檢查確認DPEE配置文件內(nèi)容無誤OGGSRC:/u01/ogg/checkprm /u01/ogg/dirprm/dpee.prm -C extract -m Classic -V3.2.8為需要抽取數(shù)據(jù)的schema增加trandata#此步根據(jù)對象表數(shù)量的多少需要相應(yīng)的時間，GGSCI (OGGSRC) 13> dblogin userid ogg password oggSuccessfully logged into database.GG

31、SCI (OGGSRC) 14>add trandata MESIF.*GGSCI (OGGSRC) 14>add trandata AHBIUSR.*3.2.9配置extract進程GGSCI (OGGSRC) 15> add ext exee,tranlog,begin nowEXTRACT added.GGSCI (OGGSRC) 16> add exttrail /u01/ogg/dirdat/lt, ext exee, megabytes 50EXTTRAIL added.3.2.10配置data pump進程GGSCI (OGGSRC) 17> add

32、 ext dpee, exttrailsource /u01/ogg/dirdat/ltEXTRACT added.GGSCI (OGGSRC) 19> add rmttrail /u01/ogg/dirdat/rt, ext dpee, megabytes 50RMTTRAIL added.3.3目標庫OGG配置-配置OGG全部用oracle用戶去操作！3.3.1創(chuàng)建OGG應(yīng)用子目錄oggtgt:/home/oracle$cd /u01/oggoggtgt:/u01/ogg$ggsciOracle GoldenGate Command Interpreter for OracleAIX

33、 6, ppc, 64bit (optimized), Oracle 11g on Jan 21 2016 09:52:07Operating system character set identified as ISO-8859-1.Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.GGSCI (oggtgt) 1> create subdirsCreating subdirectories under current directory /u01/oggParameter files

34、 /u01/ogg/dirprm: already existsReport files /u01/ogg/dirrpt: createdCheckpoint files /u01/ogg/dirchk: createdProcess status files /u01/ogg/dirpcs: createdSQL script files /u01/ogg/dirsql: createdDatabase definitions files /u01/ogg/dirdef: createdExtract data files /u01/ogg/dirdat: createdTemporary

35、files /u01/ogg/dirtmp: createdStdout files /u01/ogg/dirout: createdGGSCI (oggtgt) 2>3.3.2創(chuàng)建OGG用戶oggtgt:/home/oracle$sqlplus / as sysdbaSQL> create tablespace oggtgt datafile '/oradata/oggtgt/ogg01.dbf' size 2G ;Tablespace created.SQL> create user ogg identified by ogg default tables

36、pace ogg temporary tablespace temp account unlock;User created.SQL> grant dba to ogg;Grant succeeded.3.3.3授權(quán)OGG用戶oggtgt:/u01/ogg$sqlplus / as sysdba=SQL> /u01/ogg/sequence.sqlPlease enter the name of a schema for the GoldenGate database objects:Setting schema name to OGGSTATUS OF SEQUENCE SUPP

37、ORT-SUCCESSFUL installation of Oracle Sequence Replication support=SQL> grant execute on ogg.replicatesequence to ogg;Grant succeeded.3.3.4創(chuàng)建GLOBALS配置文件oggtgt:/u01/ogg/dirdat$cd $OGG_HOMEoggtgt:/u01/ogg$ggsciGGSCI (oggtgt) 2> edit params ./GLOBALS#輸入如下內(nèi)容#GGSCHEMA oggCHECKPOINTTABLE ogg.ckpttab

38、le3.3.5創(chuàng)建Manager配置文件#編輯MGR配置文件GGSCI (oggtgt) 3> edit params mgr #輸入如下內(nèi)容#PORT 7809DYNAMICPORTLIST 7810-7820, 7830AUTOSTART REPLICAT *AUTORESTART REPLICAT *, RETRIES 4, WAITMINUTES 2STARTUPVALIDATIONDELAY 5PURGEOLDEXTRACTS /u01/ogg/dirdat/*, USECHECKPOINTS, minkeepdays 7添加checkpointtableGGSCI (oggt

39、gt) 5> DBLOGIN USERID ogg, PASSWORD oggSuccessfully logged into database.GGSCI (oggtgt) 6> ADD CHECKPOINTTABLE ogg.ckpttableSuccessfully created checkpoint table ogg.ckpttable.創(chuàng)建replicat配置文件GGSCI (oggtgt) 8> edit params rpee#輸入如下內(nèi)容#REPLICAT rpeeSETENV (ORACLE_SID = "oggtgt")SETENV

40、 (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")USERID ogg, PASSWORD ogg-handlecollisionsassumetargetdefsallownoopupdatesdiscardfile /u01/ogg/dirrpt/ee.dsc,append, megabytes 500gettruncatesddl &include objname MESIF.* &include objnam

41、e AHBIUSR.* &include objtype 'USER'DBOPTIONS DEFERREFCONSTmap MESIF.*, target MESIF.*;map AHBIUSR.*, target AHBIUSR.*;#確認RPEE配置文件內(nèi)容oggtgt:/u01/ogg$/u01/ogg/checkprm /u01/ogg/dirprm/rpee.prm -C replicat -m Classic -V配置replicat進程GGSCI (oggtgt) 9> add rep rpee,exttrail /u01/ogg/dirdat/rt

42、 CHECKPOINTTABLE ogg.ckpttableREPLICAT added.3.4測試啟動OGG-配置OGG全部用oracle用戶去操作！3.4.1目標庫啟動OGGGGSCI (oggtgt) 9> start mgrManager started.GGSCI (oggtgt) 11> start replicat rpeeREPLICAT RPEE is already running.GGSCI (oggtgt) 12> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUN

43、NING REPLICAT RUNNING RPEE 00:00:00 00:00:06 -等待章節(jié)3.4.2的DPEE進程啟動正常后，關(guān)閉REPLICAT rpeeGGSCI (oggtgt) 13> stop replicat rpee3.4.2源庫啟動OGGGGSCI (OGGSRC) 17> start mgrManager started.GGSCI (OGGSRC) 18> info mgrManager is running (IP port OGGSRC.7809).GGSCI (OGGSRC) 68> start EXTRACT exeeSending

44、 START request to MANAGER .EXTRACT EXEE startingGGSCI (OGGSRC) 69> start EXTRACT dpeeSending START request to MANAGER .EXTRACT DPEE startingGGSCI (OGGSRC) 1> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING DPEE 00:00:00 00:00:03 EXTRACT RUNNING EXEE 00

45、:00:00 00:00:10 此處不要關(guān)閉EXEE進程！ 四、 遷移數(shù)據(jù)4.1數(shù)據(jù)泵方式4.1.1源庫創(chuàng)建數(shù)據(jù)泵目錄OGGSRC:/home/oracle$export ORACLE_SID=oggsrcOGGSRC:/home/oracle$sqlplus / as sysdbaSQL> create or replace directory expdp as '/u01/expdp'Directory created.SQL> grant read,write on directory expdp to public;Grant succeeded.SQL&g

46、t; col owner for a15SQL> col directory_name for a15SQL> col directory_path for a25SQL> select * from dba_directories where directory_name='EXPDP'OWNER DIRECTORY_NAME DIRECTORY_PATH- - -SYS EXPDP /u01/expdp4.1.2源庫獲取數(shù)據(jù)庫當前SCNSQL> select dbms_flashback.get_system_change_number from d

47、ual;GET_SYSTEM_CHANGE_NUMBER- 3867034.1.3源庫基于SCN號導出數(shù)據(jù)nohup expdp MESPRD/MESPRD directory=expdir schemas=AHBIUSR,MESERP,MESBIGDATA,MESDMZTMP,MONITORUSER,MESPTS,MESWMS,MESIF dumpfile=mes%U.dmp job_name=expdpmes parallel=4 filesize=7G flashback_scn=386703 COMPRESSION=all logfile=expdpmes.log &4.1.4

48、目標庫創(chuàng)建數(shù)據(jù)泵目錄oggtgt:/home/oracle$sqlplus / as sysdbaSQL> create or replace directory impdp as '/u01/impdir'Directory created.SQL> grant read,write on directory impdp to public;Grant succeeded.SQL> select * from dba_directories where directory_name='IMPDP'SQL> col owner for a15SQL> col directory_name for a15SQL> col directory_path for a25SQL> select * from dba_directories where directory_name='EXPDP'OWNER DIRECTORY_NAME DIRECTORY_PAT