Lesson 1

Oracle Export and Import Utilities

How do I use the Export and Import utilities to perform logical backups of an Oracle 11g database?

The Export and Import utilities (exp and imp) provided by Oracle can be used to perform logical backups of Oracle databases, and can be particularly useful for data migration tasks. The "exp" command is used to export data, while the "imp" command is used to import data.

Exporting a Database using the Export Utility

Log in to the server that hosts your Oracle database.
Set your Oracle environment by using the oraenv or coraenv script.
```
. oraenv
```
To export the entire database, use the following syntax at the command line:
```
exp userid=username/password@database full=Y file=export_file_name.dmp log=export_log_file.log
```
Replace "username", "password", and "database" with your actual Oracle username, password, and database name. The "file" parameter specifies the name of the export dump file, while the "log" parameter specifies the name of the log file that will be created during the export.
Here is an example of this command:
```
exp userid=scott/tiger@orcl full=Y file=full.dmp log=full.log
```
This command will export the entire database associated with the user "scott" and the password "tiger".
After the export operation has completed, check the log file to ensure that the export has completed without errors.

Importing a Database using the Import Utility

Log in to the server that will host your Oracle database.
Set your Oracle environment by using the oraenv or coraenv script.
```
. oraenv
```
To import the entire database, use the following syntax at the command line:
```
imp userid=username/password@database full=Y file=import_file_name.dmp log=import_log_file.log
```
Replace "username", "password", and "database" with your actual Oracle username, password, and database name. The "file" parameter specifies the name of the import dump file, while the "log" parameter specifies the name of the log file that will be created during the import.
Here is an example of this command:
```
imp userid=scott/tiger@orcl full=Y file=full.dmp log=full.log
```
This command will import the entire database associated with the user "scott" and the password "tiger".
After the import operation has completed, check the log file to ensure that the import has completed without errors.

Notes:

If you only want to export or import specific schemas, tables, or other objects, you can use the "owner", "tables", or "rows" parameters with the exp and imp commands.
Be aware that the exp and imp utilities do not export or import all types of database objects. For example, they do not handle directory objects and certain types of large objects (LOBs). If you need to export and import such objects, consider using the Oracle Data Pump utilities (expdp and impdp) instead.
The exp and imp utilities are command-line tools and they do not have a graphical user interface. However, they can be scripted and automated using shell scripts or other scripting tools.
Always backup your data before performing any import or export operation. It is recommended to perform a full database backup before and after using these utilities.

This module discusses how to use the Export and Import utilities to perform logical backups of your database. As you define your backup strategy, it is important to consider both 1) physical and 2) logical backups. The Export and Import utilities are important tools used frequently every day by experienced database administrators (DBAs).
By the end of this module, you will be able to:

Describe the functions of the Export and Import utilities
Explain how to use Export
Use Export to perform a complete backup
Use Export for incremental and cumulative backups
Discuss the direct path method of using Export
Use Import to restore database information

Export Utility

As an Oracle DBA, you will find yourself using the Export utility in many situations; one example is rolling out production databases. For instance, you can use the Export utility to create a base-level database schema. Then, when you go to a customer site, you will be able to create a blank database and import your database definitions and base-level data. This will help ensure that all current database objects are created and will help minimize installation errors.

For a full description of Export and Import, please refer to

Oracle Database Utilities 11g Release 1 (11.1) B28319-02 September 2007

or the online documentation that comes with the release CDs.
You will find many uses for Export/Import in your day-to-day activities. In the next lesson, you will get started on learning about the Export and Import utilities.