All Forums Tools
SPOLISETTI 7 posts Joined 06/09
21 Jul 2011
online archive feature in Teradata 13

Has anyone tried this feature in TD12 or TD13?

I would like to know how it does online backup. Do the inserts/updates are commited while the backup is in progress?
Does it use Transient Journal? Teradata manual on backup has below information.
I am looking for more technical information on how online backup is done.

"When online archive is initiated on either a table or a database, the
system creates and maintains either a log for the specified table or a
separate log for each table in the specified database. All changes to a
table are captured in its log. The log of all changed rows will be
archived as a part of the archive process, and will be restored and
applied to undo the changes, which occurred during the archive as
part of the restore operation. The tables within the same
transaction in the online archive will be restored to the same
transactional consistent point."

Jim Chapman 449 posts Joined 09/04
22 Jul 2011

Yes, regular DML operations, as well as the MultiLoad and Tpump utilities, may execute and commit concurrently with the archive operation. Undo information is captured into a special log that is associated with each archived table while the archive operation is active. This information is extracted from the Transient Journal, or (in the case of MultiLoad) generated directly by the load transaction. The resulting log is copied to the archive dataset at the end of the data backup phase. On restore, the log is used to roll back all the effects of the concurrent transactions.

SPOLISETTI 7 posts Joined 06/09
22 Jul 2011

Thank you Jim for the info.

Here are more details:

Restore of a table will always recover the table to the state it was in when logging was first activated on the table.

When the online archive command is issued, it logs the before images of all the rows that are modified while the backup is in progress. Teradata keeps rolling forward group lock and backs up the data. Writes are allowed on all rows except on the group of rows that are being backed up. Before image of all the rows that are modified during the backups, are kept in a separate log file. This way Teradata archives the table. Once the table backup completes, it backups the log file.

Let us say a table has 1,000 rows. In online backup, first it keeps group lock on rows 1-100 and start backing up these rows. During this time, rows 1-100 cannot be modified. But other rows 101-1000 can be modified. For e.g. row 101 is modified while rows 1-100 are being archived, teradata keeps the before image of row 101 in a separate log. This is committed as per transaction and is immediately available for the users. Any transactions that try to modify rows between 1 and 100 will be blocked until these rows are backed up. When backup on rows 1-100 completes, it comes to 101-200 and places group lock. At this point, row 101 is already modified. The backup will read this modified row 101 into table backup. Once the all rows are backed up, the log table containing before images of modified rows is also backed up.

How does restore work?
Table is first restored from the table backup. At this point, the table has modified row 101. Then with the log file, it rolls back the table to the stat it was in when the online archive command is first run. The beauty of this new feature is that it does the rollback automatically.

Divine786 10 posts Joined 08/13
29 Jan 2014

I'm facing an issue in copying data from one machine to another using arcmain copy command
but this archive script archive more rows from source machine for eg:
Source Machine event table for month of october has 14,602,977 rows but the archive scripts archive 17,444,874 rows can anyone please help me in this.

kvz 10 posts Joined 08/13
05 Jul 2016

We are getting blockings even in online backup. Blocking gets clear once the backup of that DB get completed.
Locks will be applied on DB level or Table level during online backups?
what is the solution to avoid this situation?

You must sign in to leave a comment.