[Info-Ingres] closed journals and incremental rollforwarddb
Martin Bowes
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
Paul White
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
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...@ingres.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
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-ingr...@kettleriverconsulting.com [mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Paul White
Sent: 19 June 2009 15:00
To: 'Ingres and related product discussion forum'
Cc: 'Michael Flower'
From: info-ingr...@kettleriverconsulting.com [mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Martin Bowes
To: Ingres and related product discussion forum
Cc: Michael Flower
Subject: [Info-Ingres] closed journals and incremental rollforwarddb
Michael Flower
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...@ctsu.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
Michael Flower
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
From: Martin Bowes
[mailto:martin...@ctsu.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
Ingres Forums
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
------------------------------------------------------------------------
daslu01's Profile: http://community.ingres.com/forum/member.php?userid=1056
View this thread: http://community.ingres.com/forum/showthread.php?t=10820
Armand Pirvu
Alterdb -next_jnl_file will close the journal and in 9.2 will also generate the 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 4A3C4443 0009B3FE 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 and closes 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 Jun 19 19:29:20 2009 RFP: Journal file sequence 3 not found -- skipped.
done with 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 Journal for 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 debugging tool (or set of tools) would be to use '#x' with rolldb . It will generate an 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
A
P.S. I guess I will be registering my ingres.com e-mail as well.
From: Martin Bowes <martin...@ctsu.ox.ac.uk>
To: Ingres and related product discussion forum <info-...@kettleriverconsulting.com>
Cc: Michael Flower <Michael...@ingres.com>
Sent: Friday, June 19, 2009 8:41:23 AM
Subject: [Info-Ingres] closed journals and incremental rollforwarddb
Martin Bowes
Hi Armand,
> 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 ?
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.
> 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.
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!
> 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.
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
Ingres Forums
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....@barclays.com
------------------------------------------------------------------------
nick....@barclays.com's Profile: http://community.ingres.com/forum/member.php?userid=733
View this thread: http://community.ingres.com/forum/showthread.php?t=10829
Armand Pirvu
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...@ctsu.ox.ac.uk>
To: Ingres and related product discussion forum <info-...@kettleriverconsulting.com>
Sent: Monday, June 22, 2009 4:04:34 AM
Subject: RE: [Info-Ingres] closed journals and incremental rollforwarddb
Michael Flower
Hello Marty,
The bug number for this is 122216.
We’ll send out some information shortly about a webinar on this topic - July 22nd looks a probable date (it’ll be 2-0 in the Ashes by then! ).
Regards, Mike
From: Martin Bowes
[mailto:martin...@ctsu.ox.ac.uk]
To: Ingres and related product discussion forum
Cc: Michael Flower
Subject: closed journals and incremental rollforwarddb