Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Dynamic Rollback

Sometimes, transactions are performed on the source system in error. Ideally, you would not want them replicated to the target database. So, what can be done?

We can't feasibly shut down our production database and perform a point-in-time recovery, nor can we use flashback on a specific set of transactions. We could set up Event Actions to detect and ignore erroneous transactions, such as DELETE operations without a WHERE clause, but it is impossible to trap every eventuality. Fortunately, since GoldenGate 11g Release 2, a reverse data processing utility is bundled with the software that can back out transactions (once identified) based on the period of time in which they occurred.

The reverse utility is typically employed to undo accidental deletes that have occurred on a source table. Let's see how it works.

Creating process groups and trails for reverse processing

Before we can use the reverse utility, we must create dedicated process groups for input and output trail files. Then, we can simulate an accidental deletion of all the rows in the SRC.EMP table in our Oracle 12c container database. In a production system, it may be a good idea to have the reverse process groups configured (but STOPPED) if a dynamic rollback operation is required.

First, create a dedicated integrated Extract to support the rollback operation in the source environment. For Oracle 11g Release 2 databases, a classic Extract may be used. The END timestamp can be adjusted later to reflect the time just before the data deletion, as shown in the following command:
```
GGSCI (db12c) 2> EDIT PARAMS EREVERSE
```
```
EXTRACT EREVERSE
USERIDALIAS srcdb DOMAIN admin
NOCOMPRESSDELETES
GETUPDATEBEFORES
END 2015-05-20 19:30:00
RMTHOST db12server02, MGRPORT 7809
RMTTRAIL ./dirdat/rv
TABLE PDB1.SRC.EMP;
```

Then, log in to the root container database from GGSCI and add the EREVERSE Extract group:

GGSCI (db12c) 3> dblogin USERIDALIAS srcdb DOMAIN admin
Successfully logged into database CDB$ROOT.
GGSCI (db12c as C##GGADMIN@orcl/CDB$ROOT) 3> ADD EXTRACT EREVERSE, INTEGRATED TRANLOG, BEGIN 2015-05-20 19:35:58
EXTRACT added.

Now, add the remote trail for the Extract as follows:

GGSCI (db12c as C##GGADMIN@orcl/CDB$ROOT) 4> ADD RMTTRAIL ./dirdat/rv, EXTRACT EREVERSE
RMTTRAIL added.

Then, register the integrated extract with the container database, as shown in the following command:

GGSCI (db12c as C##GGADMIN@orcl/CDB$ROOT) 5> REGISTER EXTRACT EREVERSE DATABASE CONTAINER (PDB1)
Extract EREVERSE successfully registered with database at SCN 2488975.

Let's now delete some data. Log on to the PDB1 source database and delete all the rows from SRC.EMP(take note of the time before executing the DELETE):
```
SQL> !date
Wed May 20 19:35:58 CST 2015
SQL> DELETE FROM SRC.EMP;
14 rows deleted.

SQL> commit;
Commit complete.
```

Again, take a note of the time:

SQL> !date
Wed May 20 19:37:06 CST 2015
SQL> exit

From the GGSCI prompt, alter the begin time of the EREVERSE Extract time to reflect the timestamp capture in step 5:
```
GGSCI (db12c) 1> ALTER EXTRACT EREVERSE, BEGIN 2015-05-20 19:35:58
EXTRACT altered.
```

Then, edit the EREVERSE parameter file and include the timestamp captured in step 6, as shown in the following code:

GGSCI (db12c) 1> EDIT PARAMS EREVERSE

EXTRACT EREVERSE
USERIDALIAS srcdb DOMAIN admin
NOCOMPRESSDELETES
GETUPDATEBEFORES
END 2015-05-20 19:37:06
RMTHOST db12server02, MGRPORT 7809
RMTTRAIL ./dirdat/rv
TABLE PDB1.SRC.EMP;

Now, stop all the Extract and data pump processes in the source environment:

GGSCI (db12c) 2> STOP EXTRACT *
Sending STOP request to EXTRACT EOLTP01 ...
Request processed.

Sending STOP request to EXTRACT EPMP01 ...
Request processed.

Then, start the EREVERSE Extract process, which will only extract the records for the timestamps noted in steps 5 and 6:
```
GGSCI (db12c) 3> START EREVERSE

Sending START request to MANAGER ...
EXTRACT EREVERSE starting
```
When the Extract process automatically stops, check whether the trail files exist on our target system, as shown in the following command:
```
[oracle@db12c ogg]$ ls -l ./dirdat/rv*
-rw-r-----. 1 oracle oracle 1436 May 20 19:43 ./dirdat/rv000000
```

Then, execute the reverse utility and pass the preceding rv000000 file as input and the tv000000 file as output arguments:

[oracle@db12c ogg]$ ./reverse ./dirdat/rv000000 ./dirdat/tv000000

Oracle GoldenGate Dynamic Rollback
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1
Linux, x64, 64bit (optimized) on Aug  7 2014 03:07:27

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

*Warning* Source file contained Deletes which maybe compressed
Reversed ./dirdat/rv000000 to /u01/app/oracle/product/12.1.2/ogg_src/dirdat/tv000000
Total Data Bytes              1720
  Avg Bytes/Record             114
Delete                          14
Restart OK                       1
Before Images                   14
After Images                     1

Now, we will create a Replicat process to apply the records generated in the ./dirdat/tv000000 trail file. Note the use of the RUNTIME parameter that will only process data up to the current timestamp. Incidentally, no other change records exist in the trail file apart from those generated by the reverse utility:
```
GGSCI (db12c) 1> EDIT PARAMS RREVERSE
```
```
REPLICAT RREVERSE
USERID ggadmin@PDB2, PASSWORD oracle_123
END RUNTIME
DISCARDFILE ./dirrpt/RREVERSE.dsc, APPEND, MEGABYTES 100
ASSUMETARGETDEFS
MAP PDB1.SRC.EMP, TARGET PDB2.TGT.EMP;
```
Then, add the Replicat RREVERSE in classic mode, including the BEGIN timestamp from step 5, along with CHECKPOINTTABLE. Make sure that the checkpoint table exists in your target database before executing the following command (we cannot use Integrated Replicat here because it does not support the trail files of SOURCEISTABLE):
```
GGSCI (db12c) 2> ADD REPLICAT RREVERSE, EXTTRAIL ./dirdat/tv, BEGIN 2015-05-20 19:35:58, CHECKPOINTTABLE ggadmin.ggcheckpoint
REPLICAT added.
```

Start the Replicat to apply the changes generated by the reverse utility, as shown in the following command:

GGSCI (db12c) 3> START REPLICAT RREVERSE

Sending START request to MANAGER ...
REPLICAT RREVERSE starting

When the Replicat stops automatically, verify that the changes have been reversed in the target database by viewing the Replicat process report file and querying the target table TGT.EMP:

GGSCI (db12c) 4> VIEW REPORT RREVERSE
..
Report at 2015-05-20 19:46:47 (activity since 2015-05-20 14:48:37)

From Table PDB1.SRC.EMP to PDB2.TGT.EMP:
       #                   inserts:        14
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0

If the reverse data processing produces unexpected or undesired results, you can reapply all the original changes to the database by swapping the input file with the utility's output file and then run the reverse Replicat again.

When your target table is back as it was before the DELETE operation, the source table will need to be synchronized. This can be achieved through a simple table export/import with Oracle's data pump utility (ensuring that replication is stopped) or by using GoldenGate Veridata 12c, should your organization be fortunate enough to have this excellent tool installed.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for Dynamic Rollback

Create new playlist

Sign In

Sign Up

Dynamic Rollback

Creating process groups and trails for reverse processing

Table of Contents for
Dynamic Rollback