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
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.
1. 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;
2. Run the extract from the OS prompts as a batch/special run. Do not use the GGSCI.
3. Run the reverse utility from the OS command line.
4. 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);
5. Run the replicat from the OS command prompt. Do not use GGSCI.
6. 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

November 24, 2012 - Posted by gjilevski | oracle

1 Comment »

[…] online from here or download Using OGG 11gr2 reverse utility to undo changes from OGG […]

Pingback by Using Oracle GoldenGate 11gR2 reverse utility to undo changes from OGG replication – Download « Guenadi N Jilevski's Oracle BLOG | November 24, 2012 | Reply

About

Guenadi Jilevski is a Senior Oracle Professional (OCP 7, 8, 8i, 9i, 10g, 11g, 12c OCE 10gR2 RAC, OCE Oracle RAC 11gR2 and Grid Infrastructure Administrator, Oracle Exadata 11g Certified Implementation Specialist,Oracle GoldenGate 10g/11g Certified Implementation Specialist and OCM 9i, OCM 11g) with more than 20 years of industrial experience in high end Oracle technology starting with Oracle RDBMS version 6 up to 11g. He has been involved with multi platform High Availability Solutions starting with OPS and Veritas and Sun cluster as well as complex multiple node RAC 9i, 10g and 11g implementations on AIX, Linux, Solaris and HP. He has experience in deploying disaster recovery solutions with standby databases compliant to Oracle MAA and third party solutions such as Shareplex and Goldengate. As a former employee of Oracle Corporation Advanced Technology Solutions Consulting Practice in Americas, Guenadi has been involved in multiple visible high end projects for RAC enabling EBS, Peoplesoft, Siebel ERP applications, end to end performance tuning, architectured High Availability and Disaster Recovery solutions to name a few. Presently he is working as A Senior Oracle Consultant in EMEA.

Guenadi Jilevski is Oracle OCP 7, 8, 8i, 9i, 10g, 11g, 12c OCE 10gR2 RAC, OCE Oracle RAC 11gR2 and Grid Infrastructure Administrator, Oracle Exadata 11g Certified Implementation Specialist,Oracle GoldenGate 10g/11g Certified Implementation Specialist and Oracle OCM9i , OCM 11g, Teradata Certified DBA, Informix Certified DBA.

The views expressed on this blog are my own and do not necessarily reflect the views of Current/Past/Future Employers. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect mine.

Archives
Archives
Blog Stats
- 1,077,629 hits
Top Rated
Blogroll
- WordPress.com 0
- WordPress.org 0
Calendar

November 2012

M T W T F S S

« Oct Dec »

1 2 3 4

5 6 7 8 9 10 11

12 13 14 15 16 17 18

19 20 21 22 23 24 25

26 27 28 29 30
Search

Search for:
Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 1,112 other followers
OraNA

Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG