Informix - Different onbar backup and restore dbs order

[Nick Wickens]

I perform a Whole system backup of our 120Gb Informix database on a nightly basis using onbar with Omniback 3.5.

I have noticed that as we moved to two tapes for each backup (shortly to go to three), that whole system restores involve a lot of tape swapping and searching and I have tied the reason for this down to the fact that the backup order is defined firstly by the dbspace type(ish) and then by Dbspace number as defined in sysutils when the dbspaces are created. However the restore order is defined by the order in the ixbar file which seems to be defined by dbspace type followed by alphabetical order.

The upshot of this is that restore of dbspaces takes place in a different order to the original backup which even for a single tape backup is going to involve a lot of tape searching let alone when the tape is being swapped out by an autochanger.

Other than the headache of renumbering the dbs numbers or renaming into alphabetical order has anyone a quick solution ?

[Pete Randall]

Hi Nick,

How about bigger tapes? DLT's or such?

Just a thought.


Pete

[Nick Wickens]

Hi Pete

Using DLT7000 tapes already (LTO is still several budgets in the future :@) )- Even if I could tie the backup down to one tape theres still going to be a lot of unnecessary tape searching going on so I am guessing that performance is being compromised even at that level.

[Pete Randall]

Nick,

I figured that was too obvious but had to ask? I'll run this by my DBA when he gets in in an hour or so. He's pretty sharp with this stuff. Maybe he'll have an idea.


Pete

[Rainer von Bongartz]

What about a backup where you define the order od the backed up db spaces ??

onbar supports the following
syntax

onbar -b -L -f

In file you define the dbspaces to backup. This also defines the order.

To restore use

onbar -r -p -f

for a physical restore and afterwards

onbar -l

for a logical restore.

This will ensure that the restore is done in the same order as the backup


Regards
Rainer

[Pete Randall]

Nick,

Here's my DBA's comments:

=======================
> I have noticed that as we moved to two tapes for each backup (shortly to
> go to three), that whole system restores involve a lot of tape swapping
> and searching and I have tied the reason for this down to the fact that
> the backup order is defined firstly by the dbspace type(ish) and then by
> Dbspace number as defined in sysutils when the dbspaces are created.
> However the restore order is defined by the order in the ixbar file
> which seems to be defined by dbspace type followed by alphabetical order.

I think the last sentence is wrong. In our ixbar, its the order that
the backups of each DBspace were COMPLETED. Not started. For
example last night. I suspect that this person is NOT doing a parallel
backup.

If you look at /opt/informix/online.log
you can see that onbar/omniback seems to follow its own logic:
23:00:32 Level 0 Archive started on rootdbs
23:00:52 Archive on rootdbs Completed.
23:00:52 Checkpoint Completed: duration was 0 seconds.
23:00:54 Checkpoint Completed: duration was 0 seconds.
23:00:54 Level 0 Archive started on dbs08
23:00:54 Level 0 Archive started on dbs10
23:00:57 Level 0 Archive started on bdbs06
23:00:58 Checkpoint Completed: duration was 0 seconds.
23:00:58 Level 0 Archive started on dbs03
23:00:58 Level 0 Archive started on dbs02
23:00:59 Checkpoint Completed: duration was 0 seconds.
23:00:59 Level 0 Archive started on logilog
23:00:59 Level 0 Archive started on physlog
23:01:00 Level 0 Archive started on dbs01
23:01:00 Checkpoint Completed: duration was 0 seconds.
23:01:00 Level 0 Archive started on dbs07
23:01:01 Level 0 Archive started on dbs09
23:01:01 Level 0 Archive started on dbs05
23:01:01 Level 0 Archive started on dbs04
=================


So, are you doing a parallel backup?


Pete

[Nick Wickens]

Hi Rainer

Thanks but already considered that - problem is that you then need to recover logical logs as well (Only a whole system backup will recover without log recovery) and we don't like the idead of recovering logs here, prefering a complete backup and restore from the least amount of tapes possible.

Also you need to set up a process to ensure that new dbaspaces are included in the backup spec when they are created otherwise several months down the line you find that you can't recover that system after all.

[Nick Wickens]

Hi Pete

No - Whole system onbar backups can only be done sequentially and not parallel. We could do parallel backups but only if I change to the suggestion above from Rainer.

[Zafar A. Mohammed_1]

1. What is the Informix Version?

2. Try to increase Bar Workers(XPS).


Thanks
Zafar

[Rainer von Bongartz]

Nick,

You could simply create a file
containing you db-spaces with an onstat -d command and some awk scripting before performing your backup.

QUESTION:

How do you do your backup not caring about logical logs ??

Even a whole system backup with onbar -b -w will backup logical logs. So you'll always need the logical log tapes for system recovery.

The only way to backup without logical logs is to block the database server (onmode -l ; onmode -c block) and perform a backup.
But during this time INFORMIX will be in read-only mode .

Regards
Rainer

[Rainer von Bongartz]

Sorry but my last post is not correct.

I've read it again and this are the differences:

The main difference between a whole backup (onbar -b -w) and a
parellel backup (onbar -b -L 0) is that a whole backup doesn't need logical log information to restore (it includes it in the backup)

Regards
Rainer

[Nick Wickens]

Informix version 7.31

onbar worker threads not really an issue if not doing parallel backups.

We like whole system backups because -

(1) Reduces number of tapes required for recoveries.

(2) We dont have to send log tapes offsite every morning for DR purposes.

(3) We have never been 100% confident in recoveries that involve log tapes.

(4) Our system day allows for a 30 minute window in processing at which point we do the backup. We would always want to get back to that point in time in a recovery situation for operational reasons and log tape recoveries are therefore not required. We only do log backups anyway to keep Informix and onbar happy !.

At the end of the day Informix whole system backup is a valid option but unless I have got something wrong with the setup its a very badly conceived process as in the main data should go on the tape in the same order it is going to come off to achieve best performance -

Unless you know different ...?

[Nick Wickens]

Informix/IBM have now got back to us and stated that, Yes this does appear to be a problem with onbar and they are now investigating.

Still open to any other work around solutions other than not doing whole system backups.

[Michael Tully]

Nick,

Have you looked into actually upgrading your Informix version up to 9.xxxx ? I am quite sure that we backup at least one 120Gb database daily onto a single tape (DLT7000) using omniback 4.0/onbar. I'll check tomorrow morning my time if you wish, 14 hours from now....

Regards
Michael
"When I have trouble spelling, it's called fat finger syndrome."

[Nick Wickens]

Hi Michael

In the process of testing Informix 9 with our aps at this very time.

My Board have just purchased me some nice replacement rp7410's so we are upgrading Informix and HP-UX to 11i at the same time.

Also gives me a chance to get Ominback 3.5 up to Dataprotect 5.

New Toys to play with :@)