What is ONLINE BACKUP intended for ? Is it fake feature ?

[Konstantin Andreev]

Hello.

Some time ago I asked in this conference, - How to use an ONLINE BACKUP to restore database onto another system? - but got no answers. Therefore I can conclude it is not possible.

But... what can I do with "online backup", really ? What is it needed for ? I do not see any possibility to use this backup. Please, share your knowledge, if you can.

Yes, I've read that since version 8.2 there is an option "INCLUDE LOSG" for "online backup". With this option "online backup" looks getting usable. But before v8.2 "online backup" looks absolutly unusable, fake feature. Please, correct me If I wrong.
--
Konstantin Andreev.

[Larry]

Online Backup is the ability to take a backup image copy without having
to take the database offline. I'm not sure what you mean by "fake
feature" ... online backup is used by a multitude of customers ... in
fact, it is normally preferred over offline backup because you don't
have to take the database offline. Unless I'm misunderstanding, I can't
see how you can say that it is a fake feature ... a backup is a basic
tenet of recovery strategy that most (if not all) customers need and use.

Larry Edelstein

Konstantin Andreev wrote:

Hello.

Some time ago I asked in this conference, - How to use an ONLINE BACKUP
to restore database onto another system? - but got no answers. Therefore
I can conclude it is not possible.

But... what can I do with "online backup", really ? What is it needed
for ? I do not see any possibility to use this backup. Please, share
your knowledge, if you can.

Yes, I've read that since version 8.2 there is an option "INCLUDE LOSG"
for "online backup". With this option "online backup" looks getting
usable. But before v8.2 "online backup" looks absolutly unusable, fake
feature. Please, correct me If I wrong.
--
Konstantin Andreev.

[Konstantin Andreev]

Larry wrote:

Online Backup is the ability to take a backup image copy without having to take the database offline. I'm not sure what you mean by "fake feature" ... online backup is used by a multitude of customers ... in fact, it is normally preferred over offline backup because you don't have to take the database offline.

My trouble is - I can't invent any workable scenario with online backup. For instance:

Scenario A
----------
1) I issue "BACKUP ONLINE" and move backup image into safe location.
2) Database system is burned out due fire in a building.
2) Backup image is here, but restoring from is *NOT* possible, because no logs. My backup is effectively the "fake".

Scenario B
----------
1) I issue "BACKUP ONLINE".
2) I change something in database
3) I want to revert the database to the state at the backup time, but it's *NOT* possible, because I *must* roll forward all logs. My backup is useless again.

Scenario C
----------
1) I issue "BACKUP ONLINE".
2) I change something in database
3) Unce upon a time a want to create some "restore point", for rolling forward could stop there. Therefore I must *QUIESCE* the database. But quiesce database is the same as OFFLINE from the users' point of view.
When in 1994 Microsoft started reselling Sybase ASE as it's pioneering SQL Server (v. 4.21) the simple tasks were already done simple. I just wrote "dump database db01 to device", and I was sure it's consistent and capable to be restored.

With DB2 the simple tasks tends getting more and more complicated. I've already spent the enormous amount of time trying to make the workable online backup. Please, direct me to the right way to proceed...
--
Konstantin Andreev.

[tsmithnj]

You are confusing operational issues with a very simple technical issue
that you need logs to move forward from a restore:

Scenario A: Apply scenario A to your SQL Server dump example and you
have the same issue. The point in time dump doesn't magically
incorporate new data as time passes.
Scenario B: Why anyone would use a restore operation to undo a
database change is beyond me. One doesn't fix bad form with
BACKUP/RESTORE, they fire the person who tries to ;-)
Scenaro C: This is just a modified scenario B.


You need the logs. E
Konstantin Andreev wrote:

Larry wrote:

Online Backup is the ability to take a backup image copy without having to take the database offline. I'm not sure what you mean by "fake feature" ... online backup is used by a multitude of customers ... in fact, it is normally preferred over offline backup because you don't have to take the database offline.

My trouble is - I can't invent any workable scenario with online backup. For instance:

Scenario A
----------
1) I issue "BACKUP ONLINE" and move backup image into safe location.
2) Database system is burned out due fire in a building.
2) Backup image is here, but restoring from is *NOT* possible, because no logs. My backup is effectively the "fake".

Scenario B
----------
1) I issue "BACKUP ONLINE".
2) I change something in database
3) I want to revert the database to the state at the backup time, but it's *NOT* possible, because I *must* roll forward all logs. My backup is useless again.

Scenario C
----------
1) I issue "BACKUP ONLINE".
2) I change something in database
3) Unce upon a time a want to create some "restore point", for rolling forward could stop there. Therefore I must *QUIESCE* the database. But quiesce database is the same as OFFLINE from the users' point of view.
When in 1994 Microsoft started reselling Sybase ASE as it's pioneering SQL Server (v. 4.21) the simple tasks were already done simple. I just wrote "dump database db01 to device", and I was sure it's consistent and capable to be restored.

With DB2 the simple tasks tends getting more and more complicated. I've already spent the enormous amount of time trying to make the workable online backup. Please, direct me to the right way to proceed...
--
Konstantin Andreev.

[Pierre Saint-Jacques]

If you read your documentation carefully, you find:
To do online backup your database must be roll forward enabled. This means
that you have the "capability" to keep your logs and decide to archive them
on the server or somewhere else if you want/need to.
You will also find that online backup "requires" you to, when you restore,
to roll forward at least to to the timestamp of the end of the backup.

Therefore, if you manage your logs properly with your backup image, you can
restore under all conditions that an offline backup offers and gain the
advantage of not putting the db offline when you backup.
The techniqur of backing up online and providing logs to restore has been
available from at least DB2 V7.2.

Your scenarios imply that you make changes to iether the server and/or the
date after the backup. It would suppose that logs would be available to
restore the db and bring it to a point odf consistency?

Hope this helps, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Konstantin Andreev" <pl**********@datatech.rua écrit dans le message de
news: ec**********@dns.comcor.ru...

Larry wrote:

Online Backup is the ability to take a backup image copy without having
to take the database offline. I'm not sure what you mean by "fake
feature" ... online backup is used by a multitude of customers ... in
fact, it is normally preferred over offline backup because you don't
have to take the database offline.

My trouble is - I can't invent any workable scenario with online backup.
For instance:

Scenario A
----------
1) I issue "BACKUP ONLINE" and move backup image into safe location.
2) Database system is burned out due fire in a building.
2) Backup image is here, but restoring from is *NOT* possible, because no
logs. My backup is effectively the "fake".

Scenario B
----------
1) I issue "BACKUP ONLINE".
2) I change something in database
3) I want to revert the database to the state at the backup time, but it's
*NOT* possible, because I *must* roll forward all logs. My backup is
useless again.

Scenario C
----------
1) I issue "BACKUP ONLINE".
2) I change something in database
3) Unce upon a time a want to create some "restore point", for rolling
forward could stop there. Therefore I must *QUIESCE* the database. But
quiesce database is the same as OFFLINE from the users' point of view.
When in 1994 Microsoft started reselling Sybase ASE as it's pioneering SQL
Server (v. 4.21) the simple tasks were already done simple. I just wrote
"dump database db01 to device", and I was sure it's consistent and capable
to be restored.

With DB2 the simple tasks tends getting more and more complicated. I've
already spent the enormous amount of time trying to make the workable
online backup. Please, direct me to the right way to proceed...
--
Konstantin Andreev.

[Konstantin Andreev]

21 Aug 2006, ts******@gmail.com wrote:

You are confusing operational issues with a very simple technical issue that you need logs to move forward from a restore:
Scenario A: Apply scenario A to your SQL Server dump example and you have the same issue. The point in time dump doesn't magically incorporate new data as time passes.

No need to incorporate later updates. From the dummy point of view:
- I make a backup
- I copy backup image to CD or DVD and put it in my pocket.
- I'm sure I have at least these data if my computer system is shooted with machine-gun.

Scenario B: Why anyone would use a restore operation to undo a database change is beyond me.

For example, you need to make the regression test of new version of application. Imagine Oracle/Peoplesoft issue an update of their ERP system. You would not blindly update your enterprise production system.
Another example is a diagnostic of complex application where you suspect a bug. You will need multiple runs against same data.

You need the logs.

It seems I can formulate correct question. Could you, please, read my answer to Pierre, just not to duplicate one here ?
Thank you.
--
Konstantin Andreev.

[Konstantin Andreev]

21 Aug 2006, 20:33:03, Pierre Saint-Jacques wrote:

If you read your documentation carefully, you find: To do online backup your database must be roll forward enabled.

Sure.

This means that you have the "capability" to keep your logs and decide to archive them on the server or somewhere else if you want/need to. You will also find that online backup "requires" you to, when you restore, to roll forward at least to to the timestamp of the end of the backup. Therefore, if you manage your logs properly with your backup image, you can restore under all conditions that an offline backup offers and gain the advantage of not putting the db offline when you backup.

Thank you, Pierre, this sounds reasonably. The simular discussions I have read in the various DB2 docs (Information Center, Data recovery guide, Raul F. Chong's book). But I can not find a workable invention from these discussions.

Maybe you could better help me by the example ? Let I

1) just issued "BACKUP ONLINE"
2) copyied backup image to DVD and put it in my pocket
3) ---- (unknown) ----- !!!

What else should I copy to DVD to be able to restore the database from backup in my pocket ?

The real database log's configuration ( from "get db config" CLP command ) :
LOGPRIMARY = 10
LOGSECOND = 118
LOGRETAIN = RECOVERY
USEREXIT = OFF
First active log file = S0006087.LOG
For now ( just after issuing BACKUP ONLINE ) the database log directory contains 22 log files: S0006075.LOG ... S0006096.LOG.

Theoretically, I should ask the database system to copy-out the active part of log to make my backup consistent. But what should I do in actual fact ?

Thank you in advance.
--
Konstantin Andreev.

[Oliver Mensinger]

Konstantin,

for a better handling of archive log files you need to activate the
userexit, a program that will copy the logs to another destination.
Follow this link for a guide:
http://publib.boulder.ibm.com/infoce...n/c0006086.htm

After installing the userexit you activate the archive logging with
"db2 update dbm cfg using userexit yes"

According to your example you create the online backup and copy it
together with the necessary archive log files to your dvd. How to know
what files are necessary?
Issue "db2ckbkp -H [Backupfile]", you need all archive log files since
the timestamp (see output of db2ckbkp)!

HTH, Oliver.

[Mark A]

"Konstantin Andreev" <pl**********@datatech.ruwrote in message
news:ec**********@dns.comcor.ru...

Thank you, Pierre, this sounds reasonably. The simular discussions I have
read in the various DB2 docs (Information Center, Data recovery guide,
Raul F. Chong's book). But I can not find a workable invention from these
discussions.

Maybe you could better help me by the example ? Let I

1) just issued "BACKUP ONLINE"
2) copyied backup image to DVD and put it in my pocket
3) ---- (unknown) ----- !!!

What else should I copy to DVD to be able to restore the database from
backup in my pocket ?

The real database log's configuration ( from "get db config" CLP command )
:
LOGPRIMARY = 10
LOGSECOND = 118
LOGRETAIN = RECOVERY
USEREXIT = OFF
First active log file = S0006087.LOG
For now ( just after issuing BACKUP ONLINE ) the database log directory
contains 22 log files: S0006075.LOG ... S0006096.LOG.

Theoretically, I should ask the database system to copy-out the active
part of log to make my backup consistent. But what should I do in actual
fact ?

Thank you in advance.
--
Konstantin Andreev.

If you use the INCLUDE LOGS keywords in the backup command, then you have
all the logs that were active during the on-line backup built into the
backup image, and you don't need anything else on your CD. I would also use
the COMPRESS keyword, to keep the backup to the minimum size possible.
Including the logs active during the backup in the backup image will enable
you to restore and roll the logs forward (which is mandatory) to the end of
the backup timestamp. If you are not sure what timestamp to roll the logs
forward to, just use a fictitious timestamp in the past, and DB2 will tell
you the minimum timestamp you must use in the roll forward command.

If you take an offline backup, you do not need to include any logs, and you
do not need to roll forward.

[Konstantin Andreev]

22.08.2006 22:18, Mark A wrote:

"Konstantin Andreev" <pl**********@datatech.ruwrote in message news:ec**********@dns.comcor.ru...

>Maybe you could better help me by the example ? Let I
1) just issued "BACKUP ONLINE"
2) copyied backup image to DVD and put it in my pocket
3) ---- (unknown) ----- !!!

What else should I copy to DVD to be able to restore the database from backup in my pocket ?

If you use the INCLUDE LOGS keywords in the backup command, then you have all the logs that were active during the on-line backup built into the backup image, and you don't need anything else on your CD.

Sorry, Mark, I misled you unintentionally. I did not mention in this thread I have DB2 version 8.1 which does not support "INCLUDE LOGS". My perplexity concerning online backup persists.

Indeed, what could be done ? Copy the whole bunch of current log files ( the whole log space ) to backup device (DVD) ? But...

- they are in use by database. I'm not sure that copying such files at filesystem level wouldn't break them.
- it's absurdly silly waste of space. At least all primary log space would be copied, even though *ACTIVE* part of logs IS zero, or close to zero.

Meanwhile I continue the daily denial of service to my users, because I obliged to perform the *offline* backup.
--
Konstantin Andreev.

[Joachim Klassen]

Konstantin Andreev schrieb:

22.08.2006 22:18, Mark A wrote:

"Konstantin Andreev" <pl**********@datatech.ruwrote in message news:ec**********@dns.comcor.ru...

Maybe you could better help me by the example ? Let I
1) just issued "BACKUP ONLINE"
2) copyied backup image to DVD and put it in my pocket
3) ---- (unknown) ----- !!!

What else should I copy to DVD to be able to restore the database from backup in my pocket ?

If you use the INCLUDE LOGS keywords in the backup command, then you have all the logs that were active during the on-line backup built into the backup image, and you don't need anything else on your CD.

Sorry, Mark, I misled you unintentionally. I did not mention in this thread I have DB2 version 8.1 which does not support "INCLUDE LOGS". My perplexity concerning online backup persists.

Indeed, what could be done ? Copy the whole bunch of current log files ( the whole log space ) to backup device (DVD) ? But...

- they are in use by database. I'm not sure that copying such files at filesystem level wouldn't break them.
- it's absurdly silly waste of space. At least all primary log space would be copied, even though *ACTIVE* part of logs IS zero, or close to zero.

Meanwhile I continue the daily denial of service to my users, because I obliged to perform the *offline* backup.
--
Konstantin Andreev.

You do not have to copy all log files. DB2 by default truncates the
current log file when an online backup is finished. You can determine
which log files belong to your online backup image by asking the DB2
Recovery History fter the backup : db2 list backup since <timestamp>
for <dbname>.

This will give you information about the log file which was active at
the begin of the backup operation and which was ative at the end. These
log files and of course all with numbers between them you should store
together with the online backup on your DVD. To restore hat database
later do the following:

restore db <dbnamefrom <your backup image location on DVD)
rollforward db <dbnamestop
-this will end with an SQL error. In the error message you'll get the
minimum timestamp to which the database must be rolled forward
next:
rollforward db <dbnameto <timestamp from error message>
overflowlogpath <path to the log files on DVD>
rollforward db <dbnamestop

HTH
Joachim