SATA Technology and Business Collage
Chapter Six – Backup and Recovery of the Data Warehouse
6.1 Introduction
One of the most important part is backup and recovery in a data warehouse. If your data is
precious to you, then treat it accordingly. Human errors, Hardware, Software, Network,
Process, system failure and the other unfrozen disasters can make your work vanish into
digital nothingness in no time. It makes sense backup your data on a regular basis. If such
failure affects the operation of a database system, it is required to recover the data base and
return to normal operation as quick as possible. There are utilities that can backup entire
partitions along with the operating system and everything else.
6.2 Types of Backup
The definitions below should help to clarify the issue. These definitions apply to the back up
of the database.
Complete Backup — the entire database is backed up at the same time. This includes
all database data files and the journal files.
Partial Backup — any backup that is not complete.
Cold Backup — Backup that is taken while the database is completely shut down. In
a multi- instance environment, all instances of that database must be shut down.
Hot Backup — any backup that is not cold is considered to be hot. The terminology
comes from the fact that the database engine is up and running hence hot. There are
special requirements that need to be observed if the hot backup is to be valid. These
will vary from RDBMS to RDBMS.
Consider how these important issues affect backup plan:
Realize that the restore is almost more important than the backup
Create a hot backup
Move your backup offline
Perfect the connection between your production and backup servers
Consider a warm backup
Always reassess the situation
6.3 Backup the Data Warehouse
Oracle RDBMS is basically a collection of physical database files. Backup and recovery
problems are most likely to occur at this level. Three types of files must be backed up:
database files, control files, and online redo log files. If you omit any of these files, you have
not made a successful backup of the database.
1 Data Warehousing and Data Mining
Instructor – Ephrem A.
SATA Technology and Business Collage
Cold backups shut down the database. Hot backups take backups while the database is
functioning. There are also supplemental backup methods, such as exports. Each type of
backup has its advantages and disadvantages. The major types of instance recovery are cold
restore, full database recovery, time-based recovery, and cancel-based recovery.
Oracle provides a server-managed infrastructure for backup, restore, and recovery tasks that
enables simpler, safer operations at terabyte scale. Some of the highlights are:
Details related to backup, restore, and recovery operations are maintained by the server
in a recovery catalog and automatically used as part of these operations. This reduces
administrative burden and minimizes the possibility of human errors.
Backup and recovery operations are fully integrated with partitioning. Individual
partitions, when placed in their own table spaces, can be backed up and restored
independently of the other partitions of a table.
Oracle includes support for incremental backup and recovery, enabling operations to be
completed efficiently within times proportional to the amount of changes, rather than the
overall size of the database.
The backup and recovery technology is highly scalable, and provides tight interfaces to
industry-leading media management subsystems. This provides for efficient operations
that can scale up to handle very large volumes of data. Open platforms for more
hardware options & enterprise-level platforms.
Keeping a local backup allows you to save data way in excess of the storage that you purchase
for online backup. For other backup products designed for local backup.
1. Create a Hot Backup
The best solution for critical systems is always to restore the backup once it has been made onto
another machine. This will also provide you with a “hot” backup that is ready to be used, should
your production database ever crash. If you can’t afford this higher cost, you should at least do
periodic test restores.
2. Consider a Warm (Not Hot) Backup
After you install hot backups and build a solid network, can you relax? Sort of, except there’s
one thing you still need to worry about. Hot backups are great because they are extremely
current. The minute data is changed on the primary server, you propagate that change to your
backup server.
But that very same close binding also comes with its own dangers. Consider what happens if
someone accidentally deletes the wrong data. For example, say an administrator accidentally
2 Data Warehousing and Data Mining
Instructor – Ephrem A.
SATA Technology and Business Collage
drops the accounts table in the database. (I’m not making this up; this actually happened at a
client of mine.) The inadvertent deletion will immediately get propagated to the backup, too.
If every second of downtime is expensive, you should consider creating a copy of the database
that is kept “warm”—i.e., fairly recent but not as recent as your hot backup. For example, you
might set the hot backup within a minute of the primary server, but deliberately keep your warm
backup 30 minutes behind your primary. That way, you only have to restore the last several
transaction logs rather than start from scratch.
3. Tape Backup Storage
Tape backup of customers’ data on SAN
Tape backup of Veritas disk storage
Tape backup of customers’ data direct from network
Tape archiving
Off-site tape storage
6.4 Data Warehouse Recovery Models
The model chosen can have a dramatic impact on performance, especially during data loads.
There are three recovery models: full, bulk-logged, and simple. The recovery model of a new
database is inherited from the model database when the new database is created. The model for a
database can be changed after the database has been created.
Full recovery provides the most flexibility for recovering databases to an earlier point in
time.
Bulk-logged recovery provides higher performance and lower log space consumption for
certain large-scale operations (for example, create index or bulk copy). It does this at the
expense of some flexibility of point-in-time recovery.
Simple recovery provides the highest performance and lowest log space consumption, but
it does so with significant exposure to data loss in the event of a system failure. When
using the simple recovery model, data is recoverable only to the last (most recent) full
database or differential backup. Transaction log backups are not usable for recovering
transactions because, in this model, the transactions are truncated from the log upon
checkpoint. This creates the potential for data loss. After the log space is no longer needed
for recovery from server failure (active transactions), it is truncated and reused.
Each recovery model addresses a different need. Trade-offs are made depending on the model
you chose. The trade-offs that occur pertain to performance, space utilization (disk or tape), and
protection against data loss. When you choose a recovery model, you are deciding among the
following business requirements:
3 Data Warehousing and Data Mining
Instructor – Ephrem A.
SATA Technology and Business Collage
Performance of large-scale operations (for example, index creation or bulk loads)
Data loss exposure (for example, the loss of committed transactions)
Transaction log space consumption
Simplicity of backup and recovery procedures
======================== STAY HOME, STAY SAFE ======================
4 Data Warehousing and Data Mining
Instructor – Ephrem A.