Using Oracle GoldenGate 11gR2 reverse utility to undo changes from OGG replication
Using Oracle GoldenGate 11gR2 reverse utility to undo changes from OGG replication
In the article you will have a look at an example of using the Oracle GoldenGate (OGG) 11gR2 reverse utility to back out changes replicated from the source database to the target database. In certain cases you need to back out the changes on the target database. For example:
- undo replication changes committed on the target database
- rollback an erroneous or accidently issued transaction replicated to the target
- restore the target data to a state before the replication
Although you can use flashback features, in case of a target Oracle database, or simple backup and restore, OGG 11gR2 provides a generic reverse utility that helps you performing the back out. For you to implement a change back out you need to perform the following steps:
- Determine the start and end time for the transactions to be backed out
- Configure and start online extract to capture the changes from the source database for this period including the before images and use a trail format that does not compress deletes.
- Use the reverse utility to process the trail
- Configure and start replicat to apply the change records from the reversed trails and undo the changes that were already applied.
The reverse utility takes care of the following
- Converting inserts into deletes
- Converting delete into inserts
- Using before images for updates instead of after images for updates
- Reversing begin and end transaction indicators
- Reversing the order of the database operation in the OGG trail file.
-
Reversing the trail file numbering. That’s is:
- an become b0
- an-1 becomes b1
- etc….
- a1 becomes bn-1
- a0 becomes bn
- an become b0
- Keeping the same commit timestamps. Reverse utility does not reverse the commit timestamp and time in the trail goes backward.
You can use the approach described in the article to undo changes from an interval of time where either is true
- Both start and end time of the interval are in the past
- Interval start time is in the past and interval end time is at present
The technical solution architecture deploying reverse utility can be summarized as per the illustration from Oracle documentation.
In the article I will use the following tables that are replicated from RACD database used as a source Oracle 11.2.0.3 database to RACDB Oracle 11.2.0.3 database used as a target database.
create table test6.test1(
id number(10) primary key,
c_desc varchar2(60),
c_date date);
create table test6.test2(
id number(10),
c_desc varchar2(60),
c_date date);
The change that will be undone is as follows. Note the begin and start time.
SQL>
ALTER SESSION SET NLS_DATE_FORMAT=’DD-MON-YYYY HH24:MI:SS’;
SQL> select sysdate from dual;
SYSDATE
——————–
20-NOV-2012 12:00:31
SQL>
insert into test6.test1 values ( 1,’Test 1′, sysdate);
insert into test6.test1 values ( 2,’Test 2′, sysdate);
insert into test6.test1 values ( 3,’Test 3′, sysdate);
commit;
insert into test6.test2 values ( 1,’Test 1′, sysdate);
insert into test6.test2 values ( 2,’Test 2′, sysdate);
insert into test6.test2 values ( 3,’Test 3′, sysdate);
commit;
SQL> select * from test6.test1;
ID C_DESC
———- ————————————————————
C_DATE
——————–
1 Test 1
20-NOV-2012 12:20:03
2 Test 2
20-NOV-2012 12:20:03
3 Test 3
20-NOV-2012 12:20:03
SQL> select * from test6.test2;
ID C_DESC
———- ————————————————————
C_DATE
——————–
1 Test 1
20-NOV-2012 12:20:03
2 Test 2
20-NOV-2012 12:20:03
3 Test 3
20-NOV-2012 12:20:03
SQL>
SQL> select sysdate from dual;
SYSDATE
——————–
20-NOV-2012 12:21:15
SQL>
In the article I will use the following interval to back out the changes on RACDB target database.
- Start time: 20-NOV-2012 12:00:31
- End time : 20-NOV-2012 12:21:15
Starting from OGG 11gR2 the batch jobs using SPECIALRUN in extract are not longer supported. I initially started configuring a batch job but I got the following message:
2012-11-20 13:12:10 ERROR OGG-01827 SPECIALRUN task type is no longer supported for Extract (capture).
For those with a OGG prior to 11gR2 willing to use a batch run I will briefly highlight the process. I will describe in greater details the procedure for using an online extract and replicat groups for backing out the changes with reverse utility. In pre 11gR2 OGG there are two way to get the job done
- Batch job for extract and replicat
- Online extract and replicat
In OGG 11gR2 the batch approach is de-supported and online groups for extract and replicat are the only way to get it done.
-
If using pre 11gR2 OGG and willing to use a batch job proceed as follows.
-
Create a parameter file for the extract
specialrun, tranlog
begin 2012-11-20 12:00:31
end 2012-11-20 12:21:15
userid ogg_extrcat@racd, password ogg_extrcat
rmthost raclinux1, mgrport 7805
rmtfile ./dirdat/test6.dat
table test6.test1, keycols(id);
table test6.test2;
- Run the extract from the OS prompts as a batch/special run. Do not use the GGSCI.
- Run the reverse utility from the OS command line.
-
Prepare the replicat parameter file
specialrun
begin 2012-11-20 12:00:31
end 2012-11-20 12:21:15
userid ogg_replicat@racdb, password ogg_Replicat
assumetargetdefs
extfile ./dirdat/test6_rev.dat
discardfile ./dirrpt/test6.dsc, megabytes 4, purge
map test6.test1, target test6.test1;
map test6.test2, target test6.test2, keycols(id);
- Run the replicat from the OS command prompt. Do not use GGSCI.
- Examine and verify that you meet your objectives.
-
- If using OGG 11gR2 proceed as follows
-
Create an online extract parameter file. Take a note at the end time specifying the end of the time interval.
GGSCI (raclinux1.gj.com) 2> view params extcap
extract extcap
userid ogg_extract@racd, password ogg_extract
tranlogoptions asmuser sys@ASM, asmpassword sys1
end 2012-11-20 12:21:15
nocompressdeletes
getupdatebefores
rmthost raclinux1, mgrport 7805
rmttrail ./dirdat/aa
table test6.test1, keycols(id);
table test6.test2;
GGSCI (raclinux1.gj.com) 3>
-
Create an online extract using the following command shown below. Take a note at the begin time indicating the start time of the time interval for which changes will be captured. I registered the extract so that I can have retention of the archive logs required for recovery in case of failure of the change data capture.
GGSCI (raclinux1.gj.com) 9> add extract extcap, tranlog, threads 2, begin 2012-11-20 12:00:31
EXTRACT added.
GGSCI (raclinux1.gj.com) 10>
GGSCI (raclinux1.gj.com) 11> register extract extcap, logretention
2012-11-20 14:12:48 INFO OGG-01749 Successfully registered EXTRACT EXTCAP to start managing log retention at SCN 7178115.
GGSCI (raclinux1.gj.com) 12>
-
Create a remote trail
GGSCI (raclinux1.gj.com) 12> add rmttrail ./dirdat/aa, extract extcap
RMTTRAIL added.
GGSCI (raclinux1.gj.com) 13>
-
Start extract to capture changes. After reaching the end time extract stops. The report will confirm that the insert operations are captured and the insert operations are recorded in the trail.
***********************************************************************
** Running with the following parameters **
***********************************************************************
2012-11-20 18:44:33 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
extract extcap
userid ogg_extract@racd, password ***********
2012-11-20 18:44:33 INFO OGG-03500 WARNING: NLS_LANG environment variable does not match database character set, or not set. Using da
tabase character set value of AL32UTF8.
tranlogoptions asmuser sys@ASM, asmpassword ****
end 2012-11-20 12:21:15
nocompressdeletes
getupdatebefores
rmthost raclinux1, mgrport 7805
rmttrail ./dirdat/aa
table test6.test1, keycols(id);
table test6.test2;
2012-11-20 18:44:34 INFO OGG-01635 BOUNDED RECOVERY: reset to initial or altered checkpoint.
2012-11-20 18:44:34 INFO OGG-01815 Virtual Memory Facilities for: BR
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/u02/stage_ogg11203_ora11/BR/EXTCAP.
Bounded Recovery Parameter:
Options = BRRESET
BRINTERVAL = 4HOURS
BRDIR = /u02/stage_ogg11203_ora11
2012-11-20 18:44:35 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/u02/stage_ogg11203_ora11/dirtmp.
CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE: 64G
CACHEPAGEOUTSIZE (normal): 8M
PROCESS VM AVAIL FROM OS (min): 128G
CACHESIZEMAX (strict force to disk): 96G
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production
Database Language and Character Set:
NLS_LANG = “.AL32UTF8”
NLS_LANGUAGE = “AMERICAN”
NLS_TERRITORY = “AMERICA”
NLS_CHARACTERSET = “AL32UTF8”
Maximum supported ASM read buffer size is 28 KB
Maximum supported ASM read buffer size is 28 KB
2012-11-20 18:44:36 WARNING OGG-01423 No valid default archive log destination directory found for thread 2.
2012-11-20 18:44:36 INFO OGG-00546 Default thread stack size: 2097152.
2012-11-20 18:44:36 INFO OGG-01515 Positioning to begin time Nov 20, 2012 12:00:31 PM.
2012-11-20 18:44:37 INFO OGG-01516 Positioned to (Thread 1) Sequence 587, RBA 1310736, SCN 0.0, Nov 20, 2012 12:00:31 PM.
2012-11-20 18:44:37 INFO OGG-01515 Positioning to begin time Nov 20, 2012 12:00:31 PM.
2012-11-20 18:44:39 INFO OGG-01516 Positioned to (Thread 2) Sequence 166, RBA 1040, SCN 0.0, Nov 20, 2012 12:00:31 PM.
2012-11-20 18:44:39 INFO OGG-01517 Position of first record processed for Thread 1, Sequence 587, RBA 1310736, SCN 0.7163472, Nov 20,
2012 12:00:32 PM.
2012-11-20 18:44:39 INFO OGG-00732 Found crash recovery marker from thread #2 on sequence 166 at RBA 1040. Aborting uncommitted trans
actions.
2012-11-20 18:44:39 INFO OGG-01517 Position of first record processed for Thread 2, Sequence 166, RBA 1040, SCN 0.7259453, Nov 20, 20
12 4:52:03 PM.
2012-11-20 18:44:42 INFO OGG-00732 Found crash recovery marker from thread #2 on sequence 168 at RBA 1040. Aborting uncommitted trans
actions.
2012-11-20 18:44:44 INFO OGG-00732 Found crash recovery marker from thread #1 on sequence 589 at RBA 1040. Aborting uncommitted trans
actions.
2012-11-20 18:44:49 INFO OGG-01226 Socket buffer size set to 27985 (flush size 27985).
2012-11-20 18:44:50 INFO OGG-01055 Recovery initialization completed for target file ./dirdat/aa000000, at RBA 1047.
2012-11-20 18:44:50 INFO OGG-01478 Output file ./dirdat/aa is using format RELEASE 11.2.
2012-11-20 18:44:50 INFO OGG-01026 Rolling over remote file ./dirdat/aa000001.
2012-11-20 18:44:50 INFO OGG-01053 Recovery completed for target file ./dirdat/aa000001, at RBA 1047.
2012-11-20 18:44:50 INFO OGG-01057 Recovery completed for all targets.
***********************************************************************
** Run Time Messages **
***********************************************************************
TABLE resolved (entry test6.test1):
table “TEST6”.”TEST1″, keycols(id);
Using the following key columns for source table TEST6.TEST1: ID.
TABLE resolved (entry test6.test2):
table “TEST6”.”TEST2″;
2012-11-20 18:45:15 INFO OGG-00732 Found crash recovery marker from thread #1 on sequence 603 at RBA 1040. Aborting uncommitted trans
actions.
2012-11-20 18:45:17 INFO OGG-00732 Found crash recovery marker from thread #1 on sequence 607 at RBA 1040. Aborting uncommitted trans
actions.
2012-11-20 18:45:18 WARNING OGG-00869 No unique key is defined for table ‘TEST2’. All viable columns will be used to represent the key,
but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Using the following key columns for source table TEST6.TEST2: ID, C_DESC, C_DATE.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Report at 2012-11-20 18:45:20 (activity since 2012-11-20 18:44:58)
Output to ./dirdat/aa:
From Table TEST6.TEST1:
# inserts: 3
# updates: 0
# deletes: 0
# discards: 0
From Table TEST6.TEST2:
# inserts: 3
# updates: 0
# deletes: 0
# discards: 0
REDO Log Statistics
Read ahead buffers 7
Read ahead buffer size 1024000
Read ahead for current log on
Bytes read 75660800
Bytes read ahead 74039296
Bytes unused 86016
Bytes parsed 75592192
Bytes queued 40912
Bytes output 966
— Write Operations —————— — Read Operations ——————-
Queue Name Size Count Waited Signaled Count Waited Signaled
———————— —— ————————————– ————————————–
–More–(28%)
-
Create online replicat parameter file
GGSCI (raclinux1.gj.com) 3> view params repcap
replicat repcap
–end 2012-11-20 12:21:15
end runtime
userid ogg_replicat@racdb, password ogg_replicat
assumetargetdefs
discardfile ./dirrpt/repcat.dsc, megabytes 4, purge
map test6.test1, target test6.test1;
map test6.test2, target test6.test2, keycols(id);
GGSCI (raclinux1.gj.com) 4>
-
Create an online replicat. Take a note that there is no ./dirdat/bb trail. This trail will be created by the reverse utility. It is a dummy trail file that does not correspond to a trail created by add rmttrail or add exttrail commands.
GGSCI (raclinux1.gj.com) 143> add replicat repcap, exttrail ./dirdat/bb
REPLICAT added.
GGSCI (raclinux1.gj.com) 144>
-
Reverse the captured changes by using the following command.
[oracle@raclinux1 dirdat]$ ls
aa000000 aa000001
[oracle@raclinux1 dirdat]$
[oracle@raclinux1 dirdat]$ /u02/stage_ogg11203_ora11/reverse /u02/stage_ogg11203_ora11/dirdat/aa*,/u02/stage_ogg11203_ora11/dirdat/bb*
Oracle GoldenGate Dynamic Rollback
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258
Linux, x64, 64bit (optimized) on Aug 23 2012 16:50:50
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Reversed /u02/stage_ogg11203_ora11/dirdat/aa000000 to /u02/stage_ogg11203_ora11/dirdat/bb000001
Reversed /u02/stage_ogg11203_ora11/dirdat/aa000001 to /u02/stage_ogg11203_ora11/dirdat/bb000000
Total Data Bytes 318
Avg Bytes/Record 45
Insert 6
Restart OK 1
After Images 7
[oracle@raclinux1 dirdat]$
-
Start the replicat. Examine the report file to confirm that the records from the trail are applied to the target database. Note that replicat applies deletes instead of inserts.
GGSCI (raclinux1.gj.com) 153> start replicat repcap
Sending START request to MANAGER …
REPLICAT REPCAP starting
GGSCI (raclinux1.gj.com) 154>
***********************************************************************
** Running with the following parameters **
***********************************************************************
2012-11-20 20:21:23 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
replicat repcap
end runtime
userid ogg_replicat@racdb, password ************
2012-11-20 20:21:23 INFO OGG-03501 WARNING: NLS_LANG environment variable is invalid or not set. Using operating system character set
value of AL32UTF8.
assumetargetdefs
discardfile ./dirrpt/repcat.dsc, megabytes 4, purge
map test6.test1, target test6.test1;
map test6.test2, target test6.test2, keycols(id);
2012-11-20 20:21:27 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/u02/stage_ogg11203_ora11/dirtmp.
CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE: 2G
CACHEPAGEOUTSIZE (normal): 8M
PROCESS VM AVAIL FROM OS (min): 4G
CACHESIZEMAX (strict force to disk): 3.41G
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production
Database Language and Character Set:
NLS_LANG = “.AL32UTF8”
NLS_LANGUAGE = “AMERICAN”
NLS_TERRITORY = “AMERICA”
NLS_CHARACTERSET = “AL32UTF8”
***********************************************************************
** Run Time Messages **
***********************************************************************
Opened trail file ./dirdat/bb000000 at 2012-11-20 20:21:28
MAP resolved (entry test6.test2):
map “TEST6”.”TEST2″, target test6.test2, keycols(id);
2012-11-20 20:21:45 WARNING OGG-00869 No unique key is defined for table ‘TEST2’. All viable columns will be used to represent the key,
but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Using following columns in default map by name:
ID, C_DESC, C_DATE
Using the following key columns for target table TEST6.TEST2: ID.
MAP resolved (entry test6.test1):
map “TEST6”.”TEST1″, target test6.test1;
Using following columns in default map by name:
ID, C_DESC, C_DATE
Using the following key columns for target table TEST6.TEST1: ID.
Switching to next trail file ./dirdat/bb000001 at 2012-11-20 20:21:47 due to EOF, with current RBA 2013
Opened trail file ./dirdat/bb000001 at 2012-11-20 20:21:47
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Last record for the last committed transaction is the following:
___________________________________________________________________
Trail name : ./dirdat/bb000000
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 53 (x0035) IO Time : 2012-11-20 12:20:03.000235
IOType : 3 (x03) OrigNode : 255 (xff)
TransInd : . (x02) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 587 AuditPos : 4864528
Continued : N (x00) RecCount : 1 (x01)
2012-11-20 12:20:03.000235 Delete Len 53 RBA 1785
Name: TEST6.TEST1
___________________________________________________________________
Reading ./dirdat/bb000001, current RBA 1047, 6 records
Report at 2012-11-20 20:21:48 (activity since 2012-11-20 20:21:46)
From Table TEST6.TEST2 to TEST6.TEST2:
# inserts: 0
# updates: 0
# deletes: 3
# discards: 0
From Table TEST6.TEST1 to TEST6.TEST1:
# inserts: 0
# updates: 0
# deletes: 3
# discards: 0
Last log location read:
FILE: ./dirdat/bb000001
SEQNO: 1
RBA: 1047
TIMESTAMP: 2012-11-20 18:44:49.949556
EOF: NO
READERR: 400
CACHE OBJECT MANAGER statistics
-
Verify that changes are undone.
SQL> select * from test6.test1;
no rows selected
SQL> select * from test6.test1;
no rows selected
SQL>
- You have successfully backed out transactions marked by the start and end time.
Summary
In the article you had a look at approach to back out transactions using the OGG 11gR2 reverse utility. You started by determining the start and end time of the changes to be captured and backed out. After that you created an online extract to capture the changes corresponding to that time interval. For the reverse utility to successfully back out the changes the before images of all columns must be captured. The records corresponding to transaction operations must be written in non-compressed format. After the capture completed successfully the reverse utility was executed. The replicat was created using the reversed trail file and started to apply the changes from the reversed trail. The verification confirmed that the inserts were applied as deletes, that is backed out the change.
Reference
1 Comment »
Leave a Reply
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
[…] online from here or download Using OGG 11gr2 reverse utility to undo changes from OGG […]