Hi All,



I've just started tinkering with the incremental rollforwarddb in
9.2.0...and gee its sweet!



But...when is a source journal closed off?



I'm currently running alterdb -next_jnl_file on the source database
before transferring journals to my target host.

All of which seems cool. It closes the current journal and starts a new
one, the incremental rollforwarddb then proceeds as expected straight
out of the box with no tinkering.



But when I ckpdb the source database, run the journal transfer and try
an incremental rollforwarddb on the target it barfs at the journal
before the checkpoint, complaining that it is not closed. Subsequent
journal transfers and incrementals stall at this journal and refuse to
go past this point.



Surely the checkpoint would close the journal regardless of whether it
was an online/ofline checkpoint?



So my attempts at an incremental rollforwarddb stop as soon as I
checkpoint the source database. I have to restart the process from
scratch.



Comments from anyone?



Martin Bowes

Hi Marty,



What flags are you running on the rollforwarddb?

-incremental -c +j ?

-norollback ?



I found I could roll forward on the standby server only with +c +j

but then I have been shipping journals from 9.1 to 9.2 which I was surprised
would work at all

(Upgrade is planned soon)



To flush the next journal I use these steps.



sql iidbdb<<EOF

SET TRACE POINT DM1305;

\g

COMMIT;

\g

EOF



sleep 5



sql iidbdb<<EOF

\g

SET TRACE POINT DM1314;

\g

COMMIT;

EOF



alterdb dbname -next_jnl_file



Paul





From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Martin
Bowes
Sent: Friday, 19 June 2009 11:41 PM
To: Ingres and related product discussion forum
Cc: Michael Flower
Subject: [Info-Ingres] closed journals and incremental rollforwarddb



Hi All,



I've just started tinkering with the incremental rollforwarddb in 9.2.0.and
gee its sweet!



But.when is a source journal closed off?



I'm currently running alterdb -next_jnl_file on the source database before
transferring journals to my target host.

All of which seems cool. It closes the current journal and starts a new one,
the incremental rollforwarddb then proceeds as expected straight out of the
box with no tinkering.



But when I ckpdb the source database, run the journal transfer and try an
incremental rollforwarddb on the target it barfs at the journal before the
checkpoint, complaining that it is not closed. Subsequent journal transfers
and incrementals stall at this journal and refuse to go past this point.



Surely the checkpoint would close the journal regardless of whether it was
an online/ofline checkpoint?



So my attempts at an incremental rollforwarddb stop as soon as I checkpoint
the source database. I have to restart the process from scratch.



Comments from anyone?



Martin Bowes

Hello Marty,

Glad that the IUA presentation gave you lots of inspiration!



A source journal is not 'closed' by a ckpdb in a manner that the
-incremental feature can detect.



I raised this point internally (a week before my IUA presentation).
There should be the ability to incrementally rollforward through all
journals (regardless of missing intermittent checkpoints). It's
currently being investigated.



A workaround is to use the flag -on_error_prompt

This will provide you with the ability to rollforward a non-closed
Journal. Not ideal, but it should save you having to copy across all the
files from the new checkpoint.



Regards, Mike





From: Martin Bowes [mailto:martin.bowes (AT) ctsu (DOT) ox.ac.uk]
Sent: 19 June 2009 14:41
To: Ingres and related product discussion forum
Cc: Michael Flower
Subject: closed journals and incremental rollforwarddb



Hi All,



I've just started tinkering with the incremental rollforwarddb in
9.2.0...and gee its sweet!



But...when is a source journal closed off?



I'm currently running alterdb -next_jnl_file on the source database
before transferring journals to my target host.

All of which seems cool. It closes the current journal and starts a new
one, the incremental rollforwarddb then proceeds as expected straight
out of the box with no tinkering.



But when I ckpdb the source database, run the journal transfer and try
an incremental rollforwarddb on the target it barfs at the journal
before the checkpoint, complaining that it is not closed. Subsequent
journal transfers and incrementals stall at this journal and refuse to
go past this point.



Surely the checkpoint would close the journal regardless of whether it
was an online/ofline checkpoint?



So my attempts at an incremental rollforwarddb stop as soon as I
checkpoint the source database. I have to restart the process from
scratch.



Comments from anyone?



Martin Bowes

Hi Mike,



That IUA presentation you gave couldn't have come at a better time. I'd
been waiting to get my grubby little paws on 9.2.0 for the incremental
.... and had it on my list O'things to do.



It'd be really sweet if this could be solved properly as you indicate.
Do you have a bug number I can track?



One of the problems I expect to face is that I'm will be doing this on
very large databases, things with checkpoint tar files of at least
500+G. I don't want to have to flop those on the network if I can avoid
it. Furthermore, my next item on the List O'things is to implement fast
backups using the SAN snapshot technique. So I wont have an actual
checkpoint tar file in most cases. More hacking of cktmpl.def is on the
way!



The -on_error_prompt sounds like what I need for the moment...although
I'm trying to run this as a batch program so I can see some extra
jiggery-pokery coming on there too.



Marty



From: Michael Flower [mailto:Michael.Flower (AT) ingres (DOT) com]
Sent: 19 June 2009 15:04
To: Martin Bowes; Ingres and related product discussion forum
Subject: RE: closed journals and incremental rollforwarddb



Hello Marty,

Glad that the IUA presentation gave you lots of inspiration!



A source journal is not 'closed' by a ckpdb in a manner that the
-incremental feature can detect.



I raised this point internally (a week before my IUA presentation).
There should be the ability to incrementally rollforward through all
journals (regardless of missing intermittent checkpoints). It's
currently being investigated.



A workaround is to use the flag -on_error_prompt

This will provide you with the ability to rollforward a non-closed
Journal. Not ideal, but it should save you having to copy across all the
files from the new checkpoint.



Regards, Mike





From: Martin Bowes [mailto:martin.bowes (AT) ctsu (DOT) ox.ac.uk]
Sent: 19 June 2009 14:41
To: Ingres and related product discussion forum
Cc: Michael Flower
Subject: closed journals and incremental rollforwarddb



Hi All,



I've just started tinkering with the incremental rollforwarddb in
9.2.0...and gee its sweet!



But...when is a source journal closed off?



I'm currently running alterdb -next_jnl_file on the source database
before transferring journals to my target host.

All of which seems cool. It closes the current journal and starts a new
one, the incremental rollforwarddb then proceeds as expected straight
out of the box with no tinkering.



But when I ckpdb the source database, run the journal transfer and try
an incremental rollforwarddb on the target it barfs at the journal
before the checkpoint, complaining that it is not closed. Subsequent
journal transfers and incrementals stall at this journal and refuse to
go past this point.



Surely the checkpoint would close the journal regardless of whether it
was an online/ofline checkpoint?



So my attempts at an incremental rollforwarddb stop as soon as I
checkpoint the source database. I have to restart the process from
scratch.



Comments from anyone?



Martin Bowes

Hello Marty,

I don't have a bug number yet. When I get one, I'll pass it on.



I agree with your thoughts about -on_error_prompt . It doesn't fit well
in a batch script scenario.

I'm going to give a webinar about the incremental rollforward soon. Hope
you're going to listen.

Regards, Mike



Michael Flower

Director Education Services

Ingres Europe Limited

michael.flower (AT) ingres (DOT) com <mailto:michael.flower (AT) ingres (DOT) com>



From: Martin Bowes [mailto:martin.bowes (AT) ctsu (DOT) ox.ac.uk]
Sent: 19 June 2009 15:14
To: Michael Flower; Ingres and related product discussion forum
Subject: RE: closed journals and incremental rollforwarddb



Hi Mike,



That IUA presentation you gave couldn't have come at a better time. I'd
been waiting to get my grubby little paws on 9.2.0 for the incremental
.... and had it on my list O'things to do.



It'd be really sweet if this could be solved properly as you indicate.
Do you have a bug number I can track?



One of the problems I expect to face is that I'm will be doing this on
very large databases, things with checkpoint tar files of at least
500+G. I don't want to have to flop those on the network if I can avoid
it. Furthermore, my next item on the List O'things is to implement fast
backups using the SAN snapshot technique. So I wont have an actual
checkpoint tar file in most cases. More hacking of cktmpl.def is on the
way!



The -on_error_prompt sounds like what I need for the moment...although
I'm trying to run this as a batch program so I can see some extra
jiggery-pokery coming on there too.



Marty



From: Michael Flower [mailto:Michael.Flower (AT) ingres (DOT) com]
Sent: 19 June 2009 15:04
To: Martin Bowes; Ingres and related product discussion forum
Subject: RE: closed journals and incremental rollforwarddb



Hello Marty,

Glad that the IUA presentation gave you lots of inspiration!



A source journal is not 'closed' by a ckpdb in a manner that the
-incremental feature can detect.



I raised this point internally (a week before my IUA presentation).
There should be the ability to incrementally rollforward through all
journals (regardless of missing intermittent checkpoints). It's
currently being investigated.



A workaround is to use the flag -on_error_prompt

This will provide you with the ability to rollforward a non-closed
Journal. Not ideal, but it should save you having to copy across all the
files from the new checkpoint.



Regards, Mike





From: Martin Bowes [mailto:martin.bowes (AT) ctsu (DOT) ox.ac.uk]
Sent: 19 June 2009 14:41
To: Ingres and related product discussion forum
Cc: Michael Flower
Subject: closed journals and incremental rollforwarddb



Hi All,



I've just started tinkering with the incremental rollforwarddb in
9.2.0...and gee its sweet!



But...when is a source journal closed off?



I'm currently running alterdb -next_jnl_file on the source database
before transferring journals to my target host.

All of which seems cool. It closes the current journal and starts a new
one, the incremental rollforwarddb then proceeds as expected straight
out of the box with no tinkering.



But when I ckpdb the source database, run the journal transfer and try
an incremental rollforwarddb on the target it barfs at the journal
before the checkpoint, complaining that it is not closed. Subsequent
journal transfers and incrementals stall at this journal and refuse to
go past this point.



Surely the checkpoint would close the journal regardless of whether it
was an online/ofline checkpoint?



So my attempts at an incremental rollforwarddb stop as soon as I
checkpoint the source database. I have to restart the process from
scratch.



Comments from anyone?



Martin Bowes

Hi Paul,



The initial incremental rollforwarddb is done with: +c -j -incremental
-norollback.

Subsequent incrementals are done with: -c +j -incremental -norollback.

The final (ie deployment) incremental is done with: -c +j -incremental
-rollback.



To restart after the final deployment you have to go back to step 1.



My journals are sourced from a 9.2.0 installation and being transferred
to a 9.2.0 installation. I use alterdb -next_jnl_file on the source
database to ensure the current journal is properly closed and a new one
started before the transfer to the target host.



Marty

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Paul
White
Sent: 19 June 2009 15:00
To: 'Ingres and related product discussion forum'
Cc: 'Michael Flower'
Subject: Re: [Info-Ingres] closed journals and incremental rollforwarddb



Hi Marty,



What flags are you running on the rollforwarddb?

-incremental -c +j ?

-norollback ?



I found I could roll forward on the standby server only with +c +j

but then I have been shipping journals from 9.1 to 9.2 which I was
surprised would work at all

(Upgrade is planned soon)



To flush the next journal I use these steps.



sql iidbdb<<EOF

SET TRACE POINT DM1305;

\g

COMMIT;

\g

EOF



sleep 5



sql iidbdb<<EOF

\g

SET TRACE POINT DM1314;

\g

COMMIT;

EOF



alterdb dbname -next_jnl_file



Paul





From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: Friday, 19 June 2009 11:41 PM
To: Ingres and related product discussion forum
Cc: Michael Flower
Subject: [Info-Ingres] closed journals and incremental rollforwarddb



Hi All,



I've just started tinkering with the incremental rollforwarddb in
9.2.0...and gee its sweet!



But...when is a source journal closed off?



I'm currently running alterdb -next_jnl_file on the source database
before transferring journals to my target host.

All of which seems cool. It closes the current journal and starts a new
one, the incremental rollforwarddb then proceeds as expected straight
out of the box with no tinkering.



But when I ckpdb the source database, run the journal transfer and try
an incremental rollforwarddb on the target it barfs at the journal
before the checkpoint, complaining that it is not closed. Subsequent
journal transfers and incrementals stall at this journal and refuse to
go past this point.



Surely the checkpoint would close the journal regardless of whether it
was an online/ofline checkpoint?



So my attempts at an incremental rollforwarddb stop as soon as I
checkpoint the source database. I have to restart the process from
scratch.



Comments from anyone?



Martin Bowes

Hi Martin,
Maybe you should trasfer just those closed jnls files, for example:
Suppose the current journal has sequence = 1000, then you issue alterdb
-next_jnl_file. At this point, the sequence 1001 will be created, so you
can tranfer all jnls up to sequence 1000.

I hope it helps you.

Luiz


--
daslu01

Hi Marty


For incremental there is a new log record, called JEOF.
Alterdb -next_jnl_file will close the journal and in 9.2 will also generatethe JEOF log record (Journal End Of File). JEOF will be generated also when even without alterdb -next_jnl_file, an archiver cycle occurs , closes the current jnl and opens a new one.

Sample:

LSN=(00000000,00000000), COMP_LSN=(00000000,00000000), DBID=0x4A3C4407, XID=0x0000000000000000
JEOF Length: 64 Flags:
HEADER: 00000040 000000B0 00000000 4A3C4407 00000000 00000000 00000000 00000000 00000000
HEADER: 00000000 00000000 00000000 00000000 00000000 4A3C44430009B3FE 20202020 20202020


If this is not in the journal, -icremental -norollback will ignore the journal. But -rollback will process it

I guess when you say it barfs you mean maybe something like E_DM1377_RFP_INCR_JNL_WARNING ROLLFORWARDDB -incremental -norollback for database bl, ignoring open journal file sequence 4 ?

Build 136 case

createdb bl
create table foo (col1 integer)
ckpdb +j
ckpdb
insert into foo values (100)
insert into foo values (200)

rolldb +c -j -incremental
rolldb -c +j -incremental -norollback
rolldb -c +j -incremental -rollback
ckpdb bl
delete from foo
rolldb -c +j -incremental -norollback
Here I got the E_DM1377 . But in this case is correct, since I did not close the jnl with alterdb for example. And further more when I do a select from foo I still see the records there. And this expected too. The journal was not processed since it lack JEOF and I used norollback.
Rolldb -c +j -incremental -rollback completes and as expected I don't have the records anymore. It processed the last jnl as expected. It also writes the JEOF andcloses it and opens a new one.

rollforwarddb +c -j bl -incremental
.....
rollforwarddb -c +j bl -incremental -norollback
Fri Jun 19 19:29:15 2009 RFP: Searching for incremental jnl 2
beginning restore of journals
Fri Jun 19 19:29:15 2009 RFP: Validating incremental journal 2
Fri Jun 19 19:29:15 2009 E_DM1377_RFP_INCR_JNL_WARNING ROLLFORWARDDB -incremental -norollback for database bl, ignoring open journal file sequence 2
......
rollforwarddb -c +j bl -incremental -rollback
Fri Jun 19 19:29:20 2009 RFP: Searching for incremental jnl 2
beginning restore of journals
Fri Jun 19 19:29:20 2009 RFP: Start processing journal file 2 blkseq 2
Fri Jun 19 19:29:20 2009 RFP: Searching for incremental jnl 3
Fri Jun19 19:29:20 2009 RFP: Journal file sequence 3 not found -- skipped.
donewith disk operations
Fri Jun 19 19:29:20 2009 Completed processing of 2 transactions.
Fri Jun 19 19:29:20 2009 RFP: Switching to the Next Journalfor Database 'bl'. -> jnl 3 gets created here.

Not sure I completely understood your case, but if it is the same and if it still gets stucked you did hit a bug. That is assuming with the new ckp, the new cnf, and dumps are transferred across before running rolldb +c -j -incremental.

Not sure what build you are on. Mine is 136.

An excellent debuggingtool (or set of tools) would be to use '#x' with rolldb . It will generatean rfpdb.dbg file.
Along with it in general I use also auditdb -v -all dbname

On the SAN snapshot, tinkering the ckp template and the scripting is a major component. But once it is done correctly, it is indeed super.
However , even using snapshot, one still needs to backup the snapshot. Even if a relaxed pace, but still it needs to be backed up.
I noticed way too many situations when this is ignored. The consequences are easy to imagine.


Hope this helps

Cheers
A

P.S. I guess I will be registering my ingres.com e-mail as well.



________________________________
From: Martin Bowes <martin.bowes (AT) ctsu (DOT) ox.ac.uk>
To: Ingres andrelated product discussion forum <info-ingres (AT) kettleriverconsulting (DOT) com>
Cc: Michael Flower <Michael.Flower (AT) ingres (DOT) com>
Sent: Friday, June 19, 2009 8:41:23 AM
Subject: [Info-Ingres] closed journals and incremental rollforwarddb


Hi All,

I've just started tinkering with the incremental
rollforwarddb in 9.2.0…and gee its sweet!

But…when is a source journal closed off?

I'm currently running alterdb -next_jnl_file on the source
database before transferring journals tomy target host.
All of which seems cool. It closes the current journal and
starts a new one, the incremental rollforwarddb then proceeds as expected
straight out of the box with no tinkering.

But when I ckpdb thesource database, run the journal
transfer and try an incremental rollforwarddb on the target it barfs at the
journal before the checkpoint, complaining that it is not closed. Subsequent
journal transfers and incrementals stall at this journal and refuse to go past
this point.

Surely the checkpoint would close the journal regardless of
whether it was an online/ofline checkpoint?

So my attempts at an incremental rollforwarddbstop as soon
as I checkpoint the source database. I have to restart the process from
scratch.

Comments from anyone?

Martin Bowes

Mike,

Disappointing that I need to refresh the checkpoints, but incremental
rollforward can still save us up to 30 mins for a DR invocation so I'll
definately be using the feature when we get onto 9.2. Having a
read-only and current standby will also be great for those huge queries
I wouldn't run against a production environment.

Rollforwarddb trhough checkpoints should be a general enhancement. The
inability to use checkpoint #1 to get to checkpoint #6 if I have the
journals (and arguably the dumps?) limits the recovery options for no
good reason. This is also vital for using incremental backups; so it
may arrive without a bug fix?


--
nick.bolton (AT) barclays (DOT) com
------------------------------------------------------------------------
nick.bolton (AT) barclays (DOT) com's Profile: http://community.ingres.com/forum/member.php?userid=733
View this thread: http://community.ingres.com/forum/sh...ad.php?t=10829

Hi Marty

This should be in my opinion a software improvement request.
But it is not only that ckpdb does not write JEOF at last jnl closing.
Seems that rollforwarddb -incremental is not allowed across multiple ckps either

Mon Jun 22 09:54:42 2009 E_DM1373_RFP_INCR_ARG Invalid argument(s) #c for incremental rollforwarddb

Even if I used the last ckp sequence it still gives E_DM1373

The case I showed is not across multiple ckps.
Since jnl4 is open (and it belongs to the most recent ckp) , aka no JEOF record in it, rolldb -incremental -norollback correctly ignores it .

In your case , rolldb has ran across multiple ckps.

I think this is the clue for a software improvement request.

On the SAN, not quite a super high performance thing, but for tinkering you may want to look at openfiler. Some interesting stuff there.
And it is free .

Cheers
Armand


________________________________
From: Martin Bowes <martin.bowes (AT) ctsu (DOT) ox.ac.uk>
To: Ingres and related product discussion forum <info-ingres (AT) kettleriverconsulting (DOT) com>
Cc: Armand Pirvu <armandpirvu (AT) yahoo (DOT) com>
Sent: Monday, June 22, 2009 4:04:34 AM
Subject: RE: [Info-Ingres] closed journals and incremental rollforwarddb


Hi Armand,

-norollback for database bl, ignoring open journal file sequence 4 ?

Correct. And according to Mike
Flower, this is because the ckpdb does not install the JEOF in the journal
before closing it off, and as you point out, the only way it will be processed
is to hit it with -rollback flag.

The consequence of this is that
I have to make my incremental recovery support programs much more intelligent.
They now have to realise that a new checkpoint was taken on the source, and
process accordingly. Shouldn't be difficult, but its something else that needs
to be done.


Yes, now all I have to do is get
a loaner SAN, connect it up and try to make a script that doesn't stall the
damn thing!
The manuals supplied by the
vendor made it sound so simple!



Absolutely! Anyone who doesn't
do that really hasn't thought things through!

I'm going to be interested to
see about replicating the snapshot to another SAN and using that as the
checkpoint base for the incremental recovery

Marty