«

»

Apr
18

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”

 

 

 

29 comments

No ping yet

  1. admin says:

    Hi All,

    We have started a new special online teradata training batch for developers and DBA profile. For those who are interested to learn teradata can register in this batch. Fees is quite less when you compare it with other batches and biggest advantage is that its a instructor led training batch so you can ask any doubt in the training itself. Also we’ll be covering latest version of Teradata i.e. Teradata 14.10

    Please check following link for more details about this batch –
    http://www.onlineteradatatraining.com/?page_id=96

    Limited Seats. So try to register ASAP.

  2. Gopala says:

    Admin

    What is the use of Fallback, i think this also keeps the copy of table data if we have any failure or crash with data is it?

    1. admin says:

      FALLBACK is used for data protection at table level.

  3. Moorthy says:

    Hi…. i have doubt in below mentioned point
    “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”

    But Each AMP will have only access to it’s own disk right?But How other AMPs will bring any changes in Other AMPS?

    Can you clarify on this?

    1. yavuz says:

      Did you find the asnwer? I also really wonder how it happens..

    2. admin says:

      Down AMP Recovery journal pulls all modified data from fallback sector of each AMP. Any changes to the base table is reflected in Fallback sector as well, so DARJ keeps on updating this modified data.
      Once AMP is online, first task teradata will do is to update the base table with modified data of DARJ.

  4. priyanki says:

    This is the kind of crash course on TD i was looking for..Nice topic n a great Explanation…Thanks

  5. Nitin says:

    Thanks for sharing the info in such a simple manner.

  6. raag says:

    Hi,

    I have a question. if the no after journal is set in the second example, then how the affected data wil be taken backup in journal. i could not understand the difference between no after journal and after journal. can u please explain ?

  7. Prasath says:

    Concepts are suberbly explained. Thanks.

  8. Maria says:

    Hi!
    I’ll take the Teradata 12 soon and I think this website helps a lot with informations about Teradata. Your site is amazing!

  9. xinwei says:

    Howdy, a splendid read man. Good share. However I’m experiencing issue with ur rss feed. Fail to subscribe to it. Does anyone else obtaining same RSS issue? Anyone who can assist kindly reply. Thnx

    1. admin says:

      hi
      There are lot of active RSS subscribers to my blog.

      can u give more details regarding what kind of error your facing

  10. best payday says:

    Great read nice to find a blogger who know how to write and knows what they are talking about 10/10

  11. Chennai says:

    Thank you for these tips! they were just what I was looking for – Cheers

  12. good seo says:

    whoah this blog is magnificent i love reading your articles. Keep up the good work! You know, lots of people are looking around for this info, you can aid them greatly.

  13. Car repos says:

    Hey blogger, nice work with the choice of theme on this blog. It looks reall nice.

  14. car repos says:

    pretty valuable stuff, overall I think this is well worth a bookmark, thanks

  15. http://chennaibroadbandplans.blogspot.com says:

    What a lovely blog site. I will undoubtedly be back. Please preserve writing!

  16. http://chengelpetrealestate.blogspot.com/ says:

    Hey there, just wanted to drop you a quick line to say that your post was interesting to read. Cheers

  17. Anonymous says:

    Insightful stuff=D I am going to need a decent amount of time to toy with the post!!

  18. car repo says:

    Hi there mate, I liked your blog. I was wondering if I could leave a link back to my blog about buying used cars. Do check it out if that is something that interests you.

  19. Yahwe says:

    How much is a link to your site? My site < content suppressed>

    1. admin says:

      hi,

      please mail me at nitin@teradatatech.com , for discussing the link advertisement on this site.

  20. fdnj says:

    Nice topic – respect !

  21. mikeross says:

    I think one of your advertisements caused my internet browser to resize, you might want to put that on your blacklist.

    1. admin says:

      Hi Mikeross

      Could you please tell me which advertisement caused this resizing, so that i can removed it from my site :)

      thanks for feedback

  22. Tyisha says:

    Fine details! I have been searching for some thing similar to this for a while these days. Thanks!

    1. admin says:

      Thanks for your appreciation :)

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>