SEO

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

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


On Aug 14, 7:26 am, Michael Hoffmann <b...@blather.comn> wrote:
> 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

"Michael Hoffmann" <bla@blather.comn> wrote in message
news:13c1q9hqcnf5h2f@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"

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

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

On Aug 14, 6:46 am, Michael Hoffmann <b...@blather.comn> wrote:
> 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

On Aug 13, 11:11 pm, "Mark A" <nob...@nowhere.com> wrote:
> "Michael Hoffmann" <b...@blather.comn> wrote in message
>
> news:13c1q9hqcnf5h2f@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....

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.

On Aug 15, 6:35 pm, Michael Hoffmann <b...@blather.comn> wrote:
> 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

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