Re: Restore a specific table from a ontape backup
Larry (IDS 11.50.FC7 on Solaris 10) wanted to restore a single table from an ontape archive under a different name so he could compare it with the current table, and later asked about rolling forward to a point in time a few hours ago when last night's archive was missing. Responders pointed him to archecker table-level restore (with links to IBM articles): using an older archive plus logical logs, archecker can restore to a point in time. In the archecker command file, the first CREATE TABLE only describes the source table's layout so rows can be extracted from the archive pages; the second table (via INSERT ... SELECT) receives the data, so the original is untouched. Notes: archecker prompts for logical logs unless a physical-only restore is requested, it does not replay DROP TABLE records, and BACKUP_FILTER archives may cause complications. Larry thanked the group; no problems reported afterwards.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Solaris 10
IDS 11.50.FC7
I need to restore one table from an ontape backup to a different location or
table name so that I can compare a current table to what it was last night.
What is the process for doing this?
Thank you.
Larry
Original post:
Solaris 10
IDS 11.50.FC7
I need to restore one table from an ontape backup to a different location or
table name so that I can compare a current table to what it was last night.
What is the process for doing this?
Thank you.
Larry
Response:
I think what you are looking to do would be covered as 1 of the examples in
this link of using archecker and table level restores:
http://www.ibm.com/developerworks/data/library/techarticle/dm-0608kim/
Jacques Renaut
IBM Informix Advanced Support
I appreciate the response, but I have another issue. Apparently, there was no
backup last night, long story, and all I have are the logical logs. Is there a
way to restore the table to a new table as a copy and apply logical logs to a
certain point in time?
Larry
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of JACQUES RENAUT
<jrenaut@us.ibm.com>
Sent: Wednesday, October 12, 2016 2:36 PM
To: ids@iiug.org
Subject: Re: Restore a specific table from a ontape backup [37972]
Original post:
Solaris 10
IDS 11.50.FC7
I need to restore one table from an ontape backup to a different location or
table name so that I can compare a current table to what it was last night.
What is the process for doing this?
Thank you.
Larry
Response:
I think what you are looking to do would be covered as 1 of the examples in
this link of using archecker and table level restores:
http://www.ibm.com/developerworks/data/library/techarticle/dm-0608kim/
Perform point-in-time table-level restore in Informix Dynamic
Server<http://www.ibm.com/developerworks/data/library/techarticle/dm-0608kim/>;
www.ibm.com
This article describes how to perform point-in-time table-level restores that
extract tables or portions of tables from archives and logical logs.
Table-level restore is a new feature for IBM Informix Dynamic Server Version
10.0. This feature is useful where portions of a database, a table, a portion
of a table, or a set of tables need to be recovered and also useful in
situations where tables need to be moved across server versions or platforms.
Jacques Renaut
IBM Informix Advanced Support
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Original post: I appreciate the response, but I have another issue. Apparently, there was no backup last night, long story, and all I have are the logical logs. Is there a way to restore the table to a new table as a copy and apply logical logs to a certain point in time? Larry Response: Well, if you have some archive and all the logical logs since that archive, then you should still be able to do the table level restore to generate the table as of a specific point in time...I believe that was mentioned in the article linked. Unless I'm not understanding what you are asking. Jacques Renaut IBM Informix Advanced Support
Ok. And archecker should ask me for logical logs as well if I give it the
correct parameters?
Larry
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of JACQUES RENAUT
<jrenaut@us.ibm.com>
Sent: Wednesday, October 12, 2016 3:14 PM
To: ids@iiug.org
Subject: Re: Restore a specific table from a ontape backup [37974]
Original post:
I appreciate the response, but I have another issue. Apparently, there was no
backup last night, long story, and all I have are the logical logs. Is there a
way to restore the table to a new table as a copy and apply logical logs to a
certain point in time?
Larry
Response:
Well, if you have some archive and all the logical logs since that archive,
then you should still be able to do the table level restore to generate the
table as of a specific point in time...I believe that was mentioned in the
article linked. Unless I'm not understanding what you are asking.
Jacques Renaut
IBM Informix Advanced Support
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Another question is,
If I have the table but I just need to recover it to a point in time a few
hours ago, is that possible without going through the table restore from
archive?
Larry
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of JACQUES RENAUT
<jrenaut@us.ibm.com>
Sent: Wednesday, October 12, 2016 3:14 PM
To: ids@iiug.org
Subject: Re: Restore a specific table from a ontape backup [37974]
Original post:
I appreciate the response, but I have another issue. Apparently, there was no
backup last night, long story, and all I have are the logical logs. Is there a
way to restore the table to a new table as a copy and apply logical logs to a
certain point in time?
Larry
Response:
Well, if you have some archive and all the logical logs since that archive,
then you should still be able to do the table level restore to generate the
table as of a specific point in time...I believe that was mentioned in the
article linked. Unless I'm not understanding what you are asking.
Jacques Renaut
IBM Informix Advanced Support
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Original post: Another question is, If I have the table but I just need to recover it to a point in time a few hours ago, is that possible without going through the table restore from archive? Larry Response: So your fist question of should archecker prompt you for log tapes...I believe the answer would be yes, I have not personally done that procedure, but that's the only way it could do any sort of point in time recovery though. As for this question, no not to my knowledge. Well, technically if you had an RSS server already set up with delayed apply turned on...you could look at the table on the RSS server...but in the case of just having 1 instance that's currently just a stand alone server, I'm not aware of any other option other then a table level restore. Jacques Renaut IBM Informix Advanced Support
Thank you so much. I have one final question and then I will quit bothering
you. I see that you create a command file for use with archecker. An example
it uses for restoring a data from an existing table to a new table is
database stores7;
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 datadbs;
create table customer2
(
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 datadbs;
insert into customer2 select * from customer;restore to '2006-03-24 21:10:08';
Question:
I am assuming that the first create is just for informational purposes and it
will not touch the existing table. It will only load data into the new table?
Larry
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of JACQUES RENAUT
<jrenaut@us.ibm.com>
Sent: Wednesday, October 12, 2016 3:33 PM
To: ids@iiug.org
Subject: Re: Restore a specific table from a ontape backup [37977]
Original post:
Another question is,
If I have the table but I just need to recover it to a point in time a few
hours ago, is that possible without going through the table restore from
archive?
Larry
Response:
So your fist question of should archecker prompt you for log tapes...I believe
the answer would be yes, I have not personally done that procedure, but that's
the only way it could do any sort of point in time recovery though.
As for this question, no not to my knowledge. Well, technically if you had an
RSS server already set up with delayed apply turned on...you could look at the
table on the RSS server...but in the case of just having 1 instance that's
currently just a stand alone server, I'm not aware of any other option other
then a table level restore.
Jacques Renaut
IBM Informix Advanced Support
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
You can use archecker to do exactly this.
There is quite a bit of information online on how to do this, but I am quite
partial to this article:
http://www.ibmbigdatahub.com/blog/database-utility-rescue
Mike
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LARRY
SORENSEN
Sent: Wednesday, October 12, 2016 2:09 PM
To: ids@iiug.org
Subject: Re: Restore a specific table from a ontape bac.... [37971]
Solaris 10
IDS 11.50.FC7
I need to restore one table from an ontape backup to a different location or
table name so that I can compare a current table to what it was last night.
What is the process for doing this?
Thank you.
Larry
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Larry - that is correct. archecker needs the definition of the table it's
trying to locate in the archive - that's the first "create". While it's
kind of scary because you don't want your original table to be zapped, it is
the second definition that will be loaded with data from the archive.
Mike
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LARRY
SORENSEN
Sent: Wednesday, October 12, 2016 3:39 PM
To: ids@iiug.org
Subject: Re: Restore a specific table from a ontape backup [37978]
Thank you so much. I have one final question and then I will quit bothering
you. I see that you create a command file for use with archecker. An example
it uses for restoring a data from an existing table to a new table is
database stores7;
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 datadbs;
create table customer2
(
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 datadbs;
insert into customer2 select * from customer; restore to '2006-03-2421:10:08';
Question:
I am assuming that the first create is just for informational purposes and
it will not touch the existing table. It will only load data into the new
table?
Larry
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of JACQUES
RENAUT <jrenaut@us.ibm.com>
Sent: Wednesday, October 12, 2016 3:33 PM
To: ids@iiug.org
Subject: Re: Restore a specific table from a ontape backup [37977]
Original post:
Another question is,
If I have the table but I just need to recover it to a point in time a few
hours ago, is that possible without going through the table restore from
archive?
Larry
Response:
So your fist question of should archecker prompt you for log tapes...I
believe the answer would be yes, I have not personally done that procedure,
but that's the only way it could do any sort of point in time recovery
though.
As for this question, no not to my knowledge. Well, technically if you had
an RSS server already set up with delayed apply turned on...you could look
at the table on the RSS server...but in the case of just having 1 instance
that's currently just a stand alone server, I'm not aware of any other
option other then a table level restore.
Jacques Renaut
IBM Informix Advanced Support
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Yes, archecker will allow you to restore a table to a point-in-time. If I
remember correctly though there were some complications, like this wasn't
possible if you use the BACKUP_FILTER when you do the archive, but that may
be Informix version dependent.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LARRY
SORENSEN
Sent: Wednesday, October 12, 2016 3:17 PM
To: ids@iiug.org
Subject: Re: Restore a specific table from a ontape backup [37975]
Ok. And archecker should ask me for logical logs as well if I give it the
correct parameters?
Larry
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of JACQUES
RENAUT <jrenaut@us.ibm.com>
Sent: Wednesday, October 12, 2016 3:14 PM
To: ids@iiug.org
Subject: Re: Restore a specific table from a ontape backup [37974]
Original post:
I appreciate the response, but I have another issue. Apparently, there was
no backup last night, long story, and all I have are the logical logs. Is
there a way to restore the table to a new table as a copy and apply logical
logs to a certain point in time?
Larry
Response:
Well, if you have some archive and all the logical logs since that archive,
then you should still be able to do the table level restore to generate the
table as of a specific point in time...I believe that was mentioned in the
article linked. Unless I'm not understanding what you are asking.
Jacques Renaut
IBM Informix Advanced Support
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
The first table is there for information purpose only. It use original
customer table to know how to extract the rows from the data pages. You
can then place
the data in any target table using the insert/select syntax (including a
remote system).
Yes archecker will prompt you for logical logs, unless you tell it to do a
physical only restore.
NOTE: Archecker will NOT replay drop tables log records. (Why restore a
table only to drop it??)
John F. Miller III
miller3@us.ibm.com
503-747-1366
ids-bounces@iiug.org wrote on 10/12/2016 02:39:10 PM:
> From: "LARRY SORENSEN" <LSORENSEN25@msn.com>
> To: ids@iiug.org
> Date: 10/12/2016 02:40 PM
> Subject: Re: Restore a specific table from a ontape backup [37978]
> Sent by: ids-bounces@iiug.org
>
> Thank you so much. I have one final question and then I will quit
bothering
> you. I see that you create a command file for use with archecker. An
example
> it uses for restoring a data from an existing table to a new table is
>
> database stores7;
> create table customer
> (>
> customer=5Fnum 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=5Fnum)
> ) in datadbs;
>
> create table customer2
> (>
> customer=5Fnum 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=5Fnum)
> ) in datadbs;
>
> insert into customer2 select * from customer;> restore to '2006-03-24 21:10:08';
>
> Question:
>
> I am assuming that the first create is just for informational purposes
and it
> will not touch the existing table. It will only load data into the new
table?
>
> Larry
>
> =5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=
=5F=5F=5F=5F=5F=5F=5F=5F
> From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of JACQUES
RENAUT
> <jrenaut@us.ibm.com>
> Sent: Wednesday, October 12, 2016 3:33 PM
> To: ids@iiug.org
> Subject: Re: Restore a specific table from a ontape backup [37977]
>
> Original post:
>
> Another question is,
>
> If I have the table but I just need to recover it to a point in time a
few
> hours ago, is that possible without going through the table restore from
> archive?
>
> Larry
>
> Response:
>
> So your fist question of should archecker prompt you for log
> tapes...I believe
> the answer would be yes, I have not personally done that procedure,
> but that's
> the only way it could do any sort of point in time recovery though.
>
> As for this question, no not to my knowledge. Well, technically if you
had an
> RSS server already set up with delayed apply turned on...you could
> look at the
> table on the RSS server...but in the case of just having 1 instance
that's
> currently just a stand alone server, I'm not aware of any other option
other
> then a table level restore.
>
> Jacques Renaut
> IBM Informix Advanced Support
>
>
>
***************************************************************************=
****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
***************************************************************************=
****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Yes. You can use the archecker utility to do a table restore from an
archive. Look in the Backup and Restore manyal or the online info center
for details.
Art
On Oct 12, 2016 16:09, "LARRY SORENSEN" <LSORENSEN25@msn.com> wrote:
> Solaris 10
>
> IDS 11.50.FC7
>
> I need to restore one table from an ontape backup to a different location
> or
> table name so that I can compare a current table to what it was last night.
> What is the process for doing this?
>
> Thank you.
>
> Larry
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7bae4520505daa053eb4850d
Yes archecker can restore from a previous archive and roll forward the logs.
Art
On Oct 12, 2016 17:07, "LARRY SORENSEN" <LSORENSEN25@msn.com> wrote:
> I appreciate the response, but I have another issue. Apparently, there was
> no
> backup last night, long story, and all I have are the logical logs. Is
> there a
> way to restore the table to a new table as a copy and apply logical logs
> to a
> certain point in time?
>
> Larry
>
> ________________________________
> From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of JACQUES
> RENAUT
> <jrenaut@us.ibm.com>
> Sent: Wednesday, October 12, 2016 2:36 PM
> To: ids@iiug.org
> Subject: Re: Restore a specific table from a ontape backup [37972]
>
> Original post:
>
> Solaris 10
>
> IDS 11.50.FC7
>
> I need to restore one table from an ontape backup to a different location
> or
> table name so that I can compare a current table to what it was last night.
> What is the process for doing this?
>
> Thank you.
>
> Larry
>
> Response:
>
> I think what you are looking to do would be covered as 1 of the examples in
> this link of using archecker and table level restores:
>
> http://www.ibm.com/developerworks/data/library/techarticle/dm-0608kim/
>
> Perform point-in-time table-level restore in Informix Dynamic
> Server<http://www.ibm.com/developerworks/data/library/
> techarticle/dm-0608kim/>
> www.ibm.com
> This article describes how to perform point-in-time table-level restores
> that
> extract tables or portions of tables from archives and logical logs.
> Table-level restore is a new feature for IBM Informix Dynamic Server
> Version
> 10.0. This feature is useful where portions of a database, a table, a
> portion
> of a table, or a set of tables need to be recovered and also useful in
> situations where tables need to be moved across server versions or
> platforms.
>
> Jacques Renaut
> IBM Informix Advanced Support
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c3aa3e7903dc053eb48bc6
Thank you all for your help.
Larry
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel
<art.kagel@gmail.com>
Sent: Wednesday, October 12, 2016 6:54 PM
To: ids@iiug.org
Subject: Re: Restore a specific table from a ontape backup [37986]
Yes archecker can restore from a previous archive and roll forward the logs.
Art
On Oct 12, 2016 17:07, "LARRY SORENSEN" <LSORENSEN25@msn.com> wrote:
> I appreciate the response, but I have another issue. Apparently, there was
> no
> backup last night, long story, and all I have are the logical logs. Is
> there a
> way to restore the table to a new table as a copy and apply logical logs
> to a
> certain point in time?
>
> Larry
>
> ________________________________
> From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of JACQUES
> RENAUT
> <jrenaut@us.ibm.com>
> Sent: Wednesday, October 12, 2016 2:36 PM
> To: ids@iiug.org
> Subject: Re: Restore a specific table from a ontape backup [37972]
>
> Original post:
>
> Solaris 10
>
> IDS 11.50.FC7
>
> I need to restore one table from an ontape backup to a different location
> or
> table name so that I can compare a current table to what it was last night.
> What is the process for doing this?
>
> Thank you.
>
> Larry
>
> Response:
>
> I think what you are looking to do would be covered as 1 of the examples in
> this link of using archecker and table level restores:
>
> http://www.ibm.com/developerworks/data/library/techarticle/dm-0608kim/
Perform point-in-time table-level restore in Informix Dynamic
Server<http://www.ibm.com/developerworks/data/library/techarticle/dm-0608kim/>;
www.ibm.com
This article describes how to perform point-in-time table-level restores that
extract tables or portions of tables from archives and logical logs.
Table-level restore is a new feature for IBM Informix Dynamic Server Version
10.0. This feature is useful where portions of a database, a table, a portion
of a table, or a set of tables need to be recovered and also useful in
situations where tables need to be moved across server versions or platforms.
>
> Perform point-in-time table-level restore in Informix Dynamic
> Server<http://www.ibm.com/developerworks/data/library/
> techarticle/dm-0608kim/>
> www.ibm.com<http://www.ibm.com>;
> This article describes how to perform point-in-time table-level restores
> that
> extract tables or portions of tables from archives and logical logs.
> Table-level restore is a new feature for IBM Informix Dynamic Server
> Version
> 10.0. This feature is useful where portions of a database, a table, a
> portion
> of a table, or a set of tables need to be recovered and also useful in
> situations where tables need to be moved across server versions or
> platforms.
>
> Jacques Renaut
> IBM Informix Advanced Support
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c3aa3e7903dc053eb48bc6
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.