Temp log files in rootdbs during warm restore

Greetings, Family. I recently had a misadventure and would like to gain further insight into the issues of concern. My client is on IDS 11.5 (despite 3 years of bugs and nagging to upgrade already! But that's another story.) My client DBA inadvertently blew away a chunk, realizing his mistake an ohno!second later. We therefore had to perform a warm restore of the chunk, followed by the log restore. (We chose to do it in 2 steps.) During the log restore, the rootdbspace filled up and the rollforward operation barfed. When we restarted the process I ran onstat -l to view info on the temp logs. Aha! Every blessed one was in rootdbs, rather than in the expected temp DBspaces specified by the DBSPACETEMP parameter. We had even exported a DBSPACETEMP environment variable with the same values. No help; it just wanted to use the root. What did we do? We quickly added enough chunks to rootdbs to contain the temp logs at the same size of the actual logs, about 18GB. So the chunk recovery completed and we're back in business. Crisis over, I had the chance to think about the WHY; why was it ignoring the DBSPACETEMP and creating the temp logs in the root? I posed the following theory to the Informix tech support on that PMR but he has not replied. So I ask the community. Here is my theory: 1. The onparams command, which we use to add new logical logs, has a default DBspace for each new log: rootdbs. It’s only because onparams has -d option that we can place it in a DBspace of our choosing. 2. The onbar log-restore process creates as many “temp logs” as the actual set. I believe it is creating them using an internal variation of onparams. If so, it is only reasonable - as per the default behavior of onparams - to create them in rootdbs. And no DBSPACETEMP setting, be it in $ONCONFIG or the environment, is going to change that. I also asked the tech if this behavior is more malleable in later releases, like creating the temp logs in DBSPACETEMP spaces, or if there is a parameter or env variable unbeknownst to me, that dictates where the recovery temp logs shall be created. Can anyone out there fill in where my tech chickened out? This has ramifications for any Informix server I configure in the future. Thanks much! -- Jacob Salomon +----------------------------------------------------------------------------+ | I didn't have time to write a short letter, so I wrote a long one instead. | +-------------------------------------------------------------- Mark Twain --+

Hi Jacob, just quickly: those temp log files are created as temp partitions and=20 hence follow "general" temp table allocation rules ... and would be=20 subject to same temp table allocation rule flaws. I'd say one more reason to catch up with time and bring this to a current=20 version ;-) Cheers, Andreas From: "JACOB SALOMON" <jakesalomon@yahoo.com> To: ids@iiug.org Date: 05.12.2016 06:28 Subject: Temp log files in rootdbs during warm restore [38227] Sent by: ids-bounces@iiug.org Greetings, Family.=20 I recently had a misadventure and would like to gain further insight into=20 the=20 issues of concern. My client is on IDS 11.5 (despite 3 years of bugs and=20 nagging to upgrade already! But that's another story.)=20 My client DBA inadvertently blew away a chunk, realizing his mistake an=20 ohno!second later. We therefore had to perform a warm restore of the=20 chunk,=20 followed by the log restore. (We chose to do it in 2 steps.)=20 During the log restore, the rootdbspace filled up and the rollforward=20 operation barfed. When we restarted the process I ran onstat -l to view=20 info=20 on the temp logs. Aha! Every blessed one was in rootdbs, rather than in=20 the=20 expected temp DBspaces specified by the DBSPACETEMP parameter. We had even = exported a DBSPACETEMP environment variable with the same values. No help; = it=20 just wanted to use the root. What did we do? We quickly added enough=20 chunks to=20 rootdbs to contain the temp logs at the same size of the actual logs,=20 about=20 18GB. So the chunk recovery completed and we're back in business.=20 Crisis over, I had the chance to think about the WHY; why was it ignoring=20 the=20 DBSPACETEMP and creating the temp logs in the root? I posed the following=20 theory to the Informix tech support on that PMR but he has not replied. So = I=20 ask the community. Here is my theory:=20 1. The onparams command, which we use to add new logical logs, has a=20 default=20 DBspace for each new log: rootdbs. It?s only because onparams has -d=20 option=20 that we can place it in a DBspace of our choosing.=20 2. The onbar log-restore process creates as many ?temp logs? as the actual = set. I believe it is creating them using an internal variation of=20 onparams. If=20 so, it is only reasonable - as per the default behavior of onparams - to=20 create them in rootdbs. And no DBSPACETEMP setting, be it in $ONCONFIG or=20 the=20 environment, is going to change that.=20 I also asked the tech if this behavior is more malleable in later=20 releases,=20 like creating the temp logs in DBSPACETEMP spaces, or if there is a=20 parameter=20 or env variable unbeknownst to me, that dictates where the recovery temp=20 logs=20 shall be created.=20 Can anyone out there fill in where my tech chickened out? This has=20 ramifications for any Informix server I configure in the future.=20 Thanks much!=20 -- Jacob Salomon=20 +--------------------------------------------------------------------------= --+=20 | I didn't have time to write a short letter, so I wrote a long one=20 instead. |=20 +-------------------------------------------------------------- Mark Twain = --+=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20

I haven't tested this particular situation, but I'm going to guess that the temp space created for this restore is logged. If you don't have some logged dbspaces listed in your DBSPACETEMP, they would be created in the rootdbs by default. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > JACOB SALOMON > Sent: Sunday, December 04, 2016 23:28 PM > To: ids@iiug.org > Subject: Temp log files in rootdbs during warm restore [38227] > > Greetings, Family. > > I recently had a misadventure and would like to gain further insight > into the issues of concern. My client is on IDS 11.5 (despite 3 years > of bugs and nagging to upgrade already! But that's another story.) > > My client DBA inadvertently blew away a chunk, realizing his mistake an > ohno!second later. We therefore had to perform a warm restore of the > chunk, followed by the log restore. (We chose to do it in 2 steps.) > > During the log restore, the rootdbspace filled up and the rollforward > operation barfed. When we restarted the process I ran onstat -l to view > info on the temp logs. Aha! Every blessed one was in rootdbs, rather > than in the expected temp DBspaces specified by the DBSPACETEMP > parameter. We had even exported a DBSPACETEMP environment variable with > the same values. No help; it just wanted to use the root. What did we > do? We quickly added enough chunks to rootdbs to contain the temp logs > at the same size of the actual logs, about 18GB. So the chunk recovery > completed and we're back in business. > > Crisis over, I had the chance to think about the WHY; why was it > ignoring the DBSPACETEMP and creating the temp logs in the root? I > posed the following theory to the Informix tech support on that PMR but > he has not replied. So I ask the community. Here is my theory: > > 1. The onparams command, which we use to add new logical logs, has a > default DBspace for each new log: rootdbs. It's only because onparams > has -d option that we can place it in a DBspace of our choosing. > > 2. The onbar log-restore process creates as many "temp logs" as the > actual set. I believe it is creating them using an internal variation > of onparams. If so, it is only reasonable - as per the default behavior > of onparams - to create them in rootdbs. And no DBSPACETEMP setting, be > it in $ONCONFIG or the environment, is going to change that. > > I also asked the tech if this behavior is more malleable in later > releases, like creating the temp logs in DBSPACETEMP spaces, or if > there is a parameter or env variable unbeknownst to me, that dictates > where the recovery temp logs shall be created. > > Can anyone out there fill in where my tech chickened out? This has > ramifications for any Informix server I configure in the future. > > Thanks much! > > -- Jacob Salomon > +---------------------------------------------------------------------- > ------+ > | I didn't have time to write a short letter, so I wrote a long one > | instead. | > +-------------------------------------------------------------- Mark > +Twain --+ > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

Automatic logical log creation is controlled by two parameters: AUTO_LLOG controls automatically added logical logs created to improve performance. It is set to an enable flag, a dbspace to use by default, and a maximum size of all logical logs. If the dbspace listed is exhausted before the maximum is reached, the dbspace is expandable or extendable, and there is space in the storage pool or the filesystem containing the dbspace's chunk it is grown to make space. DYNAMIC_LOGS controls logical logs automatically added to prevent transaction blocking. If set to 2 this automatically allocates new logs from the ROOT dbspace as Jacob saw. If it is set to 1 then the log file required alarm is triggered and the ALARMPROGRAM can capture the alarm and add a logical log file using onparams -a from any valid dbspace (ie with the base page size and not temp). That would be the way to prevent the auto log allocation from depleting the ROOT dbspace. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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 Mon, Dec 5, 2016 at 10:04 AM, Everett Mills < Everett.Mills@nationalbeef.com> wrote: > I haven't tested this particular situation, but I'm going to guess that the > temp space created for this restore is logged. If you don't have some > logged > dbspaces listed in your DBSPACETEMP, they would be created in the rootdbs > by > default. > > --EEM > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > JACOB SALOMON > > Sent: Sunday, December 04, 2016 23:28 PM > > To: ids@iiug.org > > Subject: Temp log files in rootdbs during warm restore [38227] > > > > Greetings, Family. > > > > I recently had a misadventure and would like to gain further insight > > into the issues of concern. My client is on IDS 11.5 (despite 3 years > > of bugs and nagging to upgrade already! But that's another story.) > > > > My client DBA inadvertently blew away a chunk, realizing his mistake an > > ohno!second later. We therefore had to perform a warm restore of the > > chunk, followed by the log restore. (We chose to do it in 2 steps.) > > > > During the log restore, the rootdbspace filled up and the rollforward > > operation barfed. When we restarted the process I ran onstat -l to view > > info on the temp logs. Aha! Every blessed one was in rootdbs, rather > > than in the expected temp DBspaces specified by the DBSPACETEMP > > parameter. We had even exported a DBSPACETEMP environment variable with > > the same values. No help; it just wanted to use the root. What did we > > do? We quickly added enough chunks to rootdbs to contain the temp logs > > at the same size of the actual logs, about 18GB. So the chunk recovery > > completed and we're back in business. > > > > Crisis over, I had the chance to think about the WHY; why was it > > ignoring the DBSPACETEMP and creating the temp logs in the root? I > > posed the following theory to the Informix tech support on that PMR but > > he has not replied. So I ask the community. Here is my theory: > > > > 1. The onparams command, which we use to add new logical logs, has a > > default DBspace for each new log: rootdbs. It's only because onparams > > has -d option that we can place it in a DBspace of our choosing. > > > > 2. The onbar log-restore process creates as many "temp logs" as the > > actual set. I believe it is creating them using an internal variation > > of onparams. If so, it is only reasonable - as per the default behavior > > of onparams - to create them in rootdbs. And no DBSPACETEMP setting, be > > it in $ONCONFIG or the environment, is going to change that. > > > > I also asked the tech if this behavior is more malleable in later > > releases, like creating the temp logs in DBSPACETEMP spaces, or if > > there is a parameter or env variable unbeknownst to me, that dictates > > where the recovery temp logs shall be created. > > > > Can anyone out there fill in where my tech chickened out? This has > > ramifications for any Informix server I configure in the future. > > > > Thanks much! > > > > -- Jacob Salomon > > +---------------------------------------------------------------------- > > ------+ > > | I didn't have time to write a short letter, so I wrote a long one > > | instead. | > > +-------------------------------------------------------------- Mark > > +Twain --+ > > > > > > *********************************************************************** > > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1148d7bea3d1530542ebb44e

Oh, AUTO_LLOGS is not available in v11.50, but DYNAMIC_LOGS is and is likely what was creating the automatic logical logs during the log restore roll forward. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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 Mon, Dec 5, 2016 at 11:22 AM, Art Kagel <art.kagel@gmail.com> wrote: > Automatic logical log creation is controlled by two parameters: > > AUTO_LLOG controls automatically added logical logs created to improve > performance. It is set to an enable flag, a dbspace to use by default, and > a maximum size of all logical logs. If the dbspace listed is exhausted > before the maximum is reached, the dbspace is expandable or extendable, and > there is space in the storage pool or the filesystem containing the > dbspace's chunk it is grown to make space. > > DYNAMIC_LOGS controls logical logs automatically added to prevent > transaction blocking. If set to 2 this automatically allocates new logs > from the ROOT dbspace as Jacob saw. If it is set to 1 then the log file > required alarm is triggered and the ALARMPROGRAM can capture the alarm and > add a logical log file using onparams -a from any valid dbspace (ie with > the base page size and not temp). That would be the way to prevent the auto > log allocation from depleting the ROOT dbspace. > > Art > > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.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 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 Mon, Dec 5, 2016 at 10:04 AM, Everett Mills < > Everett.Mills@nationalbeef.com> wrote: > >> I haven't tested this particular situation, but I'm going to guess that >> the >> temp space created for this restore is logged. If you don't have some >> logged >> dbspaces listed in your DBSPACETEMP, they would be created in the rootdbs >> by >> default. >> >> --EEM >> >> > -----Original Message----- >> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of >> > JACOB SALOMON >> > Sent: Sunday, December 04, 2016 23:28 PM >> > To: ids@iiug.org >> > Subject: Temp log files in rootdbs during warm restore [38227] >> > >> > Greetings, Family. >> > >> > I recently had a misadventure and would like to gain further insight >> > into the issues of concern. My client is on IDS 11.5 (despite 3 years >> > of bugs and nagging to upgrade already! But that's another story.) >> > >> > My client DBA inadvertently blew away a chunk, realizing his mistake an >> > ohno!second later. We therefore had to perform a warm restore of the >> > chunk, followed by the log restore. (We chose to do it in 2 steps.) >> > >> > During the log restore, the rootdbspace filled up and the rollforward >> > operation barfed. When we restarted the process I ran onstat -l to view >> > info on the temp logs. Aha! Every blessed one was in rootdbs, rather >> > than in the expected temp DBspaces specified by the DBSPACETEMP >> > parameter. We had even exported a DBSPACETEMP environment variable with >> > the same values. No help; it just wanted to use the root. What did we >> > do? We quickly added enough chunks to rootdbs to contain the temp logs >> > at the same size of the actual logs, about 18GB. So the chunk recovery >> > completed and we're back in business. >> > >> > Crisis over, I had the chance to think about the WHY; why was it >> > ignoring the DBSPACETEMP and creating the temp logs in the root? I >> > posed the following theory to the Informix tech support on that PMR but >> > he has not replied. So I ask the community. Here is my theory: >> > >> > 1. The onparams command, which we use to add new logical logs, has a >> > default DBspace for each new log: rootdbs. It's only because onparams >> > has -d option that we can place it in a DBspace of our choosing. >> > >> > 2. The onbar log-restore process creates as many "temp logs" as the >> > actual set. I believe it is creating them using an internal variation >> > of onparams. If so, it is only reasonable - as per the default behavior >> > of onparams - to create them in rootdbs. And no DBSPACETEMP setting, be >> > it in $ONCONFIG or the environment, is going to change that. >> > >> > I also asked the tech if this behavior is more malleable in later >> > releases, like creating the temp logs in DBSPACETEMP spaces, or if >> > there is a parameter or env variable unbeknownst to me, that dictates >> > where the recovery temp logs shall be created. >> > >> > Can anyone out there fill in where my tech chickened out? This has >> > ramifications for any Informix server I configure in the future. >> > >> > Thanks much! >> > >> > -- Jacob Salomon >> > +---------------------------------------------------------------------- >> > ------+ >> > | I didn't have time to write a short letter, so I wrote a long one >> > | instead. | >> > +-------------------------------------------------------------- Mark >> > +Twain --+ >> > >> > >> > *********************************************************************** >> > ******** >> > Forum Note: Use "Reply" to post a response in the discussion forum. >> >> >> ************************************************************ >> ******************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --001a114b71b8b024a20542ebd48f

Art, Thanks for responding. However, I don't believe any of those apply to my situation. Your solutions create permanent logs automatically when the is facing a imminent filling of all log space. That was not our situation. We were *not* running out of log space or anywhere near such a disaster. The onbar -r -l process was creating temporary log files into which it was copyingthe previous week's archived logs, then rolling them forwards as pertained to the DBspace being restored. When it was done with the restore (as well as when it barfed) the temporary log files were all neatly deleted. As to DYNAMIC_LOGS: In our servers, it is set to 1, not 2. We did not want logs getting created outside of our designated logs_dbs, automatically or otherwise. And it was clearly not relevant in our case, since the temporary logs WERE being created automatically, without our having to run onparams. Now Everett mentioned something about logged temp spaces but some vital words seem to have been omitted. That aside, I don't believe there is such a thing as a logged temp DBspace. Straight from the IBM Knowledge Base, URL http://www.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.admin.doc/ids_ admin_0489.htm "The database server does not perform logical or physical logging for temporary dbspaces." So that's not part of this issue either. Knowing that behavior now, I see we will need to pad the rootdbs with enough space to hold temp logs up to the size of the entire log pool, just in case we ever need to do another such restore. My question was if releases 11.7 or 12.x provide a way to control the location of temp logs in a warm restore. I'm beginning to suspect the answer is no but I'd SO love to wrong about that! Still open to responses... -- Jacob Salomon +--------------------------------------------------------------+ | Get your facts first, then you can distort them as you please | +------------------------------------------------ Mark Twain --+

Original post: Art, Thanks for responding. However, I don't believe any of those apply to my situation. Your solutions create permanent logs automatically when the is facing a imminent filling of all log space. That was not our situation. We were *not* running out of log space or anywhere near such a disaster. The onbar -r -l process was creating temporary log files into which it was copyingthe previous week's archived logs, then rolling them forwards as pertained to the DBspace being restored. When it was done with the restore (as well as when it barfed) the temporary log files were all neatly deleted. As to DYNAMIC_LOGS: In our servers, it is set to 1, not 2. We did not want logs getting created outside of our designated logs_dbs, automatically or otherwise. And it was clearly not relevant in our case, since the temporary logs WERE being created automatically, without our having to run onparams. Now Everett mentioned something about logged temp spaces but some vital words seem to have been omitted. That aside, I don't believe there is such a thing as a logged temp DBspace. Straight from the IBM Knowledge Base, URL http://www.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.admin.doc/ids_ admin_0489.htm "The database server does not perform logical or physical logging for temporary dbspaces." So that's not part of this issue either. Knowing that behavior now, I see we will need to pad the rootdbs with enough space to hold temp logs up to the size of the entire log pool, just in case we ever need to do another such restore. My question was if releases 11.7 or 12.x provide a way to control the location of temp logs in a warm restore. I'm beginning to suspect the answer is no but I'd SO love to wrong about that! Still open to responses... -- Jacob Salomon +--------------------------------------------------------------+ | Get your facts first, then you can distort them as you please | +------------------------------------------------ Mark Twain --+ Response: The link you provided is for temporary dbspaces (which are dbspaces created with the -t flag) which is true, you can't put logged objects in those dbspaces. However, what I believe others were talking about was putting a non-temporary dbspace in the $ONCONFIG parameter for DBSPACETEMP. As you most certainly can have both logged and unlogged temp tables. I think it was a post by Andreas that mentioned that the log files for warm restore followed the same logical for finding a dbspace to be located in as logged temporary tables...which is if there's an available dbspace that can be used from DBSPACETEMP, and if not, then where the dbspace where the database was in, and if not there, then finally the root dbspace. I don't think the dbspace where the database is would be applicable, so it's likely just check for a logged dbspace in DBSPACETEMP, and if there isn't, we have to use root dbspace. Jacques Renaut IBM Informix Advanced Support