User Backup and Recovery Methods | Oracle9i DBA JumpStart

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.

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:

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

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.

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

Tip

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          tables=(orders, order_items) file=exp_oe.dmp Export: Release 9.2.0.1.0 -       Production on Sat Nov 9 08:45:43 2002 Copyright (c) 1982, 2002, Oracle Corporation.       All rights reserved. Connected to: Oracle9i Enterprise Edition      Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production Export done in WE8MSWIN1252 character set      and AL16UTF16 NCHAR character set 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:\temp\exp_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:

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

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.

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 9.2.0.1.0 - Production on Sat Nov 9 09:22:47 2002 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. Username: gary Password: Connected to: Oracle9i Enterprise Edition Release      9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data      Mining options JServer Release 9.2.0.1.0 - Production Export file created by EXPORT:V09.02.00      via conventional path import done in WE8MSWIN1252 character set      and AL16UTF16 NCHAR character set . 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 9.2.0.1.0 - Production on Sat Nov 9 09:31:35 2002 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. Username: gary Password: Connected to: Oracle9i Enterprise Edition      Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining      options JServer Release 9.2.0.1.0 - Production Export file created by EXPORT:V09.02.00      via conventional path import done in WE8MSWIN1252 character set      and AL16UTF16 NCHAR character set . 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 than ran since the last export. To minimize data loss, you should export the table after any major changes are made to the table.

Flashback Query

One of the features new to 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 was 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.