Online Archive Example (Using the ONLINE Option with the ARCHIVE Statement) - Teradata Tools and Utilities

• At 4PM, table TEST has 5 rows:

  1 AAA
  2 BBB
  3 CCC
  4 DDD
  5 EEE

• Online archive starts at 5PM by executing an ARCHIVE DATA TABLES statement with the ONLINE option. At that time, table TEST has only 4 rows: rows 1, 2, 3, and 4. Row 5 was deleted.

  1 AAA
  2 BBB
  3 CCC
  4 DDD

• During the ONLINE archive: a job modifies row 3 to ‘3 XXX’ and deletes row 2.
• At the end of the ONLINE Archive job, table TEST has 3 rows:

  1 AAA
  3 XXX
  4 DDD

• A restore of table TEST is done from the online backup and the contents of table TEST after the restore should be:

  1 AAA
  2 BBB
  3 CCC
  4 DDD

Explanation of the restore result:

This is the contents of the table when Online Logging was enabled at 5PM by the ARCHIVE statement. Once logging is enabled, changes will start getting logged as the online archive job proceeds. The changes to table TEST that will be logged into the Online log should include:

b) Modify row 3 to ‘3 XXX’ (change made during the online archive job)

c) Delete row 2 (change made during the online archive job)

These changes in the Online log are also archived when the table is archived and when the table is restored, the Online log is also restored and then every change in the restored Online log will get rolled back. The resulting table will be in the same state that existed when Online Logging was first enabled on the table at 5PM.

Note that the 1st change to table TEST:

a) Delete row 5 (change made before the online archive job)

was done before the Online Archive job was started and before Online Logging was enabled and therefore is not included in the Online log and will not get rolled back during the restore.