Previous | Table of Contents | Next

Page 594

the export file. Here's an example of a full logical backup being written to the default output filename:

exp USERID=SYSTEM/MANAGER FULL=Y

The next example shows a full logical backup being saved directly to tape on a UNIX system:

exp USERID=SYSTEM/MANAGER FULL=Y FILE=/dev/rmt0

Having a recent full logical backup on hand gives a DBA a safety net of sorts if an object (such as user, table, or index) is inadvertently dropped, corrupted, or changed. Although it will not bring your beloved object back to its state when it was dropped, you do at least have the object restored.

Logical Backups of Specific User Schemas

EXP easily supports the backup of specific user schemas. Exporting a specific group of users will, by default, include the user definition, all tables, indexes, constraints, grants, and associated data. This is particularly useful if a user's USERID must change, or if a user is to be dropped and you simply want to keep a copy on hand. You can specify either a single user or a group of users. Here's an example of the command line needed to export the TGASPER schema to the standard output filename:

exp  USERID=SYSTEM/MANAGER OWNER=TGASPER

Now suppose you're cleaning up old user accounts and will be dropping the JSMITH, JKLEIN, MJONES, and BGEORGE accounts. Being the prudent DBA that you are, you never really delete anything. You're prepared if someone comes back to you next month and says, "I really, really need the XYZ table from the JKLEIN account. I know we asked you to delete that schema, but we're in a real pickle if we can't get the data from that table." This command shows exporting multiple user schemas to a UNIX tape device:

exp USERID=SYSTEM/MANAGER OWNER=JSMITH,JKLEIN,MJONES,BGEORGE FILE=/dev/rmt0

Now you're prepared.

Logical Backups of Specific Tables

Exporting a set of tables is handled very much the same way that user exports are handled. You have the option of exporting a single table or a set of tables by listing the names of the table(s) with the TABLES keyword. The following example will export the ABC and XYZ tables from the BGEORGE schema to the default output file:

exp USERID=SYSTEM/NANAGER OWNER=BGEORGE TABLES=ABC,XYZ

Backing up specific tables is useful if you need to move specific tables between schemas either in the same or different Oracle instances, for instance. You may also want to keep a logical backup of a table before dropping it from the system.

Page 595

TIP
Whether you call it experience or paranoia, I'm always reluctant to really drop anything without first making a logical backup of it. Your environment may or may not call for such measures, but be aware that it is very time-consuming and difficult to restore a dropped object.

NOTE
EXP lacks some key capabilities that many DBAs familiar with other RDBMSs may find surprising. EXP does not allow you to export information based on a SQL WHERE clause. Rather, it only allows you to export full objects, full schemas, or the whole database. Oracle's export file is, practically speaking, a proprietary format. You do not have access to the raw text data or the SQL code used to regenerate a database, tables, grants, and so on. There are many third-party tools on the market that do provide these missing capabilities.n

Using Cold Physical Backups

Cold physical backups are the DBA's tool of choice for protecting the database system as a whole from all sorts of data-destroying demons. Regular cold backups, along with a good set of archived and current redo logs, allow you to recover to any point in time—even to the very last commit just before that disk drive bearing failed.

Prior to the advent of the Windows desktop environment, cold physical backups were performed almost entirely with OS-level utilities such as TAR (UNIX) or BACKUP (VMS). The only database interaction was shutting down the database and restarting it afterwards. In the UNIX and VMS worlds, this still holds true. Because Windows machines are not command-line_oriented, Oracle created a product called Backup Manager, which allows you to use a standard Windows GUI program to perform physical backup and recovery. Because the command-line and desktop worlds perform backups differently, they are covered in two separate subsections.

Regardless of your environment or method, a cold physical backup should include the following elements of your database:

Command-Line_Driven Cold Physical Backups

UNIX and VMX DBAs may feel somewhat abandoned by Oracle because of its lack of GUI tools for administering functions such as backup and recovery. But if you do a little work perfecting some scripts, backup and recovery in the command-line world can be more flexible and trouble-free than is possible through Backup Manager.

Page 596

A cold physical backup usually involves making a file-level copy of all of the necessary database elements (configuration file(s), data files, control files, redo logs, and archived logs). Before starting your backup process, you need to make sure that your database can stay offline long enough to back it up completely. If you don't have enough time, you should consider hot
backups.

The basic steps required to perform a cold backup are as follows:

  1. Build a list of the OS-level files you'll need to back up.

  2. Shut down the database with either NORMAL or IMMEDIATE options.

  3. Perform the OS-level backup of the file list from step 1.
  4. Start up the database normally.

CAUTION
Do not back up the system after an abrupt shutdown. Only back up the database after it has been shut down using either IMMEDIATE or NORMAL mode.

In addition, once you have backed up your archived log files, you may remove them from the system. I generally like to keep the last seven days' worth of archived logs on the system. Assuming you back up your database every night, this will ensure that you can still recover the database even if your backup has failed for the last seven days. Consider the Thanksgiving weekend. Most companies are closed Thursday through Sunday. If on Wednesday your backup script fails for some reason (or the backup drive simply decides to take the holiday off also), you may not know that anything has failed until Monday morning. Even if you have a 24/7 operations staff, they may not monitor the backups as closely as you do. By keeping the last seven days' worth of archived log files on the system, you can still recover the database if it failed sometime Monday afternoon.

Building Your Filename ListNo matter how hard we try, DBAs never seem to be able to keep a solid handle on the full set of files that comprise our databases. This is unfortunate because a cold backup can be almost worthless if it is not a complete set of database files. Before you shut down the database, it may be worthwhile to just double-check everything to make sure you have a full list of the files you'll need to back up.

When the database is started, you are either explicitly or implicitly reading an init<instance>.ora file. You must back up this file. You should also take a look at this file to see if there is an ifile line, which basically tells Oracle to include another file while reading init.ora. If there is a filename listed for the ifile keyword, you must back up that file also.

To generate a list of control files being used by the database, you can either look at the applicable init.ora file or run this SQL statement:

SELECT VALUE FROM v$PARAMETER WHERE NAME='control_files';

Oracle will return a comma-delimited listing of control files.

Previous | Table of Contents | Next