Re: Dbspace recovery

Hi Art/Fernando, I have now tried som recovery attempts both warm restore of one dbspace and a could one with all dbspaces inc. rootdbs. I also have tried the rename option, but it have failed every time. Do you see what I'm doeing wrong?: WARM RESTORE: -------------------------- ATTEMPT 1) with rename -------------------------- -bash-3.00$ ontape -r -rename -p /dev/vx/rdsk/f_efaDG/db_tables01 -o 0 -n /dev/zvol/rdsk/vm-efainfx-1/zvols/db_efa1 -o 0 -D db_tablesServer is in an incompatible state or user authentication failed. -bash-3.00$ ontape -r -D db_tablesDBspace 'db_tables' is online; restoring 'db_tables' will bring all chunks comprising the DBspace OFFLINE and will terminate all active transactions and queries accessing the DBspace. OK to continue? DBspace 'db_tables' is online; restoring 'db_tables' will bring all chunks comprising the DBspace OFFLINE and will terminate all active transactions and queries accessing the DBspace. OK to continue?yes Please mount tape 1 on /opt/informix/data/LEVEL_0_backup and press Return to continue ... Archive Tape Information Tape type: Archive Backup Tape Online version: IBM Informix Dynamic Server Version 11.50.FC5 Archive date: Tue Feb 23 22:00:29 2010 User id: informix Terminal id: ? Archive level: 0 Tape device: /backup/informix/backup_device Tape blocksize (in k): 64 Tape size (in k): 10000000 Tape number in series: 1 Continue restore? (y/n)y Spaces to restore:1 [db_tables ] Physical restore failed - Archived 'db_tables' is a different space from the current 'db_tables'. Only physical restores of existing spaces are allowed. Program over. -bash-3.00$ onstat -d IBM Informix Dynamic Server Version 11.50.FC5 -- On-Line -- Up 00:17:35 -- 165888 Kbytes Dbspaces address number flags fchunk nchunks pgsize flags owner name 10330028 1 0x60001 1 1 2048 N B informix db_rootdbs 1149a6b8 2 0x40001 2 1 2048 N B informix db_loglog 118a5c20 3 0x40001 3 1 2048 N B informix db_physlog 117bae20 4 0x42001 4 1 2048 N TB informix db_temp 114b89d8 5 0x40005 5 1 2048 ND B informix db_tables 118ca4e0 6 0x40001 6 1 2048 N B informix db_indices 6 active, 2047 maximum Chunks address chunk/dbs offset size free bpages flags pathname 103301c0 1 1 0 1000000 994161 PO-B- /dev/zvol/rdsk/vm-efainfx-1/zvols/ db_rootdb 1197bd68 2 2 0 1000000 919947 PO-B- /dev/zvol/rdsk/vm-efainfx-1/zvols/ db_loglog 118a5db8 3 3 0 1000000 49947 PO-B- /dev/zvol/rdsk/vm-efainfx-1/zvols/ db_physlog 114b8028 4 4 0 1000000 999947 PO-B- /dev/zvol/rdsk/vm-efainfx-1/zvols/db_temp 114b8b70 5 5 0 1000000 0 PD-B- /dev/zvol/rdsk/vm-efainfx-1/zvols/db_efa1 118ca678 6 6 0 1000000 999947 PO-B- /dev/zvol/rdsk/vm-efainfx-1/zvols/db_efa2 6 active, 32766 maximum NOTE: The values in the "size" and "free" columns for DBspace chunks are displayed in terms of "pgsize" of the DBspace to which they belong. Expanded chunk capacity mode: always This left the chunk belonging to the dbspace db_tables in DOWN/ DISABLED state (se abowe)!? I tried to drop it useing onspaces and in onmonitor, but no luck ... 'can not drop, dbspace is not empty' and similar messages. Since this is an unused server for time being I just reinilized the rootdbs and created the dbspaces once more. So warm restoring a single dbspace did not work. Next approach was cold restore .. COLD RESTORE ----------------------- ATTEMPT 1) ----------------------- onmode -ky -bash-3.00$ ontape -r -D db_tables Please mount tape 1 on /opt/informix/data/LEVEL_0_backup and press Return to continue ... Archive Tape Information Tape type: Archive Backup Tape Online version: IBM Informix Dynamic Server Version 11.50.FC5 Archive date: Tue Feb 23 22:00:29 2010 User id: informix Terminal id: ? Archive level: 0 Tape device: /backup/informix/backup_device Tape blocksize (in k): 64 Tape size (in k): 10000000 Tape number in series: 1 Spaces to restore:1 [db_tables ] Archive Information Informix Dynamic Server Copyright(C) 1986-1999 Informix Software, Inc. Initialization Time 11/29/2004 11:53:07 System Page Size 2048 Version 16 Index Page Logging OFF Archive CheckPoint Time 02/23/2010 22:00:29 Dbspaces number flags fchunk nchunks flags owner name 1 40001 1 1 N B informix db_rootdbs 2 40001 2 1 N B informix db_loglog 3 40001 3 1 N B informix db_physlog 4 40001 4 1 N B informix db_temp 5 40001 5 1 N B informix db_tables 6 40001 6 1 N B informix db_indices Chunks chk/dbs offset size free bpages flags pathname 1 1 0 1000000 893413 PO-B /dev/vx/rdsk/f_efaDG/ db_rootdbs 2 2 0 1000000 9947 PO-B /dev/vx/rdsk/f_efaDG/ db_loglog 3 3 0 1000000 999947 PO-B /dev/vx/rdsk/f_efaDG/ db_physlog 4 4 0 4000000 3999747 PO-B /dev/vx/rdsk/f_efaDG/ db_tempdbs01 5 5 0 5000000 4345423 PO-B /dev/vx/rdsk/f_efaDG/ db_tables01 6 6 0 5000000 4760735 PO-B /dev/vx/rdsk/f_efaDG/ db_indices01 Continue restore? (y/n)y Do you want to back up the logs? (y/n)n Physical restore failed - Invalid DBspace list Program over. ----------------------------- ATTEMPT 2) with rename ----------------------------- -bash-3.00$ ontape -r -rename -p /dev/vx/rdsk/f_efaDG/db_tables01 -o 0 -n /dev/zvol/rdsk/vm-efainfx-1/zvols/db_efa1 -o 0 -D db_tables Please mount tape 1 on /opt/informix/data/LEVEL_0_backup and press Return to continue ... Archive Tape Information Tape type: Archive Backup Tape Online version: IBM Informix Dynamic Server Version 11.50.FC5 Archive date: Tue Feb 23 22:00:29 2010 User id: informix Terminal id: ? Archive level: 0 Tape device: /backup/informix/backup_device Tape blocksize (in k): 64 Tape size (in k): 10000000 Tape n

On 02/03/2010 14:52, pow43 wrote: > Hi Art/Fernando, > > I have now tried som recovery attempts both warm restore of one > dbspace and a could one with all dbspaces inc. rootdbs. I also have > tried the rename option, but it have failed every time. Do you see > what I'm doeing wrong?: > > > WARM RESTORE: > <snip> You cannot just plug a dbspace from instance1 into another instance2

Hi, Acording to other guys it should work, but I am a bit uncertain now .... Which other option do I have to be able to make a backup on one instance and in a situation be able to import this backup on another host and IDS instrance? I know dbexport would be a possebility, but not if the db is huge ... several 100 GB's .... -POW On 2 Mar, 16:28, theBP <th...@Usenet-News.Net> wrote: > On 02/03/2010 14:52, pow43 wrote:> Hi Art/Fernando, > > > I have now tried som recovery attempts both warm restore of one > > dbspace and a could one with all dbspaces inc. rootdbs. I also have > > tried the rename option, but it have failed every time. Do you see > > what I'm doeing wrong?: > > > WARM RESTORE: > > <snip> > > You cannot just plug a dbspace from instance1 into another instance2

pow43 wrote: > Hi, > > Acording to other guys it should work, but I am a bit uncertain > now .... Which other option do I have to be able to make a backup on > one instance and in a situation be able to import this backup on > another host and IDS instrance? I know dbexport would be a > possebility, but not if the db is huge ... several 100 GB's .... > > -POW > > On 2 Mar, 16:28, theBP <th...@Usenet-News.Net> wrote: >> On 02/03/2010 14:52, pow43 wrote:> Hi Art/Fernando, >> >>> I have now tried som recovery attempts both warm restore of one >>> dbspace and a could one with all dbspaces inc. rootdbs. I also have >>> tried the rename option, but it have failed every time. Do you see >>> what I'm doeing wrong?: >>> WARM RESTORE: >> <snip> >> >> You cannot just plug a dbspace from instance1 into another instance2 > dbspace restores between instances don't work. For a dbspace to be recovered, you have to roll forward the logical logs since the start of the backup of that dbspace. This is assumed to guarantee the integrity of the data. If the instance is different, the logical log rollforward would obviously fail. I personally understand the requirement of being able to "move" dbspaces from one instance to another since we can imagine scenarios where this could be acceptable (if the dbspace only contains objects of a specific database and if that database has no more objects except those included in that dbspace). But this is not supported. Basically the idea is that we don't let customers shoot themselves in the foot (even if they're just pointing down and away from the feet). Regards.

You take an archive on the source. Then you can use the Archecker utility to extract the tables from the archive and restore the data to another server! I thought I said that several days ago. Another option is to get my dbimport/dbexport replacement utility package myexport which can use the hploader to unload and load the data VERY fast to/from disk and it can process all or many tables in parallel and it does not lock the data base while it's working. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Tue, Mar 2, 2010 at 5:47 PM, pow43 <faber_38@yahoo.no> wrote: > Hi, > > Acording to other guys it should work, but I am a bit uncertain > now .... Which other option do I have to be able to make a backup on > one instance and in a situation be able to import this backup on > another host and IDS instrance? I know dbexport would be a > possebility, but not if the db is huge ... several 100 GB's .... > > -POW > > On 2 Mar, 16:28, theBP <th...@Usenet-News.Net> wrote: > > On 02/03/2010 14:52, pow43 wrote:> Hi Art/Fernando, > > > > > I have now tried som recovery attempts both warm restore of one > > > dbspace and a could one with all dbspaces inc. rootdbs. I also have > > > tried the rename option, but it have failed every time. Do you see > > > what I'm doeing wrong?: > > > > > WARM RESTORE: > > > > <snip> > > > > You cannot just plug a dbspace from instance1 into another instance2 > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Hi, Ok ... I think we maybe misunderstand eachother a little. I work mostly with Sybase and Oracle and in Sybase ASE I restore db's accross hosts and instances even. importing into different versions on a regular basis and it works perfectly. Dbexport/import ... HPL or similar approches (""aka"" bcp/sybmigrate in Sybase and exp/imp or datapump in Oracle) are not at all an option when we are planning Disaster plan for Informix databases. Then we must look at mirror/snap solutions on the SAN (we are using Solaris zfs btw) and not useing Informix tools. Thanks! Per Otto On 3 Mar, 00:43, Art Kagel <art.ka...@gmail.com> wrote: > You take an archive on the source.  Then you can use the Archecker utility > to extract the tables from the archive and restore the data to another > server!  I thought I said that several days ago. > > Another option is to get my dbimport/dbexport replacement utility package > myexport which can use the hploader to unload and load the data VERY fast > to/from disk and it can process all or many tables in parallel and it does > not lock the data base while it's working. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (a...@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KSwww.iiug.org/conf > > 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 Tue, Mar 2, 2010 at 5:47 PM, pow43 <faber...@yahoo.no> wrote: > > Hi, > > > Acording to other guys it should work, but I am a bit uncertain > > now .... Which other option do I have to be able to make a backup on > > one instance and in a situation be able to import this backup on > > another host and IDS instrance? I know dbexport would be a > > possebility, but not if the db is huge ... several 100 GB's .... > > > -POW > > > On 2 Mar, 16:28, theBP <th...@Usenet-News.Net> wrote: > > > On 02/03/2010 14:52, pow43 wrote:> Hi Art/Fernando, > > > > > I have now tried som recovery attempts both warm restore of one > > > > dbspace and a could one with all dbspaces inc. rootdbs. I also have > > > > tried the rename option, but it have failed every time. Do you see > > > > what I'm doeing wrong?: > > > > > WARM RESTORE: > > > > <snip> > > > > You cannot just plug a dbspace from instance1 into another instance2 > > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list– Skjul sitert tekst – > > – Vis sitert tekst –

By "disaster" you mean something that affects what?! If it affects only a dbspace, you need to recover it in the original instance. If it affects a complete machine/instance you need to recover the whole instance and not a dbspace (in the same machine or another). Also, if you're planning for disaster/recovery take a look at MACH 11 technology (HDR/RSS/SDS/ER). That should give you plenty of options for whatever you need to do. The option of moving a dbspace can have several usage scenarios, but I can't see how disaster recovery is one of them? Regards. On Wed, Mar 3, 2010 at 12:27 AM, pow43 <faber_38@yahoo.no> wrote: > Hi, > > Ok ... I think we maybe misunderstand eachother a little. I work > mostly with Sybase and Oracle and in Sybase ASE I restore db's accross > hosts and instances even. importing into different versions on a > regular basis and it works perfectly. Dbexport/import ... HPL or > similar approches (""aka"" bcp/sybmigrate in Sybase and exp/imp or > datapump in Oracle) are not at all an option when we are planning > Disaster plan for Informix databases. Then we must look at mirror/snap > solutions on the SAN (we are using Solaris zfs btw) and not useing > Informix tools. > > Thanks! > > Per Otto > > On 3 Mar, 00:43, Art Kagel <art.ka...@gmail.com> wrote: > > You take an archive on the source. Then you can use the Archecker > utility > > to extract the tables from the archive and restore the data to another > > server! I thought I said that several days ago. > > > > Another option is to get my dbimport/dbexport replacement utility package > > myexport which can use the hploader to unload and load the data VERY fast > > to/from disk and it can process all or many tables in parallel and it > does > > not lock the data base while it's working. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > IIUG Board of Directors (a...@iiug.org) > > > > See you at the 2010 IIUG Informix Conference > > April 25-28, 2010 > > Overland Park (Kansas City), KSwww.iiug.org/conf > > > > 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 Tue, Mar 2, 2010 at 5:47 PM, pow43 <faber...@yahoo.no> wrote: > > > Hi, > > > > > Acording to other guys it should work, but I am a bit uncertain > > > now .... Which other option do I have to be able to make a backup on > > > one instance and in a situation be able to import this backup on > > > another host and IDS instrance? I know dbexport would be a > > > possebility, but not if the db is huge ... several 100 GB's .... > > > > > -POW > > > > > On 2 Mar, 16:28, theBP <th...@Usenet-News.Net> wrote: > > > > On 02/03/2010 14:52, pow43 wrote:> Hi Art/Fernando, > > > > > > > I have now tried som recovery attempts both warm restore of one > > > > > dbspace and a could one with all dbspaces inc. rootdbs. I also have > > > > > tried the rename option, but it have failed every time. Do you see > > > > > what I'm doeing wrong?: > > > > > > > WARM RESTORE: > > > > > > <snip> > > > > > > You cannot just plug a dbspace from instance1 into another instance2 > > > > > _______________________________________________ > > > Informix-list mailing list > > > Informix-l...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list– Skjul sitert tekst > – > > > > – Vis sitert tekst – > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

This one bites me every time. PLEASE!!! Don't ask us how to make it work when some solution you've dreamed up doesn't work. Insead, lay out what it is that you are trying to accomplish and ask us how we would solve the issue. For disaster recovery, Informix has ontape and onbar which archive and restore the entire server onto the same machine or a different one as long as the machines are binary compatible and run the same OS. That's simple and fast. That's disaster recovery. In addition, Informix has HDR, SDS, and RSS secondary servers so that you can continue servicing client applications immediately after a server crashes, that's disaster avoidance. But that does not seem to be what you were asking about. If all you are trying to do is to restore the archived server onto a different machine, then it is trivial: - Create the links for each chunk to some existing storage on the machine that is at least as big as the original chunk - To determine what the chunks are in case you have not saved a copy of the onstat -d report from the server before it crashed, you can start the restore and it will print out the list of chunk paths and their sizes and offsets (offset + size equals how much storage you'll need on the replacement chunk device or file unless you use the remapping option of ontape to relocate the chunks elsewhere). - Make sure the ownerships and permissions are correct on the chunks (informix:informix 0660) - run "ontape -r" and stand back It's that simple, I've restored many hundred servers over the years between using ontape and onbar to migrate servers to new machines, test restores, post-disaster crash recovery, and post fumble-finger restores (before archecker) to recover deleted rows and dropped tables and there is nothing easier. If you are having trouble with the mechanics of restoring an archive, you need training not a different tool. Call us at ADTC, we can help. However, you keep asking about restoring a single dbspace to a different already running server instance. You cannot do that, correct. What has that to do with SNAPping SAN storage. What does that have to do with disaster recovery? Disaster recovery is about restoring an entire database server instance, not restoring a single dbspace or a single database. Usually that kind of partial restoration is about recovering from fumble fingers and that's a different animal. Other applications for this that I've seen is copying production data to a QA or Dev. environment. If these are your scenarios, which are not disaster recovery at all, then, yes ontape and onbar are not the correct tools for you. Actually, dbexport and dbimport (or myexport and myimport) and other scripted solutions are what most of us use and they work well. For large to massive databases hploader reading from one instance and piping its output to another hploader copy which is inserting the data into the remote server works VERY well. Beyond that, now with 11.50.xC6's EXTERNAL TABLES So, what is the recovery scenario that you are that you are trying to plan recovery from? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Tue, Mar 2, 2010 at 7:27 PM, pow43 <faber_38@yahoo.no> wrote: > Hi, > > Ok ... I think we maybe misunderstand eachother a little. I work > mostly with Sybase and Oracle and in Sybase ASE I restore db's accross > hosts and instances even. importing into different versions on a > regular basis and it works perfectly. Dbexport/import ... HPL or > similar approches (""aka"" bcp/sybmigrate in Sybase and exp/imp or > datapump in Oracle) are not at all an option when we are planning > Disaster plan for Informix databases. Then we must look at mirror/snap > solutions on the SAN (we are using Solaris zfs btw) and not useing > Informix tools. > > Thanks! > > Per Otto > > On 3 Mar, 00:43, Art Kagel <art.ka...@gmail.com> wrote: > > You take an archive on the source. Then you can use the Archecker > utility > > to extract the tables from the archive and restore the data to another > > server! I thought I said that several days ago. > > > > Another option is to get my dbimport/dbexport replacement utility package > > myexport which can use the hploader to unload and load the data VERY fast > > to/from disk and it can process all or many tables in parallel and it > does > > not lock the data base while it's working. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > IIUG Board of Directors (a...@iiug.org) > > > > See you at the 2010 IIUG Informix Conference > > April 25-28, 2010 > > Overland Park (Kansas City), KSwww.iiug.org/conf > > > > 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 Tue, Mar 2, 2010 at 5:47 PM, pow43 <faber...@yahoo.no> wrote: > > > Hi, > > > > > Acording to other guys it should work, but I am a bit uncertain > > > now .... Which other option do I have to be able to make a backup on > > > one instance and in a situation be able to import this backup on > > > another host and IDS instrance? I know dbexport would be a > > > possebility, but not if the db is huge ... several 100 GB's .... > > > > > -POW > > > > > On 2 Mar, 16:28, theBP <th...@Usenet-News.Net> wrote: > > > > On 02/03/2010 14:52, pow43 wrote:> Hi Art/Fernando, > > > > > > > I have now tried som recovery attempts both warm restore of one > > > > > dbspace and a could one with all dbspaces inc. rootdbs. I also have > > > > > tried the rename option, but it have failed every time. Do you see > > > > > what I'm doeing wrong?: > > > > > > > WARM RESTORE: > > > > > > <snip> > > > > > > You cannot just plug a dbspace from instance1 into another instance2 > > > > > _______________________________________________ > > > Informix-list mailing list > > > Informix-l...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list– Skjul sitert tekst > – > > > > – Vis sitert tekst – > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >