Recovery Journal

The Teradata database uses Recovery Journal to automatically maintain data integrity in the case of :

• An interrupted transaction
• An AMP failure

Recovery Journals are created, maintained and purged by the system automatically, so no DBA intervention is required. Recovery journal are tables stored in the storage medium so they take up the disk space on the system.

There are three types of Recovery Journal in Teradata-

1. Transient Journal
2. Down – AMP Recovery Journal
3. Permanent Journal

Now we look on each of the recovery journal in details –

Transient Journal

A transient journal maintains data integrity when in-flight transactions are interrupted. Data is returned to its original state after transaction failure.

A transient journal is used during normal system operation to keep “before images” of changed rows so the data can be restored to its previous state if the transaction is not completed. This happens on each AMP as changes occur. When a transaction is started, the system automatically stores a copy of all the rows affected by the transaction in the transient journal until the transaction is completed. Once the transaction is completed the “before images” are purged.

In the event of transaction failure, the “before images” are reapplied to the affected tables and deleted from the journal, and the “rollback” operation is completed.

Down AMP Recovery Journal

The down AMP recovery journal allows continued system operation while an AMP is down. A down AMP recovery journal is used with fallback protected tables to maintain a record of write transactions (updates, creates, inserts, deletes, etc) on the failed AMP while it is unavailable.

The Down AMP recovery journal starts automatically after the loss of an AMP in a cluster. Any changes to the data in the failed AMP are logged into the Down AMP recovery journal by the other AMPs in the cluster. When the failed AMP is brought back online, the restart process includes applying the changes in the Down – AMP recovery journal to the recovered AMP.

The journal is discarded once the process is complete, and the AMP is brought online, fully recovered.

Permanent Journal

Permanent Journals are an optional feature used to provide an additional level of data protection. You specify the use of permanent journal at the table level. It provides full-table recovery to a specific point in time. It can also reduce the need for costly and time – consuming full table backups.

Permanent journals are tables stored on disk array like user data is, so they can take up additional disk space, on the system. The database administrator maintains the permanent journal entries (deleting, archiving, and so on).A database can have one permanent journal.

When you create a table with permanent journaling, you must specify whether the permanent journal will capture.

• Before images – for rollback to “undo” a set of changes to a previous state.
• After images – for roll forward to “redo” to a specific state.

Following is the syntax of giving permanent journal –

CREATE DATABASE teradatatech
FROM space_amount AS
PERM = 4000000    /* permanent space */
SPOOL = 2000000 /* spool space */
NO FALLBACK
ACCOUNT = ‘$admin’
NO BEFORE JOURNAL
AFTER JOURNAL
DEFAULT JOURNAL TABLE = teradata.journal;
Here Admin has opted   for only AFTER JOURNAL and he has name the journal table as “teradata.journal”.
When user creates a table in the database “teradatatech” , by default AFTER JOURNAL is available for him to protect his data when the hardware failure occurs.

He can opt for NO AFTER JOURNAL by overriding the default.

Scenario1 : Here  by default the table has AFTER JOURNAL option.

CREATE TABLE table_name
( field1 INTEGER,
field2 INTEGER)
PRIMARY INDEX field1;

Scenario2: in this case, user has specifically stated he wanted no AFTER JOURNAL for his data. This is how user can override the defult.

CREATE TABLE table_name
FALLBACK,
NO AFTER JOURNAL
( field1 INTEGER,
field2 INTEGER)
PRIMARY INDEX field1;

In this case whenever the user inserts/updates and the transaction is committed , then the affected rows will be taken backup in the journal table “teradata.journal”