Databases Reference
In-Depth Information
In this query, Sharon is comparing the entire contents of the current ORDERS
table to the entire contents of the ORDERS table as it was two days ago and select-
ing records where the order totals don't match. She now knows which records
must be updated with the correct order total amount.
DBA Backup and Recovery Methods
The DBA has a number of additional tools for performing backup and recovery,
with capabilities for working at a much larger scale than the methods previously
discussed. Instead of a couple of tables being dropped by a user, the DBA may
need to handle a disk drive failure, resulting in the loss of an entire tablespace.
In addition to using Export and Import to back up database objects, the DBA
can perform cold backups or hot backups for an entire tablespace or an entire
database. Other tools available to the DBA include Log Miner and RMAN.
Export and Import for DBAs
Earlier in this chapter, you learned about the Export (EXP) and Import (IMP) util-
ities that a user can use to save and restore database objects. The DBA can use
additional features of these utilities for backing up all user objects in the database
or to copy a tablespace to another database. The tablespace copy feature, intro-
duced in Oracle9
i
, is known as
transportable tablespaces
. It is a very convenient
way to copy all objects in a tablespace to another database, without needing to
specify individual objects in the tablespace.
At Scott's widget company, there are two primary databases:
◆
transportable tablespace
A feature of Oracle's Import and Export
utilities that allows a tablespace to be
copied to another database. All objects
within the tablespace to be copied must
be self-contained; in other words, a table
in a tablespace to be copied must have
its associated indexes in the same
tablespace.
The OLTP database (ORD), which contains the online widget order system
and the HR tables. It has the EMPLOYEES, DEPARTMENTS, and other tables.
The data warehouse database (WH), which contains summaries of orders
processed on the online system. Analysts use this summarized information
to do “what-if” analyses to predict sales for the upcoming fiscal year.
On a weekly basis, Janice, the DBA, needs to copy the transactions from the
online database to the data warehouse database. She decides that using trans-
portable tablespaces is the most convenient and efficient way to move this data,
as there are hundreds of tables in several different schemas that need to be
merged into the data warehouse.
◆
