archecker: restore table-level data (IDS 10)

Posted in 2005

Topics: Backup & Restore, Connectivity: ESQL/C, 4GL & Embedded SQL, Server Administration, Triggers, Constraints & Referential Integrity, Migration, Import/Export & Data Conversion, Platform-Specific Issues

Fernando Ortiz — Tue, 08 Mar 2005 12:38:17 -0600 — source: Usenet: comp.databases.informix

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;[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 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 [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 #1 SMP Fri Oct 3 17:52:5 6 EDT 2003 STATUS: Arguments [-tv -f ac2.txt -lphys] 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 WARNING: Filter being disabled on esta not allowed in logical recovery STATUS: Extracting table cte:clestado into cte:esta ****************************************************************** So after this long example and post, How do I restore a subset of rows? Thanks sending to informix-list

↪ replying to Fernando Ortiz

scottishpoet — 8 Mar 2005 13:53:16 -0800 — source: Usenet: comp.databases.informix

I suggest you contact your support provider

↪ replying to Fernando Ortiz

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

archecker: restore table-level data (IDS 10)

Glossary

Your privacy choices