1 / 69

juliandyke.com

Understanding GoldenGate. Julian Dyke Independent Consultant. Web Version. juliandyke.com . Agenda. Introduction Configuration Redo versus Trail. Introduction. GoldenGate Introduction. Oracle GoldenGate is a heterogeneous replication solution

azra
Télécharger la présentation

juliandyke.com

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. UnderstandingGoldenGate Julian Dyke Independent Consultant Web Version juliandyke.com

  2. Agenda Introduction Configuration Redo versus Trail

  3. Introduction

  4. GoldenGateIntroduction • Oracle GoldenGate is a heterogeneous replication solution • GoldenGate (the company and the product) was acquired by Oracle Corporation in 2009. • GoldenGate supports: • Zero Downtime Upgrade and Migration • System Integration / Data Synchronization • Query and Report offloading • Real-time Data distribution • Real-time Data Warehousing • Live standby database • Active-active high availability • Controversial replacement for Oracle Streams

  5. GoldenGateSupported Topologies Bi-directional Peer-to-Peer Unidirectional Reporting Instance Instant Failover, Active-Active Load Balancing, High Availability Broadcast Consolidation Cascading Data Warehouse Scalability, Database Tiering Data Distribution

  6. GoldenGate Supported Databases • Oracle GoldenGate for Non Oracle Databases • Supported non-oracle databases include: • IBM DB2 on Windows, UNIX and Linux • Microsoft SQL Server 2000, 2005, 2008 • Sybase on Windows, UNIX and Linux • Teradata on Windows, UNIX and Linux • MySQL on Windows, UNIX and Linux • TimesTen on Windows and Linux (delivery only)

  7. GoldenGate Licensing • Source – Oracle Technology Global Price List – 15 March 2013 • GoldenGate licenses (Oracle to Oracle) include: • XStream • Active Data Guard • Source – Oracle Fusion Middleware Licensing Information 11gR1 Both capture and apply databases must be fully licensed

  8. GoldenGate Conventions • Environment: • Databases: • This presentation was developed in Oracle GoldenGate 11.2.1.0.1 • Virtual Box 4.2.0 • Oracle Enterprise Linux 5 Update 6 • Oracle Database 11.2.0.3.0

  9. GoldenGate Streams versus GoldenGate Oracle Streams Propagate Capture Apply Data Pump Extract Replicat Target Server Source Server Oracle GoldenGate Target Server Source Server

  10. GoldenGateBasic Architecture Manager Replicat Data Pump Extract Manager SourceDatabase LocalTrail RemoteTrail TargetDatabase Target Server Source Server

  11. GoldenGateConfiguration Options ClassicCapture IntegratedCapture Redo Logs +Archive Logs ArchiveLogsOnly UpstreamCapture DownstreamCapture

  12. GoldenGateClassic Capture Extract Data Pump OnlineRedo/Archive Logs LocalTrail SourceDatabase

  13. GoldenGate Integrated Capture Extract Data Pump Log Miner LCR LCR LCR OnlineRedo/Archive Logs LocalTrail SourceDatabase

  14. GoldenGateDownstream Capture Capture LCR LCR LogicalChange Records IntegratedExtract TrailFile OnlineRedo Logs Standby Redo Logs Primary Database Standby Database Downstream Server Source Server Real Time Downstream Mode

  15. GoldenGateDownstream Capture Capture LCR LCR LogicalChange Records IntegratedExtract TrailFile OnlineRedo Logs Archive Redo Logs Primary Database Standby Database Downstream Server Source Server Downstream Archive Log Mode

  16. GoldenGate Bi-Directional Replication OnlineRedo Logs LocalTrail RemoteTrail DataPump DataPump Capture Replicat Replicat Capture RemoteTrail LocalTrail OnlineRedo Logs Server B Server A Also known as Active-Active Replication

  17. GoldenGateSupported Data Types • The following data types are supported for both classic and integrated capture • NUMBER • BINARY FLOAT • BINARY DOUBLE • CHAR • VARCHAR2 • LONG • NCHAR • NVARCHAR2 • RAW • LONG RAW • DATE • TIMESTAMP

  18. GoldenGateSupported Data Types • There is limited support in classic capture for the following data types: • INTERVAL DAY • INTERVAL YEAR • TIMESTAMP WITH TIME ZONE • TIMESTAMP WITH LOCAL TIME ZONE • The following data types are not supported • Abstract data types with scalar, LOBs, VARRAYs, nested tables , REFS • ANYDATA • ANYDATASET • ANYTYPE • BFILE • MLSLABEL • ORDDICOM • TIMEZONE_ABBR • URITYPE • UROWID

  19. GoldenGateSupported Data Types • The following data types are supported for both classic and integrated capture • NUMBER • BINARY FLOAT • BINARY DOUBLE • CHAR • VARCHAR2 • LONG • NCHAR • NVARCHAR2 • RAW • LONG RAW • DATE • TIMESTAMP

  20. GoldenGateRestrictions • Neither capture method supports • Database replay • EXTERNAL tables • Materialized views with ROWID • Classic capture does not support • IOT mapping tables • Key compressed IOTs • XMLType tables stored as XML Object Relational • Distributed Transactions • XA and PDML distributed transactions • Capture from OLTP table compressed tables • Capture from compressed tablespaces • Exadata Hybrid Columnar Compression (EHCC)

  21. GoldenGateOracle-Reserved Schemas The following schema names are reserved by Oracle and should not be configured for GoldenGate replication:

  22. GoldenGateRAC Support • RAC support has some limitations in classic capture mode • Extract can only run against one instance • If instance fails, • Manager must be stopped on failed node: • Manager and extract must be started on a surviving node • Failover can be configured in Oracle Grid Infrastructure • Additional archive log switching may be required in archive log mode • Before shutting down extract process • Insert dummy record into a source table • Switch log files on all nodes • Additional configuration required to access ASM instance • Shared storage for trails can be: • OCFS • ACFS • DBFS • No mention of NFS in the documentation

  23. Configuration

  24. GoldenGateInstallation ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip • Zip file is approximately 90MB • On each server create GG_HOME mkdir /home/oracle/goldengate • Copy zip file to GG_HOME $ cd /home/oracle/goldengate $ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip • Add GG_HOME to profile export PATH=/home/oracle/goldengate:$PATH export LD_LIBRARY_PATH=/home/oracle/goldengate:$LD_LIBRARY_PATH • Install bundle patch in Oracle 11.2.0.3 home for integrated capture Download the following from www.oracle.com

  25. GoldenGate GGSCI $ ggsci GGSCI 1> HELP • For help on with an individual command: GGSCI 2> HELP <command> <object> • For example: GGSCI 1> INFO ALL • The best source of information is: • Oracle GoldenGate Windows and UNIX Reference Guide • Most GoldenGate configuration can be performed within GGSCI • Command-line utility • For a list of available commands:

  26. GoldenGate Parameter Files [oracle@vm5]$ ggsci GGSCI (vm5) 1> EDIT PARAMS rep1 • Stored in $GG_HOME/dirprm directory e.g. $GG_HOME/dirprm/rep1.prm • Comments are prefixed by -- and terminated by end of line -- This is a comment • Continuation character is ampersand MAP US03.T54 TARGET US01.T55, &COLMAP (col1=c1, col3=c2, col4=c3, col2=c4); Each process has a text-based parameter file Parameter files can be edited using GGSCI or with a text editor

  27. GoldenGate Subdirectories [oracle@vm4]$ ggsci GGSCI (vm4) 1> CREATE SUBDIRS Creating subdirectories under current directory /home/oracle/goldengate Parameter files /home/oracle/goldengate/dirprm: createdReport files /home/oracle/goldengate/dirrpt: createdCheckpoint files /home/oracle/goldengate/dirchk: createdProcess status files /home/oracle/goldengate/dirpcs: createdSQL script files /home/oracle/goldengate/dirsql: createdDatabase definitions files /home/oracle/goldengate/dirdef: createdExtract data files /home/oracle/goldengate/dirdat: createdTemporary files /home/oracle/goldengate/dirtmp: createdStdout files /home/oracle/goldengate/dirout: created By convention, configuration and trail files are stored in subdirectories within $GG_HOME Subdirectories are created within GGSCI:

  28. GoldenGate Schema Owner [oracle@vm4]$ sqlplus / as sysdba SQL> CREATE USER gg01 IDENTIFIED BY gg01; SQL> GRANT CONNECT, RESOURCE, DBA TO gg01; • Schema owner must be specified in GoldenGate parameters file: [oracle@vm4]$ ggsciGGSCI 1> EDIT PARAMS ./GLOBALS GGSCHEMA gg01 • In this example parameter file is /home/oracle/goldengate/GLOBALS • A schema owner is required to own GoldenGate objects • Can grant specific privileges to owner or just use DBA • For example GG01

  29. GoldenGate Tablespace [oracle@vm4]$ sqlplus / as sysdba SQL> CREATE TABLESPACE goldengateDATAFILE '/u01/app/oradata/NORTH/goldengate01.dbf‘SIZE 100MAUTOEXTEND ON; Tablespace created. SQL> ALTER USER gg01 DEFAULT TABLESPACE goldengate; User altered. • Ensure AUTOEXTEND is enabled Recommended for both source and target servers

  30. GoldenGate Role [oracle@vm4]$ cd /home/oracle/goldengate [oracle@vm4]$ sqlplus / as sysdba SQL> @role_setup GGS Role setup script This script will drop and recreate the role GGS_GGSUSER_ROLE Enter GoldenGate schema name:GG01 • Grant role to GGSCHEMA user: [oracle@vm4]$ cd /home/oracle/goldengate [oracle@vm4]$ sqlplus / as sysdba SQL> GRANT GGS_GGSUSER_ROLE TO gg01; On source server create GGS_GGSUSER_ROLE

  31. GoldenGate Manager Process [oracle@vm4]$ ggsciGGSCI 1> EDIT PARAMS MGR PORT 7809DYNAMICPORTLIST 7810-7820 • Manager process is started from GGSCI oracle@vm4]$ ggsciGGSCI 2> START MANAGER • Executable is $GG_HOME/mgr • Manager process controls all GoldenGate processes on a server • Including extract and replicat processes • Configured in parameter file

  32. GoldenGate Extract Process EXTRACT ex1USERID gg01, PASSWORD gg01EXTTRAIL /home/oracle/goldengate/dirdat/exTABLE US03.*; • Captures and stores changes in local trail file • Classic capture • Captures changes from online redo log or archived redo log • Fetches additional data from database • Integrated capture • Captures changes from log miner • Uses extract executable • Sample parameter file:

  33. GoldenGate Data Pump Process EXTRACT dp1USERID gg01, PASSWORD gg01RMTHOST vm5, MGRPORT 7809RMTTRAIL /home/oracle/goldengate/dirdat/rtTABLE US03.*; • Propagates contents of local trail file to remote trail file • Uses extract executable • In basic configurations same functionality can be achieved using extract process • Sample parameter file:

  34. GoldenGate Replicat Process REPLICAT rep1USERID gg01, PASSWORD gg01ASSUMETARGETDEFSDISCARDFILE /home/oracle/goldengate/discards, PURGEMAP US03.* TARGET US03.*; Reads changes from remote trail and applies them to database Uses replicat executable Sample parameter file:

  35. GoldenGate Processes and Trails [oracle@vm4]$ ggsci GGSCI> ADD EXTRACT ex1, TRANLOG, BEGIN NOW GGSCI> ADD EXTTRAIL /home/oracle/goldengate/dirdat/ex, EXTRACT ex1 GGSCI> ADD EXTRACT dp1 EXTTRAILSOURCE /home/oracle/goldengate/dirdat/ex GGSCI> ADD RMTTRAIL /home/oracle/goldengate/dirdat/rt, EXTRACT dp1 • On the target server: [oracle@vm5]$ ggsci GGSCI> ADD REPLICAT rep1, EXTTRAIL /home/oracle/goldengate/dirdat/rt New processes and trails must be added using GGSCI. For example on the source server:

  36. GoldenGate Checkpoint Table [oracle@vm4]$ ggsci GGSCI> DBLOGIN USERID gg01, PASSWORD gg01 GGSCI> ADD CHECKPOINTTABLE gg01.checkpointtable • Location must be added to GLOBALS parameters on target server [oracle@vm4]$ ggsci GGSCI> EDIT PARAMS ./GLOBALS GGSCHEMA gg01CHECKPOINTTABLE gg01.checkpointtable • Must exist in target database • Records location in trail of last change applied to database • Added using GGSCI

  37. GoldenGate Starting Processes [oracle@vm4]$ ggsci GGSCI> START EXTRACT ex1 GGSCI> START EXTRACT dp1 • On the target server [oracle@vm4]$ ggsci GGSCI> START REPLICAT rep1 • The EXTRACT and REPLICAT keywords can be omitted • Use equivalent STOP command to stop processes Processes are started from GGSCI On the source server

  38. GoldenGate Checking Process Status [oracle@vm4]$ ggsci GGSCI (vm4.juliandyke.com) 1> INFO ALL Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNINGEXTRACT RUNNING DP1 00:00:00 00:00:04EXTRACT ABENDED DP2 00:00:00 24:23:14EXTRACT RUNNING EX1 00:00:00 00:00:05EXTRACT STOPPED EX2 00:00:00 02:34:41 [oracle@vm5]$ ggsci GGSCI (vm5.juliandyke.com) 1> INFO ALL Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNINGREPLICAT RUNNING REP1 00:00:00 00:00:05REPLICAT RUNNING REP2 00:00:00 00:00:04 Check process status using GGSCI INFO ALL:

  39. GoldenGate Viewing Log Files [oracle@vm4]$ ggsci GGSCI> VIEW REPORT ex1 • Output is filtered through more utility • Processes are logged in report files stored in $GG_HOME/dirrpt • e.g. ex1 process will be logged in $GG_HOME/dirrpt/EX1.rpt • Up to 10 copies are retained • EX1.rpt (latest), EX11.rpt, EX12.rpt, … EX19.rpt (oldest) • If a process abends then check the report file for details • Report files can also be viewed using GGSCI

  40. GoldenGate Trail Files • Stored in $GG_HOME/dirdat directory by convention • User must specify a two-character prefix e.g. ex • File names are generated automatically by extract process e.g. • ex000000 • ex000001 • ex000002 • etc • Naming conventions require some thought

  41. GoldenGate Trail Files • Changes are only stored in trail file when a transaction commits • If a transaction rolls back then no changes are stored • Archive logs must be available for long running transactions • Index changes are generally not stored • IOT changes are stored • Undo changes are not stored • Only redo • Changes to data dictionary are not stored including • Objects, tables, columns, statistics • Segments / extents

  42. GoldenGate Trail Files – Data Representation 00 00 00 04 32 30 31 33 • NULL values are stored as FF FF 00 00 • DATE • 2-bytes followed by 19 ASCII characters in the format: YYYY-MM-DD:HH24:MI:SS • For example 2013-03-14:22:39:56 is 00 00 32 30 31 33 2D 30 33 2D 31 34 3A 32 32 3A 33 39 3A 35 36 • NULL values are stored as FF FF 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 • NUMBER • 4-byte length followed by ASCII characters e.g. 2013

  43. GoldenGate Trail Files – Data Representation 00 00 00 0A 47 6F 6C 64 65 6E 47 61 74 65 • NULL values stored as: FF FF 00 00 • CHAR • 2-byte length followed by ASCII characters space-padded • e.g. “GoldenGate” in CHAR(20) 00 00 47 6F 6C 64 65 6E 47 61 74 65 20 20 20 20 20 20 20 20 20 20 • NULL values stored as: FF FF 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 • VARCHAR2 • 4-byte length followed by ASCII characters • e.g. “GoldenGate” in VARCHAR2(20)

  44. GoldenGate Logdump Utility • To view data set the following parameters: [oracle@vm4]$ logdump Logdump> GHDR ONLogdump> FILEHEADER DETAILLogdump> DETAIL DATALogdump> USERTOKEN DETAILLogdump> RECLEN 128 Logdump> OPEN <trailFileName> • To show next recorduse NEXT or N Logdump> NEXT # or N • To return to start of file Logdump> POS 0 • Dumps contents of GoldenGate trails from • Local trail • Remote trail

  45. GoldenGate Logdump Hdr-Ind : E (x45) Partition : . (x04)UndoFlag: . (x00) BeforeAfter: A (x41)RecLength: 51 (x0033) IO Time : 2013/04/05 06:24:00.000.000IOType: 5 (x05) OrigNode : 255 (xff)TransInd: . (x01) FormatType : R (x52)SyskeyLen: 0 (x00) Incomplete : . (x00)AuditRBA: 80 AuditPos : 32769040Continued : N (x00) RecCount : 1 (x01) 2013/04/05 06:24:00.000.000 Insert Len 51 RBA 9060Name: US03.T1After Image: Partition 4 G m0000 0006 0000 0002 3630 0001 0007 0000 0003 5359 | ........60........SY5300 0200 0d00 0000 094f 424a 4552 524f 5224 0003 | S........OBJERROR$..0009 0000 0005 5441 424c 45 | ......TABLEColumn 0 (x0000), Len 6 (x0006)0000 0002 3630 | ....60Column 1 (x0001), Len 7 (x0007)0000 0003 5359 53 | ....SYSColumn 2 (x0002), Len 13 (x000d) 0000 0009 4f42 4a45 5252 4f52 24 | ....OBJERROR$Column 3 (x0003), Len 9 (x0009)0000 0005 5441 424c 45 | ....TABLE Sample output

  46. GoldenGate Minimal Supplemental Logging SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; • Extract processes will not start unless minimal supplemental logging has been configured in database • Minimal supplemental logging is used for: • chained blocks • blocks in index clusters • Minimal supplemental logging is enabled using:

  47. GoldenGateSupplemental Logging [oracle@vm4]$ ggsci GGSCI 1> DBLOGIN USERID us01 PASSWORD us01 GGSCI 2> ADD TRANDATA t1 Logging of supplemental redo data enabled for table US01.T1. • Internally this executes the following DDL: ALTER TABLE "US01"."T1" ADD SUPPLEMENTAL LOG GROUP "GGS_76111" ("C1") ALWAYS /* GOLDENGATE_DDL_REPLICATION */ • where 76111 is the OBJ# of the table Supplemental logging should be configured for all tables being replicated Enabled using GGSCI. For example:

  48. GoldenGateSequences • To install sequence support on each server run: [oracle@vm4]$ cd /home/oracle/goldengate[oracle@vm4]$ sqlplus / as sysdbaSQL> @sequence.sql • The sequence.sqlscript creates the following procedures in the GGSCHEMA : • SEQTRACE • GETSEQFLUSH • REPLICATESEQUENCE • UPDATESEQUENCE • Sequences are not supported by default. Additional configuration is required: • Sequences will not be identical across the databases • Sequence in target database will never be lower than the same sequence on the source database

  49. GoldenGateSequences EXTRACT ex1USERID gg01, PASSWORD gg01EXTTRAIL /home/oracle/goldengate/dirdat/exSEQUENCE us03.*;TABLE us03.*; • Data Pump process: EXTRACT dp1USERID gg01, PASSWORD gg01RMTHOST vm5, MGRPORT 7809RMTTRAIL /home/oracle/goldengate/dirdat/rtSEQUENCE us03.*;TABLE us03.*; • Replicat process: REPLICAT rep1USERID gg01, PASSWORD gg01ASSUMETARGETDEFSDISCARDFILE /home/oracle/goldengate/discards, PURGEMAP US03.SEQ1, TARGET US03.SEQ1;MAP US03.T*, TARGET US03.*; Extract Process

  50. GoldenGateTRUNCATE Statements • TRUNCATE statements are not supported by default • TRUNCATE support can be configured • Standalone • As part of full DDL support • These options are mutually exclusive • Standalone TRUNCATE support • Does not support partitioned tables • Configure full DDL support for partitions/subpartitions • Enabled using GETTRUNCATES parameter • Must be specified BEFORE tables/mappings in parameter file

More Related