Download (direct link):
To protect against the loss of data in the event of a site disaster, many applications require that data archives be kept off-site at all times. Ideally, users dump the database to magnetic tape daily and store the tape off-site.
Daily archives may not be practical for very large databases. To solve this problem, you can activate after-change journals and take a daily archive of the journal itself, which provides archived copies of all changes made since the last full database archive.
The full backup tapes along with the journal backup tapes could be used to restore the entire database.
Using Journals with PPIs
You can use the ALTER TABLE statement to change the columns and the uniqueness of a PI or PPI, and to change, add, remove, or revalidate the partitioning of a PPI. These alterations affect the structure of a table, which causes the table version number to increment.
ARC cannot execute a cluster restore, single AMP restore, or rollforward or rollback of a permanent journal if the current version of a table is different from the archived version number. (For further details, see "Using ARC With Modified PIs or Partitioning" on page 8-8.)
2 - 78 Teradata RDBMS Database Administration
Chapter 2: Building the Teradata RDBMS
Each journal table consists of three subtables:
• Active subtable
• Saved subtable
• Restored subtable
The active and saved subtables together are referred to as the Current Journal. The restored subtable is called the Restored Journal.
Note: These journal subtables are maintained in an internal Teradata database format. They are not accessible by SQL statements and cannot be used for audit trail purposes.
The contents and purpose of each subtable are discussed below:
IF a journal is the . THEN .
Current Journal each time you update a data table that has an associated journal table, a change image is appended to the active subtable. You cannot archive journal tables while the change images are in the active subtable. Instead, you must move the images to the saved subtable. To move images from active to saved areas, use the ARC utility and enter the CHECKPOINT WITH SAVE command. A checkpoint places a marker at the chronological end of the active subtable. The database assigns an event number any time a user submits the checkpoint statement. The WITH SAVE option of the CHECKPOINT command inserts a checkpoint in the active subtable and then appends the contents of the active subtable to the end of the saved subtable.
After the database appends the contents of the active subtable to the end of the saved subtable, it initiates a new active subtable automatically. You can now submit an ARCHIVE JOURNAL TABLE command. Archiving the journal saves it to tape.
Restored Journal to restore the journal, move the journal table contents from the portable storage media back to the restored subtable using the ARC utility. The information stays in the restored subtable until you invoke roll operations.
2 - 78
Teradata RDBMS Database AdministrationChapter 2: Building the Teradata RDBMS
Permanent Journal Archive or Recovery
To perform backup and recovery functions associated with permanent journals, run the ARC (Archive and Recovery) utility. You can execute ARC on a channel-attached host or Windows 2000 client. Also, you can use BakBone NetVault (with Teradata plugin) on a network-attached client or server.
The archive and recovery commands include:
ROLLFORWARD Replaces a data row by its after-change image, starting from the beginning of the journal and proceeding to either a checkpoint or the end of the journal.
ROLLBACK Replaces a data row by its before change image from the end of the journal, to a checkpoint or to the beginning of the journal.
DELETE Deletes the contents of either the saved or restored journal areas.
Backing up tables on Teradata RDBMS involves the following steps (for more information on archiving and restoring your database, see Chapter 8: "Archiving, Restoring and Recovering Data"):
1 Archive the data tables onto portable storage media.
2 Submit a checkpoint with a SAVE statement to move change images from the active journal to the saved journal.
3 Archive the journal tables onto portable storage media.
4 Submit the DELETE JOURNAL statement to erase the saved journal rows.
Recovery with Permanent Journals
Use ARC statements when a batch program is run:
1 Submit an SQL Checkpoint statement as the first statement of the batch job, with or without a Checkpoint name.
2 If required, ROLLBACK to the Checkpoint using either the checkpoint name or the event number supplied by the DBC when you executed the Checkpoint command. Subsequent changes are also backed out.
3 The data table is now in its original condition. A permanent journal is time-oriented, not transaction-oriented.
2 - 78 Teradata RDBMS Database Administration