Instructor's Notes

(If applicable)

• Lesson 20: Logical Backups
• This hands-on lesson will teach you how to use the import and export utilities to perform logical backups of the database.

Instructor's Notes

(If applicable)

• Logical backups are taken with the Export utility. They are called "logical" backups, because logical database objects such as a table are backed up. Export writes output to a binary file, which can be read by the Import utility.
• The export utility is most often used to create secondary backups (what if your RMAN backup sets are lost?) and to move database objects by FTP'ing to another server and importing.

Instructor's Notes

(If applicable)

• The Export utility creates a binary dump file containing the object definitions (DDL) and data (if ROWS=Y parameter used, otherwise the file contains just the DDL). The file is in an Oracle proprietary format; the Import utility must be used to restore the file.
• The Export utility is used when the database is open. However, consistency must be considered if more than one related object is being exported. Export does not, by default, maintain consistency across tables. So, you will need to either STARTUP RESTRICT or use the Export CONSISTENT=Y parameter. However the latter solution may require very large rollback or undo segments making this a difficult solution.
• EXPORT (and IMPORT) can be run from the operating system command line, from command files (scripts) and from Oracle Enterprise Manager (OEM).

Instructor's Notes

(If applicable)

• The Export utility supports several modes. In each mode, we can request that just the DDL to recreate the object be exported or, perhaps more commonly, request that the DDL and the contents of the tables be exported.
• Export supports the following modes:
• Table – The table structure (DDL to recreate), data (optional), indexes and grants are exported. This can be limited to a partition of a table.
• User or Schema mode – All objects owned by a user are exported. Warning: Grants and indexes created by another user on the users objects are not exported. Query DBA_TAB_PRIVS to determine if 3rd party grants exists, for example:
• SELECT * FROM DBA_TAB_PRIVS WHERE grantor ^= Owner;
• To find indexes created by 3rd parties, query DBA_INDEXES, for example:
• SELECT * FROM DBA_INDEXES WHERE owner ^= table_owner;
• Tablespace – All objects within the specified tablespace are exported. This includes indexes on the objects, even if the indexes are not in the same tablespace.
• Database – All DDL and data (optional) needed to recreate the entire database is exported.

Instructor's Notes

(If applicable)

• EXPORT can be invoked from the OS command line from the server or any client that has had client administrator installation performed on it. (The OS command is "EXP".)
• In this example (not all output shown), the following options are used:
• USERID= (The string USERID= can be omitted if the first option passed is the username/password) – Specify the username to connect to the database and run the export. The user requires the EXP_FULL_DATABASE role to export objects other than their own.
• OWNER= - This denotes a user-level export. Specify the user or a comma delimited list of users whose objects should be exported.
• COMPRESS=N – This prevents export from creating DDL which will, when the object is imported, create a large enough initial extent so that the entire object (e.g. table, cluster or index) will fit into a single extent. (The default is COMPRESS=Y, which is a holdover from the old days when this was a good idea.) Compressing into a single extent is not necessary for better performance and can lead to problems (e.g. what if you do not have enough space for a single large extent?).
• Notes continue on the next page…

Instructor's Notes

(If applicable)

• FILE= - Specify the path and file name of the output dump file (I.e. the file that contains the exported data). The default is "EXPDAT.DMP" in the current working directory.
• CONSISTENT= - "Y" requests a read-consistent version of all objects. This should be used if more than one related object is being exported and the objects are available for update. Can require very large rollback or undo segments making this a difficult solution. Consider STARTUP RESTRICT to prevent access by other users instead of this option.
• LOG= - The path and name of a file where a log of the export process will be written. The message we hope for, I.e. the message that indicates all is well can be found at the end of the log file: "Export terminated successfully without warnings."

Instructor's Notes

(If applicable)

• Export supports many useful parameters:
• PARFILE= - Specify a parameter file that contains the parameters for be used in the export.
• FULL= - Invokes a full database export. The entire database is exported.
• FLASHBACK_SCN= / FLASHBACK_TIME= - Use to get a consistent export as of a previous System Change Number (SCN) or time. The time needs to be in the format specified. Note that if the UNDO is not available in the UNDO segments, the export will fail. Execute privilege on the DBMS_FLASHBACK package is required, in addition to export privileges. Oracle9i feature.
• Note that the flashback export does not support objects that have been dropped, altered or truncated.

Instructor's Notes

(If applicable)

• Export features, continued:
• TABLESPACES - Specify one or a comma-delimited list of tablespaces. Related index definitions are exported, even if they do not reside in the specified tablespace(s).
• TABLES - Specify one table, a comma-delimited list of tables or a string with wildcards % or _. The use of wildcards is an Oracle9i feature. The DDL, data, indexes and grants are exported. Partitions can be exported with the colon syntax, for example TABLES=dave.large_table:part1
• QUERY - Code a WHERE clause to filter rows in a table export. The backslash (\) is used as an escape character.
• DIRECT=Y – Invokes export in direct path mode. Direct path is faster because it bypasses the "SQL evaluation buffer". The SQL evaluation buffer processes the WHERE clause. Note that this direct path mode is not the same as a direct path SQL Loader that bypasses the buffer cache entirely. Also note:
• The QUERY feature cannot be used with DIRECT=Y.
• The default is DIRECT=N.

Instructor's Notes

(If applicable)

• The import utility is used to read the exported dump file. Import will create the objects defined in the file and, if ROWS=Y is used, insert rows of data into the objects.
• The entire contents of the dump file do not need to be imported. Use the FROMUSER parameter to specify which users objects you want to import.
• You'll need CREATE SESSION privilege and all the system privileges required to create the objects found in the dump file. If the dump file was created with a full export, you will require the IMP_FULL_DATABASE role. (This role, by default, is granted to the DBA role.)
• It is common to use FTP to send the dump file to another server. Remember the dump file is in binary format; therefore, use binary mode FTP so the dump file is not corrupted.
• If you plan on importing into a database residing on a different operating system, use the same RECORDLENGTH parameter on both the export and the import.
• See the Oracle Database Utilities manual for additional details.

Instructor's Notes

(If applicable)

• The first example is a FULL import. It will create all the objects found in the dump file and insert any data found in the dump file.
• The second example is a user-mode import. Even if the dump file contains a full dump, it will import only those objects owned by user DAVE.
• The third and final example is another user-mode import. However, this example creates all objects exported from the DAVE schema in the DAVETEST schema. User DAVETEST must exist and the user needs quota on the appropriate tablespace(s) prior to running this import.

Instructor's Notes

(If applicable)

• General tips concerning import operations:
• Sometimes you will import just for the data – the objects may already exists. In this case, use the IGNORE=Y option to tell import to continue even if there are errors.
• By default, import commits after each object. This may require too much undo segment space. Use the COMMIT=Y parameter to tell import to commit more frequently – after each array. The default array size is operating system dependent but can be overridden with the BUFFER parameter.
• If you are importing tablespaces into a second database on the same server you exported from, care must be taken to not overwrite the datafiles from the source database. Use the DESTROY=N parameter to prevent import from corrupting existing datafiles. It does this by not including the REUSE option on the CREATE TABLESPACE statement. In this situation, pre-create the tablespaces needed on the 2nd instance, of course using different datafile names.


• Notes continue on the next page…

Instructor's Notes

(If applicable)

• The SHOW=Y parameter simply displays the contents of the dump file, without importing.
• In some situations, you may want Oracle to recalculate statistics rather than importing them from the dump file. Use STATISTICS=RECALCULATE to do this. By default, existing statistics are imported.
• As of Oracle9i, Import supports resumable space management feature. Resumable Space Management (RSM) manages space allocation failures. It allows the transaction that has encountered the space allocation error to suspend. If you fix the cause of the error, the transaction will automatically resume. If you do not fix the error, you will eventually get a RSM time out error. See the RESUMABLE% parameters.

Instructor's Notes

(If applicable)

Instructor's Notes

(If applicable)

• Logical Backup Workshop
  1. Produce a list of tables owned by the administrative user created for this course.
  2. Execute a consistent user-mode export on this user.
  3. Drop this user.
  4. Take the necessary actions to recreate the user and all tables.