"Hello Andrew. Yeah, well, um we kind of ran an update with a bad where clause and messed up some of our data. Can you fix just the rows we updated?"
"Oh yeah, sure. We've got the backups and the logs so we'll be OK."
"How long will it take? This is production and it is kind of a big deal"
"You're in luck. Informix has a feature called Table Level Restore that allows me to extract only the rows we want from a backup in a few minutes. Without this feature we would have to perform a full restore to a test engine, which can take hours, and then extract the rows from the restored instance."
"Oh thank heavens we run Informix and not some other database engine that doesn't have this feature."
You can use Table-Level Restore (TLR) to extract an entire table or a subset of rows in a table from your backups. The feature has been around since version 10, is easy to use and can come in really handy if you find yourself in the situation I described above.
To performa a TLR you'll need:
- A Level 0 archive taken via onbar or ontape (I'll use onbar in the examples)
- A schema command file
- archecker (included with the engine)
- Optionally, logical log backups
If you do not have the logical logs backed up or only want to restore the data to the time of the Level 0 archive then you'll be performing a physical restore.
If you have the logical logs and want to restore a table to a point in time then you'll be performing a logical restore. This is great because if someone does make a mistake, you can return the data to the state it was immediately before the mistake was made. A logical restore is performed by TLR first completing a physical restore and then traversing the logical logs converting the log records to SQL statements that are applied to the data from the physical restore. A logical restore can not process changes that exist in the current logical log. If you need to restore to a point in time that is contained in the current log you can shift to the next logical log via onmode -l.
Schema Command File
This is where the magic happens. In this file you will define the source table you want to restore from, the target table you want to restore to and what point in time to restore to.
The schema command file contains:
- SET statements that can enable certain features of TLR
- a DATABASE statement to declare what database we're pulling data from
- CREATE TABLE statements to define the structure of the table we're restoring from and the table we're restoring to
- an INSERT INTO statement that defines which table is the source, which table is the target and optionally what rows we want to restore
- a RESTORE statement that defines if this is a physical or logical restore and what point in time to restore to
set commit to 10000; set workspace to dbspace001;
The DATABASE statement defines which database in the archive we are working with.
The CREATE TABLE statement defines the structure of the source table and the dbspaces and partitions it lives in and defines the structure of the target table. The source table definition must be identical to how the table exists in the archive. The target table definition only needs to contain the columns you want to restore (I guess I forgot to mention that in addition to only restoring select rows you can also restore only select columns if you want.) If the target table does not exist, it will be created during the restore. If the target table does exist, the table will be appended to.
-- source table lives in 2 partitions in 2 dbspaces create table source_table_name ( column1 integer, column2 float, column3 char(17) ) fragment by expression partition p1 (column1 < 50) in dbspace001, partition p2 (column1 >= 50) in dbspace002; -- target table -- will be created if does not exists and appended to if it does exist -- in this example the table only has column1 and column2 because those -- are the only columns to be restored create table target_table_name ( column1 integer, column2 float ) in dbspace001;
The INSERT INTO statement will define the source table (it will be the table we select from) and the target table (the table we are inserting into). This statement can optionally define the columns we want to restore (in the select projection list) and the rows we want to restore (via the where clause).
insert into target_table_name -- identifies target table select column1, -- identifies the columns we column2 -- want to restore from source_table_name -- identifies the source table where column1 between 30 and 100; -- identifies the rows to restore
The RESTORE statement will identify if this is a physical restore or a logical restore and when to restore to.
-- both a physical and a logical restore are included in this example -- pick one or the other, don't include 2 restore commands in the same schema command file -- physical restore includes the with no log clause restore to current with no log; -- logical restore to a specific time restore to "2010-12-10 04:32:00";
Invoking TLR with the archecker Command
archecker used to be just for verifying that your backups weren't corrupt, it has now been extended to perform TLR.
To start TLR using onbar and your schema command file:
> archecker -bdvs -X -f your_schema.cmd IBM Informix Dynamic Server Version 11.50.FC6W4 Program Name: archecker Version: 8.0 Released: 2010-04-26 22:13:21 CSDK: IBM Informix CSDK Version 3.50 ESQL: IBM Informix-ESQL Version 3.50.FC4 Compiled: 04/26/10 22:13 on Linux 2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:56:28 EST 2006 AC_STORAGE /tmp AC_MSGPATH /tmp/ac_msg.log AC_VERBOSE on AC_TAPEBLOCK 62 KB AC_IXBAR /opt/informix/etc/ixbar.0 Dropping old log control tables Extracting table database_name:source_table_name into database_name:target_table_name ........................................ ........................................ ........................................ ......................... Scan PASSED Control page checks PASSED Table checks PASSED Table extraction commands 1 Tables found on archive 1 LOADED: st_switch:scuvr_dnis_restore produced 171721 rows. Creating log control tables Staging Log 18013 Staging Log 18014 Switching to log 18014 Staging Log 18015 Switching to log 18015 Staging Log 18016 Switching to log 18016 Staging Log 18017 Switching to log 18017 Logically recovered database_name:target_table_name Inserted 120 Deleted 79 Updated 38After the TLR completes the target_table_name table will contain the rows from source_table_name with a column1 value between 30 and 100. You can now use the data in the target table to correct the data in the source table.