Changed data recovery
It's all very well replicating data in real-time between a source and target database, but what if something disastrous happens to the source data like a user drops a table, deletes important records, or the data becomes corrupt? The answer may be to build a delay into the Changed Data Delivery (CDD), but this seems to defeat the object of fast data replication. What can be done to manage the changed data, ensuring that only the valid transactions succeed?
There are a number of solutions to this problem, none of which prevent user error or data corruption. Let's take a look at some of these options that are provided by the Oracle database.
Point-in-time recovery (PITR)
Since Oracle 10g, the database provides Flashback technology, allowing the database, a table, or even a transaction to be flashed back to a given SCN or timestamp. The Flashback technology provides a fast, reliable recovery mechanism over the traditional method of performing a point-in-time recovery.
Oracle Recovery Manager (RMAN)
RMAN supports PITR. However, the database would have to be mounted and not open, preventing users from connecting. Furthermore, the database would need to be restored from a backup and then recovered to a specified SCN or timestamp. All this takes time and is unacceptable, particularly with the database offline!
Flashback
A far quicker recovery method is the Oracle 12c Flashback technology. Here, a dropped table can be recovered instantaneously from the Recycle Bin by executing just one command and with the database open. It is a similar story for inpidual transactions too. These can be backed out using the information provided from a Flashback Transaction Query. The result of adopting these methods would also generate redo, which the GoldenGate Extract process would then write to the trail files for onward replication. Therefore, no action is required on the target database.
Should you wish to flashback the whole database to a point in time before the error, Flashback would need to be enabled at database level. This operation causes the database to generate Flashback logs in addition to its redo logs, all of which are written to the Fast Recovery Area (FRA). Note, however, that your Oracle 12c Multitenant Container Database (CDB) configured with more than one Pluggable Database (PDB) supports flashback at the container (root) level only, thus affecting all PDBs. To flashback a PDB, you must first offline the PDB datafiles, flashback the CDB, and then perform conventional restore and PITR operations on the PDB.
Tip
Note: The multitenant database feature is an additional cost option to the Oracle Database 12c Enterprise Edition license.
Using a nonmultitenant database as an example, to recover from data loss or corruption in a GoldenGate environment, it is important to perform the flashback on both source and target databases. This is, however, an offline operation. The Oracle GoldenGate 12c Veridata product can be used to perform both data comparison and synchronization at the column level, following data recovery.
To guard against human error, the Flashback technology appears to provide the most appropriate solution, but what does this mean to GoldenGate? Simply alter your Extract process to start replicating data from the specified timestamp.
For a bit of insurance and peace of mind, it's worth enabling Flashback on your mission-critical source and target databases, making sure you factor in the additional storage requirements for the FRA in your design.
Flashback can be enabled at database level using the following command executed as SYSDBA:
SQL> ALTER DATABASE FLASHBACK ON;
Following a replication failure, the ability to clean-up the replicated data and synchronize the target and source databases is greatly improved. For one, the apply process, when configured in the integrated mode, no longer replicates transactions that have already been applied. Coupled with the Dynamic Rollback feature, the flashback of the target database may not be entirely necessary, depending on the failure scenario or the extent of data corruption. We discuss Dynamic Rollback in detail in Chapter 10, Troubleshooting GoldenGate.
SAN Snapshot
SAN Snapshots provide an alternative solution to PITR. Typically, snaps are scheduled every 30 minutes, capturing a snapshot of the data that will provide a restore point. The beauty of the SAN Snapshot is its ability to snap all file types as an online operation; database files, raw files, filesystems, and so on, which lends itself perfectly to a GoldenGate environment. You no longer need to concern yourself with winding back your database and Extract process(es) to a point in time; simply restore the snap and wind forward to a point just before the error by applying the database archived logs. The GoldenGate trail and checkpoint files will remain in synchronization with the database as if nothing ever happened. The only additional task is to replay any legitimate transactions that occurred after the error or corruption.