I would like to share this feature with all of you – Informix Archecker.
It can be used to restore/recover a missing table from backup.
Note: Informix Archecker is available since IDS 10.00 or higher.
Scenario 1:
– Someone accidentally dropped a table “state” on 2013-09-25 11:04:16.
– You will restore/recover the table “state” point-in-time until 2013-09-25 11:00:13.
– Database = stores_demo Table = state
– You are using onbar with Netbackup for backup/restore.
2013-09-25 10:56:01:
Full level 0 backup completed
2013-09-25 11:00:13:
insert into state values (‘PJ’,’Petaling Jaya’);
swtich and backup logical logs
2013-09-25 11:02:22:
insert into state values (‘JB’,’Johor Bahru’);
swtich and backup logical logs
2013-09-25 11:04:16:
drop table state ;
swtich and backup logical logs
Steps to recover:
(1) Create a schema file for archecker like this:
#– res_state.scm –#
database stores_demo ;
create table state (
code char(2),
sname char(15),
primary key (code)
) in edw_appdbs1 ;
insert into state select * from state ;
restore to ‘2013-09-25 11:00:13’ ;
(2) Use informix id perform followings:
$ export INFXBSA_CLIENT=mytesthost
$ archecker -Xvbs -f ./res_state.scm
IBM Informix Dynamic Server Version 11.50.FC9W2
Program Name: archecker
Version: 8.0
Released: 2012-03-12 23:27:47
CSDK: IBM Informix CSDK Version 3.50
ESQL: IBM Informix-ESQL Version 3.50.FC4
Compiled: 03/12/12 23:28 on HP-UX B.11.11 U
AC_STORAGE /tmp
AC_MSGPATH /tmp/ac_msg.log
AC_VERBOSE on
AC_TAPEBLOCK 62 KB
AC_IXBAR /usr/inf_edw/etc/ixbar.3
Dropping old log control tables
Extracting table stores_demo:state into stores_demo:state
Scan PASSED
Control page checks PASSED
Table checks PASSED
Table extraction commands 1
Tables found on archive 1
LOADED: stores_demo:state produced 52 rows.
Creating log control tables
Staging Log 27
Staging Log 28
Switching to log 28
Staging Log 29
Switching to log 29
Logically recovered stores_demo:state Inserted 1 Deleted 0 Updated 0
(3) AC Logs contents /tmp/ac_msg.log
2013-09-25 11:06:32
—————————————–
STATUS: IBM Informix Dynamic Server Version 11.50.FC9W2
Program Name: archecker
Version: 8.0
Released: 2012-03-12 23:27:47
CSDK: IBM Informix CSDK Version 3.50
ESQL: IBM Informix-ESQL Version 3.50.FC4
Compiled: 03/12/12 23:28 on HP-UX B.11.11 U
STATUS: Arguments [-Xvbs -f ./res_state.scm]
STATUS: AC_STORAGE /tmp
STATUS: AC_MSGPATH /tmp/ac_msg.log
STATUS: AC_VERBOSE on
STATUS: AC_TAPEBLOCK 62 KB
STATUS: AC_IXBAR /usr/inf_edw/etc/ixbar.3
STATUS: AC_SCHEMA ./res_state.scm
TIME: [2013-09-25 11:06:37] All old validation files removed.
STATUS: Dropping old log control tables
STATUS: SQL [SET INDEXES, TRIGGERS, CONSTRAINTS FOR state DISABLED]
STATUS: Extracting table stores_demo:state into stores_demo:state
STATUS: Determined Storage Manager to be veritas-netbackup.
STATUS: starting to scan dbspace 1 created on 2013-09-25 10:55:28.
STATUS: Archive timestamp 0X0004E634.
TIME: [2013-09-25 11:10:59] Found Partition state in space edw_rootdbs1 (0x001001AE).
STATUS: Scan PASSED
STATUS: Control page checks PASSED
STATUS: Table checks PASSED
STATUS: Table extraction commands 1
STATUS: Tables found on archive 1
STATUS: LOADED: stores_demo:state produced 52 rows.
TIME: [2013-09-25 11:11:10] Physical Extraction Completed
STATUS: Creating log control tables
TIME: [2013-09-25 11:11:10] Log Stager started (pid = 2489)
TIME: [2013-09-25 11:11:10] Log Applier started (pid = 24102)
STATUS: Setting up log stream 1
STATUS: Determined Storage Manager to be veritas-netbackup.
STATUS: Starting recovery at log 27.
STATUS: Common rollforward point 0027:0x00004:0x0018.
STATUS: Staging Log 27
STATUS: Determined Storage Manager to be veritas-netbackup.
STATUS: Determined Storage Manager to be veritas-netbackup.
STATUS: Staging Log 28
TIME: [2013-09-25 11:17:44] Switching to log 28
STATUS: Determined Storage Manager to be veritas-netbackup.
STATUS: Staging Log 29
TIME: [2013-09-25 11:20:00] Switching to log 29
STATUS: Recovery PIT(Wed Sep 25 11:00:13 2013) reached logid:29 page:0x00000 offset:0x0018
STATUS: All logstreams have been staged by stager 2489
TIME: [2013-09-25 11:20:10] Scanner (2489) is done scanning logs
STATUS: Log stager elapsed processing time 00 H 08 M 59.959 S
TIME: [2013-09-25 11:20:10] Unload Completed
STATUS: archecker completed staging pid = 2489 exit code: 0
STATUS: SQL [SET INDEXES, TRIGGERS, CONSTRAINTS FOR state ENABLED]
STATUS: Logically recovered stores_demo:state Inserted 1 Deleted 0 Updated 0
STATUS: Log applier processing time 00 H 09 M 00.484 S
TIME: [2013-09-25 11:20:11] Unload Completed
STATUS: archecker completed applying pid = 24102 exit code: 0
(4) Verify data:
$ dbaccess stores_demo –
Database selected.
> select * from state where code in (‘PJ’,’JB’) ;
code sname
PJ Petaling Jaya
1 row(s) retrieved.
> select count(*) from state ;
(count(*))
53
1 row(s) retrieved.
(5) Post-task:
Please remember to check if constraints are valid and enable them.