An export is not a backup

Posts on Orafaq reveal that a significant subset of database administrators believe
that an export is a backup. This paper summarizes what an export is, what a backup is,
and why the two are different.

The problem

A search of the Backup & Recovery forum for the strings "backup" and "export" produces
many hits, such as:

http://www.orafaq.com/forum/mv/msg/158183/458167/0/#msg_458167
"we have datapump exports scheduled for our small databases for backup everyday"

http://www.orafaq.com/forum/mv/msg/155509/445308/0/#msg_445308
"you might like to use export or Data pump export. If you feel RMAN too hard"

http://www.orafaq.com/forum/mv/msg/150685/424794/0/#msg_424794
"I'm interested in RMAN in compare with other ORACLE backup tools (exp and others) too"

http://www.orafaq.com/forum/mv/msg/151398/427627/0/#msg_427627
"if your database is not transactional generate a full backup file to a simple dba.dmp"

http://www.orafaq.com/forum/mv/msg/137468/376000/0/#msg_376000
"the procedure to restore a db if there in only full export backup (and control file backup)"

Clearly, there is a popular belief that an export is a backup. This is incorrect, and any
DBA who believes that an export is a backup is showing ignorance of the Oracle architecture
and moreover is a very dangerous person to be running a database.

What is a traditional export?

I shall use "traditional export" to refer to an export created with the exp utility. This
is a user process that connects to the database instance through a server process over Oracle
Net. This is perfectly normal client-server processing: the exp user process generates SQL,
the server process executes it. The SQL generated is SELECT statements against the data
dictionary to extract the definitions of database objects, and SELECTs against tables to read
the rows they contain. This data is sent back to the exp user process, to be formatted as DDL
statements that can be used to create objects, and INSERT statements that can insert rows
into tables. These statements make up the export dump file, written by the exp utility to the
client machine.
For example, consider an export accomplished with this command:

exp scott/tiger@orcl tables=dept file=dept.dmp

Sending the generated file through a utility that will remove any non-printable characters
will give this (partial output only):

EXPORT:V11.02.00
USCOTT
RTABLES
2048
                                      Thu May 27 20:12:51 2010dept.dmp
#G##
#G##
+00:00
BYTE
UNUSED
INTERPRETED
DISABLE:ALL
METRICST
TABLE "DEPT"
CREATE TABLE "DEPT" ("DEPTNO" NUMBER(2, 0), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
INSERT INTO "DEPT" ("DEPTNO", "DNAME", "LOC") VALUES (:1, :2, :3)
ACCOUNTING
NEW YORK
RESEARCH
DALLAS
SALES
CHICAGO
OPERATIONS
BOSTON
CREATE UNIQUE INDEX "PK_DEPT" ON "DEPT" ("DEPTNO" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING

The critical lines are the CREATE TABLE statement, and the INSERT statement with its bind
variables followed by the values for the DNAME and LOC columns (note that the DEPTNO values
are not visible, because the nature of Oracle's encoding for number data types means that
they are not usually printable.) Examining this file makes it clear what a traditional import
will do: it will execute the statements it finds in the dump file, to create the objects and
insert rows into them.

What is a Data Pump export?

Functionally, a Data Pump export is similar to a traditional export. The output is a dump file
of information extracted from the data dictionary that can be used to create objects, followed
by rows to be inserted into tables. However, the implementation is different. The DDL commands
are not extracted by querying the data dictionary directly, but by using the DBMS_METADATA
package. The row data is not (as a rule) extracted with normal SELECT statements executed by
copying blocks into the database buffer cache, but rather by direct reads of the datafiles.
And most importantly, the work of a Data Pump export is not accomplished through a client-server
session but by server background processes: Oracle Net is not involved, and the dump file is
written to the server machine, not to the client. The expdp and impdp utilities are user
processes that connect over Oracle Net, but they don't do any work: they merely define and
control the Data Pump job.

For the purposes of this paper, traditional and Data Pump exports are the same: neither is a backup.

What is a backup?

A backup is a copy of the database files. It may be made with operating system utilities and
SQL*Plus (a user managed backup) or by RMAN, the Recovery Manager (a server managed backup.)
A backup may be open or closed (accomplished while the database open, or while it is closed),
and partial or whole (meaning either a subset of the files that make up the database, or the
entire file set). A further variation possible only with RMAN is full or incremental (all
blocks of a file, or only those blocks changed since a previous backup).

A user managed closed whole backup is three steps:
Copy the controlfile; copy the online logs; copy the datafiles.
A user managed open whole backup is also three steps:
ALTER DATABASE BACKUP CONTROLFILE; copy the datafiles, while their tablespace is in backup
mode; archive the online logs.
The need for an ALTER DATABASE command to backup the controlfile is to ensure read consistency.
As the controlfile is continually being updated, we must rely on Oracle to take a read consistent
snapshot of it.
The tablespaces must be in backup mode while being copied in order that read consistent copies
of changed blocks are written to the redo log, from where they can be extracted to replace any
blocks in the backup that are inconsistent due to their being over-written by the database
writer while being copied.
Archiving of the online redo logs is necessary to ensure that all the change vectors applied
to the database during the course of the backup are available, in order that all the files
can be brought to a consistent state.

A server managed closed whole backup is identical to user managed, except that RMAN will never
backup online logs: they will never be needed by RMAN because RMAN ensures that the datafiles
are consistent, or it will not back them up at all.
A server managed open backup is identical to user managed, except that RMAN does not place
tablespaces into backup mode. Being aware of the Oracle block formatting, RMAN can detect if
a block was updated while being copied, and will re-try until it reads a consistent version.

So why is an export not a backup?

The preceding descriptions of export and backup make this clear. While both export and backup
will write out the contents of the database, they do this in different ways. An export is table
oriented: individual objects and their rows are written out, one by one. The granularity of the
export is the table. A backup is file oriented: the granularity of the backup is the file. Why
does one need a backup? To protect the database against damage to files. A means is needed to
replace damaged files: extracting the file from a backup will do exactly that. An export can
never do this. Any one exported table may have had extents in many files, and any one file may
have extents of many tables; there is no way an import can replace one file, because of this
many-to-many relationship between tables and files.
So, you may say, what about a full export? Is that not a backup, since one can create a new
database and import every table? No, that is not good enough. First, there is the issue of
transactional consistency. Oracle's default isolation level ensures that every SELECT statement
will give consistent results, but it does not ensure that a series of SELECTs will give consistent
results. You can use CONSISTENT=Y in an attempt to get a consistent export, but you may need an
undo tablespace the size of Jupiter for this to succeed, and in any case CONSISTENT=Y is not
supported for SYS. But much worse is recovery: it cannot be done. There is no way to apply redo
to an imported database, because redo change vectors are block and file oriented: after a full
import, the block and file references will be meaningless.

So to conclude, export and import cannot restore and recover following damage to datafiles.
Backups are the only way to do this.