Undoing the applied changes using the reverse utility
Part of the job of setting a GoldenGate replication consists of defining what to replicate. Once theserules are in place, GoldenGate processes scan the redo data for any records for the replicated tables. Most of the time, these changes are intended and generated by some application. However, sometimes some data updates are performed in the source environment by mistake and you would ideally not want them to be replicated to the target environment. In such scenarios, the administrator might want to roll back the changes performed. You can perform such tasks using the latest database technologies, for example, flashback. However, it is very difficult to roll back only a set of transactions from the database especially when you have applied complex filtering in the GoldenGate configuration. In such cases, you need another approach which will reverse the changes that were applied using GoldenGate. GoldenGate provides a reverse utility using which you can undo the changes in the target database. In this recipe we will go through how to configure and use this utility. We will do this by following a simple scenario in which a few statements are applied to the target database and then reversed.
For this recipe we will refer to the setup done in Setting up a simple GoldenGate replication configuration between two single node databases in Chapter 2, Setting up GoldenGate Replication. We will perform a few simple
INSERT/UPDATE/DELETE operations on some tables owned by the SCOTT user. Once these changes are applied to the target database, we will verify them and then reverse those using the reverse utility.
In order to demonstrate reversing the changes applied by GoldenGate, let's first perform some changes in the source environment:
- List the records in the
EMPandDEPTtable in the target database:SQL> SELECT * FROM EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---- 7934 MILLER CLERK 7782 23-JAN-82 1300 10 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7839 KING PRESIDENT 17-NOV-81 5000 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7369 SMITH CLERK 7902 17-DEC-80 800 20 14 rows selected. SQL> SELECT * FROM DEPT; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Run the following statements in the source database SQL> SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') FROM DUAL; TO_CHAR(SYSDATE,'DD-MON-YYYYH ----------------------------- 01-MAY-2013 21:22:36 SQL> DELETE EMP; 14 rows deleted. SQL> INSERT INTO SCOTT.EMP VALUES (8800,'ROGER','ANALYST',7934,'23-DEC-1972',2300,0,10); 1 row created. SQL> INSERT INTO DEPT VALUES (50,'MARKETING','SAN JOSE'); 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') FROM DUAL; TO_CHAR(SYSDATE,'DD-MON-YYYYH ----------------------------- 01-MAY-2013 21:23:52
- Verify the current data in the
EMPandDEPTtable in the target database:SQL> SELECT * FROM EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ------- --- ---- -------- ---- ---- ------ 8800 ROGER ANALYST 7934 23-DEC-72 2300 0 10 SQL> SELECT * FROM DEPT; DEPTNO DNAME LOC ------ ---------- -------------- 50 MARKETING SAN JOSE 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
Now, we will run through the following steps to reverse the preceding changes:
- Stop the Extract and Datapump processes in the source environment:
GGSCI (prim1-ol6-112.localdomain) 1> STOP EXTRACT * Sending STOP request to EXTRACT EGGTEST1 ... Request processed. Sending STOP request to EXTRACT PGGTEST1 ... Request processed.
- Stop the Replicat process in the target environment:
GGSCI (stdby1-ol6-112.localdomain) 4> STOP RGGTEST1 Sending STOP request to REPLICAT RGGTEST1 ... Request processed.
- Set up an Extract in the source environment just to read the records between the timestamps that we noted in step 1:
GGSCI (prim1-ol6-112.localdomain) 1> EDIT PARAMS EGGREVERSE EXTRACT EGGREVERSE USERID GGATE_ADMIN@DBORATEST, PASSWORD GGATE_ADMIN NOCOMPRESSDELETES GETUPDATEBEFORES END 2013-05-01 21:23:52 RMTHOST stdby-ol6-112 , MGRPORT 8809 RMTTRAIL /u01/app/ggate/dirdat/reverse/rt TABLE SCOTT.*;
- Add the Extract to the source GoldenGate config:
GGSCI (prim1-ol6-112.localdomain) 1> ADD EXTRACT EGGREV, TRANLOG, BEGIN 2013-05-01 21:22:36 EXTRACT added.
- Add the remote trail for the Extract:
GGSCI (prim1-ol6-112.localdomain) 2> ADD RMTTRAIL /u01/app/ggate/dirdat/reverse/rt, EXTRACT EGGREV RMTTRAIL added.
- Start the Extract process, it will only extract the records for the timestamps noted in step 1:
GGSCI (prim1-ol6-112.localdomain) 3> start eggrev Sending START request to MANAGER ... EXTRACT EGGREV starting
- The Extract process in step 8, created a trail file on the target system.
- We will use the reverse utility to generate a trail file with reverse records:
[ggate@stdby1-ol6-112 ggate]$ ./reverse ./dirdat/reverse/rt000000 ./dirdat/reverse/st000000 Oracle GoldenGate Dynamic Rollback Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized) on Apr 23 2012 04:59:01 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. *Warning* Source file contained Deletes which maybe compressed Reversed ./dirdat/reverse/rt000000 to /u01/app/ggate/dirdat/reverse/st000000 Total Data Bytes 1890 Avg Bytes/Record 118 Delete 14 Insert 2 Before Images 14 After Images 2
- Now we will create a Replicat process to apply the records generated in the
./dirdat/reverse/st000000trail file:GGSCI (stdby1-ol6-112.localdomain) 1> EDIT PARAMS RGGREV REPLICAT RGGREV USERID GGATE_ADMIN@TGORTEST, PASSWORD GGATE_ADMIN END 2013-05-01 21:23:52 DISCARDFILE /u01/app/ggate/dirrpt/RGGREV.dsc,append,MEGABYTES 500 ASSUMETARGETDEFS MAP SCOTT.*, TARGET SCOTT.*;
- Add the replicat to the target GoldenGate configuration:
GGSCI (stdby1-ol6-112.localdomain) 3> ADD REPLICAT RGGREV, EXTTRAIL ./dirdat/reverse/st, CHECKPOINTTABLE GGATE_ADMIN.CHECKPOINT REPLICAT added.
- Start the replicat to apply the changes generated by the reverse utility:
GGSCI (stdby1-ol6-112.localdomain) 4> START REPLICAT RGGREV Sending START request to MANAGER ... REPLICAT RGGREV starting
- Verify that the changes have been reversed in the target database and the data is as it was before the changes made in step 1:
SQL> SELECT * FROM EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---- 7934 MILLER CLERK 7782 23-JAN-82 1300 10 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7839 KING PRESIDENT 17-NOV-81 5000 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7369 SMITH CLERK 7902 17-DEC-80 800 20 14 rows selected. SQL> SELECT * FROM DEPT; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
The reverse utility is quite useful to roll back accidental changes that might have happened in an environment. You would need to determine a time slot during which the unwanted changes happened. Once you have determined the timeslot you can use GoldenGate to extract the changes occurred, reverse them, and apply the deltas to bring the data back to the state where it was.
In this recipe we first verify the current state of the data in the source and the target database, and then make some data modifications. We also capture the timestamps before and after making these changes. We then stop the current GoldenGate processes and create a new extract called
EGGREVspecifically to extract the changes for that timeslot. This extract also transfers the data to the remote trail file. So once the extract has stopped, we get a new trail file in the target system. This trail file is then fed into the reverse utility in step 8. The reverse utility reads this trail file and generates opposite records. These records are written to a new trail file. In steps 9 and 10, we create a new Replicat process called RGGREV using which the changes in this trail file are applied to the target database. Once the Replicat process has stopped we verify the data in the target database.
The reverse utility reverses the operations by:
- Converting INSERTs to DELETEs
- Converting DELETEs to INSERTs
- Running UPDATEs with old values
- Reversing the sequence in which the statements were run
In order to enable the reverse utility to complete the preceding operations successfully, it is very crucial that you run the Extract process for capturing changes for reversing operations with the
NOCOMPRESSDELETES and GETUPDATEBEFORES options.
There are a few data types for which reversing is not supported in Oracle. This is because GoldenGate does not generate/capture the before images of these data types. The unsupported data types for the reverse utility are:
- CLOB
- BLOB
- NCLOB
- LONG
- LONG RAW
- XMLType
- UDT
- Nested Tables
- VARRAY
No comments:
Post a Comment