Informix

Hello,

I have to prepare restore plans for our legacy Informix system, and I'm new to Informix.

In hardware failure case everything is clear - we lose dbspace/chunk, so we restore whole dbspace.

In user mistake case things are confusing to me - in other RDBMSes if we lose database due to user mistake we do point-in-time restore of that damaged database.
However I just learned that there is no database level restore in Informix,  point-in-time restore possible but for the whole dbspace - since dbspaces contain multiple databases in our configuration, the best thing I could do is to point-in-time restore all dbspace databases instead of damaged one. This way we lose some data on healthy databases which share same dbspace with damaged one ?  

Could someone explain what is restore strategy for single user mistake damaged database ?

Thanks,
Vilius M.

Vilius:

So, here are your options:

1. You can periodically use dbexport (or my dbexport replacement utility myexport which does not require locks) to take snapshots of your databases. Not great, but many use this method, especially for databases that do not have high transaction rates or are easy to reapply changes and catch up from an export that might be an hour old.
2. Use onbar for archiving (rather than ontape) and you can do a point-in-time restore of individual dbspaces, so as long as each database has dedicated dbspaces you can restore one database without affecting others. This might require a substantial reorganization of your storage though.
3. Spin up an RSS secondary server set on a delay-apply setting (I have clients that set up to an hour of delay-apply) then if someone fumble-fingers some massive update or delete you can break the replication before the logs containing the mistake are applied to the secondary then export the database from the secondary and restore it to the primary (dbexport/dbimport or myexport/myimport).

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Tue August 27, 2024 05:08 AM
From: Vilius Mockūnas
Subject: Informix restore strategy

Hello,

I have to prepare restore plans for our legacy Informix system, and I'm new to Informix.

In hardware failure case everything is clear - we lose dbspace/chunk, so we restore whole dbspace.

In user mistake case things are confusing to me - in other RDBMSes if we lose database due to user mistake we do point-in-time restore of that damaged database.
However I just learned that there is no database level restore in Informix,  point-in-time restore possible but for the whole dbspace - since dbspaces contain multiple databases in our configuration, the best thing I could do is to point-in-time restore all dbspace databases instead of damaged one. This way we lose some data on healthy databases which share same dbspace with damaged one ?  

Could someone explain what is restore strategy for single user mistake damaged database ?

Thanks,
Vilius M.

------------------------------
Vilius Mockūnas
------------------------------

How do I do point-in-time restore of individual dbspace using onbar ?

I tried to do warm restore and I got warning "A Point in Time restore is permitted only during a Full restore" - so it's not possible using warm.

If I do cold restore using -t point-in-time - is that point in time applied for all dbspces ?
How do I apply -t for single noncritical dbspace and leave others intact ?

Thanks.

------------------------------
Vilius Mockūnas

Original Message:
Sent: Tue August 27, 2024 05:42 AM
From: Art Kagel
Subject: Informix restore strategy

Vilius:

So, here are your options:

1. You can periodically use dbexport (or my dbexport replacement utility myexport which does not require locks) to take snapshots of your databases. Not great, but many use this method, especially for databases that do not have high transaction rates or are easy to reapply changes and catch up from an export that might be an hour old.
2. Use onbar for archiving (rather than ontape) and you can do a point-in-time restore of individual dbspaces, so as long as each database has dedicated dbspaces you can restore one database without affecting others. This might require a substantial reorganization of your storage though.
3. Spin up an RSS secondary server set on a delay-apply setting (I have clients that set up to an hour of delay-apply) then if someone fumble-fingers some massive update or delete you can break the replication before the logs containing the mistake are applied to the secondary then export the database from the secondary and restore it to the primary (dbexport/dbimport or myexport/myimport).

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Tue August 27, 2024 05:08 AM
From: Vilius Mockūnas
Subject: Informix restore strategy

Hello,

I have to prepare restore plans for our legacy Informix system, and I'm new to Informix.

In hardware failure case everything is clear - we lose dbspace/chunk, so we restore whole dbspace.

In user mistake case things are confusing to me - in other RDBMSes if we lose database due to user mistake we do point-in-time restore of that damaged database.
However I just learned that there is no database level restore in Informix,  point-in-time restore possible but for the whole dbspace - since dbspaces contain multiple databases in our configuration, the best thing I could do is to point-in-time restore all dbspace databases instead of damaged one. This way we lose some data on healthy databases which share same dbspace with damaged one ?  

Could someone explain what is restore strategy for single user mistake damaged database ?

Thanks,
Vilius M.

------------------------------
Vilius Mockūnas
------------------------------

I don't believe you can with your configuration. I think this may be a reference to a feature available when using tenant databases:

https://www.ibm.com/docs/en/informix-servers/14.10?topic=multitenancy-restoring-tenant-database-point-in-time#ids_admin_1477

I think it is the only way you can achieve the separation necessary to permit restoring different databases to different points in time within an instance. Having different databases within a standard instance, even if these are also fully segregated at dbspace level is not sufficient.

For your restore scenario, if you have a damaged dbspace you can restore it with onbar and logical logs (both backed up and online) to the current time. If you need point in time recovery for some reason, that would need to be to another system and for the whole instance.

I hope this is helpful.

Ben.

How do I do point-in-time restore of individual dbspace using onbar ?

I tried to do warm restore and I got warning "A Point in Time restore is permitted only during a Full restore" - so it's not possible using warm.

If I do cold restore using -t point-in-time - is that point in time applied for all dbspces ?
How do I apply -t for single noncritical dbspace and leave others intact ?

Thanks.

------------------------------
Vilius Mockūnas

Original Message:
Sent: Tue August 27, 2024 05:42 AM
From: Art Kagel
Subject: Informix restore strategy

Vilius:

So, here are your options:

1. You can periodically use dbexport (or my dbexport replacement utility myexport which does not require locks) to take snapshots of your databases. Not great, but many use this method, especially for databases that do not have high transaction rates or are easy to reapply changes and catch up from an export that might be an hour old.
2. Use onbar for archiving (rather than ontape) and you can do a point-in-time restore of individual dbspaces, so as long as each database has dedicated dbspaces you can restore one database without affecting others. This might require a substantial reorganization of your storage though.
3. Spin up an RSS secondary server set on a delay-apply setting (I have clients that set up to an hour of delay-apply) then if someone fumble-fingers some massive update or delete you can break the replication before the logs containing the mistake are applied to the secondary then export the database from the secondary and restore it to the primary (dbexport/dbimport or myexport/myimport).

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Tue August 27, 2024 05:08 AM
From: Vilius Mockūnas
Subject: Informix restore strategy

Hello,

I have to prepare restore plans for our legacy Informix system, and I'm new to Informix.

In hardware failure case everything is clear - we lose dbspace/chunk, so we restore whole dbspace.

In user mistake case things are confusing to me - in other RDBMSes if we lose database due to user mistake we do point-in-time restore of that damaged database.
However I just learned that there is no database level restore in Informix,  point-in-time restore possible but for the whole dbspace - since dbspaces contain multiple databases in our configuration, the best thing I could do is to point-in-time restore all dbspace databases instead of damaged one. This way we lose some data on healthy databases which share same dbspace with damaged one ?  

Could someone explain what is restore strategy for single user mistake damaged database ?

Thanks,
Vilius M.

------------------------------
Vilius Mockūnas
------------------------------

I only can second what Ben already stated:  point-in-time (PIT) restore is available only with whole-system restore or with tenant restore (where everything pertaining to the tenant database resides in a well defined set of db-/sb-/blobspaces which then would be PIT restored together.)

So the only option, besides archecker table-level-restore (TLR, which also supports PIT), would be a whole-system restore to a different system - which might well be restricted to only the critical dbspaces plus the one(s) you're interested in.
You cannot restore select parts of (in) your current system to a historical PIT.

BR,
 Andreas

I don't believe you can with your configuration. I think this may be a reference to a feature available when using tenant databases:

https://www.ibm.com/docs/en/informix-servers/14.10?topic=multitenancy-restoring-tenant-database-point-in-time#ids_admin_1477

I think it is the only way you can achieve the separation necessary to permit restoring different databases to different points in time within an instance. Having different databases within a standard instance, even if these are also fully segregated at dbspace level is not sufficient.

For your restore scenario, if you have a damaged dbspace you can restore it with onbar and logical logs (both backed up and online) to the current time. If you need point in time recovery for some reason, that would need to be to another system and for the whole instance.

I hope this is helpful.

Ben.

How do I do point-in-time restore of individual dbspace using onbar ?

I tried to do warm restore and I got warning "A Point in Time restore is permitted only during a Full restore" - so it's not possible using warm.

If I do cold restore using -t point-in-time - is that point in time applied for all dbspces ?
How do I apply -t for single noncritical dbspace and leave others intact ?

Thanks.

------------------------------
Vilius Mockūnas

Original Message:
Sent: Tue August 27, 2024 05:42 AM
From: Art Kagel
Subject: Informix restore strategy

Vilius:

So, here are your options:

1. You can periodically use dbexport (or my dbexport replacement utility myexport which does not require locks) to take snapshots of your databases. Not great, but many use this method, especially for databases that do not have high transaction rates or are easy to reapply changes and catch up from an export that might be an hour old.
2. Use onbar for archiving (rather than ontape) and you can do a point-in-time restore of individual dbspaces, so as long as each database has dedicated dbspaces you can restore one database without affecting others. This might require a substantial reorganization of your storage though.
3. Spin up an RSS secondary server set on a delay-apply setting (I have clients that set up to an hour of delay-apply) then if someone fumble-fingers some massive update or delete you can break the replication before the logs containing the mistake are applied to the secondary then export the database from the secondary and restore it to the primary (dbexport/dbimport or myexport/myimport).

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Tue August 27, 2024 05:08 AM
From: Vilius Mockūnas
Subject: Informix restore strategy

Hello,

I have to prepare restore plans for our legacy Informix system, and I'm new to Informix.

In hardware failure case everything is clear - we lose dbspace/chunk, so we restore whole dbspace.

In user mistake case things are confusing to me - in other RDBMSes if we lose database due to user mistake we do point-in-time restore of that damaged database.
However I just learned that there is no database level restore in Informix,  point-in-time restore possible but for the whole dbspace - since dbspaces contain multiple databases in our configuration, the best thing I could do is to point-in-time restore all dbspace databases instead of damaged one. This way we lose some data on healthy databases which share same dbspace with damaged one ?  

Could someone explain what is restore strategy for single user mistake damaged database ?

Thanks,
Vilius M.

------------------------------
Vilius Mockūnas
------------------------------

How do I do point-in-time restore of individual dbspace using onbar ?

I tried to do warm restore and I got warning "A Point in Time restore is permitted only during a Full restore" - so it's not possible using warm.

If I do cold restore using -t point-in-time - is that point in time applied for all dbspces ?
How do I apply -t for single noncritical dbspace and leave others intact ?

Thanks.

------------------------------
Vilius Mockūnas

Original Message:
Sent: Tue August 27, 2024 05:42 AM
From: Art Kagel
Subject: Informix restore strategy

Vilius:

So, here are your options:

1. You can periodically use dbexport (or my dbexport replacement utility myexport which does not require locks) to take snapshots of your databases. Not great, but many use this method, especially for databases that do not have high transaction rates or are easy to reapply changes and catch up from an export that might be an hour old.
2. Use onbar for archiving (rather than ontape) and you can do a point-in-time restore of individual dbspaces, so as long as each database has dedicated dbspaces you can restore one database without affecting others. This might require a substantial reorganization of your storage though.
3. Spin up an RSS secondary server set on a delay-apply setting (I have clients that set up to an hour of delay-apply) then if someone fumble-fingers some massive update or delete you can break the replication before the logs containing the mistake are applied to the secondary then export the database from the secondary and restore it to the primary (dbexport/dbimport or myexport/myimport).

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Tue August 27, 2024 05:08 AM
From: Vilius Mockūnas
Subject: Informix restore strategy

Hello,

I have to prepare restore plans for our legacy Informix system, and I'm new to Informix.

In hardware failure case everything is clear - we lose dbspace/chunk, so we restore whole dbspace.

In user mistake case things are confusing to me - in other RDBMSes if we lose database due to user mistake we do point-in-time restore of that damaged database.
However I just learned that there is no database level restore in Informix,  point-in-time restore possible but for the whole dbspace - since dbspaces contain multiple databases in our configuration, the best thing I could do is to point-in-time restore all dbspace databases instead of damaged one. This way we lose some data on healthy databases which share same dbspace with damaged one ?  

Could someone explain what is restore strategy for single user mistake damaged database ?

Thanks,
Vilius M.

------------------------------
Vilius Mockūnas
------------------------------

Hello,

I have to prepare restore plans for our legacy Informix system, and I'm new to Informix.

In hardware failure case everything is clear - we lose dbspace/chunk, so we restore whole dbspace.

In user mistake case things are confusing to me - in other RDBMSes if we lose database due to user mistake we do point-in-time restore of that damaged database.
However I just learned that there is no database level restore in Informix,  point-in-time restore possible but for the whole dbspace - since dbspaces contain multiple databases in our configuration, the best thing I could do is to point-in-time restore all dbspace databases instead of damaged one. This way we lose some data on healthy databases which share same dbspace with damaged one ?  

Could someone explain what is restore strategy for single user mistake damaged database ?

Thanks,
Vilius M.