Re: Restore + Logical log - caution
Discussion of why an ontape level-0 archive includes logical logs (e.g. "This tape contains the following logical logs: 129537 - 129634"), so a logical restore asks for log tapes going back to a log already backed up hours earlier. Madison Pruet (IBM) explained this is long-standing, expected behaviour: the archive checkpoint is taken at the start of the backup, and the logs spanning transactions open at that point must be kept so those transactions can be rolled back/forward during restore; only the logs needed for a valid restore are included. A PMR confirmed it as working as designed, with an APAR raised only to document it. Mark's efficiency concern about mounting a tape for one log drew no further fix.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I read the APAR and I am left wondering what's the purpose of putting the logical logs that encompass the open trx on the archive tape to begin with ? Is it requiring just one log prior the archive checkpoint or all that have an open transaction ? Mark
Because otherwise it would not be possible to rollback the transaction. The backup is taken as of the archive checkpoint which is done at the START of the backup. At that point in time, we don't know if the transaction will commit or rollback. If it should rollback, then any restore of that archive would result in corruption because the rollback would not be able to be performed. M.P. From: "MARK JALKIEWICZ" <mark.jalkiewicz@verizon.net> To: ids@iiug.org Date: 01/20/2012 01:41 PM Subject: Re: Restore + Logical log - caution [26002] Sent by: ids-bounces@iiug.org I read the APAR and I am left wondering what's the purpose of putting the logical logs that encompass the open trx on the archive tape to begin with ? Is it requiring just one log prior the archive checkpoint or all that have an open transaction ? Mark ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
I don't write the engine. I don't even work for IBM. But, when you start an archive, what is in the DB at the start of an archive. I have no doubt that some pages have a timestamp prior to the start of the archive ( so they need to be included in the archive ) but also have rows on them that have yet to be committed. Think about LRU and all the other pieces of the DB engine. Now assume that after the start of the archive and after the end of the archive that the user causes the transaction to be rolled back. All the changed rows that were part of that transaction are put back to what they looked like prior to the start of the transaction....... by using the data in the logical logs and undoing the changes.... Now suppose you restore you DB using the level 0 archive..... and you are rolling through the logical logs...... Where is the data to undo that transaction on the page that had already been written, with uncommitted data on it when the archive started? Its not in the DB those rows were part of the archive. Its undo is only in the logical logs the ones that were written when the update/insert/delete on the row was done. What should the DB do if it does not have that information? If you think about this a while it should become clear. Just remember that a transaction can span many logical logs or few. A transaction can be larger then the entire buffer pool ( although that would not be ideal ) a transaction can span many hours even many days..... ( although this too would be very bad practice and design ) Just work through the issue as if you were writing the engine. From: "MARK JALKIEWICZ" <mark.jalkiewicz@verizon.net> To: ids@iiug.org Date: 01/20/2012 01:40 PM Subject: Re: Restore + Logical log - caution [26002] Sent by: ids-bounces@iiug.org I read the APAR and I am left wondering what's the purpose of putting the logical logs that encompass the open trx on the archive tape to begin with ? Is it requiring just one log prior the archive checkpoint or all that have an open transaction ? Mark ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
But, Madison, isn't it the case that since the transaction is open the logical log that contains the begin work must be available online in the dbspace that stores the logical logs? Or are you allowing for the possibility that the transaction may have completed and the logical log overwritten between the beginning of the archive and actually copying the logical log dbspace out to the archive? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Jan 20, 2012 at 3:07 PM, Madison Pruet <mpruet@us.ibm.com> wrote: > Because otherwise it would not be possible to rollback the transaction. > The backup is taken as of the archive checkpoint which is done at the START > of the backup. At that point in time, we don't know if the transaction > will commit or rollback. If it should rollback, then any restore of that > archive would result in corruption because the rollback would not be able > to be performed. > > M.P. > > From: "MARK JALKIEWICZ" <mark.jalkiewicz@verizon.net> > > To: ids@iiug.org > > Date: 01/20/2012 01:41 PM > > Subject: Re: Restore + Logical log - caution [26002] > > Sent by: ids-bounces@iiug.org > > I read the APAR and I am left wondering what's the purpose of putting the > logical logs that encompass the open trx on the archive tape to begin > with ? > > Is it requiring just one log prior the archive checkpoint or all that have > an > open transaction ? > > Mark > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae934086376572704b6fbde44
true for the physical restore only.. not questioning that point. what I am questioning is the requirement (logical restore) for a user to insert a logical log tape that has transactions on it that are already on the archive tape seems to be not very efficient. What if it only needs one log from that tape and that log is at the tail end of the tape ? Might be a bit petty but u get the point.
George, the physical log images written at the end of each dbspace's archive take care of the scenario you mentioned. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Jan 20, 2012 at 3:48 PM, George_Palmer@aotx.uscourts.gov < George_Palmer@aotx.uscourts.gov> wrote: > I don't write the engine. I don't even work for IBM. > > But, when you start an archive, what is in the DB at the start of an > archive. I have no doubt that some pages have a timestamp prior to the > start of the archive ( so they need to be included in the archive ) but > also have rows on them that have yet to be committed. Think about LRU and > all the other pieces of the DB engine. > > Now assume that after the start of the archive and after the end of the > archive that the user causes the transaction to be rolled back. All the > changed rows that were part of that transaction are put back to what they > looked like prior to the start of the transaction....... by using the data > in the logical logs and undoing the changes.... > > Now suppose you restore you DB using the level 0 archive..... and you are > rolling through the logical logs...... Where is the data to undo that > transaction on the page that had already been written, with uncommitted > data on it when the archive started? Its not in the DB those rows were > part of the archive. Its undo is only in the logical logs the ones that > were written when the update/insert/delete on the row was done. What > should the DB do if it does not have that information? > > If you think about this a while it should become clear. Just remember that > a transaction can span many logical logs or few. A transaction can be > larger then the entire buffer pool ( although that would not be ideal ) a > transaction can span many hours even many days..... ( although this too > would be very bad practice and design ) > > Just work through the issue as if you were writing the engine. > > From: "MARK JALKIEWICZ" <mark.jalkiewicz@verizon.net> > To: ids@iiug.org > Date: 01/20/2012 01:40 PM > Subject: Re: Restore + Logical log - caution [26002] > Sent by: ids-bounces@iiug.org > > I read the APAR and I am left wondering what's the purpose of putting the > logical logs that encompass the open trx on the archive tape to begin > with ? > > Is it requiring just one log prior the archive checkpoint or all that have > an > open transaction ? > > Mark > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e889220a70a04b6fbed14
The origin of this thread included the following.... -------------------------------------------------------------------------------- -------- e.g. when the the archive bellow start running, the current log is 129634 , the log 129537 was backuped 5 hours early. If I try restore this Archive with logical logs, the engine will request the files/tapes since log 129537. Please enter the level of archive to be performed (0, 1, or 2) 0 Please mount tape 1 on /dev/rmt0 and press Return to continue ... 10 percent done. 20 percent done. 30 percent done. 40 percent done. 50 percent done. 60 percent done. 70 percent done. 80 percent done. 100 percent done. Read/Write End Of Medium enabled: blocks = 307955 Please label this tape as number 1 in the arc tape sequence. This tape contains the following logical logs: 129537 - 129634 Program over. A PMR was opened over this behave and answered as expected behave. Was created a APAR over the documentation (this behave wasn't documented before). ------------------------------------------------------------------------- Yes - the active log files are included in the archive - and have been since IDS was first called online. Nothing new - expected behavior - and has been for years. N.B. --- we don't include ALL of the logical log files - only those which are needed for valid restore. And we include a message to say which logical log files were included. M.P. From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 01/20/2012 02:54 PM Subject: Re: Restore + Logical log - caution [26005] Sent by: ids-bounces@iiug.org But, Madison, isn't it the case that since the transaction is open the logical log that contains the begin work must be available online in the dbspace that stores the logical logs? Or are you allowing for the possibility that the transaction may have completed and the logical log overwritten between the beginning of the archive and actually copying the logical log dbspace out to the archive? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Jan 20, 2012 at 3:07 PM, Madison Pruet <mpruet@us.ibm.com> wrote: > Because otherwise it would not be possible to rollback the transaction. > The backup is taken as of the archive checkpoint which is done at the START > of the backup. At that point in time, we don't know if the transaction > will commit or rollback. If it should rollback, then any restore of that > archive would result in corruption because the rollback would not be able > to be performed. > > M.P. > > From: "MARK JALKIEWICZ" <mark.jalkiewicz@verizon.net> > > To: ids@iiug.org > > Date: 01/20/2012 01:41 PM > > Subject: Re: Restore + Logical log - caution [26002] > > Sent by: ids-bounces@iiug.org > > I read the APAR and I am left wondering what's the purpose of putting the > logical logs that encompass the open trx on the archive tape to begin > with ? > > Is it requiring just one log prior the archive checkpoint or all that have > an > open transaction ? > > Mark > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae934086376572704b6fbde44 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.