All Forums Database
goldminer 118 posts Joined 05/09
15 Feb 2014
Online backup performance

Generally speaking, can anyone tell me if online backups perform better than regular backups?  In other words, if I am backing up a 500gb table, which method would back up quicker, online or regular?
Thanks,
 
Joe

Adeel Chaudhry 773 posts Joined 04/08
15 Feb 2014

Online backups are slower than regular backups. During online backup DB stays available and in regular/offline there is a DB outage. Hence it depends on your needs and environment if you can afford DB outage or not.
 
HTH!
 

-- If you are stuck at something .... consider it an opportunity to think anew.

Raja_KT 1246 posts Joined 07/09
16 Feb 2014

Think about the resources :) and priorities during online backups and regular. For regular one priority is given. In almost all DBs that I know Online backups are slower than regular .
Cheers,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Hustler 9 posts Joined 12/15
24 Mar 2016

Hi Everybody,
I have few questions around online backups.
Sol& Tools: Currently we are planning to use Teradata DSA(TDSA) solution with NetBackup  tool. The amount of data to be backed up is currently around say 10TB. Backing to disks not the tapes
** Abbreviations:
(TDSA)- Teradata Data stream architecture solution.
TDATA-Teradata
I knew some clients run online backups as they run ETL most of the time, What said is the explicit feature of the online backup. That being said, I am not clear of 2-3% change guideline (if I am not wrong where it explicitly says while running online backups there should not be more than 2-3% change at the whole database level)** Correct me if I am wrong/not exact**
DID ANYBODY HAD SEEN BACKUP FAILED OR ETL FAILED BCZ OF SPOOL SPACE EXCEEDED.” considering only 20-30% of total space is dbc spool space
As I understood it, while running online backup TDSA read the whole and create the check point and start writing to the disk solution, while at the same time users can do read and write (R&W) operations on the objects.
                The question here, 1. is  DSA copying all the objects and placing them in the spool?(I hope this is not what goes there, but if you say yes, what for 10TB backup assuming it is a full backup, how much space it take up form DBC spool?), OR
2. is Teradata keep the updates of any kind in a temporary table(which of course should go to spool)  which one happens and how 2-3% change thumb rule effects the spool? Or what is this guideline warning us towards? If you say yes for this 2nd after running any ETL during backup and if we query the object will TDATA gives us new updated values?
                Scenario: If the table name is “SMILING” before backup run and while running backup table name is updated to “TERRIFIED”. Will querying the table with TERRIFIED return the values? If you say we don’t rename tables so often, if the update is at row level will we get the updated values while backup is still is in running status.
3. AS TERADATA already had the data compressed while storing on it’s disk, is it true that TDSA still compresses the data while backing it to disk backups.(if it recompresses what is the expected ratio?)
**I appreciate if you could attach any standard documents as a reference for any comments**
Hoping for a good advice, Please do ask me if I forgot to mention any details.
Thanks in advance. 

Thanks,
Komal

Fred 1096 posts Joined 08/04
27 Mar 2016

1. DSA (online or not) does not copy objects to spool.
2. Online backup journals changes to a PermSpace subtable for each table being backed up. Search the documentation for "online archive logging". This journal is appended to the backup of the table, and used to automatically "roll back" to the initial consistency point (start of the backup) during a restore. Online backup holds an ACCESS lock on the object and RENAME requires EXCLUSIVE lock, so a RENAME would be blocked until the backup had completed. Queries are able to access updated values during online backup.
3. With DSA, any compressed data blocks are sent to the storage device as-is (contrasted with ARC which decompresses the data blocks before sending to the client). The storage subsystem may perform some additional compression or de-duplication.

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?

 

Thanks,

KVZ

You must sign in to leave a comment.