Guenadi N Jilevski's Oracle BLOG

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

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:

  1. Determine the start and end time for the transactions to be backed out
  2. 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.
  3. Use the reverse utility to process the trail
  4. 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.

  1. 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.
  2. If using OGG 11gR2 proceed as follows
  1. 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>

  2. 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>

  3. Create a remote trail

    GGSCI (raclinux1.gj.com) 12> add rmttrail ./dirdat/aa, extract extcap

    RMTTRAIL added.

    GGSCI (raclinux1.gj.com) 13>

  4. 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%)

  5. 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>

  6. 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>

  7. 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]$

  8. 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

  9. Verify that changes are undone.

    SQL> select * from test6.test1;

    no rows selected

    SQL> select * from test6.test1;

    no rows selected

    SQL>

  10. 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. Oracle® GoldenGate Windows and UNIX Administrator’s Guide 11g Release 2 Patch Set 1 (11.2.1.0.1) E29397-01
  2. Oracle® GoldenGate Windows and UNIX Reference Guide 11g Release 2 Patch Set 1 (11.2.1.0.1) E29399-01

November 24, 2012 - Posted by | oracle

1 Comment »

  1. [...] 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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 782 other followers

%d bloggers like this: