Recovering Data - Teradata Database

Teradata Database Administration

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Recovering Data

Some important concepts regarding data recovery operations are:

  • As with archive or restore operations, you use the ARC utility with permanent journals (PJs) for data recovery operations.
  • The CHECKPOINT statement indicates a recovery point in a journal.
  • The CHECKPOINT WITH SAVE statement saves stored images before a row marker in an active subtable and appends them to the saved subtable.
  • ROLLBACK or ROLLFORWARD operations, which involve PJs, can use either current journals (active and saved subtables) or restored journals (restored subtable). ROLLBACK commands help you recover from one or more transaction errors and reverses changes made to a database or table.
  • ROLLFORWARD commands help you recover from hardware errors. These commands replace existing row data with after-change images.
  • DELETE JOURNAL command erases the contents of either the restored subtable or the saved subtable in the PJ.
  • There are several recovery control system views that contain information about ARC utility events.
  • You can cancel rollback if you decide it is quicker to restore the tables instead.
  • Data Recovery Using Roll Operations

    The restore statement allows you to move information from archive files back to Teradata Database. The restore operation can restore data or journal tables. When you restore a journal table, the system restores the information to a permanent journal subtable. Before you can use the tables, you must perform a rollback or rollforward operation to move the journal tables back to the data tables.

    Roll operations can use either the current journal or the restored journal. If you specify the current journal, then the ARC utility uses information stored in both the active and saved subtables.

    A permanent journal is checkpoint-oriented rather than transaction-oriented. The goal of the journals is to return existing data tables to some previous or subsequent checkpoint. For example, if a batch program corrupted existing data, the rollback operation would return the data to a checkpoint prior to the running of the batch job.A rollforward operation might occur after an all-AMP restore. After you move the data and journal archive files back to the database, the data tables would only include changes committed since the last full backup. Any intermediate changes would reside in the journal tables. The rollforward operation would replace the existing data with changes from the journal table.

    In summary:

  • The RESTORE function copies journal archive files to the restored subtable of the PJ.
  • ROLLBACK and ROLLFORWARD statements apply journal table contents to data tables.
  • Roll operations can use either Current journal (active and saved subtables) or Restored journal (restored subtable).
  • CHECKPOINT Statement

    Use the CHECKPOINT statement to indicate a recovery point in the Journal. The CHECKPOINT statement places a marker row after the most recent change image row in the active subtable of a permanent journal.

    Teradata Database assigns an event number to the marker row and returns the number in response. You may assign a name to the CHECKPOINT statement rather than use the event number in subsequent ARC activities.

    The following table describes the options to the CHECKPOINT statement.




    NAMED chkptname

    Checkpoint names may be up to 120 characters and 512 bytes long and are not case-specific. Teradata Database always supplies an event number for each checkpoint. Use the number to reference a checkpoint if a name is not supplied.

    If there are duplicate checkpoint names in the journal and an event number is not specified:

  • Rollforward uses the first (oldest) occurrence.
  • Rollback uses the last (latest) occurrence.

    By default, the system acquires a read lock on all tables assigned to the journal being checkpointed. A checkpoint with save may optionally use an access lock.

    The read lock suspends update activity for all data tables that might write changes to the journal table during checkpoint. This lock provides a clean point on the journal.

    The access lock accepts all transactions that insert change images to the journal, but it treats them as though they were submitted after the checkpoint was written.

    The access lock option requires that you also use the WITH SAVE option.A checkpoint with save under an access lock is only useful for coordinating rollforward activities from the restored journal, and then from the current journal.


    The WITH SAVE option logically moves the contents of the active subtables of the identified journals to the saved subtable.

    After you archive the saved area of the journal, you can delete this section of the current journal to make space for subsequent saved journal images.

    The database automatically initiates a new active subtable. You can archive the contents of the saved subtable to an archive file.