Database Backup   «Prev  Next»

Lesson 1

Oracle Export and Import 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:
  1. Describe the functions of the Export and Import utilities
  2. Explain how to use Export
  3. Use Export to perform a complete backup
  4. Use Export for incremental and cumulative backups
  5. Discuss the direct path method of using Export
  6. 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.
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.
For a full description of Export and Import, please refer to the more current documentation for Oracle 11g's Export and Import utilities. This guide provides comprehensive information on these utilities. You can access it here: Oracle Database Utilities
This guide offers detailed instructions and best practices for using the Export (`exp`) and Import (`imp`) utilities, as well as the newer Data Pump Export (`expdp`) and Data Pump Import (`impdp`) utilities introduced in Oracle 10g and enhanced in 11g. These Data Pump utilities provide improved performance and additional features compared to the traditional Export and Import utilities.
For a comprehensive overview of Oracle Database 11g Release 2 documentation, including the utilities guide, you can visit the Oracle Database Online Documentation library: Oracle Library
These resources will assist you in effectively performing logical backups and restores of your Oracle 11g database.
Oracle RMAN Backup Recovery

To perform logical backups and restores of an Oracle 11g database, you can use either the legacy `exp`/`imp` utilities or the newer and more efficient `Data Pump Export` (`expdp`) and `Data Pump Import` (`impdp`) utilities. Here's how to use both:

1. Using the Legacy `exp` and `imp` Utilities

Export (Logical Backup):
  1. Syntax for Exporting: 
    exp system/password@ORCL full=y file=backup.dmp log=backup.log
    • system/password@ORCL: The username, password, and database service (ORCL in this case).
    • full=y: Exports the entire database.
    • file=backup.dmp: The output dump file where the exported data will be saved.
    • log=backup.log: The log file for the export process.
  2. Steps:
    • Ensure the exp utility is available in your Oracle installation directory.
    • Run the command from your command line or terminal.
    • Check the log file (backup.log) for any errors.

Import (Restoring Data)
  1. Syntax for Importing: 
    imp system/password@ORCL full=y file=backup.dmp log=import.log
    • system/password@ORCL: The username, password, and database service.
    • full=y: Imports the entire database.
    • file=backup.dmp: The dump file from which data will be imported.
    • log=import.log: The log file for the import process.
  2. Steps:
    • Ensure the imp utility is available in your Oracle installation directory.
    • Run the command from your command line or terminal.
    • Check the log file (import.log) for any errors.

2. Using the Recommended `Data Pump Export` (`expdp`) and `Data Pump Import` (`impdp`) Utilities

  1. Setup:
    • Create a directory object in the database: 
      CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/path/to/backup';
    • Grant permissions: 
      GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO system;
  2. Syntax for Exporting: 
    expdp system/password@ORCL full=y directory=DATA_PUMP_DIR dumpfile=backup.dmp logfile=backup.log
    • full=y: Exports the entire database.
    • directory=DATA_PUMP_DIR: The directory object created in the database.
    • dumpfile=backup.dmp: The output dump file.
    • logfile=backup.log: The log file for the export process.
  3. Steps:
    • Run the command from your command line or terminal.
    • The backup.dmp file will be created in the specified directory path.

Import (Restoring Data) with `impdp`:
  1. Syntax for Importing: 
    impdp system/password@ORCL full=y directory=DATA_PUMP_DIR dumpfile=backup.dmp logfile=import.log
    • full=y: Imports the entire database.
    • directory=DATA_PUMP_DIR: The directory object created in the database.
    • dumpfile=backup.dmp: The dump file from which data will be imported.
    • logfile=import.log: The log file for the import process.
  2. Steps:
    • Run the command from your command line or terminal.
    • Check the log file (import.log) for any errors or issues.

Tips for Using Data Pump Utilities (`expdp`/`impdp`):
  • Parallelism: You can increase the performance of your export/import operations by using the parallel parameter, e.g., parallel=4.
  • Tablespace Exports: You can export specific tablespaces using tablespaces=ts_name.
  • Schemas: Use schemas=schema_name to export/import specific schemas.
  • Data Filtering: Use the include and exclude parameters to filter specific objects.

Benefits of Using Data Pump:
  • Better Performance: expdp and impdp are optimized and faster than the traditional exp and imp.
  • Advanced Filtering: Data Pump offers more options to include or exclude specific objects.
  • Network Mode: Data Pump can perform exports and imports across a network, which is useful for database migration.

SEMrush Software TargetSEMrush Software Banner