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! |
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