Question about external backups to filesystems

[RoB]

Hi all,

I'm coming from the Informix world and I have a customer using DB2
8.2.3 for Linux on Red Hat Enterprise ES.

The customer is performing filesystem backups of the containers etc
every night but they are not shutting down the database server while
doing this. I only assume that this most likelly would leave an
inconsistant backup image as there is nothing assuring that the
modified pages in the buffer pool get written to disk before the
filesystem backup starts. There is plenty of acticivty on the database
24x7.

Question: Apart from shutting down the instance before performing such
an external backup, is there a way in db2 to block all access and make
sure that all modified pages in memory gets written to disk? I've
tried the not so user-friendly online db2 documentation but without
any luck. With Informix Dynamic Server (IDS) this would be archieved
by issuing the commands "onmode -c BLOCK" and then "onmode -c UNBLOCK"
once the external backup has finished.

Any input is appreciated.

RoB

[krx]

On Mar 23, 8:25 am, "RoB" <pluma...@yahoo .comwrote:

Hi all,

I'm coming from the Informix world and I have a customer using DB2
8.2.3 for Linux on Red Hat Enterprise ES.

The customer is performing filesystem backups of the containers etc
every night but they are not shutting down the database server while
doing this. I only assume that this most likelly would leave an
inconsistant backup image as there is nothing assuring that the
modified pages in the buffer pool get written to disk before the
filesystem backup starts. There is plenty of acticivty on the database
24x7.

Question: Apart from shutting down the instance before performing such
an external backup, is there a way in db2 to block all access and make
sure that all modified pages in memory gets written to disk? I've
tried the not so user-friendly online db2 documentation but without
any luck. With Informix Dynamic Server (IDS) this would be archieved
by issuing the commands "onmode -c BLOCK" and then "onmode -c UNBLOCK"
once the external backup has finished.

Any input is appreciated.

RoB

There's no need to shutdown the database if it needs to be used 24x7.
Set up Log archival and start taking online backups.

[RoB]

On Mar 23, 12:51 pm, "krx" <kedar.she...@g mail.comwrote:

On Mar 23, 8:25 am, "RoB" <pluma...@yahoo .comwrote:

Hi all,

I'm coming from the Informix world and I have a customer using DB2
8.2.3 for Linux on Red Hat Enterprise ES.

The customer is performing filesystem backups of the containers etc
every night but they are not shutting down the database server while
doing this. I only assume that this most likelly would leave an
inconsistant backup image as there is nothing assuring that the
modified pages in the buffer pool get written to disk before the
filesystem backup starts. There is plenty of acticivty on the database
24x7.

Question: Apart from shutting down the instance before performing such
an external backup, is there a way in db2 to block all access and make
sure that all modified pages in memory gets written to disk? I've
tried the not so user-friendly online db2 documentation but without
any luck. With Informix Dynamic Server (IDS) this would be archieved
by issuing the commands "onmode -c BLOCK" and then "onmode -c UNBLOCK"
once the external backup has finished.

Any input is appreciated.

RoB

There's no need to shutdown the database if it needs to be used 24x7.
Set up Log archival and start taking online backups.- Hide quoted text -

- Show quoted text -

Hi,

This customer was "having problems" with setting up a proper db2
backup strategy so they went on with using filesystem backups. As far
as they are concerned, they think this works fine and want to continue
doing it. So I'm bascially not looking for other solutions here,
although I know that they exist, but I'm looking for a way to tweak
their current strategy into something that actually will restore a
consistent backup.

RoB

[Philip Nelson]

RoB wrote:

On Mar 23, 12:51 pm, "krx" <kedar.she...@g mail.comwrote:

>On Mar 23, 8:25 am, "RoB" <pluma...@yahoo .comwrote:

Hi all,

I'm coming from the Informix world and I have a customer using DB2
8.2.3 for Linux on Red Hat Enterprise ES.

The customer is performing filesystem backups of the containers etc
every night but they are not shutting down the database server while
doing this. I only assume that this most likelly would leave an
inconsistant backup image as there is nothing assuring that the
modified pages in the buffer pool get written to disk before the
filesystem backup starts. There is plenty of acticivty on the database
24x7.

Question: Apart from shutting down the instance before performing such
an external backup, is there a way in db2 to block all access and make
sure that all modified pages in memory gets written to disk? I've
tried the not so user-friendly online db2 documentation but without
any luck. With Informix Dynamic Server (IDS) this would be archieved
by issuing the commands "onmode -c BLOCK" and then "onmode -c UNBLOCK"
once the external backup has finished.

Any input is appreciated.

RoB

There's no need to shutdown the database if it needs to be used 24x7.
Set up Log archival and start taking online backups.- Hide quoted text -

- Show quoted text -

Hi,

This customer was "having problems" with setting up a proper db2
backup strategy so they went on with using filesystem backups. As far
as they are concerned, they think this works fine and want to continue
doing it. So I'm bascially not looking for other solutions here,
although I know that they exist, but I'm looking for a way to tweak
their current strategy into something that actually will restore a
consistent backup.

RoB

RoB,

I'd say that if the customer is using filesystem backups, especially if he
actually isn't shutting down the instance, then "all bets are off" on being
able to recover anything at all.

In fact, without using the tools provided for the job, which have worked
exceptionally well for me over many years, I don't think he will get any
sympathy from IBM if he loses all his data.

I wonder if he has ever attempted a restore with what he has.

So there really is no solution but to put in place a backup and recovery
strategy using the DB2 BACKUP command as the foundation for what he does.

If he needs someone to do this for him, then he just has to ask. I'm sure
there are plenty of people on the list (myself included) with the skills to
do such a thing.

Phil Nelson
ScotDB Limited
(te*****@scotdb .com)

[RoB]

On Mar 23, 1:22 pm, Philip Nelson <team...@scotdb .comwrote:

RoB wrote:

On Mar 23, 12:51 pm, "krx" <kedar.she...@g mail.comwrote:

On Mar 23, 8:25 am, "RoB" <pluma...@yahoo .comwrote:

Hi all,

I'm coming from the Informix world and I have a customer using DB2
8.2.3 for Linux on Red Hat Enterprise ES.

The customer is performing filesystem backups of the containers etc
every night but they are not shutting down the database server while
doing this. I only assume that this most likelly would leave an
inconsistant backup image as there is nothing assuring that the
modified pages in the buffer pool get written to disk before the
filesystem backup starts. There is plenty of acticivty on the database
24x7.

Question: Apart from shutting down the instance before performing such
an external backup, is there a way in db2 to block all access and make
sure that all modified pages in memory gets written to disk? I've
tried the not so user-friendly online db2 documentation but without
any luck. With Informix Dynamic Server (IDS) this would be archieved
by issuing the commands "onmode -c BLOCK" and then "onmode -c UNBLOCK"
once the external backup has finished.

Any input is appreciated.

RoB

There's no need to shutdown the database if it needs to be used 24x7.
Set up Log archival and start taking online backups.- Hide quoted text -

- Show quoted text -

Hi,

This customer was "having problems" with setting up a proper db2
backup strategy so they went on with using filesystem backups. As far
as they are concerned, they think this works fine and want to continue
doing it. So I'm bascially not looking for other solutions here,
although I know that they exist, but I'm looking for a way to tweak
their current strategy into something that actually will restore a
consistent backup.

RoB

RoB,

I'd say that if the customer is using filesystem backups, especially if he
actually isn't shutting down the instance, then "all bets are off" on being
able to recover anything at all.

In fact, without using the tools provided for the job, which have worked
exceptionally well for me over many years, I don't think he will get any
sympathy from IBM if he loses all his data.

I wonder if he has ever attempted a restore with what he has.

So there really is no solution but to put in place a backup and recovery
strategy using the DB2 BACKUP command as the foundation for what he does.

If he needs someone to do this for him, then he just has to ask. I'm sure
there are plenty of people on the list (myself included) with the skills to
do such a thing.

Phil Nelson
ScotDB Limited
(team...@scotdb .com)- Hide quoted text -

- Show quoted text -

My thoughts precisely. As far as I know they have never tried to
restore... Which probably won't be successful anyway..

So if we go back to the original question: Is there a way (with a
command) to block the database server without shutting it down, and
hence killing off all the user sessions, and get all content in the
buffer pools to be written to their disk?

If this is not possible, then I wonder how a, say 15 terabyte large
database gets backed up nightly by db2 without shutting it down? Such
a backup with any standard server utility will most likely take more
than 24 hours which stops it from being performed each night. With
other database servers you can have a SAN that internally replicates
the disks and then just block the database server for a few seconds
(which also writes all dirty pages to disk) while you split off the
newly created disk image within the SAN. You can combine this level 0
external archive with database server logical log backups to be able
to bring the instance back to the point in time of the failure.

RoB

[aj]

Does your customer use a SAN? If so, split mirrors are a possibility.
Look at:

http://www-128.ibm.com/developerwork...azi/index.html

Basically, you use functionality provided by your storage vendor to make
an instantaneous copy of DB2 containers, utilizing a few DB2 tricks (SET
WRITE SUSPEND FOR DATABASE, SET WRITE RESUME FOR DATABASE, and DB2INIDB)
to make sure there are no in-flight transactions in the database. You
can then bring the copy online.

We happen to use EMC, and their name for it is "SnapView"

HTH

aj

PS - I'm a former Informix guy also. Welcome to DB2! :)

RoB wrote:

On Mar 23, 1:22 pm, Philip Nelson <team...@scotdb .comwrote:

>RoB wrote:

>>On Mar 23, 12:51 pm, "krx" <kedar.she...@g mail.comwrote:
On Mar 23, 8:25 am, "RoB" <pluma...@yahoo .comwrote:
Hi all,
I'm coming from the Informix world and I have a customer using DB2
8.2.3 for Linux on Red Hat Enterprise ES.
The customer is performing filesystem backups of the containers etc
every night but they are not shutting down the database server while
doing this. I only assume that this most likelly would leave an
inconsistan t backup image as there is nothing assuring that the
modified pages in the buffer pool get written to disk before the
filesyste m backup starts. There is plenty of acticivty on the database
24x7.
Question: Apart from shutting down the instance before performing such
an external backup, is there a way in db2 to block all access and make
sure that all modified pages in memory gets written to disk? I've
tried the not so user-friendly online db2 documentation but without
any luck. With Informix Dynamic Server (IDS) this would be archieved
by issuing the commands "onmode -c BLOCK" and then "onmode -c UNBLOCK"
once the external backup has finished.
Any input is appreciated.
RoB
There's no need to shutdown the database if it needs to be used 24x7.
Set up Log archival and start taking online backups.- Hide quoted text -
- Show quoted text -
Hi,
This customer was "having problems" with setting up a proper db2
backup strategy so they went on with using filesystem backups. As far
as they are concerned, they think this works fine and want to continue
doing it. So I'm bascially not looking for other solutions here,
although I know that they exist, but I'm looking for a way to tweak
their current strategy into something that actually will restore a
consistent backup.
RoB

RoB,

I'd say that if the customer is using filesystem backups, especially if he
actually isn't shutting down the instance, then "all bets are off" on being
able to recover anything at all.

In fact, without using the tools provided for the job, which have worked
exceptionall y well for me over many years, I don't think he will get any
sympathy from IBM if he loses all his data.

I wonder if he has ever attempted a restore with what he has.

So there really is no solution but to put in place a backup and recovery
strategy using the DB2 BACKUP command as the foundation for what he does.

If he needs someone to do this for him, then he just has to ask. I'm sure
there are plenty of people on the list (myself included) with the skills to
do such a thing.

Phil Nelson
ScotDB Limited
(team...@scotd b.com)- Hide quoted text -

- Show quoted text -

My thoughts precisely. As far as I know they have never tried to
restore... Which probably won't be successful anyway..

So if we go back to the original question: Is there a way (with a
command) to block the database server without shutting it down, and
hence killing off all the user sessions, and get all content in the
buffer pools to be written to their disk?

If this is not possible, then I wonder how a, say 15 terabyte large
database gets backed up nightly by db2 without shutting it down? Such
a backup with any standard server utility will most likely take more
than 24 hours which stops it from being performed each night. With
other database servers you can have a SAN that internally replicates
the disks and then just block the database server for a few seconds
(which also writes all dirty pages to disk) while you split off the
newly created disk image within the SAN. You can combine this level 0
external archive with database server logical log backups to be able
to bring the instance back to the point in time of the failure.

RoB

[aj]

PS - I suggest your customer start doing online backups rather than
filesystem container backups right quick, before they have a Really,
Really Bad Day(tm).

Also, verify the online backups w/ the db2ckbkp command.

aj

aj wrote:

Does your customer use a SAN? If so, split mirrors are a possibility.
Look at:

http://www-128.ibm.com/developerwork...azi/index.html
Basically, you use functionality provided by your storage vendor to make
an instantaneous copy of DB2 containers, utilizing a few DB2 tricks (SET
WRITE SUSPEND FOR DATABASE, SET WRITE RESUME FOR DATABASE, and DB2INIDB)
to make sure there are no in-flight transactions in the database. You
can then bring the copy online.

We happen to use EMC, and their name for it is "SnapView"

HTH

aj

PS - I'm a former Informix guy also. Welcome to DB2! :)

RoB wrote:

>On Mar 23, 1:22 pm, Philip Nelson <team...@scotdb .comwrote:

>>RoB wrote:
On Mar 23, 12:51 pm, "krx" <kedar.she...@g mail.comwrote:
On Mar 23, 8:25 am, "RoB" <pluma...@yahoo .comwrote:
>Hi all,
>I'm coming from the Informix world and I have a customer using DB2
>8.2.3 for Linux on Red Hat Enterprise ES.
>The customer is performing filesystem backups of the containers etc
>every night but they are not shutting down the database server while
>doing this. I only assume that this most likelly would leave an
>inconsista nt backup image as there is nothing assuring that the
>modified pages in the buffer pool get written to disk before the
>filesyst em backup starts. There is plenty of acticivty on the
>database
>24x7.
>Question : Apart from shutting down the instance before performing
>such
>an external backup, is there a way in db2 to block all access and
>make
>sure that all modified pages in memory gets written to disk? I've
>tried the not so user-friendly online db2 documentation but without
>any luck. With Informix Dynamic Server (IDS) this would be archieved
>by issuing the commands "onmode -c BLOCK" and then "onmode -c
>UNBLOCK"
>once the external backup has finished.
>Any input is appreciated.
>RoB
There's no need to shutdown the database if it needs to be used 24x7.
Set up Log archival and start taking online backups.- Hide quoted
text -
- Show quoted text -
Hi,
This customer was "having problems" with setting up a proper db2
backup strategy so they went on with using filesystem backups. As far
as they are concerned, they think this works fine and want to continue
doing it. So I'm bascially not looking for other solutions here,
although I know that they exist, but I'm looking for a way to tweak
their current strategy into something that actually will restore a
consistent backup.
RoB
RoB,

I'd say that if the customer is using filesystem backups, especially
if he
actually isn't shutting down the instance, then "all bets are off" on
being
able to recover anything at all.

In fact, without using the tools provided for the job, which have worked
exceptional ly well for me over many years, I don't think he will get any
sympathy from IBM if he loses all his data.

I wonder if he has ever attempted a restore with what he has.

So there really is no solution but to put in place a backup and recovery
strategy using the DB2 BACKUP command as the foundation for what he
does.

If he needs someone to do this for him, then he just has to ask. I'm
sure
there are plenty of people on the list (myself included) with the
skills to
do such a thing.

Phil Nelson
ScotDB Limited
(team...@scot db.com)- Hide quoted text -

- Show quoted text -

My thoughts precisely. As far as I know they have never tried to
restore... Which probably won't be successful anyway..

So if we go back to the original question: Is there a way (with a
command) to block the database server without shutting it down, and
hence killing off all the user sessions, and get all content in the
buffer pools to be written to their disk?

If this is not possible, then I wonder how a, say 15 terabyte large
database gets backed up nightly by db2 without shutting it down? Such
a backup with any standard server utility will most likely take more
than 24 hours which stops it from being performed each night. With
other database servers you can have a SAN that internally replicates
the disks and then just block the database server for a few seconds
(which also writes all dirty pages to disk) while you split off the
newly created disk image within the SAN. You can combine this level 0
external archive with database server logical log backups to be able
to bring the instance back to the point in time of the failure.

RoB

[RoB]

On Mar 23, 3:32 pm, aj <ron...@mcdonal ds.comwrote:

Does your customer use a SAN? If so, split mirrors are a possibility.
Look at:

http://www-128.ibm.com/developerwork...rticle/dm-0508...

Basically, you use functionality provided by your storage vendor to make
an instantaneous copy of DB2 containers, utilizing a few DB2 tricks (SET
WRITE SUSPEND FOR DATABASE, SET WRITE RESUME FOR DATABASE, and DB2INIDB)
to make sure there are no in-flight transactions in the database. You
can then bring the copy online.

We happen to use EMC, and their name for it is "SnapView"

HTH

aj

PS - I'm a former Informix guy also. Welcome to DB2! :)

RoB wrote:

On Mar 23, 1:22 pm, Philip Nelson <team...@scotdb .comwrote:

RoB wrote:
On Mar 23, 12:51 pm, "krx" <kedar.she...@g mail.comwrote:
On Mar 23, 8:25 am, "RoB" <pluma...@yahoo .comwrote:
Hi all,
I'm coming from the Informix world and I have a customer using DB2
8.2.3 for Linux on Red Hat Enterprise ES.
The customer is performing filesystem backups of the containers etc
every night but they are not shutting down the database server while
doing this. I only assume that this most likelly would leave an
inconsista nt backup image as there is nothing assuring that the
modified pages in the buffer pool get written to disk before the
filesystem backup starts. There is plenty of acticivty on the database
24x7.
Question: Apart from shutting down the instance before performing such
an external backup, is there a way in db2 to block all access and make
sure that all modified pages in memory gets written to disk? I've
tried the not so user-friendly online db2 documentation but without
any luck. With Informix Dynamic Server (IDS) this would be archieved
by issuing the commands "onmode -c BLOCK" and then "onmode -c UNBLOCK"
once the external backup has finished.
Any input is appreciated.
RoB
There's no need to shutdown the database if it needs to be used 24x7.
Set up Log archival and start taking online backups.- Hide quoted text -
- Show quoted text -
Hi,
This customer was "having problems" with setting up a proper db2
backup strategy so they went on with using filesystem backups. As far
as they are concerned, they think this works fine and want to continue
doing it. So I'm bascially not looking for other solutions here,
although I know that they exist, but I'm looking for a way to tweak
their current strategy into something that actually will restore a
consistent backup.
RoB
RoB,

I'd say that if the customer is using filesystem backups, especially if he
actually isn't shutting down the instance, then "all bets are off" on being
able to recover anything at all.

In fact, without using the tools provided for the job, which have worked
exceptionally well for me over many years, I don't think he will get any
sympathy from IBM if he loses all his data.

I wonder if he has ever attempted a restore with what he has.

So there really is no solution but to put in place a backup and recovery
strategy using the DB2 BACKUP command as the foundation for what he does.

If he needs someone to do this for him, then he just has to ask. I'm sure
there are plenty of people on the list (myself included) with the skills to
do such a thing.

Phil Nelson
ScotDB Limited
(team...@scotdb .com)- Hide quoted text -

- Show quoted text -

My thoughts precisely. As far as I know they have never tried to
restore... Which probably won't be successful anyway..

So if we go back to the original question: Is there a way (with a
command) to block the database server without shutting it down, and
hence killing off all the user sessions, and get all content in the
buffer pools to be written to their disk?

If this is not possible, then I wonder how a, say 15 terabyte large
database gets backed up nightly by db2 without shutting it down? Such
a backup with any standard server utility will most likely take more
than 24 hours which stops it from being performed each night. With
other database servers you can have a SAN that internally replicates
the disks and then just block the database server for a few seconds
(which also writes all dirty pages to disk) while you split off the
newly created disk image within the SAN. You can combine this level 0
external archive with database server logical log backups to be able
to bring the instance back to the point in time of the failure.

RoB- Hide quoted text -

- Show quoted text -

Thanks AJ! That was exactly the sequence of commands what I was
after.

This customer doesn't use SAN splits but by applying these commands
around their filesystem backup it will be consistent, which is what
I've been trying to achieve.

Thanks again you all for your input,

RoB

[Ian]

RoB wrote:

Thanks AJ! That was exactly the sequence of commands what I was
after.

This customer doesn't use SAN splits but by applying these commands
around their filesystem backup it will be consistent, which is what
I've been trying to achieve.

You need to read the documentation to understand how this is used, and
the ins and outs (and consequences) of it.

Using SET WRITE SUSPEND will block DB2 from doing any writes to disk,
however it does not flush data from bufferpools to disk, or do anything
to cause the OS to flush applicable I/O buffers (at least that's my
understanding). The database is not consistent.

The intent of this (when used with split-mirrors) is to allow you to
mount the mirrors on another server and then use the BACKUP DATABASE
command to actually back up the database. This is a very common way to
handle large data warehouses (like 15Tb), if the entire database must be
backed up.

However, you have to realize that "unfreezing the database" using the
'db2inidb' command can have consequences for recovery.

Using SET WRITE SUSPEND with the customer's desired backup mechanism
might work, but I wouldn't depend on it. There's a reason people are
suggesting you use the supported backup mechanism. And you really
should direct your customer towards a workable solution.

In addition, your customer would need to keep the database in
write-suspend mode for the duration of the backup (i.e. to ensure that
all DB2 data backed up is consistent). If you're talking about 15Tb
of data, this file system level backup will take a LONG time, and
will likely have some effect on database users. i.e., if you can't
write to temporary tablespaces, even select queries can block.

[RoB]

On Mar 23, 9:38 pm, Ian <ianb...@mobile audio.comwrote:

RoB wrote:

Thanks AJ! That was exactly the sequence of commands what I was
after.

This customer doesn't use SAN splits but by applying these commands
around their filesystem backup it will be consistent, which is what
I've been trying to achieve.

You need to read the documentation to understand how this is used, and
the ins and outs (and consequences) of it.

Using SET WRITE SUSPEND will block DB2 from doing any writes to disk,
however it does not flush data from bufferpools to disk, or do anything
to cause the OS to flush applicable I/O buffers (at least that's my
understanding). The database is not consistent.

The intent of this (when used with split-mirrors) is to allow you to
mount the mirrors on another server and then use the BACKUP DATABASE
command to actually back up the database. This is a very common way to
handle large data warehouses (like 15Tb), if the entire database must be
backed up.

However, you have to realize that "unfreezing the database" using the
'db2inidb' command can have consequences for recovery.

Using SET WRITE SUSPEND with the customer's desired backup mechanism
might work, but I wouldn't depend on it. There's a reason people are
suggesting you use the supported backup mechanism. And you really
should direct your customer towards a workable solution.

In addition, your customer would need to keep the database in
write-suspend mode for the duration of the backup (i.e. to ensure that
all DB2 data backed up is consistent). If you're talking about 15Tb
of data, this file system level backup will take a LONG time, and
will likely have some effect on database users. i.e., if you can't
write to temporary tablespaces, even select queries can block.

Thanks for that Ian. The procedure made will basically be the same
described here only that it'll be done to a filesystem (the size of
their db is in the range of 60 GB and that will, as you correctly
pointed out, take quite a while to perform on a filesystem):

http://www.db2mag.com/story/showArti...leID=173500277

If using a good SAN solution (like EMC etc) the creation of the
mirrors can by the SAN can normally be performed while the database
server is online so you only need to issue the blocking mechanism at a
time when the SAN has finished with creating the mirrors. Any changes
done against the disks while the mirrors are being performed will be
tracked by the SAN and applied to the mirror image before finishing.

I have stressed the importance (and will continue doing so) of a db2
type backup to the customer but implementing it is their choice to
make.

So, is there actually a way to force a db2 instance to flush the dirty
buffers in the buffer pools to disk?

The flushing of the OS cache is probably handled differently on
different OSes but does anyone have any idea of how to force RedHat to
do this?
Also, while we're at it, does anyone know how to disable the OS
caching of filesystems on RedHat to prevent the double caching of
accessed pages from the containers (by both the OS and the db2
instance)?

Thanks,

RoB