need to backup the individual database
A user on IDS 11.5/RHEL asked whether ontape can back up just one of three databases. Answer: no — ontape always backs up the whole instance (minus temp dbspaces). Suggested alternatives: use dbexport (which locks the database) or Art Kagel's myexport/myimport replacement (no locking), or use ON-Bar to back up only the dbspaces holding that database, though critical dbspaces (root, logs) and logical logs are still needed for a consistent restore. It was also clarified that ontape's -U option only sets unbuffered logging, it does not limit the backup's scope. A "fake" ON-Bar backup can avoid a forced level 0 afterwards, but complicates any later recovery; running periodic level 0 plus incremental level 1 backups was recommended instead.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I am working in RHEL 5.3 with IDS 11.5
Currently we are having 3 database
I would like to backup a single database, say db1
But "ontape -s" takes the backup of 3 databases
Do i have any option in ontape to take individual database backup?
Thanks in advance!
Regards,
Lakki
You can use the dbexport utility to extract or backup a single database.
Note that it will attempt to lock the database and so may either fail or
lock out other users until it is done. If that is a problem you can get my
dbexport/dbimport replacement utility package, myexport, from the Oninit
website (www.oninit.com/utils) or the IIUG Software Repository (
www.iiug.org/software).
Myexport also requires two other packages and optionally a third:
utils2_ak
sqlcmd (version 80 or later)
myonpload (optional if you want to use the myexport/myimport options that
use the HPLoader for the actual IO instead of Jonathan Leffler's sqlcmd
package).
Myexport does not lock the database so you can use the -m & -u options to
minimize any inconsistencies between tables and to reload with constraint
and index filtering. Myexport and myimport are compatible with dbexport and
dbimport unless you use -m and/or -u.
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Tue, Apr 7, 2009 at 11:27 AM, LAKSHMI DEVI PALANISSAMY <
lakshmidevip@hcl.in> wrote:
> Hi,
>
> I am working in RHEL 5.3 with IDS 11.5
>
> Currently we are having 3 database
> I would like to backup a single database, say db1
>
> But "ontape -s" takes the backup of 3 databases
> Do i have any option in ontape to take individual database backup?
>
> Thanks in advance!
>
> Regards,
> Lakki
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016369fa3743d90ed0466f8dcac
Hi,
short answer: No.
Ontape always does the equivalent of an (ON-Bar)
whole system backup (see below).
Longer answer:
With ontape you always have to backup everything
(except temporary dbspaces which are not backed up).
With ON-Bar you can backup dbspaces individually.
And if you put separate databases into separate dbspaces,
then you can (almost) backup each database separately.
Note that you have to backup the critical dbspaces also
(at some point), i.e. rootdbspace and dbspaces containing
logical and physical log, etc.
And, as you'd be backing up single dbspaces (as opposed
to a whole system backup), you have to also backup and
restore logical logs in order to ever get to a consistent state
again after any restore.
TIA, Martin
--
Martin Fuerderer
IBM Informix Development Munich, Germany
Information Management
IBM Deutschland Research & Development GmbH
Chairman of the Supervisory Board: Martin Jetter
Board of Management: Erich Baier
Corporate Seat: Boeblingen, Germany
Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294
ids-bounces@iiug.org wrote on 07.04.2009 17:27:25:
> Hi,
>
> I am working in RHEL 5.3 with IDS 11.5
>
> Currently we are having 3 database
> I would like to backup a single database, say db1
>
> But "ontape -s" takes the backup of 3 databases
> Do i have any option in ontape to take individual database backup?
>
> Thanks in advance!
>
> Regards,
> Lakki
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Can i use "ontape -s -L 0 -U db1"?
But upon restore the same, it leads me to a force level 0 backup?
Is it Ok?
NO! The -U db1 option does NOT backup only database db1, it still backs up
the entire server but then it marks database db1 as an Unbuffered Log
database. That's all. IF and ONLY IF a database resides entirely withing a
subset of dbspaces and ONLY if you are using onbar, you can backup only
those dbspaces that contain the one database. However, at recovery time,
you will still have to have a recent level 0 archive that includes the
critical dbspaces in order to be able to restore your one database's
dbspaces successfully.
Why do you want to only archive a single database?
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Tue, Apr 7, 2009 at 12:16 PM, LAKSHMI DEVI PALANISSAMY <
lakshmidevip@hcl.in> wrote:
> Can i use "ontape -s -L 0 -U db1"?
>
> But upon restore the same, it leads me to a force level 0 backup?
> Is it Ok?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016368e1c09566dee0466f98398
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> LAKSHMI DEVI PALANISSAMY
> Sent: Tuesday, April 07, 2009 11:16 AM
> To: ids@iiug.org
> Subject: Re: need to backup the individual database [15459]
>
> Can i use "ontape -s -L 0 -U db1"?
>
The -U option allows you to change the logging mode of a database to
unbuffered logging. It still performs a backup of all databases and
spaces when it is executed.
--EEM
> But upon restore the same, it leads me to a force level 0 backup?
> Is it Ok?
>
>
>
************************************************************************
**
> *****
> Forum Note: Use "Reply" to post a response in the discussion forum.
Out of the 3 databases, i do rarely use the other 2.
Hence i decided to go for archiving a single database.
If there is no other way, i can use dbexport command to archive the single
database!
But i have a concern that whenever i do a restore with backup tape obtained by
"ontape -s -U db1", do i need to change the logging status again by doing a
level 0 backup?
Thanks,
Lakshmi
Can i do a fake backup using onbar utility to avoid a forced level 0 backup
after restore?
Dear ,
If you often change only "this" database, why not execute a Level 0 backup of
the instance (suggest saturday or sunday or 1st of the month) and the other
days execute a 'incremental' backup of Level 1 ??????????
BR
R Ferronato
> To: ids@iiug.org
> From: lakshmidevip@hcl.in
> Subject: Re: need to backup the individual database [15465]
> Date: Tue, 7 Apr 2009 15:29:57 -0400
>
> Can i do a fake backup using onbar utility to avoid a forced level 0 backup
> after restore?
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
_________________________________________________________________
News, entertainment and everything you care about at Live.com. Get it now!
http://www.live.com/getstarted.aspx
On Tue, Apr 7, 2009 at 12:29, LAKSHMI DEVI PALANISSAMY
<lakshmidevip@hcl.in> wrote:
> Can i do a fake backup using onbar utility to avoid a forced level 0 backup
> after restore?
Yes, but if you have to recover again, you will need to recover from
the original (non-fake) level 0 and any subsequent level 1 and level 2
(up to one of each), plus roll forward from logical logs. This is
usually even more onerous than the level 0 backup.
Backups are not for fun - they are there to protect your job.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease
to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
George Burns - "I spent a year in that town, one Sunday." -
http://www.brainyquote.com/quotes/authors/g/george_burns.html