STDIO L0 restore plus logical log restore with ontape

Hi, I need to restore a gzipped ifx level zero backup pluss some logical logs. However I can't ungzip the file first because of lack of big storage for the uncompressed file and the performance and time penalty of writing 50+ GB to disk and then reading it during a restore. The CPU is much faster at decompressing the gzip on the fly. So I want to use STDIO and interactive ontape to restore the logs. On the other hand doing a ontape -r -t STDIO and then restoring the logical log files separately has failed too. I guess I simply don't understand how to do that correctly. ontape -l always says there's nothing to restore. And yes, I've read the informix guide repeatedly. Informix version 11.50UC6 and I haven't yet rebuilt the sysmaster (or was it sysutils) database as outlined in the 11.50.UC6.W1 fix. I'm doing the restores on a test/development server server. I'm asking for pointers on how to enable interactive restore while using STDIO or general guidelines on restoring a level zero and logical logs with seperate ontape commands. The logical logs are on disk too. No physical tape devices involved. Regards, -- Aleksander Kamenik System Administrator Krediidiinfo AS an Experian Company Phone: +372 665 9649 Email: aleksander@krediidiinfo.ee http://www.krediidiinfo.ee/ http://www.experiangroup.com/

On 04/02/2010 10:22, Aleksander Kamenik wrote: > Hi, > > I need to restore a gzipped ifx level zero backup pluss some logical logs. > > However I can't ungzip the file first because of lack of big storage for > the uncompressed file and the performance and time penalty of writing > 50+ GB to disk and then reading it during a restore. The CPU is much > faster at decompressing the gzip on the fly. > > So I want to use STDIO and interactive ontape to restore the logs. > > > > On the other hand doing a ontape -r -t STDIO and then restoring the > logical log files separately has failed too. I guess I simply don't > understand how to do that correctly. ontape -l always says there's > nothing to restore. > > And yes, I've read the informix guide repeatedly. > > > Informix version 11.50UC6 and I haven't yet rebuilt the sysmaster (or > was it sysutils) database as outlined in the 11.50.UC6.W1 fix. > > I'm doing the restores on a test/development server server. > > > I'm asking for pointers on how to enable interactive restore while using > STDIO or general guidelines on restoring a level zero and logical logs > with seperate ontape commands. > > The logical logs are on disk too. No physical tape devices involved. > > Regards, > Take a backup : ontape -s -L 0 -t STDIO | gzip -c > ontape.gz Move on some logs : onmode -l onmode -c onmode -l onmode -c Backup some logs : ontape -a Performing automatic backup of logical logs. File created: /ok_226_Log0000000006 File created: /ok_226_Log0000000007 File created: /ok_226_Log0000000008 File created: /ok_226_Log0000000009 Do you want to back up the current logical log? (y/n) y File created: /ok_226_Log0000000010 For the restore (engine offline) : gunzip the file to STDIO and pipe it to ontape, doing a physical only restore : gunzip -c ontape.gz | ontape -p -t STDIO Now to the logical restore : ontape -l Now bring the engine online : onmode -m

On 02/04/2010 02:04 PM, theBP wrote: > Take a backup : > > ontape -s -L 0 -t STDIO | gzip -c> ontape.gz ... > ontape -a On the main server this is done automatically. I have the nightly L0 as well as 7 about 100MB sized logical log files. I need to restore to a state before a table was dropped mid day. So I have seven logs to restore after the level zero to get most of the lost data back. The eighth log would contain the drop statement (I'll get to archecker later). So I'm restoring onto a another server. No ER or HDR involved whatsoever. > For the restore (engine offline) : > > gunzip the file to STDIO and pipe it to ontape, doing a physical only restore : > > gunzip -c ontape.gz | ontape -p -t STDIO Right, I used -r instead of -p. -r put the server into quiescent from fast recovery. -p doesn't. Thanks! (Though I have to wonder, is it even possible to restore a level 1 backup when you're using STDIO for the level zero restore? There's no switch in ontape for level 1 like there's for logical logs.) > Now to the logical restore : > > ontape -l All good. Restored the 7 logical logs. > Now bring the engine online : > onmode -m Done. The online.log says the logs were applied and lots of checkpoints were done. In ISA I can see the 7 logical logs are there and a new one was started. And here's the really weird thing. I don't see any changes in the tables that were supposed to come from the logical logs. The tables are the same as if they would have been after the L0 restore without any further logical log restores. I'm sure I'm looking at the correct database as well as tables. When I check the production server (where the backups were taken) I can see changes from that time period. But on the restored server they go only up to the level zero backup. As if I hadn't applied any logs. The logical logs are timestamped in the file name additionally to the log sequence number. Also the logs would not apply if they were the wrong ones or if I fed them to ontape in the wrong order. Even if by magic the timestamps had drifted into the future, there's 700MB of logical logs which data doesn't show up even in popular tables. And btw the servers use ntp to keep time. I'm positive that the logs did not apply or the data was not there to begin with. But the files are there, they're unique (different md5sums), correct size and contain correctly numbered logical log files (otherwise ontape would not accept them). I also tried applying the logs after the L0 restore one by one using continuous restore (7 times ontape -l -C and once ontape -l -X). Same result. So what could possibly be the reason that the logical log restores don't apply to the tables, but are apparently written to the logical log. I can't help but think that I'm doing something very stupid. And probably I am, but I can't figure out what. This is 11.50.UC6 without the .W1 sysmaster rebuild. Could this be what's causing problem? I should also mention that I tried to restore the table from the L0 and logical logs with archecker. No problem with the L0, however no luck with the logical logs. archecker just hangs there, doing nothing. So maybe it is the log files that are corrupt? I couldn't figure out how to make archecker verify the files either, again, it would do pretty much nothing. The 'stage' does nothing and 'apply' doesn't have anything to apply. Though maybe archecker does not work be cause of the old/broken sysmaster database? I have to admit too that I've never used archecker before. Any ideas appreciated. Regards, -- Aleksander Kamenik System Administrator Krediidiinfo AS an Experian Company Phone: +372 665 9649 Email: aleksander@krediidiinfo.ee http://www.krediidiinfo.ee/ http://www.experiangroup.com/

Aleksander Kamenik wrote: > On 02/04/2010 02:04 PM, theBP wrote: > >> Take a backup : >> >> ontape -s -L 0 -t STDIO | gzip -c> ontape.gz > ... >> ontape -a> > On the main server this is done automatically. I have the nightly L0 as > well as 7 about 100MB sized logical log files. I need to restore to a > state before a table was dropped mid day. So I have seven logs to > restore after the level zero to get most of the lost data back. The > eighth log would contain the drop statement (I'll get to archecker later). > > So I'm restoring onto a another server. No ER or HDR involved whatsoever. > >> For the restore (engine offline) : >> >> gunzip the file to STDIO and pipe it to ontape, doing a physical only >> restore : >> >> gunzip -c ontape.gz | ontape -p -t STDIO > > Right, I used -r instead of -p. -r put the server into quiescent from > fast recovery. -p doesn't. Thanks! > > (Though I have to wonder, is it even possible to restore a level 1 > backup when you're using STDIO for the level zero restore? There's no > switch in ontape for level 1 like there's for logical logs.) > >> Now to the logical restore : >> >> ontape -l> > All good. Restored the 7 logical logs. > >> Now bring the engine online : >> onmode -m> > Done. > > The online.log says the logs were applied and lots of checkpoints were > done. In ISA I can see the 7 logical logs are there and a new one was > started. > > > And here's the really weird thing. I don't see any changes in the tables > that were supposed to come from the logical logs. The tables are the > same as if they would have been after the L0 restore without any further > logical log restores. > > > I'm sure I'm looking at the correct database as well as tables. When I > check the production server (where the backups were taken) I can see > changes from that time period. But on the restored server they go only > up to the level zero backup. As if I hadn't applied any logs. > > > The logical logs are timestamped in the file name additionally to the > log sequence number. Also the logs would not apply if they were the > wrong ones or if I fed them to ontape in the wrong order. > > Even if by magic the timestamps had drifted into the future, there's > 700MB of logical logs which data doesn't show up even in popular tables. > And btw the servers use ntp to keep time. > > > I'm positive that the logs did not apply or the data was not there to > begin with. But the files are there, they're unique (different md5sums), > correct size and contain correctly numbered logical log files (otherwise > ontape would not accept them). > > > I also tried applying the logs after the L0 restore one by one using > continuous restore (7 times ontape -l -C and once ontape -l -X). Same > result. > > > So what could possibly be the reason that the logical log restores don't > apply to the tables, but are apparently written to the logical log. > > > I can't help but think that I'm doing something very stupid. And > probably I am, but I can't figure out what. > > > This is 11.50.UC6 without the .W1 sysmaster rebuild. Could this be > what's causing problem? > > > > I should also mention that I tried to restore the table from the L0 and > logical logs with archecker. No problem with the L0, however no luck > with the logical logs. > > archecker just hangs there, doing nothing. So maybe it is the log files > that are corrupt? I couldn't figure out how to make archecker verify the > files either, again, it would do pretty much nothing. > > The 'stage' does nothing and 'apply' doesn't have anything to apply. > Though maybe archecker does not work be cause of the old/broken > sysmaster database? > > I have to admit too that I've never used archecker before. > > > > > Any ideas appreciated. > > Regards, > Are you sure your tables are in a logged database? Probably a stupid suggestion, but for now it's the only one that poped up... Regards.

On 02/07/2010 12:29 AM, Fernando Nunes wrote: > Are you sure your tables are in a logged database? Probably a stupid > suggestion, but for now it's the only one that poped up... You're right. For some reason logging has been disabled. It definitely was enabled a few years back when logical logs were used for a restore. That's a bummer. It didn't even come to mind that the status could have changed. And I didn't notice it, should have whatever time ago. I'm not sure whether I'll be able to figure out when and why this changed though. It's also quite weird that during a day more than a gigabyte of logical logs are created for the remaining logged database which is very low traffic. Anyway, I tried turning logging on but ontape complains: Invalid logging change to dbname I'm looking into this now. The bad is that some data got lost, the good, that this can fixed and maybe in the future even more data won't be lost. Regards, -- Aleksander Kamenik System Administrator Krediidiinfo AS an Experian Company Phone: +372 665 9649 Email: aleksander@krediidiinfo.ee http://www.krediidiinfo.ee/ http://www.experiangroup.com/