When is an offline backup not really offline?

[Michael Hoffmann]

Hello,

I hope this doesn't come across as totally newbieish, but I've been
searching the web and the IBM docs until smoke is coming out of my head.

Scenario: We are trying to take primitive "snapshots" of our production
database into a test environment by taking (what we thought are) offline
backups of production, then doing a redirected restore into test.

Here's the steps I do:

-- Make absolutely sure there's nobody connected but the admin.
$ db2stop force;
$ db2start admin mode user db2admin;
$ db2 CONNECT TO REX;
$ db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
$ db2 CONNECT RESET;
$ db2 FORCE APPLICATION ALL;
$ db2 DEACTIVATE DB REX;

-- Just checking...
$ db2 list application

$ db2 BACKUP DATABASE REX TO "/tmp" WITH 2 BUFFERS BUFFER 1024
PARALLELISM 1 WITHOUT PROMPTING;

-- verify we can get back in
$ db2 CONNECT TO REX;
$ db2 UNQUIESCE DATABASE;
$ db2 CONNECT RESET;
$ db2stop force
$ db2start
$ db2 connect to rex;

Now, this should have taken an OFFLINE backup, correct? Yet, when I try
to restore it into the test environment:

$ db2 create db rex alias testrex;

$ db2 restore db rex from /tmp taken at 'yyyymmddhhmmss' on
'/db/rextest' into testrex newlogpath '/translogs/rextest' redirect
without rolling forward;

I get:

"SQL2537N Roll-forward is required following the Restore."

Err, what? I thought/read that only happens if I work with an ONLINE
backup! How did my supposed OFFline backup turn into an ONline one?

What am I missing here? Is there another mystery incantation I have to
mumble to enforce that absolutely, totally, certainly, assuredly makes
sure that my backup is offline so that I can "ignore" the logs and not
have to do a rollforward?

Thanks
Mike

[Kiran Nair]

may be complete or stop key work is needed and archival logging is
enabled.
On Aug 14, 7:26 am, Michael Hoffmann <[e-mail]:

Hello,

I hope this doesn't come across as totally newbieish, but I've been
searching the web and the IBM docs until smoke is coming out of my head.

Scenario: We are trying to take primitive "snapshots" of our production
database into a test environment by taking (what we thought are) offline
backups of production, then doing a redirected restore into test.

Here's the steps I do:

-- Make absolutely sure there's nobody connected but the admin.
$ db2stop force;
$ db2start admin mode user db2admin;
$ db2 CONNECT TO REX;
$ db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
$ db2 CONNECT RESET;
$ db2 FORCE APPLICATION ALL;
$ db2 DEACTIVATE DB REX;

-- Just checking...
$ db2 list application

$ db2 BACKUP DATABASE REX TO "/tmp" WITH 2 BUFFERS BUFFER 1024
PARALLELISM 1 WITHOUT PROMPTING;

-- verify we can get back in
$ db2 CONNECT TO REX;
$ db2 UNQUIESCE DATABASE;
$ db2 CONNECT RESET;
$ db2stop force
$ db2start
$ db2 connect to rex;

Now, this should have taken an OFFLINE backup, correct? Yet, when I try
to restore it into the test environment:

$ db2 create db rex alias testrex;

$ db2 restore db rex from /tmp taken at 'yyyymmddhhmmss' on
'/db/rextest' into testrex newlogpath '/translogs/rextest' redirect
without rolling forward;

>

I get:

"SQL2537N Roll-forward is required following the Restore."

Err, what? I thought/read that only happens if I work with an ONLINE
backup! How did my supposed OFFline backup turn into an ONline one?

What am I missing here? Is there another mystery incantation I have to
mumble to enforce that absolutely, totally, certainly, assuredly makes
sure that my backup is offline so that I can "ignore" the logs and not
have to do a rollforward?

Thanks
Mike

[Mark A]

"Michael Hoffmann" <bl*@blather.comnwrote in message
news:13*************@corp.supernews.com...

Hello,

I hope this doesn't come across as totally newbieish, but I've been
searching the web and the IBM docs until smoke is coming out of my head.

Scenario: We are trying to take primitive "snapshots" of our production
database into a test environment by taking (what we thought are) offline
backups of production, then doing a redirected restore into test.

Here's the steps I do:

-- Make absolutely sure there's nobody connected but the admin.
$ db2stop force;
$ db2start admin mode user db2admin;
$ db2 CONNECT TO REX;
$ db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
$ db2 CONNECT RESET;
$ db2 FORCE APPLICATION ALL;
$ db2 DEACTIVATE DB REX;

-- Just checking...
$ db2 list application

$ db2 BACKUP DATABASE REX TO "/tmp" WITH 2 BUFFERS BUFFER 1024 PARALLELISM
1 WITHOUT PROMPTING;

-- verify we can get back in
$ db2 CONNECT TO REX;
$ db2 UNQUIESCE DATABASE;
$ db2 CONNECT RESET;
$ db2stop force
$ db2start
$ db2 connect to rex;

Now, this should have taken an OFFLINE backup, correct? Yet, when I try to
restore it into the test environment:

$ db2 create db rex alias testrex;

$ db2 restore db rex from /tmp taken at 'yyyymmddhhmmss' on '/db/rextest'
into testrex newlogpath '/translogs/rextest' redirect without rolling
forward;

I get:

"SQL2537N Roll-forward is required following the Restore."

Err, what? I thought/read that only happens if I work with an ONLINE
backup! How did my supposed OFFline backup turn into an ONline one?

What am I missing here? Is there another mystery incantation I have to
mumble to enforce that absolutely, totally, certainly, assuredly makes
sure that my backup is offline so that I can "ignore" the logs and not
have to do a rollforward?

Thanks
Mike

Where are your "set tablespace containers" statrements, followed by "restore
db db-name continue"

[Michael Hoffmann]

Mark A wrote:

>
Where are your "set tablespace containers" statrements, followed by "restore
db db-name continue"

Well, if the first restore statement fails, then I don't even get to the
"continue" one.

Mike

[Michael Hoffmann]

Kiran Nair wrote:

may be complete or stop key work is needed and archival logging is
enabled.

Archival logging is set on the database being backed up.

What I'm not clear on is whether I need to activate it on the target DB
after I create it, but before I begin the restore.

As someone else asked about tablespace creation: I was under the
impression (misapprehension?) that a restore from an offline DB would
create a complete replica, down to the tablespace setup?

Though I'm not sure what that affects here: I am getting an error
message on an offline DB that I should only get if I created an online
backup.

That's what confuses me.

Mike

[Keith]

On Aug 14, 6:46 am, Michael Hoffmann <[e-mail]:

Kiran Nair wrote:

may be complete or stop key work is needed and archival logging is
enabled.

Archival logging is set on the database being backed up.

What I'm not clear on is whether I need to activate it on the target DB
after I create it, but before I begin the restore.

As someone else asked about tablespace creation: I was under the
impression (misapprehension?) that a restore from an offline DB would
create a complete replica, down to the tablespace setup?

Though I'm not sure what that affects here: I am getting an error
message on an offline DB that I should only get if I created an online
backup.

That's what confuses me.

Mike

Pls see if this is a possibility:

sql2537 indicates that you are trying to use an online image to
restore (with w/o RF). Looking at the steps you've done, the only
possibility (that I can think of) for that is the time stamp used in
restore cmd. You may have other images in /tmp and may not have used
the time timestamp of the offline image that you just took.

Keith Ponnapalli
IBM Certified Advanced Database Administrator - DB2 UDB V8.1 for LUW
INFORMIX Certified Database Administrator

[jkraj83]

On Aug 13, 11:11 pm, "Mark A" <[e-mail]:

"Michael Hoffmann" <[e-mail] in message

news:13*************@corp.supernews.com...

Hello,

I hope this doesn't come across as totally newbieish, but I've been
searching the web and the IBM docs until smoke is coming out of my head.

Scenario: We are trying to take primitive "snapshots" of our production
database into a test environment by taking (what we thought are) offline
backups of production, then doing a redirected restore into test.

Here's the steps I do:

-- Make absolutely sure there's nobody connected but the admin.
$ db2stop force;
$ db2start admin mode user db2admin;
$db2CONNECT TO REX;
$db2QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
$db2CONNECT RESET;
$db2FORCE APPLICATION ALL;
$db2DEACTIVATE DB REX;

-- Just checking...
$db2list application

$db2BACKUP DATABASE REX TO "/tmp" WITH 2 BUFFERS BUFFER 1024 PARALLELISM
1 WITHOUT PROMPTING;

-- verify we can get back in
$db2CONNECT TO REX;
$db2UNQUIESCE DATABASE;
$db2CONNECT RESET;
$ db2stop force
$ db2start
$db2connect to rex;

Now, this should have taken an OFFLINE backup, correct? Yet, when I try to
restore it into the test environment:

$db2create db rex alias testrex;

$db2restore db rex from /tmp taken at 'yyyymmddhhmmss' on '/db/rextest'
into testrex newlogpath '/translogs/rextest' redirect without rolling
forward;

I get:

"SQL2537N Roll-forward is required following the Restore."

Err, what? I thought/read that only happens if I work with an ONLINE
backup! How did my supposed OFFline backup turn into an ONline one?

What am I missing here? Is there another mystery incantation I have to
mumble to enforce that absolutely, totally, certainly, assuredly makes
sure that my backup is offline so that I can "ignore" the logs and not
have to do a rollforward?

Thanks
Mike

Where are your "set tablespace containers" statrements, followed by "restore
db db-name continue"- Hide quoted text -

- Show quoted text -

------------------------------------------------------------------------------------------------------------------------

wondering how its...I will suggest to try take out the option without
rolling forward, restore and give db2 rollforward db stop.
May be it will help....

[Michael Hoffmann]

Keith wrote:

sql2537 indicates that you are trying to use an online image to
restore (with w/o RF). Looking at the steps you've done, the only
possibility (that I can think of) for that is the time stamp used in
restore cmd. You may have other images in /tmp and may not have used
the time timestamp of the offline image that you just took.

Hi Keith,

Thanks for that. I've no been playing around with various options and
scenarios to a point where I can barely make out my own notes.

I've now been trying to test with both ON and OFFline backups and am
getting weird results.

In the ONline case, I thought it would be straight-forward:

I'd do this on the source instance:

$ db2 BACKUP DATABASE REX ONLINE TO "/tmp" WITH 2 BUFFERS BUFFER 4096
PARALLELISM 1 INCLUDE LOGS WITHOUT PROMPTING;

Note that there's not much "complexity" in this source DB - pretty much
standard, default tablespaces, all set to auto-maintenance/optimization.

And then this on the target instance:

<any vestige of target instance is removed and uncataloged - I even
tested with a "fresh new instance".>

$ db2 restore db rex from /tmp taken at 'yyyymmddhhmmss' on \
'/db/rextest' into testrex newlogpath '/translogs/rextest' redirect;
$ db2 restore db rex continue;

$ db2 rollforward db testrex to end of logs;
$ db2 rollforward db testrex stop;

Or so, I try. I get an error message at the first rollfoward telling me
that it can't find the the logfiles! As if it had ignored the "include
logs" option.

Then, I try the OFFline case, and now get quite a bit further:

<first quiesce, force all connections off, etc>

$ db2 BACKUP DATABASE REX TO "/tmp" WITH 2 BUFFERS BUFFER 4096
PARALLELISM 1 WITHOUT PROMPTING;

Then again at the target (fresh as above), I've played with these two cases:

$ db2 restore db rex from /tmp taken at 'yyyymmddhhmmss' on \
'/db/rextest' into testrex newlogpath '/translogs/rextest' redirect \
without rolling forward;

Which seems to work, except there's this paranoid streak in me asking
whether this offline backup really is a fully rolled forward version
and/or whether my restore is now missing the most recent transactions of
the active logs, because I told it "without rolling forward".

The reason for this paranoia is because, just for the heck of it, I
tried this:

Backup as above, but the restore with this:

$ db2 restore db rex from /tmp taken at 'yyyymmddhhmmss' on \
'/db/rextest' into testrex newlogpath '/translogs/rextest' redirect;
$ db2 restore db rex continue;
$ db2 rollforward db testrex to end of logs;
$ db2 rollforward db testrex stop;

And the rollforward goes into "deep meditation" for quite a bit, which I
naively assume to mean it *is* doing something roll-forwardish.

What is is rolling forward in an OFFLine backup?! And if there's logs to
roll forward, what does that mean when I do a "without rolling forward"
restore on an offline DB?

So, I now have two scenarios, one which doesn't work the other which
kinda works but I'm not sure I trust the results. (if we look at this in
the light of not just a replication scenario, but as a disaster recovery
restore situation).

My head hurts...
Mike

PS: To clarify just a minor thing, in the above the rexprod instance has
a rex database with alias prodrex, the rextest instance has a rex
database with alias testrex.

[Keith]

On Aug 15, 6:35 pm, Michael Hoffmann <[e-mail]:

Keith wrote:

sql2537 indicates that you are trying to use an online image to
restore (with w/o RF). Looking at the steps you've done, the only
possibility (that I can think of) for that is the time stamp used in
restore cmd. You may have other images in /tmp and may not have used
the time timestamp of the offline image that you just took.

Hi Keith,

Thanks for that. I've no been playing around with various options and
scenarios to a point where I can barely make out my own notes.

I've now been trying to test with both ON and OFFline backups and am
getting weird results.

In the ONline case, I thought it would be straight-forward:

I'd do this on the source instance:

$ db2 BACKUP DATABASE REX ONLINE TO "/tmp" WITH 2 BUFFERS BUFFER 4096
PARALLELISM 1 INCLUDE LOGS WITHOUT PROMPTING;

Note that there's not much "complexity" in this source DB - pretty much
standard, default tablespaces, all set to auto-maintenance/optimization.

And then this on the target instance:

<any vestige of target instance is removed and uncataloged - I even
tested with a "fresh new instance".>

$ db2 restore db rex from /tmp taken at 'yyyymmddhhmmss' on \
'/db/rextest' into testrex newlogpath '/translogs/rextest' redirect;
$ db2 restore db rex continue;

$ db2 rollforward db testrex to end of logs;
$ db2 rollforward db testrex stop;

Or so, I try. I get an error message at the first rollfoward telling me
that it can't find the the logfiles! As if it had ignored the "include
logs" option.

Then, I try the OFFline case, and now get quite a bit further:

<first quiesce, force all connections off, etc>

$ db2 BACKUP DATABASE REX TO "/tmp" WITH 2 BUFFERS BUFFER 4096
PARALLELISM 1 WITHOUT PROMPTING;

Then again at the target (fresh as above), I've played with these two cases:

$ db2 restore db rex from /tmp taken at 'yyyymmddhhmmss' on \
'/db/rextest' into testrex newlogpath '/translogs/rextest' redirect \
without rolling forward;

Which seems to work, except there's this paranoid streak in me asking
whether this offline backup really is a fully rolled forward version
and/or whether my restore is now missing the most recent transactions of
the active logs, because I told it "without rolling forward".

The reason for this paranoia is because, just for the heck of it, I
tried this:

Backup as above, but the restore with this:

$ db2 restore db rex from /tmp taken at 'yyyymmddhhmmss' on \
'/db/rextest' into testrex newlogpath '/translogs/rextest' redirect;
$ db2 restore db rex continue;
$ db2 rollforward db testrex to end of logs;
$ db2 rollforward db testrex stop;

And the rollforward goes into "deep meditation" for quite a bit, which I
naively assume to mean it *is* doing something roll-forwardish.

What is is rolling forward in an OFFLine backup?! And if there's logs to
roll forward, what does that mean when I do a "without rolling forward"
restore on an offline DB?

So, I now have two scenarios, one which doesn't work the other which
kinda works but I'm not sure I trust the results. (if we look at this in
the light of not just a replication scenario, but as a disaster recovery
restore situation).

My head hurts...
Mike

PS: To clarify just a minor thing, in the above the rexprod instance has
a rex database with alias prodrex, the rextest instance has a rex
database with alias testrex.

For Online case w/ include logs:

Look into LOGTARGET parameter of restore and use that in RF

Keith Ponnapalli
IBM Certified Advanced Database Administrator - DB2 UDB V8.1 for LUW
INFORMIX Certified Database Administrator

[Michael Hoffmann]

Keith wrote:

>
For Online case w/ include logs:

Look into LOGTARGET parameter of restore and use that in RF

Thank you! Thank you! Thank you!

That did the trick! :-D

Now, all I need to do is figure out how to pass custom parameters to the
automatic maintenance backup, so I can have it do online backups with
INCLUDE LOGS and I'll breathe a lot easier.

Regards
Mike

[Ian]

Michael Hoffmann wrote:

Keith wrote:

>>
For Online case w/ include logs:

Look into LOGTARGET parameter of restore and use that in RF

Thank you! Thank you! Thank you!

That did the trick! :-D

Now, all I need to do is figure out how to pass custom parameters to the
automatic maintenance backup, so I can have it do online backups with
INCLUDE LOGS and I'll breathe a lot easier.

One other note: With an offline backup, you have the option of
specifying 'WITHOUT ROLLING FORWARD' in the 'RESTORE DATABASE' command.
If you don't specify this -- then you can bring your database online
with the command, 'rollforward database <xcomplete'.

Also note (since no one else seemed to touch on this): You can
rollforward a database that is enabled for forward recovery regardless
of whether the backup was online or offline. By default (i.e. without
using the option mentioned above) DB2 will leave the database in
rollforward pending state.
Ian Bjorhovde