"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
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
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 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.
Hi Andrew,
ReplyDeletegood article.
Maybe just need to mention physical restore restriction for result filtering.
Also I have one comment. This example works great for en_us locale. But, if you do not live in country which uses en_us, than you can hit some problems with restoring to a specific time (i.e. datetime value is problem). You must assign exact value which is expected in database locale. There are two solutions for this:
1) you can set DBTIME env variable to '%Y-%m-%d %H:%M:%S' and use your sintax
or
2) you can read right value with
(suppose you have export CLIENT_LOCALE=hr_hr.859-2)
export USE_DTENV=1
echo 'select first 1 current from systables;' | dbaccess sysmaster -
and you will have right format for datetime.
Regards
-bash-3.00$ archecker -tvsX -f /export/home/informix/cmdfile1.cmd
ReplyDeleteIBM Informix Dynamic Server Version 11.50.FC7
Program Name: archecker
Version: 8.0
Released: 2010-05-20 19:43:18
CSDK: IBM Informix CSDK Version 3.50
ESQL: IBM Informix-ESQL Version 3.50.FC4
Compiled: 05/20/10 19:43 on SunOS 5.10 Generic_Patch_118844-30
AC_STORAGE /tmp
AC_MSGPATH /tmp/ac_msg.log
AC_VERBOSE on
AC_TAPEDEV /export/home/ids_backup/data_mcptestsa
AC_TAPEBLOCK 128 KB
AC_LTAPEDEV /export/home/ids_backup/llog_mcptestsa_d/
AC_LTAPEBLOCK 128 KB
CRITICAL ERROR: Connection to the database server 'ids_mcptestsa' has failed (SQLCODE: -27000).
CRITICAL ERROR: Unable to initialize resources
What are the contents of /export/home/informix/cmdfile1.cmd?
ReplyDeleteHey Andrew!
ReplyDeleteI could try it, but call me lazy.
Can we do multiple tables in a cmd file?
(will be trying in a few mins, LOL)
No, the answer is no (or my cmd file sucked).
ReplyDeletearchecker threw:
CRITICAL ERROR: Unable to initialize extraction
in log:
STATUS: Dropping old log control tables
ERROR: Multiple RESTORE commands found
ERROR: Error storing PIT information
CRITICAL ERROR: Unable to initialize extraction
multiple parameters RESTORE found, can only have one;
Deleterestore to current with no log ;
restore to current;
restore to '2006-05-08 02:00:00';
Correct, you can only have 1 restore command. Pick which one you want.
DeleteIs this thread still active? Where do I actually specify the backup file so archecker would know to select the correct backup file
ReplyDeletefantastic article! This is so chock full of users information and the resources you provided was helpful to me. This is comprehensive and helpful list.There I found an informative article discussing about methods to perform table level recovery you may also have a look: http://www.sqlmvp.org/table-level-recovery-for-selected-tables/
ReplyDelete