Teradata: Journals and Recovery in Teradata

Teradata consists of Journals to protect the data from getting lost in case a hardware or software issue occurs during processing. These journals help in protecting the data and maintaining the consistency of data in case a failure occurs.


There are three kind of journals in Teradata:

1. Down-Amp Recovery Journal
2. Permanent Journal
3. Transient Journal


DOWN AMP RECOVERY JOURNAL: Down amp recovery journal is used for not losing data when an amp goes down. It facilitates the database to continue its operation even though an amp is down. As soon as the amp goes down, its down amp recovery journal is activated. This journal stores all the data, that the amp would have received had it been up and running. All the data, in the down amps absence is written to the down amp recovery journal.
As soon as the issue is fixed and the amp is back online, all the data from the recovery journal is flushes to the amp so that the data remains consistent throughout the system and we don’t suffer with data loss. This feature is implemented at the system level and Teradata is designed to use down amp recovery journal in case an amp fails.


PERMANENT JOURNAL: Permanent Journals are used mostly at the table level. This is an optional feature and can be invoked by explicitly specifying it in the SQL. Permanent Journal is used for maintaining an audit trail of activities that take place since the creation of the table/ backup of the journal. For example, my table has 100 rows and i have since then inserted 50 rows and deleted 10 rows, then the journal will hold record of these 60 rows and in case we want to roll back to a specific point in time, we can use the journal to do so. We, are aware that a fallback table keeps a second copy of the table. While the fallback table stores all the rows of the table, the journal only stores the rows that are touched since the table creation and hence it is less costly to maintain the same.

There are two types of permanent journal implementation: Before Journal and After Journal
Before Journal is used to store before image of table to rollback in case of abort of transactions and after jounral is used to keep an audit trail of activities since the creation of the table to protect data in case of hardware failures.


After journal is switched on by default but we can explicitly state for the table to not use after journal as follows:




TRANSIENT JOURNAL: Transient Journal is used for storing before images of all the transactions running on the database at a particular point in time. Transient Journal is used for rollbacking all the data of the tables that had sessions running on when any hardware failure occurs or any session is aborted. For example: When we are deleting 100,000 rows from a table of million rows, the before image of data is stored in the transient journal and is flushed if the delete is successful or copied back ( rollback) if the transaction fails or gets aborted.
We have to make sure transient journal is never full and doesn’t fill up DBC as that will make the database hang and restart the database.

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (9 votes, average: 5.00 out of 5)

Add Comment