Re: archecker: restore table-level data (IDS 10)

Posted in 2005

John Miller — Wed, 09 Mar 2005 06:43:26 GMT — source: Usenet: comp.databases.informix

In short filtering is not allowed when you do logical recovery. You must do a physical only restore. To do a physical only restore you must specific this in the schema command file, NOT at the command line. The command line only limits the execution of phases. RESTORE TO CURRENT WITH NO LOGS More comments below. John Miller Fernando Ortiz wrote: > Hi, > > I'm testing the table level restore using archecker. I'm able to do a full restore of one > table but I can't filter data (i.e. where clause in select). > > Using this schema (ac.txt) without filtering works fine > > database cte; > create table clestado ( > estacoes smallint, > estanomb char(34), > PRIMARY KEY (estacoes) > ) in ctemaes; > create table esta (coes smallint, nomb char(34)); > insert into esta select * from clestado;> > ************************************************************************** > > [informix@tina tmp]$ archecker -tv -f ac.txt > 2005-03-08 09:50:17 > ----------------------------------------- > STATUS: IBM Informix Dynamic Server Version 10.00.UC1 > Program Name: archecker > Version: 8.0 > Released: 2005-01-05 22:15:53 > CSDK: IBM Informix CSDK Version 2.90 > ESQL: IBM Informix-ESQL Version 2.90.UN458 > Compiled: 01/05/05 22:16 on Linux 2.4.21-4.ELsmp #1 SMP Fri Oct 3 17:52:56 EDT 2003 > > STATUS: Arguments [-tv -f ac.txt] > STATUS: AC_STORAGE /tmp > STATUS: AC_MSGPATH /tmp/ac_msg.log > STATUS: AC_VERBOSE on > STATUS: AC_TAPEDEV /dev/st0 > STATUS: AC_TAPEBLOCK 256 KB > STATUS: AC_LTAPEDEV /dev/null > STATUS: AC_LTAPEBLOCK 16 KB > STATUS: AC_SCHEMA ac.txt > TIME: [2005-03-08 09:50:17] All old validation files removed. > STATUS: Dropping old log control tables > STATUS: SQL [SET INDEXES, TRIGGERS, CONSTRAINTS FOR esta DISABLED] > STATUS: Extracting table cte:clestado into cte:esta > > Please put in Phys Tape 1. > Type <return> or 0 to end: > > STATUS: Tape type: Archive Backup Tape > STATUS: OnLine version: IBM Informix Dynamic Server Version 10.00.UC1 > STATUS: Archive date: Fri Mar 4 08:57:56 2005 > STATUS: Archive level: 0 > STATUS: Tape blocksize: 262144 > STATUS: Tape size: 70000000 > STATUS: Tape number in series: 1 > TIME: [2005-03-08 10:11:49] Found Partition clestado in space ctemaes (0x01100071). > TIME: [2005-03-08 11:11:39] Tape 1 completed > 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: cte:esta produced 95 rows. > TIME: [2005-03-08 11:12:01] Physical Extraction Completed > STATUS: Creating log control tables > TIME: [2005-03-08 11:12:02] Log Stager started (pid = 15899) > TIME: [2005-03-08 11:12:02] Log Applier started (pid = 15860) > STATUS: Setting up log stream 1 > STATUS: Starting recovery at log 618. > STATUS: Common rollforward point 0618:0x14C62:0x0018. > > Please put in log tape with log id 618. > Type <return> or 0 to end: > 0 > TIME: [2005-03-08 11:14:03] Scanner (15899) is done scanning logs > STATUS: Log stager elapsed processing time 00 H 02 M 00.723 S > TIME: [2005-03-08 11:14:03] Unload Completed > > > > STATUS: archecker completed staging pid = 15899 exit code: 0 > STATUS: SQL [SET INDEXES, TRIGGERS, CONSTRAINTS FOR esta ENABLED] > STATUS: Logically recovered cte:esta Inserted 0 Deleted 0 Updated 0 > STATUS: Log applier processing time 00 H 02 M 02.181 S > TIME: [2005-03-08 11:14:04] Unload Completed > > > > STATUS: archecker completed applying pid = 15860 exit code: 0 > > > *************************************************************************** > > [informix@tina tmp]$ dbaccess cte - > > Database selected. > > > select count(*) from esta where coes < 30;> (count(*)) > 29 > > 1 row(s) retrieved. > > select count(*) from esta;> (count(*)) > 95 > 1 row(s) retrieved. > > ******************************************************************************* > > Now I'try to restore a subset of rows, I use this schema file (ac2.txt) > > [informix@tina tmp]$ cat ac2.txt > database cte; > create table clestado ( > estacoes smallint, > estanomb char(34), > PRIMARY KEY (estacoes) > ) in ctemaes; > create table esta (coes smallint, nomb char(34)); > insert into esta select * from clestado where estacoes < 30; RESTORE TO CURRENT WITH NO LOG; Add the line above to you the file above and you should get what you want. > [informix@tina tmp]$ archecker -tv -f ac2.txt > 2005-03-08 11:33:21 > ----------------------------------------- > STATUS: IBM Informix Dynamic Server Version 10.00.UC1 > Program Name: archecker > Version: 8.0 > Released: 2005-01-05 22:15:53 > CSDK: IBM Informix CSDK Version 2.90 > ESQL: IBM Informix-ESQL Version 2.90.UN458 > Compiled: 01/05/05 22:16 on Linux 2.4.21-4.ELsmp #1 SMP Fri Oct 3 17:52:5 > 6 EDT 2003 > > STATUS: Arguments [-tv -f ac2.txt] > STATUS: AC_STORAGE /tmp > STATUS: AC_MSGPATH /tmp/ac_msg.log > STATUS: AC_VERBOSE on > STATUS: AC_TAPEDEV /dev/st0 > STATUS: AC_TAPEBLOCK 256 KB > STATUS: AC_LTAPEDEV /dev/null > STATUS: AC_LTAPEBLOCK 16 KB > STATUS: AC_SCHEMA ac2.txt > TIME: [2005-03-08 11:33:26] All old validation files removed. > STATUS: Dropping old log control tables > WARNING: Filter being disabled on esta not allowed in logical recovery > ^^^^^^^ > ^^^^^ > ^^ > NOTE This is correct, you may not apply filters when doing logical recovery. > > > STATUS: SQL [SET INDEXES, TRIGGERS, CONSTRAINTS FOR esta DISABLED] > STATUS: Extracting table cte:clestado into cte:esta > > Please put in Phys Tape 1. > Type <return> or 0 to end: > > > > ************************************************************************************************************************************************************ > > I got the error, filter not allowed in logical recovery, so I try physical recovery only > NO. NO NO. This does not do physical recovery only, but just runs the phsycail recovery phase, it assume that more phases (i.e. the logical recovery phases) will be cominig. > > [informix@tina tmp]$ archecker -tv -f ac2.txt -lphys > 2005-03-08 11:33:47 > ----------------------------------------- > STATUS: IBM Informix Dynamic Server Version 10.00.UC1 > Program Name: archecker > Version: 8.0 > Released: 2005-01-05 22:15:53 > CSDK: IBM Informix CSDK Version 2.90 > ESQL: IBM Informix-ESQL Version 2.90.UN458 > Compiled: 01/05/05 22:16 on Linux 2.4.21-4.ELsmp