Oracle Clustered File System (OCFS)
Download
Report
Transcript Oracle Clustered File System (OCFS)
Understanding
GoldenGate
Julian Dyke
Independent Consultant
Web Version
1
© 2013 Julian Dyke
juliandyke.com
Agenda
2
© 2013 Julian Dyke
Introduction
Configuration
Redo versus Trail
juliandyke.com
Introduction
3
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Introduction
Oracle GoldenGate is a heterogeneous replication solution
GoldenGate (the company and the product) was acquired by Oracle
Corporation in 2009.
GoldenGate supports:
4
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
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Supported Topologies
Unidirectional
Reporting Instance
5
Bi-directional
Instant Failover, Active-Active
Peer-to-Peer
Load Balancing, High Availability
Broadcast
Consolidation
Cascading
Data Distribution
Data Warehouse
Scalability, Database Tiering
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Supported Databases
Oracle GoldenGate for Non Oracle Databases
Supported non-oracle databases include:
6
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)
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Licensing
Both capture and apply databases must be fully licensed
Product
Processor License
Enterprise Edition
47,500
Real Application Clusters
23,000
Active Data Guard
10,000
GoldenGate
17,500
Management Pack for GoldenGate
7
3,500
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
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Conventions
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
Environment:
8
Name
Abbreviation
Value
Home Directory
$GG_HOME
/home/oracle/goldengate
GoldenGate Schema
GGSCHEMA
GG01
Source
Target
Hostname
vm4
vm5
Database Name
NORTH
SOUTH
Databases:
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Streams versus GoldenGate
Oracle Streams
Capture
Propagate
Source Server
Apply
Target Server
Oracle GoldenGate
Extract
Data Pump
Source Server
9
© 2013 Julian Dyke
Replicat
Target Server
juliandyke.com
GoldenGate
Basic Architecture
Manager
Extract
Source
Database
Data Pump
Local
Trail
Source Server
10
© 2013 Julian Dyke
Manager
Replicat
Remote
Trail
Target
Database
Target Server
juliandyke.com
GoldenGate
Configuration Options
Classic
Capture
Redo Logs +
Archive
Logs
11
© 2013 Julian Dyke
Archive
Logs
Only
Integrated
Capture
Upstream
Capture
Downstream
Capture
juliandyke.com
GoldenGate
Classic Capture
Extract
Source
Database
12
© 2013 Julian Dyke
Online
Redo/Archive
Logs
Data Pump
Local
Trail
juliandyke.com
GoldenGate
Integrated Capture
LCR LCR LCR
Log Miner
Source
Database
13
Online
Redo/Archive
Logs
© 2013 Julian Dyke
Extract
Data Pump
Local
Trail
juliandyke.com
GoldenGate
Downstream Capture
Real Time Downstream Mode
LCR LCR
Online
Redo Logs
Primary Database
Source Server
14
© 2013 Julian Dyke
Standby
Redo Logs
Logical
Change
Records
Capture
Integrated
Extract
Trail
File
Standby Database
Downstream Server
juliandyke.com
GoldenGate
Downstream Capture
Downstream Archive Log Mode
LCR LCR
Online
Redo Logs
Primary Database
Source Server
15
© 2013 Julian Dyke
Archive
Redo Logs
Logical
Change
Records
Capture
Integrated
Extract
Trail
File
Standby Database
Downstream Server
juliandyke.com
GoldenGate
Bi-Directional Replication
Also known as Active-Active Replication
Online
Redo Logs
Local
Trail
Data
Pump
Capture
Remote
Trail
Server A
© 2013 Julian Dyke
Replicat
Data
Pump
Replicat
16
Remote
Trail
Capture
Local
Trail
Online
Redo Logs
Server B
juliandyke.com
GoldenGate
Supported Data Types
17
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
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Supported Data Types
18
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
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Supported Data Types
19
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
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Restrictions
20
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)
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Oracle-Reserved Schemas
21
The following schema names are reserved by Oracle and should not be
configured for GoldenGate replication:
$AURORA
EXFSYS
REPADMIN
$JIS
MDSYS
SYS
$ORB
ODM
SYSMAN
$UNAUTHENTICATED ODM_MTR
SYSTEM
$UTILITY
OLAPSYS
TRACESVR
ANONYMOUS
ORDPLUGINS
WKPROXY
AURORA
ORDSYS
WKSYS
CTXSYS
OSE$HTTP$ADMIN WMSYS
DBSNMP
OUTLN
DMSYS
PERFSTAT
DSSYS
PUBLIC
© 2013 Julian Dyke
XDB
juliandyke.com
GoldenGate
RAC Support
22
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
© 2013 Julian Dyke
juliandyke.com
Configuration
23
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Installation
Download the following from www.oracle.com
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
24
Install bundle patch in Oracle 11.2.0.3 home for integrated capture
© 2013 Julian Dyke
juliandyke.com
GoldenGate
GGSCI
Most GoldenGate configuration can be performed within GGSCI
Command-line utility
For a list of available commands:
$ ggsci
GGSCI 1> HELP
For help on with an individual command:
GGSCI 2> HELP <command> <object>
For example:
GGSCI 1> INFO ALL
25
The best source of information is:
Oracle GoldenGate Windows and UNIX Reference Guide
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Parameter Files
Each process has a text-based parameter file
Parameter files can be edited using GGSCI or with a text editor
[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);
26
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Subdirectories
By convention, configuration and trail files are stored in subdirectories within
$GG_HOME
Subdirectories are created within GGSCI:
[oracle@vm4]$ ggsci
GGSCI (vm4) 1> CREATE SUBDIRS
Creating subdirectories under current directory /home/oracle/goldengate
Parameter files
Report files
Checkpoint files
Process status files
SQL script files
Database definitions files
Extract data files
Temporary files
Stdout files
27
© 2013 Julian Dyke
/home/oracle/goldengate/dirprm: created
/home/oracle/goldengate/dirrpt: created
/home/oracle/goldengate/dirchk: created
/home/oracle/goldengate/dirpcs: created
/home/oracle/goldengate/dirsql: created
/home/oracle/goldengate/dirdef: created
/home/oracle/goldengate/dirdat: created
/home/oracle/goldengate/dirtmp: created
/home/oracle/goldengate/dirout: created
juliandyke.com
GoldenGate
Schema Owner
A schema owner is required to own GoldenGate objects
Can grant specific privileges to owner or just use DBA
For example GG01
[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]$ ggsci
GGSCI 1> EDIT PARAMS ./GLOBALS
GGSCHEMA gg01
28
In this example parameter file is /home/oracle/goldengate/GLOBALS
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Tablespace
Recommended for both source and target servers
[oracle@vm4]$ sqlplus / as sysdba
SQL> CREATE TABLESPACE goldengate
DATAFILE '/u01/app/oradata/NORTH/goldengate01.dbf‘
SIZE 100M
AUTOEXTEND ON;
Tablespace created.
SQL> ALTER USER gg01 DEFAULT TABLESPACE goldengate;
User altered.
29
Ensure AUTOEXTEND is enabled
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Role
On source server create GGS_GGSUSER_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;
30
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Manager Process
Manager process controls all GoldenGate processes on a server
Including extract and replicat processes
Configured in parameter file
[oracle@vm4]$ ggsci
GGSCI 1> EDIT PARAMS MGR
PORT 7809
DYNAMICPORTLIST 7810-7820
Manager process is started from GGSCI
oracle@vm4]$ ggsci
GGSCI 2> START MANAGER
31
Executable is $GG_HOME/mgr
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Extract Process
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:
EXTRACT ex1
USERID gg01, PASSWORD gg01
EXTTRAIL /home/oracle/goldengate/dirdat/ex
TABLE US03.*;
32
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Data Pump Process
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:
EXTRACT dp1
USERID gg01, PASSWORD gg01
RMTHOST vm5, MGRPORT 7809
RMTTRAIL /home/oracle/goldengate/dirdat/rt
TABLE US03.*;
33
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Replicat Process
Reads changes from remote trail and applies them to database
Uses replicat executable
Sample parameter file:
REPLICAT rep1
USERID gg01, PASSWORD gg01
ASSUMETARGETDEFS
DISCARDFILE /home/oracle/goldengate/discards, PURGE
MAP US03.* TARGET US03.*;
34
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Processes and Trails
New processes and trails must be added using GGSCI.
For example on the source server:
[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
35
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Checkpoint Table
Must exist in target database
Records location in trail of last change applied to database
Added using GGSCI
[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 gg01
CHECKPOINTTABLE gg01.checkpointtable
36
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Starting Processes
Processes are started from GGSCI
On the source server
[oracle@vm4]$ ggsci
GGSCI> START EXTRACT ex1
GGSCI> START EXTRACT dp1
On the target server
[oracle@vm4]$ ggsci
GGSCI> START REPLICAT rep1
37
The EXTRACT and REPLICAT keywords can be omitted
Use equivalent STOP command to stop processes
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Checking Process Status
Check process status using GGSCI INFO ALL:
[oracle@vm4]$ ggsci
GGSCI (vm4.juliandyke.com) 1> INFO ALL
Program
Status
Group
Lag at Chkpt
Time Since Chkpt
MANAGER
EXTRACT
EXTRACT
EXTRACT
EXTRACT
RUNNING
RUNNING
ABENDED
RUNNING
STOPPED
DP1
DP2
EX1
EX2
00:00:00
00:00:00
00:00:00
00:00:00
00:00:04
24:23:14
00:00:05
02:34:41
[oracle@vm5]$ ggsci
GGSCI (vm5.juliandyke.com) 1> INFO ALL
38
Program
Status
Group
Lag at Chkpt
Time Since Chkpt
MANAGER
REPLICAT
REPLICAT
RUNNING
RUNNING
RUNNING
REP1
REP2
00:00:00
00:00:00
00:00:05
00:00:04
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Viewing Log Files
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
[oracle@vm4]$ ggsci
GGSCI> VIEW REPORT ex1
39
Output is filtered through more utility
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Trail Files
40
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
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Trail Files
41
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
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Trail Files – Data Representation
NUMBER
4-byte length followed by ASCII characters e.g. 2013
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
42
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Trail Files – Data Representation
VARCHAR2
4-byte length followed by ASCII characters
e.g. “GoldenGate” in VARCHAR2(20)
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
43
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Logdump Utility
Dumps contents of GoldenGate trails from
Local trail
Remote trail
To view data set the following parameters:
[oracle@vm4]$ logdump
Logdump>
Logdump>
Logdump>
Logdump>
Logdump>
GHDR ON
FILEHEADER DETAIL
DETAIL DATA
USERTOKEN DETAIL
RECLEN 128
Logdump> OPEN <trailFileName>
To show next record use NEXT or N
Logdump> NEXT
# or N
To return to start of file
Logdump> POS 0
44
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Logdump
Sample output
Hdr-Ind
UndoFlag
RecLength
IOType
TransInd
SyskeyLen
AuditRBA
Continued
:
:
:
:
:
:
:
:
E
.
51
5
.
0
N
(x45)
(x00)
(x0033)
(x05)
(x01)
(x00)
80
(x00)
Partition :
. (x04)
BeforeAfter:
A (x41)
IO Time
: 2013/04/05 06:24:00.000.000
OrigNode
:
255 (xff)
FormatType :
R (x52)
Incomplete :
. (x00)
AuditPos
: 32769040
RecCount
:
1 (x01)
2013/04/05 06:24:00.000.000 Insert
Len
51 RBA 9060
Name: US03.T1
After Image:
Partition 4
G
0000 0006 0000 0002 3630 0001 0007 0000 0003 5359 | ........60........SY
5300 0200 0d00 0000 094f 424a 4552 524f 5224 0003 | S........OBJERROR$..
0009 0000 0005 5441 424c 45
| ......TABLE
Column
0 (x0000), Len
6 (x0006)
0000 0002 3630
| ....60
Column
1 (x0001), Len
7 (x0007)
0000 0003 5359 53
| ....SYS
Column
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
45
© 2013 Julian Dyke
m
juliandyke.com
GoldenGate
Minimal Supplemental Logging
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:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
46
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Supplemental Logging
Supplemental logging should be configured for all tables being replicated
Enabled using GGSCI. For example:
[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 */
47
where 76111 is the OBJ# of the table
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Sequences
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
To install sequence support on each server run:
[oracle@vm4]$ cd /home/oracle/goldengate
[oracle@vm4]$ sqlplus / as sysdba
SQL> @sequence.sql
48
The sequence.sql script creates the following procedures in the GGSCHEMA :
SEQTRACE
GETSEQFLUSH
REPLICATESEQUENCE
UPDATESEQUENCE
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Sequences
Extract Process
EXTRACT ex1
USERID gg01, PASSWORD gg01
EXTTRAIL /home/oracle/goldengate/dirdat/ex
SEQUENCE us03.*;
TABLE us03.*;
Data Pump process:
EXTRACT dp1
USERID gg01, PASSWORD gg01
RMTHOST vm5, MGRPORT 7809
RMTTRAIL /home/oracle/goldengate/dirdat/rt
SEQUENCE us03.*;
TABLE us03.*;
Replicat process:
REPLICAT rep1
USERID gg01, PASSWORD gg01
ASSUMETARGETDEFS
DISCARDFILE /home/oracle/goldengate/discards, PURGE
MAP US03.SEQ1, TARGET US03.SEQ1;
MAP US03.T*, TARGET US03.*;
49
© 2013 Julian Dyke
juliandyke.com
GoldenGate
TRUNCATE 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
50
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
© 2013 Julian Dyke
juliandyke.com
GoldenGate
TRUNCATE Statements
Extract process
EXTRACT ex1
USERID gg01, PASSWORD gg01
EXTTRAIL /home/oracle/goldengate/dirdat/ex
GETTRUNCATES
TABLE us03.t*;
Data pump process
EXTRACT dp1
USERID gg01, PASSWORD gg01
RMTHOST vm5, MGRPORT 7809
RMTTRAIL /home/oracle/goldengate/dirdat/rt
GETTRUNCATES
TABLE us03.t*;
Replicat process
REPLICAT rep1
USERID gg01, PASSWORD gg01
ASSUMETARGETDEFS
DISCARDFILE /home/oracle/goldengate/discards, PURGE
GETTRUNCATES
MAP US03.*, TARGET US03.*;
51
© 2013 Julian Dyke
juliandyke.com
GoldenGate
DDL Support
DDL is not supported by default
To install DDL support on the source server:
[oracle@vm4]$ cd /home/oracle/goldengate
[oracle@vm4]$ sqlplus / as sysdba
SQL> @marker_setup
SQL> @ddl_setup
SQL> @role_setup
SQL> @ddl_enable
52
No configuration necessary on target server for DDL support
DDL support is implemented using
System DDL trigger GGS_DDL_TRIGGER_BEFORE
DDL Replication Package
GGS_MARKER table
GGS_DDL_HIST table
© 2013 Julian Dyke
juliandyke.com
GoldenGate
DDL Support
Extract process:
EXTRACT ex1
USERID gg01, PASSWORD gg01
EXTTRAIL /home/oracle/goldengate/dirdat/ex
DDL INCLUDE ALL
TABLE US03.t*;
Data Pump process:
EXTRACT dp1
USERID gg01, PASSWORD gg01
RMTHOST vm5, MGRPORT 7809
RMTTRAIL /home/oracle/goldengate/dirdat/rt
DDL INCLUDE ALL
TABLE US03.t*;
Replicat process
REPLICAT rep1
USERID gg01, PASSWORD gg01
ASSUMETARGETDEFS
DISCARDFILE /home/oracle/goldengate/discards, PURGE
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
MAP US03.*, TARGET US03.*;
53
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Mappings
Mappings between source and target can be specified
At schema level
At table level
At column level
All mappings are performed by the replicat process
Mappings are specified in replicat parameter file
Schema level mapping
REPLICAT rep1
USERID gg01, PASSWORD gg01
ASSUMETARGETDEFS
DISCARDFILE /home/oracle/goldengate/discards, PURGE
MAP US03.* TARGET US01.*;
Table level mapping
REPLICAT rep1
USERID gg01, PASSWORD gg01
ASSUMETARGETDEFS
DISCARDFILE /home/oracle/goldengate/discards, PURGE
MAP US03.T52 TARGET US01.T53;
54
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Mappings
Column Level Mappings require a definition generated on source and copied
to target
On the source
[oracle@vm4]$ cd /home/oracle/goldengate
[oracle@vm4]$ ggsci
GGSCI 1> EDIT PARAMS defgen1
DEFSFILE ./dirdef/defgen1.def
USERID us03 PASSWORD us03
TABLE us03.t54;
Generate the definitions using
[oracle@vm4]$ cd /home/oracle/goldengate
[oracle@vm4]$ ./defgen paramfile /home/oracle/goldengate/dirprm/defgen1.prm
55
Copy $GG_HOME/dirdef/defgen1.def to same location on target
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Mappings
Configure replicat parameters
REPLICAT rep1
USERID gg01, PASSWORD gg01
SOURCEDEFS ./dirdef/defgen1.def
DISCARDFILE /home/oracle/goldengate/discards, PURGE
MAP US03.T54 TARGET US01.T55, &
COLMAP (col1=c1, col3=c2, col4=c3, col2=c4);
Note – COLMAP syntax is <TargetColumnName>=<SourceColumnName>
Mapping is written to the report file e.g.:
MAP resolved (entry US03.T54):
MAP "US03"."T54" TARGET US01.T55, COLMAP (col1=c1, col3=c2, col4=c3,
col2=c4);
Using the following key columns for target table US01.T55: COL1.
56
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Integrated Capture
Requires
Oracle 11.2.0.3 or above
Bundle patch 15987144
Uses additional memory in streams pool
May need to increase
MEMORY_TARGET parameter
STREAMS_POOL parameter
Size of /dev/shm (specified in /etc/fstab)
To avoid warnings
SQL> ALTER SYSTEM SET compatible = '11.2.0.3.0' SCOPE=SPFILE;
SQL> STARTUP FORCE;
57
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Integrated Capture
Grant administrative privileges to GGSCHEMA
BEGIN
dbms_goldengate_auth.grant_admin_privilege
(
grantee => 'GG01',
privilege_type => 'CAPTURE',
grant_select_privileges => TRUE
);
END;
/
On source server register extract group with database
[oracle@vm4]$ ggsci
GGSCI (vm4) 1> DBLOGIN USERID gg01 PASSWORD gg01
Successfully logged into database.
GGSCI (vm4) 2> REGISTER EXTRACT ex2 DATABASE
2013-04-19 14:04:01 INFO OGG-02003 Extract EX2 successfully registered
with database at SCN 2109330.
58
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Integrated Capture
Extract process parameters
EXTRACT ex2
USERID gg01, PASSWORD gg01
TRANLOGOPTIONS INTEGRATED PARAMS (MAX_SGA_SIZE 100)
EXTTRAIL /home/oracle/goldengate/dirdat/il
TABLE us01.t101;
Data pump process parameters
EXTRACT dp2
USERID gg01, PASSWORD gg01
RMTHOST vm5, MGRPORT 7809
RMTTRAIL /home/oracle/goldengate/dirdat/ir
TABLE US01.*;
Replicat process parameters
REPLICAT rep2
USERID gg01, PASSWORD gg01
ASSUMETARGETDEFS
DISCARDFILE /home/oracle/goldengate/discards, PURGE
MAP US01.* TARGET US01.*;
59
© 2013 Julian Dyke
juliandyke.com
GoldenGate
Integrated Capture
New processes and trails must be added using GGSCI.
For example on the source server:
[oracle@vm4]$ ggsci
GGSCI> ADD EXTRACT ex2 INTEGRATED TRANLOG, BEGIN NOW
GGSCI> ADD EXTTRAIL /home/oracle/goldengate/dirdat/il, EXTRACT ex2
GGSCI> ADD EXTRACT dp2 EXTTRAILSOURCE /home/oracle/goldengate/dirdat/il
GGSCI> ADD RMTTRAIL /home/oracle/goldengate/dirdat/ir, EXTRACT dp2
On the target server:
[oracle@vm5]$ ggsci
GGSCI> ADD REPLICAT rep2, EXTTRAIL /home/oracle/goldengate/dirdat/ir
60
Start processes in GGSCI
© 2013 Julian Dyke
juliandyke.com
Redo
Versus
Trail
61
© 2013 Julian Dyke
juliandyke.com
Redo versus Trail
Single-Row Inserts
INSERT INTO t2
VALUES (1,’Alpha’);
TX Header
Redo
Trail
5.2
DRP Undo
5.1
Insert Row 1
11.2
Insert Row 2
IRP Redo
5.20
5.1
Index Insert
INSERT INTO t2
VALUES (2,’Beta’);
DRP Undo
10.2
5.1
11.2
IRP Redo
5.20
5.1
10.2
COMMIT;
Commit
62
© 2013 Julian Dyke
5.4
24.4
juliandyke.com
Redo versus Trail
Multi-Row Inserts
INSERT INTO t2
SELECT * FROM t1;
Redo
Trail
5.2
Insert Row 1
TX Header
QMD Undo
5.1
11.3
QMI Redo
Insert Row 2
Insert Row 3
Insert Row 4
Index Insert
5.20
Insert Row 5
5.1
Insert Row 6
10.2
Commit
COMMIT;
63
© 2013 Julian Dyke
5.4
24.4
juliandyke.com
Redo versus Trail
Direct Inserts
INSERT /*+ APPEND */
INTO t2
SELECT * FROM t1;
TX Header
Redo
Trail
5.2
5.1
Insert Row 1
5.20
Insert Row 2
24.6
19.1
Direct Load
Insert Row 3
Insert Row 4
Insert Row 5
5.2
5.1
Index Insert
Insert Row 6
10.2
Commit
COMMIT;
64
© 2013 Julian Dyke
5.4
24.4
juliandyke.com
Redo versus Trail
Single-Row Updates
Redo
UPDATE t2
SET c2 = ‘Alpha’
WHERE c1 = 1;
TX Header
5.2
URP Undo
Update Row 1
5.1
11.5
UPDATE t2
SET c2 = ‘Beta’
WHERE c1 = 2;
Trail
Update Row 2
URP Redo
5.20
URP Undo
Update Row 3
5.1
11.5
URP Redo
UPDATE t2
SET c2 = ‘Gamma’
WHERE c1 = 3;
URP Undo
5.1
11.5
5.1
URP Redo
Commit
COMMIT;
65
© 2013 Julian Dyke
5.4
24.4
juliandyke.com
Redo versus Trail
Multi-Row Updates
Redo
UPDATE t2
SET c3 = c3 * 1.1;
Trail
TX Header
5.2
URP Undo
Update Row 1
5.1
11.5
Update Row 2
URP Redo
5.20
URP Undo
Update Row 3
5.1
11.5
URP Redo
URP Undo
5.1
11.5
5.1
URP Redo
Commit
COMMIT;
66
© 2013 Julian Dyke
5.4
24.4
juliandyke.com
Redo versus Trail
Single-Row Deletes
Redo
Trail
TX Header
DELETE FROM t2
WHERE c1 = 1;
5.2
IRP Undo
DRP Redo
Delete Row 1
5.1
11.3
Delete Row 2
5.20
Index Undo
5.1
DELETE FROM t
WHERE c1 = 2;
Index Redo
10.2
IRP Undo
5.1
DRP Redo
5.1
11.3
Index Undo
5.1
Index Redo
10.2
Commit
5.4
24.4
COMMIT;
67
© 2013 Julian Dyke
juliandyke.com
Redo versus Trail
Multi-Row Deletes
Redo
Trail
TX Header
DELETE FROM t2;
5.2
IRP Undo
DRP Redo
Delete Row 1
5.1
11.3
Delete Row 2
5.20
Index Undo
5.1
Index Redo
10.2
IRP Undo
5.1
DRP Redo
5.1
11.3
Index Undo
5.1
Index Redo
10.2
Commit
5.4
24.4
COMMIT;
68
© 2013 Julian Dyke
juliandyke.com
Thank You For Your Interest
[email protected]
69
© 2013 - Julian Dyke
juliandyke.com