Oracle_Goldengate_11g_Install_and_Setup_Guide.doc

Oracle Goldengate 10g安装、配置指南Table of Contents1Introduction42Download Goldengate 11g43Prepare 10gR2 to 11gR2 test environment44Install Goldengate 11g on Linux54.1Create install directory and unzip downloaded file on both system54.2Create sub working directories for Goldengate54.3Introduction to the Command Interface75Configure Oracle Database for Goldengate75.1Create Goldengate user75.2Enable supplemental log mode in source system95.3Enable archive log mode in source system95.4Enable force logging mode in source system96Configure Goldengate Manager Process106.1Configure MGR in source system106.2Configure MGR in target system117Load Initial Data by Direct Load method117.1Configure Extract process in source system117.2Configure replicat process in target system127.3Accomplish Initial Load137.4Verify Initial Load result and process status138Configure Extract Process in Source system158.1Edit extract process parameter158.2Define GoldenGate local trail168.3Start primary Extract process169Configure pump process in source system179.1Edit data pump process parameter179.2Add GoldenGate remote trail in Source system1710Configure replicat process in target system1810.1Create GLOBALS parameter in target system1810.2Edit Delivery process parameter1910.3Verify if DML can be duplicated correctly2010.3.1Insert operation2010.3.2Update operation2010.3.3Delete operation2111Limitation2111.1Column Width differs in different encoding2111.2Object name with Japanese characters221 IntroductionThis Document briefly describes how to replicate data change from local lower version (Oracle Database ) database to remote higher version database (Oracle Database ) through Goldengate 11g.2 Download Goldengate 11gDownload URL: select a Product Pack: Oracle Fusion MiddlewarePlatform: Linux x86Choose: Oracle GoldenGate on Oracle v.0 Media Pack for Linux x86Download:Oracle GoldenGate V.0 for Oracle 10g on Linux x86 (Part V22227-01)Oracle GoldenGate V.0 for Oracle 11g on Linux x86 (Part V22228-01)3 Prepare 10gR2 to 11gR2 test environmentItemSource SystemTarget SystemPlatformOEL5.4OEL5.4HostnameDbGridDatabaseOracle Oracle Character SetZhs16gbkZhs16gbkORACLE_SIDPRODCUUGListener Name/PortLISTENER/1521LISTENER/1521Goldengate Useroggogg4 Install Goldengate 11g on Linux4.1 Create install directory and unzip downloaded file on both systemFor source system(db):oracledb mkdir -p /u01/app/oggoracledb cp V22227-01.zip /u01/app/oggoracledb cd /u01/app/oggoracledb unzip V22227-01.ziporacledb tar xvf ggs_Linux_x86_ora10g_32bit_v11_1_1_0_0_078.tarFor target system(grid):oraclegrid mkdir -p /u01/app/oggoraclegrid cp V22228-01.zip /u01/app/oggoraclegrid cd /u01/app/oggoraclegrid unzip V22228-01.ziporaclegrid tar xvf ggs_Linux_x86_ora11g_32bit_v11_1_1_0_0_078.tar4.2 Create sub working directories for GoldengateFor both Source system and Target system:oracledb ./ggsciOracle GoldenGate Command Interpreter for OracleVersion .0 Build 078Linux, x86, 32bit (optimized), Oracle 10 on Jul 28 2010 13:24:18Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.GGSCI () 1 create subdirsCreating subdirectories under current directory /u01/app/oggParameter files /u01/app/ogg/dirprm: createdReport files /u01/app/ogg/dirrpt: createdCheckpoint files /u01/app/ogg/dirchk: createdProcess status files /u01/app/ogg/dirpcs: createdSQL script files /u01/app/ogg/dirsql: createdDatabase definitions files /u01/app/ogg/dirdef: createdExtract data files /u01/app/ogg/dirdat: createdTemporary files /u01/app/ogg/dirtmp: createdVeridata files /u01/app/ogg/dirver: createdVeridata Lock files /u01/app/ogg/dirver/lock: createdVeridata Out-Of-Sync files /u01/app/ogg/dirver/oos: createdVeridata Out-Of-Sync XML files /u01/app/ogg/dirver/oosxml: createdVeridata Parameter files /u01/app/ogg/dirver/params: createdVeridata Report files /u01/app/ogg/dirver/report: createdVeridata Status files /u01/app/ogg/dirver/status: createdVeridata Trace files /u01/app/ogg/dirver/trace: createdStdout files /u01/app/ogg/dirout: createdAbove directories are created for following purpose:NamePurposedirchkCheckpoint filesdirdatGoldenGate trailsdirdefData definition filesdirprmParameter filesdirpcsProcess status filesdirrptReport filesdirsqlSQL script filesdirtmpTemporary files4.3 Introduction to the Command Interface View HELP summary for all commandsGGSCI HELPGGSCI HELP ALL View HELP summary for a COMMAND/ENTITYGGSCI HELP ADD EXTRACTGGSCI HELP ADD EXTTRAIL View your command historyGGSCI HISTORY View a brief informational summary of all processesGGSCI INFO ALL5 Configure Oracle Database for Goldengate5.1 Create Goldengate userCreate user ogg for both source system and target system, and grant appropriate privileges.For Source System:oracledb ogg sqlplus / as sysdbacreate tablespace tbs_gguser datafile /u01/app/oracle/oradata/soraeuc/gguser.dbf size 50M autoextend on;create user ogg identified by Ogg default tablespace tbs_gguser temporary tablespace TEMP quota unlimited on tbs_gguser;grant CONNECT, RESOURCE to ogg;grant CREATE SESSION, ALTER SESSION to ogg;grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ogg;grant ALTER ANY TABLE to ogg;grant FLASHBACK ANY TABLE to ogg;grant EXECUTE on DBMS_FLASHBACK to ogg;Insert test table and data for Source System:SQL conn scott/tigerSQL select * from emp_ogg;For Target System:oraclegrid ogg sqlplus / as sysdbacreate tablespace tbs_gguser datafile /u01/app/oracle/oradata/torautf/gguser.dbf size 50M autoextend on;create user ogg identified by Ogg default tablespace tbs_gguser temporary tablespace TEMP quota unlimited on tbs_gguser;grant CONNECT, RESOURCE to ogg;grant CREATE SESSION, ALTER SESSION to ogg;grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ogg;grant CREATE TABLE to ogg;Create empty test table and grant DML privilege to Goldengate user:SQL conn scott/tigerSQL grant INSERT, UPDATE, DELETE on scott.emp_ogg to ogg;SQL grant INSERT, UPDATE, DELETE on scott.dept_ogg to ogg;5.2 Enable supplemental log mode in source systemCheck supplemental log mode is enabled or not by following query:SQL select SUPPLEMENTAL_LOG_DATA_MIN from v$database;SUPPLEMENTAL_LOG_DATA_MI-NOIf not, enable it:SQL alter database add supplemental log data;SQL alter system switch logfile;5.3 Enable archive log mode in source systemEnable archive log mode:oracledb ogg sqlplus / as sysdbaSQL alter system set log_archive_dest= /u01/app/oracle/oradata/soraeuc/arch; SQL shutdown immediateSQL startup mountSQL alter database archivelog;SQL alter database open;Check database archive log information:oracledb ogg sqlplus / as sysdbaSQL alter system archive log current;SQL archive log list;5.4 Enable force logging mode in source systemCheck force logging mode is enabled or not by following query:SQL SELECT force_logging FROM v$database;FORCE_LOG-NOEnable force logging mode:SQL alter database force logging;Enable transaction data change capture for these two tables in Source system:GGSCI () 3 DBLOGIN USERID ogg, PASSWORD OggSuccessfully logged into database.GGSCI () 5 ADD TRANDATA scott.EMP_OGGLogging of supplemental redo data enabled for table SCOTT.EMP_OGG.GGSCI () 6 ADD TRANDATA scott.DEPT_OGGLogging of supplemental redo data enabled for table SCOTT.DEPT_OGG.Verify that supplemental logging has been turned on for these tables.GGSCI () 7 INFO TRANDATA scott.emp*Logging of supplemental redo log data is enabled for table SCOTT.EMP_OGGLogging of supplemental redo log data is enabled for table SCOTT.DEPT_OGG6 Configure Goldengate Manager Process6.1 Configure MGR in source systemCreate the Manager parameter file.oracledb ogg ./ggsciGGSCI () 1 EDIT PARAMS MGRUse the editor to assign a port.PORT 7809PURGEOLDEXTRACTS /dirdat, USECHECKPOINTSStart the Manager.GGSCI () 2 START MGRVerify that the Manager has started.GGSCI () 3 INFO MGRManager is running (IP port .7809).6.2 Configure MGR in target systemCreate the Manager parameter file.oraclegrid ogg ./ggsciGGSCI () 1 EDIT PARAMS MGRUse the editor to assign a port.PORT 7809PURGEOLDEXTRACTS /u01/app/ogg/dirdat, USECHECKPOINTSStart the Manager.GGSCI () 2 START MGRVerify that the Manager has started.GGSCI () 3 INFO MGRManager is running (IP port .7809).7 Load Initial Data by Direct Load method7.1 Configure Extract process in source systemAdd an Extract process called EINI_1:GGSCI () 1 ADD EXTRACT EINI_1, SOURCEISTABLEEXTRACT added.Verify Extract process:GGSCI () 2 INFO EXTRACT *, TASKSEXTRACT EINI_1 Initialized 2010-09-06 16:56 Status STOPPEDCheckpoint Lag Not AvailableLog Read Checkpoint Not Available First Record Record 0Task SOURCEISTABLEEdit EINI_1:GGSCI () 3 EDIT PARAMS EINI_1Add:- GoldenGate Initial Data Capture- for EMP_OGG and DEPT_OGG-EXTRACT EINI_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)USERID ogg, PASSWORD OggRMTHOST grid, MGRPORT 7809RMTTASK REPLICAT, GROUP RINI_1TABLE scott.EMP_OGG;TABLE scott.DEPT_OGG;For chinese support, we should set environment variable “NLS_LANG” in Extract/Pump/Replicat process parameters.7.2 Configure replicat process in target systemAdd initial load delivery processGGSCI () 1 ADD REPLICAT RINI_1, SPECIALRUNREPLICAT added.Verify result:GGSCI () 2 INFO REPLICAT *, TASKSREPLICAT RINI_1 Initialized 2010-09-06 17:19 Status STOPPEDCheckpoint Lag 00:00:00 (updated 00:01:12 ago)Log Read Checkpoint Not AvailableTask SPECIALRUNEdit initial load delivery process RINI_1：.GGSCI () 3 EDIT PARAMS RINI_1Add:- GoldenGate Initial Load Delivery-REPLICAT RINI_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)ASSUMETARGETDEFSUSERID ogg, PASSWORD OggDISCARDFILE ./dirrpt/RINIaa.dsc, PURGEMAP scott.*, TARGET scott.*;Note: In the MAP statement, the first owner/schema is for the source and the second for the target.7.3 Accomplish Initial LoadStart Initial Load process EINI_1 in source system, then RINI_1 process in target system will be started automatically:GGSCI () 6 START EXTRACT EINI_1Sending START request to MANAGER .EXTRACT EINI_1 starting7.4 Verify Initial Load result and process statusGGSCI () 8 VIEW REPORT EINI_1 Processing table SCOTT.EMP_OGGProcessing table SCOTT.DEPT_OGG* * Run Time Statistics * *Report at 2010-08-09 23:18:34 (activity since 2010-08-09 23:18:28)Output to RINI_1:From Table SCOTT.EMP_OGG: # inserts: 2 # updates: 0 # deletes: 0 # discards: 0From Table SCOTT.DEPT_OGG: # inserts: 2 # updates: 0 # deletes: 0 # discards: 0You can also verify the results in target system:GGSCI () 6 VIEW REPORT RINI_1 # inserts: 2 # updates: 0 # deletes: 0 # discards: 0From Table SCOTT.DEPT_OGG to SCOTT.DEPT_OGG: # inserts: 2 # updates: 0 # deletes: 0 # discards: 0Check the initial data has been transferred to Target system:SQL select * from emp_ogg;CUST NAME CITY ST- - - -WILL BG SOFTWARE CO. SEATTLE WAJANE ROCKY FLYER INC. DENVER COSQL select * from dept_ogg;CUST ORDER_DAT PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID- - - - - - -WILL 30-SEP-94 CAR 144 17520 3 100JANE 11-NOV-95 PLANE 256 133300 1 100After initial load, extract process EINI_1 and replicat process RINI_1 stop automatically.Source system:GGSCI () 10 INFO EXTRACT EINI_1EXTRACT EINI_1 Last Started 2010-09-06 17:26 Status STOPPEDCheckpoint Lag Not AvailableLog Read Checkpoint Table SCOTT.DEPT_OGG 2010-09-06 17:26:29 Record 2Task SOURCEISTABLETarget System:GGSCI () 9 INFO REPLICAT RINI_1REPLICAT RINI_1 Initialized 2010-09-06 17:19 Status STOPPEDCheckpoint Lag 00:00:00 (updated 20:43:50 ago)Log Read Checkpoint Not AvailableTask SPECIALRUN8 Configure Extract Process in Source system8.1 Edit extract process parameterGGSCI () 1 EDIT PARAMS EORA_1Add:- Change Capture parameter file to capture- EMP_OGG and DEPT_OGG changesEXTRACT EORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)USERID ogg, PASSWORD OggEXTTRAIL ./dirdat/aaTABLE scott.EMP_OGG;TABLE scott.DEPT_OGG;Please note that “aa” is prefix for local trail file. Execute the following commands in source system to add Primary Extract group.GGSCI () 2 ADD EXTRACT EORA_1, TRANLOG, BEGIN NOWEXTRACT added.8.2 Define GoldenGate local trailGGSCI () 4 ADD EXTTRAIL ./dirdat/aa, EXTRACT EORA_1, MEGABYTES 5EXTTRAIL added.8.3 Start primary Extract processGGSCI () 10 START EXTRACT EORA_1Sending START request to MANAGER .EXTRACT EORA_1 startingVerify extract process is running or not:GGSCI () 3 INFO EXTRACT EORA_1EXTRACT EORA_1 Last Started 2010-09-07 16:24 Status RUNNINGCheckpoint Lag 00:07:53 (updated 00:00:01 ago)Log Read Checkpoint Oracle Redo Logs 2010-09-07 16:16:12 Seqno 8, RBA 45242384Now Goldengate will generate local tail file “aa000000” under dirdat in Source system:oracledb ll / ogg/dirdat/合計 4-rw-rw-rw- 1 oracle oinstall 944 9月 7 16:24 aa0000009 Configure pump process in source system9.1 Edit data pump process parameterGGSCI () 6 EDIT PARAMS PORA_1Add:- Data Pump parameter file to read the local- trail of EMP_OGG and DEPT_OGG changes-EXTRACT PORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)PASSTHRURMTHOST grid, MGRPORT 7809RMTTRAIL ./dirdat/paTABLE scott.EMP_OGG;TABLE scott.DEPT_OGG;Add data pump Extract groupGGSCI () 7 ADD EXTRACT PORA_1, EXTTRAILSOURCE ./dirdat/aaEXTRACT added.Verify results:GGSCI () 8 INFO EXTRACT PORA_1EXTRACT PORA_1 Initialized 2010-09-07 17:10 Status STOPPEDCheckpoint Lag 00:00:00 (updated 00:00:08 ago)Log Read Checkpoint File ./dirdat/aa000000 First Record RBA 09.2 Add GoldenGate remote trail in Source systemGGSCI () 9 ADD RMTTRAIL ./dirdat/pa, EXTRACT PORA_1, MEGABYTES 5RMTTRAIL added.Start data pump process:GGSCI () 12 START EXTRACT PORA_1Sending START request to MANAGER .EXTRACT PORA_1 startingVerify the results:GGSCI () 13 INFO EXTRACT PORA_1EXTRACT PORA_1 Last Started 2010-09-07 17:11 Status RUNNINGCheckpoint Lag 00:00:00 (updated 00:00:05 ago)Log Read Checkpoint File ./dirdat/aa000000 First Record RBA 0Now Goldengate will generate remote tail file “pa000000” under dirdat in Target system:oraclegrid ogg ll dirdat/合計 4-rw-rw-rw- 1 oracle oinstall 0 9月 7 17:11 pa00000010 Configure replicat process in target system10.1 Create GLOBALS parameter in target systemEdit GLOBALS(upper case) parameter file to indicate checkpoint tableGGSCI () 12 EDIT PARAMS ./GLOBALSAdd:CHECKPOINTTABLE ogg.ggschkptVerify:oraclegrid ogg ll GLOBALS -rw-rw-rw- 1 oracle oinstall 29 9月 8 10:26 GLOBALSFor GLOBALS configuration take effect, we must exit GGSCI session:GGSCI () 13 exitAdd replicat checkpoint table in target system:GGSCI () 1 DBLOGIN USERID ogg, PASSWORD OggSuccessfully logged into database.GGSCI () 2 ADD CHECKPOINTTABLENo checkpoint table specified, using GLOBALS specification (ogg.ggschkpt).Successfully created checkpoint table OGG.GGSCHKPT.10.2 Edit Delivery process parameterAdd Replicat group:GGSCI (gri