Informix Archecker – point in time restore/recover table (Scenario 2)

I would like to share this feature with all of you – Informix Archecker.
It can be used to restore/recover (point-in-time) to a new target table (new table name) from backup.

Scenario 2:
– Someone accidentally performed a mass update for all rows in table “customer” on 2014-08-21 10:48:00.
– You need to restore & recover (point in time) to a new table called “customer_res” until 2014-08-21 10:46:00. (Current table “customer” will be maintained)
– Database = stores_demo, Source Table = customer, Target New Table = customer_res
– You are using Informix Archecker with Netbackup Informix Agent for backup/restore.

Note: Informix Archecker is available since IDS 10.00 or higher.

(1) 2014-08-21 09:50:00:

$ dbaccess stores_demo –
> select count(*) from customer ;

(count(*))

28

2014-08-21 09:51:00 – 10:04:00:
– onbar Level 0 backup started and completed successfully (with logical logs backup).

(2) 2014-08-21 10:44:00:

$ dbaccess stores_demo –
> insert into customer (fname, lname, company) values (‘Bala’,’Krishnan’,’Anil Textile Ltd.’) ;

1 row(s) inserted.

– checkpoint (onmode -c)
– Switch logical logs (onmode -l)
– backup logical logs (onbar -b -l)

$ dbaccess stores_demo –
> select count(*) from customer ;

(count(*))

29

(3) 2014-08-21 10:48:00:

$ dbaccess stores_demo –
> select count(*) from customer ;

(count(*))

29

> update customer set fname = ‘Bill’ ;

29 row(s) updated.

– Force checkpoint (onmode -c)
– Switch logical logs (onmode -l)
– backup logical logs (onbar -b -l)

> select fname, count(*) from customer group by fname ;

fname (count(*))

Bill 29

—————————-
archecker steps of procedure
—————————-

(4) Prepare archecker schema file (customer.sch).
– Please take note here that there are 2 CREATE TABLE statements.
– 1st CREATE TABLE refers to source table (customer).
– 2nd CREATE TABLE refers to target restore table (customer_res).
– Please provide storage option “in dbspace name” in 1st CREATE TABLE (source) statement simply because to let archcker to search for the correct DBspace that storing the table data.

#– customer.sch –#

database stores_demo ;

create table customer
(
customer_num serial not null ,
fname char(15),
lname char(15),
company char(20),
address1 char(20),
address2 char(20),
city char(15),
state char(2),
zipcode char(5),
phone char(18),
primary key (customer_num)
) in my_dbs9 ;

create table “myadm”.customer_res
(
customer_num serial not null ,
fname char(15),
lname char(15),
company char(20),
address1 char(20),
address2 char(20),
city char(15),
state char(2),
zipcode char(5),
phone char(18),
primary key (customer_num) constraint pk_cust_res1
) in my_dbs9 ;

insert into customer_res select * from customer ;
restore to ‘2014-08-21 10:46:00’ ;

(5) Run archecker (using informix OS id).

$ export INFXBSA_CLIENT=mytesthost
$ archecker -Xvbs -f ./customer.sch
$ cat /tmp/ac_msg.log

2014-08-21 11:08:12
—————————————–
STATUS: IBM Informix Dynamic Server Version 11.50.FC7
Program Name: archecker
Version: 8.0
Released: 2010-05-10 22:05:13
CSDK: IBM Informix CSDK Version 3.50
ESQL: IBM Informix-ESQL Version 3.50.FC4
Compiled: 05/10/10 22:05 on Linux 2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:56:28 EST 2006

STATUS: Arguments [-Xvbs -f ./customer.sch]
STATUS: AC_STORAGE /tmp
STATUS: AC_MSGPATH /tmp/ac_msg.log
STATUS: AC_VERBOSE on
STATUS: AC_TAPEBLOCK 62 KB
STATUS: AC_IXBAR /usr/informix_my/etc/ixbar.15
STATUS: AC_SCHEMA ./customer.sch
TIME: [2014-08-21 11:08:12] All old validation files removed.
STATUS: Dropping old log control tables
STATUS: SQL [SET INDEXES, TRIGGERS, CONSTRAINTS FOR customer_res DISABLED]
STATUS: Extracting table stores_demo:customer into stores_demo:customer_res
STATUS: Determined Storage Manager to be veritas-netbackup.
STATUS: starting to scan dbspace 18 created on 2014-08-21 10:00:24.
STATUS: Archive timestamp 0XC145CD3C.
TIME: [2014-08-21 11:11:53] Found Partition customer in space my_dbs9 (0x01200022).
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:customer_res produced 28 rows.
TIME: [2014-08-21 11:12:55] Physical Extraction Completed
STATUS: Creating log control tables
TIME: [2014-08-21 11:12:55] Log Stager started (pid = 19072)
TIME: [2014-08-21 11:12:55] Log Applier started (pid = 17319)
STATUS: Setting up log stream 1
STATUS: Determined Storage Manager to be veritas-netbackup.
STATUS: Starting recovery at log 5674.
STATUS: Common rollforward point 5674:0x01016:0x0018.
STATUS: Staging Log 5674
STATUS: Determined Storage Manager to be veritas-netbackup.
STATUS: Staging Log 5675
TIME: [2014-08-21 11:25:32] Switching to log 5675
STATUS: Determined Storage Manager to be veritas-netbackup.
STATUS: Staging Log 5676
TIME: [2014-08-21 11:27:05] Switching to log 5676
STATUS: Recovery PIT(Thu Aug 21 10:46:00 2014) reached logid:5676 page:0x000FA offset:0x02E4
STATUS: All logstreams have been staged by stager 19072
TIME: [2014-08-21 11:27:15] Scanner (19072) is done scanning logs
STATUS: Log stager elapsed processing time 00 H 14 M 20.170 S
TIME: [2014-08-21 11:27:15] Unload Completed
STATUS: archecker completed staging pid = 19072 exit code: 0
STATUS: SQL [SET INDEXES, TRIGGERS, CONSTRAINTS FOR customer_res ENABLED]
STATUS: Logically recovered stores_demo:customer_res Inserted 1 Deleted 0 Updated 0
STATUS: Log applier processing time 00 H 14 M 20.902 S
TIME: [2014-08-21 11:27:16] Unload Completed
STATUS: archecker completed applying pid = 17319 exit code: 0

(6) Verify data.

$ dbaccess stores_demo –
> select fname from customer;

fname

Ludwig
Carole
Philip
Anthony
Raymond
George
Charles
Donald
Jane
Roy
Frances
Margaret
Lana
Frank
Alfred
Jean
Arnold
Dick
Bob
Fred
Jason
Cathy
Marvin
Chris
James
Eileen
Kim
Frank
Bala

29 row(s) retrieved.

Tagged Under : , , , ,

Make a Comment