Hello,
This goal of this email is to solicit suggestions to correct the gap in our
current backup/recovery strategy and to implement a new (possibly better)
strategy for the future environment - both represent 24 x 7 large "active"
warehouse under the DB2 UDB EEE. "Active" as the new industry definition
means that it encompasses multiple data marts and its processing covers much
more than complex reports, i.e. it accommodates daily Java based "inserts"
with some "updates", daily autoloader jobs, daily and monthly Cognos cubes
building processes, monthly and quarterly purging via "delete", etc.
Current Processing Environment:
RS/6000-S85
24 CPU
56 GB
ESS Storage (2 ESS)
6.2 TB total allocated
~4.5 TB (data + indexes)
AIX 4.3.3
DB2 UDB EEE 7.2 FixPak4
17 logical nodes (16 data and 1 catalog)
12-15 MSTR complex reports (MicroStrategy queries)
2 Cognos Transformers
2 Autoloaders
Hourly up to 10 Java based insert jobs (with a few updates)
----------------------------------------------------------------------------
---------------------------------------------
NOTE: though officially we have weekly maintenance window every Sunday, we
can only guarantee that queries will not run. The jobs have so many
dependencies on the files that arrive from MVS that it is almost impossible
to automate the time for maintenance window. However, we will attempt to do
so.
Backup/Recovery Problem:
Our backup strategy consists of critical tablespace level online backups,
starting with the catalog tablespace. It has been decided during the initial
database design stage that due to budget constraint, the rest of the
tablespaces can be re-done, and since the data files are available, by using
autoloader tables can be easily re-loaded. We have also all the archive logs
since the activation of the log retain and user exit parameters. To turn the
logging ON, we fooled the database by taking a full database backup to
/dev/null. Also, our tablespaces that participate in the autoloader, have
NONRECOVERABLE option in their control files to prevent "backup pending"
mode. Our backup strategy without having a full database backup image means
that we will be able to recover critical tablespaces, but will not be able
to recover a database in case of disaster scenario.
Our preliminary backup plan to correct the existing gap is as following:
once a month full off-line database backup
weekly tablespace backup of the same critical tablespaces
keep the archive logs from the last successful full database backup
For example:
Backup
full database backup off-line (~4.5 TB) Sunday 3/02/03
Catalog and critical tablespaces on-line backup Sunday 3/09/03; 3/16/03;
3/23/03; 3/30/03
Keep the archived logs from after the db backup on 3/02/03
Next full database backup off-line 4/06/03
Recovery
Assuming, that the database crashed on 3/25 and needs to be recovered
Is this would be the best plan of actions?
1) Restore the database from the previous good full db backup image
(3/02/03)
2) Rollforward to end of logs
OR
1) Restore the database from the previous good full db backup image
(3/02/03)
2) Restore tablespaces from the last good images (3/23/03)
2) Rollforward ?
I am sure that there are some UDB users who have implemented backup/recovery
strategies for VLDB. I am writing to solicit your expert advice.
Because of this exposure and a very tight schedule to correct it, I would
like to limit our testing to only those scenarios that are 1) fairly easy to
implement; 2) already known as "working" in similar environments
===========================================================
I am also looking for any ideas/suggestions about the future backup/recovery
strategy, which may not be limited to the database utilities only, which
will be implemented in the following environment:
Future Processing Environment:
1 P690 divided into 2 LPARs, with total of 32 CPU and 64 GB of memory
3 ESS
15 or more TB of total allocated disk
AIX 5.1 (possibly 5.2 by the end of Oct. 2003)
DB2 UDB EEE 7.2 FixPak8 initially (V. 8 64-bit possibly by the end of Oct.
2003)
I understand that it is always a trade-off between cost and best solution.
That is why I would like to consider and prepare multiple options for our
customers.
Thanks in advance for your help!
Ellen Klebaner-Reys
Data Management Services
Inovant - a Visa Solutions Company
[EMAIL PROTECTED]/650-432-1746 m/s: 3125-1D
-
::: When replying to the list, please use 'Reply-All' and make sure
::: a copy goes to the list ([EMAIL PROTECTED]).
*** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
*** For more information, check http://www.db2eug.uni.cc
