13.2. User Backup and Recovery Methods

There are a number of methods that database users and developers can use to back up and restore the data in their tables. While a good DBA has a comprehensive database backup and restore plan in place, there are a couple of reasons why database users might make their own backups:

• The DBA is typically very busy and may not be able to respond to a user's request to restore data in a timely manner.

• The type of backup a DBA typically performs is at an enterprise level— entire tablespaces rather than individual user objects—making it difficult to accommodate requests to restore individual objects.

In this section, we'll talk about two ways that database users can back up and restore the objects they own or objects that are accessible to them in the database: by using the Export and Import utilities and by running flashback queries.

13.2.1. Export and Import for Users

The Export and Import utilities save and retrieve objects stored in an operating system file external to the database. They work with database table objects, along with their associated indexes, constraints, and permissions. These commands are similar in their syntax and are executed outside the database at an operating system prompt.

The Export (EXP) Utility

The Export utility (EXP) connects to the database and performs a SELECT statement on the table or tables specified in the EXP command. It places the results of the SELECT statement, along with the DDL statements required to create the tables and their associated indexes, into a single binary dump file. Subsequently, this dump file can be used to restore the tables in case of data loss. In addition, the dump file can be used to copy the table to another database. The format of the EXP command is as follows:

Export utility (EXP)

An Oracle utility that copies the contents of one or more tables to a binary dump file, along with the DDL needed to create the table and its associated indexes, permissions, and constraints.

EXP username/password KEYWORD=(value1, value2, ...)

If the EXP command is executed without specifying any parameters, Export prompts the user for the parameters in an interactive mode. The username and password belong to the user who owns the objects to be exported. The TABLES keyword specifies the tables that are to be exported to the dump file, which defaults to the filename EXPDAT.DMP. Running EXP -HELP displays all of the Export options. The most common keywords are listed below.

Keyword	Description
FILE	Destination for the dump file; defaults to EXPDAT.DMP
TABLES	List of table names
ROWS	Export rows of the table; defaults to Y
INDEXES	Export indexes; defaults to Y
CONSTRAINTS	Export table constraints; defaults to Y
GRANTS	Export privileges granted on tables; defaults to Y
COMPRESS	Create a single extent for each table in the CREATE TABLE statement generated by EXP; defaults to Y

While the default for the COMPRESS parameter of Export is Y, it should almost always be set to N to avoid wasting disk space when new extents are allocated for the imported version of the table.

At Scott's widget company, one of the developers, Gary, is working on a project to provide customers with customized widgets, made to order. He is working on the order entry part of the system, and he has a copy of the Order Entry department's ORDER and ORDER_ITEM tables in his own schema:

select table_name from all_tables
where owner='GARY';

TABLE_NAME
--------------------
ORDERS
ORDER_ITEMS

2 rows selected.

Gary decides to use Export to save a copy of these tables to a binary dump file on a local PC's hard drive, just in case one of the tables is inadvertently dropped:

E:TEMP>exp gary/castiron@ord
       tables=(orders, order_items) file=exp_oe.dmp

Export: Release 10.1.0.2.0 -
          Production on Mon Jun 21 22:57:53 2004

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition
      Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set
       and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set
       (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table       ORDERS         105 rows exported
. . exporting table       ORDER_ITEMS    665 rows exported

Export terminated successfully without warnings.

E:TEMP>

The operating system file E: empexp_oe.dmp contains the definitions of the two tables and their contents, along with any indexes, constraints, and permissions defined on the tables.

The Import (IMP) Utility

The Import utility (IMP) reads a binary dump file produced by the Export utility and restores the tables and any associated indexes, constraints, and permissions saved in the dump file. The format of the IMP command is as follows:

Import utility (IMP)

An Oracle utility that takes as input a binary dump file created by the Export utility and restores one or more database tables, along with any associated indexes, permissions, and constraints.

IMP username/password KEYWORD=(value1, value2, ...)

If the IMP command is executed without specifying any parameters, Import can prompt the user for the parameters in an interactive mode. The username and password belong to the user who owns the objects to be imported. The TABLES keyword lists the tables that are to be imported from the dump file, which defaults to a name of EXPDAT.DMP. Running IMP -HELP lists all of the Import options. The most common keywords are listed below.

Keyword	Description
FILE	Dump file to restore from; defaults to EXPDAT.DMP
TABLES	List of table names to restore
ROWS	Import rows of the table; defaults to Y
INDEXES	Import indexes; defaults to Y
CONSTRAINTS	Import table constraints; defaults to Y
GRANTS	Import privileges granted on tables; defaults to Y
SHOW	Show just the file contents and do not perform the restore; defaults to N

Later in the week, Gary, the database developer, inadvertently drops the ORDER_ ITEMS table that he was using to test his custom widgets application. He remembers using Export earlier in the week to create a backup to the file exp_ oe.dmp, but is not sure of its contents. He uses the SHOW option of the IMP command to query the contents of the dump file:

E:TEMP>imp file=exp_oe.dmp show=y

Import: Release 10.1.0.2.0 - Production on Mon Jun 21
23:13:21 2004

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Username: gary@ord
Password:

Connected to: Oracle Database 10g Enterprise Edition Release
   10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00
   via conventional path
import done in WE8MSWIN1252 character set
   and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set
   (possible charset conversion)
. importing GARY's objects into GARY

 "CREATE TABLE "ORDERS"
     ("ORDER_ID" NUMBER(12, 0) NOT NULL ENABLE,"ORDER_DAT"
 "E" TIMESTAMP (6) WITH LOCAL TIME ZONE
     CONSTRAINT "ORDER_DATE_NN" NOT NULL E"
...
"CREATE TABLE "ORDER_ITEMS"
     ("ORDER_ID" NUMBER(12, 0) NOT NULL ENABLE, "LINE"
...
Import terminated successfully without warnings.

E:TEMP>

Since the SHOW=Y option was specified, the tables were not actually restored to the database, even though the output from IMP seems to indicate that the restore took place. Since this file has the table that Gary wants, he performs the import and specifies the file he dropped:

E:TEMP>imp file=exp_oe.dmp tables=order_items

Import: Release 10.1.0.2.0 - Production on Mon Jun 21
23:24:47 2004

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Username: gary@ord
Password:

Connected to: Oracle Database 10g Enterprise Edition
     Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00
     via conventional path
import done in WE8MSWIN1252 character set
     and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set
     (possible charset conversion)
. importing GARY's objects into GARY
. . importing table    "ORDER_ITEMS"     665 rows imported
Import terminated successfully without warnings.

E:TEMP>

Gary's ORDER_ITEMS table is now restored. Any changes made to the table since the export was performed are lost. Those changes will need to be manually restored by rerunning the INSERT, DELETE, and UPDATE statements that ran since the last export. To minimize data loss, you should export the table after any major changes are made to the table.

13.2.2. Flashback Query

One of the features introduced in Oracle9i is called flashback query. It allows a user to "go back in time" and view the contents of a table as it existed at some point in the recent past. A flashback query looks a lot like a standard SQL SELECT statement, with the addition of the AS OF TIMESTAMP clause.

flashback query

A feature of the Oracle database that allows a user to view the contents of a table as of a user-specified point in time in the past. How far in the past a flashback query can retrieve rows depends on the size of the undo tablespace and on the setting of the UNDO_RETENTION system parameter.

Before users can take advantage of the flashback query feature, the DBA must perform two tasks:

• The DBA must make sure that there is an undo tablespace in the database that is large enough to retain changes made by all users for a specified period of time. This is the same tablespace that is used to support COMMIT and ROLLBACK functionality (discussed in Chapter 7, "Logical Consistency").

• The DBA must specify how long the undo information will be retained for use by flashback queries by using the initialization parameter UNDO_ RETENTION. This parameter is specified in seconds; therefore, if the DBA specifies UNDO_RETENTION=172800, the undo information for flashback queries will be available for two days.

At Scott's widget company, an error in the Accounting department added $2,000 to two orders placed yesterday:

update orders
set order_total = order_total+2000
where order_id in (2367,2361);

2 rows updated.

select order_id, customer_id, order_total
from orders where order_id in (2367,2361);

  ORDER_ID CUSTOMER_ID ORDER_TOTAL
---------- ----------- -----------
      2361         108    122131.3
      2367         148    146054.8

2 rows selected.

Today, the customer with customer ID 108 called to complain that his bill from his last order (order number 2361) is $2,000 higher than expected. Sharon, one of the order-entry clerks, retrieves the row from the ORDERS table with the information for order number 2361:

select order_id, customer_id, order_total
from orders where order_id = 2361;

ORDER_ID CUSTOMER_ID ORDER_TOTAL
---------- ----------- -----------
      2361         108    122131.3

1 row selected.

Before calling back the customer, Sharon finds out from the Accounting department that a day ago, two of the orders were incorrectly modified with an additional surcharge. To confirm whether this particular order was affected by the accounting error, she uses a flashback query to see if this order had a different order total two days ago:

select order_id, customer_id, order_total from orders
as of timestamp (sysdate - 2)
where order_id = 2361;

  ORDER_ID CUSTOMER_ID ORDER_TOTAL
---------- ----------- -----------
      2361         108    120131.3

1 row selected.

This flashback query confirms that the order total for this order was $2,000 less two days ago. The AS OF TIMESTAMP clause specifies how far back in the past you want to view the contents of this table. In this case, (sysdate - 2) evaluates to today's date minus two days—in other words, two days ago. Sharon concludes that at some point in the past two days, this was one of the orders that were incorrectly modified. To find all of the orders that have the incorrect surcharge, she uses another flashback query as a nested query to compare the order totals:

select o.order_id, o.customer_id,
  o.order_total "CURR_TOTAL", oo.order_total "ORIG_TOTAL"
from orders o,
      (select order_id, order_total from orders
       as of timestamp (sysdate - 2)) oo
where o.order_id = oo.order_id and
      o.order_total != oo.order_total;

  ORDER_ID CUSTOMER_ID ORDER_TOTAL ORIG_TOTAL
---------- ----------- ----------- ----------
      2361         108    122131.3   120131.3
      2367         148    146054.8   144054.8

2 rows selected.

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 selecting records where the order totals don't match. She now knows which records must be updated with the correct order total amount.