Table Level Restore - Pretty Useful Stuff

"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."

Table-Level Restore

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

Physical Restore

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.

Logical 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

The optional SET statement can change the commit interval from the default of 1000 to set the number of rows that are inserted during the physical restore between commits. The SET statement can also be used to change the working dbspace from the default of rootdbs.

set commit to 10000;
set workspace to dbspace001;

The DATABASE statement defines which database in the archive we are working with.

database database_name;

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 38

After 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.