Transcript Supplemental Logging - UK Oracle User Group

6-Node Active-Active Oracle
GoldenGate
Experiences and Lessons Learned
Luke Davies
Why Pythian
• Recognized Leader:
•
Global industry leader in data infrastructure managed services and consulting with
expertise in Oracle, Oracle Applications, Microsoft SQL Server, MySQL, big data and
systems administration
•
Work with over 200 multinational companies such as Forbes.com, Fox Sports,
Nordion and Western Union to help manage their complex IT deployments
• Expertise:
•
One of the world’s largest concentrations of dedicated, full-time DBA expertise.
Employ 8 Oracle ACEs/ACE Directors
•
Hold 7 Specializations under Oracle Platinum Partner program, including Oracle
Exadata, Oracle GoldenGate & Oracle RAC
• Global Reach & Scalability:
•
24/7/365 global remote support for DBA and consulting, systems administration,
special projects or emergency response
© 2012 – Pythian
About Luke Davies
•
Team Technical Lead
•
Oracle DBA since V6 (1990)
•
BSc Microbiology
•
With Pythian since 2008
•
Main Focus
•
Oracle Core
•
Oracle Performance Tuning
© 2012 – Pythian
6-Node Active-Active Oracle
GoldenGate
Set Up
Set Up
• Business requirements
•
High availability
•
Easily Scalable
•
Application releases with zero downtime
•
Database patches/upgrades with zero downtime
•
Real-time reporting database with no impact to Online users
•
Other application entry points with no impact to Online users
• Original Solution based on
•
Oracle Standard Edition 10gR2
•
GoldenGate V10.4
© 2012 – Pythian
Set Up
© 2012 – Pythian
Set Up
© 2012 – Pythian
6-Node Active-Active Oracle
GoldenGate
Experiences and Lessons Learned
Topics
Active/Active considerations
•
•
Supplemental Logging
•
DDL Replication
•
Conflict Avoidance
•
Conflict Detection
•
Release Management
•
Data comparison
General considerations
•
•
Trail file deletion
•
Archive log deletion
© 2012 – Pythian
Supplemental Logging
Required at the database level (Minimum Level)
•
•
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
•
Minimal Supplemental Logging
“logs the minimal amount of information needed for LogMiner to identify, group, and
merge the redo operations associated with DML changes”
Identification key logging (PK, UK, FK)
•
•
•
Table Level
•
Database Level
•
Method
•
SQL
e.g. ALTER TABLE <> ADD SUPPLEMENTAL LOG DATA …
•
GoldenGate
e.g. ADD TRANDATA <Table Name>
Cost of Omission
Changes may fail
© 2012 – Pythian
Supplemental Logging
Tgt
Source
create table repuser2.tbl4
(id number, fname varchar2(100), lname varchar2(100), issued date);
2012-11-14 09:20:57 INFO OGG-00487 Oracle GoldenGate Capture for Oracle, ext_nb.prm: DDL operation included
[INCLUDE ALL OBJNAME REPUSER2.*], optype [CREATE], objtype [TABLE], objowner [REPUSER2], objname [TBL4].
2012-11-14 09:20:57 INFO
OGG-00497 Oracle GoldenGate Capture for Oracle, ext_nb.prm:
Writing DDL operation to
extract trail file.
Successfully added TRAN
DATA for table [REPUSER2.TBL4], operation [ALTER TABLE "REPUSER2"."TBL4" ADD SUPPLEMENTAL LOG
GROUP "GGT_61642" ("ID","FNAME","LNAME","ISSUED") ALWAYS /*
GOLDENGATE_DDL_REPLICATION */ (size 141)].
2012-11-14 09:21:03 INFO
OGG-00477 Oracle GoldenGate Capture for Oracle, ext_nb.prm:
2012-11-14 09:21:04 INFO OGG-00477 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully added TRAN DATA
for table [REPUSER2.TBL4], operation [ALTER TABLE "REPUSER2"."TBL4" ADD SUPPLEMENTAL LOG DATA (ALL)
COLUMNS /* GOLDENGATE_DDL_REPLICATION */ (size 103)].
2012-11-14 09:21:04 INFO OGG-00479 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully deleted TRAN DATA
for [CREATE] DDL operation, table [REPUSER2.TBL4], operation [ ALTER TABLE "REPUSER2"."TBL4" DROP
SUPPLEMENTAL LOG GROUP "GGT_61642" /* GOLDENGATE_DDL_REPLICATION */ (size 103)].
select * from dba_log_groups where table_name='TBL4';
OWNER
----REPUSER2
LOG_GROUP_NAME
-------------GGS_61642
TABLE_NAME
---------TBL4
LOG_GROUP_TYPE
-------------USER LOG GROUP
© 2012 – Pythian
ALWAYS GENERATED
------ --------ALWAYS USER NAME
Drop SL
for all
named
columns
Supplemental Logging
Src
Target
DDL found, operation
[create table repuser2.tbl4 (id number, fname varchar2(100), lname varchar2(100), issued date)
2012-11-14 09:21:05 INFO
OGG-00482 Oracle GoldenGate Delivery for Oracle, rena_nb.prm:
(size 95)].
2012-11-14 09:21:05 INFO
OGG-00489 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL is of mapped scope, after
mapping new operation [create
table REPUSER2.TBL4 (id number, fname varchar2(100), lname
varchar2(100), issued date) (size 95)].
2012-11-14 09:21:05 INFO
operation to [SYS].
OGG-01407 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: Setting current schema for DDL
2012-11-14 09:21:05 INFO
OGG-00484 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: Executing DDL operation.
2012-11-14 09:21:06 INFO
OGG-00483 Oracle GoldenGate Delivery for Oracle, rena_nb.prm:
DDL operation
successful.
2012-11-14 09:21:06 INFO
operation to [ggsdb].
OGG-01408 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: Restoring current schema for DDL
desc repuser2.tbl4
Name
ID
FNAME
LNAME
ISSUED
Null?
No SL
In
Target
TBL4
in
Target
Type
NUMBER
VARCHAR2(100)
VARCHAR2(100)
DATE
© 2012 – Pythian
Supplemental Logging
Tgt
Source
alter table repuser2.tbl4 add primary key(id);
OGG-01487 Oracle GoldenGate Capture for Oracle, ext_nb.prm: DDL found, operation [alter
table repuser2.tbl4 add primary key(id) (size 47)], start SCN [9690796], commit SCN [9690813] instance [orcl1123 (1)],
DDL seqno [1008], marker seqno [1008].
2012-11-14 09:51:07 INFO
2012-11-14 09:51:07 INFO OGG-00477 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully added TRAN DATA
for table [REPUSER2.TBL4], operation [ALTER TABLE "REPUSER2"."TBL4" ADD SUPPLEMENTAL LOG GROUP
"GGT_61642" ("ID") ALWAYS /* GOLDENGATE_DDL_REPLICATION */ (size 116)].
2012-11-14 09:51:07 INFO OGG-00479 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully deleted TRAN DATA
for [ALTER] DDL operation, table [REPUSER2.TBL4], operation [ ALTER TABLE "REPUSER2"."TBL4" DROP
SUPPLEMENTAL LOG DATA (ALL) COLUMNS /* GOLDENGATE_DDL_REPLICATION */ (size 104)].
2012-11-14 09:51:07 INFO OGG-00477 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully added TRAN DATA
for table [REPUSER2.TBL4], operation [ALTER TABLE "REPUSER2"."TBL4" ADD SUPPLEMENTAL LOG GROUP
"GGS_61642" ("ID") ALWAYS /* GOLDENGATE_DDL_REPLICATION */ (size 116)].
2012-11-14 09:51:07 INFO OGG-00479 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully deleted TRAN DATA
for [ALTER] DDL operation, table [REPUSER2.TBL4], operation [ ALTER TABLE "REPUSER2"."TBL4" DROP
SUPPLEMENTAL LOG GROUP "GGT_61642" /* GOLDENGATE_DDL_REPLICATION */ (size 103)].
select * from dba_log_group_columns where table_name='TBL4';
OWNER
LOG_GROUP_NAME TABLE_NAME COLUMN_NAME POSITION
------------------ ---------- ----------- -------REPUSER2 GGS_61642
TBL4
ID
1
© 2012 – Pythian
LOGGING_PROPERTY
---------------LOG
SL for all
columns
is
dropped
SL for ID
is added
(PK)
Supplemental Logging
Source
Target
2012-11-14 09:51:11 INFO
[alter
OGG-00482 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL found, operation
table repuser2.tbl4 add primary key(id) (size 47)].
2012-11-14 09:51:11 INFO
OGG-00489 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL is of mapped scope,
after mapping new operation [alter
table REPUSER2.TBL4 add primary key(id) (size 47)].
2012-11-14 09:51:11 INFO
DDL operation to [SYS].
OGG-01407 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: Setting current schema for
2012-11-14 09:51:11 INFO
OGG-00484 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: Executing DDL operation.
2012-11-14 09:51:14 INFO
OGG-00483 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL operation successful.
2012-11-14 09:51:14 INFO
DDL operation to [ggsdb].
OGG-01408 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: Restoring current schema for
select * from dba_log_groups where table_name='TBL4';
no rows
NO SL
In
Target
YET !!
© 2012 – Pythian
Supplemental Logging
What if we keep running replication this way
T
Source
Target
t1
insert into repuser2.tbl4 values (1,'Test','TestL',sysdate);
insert into repuser2.tbl4 values (2,'Test','TestL2',sysdate);
insert into repuser2.tbl4 values (3,'Test','TestL3',sysdate);
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ -------AAAPDKAAEAAAAIOAAA
AAAPDKAAEAAAAIOAAB
AAAPDKAAEAAAAIOAAC
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestL3 14/11/12
t2
© 2012 – Pythian
Supplemental Logging
What if we keep running replication this way
T
Source
Target
t1
insert into repuser2.tbl4 values (1,'Test','TestL',sysdate);
insert into repuser2.tbl4 values (2,'Test','TestL2',sysdate);
insert into repuser2.tbl4 values (3,'Test','TestL3',sysdate);
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ --------
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ ---------
AAAPDKAAEAAAAIOAAA
AAAPDKAAEAAAAIOAAB
AAAPDKAAEAAAAIOAAC
AAAar/AAEAAAE5HAAA
AAAar/AAEAAAE5HAAB
AAAar/AAEAAAE5HAAC
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestL3 14/11/12
t2
© 2012 – Pythian
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestL3 14/11/12
Supplemental Logging
What if we keep running replication this way
T
Source
t1
insert into repuser2.tbl4 values (1,'Test','TestL',sysdate);
insert into repuser2.tbl4 values (2,'Test','TestL2',sysdate);
insert into repuser2.tbl4 values (3,'Test','TestL3',sysdate);
t2
Target
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ --------
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ ---------
AAAPDKAAEAAAAIOAAA
AAAPDKAAEAAAAIOAAB
AAAPDKAAEAAAAIOAAC
AAAar/AAEAAAE5HAAA
AAAar/AAEAAAE5HAAB
AAAar/AAEAAAE5HAAC
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestL3 14/11/12
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestL3 14/11/12
update repuser2.tbl4 set lname='TestL4' where fname='Test' and
rowid='AAAar/AAEAAAE5HAAC‘;
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ --------AAAar/AAEAAAE5HAAA
AAAar/AAEAAAE5HAAB
AAAar/AAEAAAE5HAAC
© 2012 – Pythian
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestL4 14/11/12
Supplemental Logging
What if we keep running replication this way
T
Source
t1
insert into repuser2.tbl4 values (1,'Test','TestL',sysdate);
insert into repuser2.tbl4 values (2,'Test','TestL2',sysdate);
insert into repuser2.tbl4 values (3,'Test','TestL3',sysdate);
t2
Target
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ --------
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ ---------
AAAPDKAAEAAAAIOAAA
AAAPDKAAEAAAAIOAAB
AAAPDKAAEAAAAIOAAC
AAAar/AAEAAAE5HAAA
AAAar/AAEAAAE5HAAB
AAAar/AAEAAAE5HAAC
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestL3 14/11/12
UPDATE "REPUSER2"."TBL4" SET "LNAME"=:a1 WHERE
"ID"=:b0
OCI Error ORA-01403: no data found, SQL <UPDATE
"REPUSER2"."TBL4" SET "LNAME" = :a1 WHERE "ID" = :b0>
Operation failed at seqno 5 rba 4598
Discarding record on action DISCARD on error 1403
Problem replicating REPUSER2.TBL4 to REPUSER2.TBL4
Mapping problem with compressed update record (target format)...
*
ID =
LNAME = TestL4
TestL 14/11/12
TestL2 14/11/12
TestL3 14/11/12
update repuser2.tbl4 set lname='TestL4' where fname='Test' and
rowid='AAAar/AAEAAAE5HAAC‘;
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ --------AAAar/AAEAAAE5HAAA
AAAar/AAEAAAE5HAAB
AAAar/AAEAAAE5HAAC
Record not found
1 Test
2 Test
3 Test
Missing
Value!
© 2012 – Pythian
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestL4 14/11/12
Supplemental Logging
What if supplemental logging is enabled on target
T
Source
Target
t1
insert into repuser2.tbl4 values (1,'Test','TestL',sysdate);
insert into repuser2.tbl4 values (2,'Test','TestL2',sysdate);
insert into repuser2.tbl4 values (3,'Test','TestL3',sysdate);
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ -------AAAPDKAAEAAAAIOAAA
AAAPDKAAEAAAAIOAAB
AAAPDKAAEAAAAIOAAC
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestL3 14/11/12
t2
© 2012 – Pythian
Supplemental Logging
What if supplemental logging is enabled on target
T
Source
Target
t1
insert into repuser2.tbl4 values (1,'Test','TestL',sysdate);
insert into repuser2.tbl4 values (2,'Test','TestL2',sysdate);
insert into repuser2.tbl4 values (3,'Test','TestL3',sysdate);
add trandata repuser2.tbl4
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ --------
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ ---------
AAAPDKAAEAAAAIOAAA
AAAPDKAAEAAAAIOAAB
AAAPDKAAEAAAAIOAAC
AAAar/AAEAAAE5HAAA
AAAar/AAEAAAE5HAAB
AAAar/AAEAAAE5HAAC
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestL3 14/11/12
t2
© 2012 – Pythian
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestL3 14/11/12
Supplemental Logging
What if supplemental logging is enabled on target
T
Source
Target
t1
insert into repuser2.tbl4 values (1,'Test','TestL',sysdate);
insert into repuser2.tbl4 values (2,'Test','TestL2',sysdate);
insert into repuser2.tbl4 values (3,'Test','TestL3',sysdate);
add trandata repuser2.tbl4
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ --------
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ ---------
AAAPDKAAEAAAAIOAAA
AAAPDKAAEAAAAIOAAB
AAAPDKAAEAAAAIOAAC
AAAar/AAEAAAE5HAAA
AAAar/AAEAAAE5HAAB
AAAar/AAEAAAE5HAAC
t2
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestL3 14/11/12
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestL3 14/11/12
update repuser2.tbl4 set lname='TestLB' where fname='Test' and
rowid='AAAar/AAEAAAE5HAAC‘;
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ --------AAAar/AAEAAAE5HAAA
AAAar/AAEAAAE5HAAB
AAAar/AAEAAAE5HAAC
© 2012 – Pythian
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestLB 14/11/12
Supplemental Logging
What if supplemental logging is enabled on target
T
Source
Target
t1
insert into repuser2.tbl4 values (1,'Test','TestL',sysdate);
insert into repuser2.tbl4 values (2,'Test','TestL2',sysdate);
insert into repuser2.tbl4 values (3,'Test','TestL3',sysdate);
add trandata repuser2.tbl4
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ --------
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ ---------
AAAPDKAAEAAAAIOAAA
AAAPDKAAEAAAAIOAAB
AAAPDKAAEAAAAIOAAC
AAAar/AAEAAAE5HAAA
AAAar/AAEAAAE5HAAB
AAAar/AAEAAAE5HAAC
t2
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestL3 14/11/12
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestL3 14/11/12
UPDATE "REPUSER2"."TBL4" SET "LNAME"=:a1 WHERE
"ID"=:b0
a1 : TestLB
b0 : 3
update repuser2.tbl4 set lname='TestLB' where fname='Test' and
rowid='AAAar/AAEAAAE5HAAC‘;
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ --------
ROWID
ID FNAME LNAME ISSUED
------------------ -- ----- ------ ---------
AAAPDKAAEAAAAIOAAA
AAAPDKAAEAAAAIOAAB
AAAPDKAAEAAAAIOAAC
AAAar/AAEAAAE5HAAA
AAAar/AAEAAAE5HAAB
AAAar/AAEAAAE5HAAC
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestLB 14/11/12
© 2012 – Pythian
1 Test
2 Test
3 Test
TestL 14/11/12
TestL2 14/11/12
TestLB 14/11/12
Supplemental Logging
What if supplemental logging is enabled on DB level
T
Source
Target
t1
insert into repuser2.tbl4 values (1,'Test','TestL',sysdate);
insert into repuser2.tbl4 values (2,'Test','TestL2',sysdate);
insert into repuser2.tbl4 values (3,'Test','TestL3',sysdate);
ROWID
ID FNAME LNAME
ISSUED
------------------ -- ----- ------- -------AAAPDKAAEAAAAIOAAA
AAAPDKAAEAAAAIOAAB
AAAPDKAAEAAAAIOAAC
1 Test
2 Test
3 Test
TestL
TestL2
TestL3
14/11/12
14/11/12
14/11/12
t2
© 2012 – Pythian
Supplemental Logging
What if supplemental logging is enabled on DB level
T
Source
Target
t1
insert into repuser2.tbl4 values (1,'Test','TestL',sysdate);
insert into repuser2.tbl4 values (2,'Test','TestL2',sysdate);
insert into repuser2.tbl4 values (3,'Test','TestL3',sysdate);
delete trandata repuser2.tbl4
alter database add supplemental log data (primary key) columns;
alter system switch logfile;
ROWID
ID FNAME LNAME
ISSUED
------------------ -- ----- ------- -------AAAPDKAAEAAAAIOAAA
AAAPDKAAEAAAAIOAAB
AAAPDKAAEAAAAIOAAC
1 Test
2 Test
3 Test
TestL
TestL2
TestL3
14/11/12
14/11/12
14/11/12
ROWID
ID FNAME LNAME
ISSUED
------------------ -- ----- ------- --------AAAar/AAEAAAE5HAAA
AAAar/AAEAAAE5HAAB
AAAar/AAEAAAE5HAAC
t2
© 2012 – Pythian
1 Test
2 Test
3 Test
TestL
TestL2
TestL3
14/11/12
14/11/12
14/11/12
Supplemental Logging
What if supplemental logging is enabled on DB level
T
Source
Target
t1
insert into repuser2.tbl4 values (1,'Test','TestL',sysdate);
insert into repuser2.tbl4 values (2,'Test','TestL2',sysdate);
insert into repuser2.tbl4 values (3,'Test','TestL3',sysdate);
delete trandata repuser2.tbl4
alter database add supplemental log data (primary key) columns;
alter system switch logfile;
ROWID
ID FNAME LNAME
ISSUED
------------------ -- ----- ------- -------AAAPDKAAEAAAAIOAAA
AAAPDKAAEAAAAIOAAB
AAAPDKAAEAAAAIOAAC
t2
1 Test
2 Test
3 Test
TestL
TestL2
TestL3
14/11/12
14/11/12
14/11/12
ROWID
ID FNAME LNAME
ISSUED
------------------ -- ----- ------- --------AAAar/AAEAAAE5HAAA
AAAar/AAEAAAE5HAAB
AAAar/AAEAAAE5HAAC
1 Test
2 Test
3 Test
TestL
TestL2
TestL3
14/11/12
14/11/12
14/11/12
update repuser2.tbl4 set lname='TestLC’ where fname='Test' and
rowid=‘AAAar/AAEAAAE5HAAC’;
ROWID
ID FNAME LNAME
ISSUED
------------------ -- ----- ------- ---------
AAAar/AAEAAAE5HAAA
AAAar/AAEAAAE5HAAB
AAAar/AAEAAAE5HAAC
© 2012 – Pythian
1 Test
2 Test
3 Test
TestL
TestL2
TestLC
14/11/12
14/11/12
14/11/12
Supplemental Logging
What if supplemental logging is enabled on DB level
T
Source
Target
t1
insert into repuser2.tbl4 values (1,'Test','TestL',sysdate);
insert into repuser2.tbl4 values (2,'Test','TestL2',sysdate);
insert into repuser2.tbl4 values (3,'Test','TestL3',sysdate);
delete trandata repuser2.tbl4
alter database add supplemental log data (primary key) columns;
alter system switch logfile;
ROWID
ID FNAME LNAME
ISSUED
------------------ -- ----- ------- -------AAAPDKAAEAAAAIOAAA
AAAPDKAAEAAAAIOAAB
AAAPDKAAEAAAAIOAAC
t2
1 Test
2 Test
3 Test
TestL
TestL2
TestL3
14/11/12
14/11/12
14/11/12
ROWID
ID FNAME LNAME
ISSUED
------------------ -- ----- ------- --------AAAar/AAEAAAE5HAAA
AAAar/AAEAAAE5HAAB
AAAar/AAEAAAE5HAAC
1 Test
2 Test
3 Test
TestL
TestL2
TestL3
14/11/12
14/11/12
14/11/12
UPDATE "REPUSER2"."TBL4" SET "LNAME"=:a1 WHERE
"ID"=:b0
a1 : TestLC
b0 : 3
update repuser2.tbl4 set lname='TestLC’ where fname='Test'
and rowid=‘AAAar/AAEAAAE5HAAC’;
ROWID
ID FNAME LNAME
ISSUED
------------------ -- ----- ------- --------
ROWID
ID FNAME LNAME
ISSUED
------------------ -- ----- ------- ---------
AAAPDKAAEAAAAIOAAA
AAAPDKAAEAAAAIOAAB
AAAPDKAAEAAAAIOAAC
AAAar/AAEAAAE5HAAA
AAAar/AAEAAAE5HAAB
AAAar/AAEAAAE5HAAC
1 Test
2 Test
3 Test
TestL
TestL2
TestLC
14/11/12
14/11/12
14/11/12
© 2012 – Pythian
1 Test
2 Test
3 Test
TestL
TestL2
TestLC
14/11/12
14/11/12
14/11/12
Supplemental Logging
Supplemental logging is critical to
healthy replication specially for
tables with update/delete changes
PK
Existing
object
Supplemental logging
New
object
© 2012 – Pythian
UK
KEYCOLs
ALL
Supplemental Logging
PK
Existing
object
Supplemental logging
New
object
DDLOPTIONS &
ADDTRANDATA &
GETREPLICATES &
REPORT
UK
KEYCOLs
ALL
To add SL to source DB
To replicat add SL to target
© 2012 – Pythian
Supplemental Logging
Time
Source
t1
create table repuser2.tbl11 (id number, name varchar2(10));
2012-11-09 14:58:58 INFO
OGG-01487 Oracle GoldenGate Capture for Oracle, ext_nb.prm: DDL found, operation [create
table
repuser2.tbl11 (id number, name varchar2(10))
2012-11-09 14:58:59 INFO
OGG-00477 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully added TRAN DATA for table
[REPUSER2.TBL11], operation [ALTER TABLE
COLUMNS
"REPUSER2"."TBL11" ADD SUPPLEMENTAL LOG DATA (ALL)
/* GOLDENGATE_DDL_REPLICATION */ (size 104)].
t2
© 2012 – Pythian
Supplemental Logging
Time
Source
t1
create table repuser2.tbl11 (id number, name varchar2(10));
2012-11-09 14:58:58 INFO
OGG-01487 Oracle GoldenGate Capture for Oracle, ext_nb.prm: DDL found, operation [create
table
repuser2.tbl11 (id number, name varchar2(10))
2012-11-09 14:58:59 INFO
OGG-00477 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully added TRAN DATA for table
[REPUSER2.TBL11], operation [ALTER TABLE
COLUMNS
t2
"REPUSER2"."TBL11" ADD SUPPLEMENTAL LOG DATA (ALL)
/* GOLDENGATE_DDL_REPLICATION */ (size 104)].
alter table repuser2.tbl11 add primary key (id);
2012-11-09 15:00:04 INFO
OGG-01487 Oracle GoldenGate Capture for Oracle, ext_nb.prm: DDL found, operation [alter
table
repuser2.tbl11 add primary key (id)
(2012-11-09 15:00:04 INFO
OGG-00477 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully added TRAN DATA for table
[REPUSER2.TBL11], operation [ALTER TABLE
"GGT_61549" ("ID") ALWAYS
2012-11-09 15:00:04 INFO
"REPUSER2"."TBL11" ADD SUPPLEMENTAL LOG GROUP
/* GOLDENGATE_DDL_REPLICATION */ (size 117)].
OGG-00479 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully deleted TRAN DATA for [ALTER] DDL
operation, table [REPUSER2.TBL11], operation [ ALTER TABLE
(ALL) COLUMNS
"REPUSER2"."TBL11" DROP SUPPLEMENTAL LOG DATA
/* GOLDENGATE_DDL_REPLICATION */ (size 105)].
2012-11-09 15:00:04 INFO
OGG-00477 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully added TRAN DATA for table
[REPUSER2.TBL11], operation [ALTER TABLE
"GGS_61549" ("ID") ALWAYS
2012-11-09 15:00:04 INFO
"REPUSER2"."TBL11" ADD SUPPLEMENTAL LOG GROUP
/* GOLDENGATE_DDL_REPLICATION */ (size 117)].
OGG-00479 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully deleted TRAN DATA for [ALTER] DDL
operation, table [REPUSER2.TBL11], operation [ ALTER TABLE
GROUP "GGT_61549"
"REPUSER2"."TBL11" DROP SUPPLEMENTAL LOG
/* GOLDENGATE_DDL_REPLICATION */ (size 104)].
© 2012 – Pythian
Supplemental Logging
Source
Target
2012-11-14 09:51:11 INFO
[alter
OGG-00482 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL found, operation
table repuser2.tbl11 add primary key(id) (size 47)].
2012-11-14 09:51:11 INFO
OGG-00489 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL is of mapped scope,
after mapping new operation [alter
table REPUSER2.TBL11 add primary key(id) (size 47)].
2012-11-14 09:51:11 INFO
DDL operation to [SYS].
OGG-01407 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: Setting current schema for
2012-11-14 09:51:11 INFO
OGG-00484 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: Executing DDL operation.
2012-11-14 09:51:14 INFO
No SL
OGG-01408 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: Restoring current schema foroperations
2012-11-14 09:51:14 INFO
DDL operation to [ggadmin].
OGG-00483 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL operation successful.
© 2012 – Pythian
Supplemental Logging
Source
Target
2012-11-14 09:51:11 INFO
[alter
OGG-00482 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL found, operation
table repuser2.tbl11 add primary key(id) (size 47)].
2012-11-14 09:51:11 INFO
OGG-00489 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL is of mapped scope,
after mapping new operation [alter
table REPUSER2.TBL11 add primary key(id) (size 47)].
2012-11-14 09:51:11 INFO
DDL operation to [SYS].
OGG-01407 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: Setting current schema for
2012-11-14 09:51:11 INFO
OGG-00484 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: Executing DDL operation.
2012-11-14 09:51:14 INFO
No SL
OGG-01408 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: Restoring current schema foroperations
2012-11-14 09:51:14 INFO
DDL operation to [ggadmin].
OGG-00483 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL operation successful.
select * from dba_log_groups where table_name='TBL11';
no rows
© 2012 – Pythian
Supplemental Logging
Problem documented in MOS 1472420.1
•
“If your TRANSLOGOPTIONS EXCLUDEUSER specified in the Extract is
the same as the EXTRACT USERID, the DDL to add supplemental
logging is not captured and sent to the target.”
Our extract settings were
•
•
USERID ggadmin
•
TRANLOGOPTIONS EXCLUDEUSER ggadmin
Extract USER ID and
Replicat USER ID should
be different
© 2012 – Pythian
Supplemental Logging
Time
Source
t1
create table repuser2.tbl11 (id number, name varchar2(10));
2012-11-09 14:58:58 INFO
OGG-01487 Oracle GoldenGate Capture for Oracle, ext_nb.prm: DDL found, operation [create
table
repuser2.tbl11 (id number, name varchar2(10))
2012-11-09 14:58:59 INFO
OGG-00477 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully added TRAN DATA for table
[REPUSER2.TBL11], operation [ALTER TABLE
COLUMNS
t2
"REPUSER2"."TBL11" ADD SUPPLEMENTAL LOG DATA (ALL)
/* GOLDENGATE_DDL_REPLICATION */ (size 104)].
alter table repuser2.tbl11 add primary key (id);
2012-11-09 15:00:04 INFO
OGG-01487 Oracle GoldenGate Capture for Oracle, ext_nb.prm: DDL found, operation [alter
table
repuser2.tbl11 add primary key (id)
(2012-11-09 15:00:04 INFO
OGG-00477 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully added TRAN DATA for table
[REPUSER2.TBL11], operation [ALTER TABLE
"GGT_61549" ("ID") ALWAYS
2012-11-09 15:00:04 INFO
"REPUSER2"."TBL11" ADD SUPPLEMENTAL LOG GROUP
/* GOLDENGATE_DDL_REPLICATION */ (size 117)].
OGG-00479 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully deleted TRAN DATA for [ALTER] DDL
operation, table [REPUSER2.TBL11], operation [ ALTER TABLE
(ALL) COLUMNS
"REPUSER2"."TBL11" DROP SUPPLEMENTAL LOG DATA
/* GOLDENGATE_DDL_REPLICATION */ (size 105)].
2012-11-09 15:00:04 INFO
OGG-00477 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully added TRAN DATA for table
[REPUSER2.TBL11], operation [ALTER TABLE
"GGS_61549" ("ID") ALWAYS
2012-11-09 15:00:04 INFO
"REPUSER2"."TBL11" ADD SUPPLEMENTAL LOG GROUP
/* GOLDENGATE_DDL_REPLICATION */ (size 117)].
OGG-00479 Oracle GoldenGate Capture for Oracle, ext_nb.prm: Successfully deleted TRAN DATA for [ALTER] DDL
operation, table [REPUSER2.TBL11], operation [ ALTER TABLE
GROUP "GGT_61549"
"REPUSER2"."TBL11" DROP SUPPLEMENTAL LOG
/* GOLDENGATE_DDL_REPLICATION */ (size 104)].
© 2012 – Pythian
Supplemental Logging
Time
Target
t1,t2
2012-11-09 14:59:01 INFO
operation [create
OGG-00489 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL is of mapped scope, after mapping new
table REPUSER2."TBL11" (id number, name varchar2(10))
2012-11-09 15:00:06 INFO
OGG-00482 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL found, operation [alter
repuser2.tbl11 add primary key (id)
2012-11-09 15:02:53 INFO
(size 62)].
table
(size 49)].
OGG-00482 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL found, operation [ALTER TABLE
"REPUSER2"."TBL11" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS (size 72)].
2012-11-09 15:02:53 INFO
OGG-00483 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL operation successful.
2012-11-09 15:03:27 INFO
OGG-00482 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL found, operation [ALTER TABLE
"REPUSER2"."TBL11" ADD SUPPLEMENTAL LOG GROUP "GGT_61549" ("ID") ALWAYS
(size 85)].
2012-11-09 15:03:27 INFO
OGG-00483 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL operation successful.
2012-11-09 15:04:59 INFO
OGG-00482 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL found, operation [ALTER TABLE
"REPUSER2"."TBL11" DROP SUPPLEMENTAL LOG GROUP "GGT_61549"
(size 72)].
2012-11-09 15:04:59 INFO
OGG-00483 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: DDL operation successful.
2012-11-09 15:04:59 INFO
[ggsdb].
OGG-01408 Oracle GoldenGate Delivery for Oracle, rena_nb.prm: Restoring current schema for DDL operation to
GETREPLICATES does not work as it is expected
© 2012 – Pythian
Supplemental Logging
Tim
e
Source
Tim
e
Target
t1
Create table
t1
Create table
t2
ADD SUPPLEMENTAL LOG DATA (ALL)
COLUMNS
t2
Create PK index
t3
ADD SUPPLEMENTAL LOG DATA (ALL)
COLUMNS
t3
Create PK index
t4
add supplemental log data (id) (temp)
t4
add supplemental log data (id) (temp)
t5
drop SUPPLEMENTAL LOG DATA (ALL)
t5
drop supplemental log data (id) (temp)
T6
add supplemental log data (id)
T7
drop supplemental log data (id) (temp)
1. ADDTRANDATA on source for new object.
2. Monitor replicated object on target and add
SL manually before any DML change
© 2012 – Pythian
Supplemental Logging
SCHEMATRANDATA
In GoldenGate 11.1
OGG-01783 Cannot verify existence of table function that
is required to enable schema
level supplemental logging, failed to find function
DB Patches needed to support SCHEMATRANDATA in OGG
(Oracle GoldenGate), OGG-01783 Cannot verify existence
of table function that is required to enable schema [MOS 1426440.1]
To use SCHEMATRANDATA, please apply the fix in bug 13794550
© 2012 – Pythian
Only on
some
platforms
Supplemental Logging
Lessons Learned
•
Supplemental logging is critical for proper function
•
Different users needed for extract and replicat
•
Add trandata manually for replicated nodes
•
Need to monitor schema for tables without trandata
•
Use SCHEMATRANDATA if you can
© 2012 – Pythian
Topics
Active/Active considerations
•
•
Supplemental Logging
•
DDL Replication
•
Conflict Avoidance
•
Conflict Detection
•
Release Management
•
Data comparison
General considerations
•
•
Trail file deletion
•
Archive log deletion
© 2012 – Pythian
DDL Replication
Time
Source
T1
create table luke (
a number
,b date
,c varchar2(30) );
Target
alter table luke
add primary key ( a );
T2
© 2012 – Pythian
DDL Replication
Time
Source
Target
T1
create table luke (
a number
,b date
,c varchar2(30) );
desc luke
Name
Null?
-------A
B
C
alter table luke
add primary key ( a );
Type
---NUMBER
DATE
VARCHAR2(30)
add trandata luke
T2
© 2012 – Pythian
DDL Replication
Time
Source
Target
T1
create table luke (
a number
,b date
,c varchar2(30) );
desc luke
Name
Null?
-------A
B
C
alter table luke
add primary key ( a );
Type
---NUMBER
DATE
VARCHAR2(30)
add trandata luke
T2
insert into luke values
( 1, sysdate, 'Luke Test 1' );
© 2012 – Pythian
DDL Replication
Time
Source
Target
T1
create table luke (
a number
,b date
,c varchar2(30) );
desc luke
Name
Null?
-------A
B
C
alter table luke
add primary key ( a );
Type
---NUMBER
DATE
VARCHAR2(30)
add trandata luke
T2
insert into luke values
( 1, sysdate, 'Luke Test 1' );
select * from luke;
A B
C
- --------- ------------------1 19-NOV-12 Luke Test 1
insert into luke values
( 2, sysdate, 'Luke Test 2' );
© 2012 – Pythian
DDL Replication
Time
Source
T3
select * from luke;
A
2
1
B
--------19-NOV-12
19-NOV-12
Target
C
------------------Luke Test 2
Luke Test 1
update luke set
c = 'Luke Test 1 updated'
where a = 1;
T4
© 2012 – Pythian
DDL Replication
Time
Source
Target
T3
select * from luke;
select * from luke;
A
2
1
A
2
1
B
--------19-NOV-12
19-NOV-12
C
------------------Luke Test 2
Luke Test 1
update luke set
c = 'Luke Test 1 updated'
where a = 1;
B
--------19-NOV-12
19-NOV-12
C
------------------Luke Test 2
Luke Test 1 updated
update luke set
c = 'Luke Test 2 updated'
where a = 2;
T4
© 2012 – Pythian
DDL Replication
Time
Source
Target
T3
select * from luke;
select * from luke;
A
2
1
A
2
1
B
--------19-NOV-12
19-NOV-12
C
------------------Luke Test 2
Luke Test 1
update luke set
c = 'Luke Test 1 updated'
where a = 1;
T4
B
--------19-NOV-12
19-NOV-12
C
------------------Luke Test 2
Luke Test 1 updated
update luke set
c = 'Luke Test 2 updated'
where a = 2;
select * from luke;
A
2
1
B
--------19-NOV-12
19-NOV-12
C
------------------Luke Test 2 updated
Luke Test 1 updated
alter table luke add
( d timestamp );
© 2012 – Pythian
DDL Replication
Time
Source
Target
T3
select * from luke;
select * from luke;
A
2
1
A
2
1
T4
B
--------19-NOV-12
19-NOV-12
C
------------------Luke Test 2
Luke Test 1
B
--------19-NOV-12
19-NOV-12
C
------------------Luke Test 2
Luke Test 1 updated
update luke set
c = 'Luke Test 1 updated'
where a = 1;
update luke set
c = 'Luke Test 2 updated'
where a = 2;
select * from luke;
desc luke
Name
Null?
-------A
B
C
D
A
2
1
B
--------19-NOV-12
19-NOV-12
C
------------------Luke Test 2 updated
Luke Test 1 updated
alter table luke add
( d timestamp );
© 2012 – Pythian
Type
---NUMBER
DATE
VARCHAR2(30)
TIMESTAMP(6)
DDL Replication
Time
Source
T5
insert into luke values
( 3, sysdate,
'Luke Test 3',
systimestamp );
Target
T6
© 2012 – Pythian
DDL Replication
Time
Source
Target
T5
insert into luke values
( 3, sysdate,
'Luke Test 3',
systimestamp );
select a,d from luke;
A D
- ---------------------------2
3 22-NOV-12 09.40.30.622017 AM
1
insert into luke values
( 4, sysdate,
'Luke Test 4',
systimestamp );
T6
© 2012 – Pythian
DDL Replication
Time
Source
Target
T5
insert into luke values
( 3, sysdate,
'Luke Test 3',
systimestamp );
select a,d from luke;
A D
- ---------------------------2
3 22-NOV-12 09.40.30.622017 AM
1
insert into luke values
( 4, sysdate,
'Luke Test 4',
systimestamp );
T6
select a,d from luke;
A D
- ---------------------------3 22-NOV-12 09.40.30.622017 AM
2
4
1
© 2012 – Pythian
Missing
Value!
DDL Replication
Time
Source
Target
T5
insert into luke values
( 3, sysdate,
'Luke Test 3',
systimestamp );
select a,d from luke;
A D
- ---------------------------2
3 22-NOV-12 09.40.30.622017 AM
1
insert into luke values
( 4, sysdate,
'Luke Test 4',
systimestamp );
T6
Stop extract
Start extract
select a,d from luke;
A D
- ---------------------------3 22-NOV-12 09.40.30.622017 AM
2
4
<<<*** MISSING ***>>>
1
insert into luke values
( 5, sysdate,
'Luke Test 5',
systimestamp );
© 2012 – Pythian
Supplemental Logging
Source
Discard -> Process Abending : 2012-11-22 09:53:07
Source Context :
SourceModule
: [ggstd.conv.endian]
SourceID
:
[/scratch/pradshar/view_storage/pradshar_bugdbrh40_12927937/oggcore/OpenSys/src/gglib/ggstd/lecnv.c]
SourceFunction
: [convCompSQL]
SourceLine
: [530]
ThreadBacktrace
: [9] elements
: [/ggs/replicat(CMessageContext::AddThreadContext()+0x26) [0x5f1e56]]
: [/ggs/replicat(CMessageFactory::CreateMessage(CSourceContext*, unsigned int,
...)+0x7b2) [0x5e88f2]]
: [/ggs/replicat(_MSG_ERR_MAP_COL_INDEX_INVALID(CSourceContext*, DBString<777>
const&, int, int, CMessageFactory::MessageDisposition)+0xa6) [0x5bee16]]
: [/ggs/replicat [0x900e6c]]
: [/ggs/replicat(ggConvRecLE(char*, file_def*, int, char, char)+0x6c) [0x900fac]]
: [/ggs/replicat [0x8f4d6e]]
: [/ggs/replicat(main+0x84b) [0x507a0b]]
: [/lib64/libc.so.6(__libc_start_main+0xf4) [0x34cd81d994]]
: [/ggs/replicat(__gxx_personality_v0+0x1e2) [0x4e3ffa]]
2012-11-22 09:53:07 ERROR
OGG-01161 Bad column index (3)
specified for table FEXLINK.LUKE, max columns = 3.
© 2012 – Pythian
DDL Replication
Time
Source
T7
Restart replicat
Target
select a,d from luke;
A
3
2
4
5
1
D
---------------------------22-NOV-12 09.40.30.622017 AM
22-NOV-12 09.53.02.177904 AM
GoldenGate parameters (Replicat only)
•
DDLOPTIONS UPDATEMETADATA
•
•
Automatically updates GoldenGate metadata cache
© 2012 – Pythian
DDL Replication
•
UPDATEMETADATA doesn’t work both ways
1. After DDL ALTER must bounce REPLICAT
on the local node and PRIMARY EXTRACT
on other nodes
© 2012 – Pythian
Topics
Active/Active considerations
•
•
Supplemental Logging
•
DDL Replication
•
Conflict Avoidance
•
Conflict Detection
•
Release Management
•
Data comparison
General considerations
•
•
Trail file deletion
•
Archive log deletion
© 2012 – Pythian
Conflict Avoidance
2 Columns
•
•
•
LAST_UPDATED_AT (DATE)
•
LAST_UPDATING_BY (VARCHAR2(30))
Trigger
CREATE OR REPLACE TRIGGER <Owner>.<Table Name>_BIU
BEFORE INSERT OR UPDATE
ON <Owner>.<Table Name>
FOR EACH ROW
WHEN ( user != 'GGADMIN' )
DECLARE
l_username VARCHAR2(50);
BEGIN
SELECT NVL(SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER'),USER)
INTO
l_username
FROM
DUAL;
:NEW.last_updated_at
:= SYSDATE;
:NEW.last_updating_user := l_username;
END;
/
© 2012 – Pythian
Conflict Avoidance
Sequences
•
16 digit sequence number
•
Each silo has a unique 2 digit starting number e.g. 21, 31 etc
•
Ability to find originating silo for management reports
•
Guarantee uniqueness
•
Extra maintenance for new sequences
•
Need GG parameter
•
DDL EXCLUDE OBJTYPE SEQUENCE
© 2012 – Pythian
Topics
Active/Active considerations
•
•
Supplemental Logging
•
DDL Replication
•
Conflict Avoidance
•
Conflict Detection
•
Release Management
•
Data comparison
General considerations
•
•
Trail file deletion
•
Archive log deletion
© 2012 – Pythian
Conflict Detection
Discards
Check discard files
•
Use a cron script or other scheduler for every 5 mins
•
Discard reader built in PL/SQL
•
•
PIPELINE function
•
Parses the discard file for common errors
•
Condenses the output to one line with the main error information
•
Check blog for source code
•
http://www.pythian.com/news/37703/read-goldengate-discard-files/
© 2012 – Pythian
Conflict Detection
Discards
Usage of READ_DISCARD
•
•
•
The discard had 567 lines
READ_DISCARD reduced output to 6 lines
select LINE_NUMBER , ORACLE_ERROR
, SOURCE_OBJECT_NAME , ERROR_OPERATION
, ERROR_COLUMN , ERROR_VALUE , PK_TABLE_NAME
from table(read_discard(‘repname’));
LINE_NUMBER
----------1
44
63
263
311
392
ORA_ERROR
--------ORA-02291
ORA-02291
ORA-02291
ORA-01403
ORA-01403
ORA-01403
SOURCE_OBJECT_NAME
-----------------CHILD_TABLE1
CHILD_TABLE2
CHILD_TABLE3
TABLE1
PARENT
CHILD_TABLE3
ERROR_OPERATION
--------------INSERT
INSERT
INSERT
UPDATE
UPDATE
UPDATE
ERROR_COLUMN
----------------PARENT_ID
CHILD1_ID
PARENT_ID
TABLE1_ID
PARENT_ID
CHILD3_ID
6 rows selected.
© 2012 – Pythian
ERROR_VALUE
---------------4100000000360956
6100000000041100
4100000000360956
4100000000562453
4100000000360956
6100000000264611
PK_TABLE_NAME
------------PARENT
CHILD_TABLE1
PARENT
Topics
Active/Active considerations
•
•
Supplemental Logging
•
DDL Replication
•
Conflict Avoidance
•
Conflict Detection
•
Release Management
•
Data comparison
General considerations
•
•
Trail file deletion
•
Archive log deletion
© 2012 – Pythian
Release Management
App
code
PreDDL
DB
change
Major Release script
DDL
DML
PostDDL
PreDDL
PostDDL
DDL
Statements which can not be replicated
OR
We do not want to be replicated
Run these statements outside replication cycle
CREATE SEQUENCE
CREATE/ALTER MATERIALIZED VIEW
CREATE SYNONYM on other user
Due to GG limitations
© 2012 – Pythian
DML
Statements which are replicated
via GoldenGate
Release Management
Challenge – Replication from superset
structure to minor set
Version
N
Version
N+1
ERROR
OGG-01161 Oracle GoldenGate Delivery for Oracle, resb01sa.prm:
Bad column index (nn) specified for table mytab, max columns = 59.
ERROR
OGG-01668
Oracle GoldenGate Delivery for Oracle, resb01sa.prm:
PROCESS ABENDING.
© 2012 – Pythian
Release Management
Drop Column
T
Source
Target
t1
desc mytab
desc mytab
ID
Name
ID
Name
not null number
varchar2(10)
t2
t3
t4
© 2012 – Pythian
not null number
varchar2(10)
Release Management
Drop Column
T
Source
Target
t1
desc mytab
desc mytab
ID
Name
ID
Name
t2
not null number
varchar2(10)
alter table mytab drop column name;
t3
t4
© 2012 – Pythian
not null number
varchar2(10)
Release Management
Drop Column
T
Source
Target
t1
desc mytab
desc mytab
ID
Name
ID
Name
t2
not null number
varchar2(10)
alter table mytab drop column name;
not null number
varchar2(10)
Generate defgen and copy to source
./defgen paramfile ./dirprm/defgen.prm
insert into mytab values(1,’Test01’);
t3
t4
© 2012 – Pythian
Release Management
Drop Column
T
Source
Target
t1
desc mytab
desc mytab
ID
Name
ID
Name
t2
not null number
varchar2(10)
alter table mytab drop column name;
not null number
varchar2(10)
Generate defgen and copy to source
./defgen paramfile ./dirprm/defgen.prm
insert into mytab values(1,’Test01’);
t3
-- ASSUMETARGETDEFS
SOURCEDEFS ./dirsql/targetdefgen.sql
MAP mytab,
COLMAP (
ID=ID
);
TARGET mytab,
start replicat
t4
© 2012 – Pythian
Release Management
Drop Column
T
Source
Target
t1
desc mytab
desc mytab
ID
Name
ID
Name
t2
not null number
varchar2(10)
alter table mytab drop column name;
not null number
varchar2(10)
Generate defgen and copy to source
./defgen paramfile ./dirprm/defgen.prm
insert into mytab values(1,’Test01’);
t3
-- ASSUMETARGETDEFS
SOURCEDEFS ./dirsql/targetdefgen.sql
MAP mytab,
COLMAP (
ID=ID
);
start pump extract
TARGET mytab,
start replicat
t4
© 2012 – Pythian
Release Management
Drop Column
T
Source
Target
t1
desc mytab
desc mytab
ID
Name
ID
Name
t2
not null number
varchar2(10)
alter table mytab drop column name;
not null number
varchar2(10)
Generate defgen and copy to source
./defgen paramfile ./dirprm/defgen.prm
insert into mytab values(1,’Test01’);
t3
-- ASSUMETARGETDEFS
SOURCEDEFS ./dirsql/targetdefgen.sql
MAP mytab,
COLMAP (
ID=ID
);
start pump extract
TARGET mytab,
start replicat
t4
select * from mytab
Select * from mytab
ID
-------1
ID
NAME
---- ----------1
Test01
© 2012 – Pythian
Release Management
Modify Column
T
Source
Target
t1
desc mytab
desc mytab
ID
Name
ID
Name
not null number
varchar2(10)
t2
t3
t4
© 2012 – Pythian
not null number
varchar2(10)
Release Management
Modify Column
T
Source
Target
t1
desc mytab
desc mytab
ID
Name
ID
Name
t2
not null number
varchar2(10)
alter table mytab
modify column name (varchar2(6));
t3
t4
© 2012 – Pythian
not null number
varchar2(10)
Release Management
Modify Column
T
Source
Target
t1
desc mytab
desc mytab
ID
Name
ID
Name
t2
not null number
varchar2(10)
alter table mytab
modify column name (varchar2(6));
not null number
varchar2(10)
Generate defgen and copy to source
./defgen paramfile ./dirprm/defgen.prm
insert into mytab values(1,’Test011’);
t3
t4
© 2012 – Pythian
Release Management
Modify Column
T
Source
Target
t1
desc mytab
desc mytab
ID
Name
ID
Name
t2
not null number
varchar2(10)
alter table mytab
modify column name (varchar2(6));
not null number
varchar2(10)
Generate defgen and copy to source
./defgen paramfile ./dirprm/defgen.prm
insert into mytab values(1,’Test011’);
t3
-- ASSUMETARGETDEFS
SOURCEDEFS ./dirsql/targetdefgen.sql
MAP mytab, TARGET mytab,
COLMAP (
USEDEFAULTS,
NAME=@STREXT(NAME,1,6)
);
start replicat
t4
© 2012 – Pythian
Release Management
Modify Column
T
Source
Target
t1
desc mytab
desc mytab
ID
Name
ID
Name
t2
not null number
varchar2(10)
alter table mytab
modify column name (varchar2(6));
not null number
varchar2(10)
Generate defgen and copy to source
./defgen paramfile ./dirprm/defgen.prm
insert into mytab values(1,’Test011’);
t3
-- ASSUMETARGETDEFS
SOURCEDEFS ./dirsql/targetdefgen.sql
start pump extract
MAP mytab, TARGET mytab,
COLMAP (
USEDEFAULTS,
NAME=@STREXT(NAME,1,6)
);
start replicat
t4
© 2012 – Pythian
Release Management
Modify Column
T
Source
Target
t1
desc mytab
desc mytab
ID
Name
ID
Name
t2
not null number
varchar2(10)
alter table mytab
modify column name (varchar2(6));
not null number
varchar2(10)
Generate defgen and copy to source
./defgen paramfile ./dirprm/defgen.prm
insert into mytab values(1,’Test011’);
t3
-- ASSUMETARGETDEFS
SOURCEDEFS ./dirsql/targetdefgen.sql
start pump extract
MAP mytab, TARGET mytab,
COLMAP (
USEDEFAULTS,
NAME=@STREXT(NAME,1,6)
);
start replicat
t4
Select * from mytab
Select * from mytab
ID
NAME
---- ----------1
Test01
ID
NAME
---- ----------1
Test011
© 2012 – Pythian
Release Management
ASSUMETARGETDEFS
SOURCEDEFS & MAP
Old Version: Create metadata for New
Version DB
( defgen )
New Version: Use SOURCEDEFS & MAP for
pulling data from superset structure
© 2012 – Pythian
Topics
Active/Active considerations
•
•
Supplemental Logging
•
DDL Replication
•
Conflict Avoidance
•
Conflict Detection
•
Release Management
•
Data comparison
General considerations
•
•
Trail file deletion
•
Archive log deletion
© 2012 – Pythian
Data Comparison
User developed scripts
•
Master shell scripts calls PL/SQL package
•
Compares VARCHAR2, NUMBER, DATE and TIMESTAMP
•
Large VARCHAR2, BLOB, CLOB fields are hashed for comparison
•
Requires a database link
Uses LAST_UPDATED_AT timestamps to compare a subset of each table
e.g. SYSDATE – 7
•
Saves the ID of the offending row in a custom table
•
Inconsistencies are handled manually
•
Can be time consuming
•
© 2012 – Pythian
Data Comparison
Veridata
•
Need to set up a server with a database component
•
Each database in the replication cluster needs an agent
•
There can be significant initial configuration for tables to excluded columns,
if necessary.
•
Hashes the entire row for less network traffic
•
All comparison is done on the veridata server
•
Reports are good and clear
•
Need to write an XML parser to create the insert / update / delete
statements
•
May need some clean up operations for report files on the server
© 2012 – Pythian
Topics
Active/Active considerations
•
•
Supplemental Logging
•
DDL Replication
•
Conflict Avoidance
•
Conflict Detection
•
Release Management
•
Data comparison
General considerations
•
•
Trail file deletion
•
Archive log deletion
© 2012 – Pythian
Trail file deletion
PURGEOLDEXTRACTS in mgr.prm
•
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 6
•
•
Problems can occur if trail files have the same name (MOS 1460097.1)
•
Best practice use unique trail file names (MOS 1324890.1)
Use unique trail file names
Use a script to check primary extracts, datapumps and replicats
•
•
Script links Primary Extract -> DataPump(s) -> Replicat
•
Only delete once have been used by the next level
•
•
Used by Replicat if you want to be really safe
Parsing output from -> info <ER Name>, showch
© 2012 – Pythian
Trail file deletion
Info extract, showch output
Read Checkpoint #1
GGS Log Trail
Startup Checkpoint (starting position in the data source):
Sequence #: 8
RBA: 1139
Timestamp: 2012-11-08 18:57:11.595452
Extract Trail: ./dirdat/wb
Current Checkpoint (position of last record read in the data source):
Sequence #: 11
RBA: 1139
Timestamp: 2012-11-12 11:51:02.933110
Extract Trail: ./dirdat/wb
Write Checkpoint #1
GGS Log Trail
Current Checkpoint (current write position):
Sequence #: 9
RBA: 5099
Timestamp: 2012-11-12 12:17:18.995401
Extract Trail: ./dirdat/wb
© 2012 – Pythian
Topics
Active/Active considerations
•
•
Supplemental Logging
•
DDL Replication
•
Conflict Avoidance
•
Conflict Detection
•
Release Management
•
Data comparison
General considerations
•
•
Trail file deletion
•
Archive log deletion
© 2012 – Pythian
Archive Log Deletion
GoldenGate only sends transaction on commit
Need to keep archive logs until transaction completes
Either user a script for archive log deletion
Or since 11.1.1.1 can register the extract (EE only)
•
•
•
•
REGISTER EXTRACT <Extract Name> LOGRETENTION
•
•
Get
•
•
•
DBA_CAPTURE -> OGG2$_EXT01UPL88C29FC8
RMAN-08137: WARNING: archived log not deleted, needed for
standby or upstream capture process
Beware of bugs! (MOS 1487374.1)
© 2012 – Pythian
Lessons Learned
• Supplemental Logging is critical - get it right!
• Structural changes need to be managed carefully
• Parts of the database structure needs special attention
•
Sequences, Materialized Views, Extra columns
• Need to set up monitoring
•
Discards
•
Row comparison
•
GG processes and function
• Trail files need to be cleaned up
• Archive log cleanup must be integrated with GG
© 2012 – Pythian
Thank you and Q&A
To contact us…
[email protected]
1-877-PYTHIAN
To follow us…
http://www.pythian.com/news/
http://www.facebook.com/pages/The-Pythian-Group/163902527671
@pythian
@pythianjobs
http://www.linkedin.com/company/pythian
© 2012 – Pythian