4

I want to know if there's any way to recover the deleted or updated records updated accidentally in an Informix DB?

The table structure is like this:

ID  NAME  CODE
1   john   yy7
2   Rany   uu6
3   Mike   hh7

The data has been updated to:

ID  NAME  CODE
1   XX   yy7
2   XX   uu6
3   XX   hh7
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Anyname Donotcare
  • 11,113
  • 66
  • 219
  • 392
  • 1
    Ask your DBA to restore a back-up. – jarlh Apr 04 '16 at 10:10
  • @jarlh : the last backup is a little earlier – Anyname Donotcare Apr 04 '16 at 10:11
  • 3
    And no db logging? – jarlh Apr 04 '16 at 10:11
  • @jarlh : is there any automatic log in `informix` because i don't think i have manual log – Anyname Donotcare Apr 04 '16 at 10:15
  • 1
    You need to find out whether your database was created 'WITH LOG' or not. A simple test is: can you run BEGIN WORK successfully. (If it works, add a ROLLBACK WORK.) If your DB was created with a log, then there may be ways to recover the data. If not, the data is gone. Do consult with your DBA quickly. The longer you leave it, the harder it is to recover. Recovery is not straight-forward. Even with logs, there are many things that could prevent recovery. – Jonathan Leffler Apr 04 '16 at 13:30
  • @JonathanLeffler: Could You help me how to know if my DB created with log or not please ? – Anyname Donotcare Apr 04 '16 at 14:53
  • I told you how to do it. Try to start a transaction; if it fails, your DB is unlogged. If it works, there's an outside chance that you may be able to recover the data. Which version of Informix are you using, and on which platform? – Jonathan Leffler Apr 04 '16 at 15:08
  • @JonathanLeffler :`IBM Informix Dynamic Server Version 12.10.FC3` – Anyname Donotcare Apr 04 '16 at 15:11
  • `informix client SDK version : 3.50` – Anyname Donotcare Apr 04 '16 at 15:11
  • Curious. You're using an old CSDK with a new server. The current CSDK version is 4.10 (you'll skip 3.70). Platform (meaning operating system)? If you have access to `dbaccess` you can check the DB logging status with that. Or use `onstat`. Your server is recent and a 64-bit version. – Jonathan Leffler Apr 04 '16 at 15:15
  • If you created the DB and didn't ensure it has logging, it will be unlogged. That's the default for historical reasons, though there might now be a way to override that default. – Jonathan Leffler Apr 04 '16 at 15:18
  • @JonathanLeffler : Could i ask if the log has the same DB schema . i mean creating log table for each table ? or just a flat table – Anyname Donotcare Apr 04 '16 at 15:19
  • 1
    Your choice of terminology suggests that you're new to Informix. I'd go for chat except I'm on the app and walking the dog. – Jonathan Leffler Apr 04 '16 at 15:25
  • 1
    The log doesn't have a schema per se. It records all changes made to all the databases in the instance of Informix. It exists whether a database within the instance is logged or not. However, if the DB is logged, information about before and after images of rows are stored so that the system can be recovered up to the last commit (and uncommitted changes rolled back). If your DB is logged, it might be possible to do some sort of point-in-time recovery to just before the faulty changes were made, but it is a delicate operation and much more info about backups etc will be required. – Jonathan Leffler Apr 04 '16 at 15:34
  • @JonathanLeffler :Really thank u so much for your help :ٍD – Anyname Donotcare Apr 04 '16 at 15:36
  • 1
    The more activity that occurs on the system (in any of the databases), the harder it will be to recover. Are you using a 'tenant database' by any chance? The chances are if you don't know, you aren't. – Jonathan Leffler Apr 04 '16 at 15:37
  • @JonathanLeffler : I don't think so :D – Anyname Donotcare Apr 04 '16 at 15:39
  • 1
    Pity about it not being a tenant database. Are you the DBA too? If so, you may be able to get help from the IBM/Informix tech support team. Otherwise, it is time to start planning on your way forward, regenerating the data as best you can (maybe recover your latest archives into a new instance and export what you had from that 'temporary' instance back to your main one). You need to ensure your database is logged, and that you have a (tested) backup and restore system. Consider looking at the [IIUG web site](http://www.iiug.org/) and its mailing lists. – Jonathan Leffler Apr 04 '16 at 15:47
  • @JonathanLeffler : No, I'm not a DBA.just want to help the DBA in my team to solve the problem .we intend to migrate from `informix` to `Oracle` or `sqlserver` as soon as possible – Anyname Donotcare Apr 04 '16 at 15:51
  • As to deleted rows, is there a way to undelete them by removing the null flags since those rows are still physically in the table? – Joe R. May 08 '16 at 07:47

0 Answers0