Archecker single table restore compressed ontape
User on AIX 7.1 / IDS 11.70.FC4 made ontape STDIO level-0 backups with BACKUP_FILTER=gzip and found the resulting file couldn't be read by gzip, fearing corruption, and asked whether archecker could extract one table from it. Replies explained that BACKUP_FILTER only compresses the backup payload, not ontape's header/meta blocks, so the file isn't a plain .gz but is still valid; archecker handles it via AC_RESTORE_FILTER (physical restore at least). The extraction then ran, and the later "-1226 decimal exceeds precision" errors turned out to be a mismatched table definition in the archecker schema file; after fixing it, the single-table restore worked. The "19" in the archive filename is the SERVERNUM.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
AIX 7.1 IDS 11.7FC4.
I have a nightly cron that runs the following ontape backup:
# ontape -t STDIO -s -L 0 > /backup/backup
# onstat -c |grep -i filter
BACKUP_FILTER /usr/bin/gzip
RESTORE_FILTER /usr/bin/gunzip
messages in the log that rootdbs, datadbs ... started ... and after a while
completed successfully.
I need to restore 1 table from this image using archecker that I created few
days ago. However, if i run gzip -l or even gzip -cd ... it says that this
file is not a valid gz file i.e. I cannot uncompress it.
2 questions:
1. are my backup images corrupted so that I will never be able to use them
even if I restore with the same onconfig file? (AM i doing the backup command
wrong?)
2. is there any way that archecker can obtain the table from compressed image?
Tried to understand what Art stated in
http://members.iiug.org/forums/ids/index.cgi/noframes/read/25451 but did not
helped me much.
Cannot do a full restore due to space limitations.
Looking forward any clarification.
Your backups are likely good - I don't think that using BACKUP_FILTER is the
quite same as piping the backup through gzip, so you can't expect to unzip
it.
archecker will work with an archive which was made using BACKUP_FILTER. The
only problem that I have had is doing a point-in-time restore, where it
needs to roll forward from the logical logs, but you can do a physical
recovery which will restore the table to the time that the backup was taken.
There was a thread about performing a point-in-time recovery of a table with
archecker just a couple of weeks ago. I don't know if that was ever
resolved.
Mike
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
ALEKSANDAR IVANOVSKI
Sent: Thursday, September 21, 2017 12:35 PM
To: ids@iiug.org
Subject: Archecker single table restore compressed ontape [39957]
Hi,
AIX 7.1 IDS 11.7FC4.
I have a nightly cron that runs the following ontape backup:
# ontape -t STDIO -s -L 0 > /backup/backup
# onstat -c |grep -i filter
BACKUP_FILTER /usr/bin/gzip
RESTORE_FILTER /usr/bin/gunzip
messages in the log that rootdbs, datadbs ... started ... and after a while
completed successfully.
I need to restore 1 table from this image using archecker that I created few
days ago. However, if i run gzip -l or even gzip -cd ... it says that this
file is not a valid gz file i.e. I cannot uncompress it.
2 questions:
1. are my backup images corrupted so that I will never be able to use them
even if I restore with the same onconfig file? (AM i doing the backup
command
wrong?)
2. is there any way that archecker can obtain the table from compressed
image?
Tried to understand what Art stated in
http://members.iiug.org/forums/ids/index.cgi/noframes/read/25451 but did not
helped me much.
Cannot do a full restore due to space limitations.
Looking forward any clarification.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Thank you Mike for the fast response. Physical restore will do for me, I'll try to restore it in a dump file or external table and get back with the results. Thank you. Aleksandar
Try the following, just until seeing the initial screep stating the spaces
and chunks and gain confidence that your backup is usable:
ontape -p -t /backup/backup
ontape -t STDIO -s -L 0 > /backup/backup, even with
BACKUP_FILTER /usr/bin/gzip,
will not produce the same output as
ontape -t STDIO -s -L 0 | /usr/bin/gzip > /backup/backup without
BACKUP_FILTER.
Only the backup payload will get filtered, not ontape's leading, trailing
and other 'meta' blocks.
Can't answer the TLR & BACKUP_FILTER question from where I currently stand.
From: "ALEKSANDAR IVANOVSKI" <aleksandar.ivanovski@gmail.com>
To: ids@iiug.org
Date: 09/21/2017 08:35 PM
Subject: Archecker single table restore compressed ontape [39957]
Sent by: ids-bounces@iiug.org
Hi,
AIX 7.1 IDS 11.7FC4.
I have a nightly cron that runs the following ontape backup:
# ontape -t STDIO -s -L 0 > /backup/backup
# onstat -c |grep -i filter
BACKUP_FILTER /usr/bin/gzip
RESTORE_FILTER /usr/bin/gunzip
messages in the log that rootdbs, datadbs ... started ... and after a while
completed successfully.
I need to restore 1 table from this image using archecker that I created
few
days ago. However, if i run gzip -l or even gzip -cd ... it says that this
file is not a valid gz file i.e. I cannot uncompress it.
2 questions:
1. are my backup images corrupted so that I will never be able to use them
even if I restore with the same onconfig file? (AM i doing the backup
command
wrong?)
2. is there any way that archecker can obtain the table from compressed
image?
Tried to understand what Art stated in
http://members.iiug.org/forums/ids/index.cgi/noframes/read/25451 but did
not
helped me much.
Cannot do a full restore due to space limitations.
Looking forward any clarification.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Dear all, I've managed to do # archecker -tvds -f kr_kredit_rata_restore.sql it is running right in this moment and probably will restore the table. Filters in onconfig are set as on the machine that the backup was done STATUS: IBM Informix Dynamic Server Version 11.70.FC4 Program Name: archecker Version: 8.0 Released: 2011-10-12 22:56:09 CSDK: IBM Informix CSDK Version 3.70 ESQL: IBM Informix-ESQL Version 3.70.FN305 Compiled: 10/12/11 22:56 on AIX 1 6 STATUS: Arguments [-tvds -f kr_kredit_rata_restore.sql] STATUS: AC_STORAGE /tmp STATUS: AC_MSGPATH /tmp/ac_msg.log STATUS: AC_VERBOSE on STATUS: AC_TAPEDEV /export_dir/ STATUS: AC_TAPEBLOCK 32 KB STATUS: AC_LTAPEDEV /dev/null STATUS: AC_LTAPEBLOCK 32 KB STATUS: AC_RESTORE_FILTER /usr/bin/gunzip STATUS: AC_SCHEMA kr_kredit_rata_restore.sql TIME: [2017-09-22 06:50:29] All old validation files removed. STATUS: Dropping old log control tables STATUS: Restore only physical image of archive STATUS: Target table [kr_kredit_rata_restore] already created STATUS: SQL [SET INDEXES, TRIGGERS, CONSTRAINTS FOR kr_kredit_rata_restore DISABLED] STATUS: Extracting table enc3:kr_kredit_rata into enc3:kr_kredit_rata_restore STATUS: Archive file /export_dir/escb_19_L0 STATUS: Tape type: Archive Backup Tape STATUS: OnLine version: IBM Informix Dynamic Server Version 11.70.FC4 STATUS: Archive date: Wed Sep 20 03:00:00 2017 STATUS: Archive level: 0 STATUS: Tape blocksize: 32768 STATUS: Tape size: 0 STATUS: Tape number in series: 1 STATUS: Using restore filter program: '/usr/bin/gunzip'. STATUS: Starting restore filter TIME: [2017-09-22 06:50:30] Phys Tape 1 started STATUS: starting to scan dbspace 1 created on 2017-09-20 03:00:00. STATUS: Archive timestamp 0X8332CE56. STATUS: starting to scan dbspace 2 created on 2017-09-20 03:00:00. STATUS: Archive timestamp 0X8332CE56. Thank you all for helping me out. One more question, the archecker requested the file name escb_19_L0 (which is host name on the restoring MACHINE _19 _L0(i guess its level0 archive). I did rename the file My question is, where this name or parts of it(19) comes from??? Again thank you, Aleksandar
Glad that it's working. The "19" in the name of the backup is the SERVERNUM (as listed in the archive) of the Informix Instance on which you performed the archive. It's used here to uniquely identify the Informix server on the machine - in case you have more than one. Mike -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ALEKSANDAR IVANOVSKI Sent: Thursday, September 21, 2017 11:06 PM To: ids@iiug.org Subject: Re: Archecker single table restore compressed onta [39968] Dear all, I've managed to do # archecker -tvds -f kr_kredit_rata_restore.sql it is running right in this moment and probably will restore the table. Filters in onconfig are set as on the machine that the backup was done STATUS: IBM Informix Dynamic Server Version 11.70.FC4 Program Name: archecker Version: 8.0 Released: 2011-10-12 22:56:09 CSDK: IBM Informix CSDK Version 3.70 ESQL: IBM Informix-ESQL Version 3.70.FN305 Compiled: 10/12/11 22:56 on AIX 1 6 STATUS: Arguments [-tvds -f kr_kredit_rata_restore.sql] STATUS: AC_STORAGE /tmp STATUS: AC_MSGPATH /tmp/ac_msg.log STATUS: AC_VERBOSE on STATUS: AC_TAPEDEV /export_dir/ STATUS: AC_TAPEBLOCK 32 KB STATUS: AC_LTAPEDEV /dev/null STATUS: AC_LTAPEBLOCK 32 KB STATUS: AC_RESTORE_FILTER /usr/bin/gunzip STATUS: AC_SCHEMA kr_kredit_rata_restore.sql TIME: [2017-09-22 06:50:29] All old validation files removed. STATUS: Dropping old log control tables STATUS: Restore only physical image of archive STATUS: Target table [kr_kredit_rata_restore] already created STATUS: SQL [SET INDEXES, TRIGGERS, CONSTRAINTS FOR kr_kredit_rata_restore DISABLED] STATUS: Extracting table enc3:kr_kredit_rata into enc3:kr_kredit_rata_restore STATUS: Archive file /export_dir/escb_19_L0 STATUS: Tape type: Archive Backup Tape STATUS: OnLine version: IBM Informix Dynamic Server Version 11.70.FC4 STATUS: Archive date: Wed Sep 20 03:00:00 2017 STATUS: Archive level: 0 STATUS: Tape blocksize: 32768 STATUS: Tape size: 0 STATUS: Tape number in series: 1 STATUS: Using restore filter program: '/usr/bin/gunzip'. STATUS: Starting restore filter TIME: [2017-09-22 06:50:30] Phys Tape 1 started STATUS: starting to scan dbspace 1 created on 2017-09-20 03:00:00. STATUS: Archive timestamp 0X8332CE56. STATUS: starting to scan dbspace 2 created on 2017-09-20 03:00:00. STATUS: Archive timestamp 0X8332CE56. Thank you all for helping me out. One more question, the archecker requested the file name escb_19_L0 (which is host name on the restoring MACHINE _19 _L0(i guess its level0 archive). I did rename the file My question is, where this name or parts of it(19) comes from??? Again thank you, Aleksandar **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Hi,
The archecker itself and the ontape is probably working but I have additional
problems getting the log info:
ERROR: Unable to convert page(27_603909) for partnum 2099394
ERROR: "PUT CURSOR" failed
ERROR: -1226: Decimal or money value exceeds maximum precision.
So i dig a little bit on the net trying to implement external table
http://www-01.ibm.com/support/docview.wss?uid=swg21655476 and using
("/tmp/tlr.unl", delimited ); but no luck. I got a file in the tmp directory
with few thousand more lines than the actual row count should be, on the other
hand the rows were unusable so the problem persists.
I also tried to change insert into new_table
select * from real_table (insert into A,B,C select A,B,C from ...)
with the actual rows from both tables again no luck
AFAIS the environments between the backup server and restore server are all
the same, maybe something in the onconfigs that I am not aware of regarding
decimal and money precision and scale???
Do you have any idea what this can be caused from?
Is there and way I can implement SQLCA.SQLCODE within the scope of conf file
of the archecker so that I can get the error???
Other than this here is the conf file:
database db;
create table xxx
(
sektor char(9),
godina char(4),
partija char(8),
stavka smallint,
glavnica decimal(16,2),
kamata decimal(16,2),
dospeanost date,
f_prefrlena char(1),
eks_kamata decimal(16,2),
int_kamata decimal(16,2),
stapka decimal(8,5),
stapka_eks decimal(20,8)
) in datadbs ;
create table xxx_restore
(
sektor char(9),
godina char(4),
partija char(8),
stavka smallint,
glavnica decimal(16,2),
kamata decimal(16,2),
dospeanost date,
f_prefrlena char(1),
eks_kamata decimal(16,2),
int_kamata decimal(16,2),
stapka decimal(8,5),
stapka_eks decimal(20,8)
) in datadbs ;
insert into xxx_restore
select * from xxx ;
restore to current with no log;
So I guess the problem is somewhere within the decimal fields. The original
table has not been altered for years ....
Am I missing something, maybe the extend sizes in the conf file ... lock mode
or something??
Any help?
Thank you
Aleksandar
Solved. The problem was in wrong copy paste for the first time definition in the archecker file, after which the definition of the table (empty) on the IDS was not the same with the conf file itself. Now everything works perfect. Thank you, Aleksandar