Previous | Table of Contents | Next

Page 588

continued
You can't lose any transactions in production OLTP systems. Running in ARCHIVELOG mode is highly desirable (when practical) because it allows you to fully recover your database to the last commit, even if your last physical backup was two days ago.

Even with ARCHIVELOG mode enabled, you must still make regular physical backups of the system. You must have all the archived redo logs since the last physical backup to fully recover a database. A regular physical backup will reduce the amount of space needed to store archived redo logs, as well as reduce the risk from losing transactions because of a lost or damaged archived redo log.

Physical backups should be used for recovery on the same machine, Oracle version, and instance from which they originated. Therefore, physical backups should be considered non-portable. They are usually only useful for safeguarding data on the same machine and instance against data loss.

One exception to this rule is when you want to completely transfer a database from one system to another. As long as both machines are of the same architecture, OS version, and Oracle version, you can just copy the physical components of a database from system A to system B. If any of these conditions are not met, or if you are just in doubt, use a logical backup to move your data.

A logical data backup is usually a collection of SQL statements to re-create the database objects (the database itself, tables, indexes, grants, roles, and so on), as well as the individual records in an ASCII format.

Oracle's logical backup system should be used when you need to move specific data between instances, or when you need to copy all of an instance's data between differing system archi-tectures, OS versions, or Oracle versions. Logical backups are usually run by the DBA on an as-needed basis, although there is certainly a need for somewhat regular logical backups in addition to physical backups.

In developer environments, application developers should work with DBAs to plan a logical backup strategy. Typically, two copies of the database exist: one that is production and another that is used by the developers to debug their applications. The development database should be synchronized with the production database periodically.

Let's talk about some practical real-world problems. You must have your database running in ARCHIVELOG mode and the archiver must be running for you to recover your system to the last commit. The following sections list common backup needs and the recommended backup methods. The remainder of this chapter will cover the actual mechanics of the backup and recovery methods provided by Oracle.

To help illustrate the correct usage of each backup system, the following provide several corresponding examples:

Page 589

For the cold physical backup,

For the hot physical backup,

For the full logical backup,

For logical backup of a specific table,

For logical backup of a specific user,

TIP
These specific cases illustrate which backup methods are appropriate for the different situations you will encounter throughout your database's life. Don't rely on a single backup method. Although most DBAs understand the need for physical backups, many do not run logical backups regularly. This makes them vulnerable to an inadvertent DROP command. Remember that DROPs are immediate; there is no rollback. With help from Oracle Support, it is possible to use your physical backups to recover a dropped table. This is a very time-consuming and expensive process that probably can be avoided by having a logical backup on hand. Remember that the system does not need to be shut down for logical backups.

								               	continues

Page 590

continued
Making a logical backup is very important to do before you run a SQL script that you have not thoroughly examined. DROPs or DELETEs might lurk unexpectedly, just waiting to zap your database!

If you run hot backups as your physical backup, you should also run cold backups when you have the opportunity. Because of the complexity of hot backups, it gives many DBAs (myself included) a warm fuzzy to know that we have a fairly recent cold backup in our back pockets. You can never have enough backups.

Using Logical Backups

Oracle provides us with logical backups through the EXP facility (IMP is used for logical recovery). You can use EXP either entirely in batch mode, entirely interactively, or interactively with some keywords specified. Before we discuss the operational aspect of EXP, let's look at the available keywords. Running exp HELP=Y will show all of them, as seen in Listing 24.1.

Listing 24.1 EXP Keywords: Output of exp HELP=Y.
oreo:/opt/oracle/bin$ exp help=y

Keyword     Description (Default)        Keyword      Description (Default)
--------------------------------------------------------------------------
USERID      username/password            FULL         export entire file (N)
BUFFER      size of data buffer          OWNER        list of owner usernames
FILE        output file (EXPDAT.DMP)     TABLES       list of table names
COMPRESS    import into one extent (Y)   RECORDLENGTH length of IO record
GRANTS      export grants (Y)            INCTYPE      incremental export type
INDEXES     export indexes (Y)           RECORD       track incr. export (Y)
ROWS        export data rows (Y)         PARFILE      parameter filename
CONSTRAINTS export constraints (Y)       CONSISTENT   cross-table consistency
LOG         log file of screen output    STATISTICS   analyze objects (ESTIMATE)

The keywords shown in Listing 24.1 are explained below.

USERID is the user ID/password that EXP will use to log in to the database to perform the export. At the minimum, you must have the CONNECT SESSION privilege enabled to use EXP. If you intend to export another user's objects, you must have the EXP_FULL_DATABASE privilege. A DBA user, of course, can do anything.

TIP
You can use EXP with remote databases using the usual USERID/PASSWORD@REMOTE_NAME convention.

FULL specifies whether you are exporting the entire database or not. It defaults to N.

BUFFER is used to determine how many rows will be loaded into memory before committing to the export file. The default value is OS dependent. The larger your buffer, generally the faster

Previous | Table of Contents | Next