User Backup and Recovery Methods - Oracle DBA

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:

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.

Export Utility (EXP)

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:

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:

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.

Note As of Oracle 10g, the new utilities EXPDB and IMPDB, the command-line utility interface to Oracle Data Pump, replace most of the functionality of EXP and IMP in Oracle9i and earlier and provide features such as import and export directly between instances. The original Export and Import utilities, however, should still be used in an Oracle 10g database when importing backups from a previous release of Oracle, or you will need to export data to import into a previous release of Oracle.

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:

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.

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.

Import Utility (IMP)

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:

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:

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.

Note As an alternative to importing a dropped table from an export dump file, Oracle 10g supports a recycle bin concept, keeping the contents of the dropped table hidden in a special area on disk and accessible as long as the disk space occupied by the dropped table is not needed for new objects in the tablespace.

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.
  • 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:

flashback query

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:

flashback query

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:

flashback query

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:

flashback query

2 rows selected.3

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.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle DBA Topics