Oracle Concepts - Imports and Exports Oracle Tips by Burleson Consulting

Imports/Exports

Imports and exports extract or insert an Oracle-readable copy of the actual data and structures in the database. The exports can be used to recover single data structures to the date and time the export was taken. Exports come in three types: full, cumulative, and incremental. Full, as its name implies, provides a full logical copy of the database and its structures. A cumulative provides a complete copy of altered structures since the last full or the last cumulative export. Incremental exports provide a complete copy of altered structures since the last incremental, cumulative, or full export.

Limitations on export/import:

* A database must be running to perform either an export or import.

* Export files shouldn?t be edited and can only be used by import.

* (Import only) imports full tables; it can?t be used to do a conditional load.

* Exported data is only a logical copy of the data. An export can only allow recovery to the date and time the export was taken.

* Imports and exports are accomplished using the Oracle IMPORT and EXPORT utilities.

Exports

For exports, the EXPORT utility is used. The format for using this command follows:

Format:  EXP KEYWORD=value ?or? KEYWORD=(list of values)

Example: EXP AULT/AUTHOR GRANTS=N TABLES=(CHAPTERS, EDITORS,ADVANCES)

Exports should be automated and scheduled to run automatically. An export methodology should be worked out such that the DBA is reasonably certain a deleted file can be recovered. The parameters for export can either be placed on the command line, or, in a parameter file which can then be accessed using the PARFILE command line option.

IMPORT

The format of the IMPORT command follows.

Format:  IMP KEYWORD=value ?or? KEYWORD=(list of values)

Example: IMP AULT/AUTHOR IGNORE=Y TABLES=(EXPENSES, ADVANCES) FULL=N

Under Oracle7, the user must be granted the EXP_FULL_DATABASE role in order to do full exports. In order to perform a full import, the user must have the IMP_FULL_DATABASE role. The users with the DBA role are granted these implicitly.

An example of when the DBA would want to grant these roles to a user would be a user whose password is specified in the command script used for doing the automatic exports. If the only role granted to the user is CREATE_SESSION and EXP_FULL_DATABASE even if the user?s password is compromised, they won?t be able to do much damage.

Archive Logs

The redo logs store all transactions that alter the database, all committed updates, adds, or deletes of tables, structures, or data. If archiving is disabled, only data in the current off-line and on-line redo logs can be recovered. If the system recycles through all redo logs, the old ones are reused, destroying their contents. If  archive logging is enabled, the redo logs are written out to storage before reuse. Archive logging allows recovery to a specific point in time since the last full cold backup or complete off-line backup. Under Oracle8i archive logs can be duplexed. The initialization parameters that control archive logging are:

Specifies the format for archive log names, %s and %t add the sequence and redo thread numbers to the format.

Under Oracle7 and Oracle8 redo logs are specified in groups; each group forms a shadow set and is archived together. Under Oracle7 archive logs can also be assigned to threads for use in parallel or shared instances. Individual logs are called members. Threads hold groups that hold members. Each member of a redo log group is the same size and should be on separate physical platters. Oracle automatically synchronizes members of a group into a shadow set.

Redo logs cannot be used to recover a database brought back from a full export.

To switch a database that is not currently using archive logging to use archive logging the steps are:

1. Shutdown database using immediate or normal options

2. Edit the initialization parameter file to include appropriate archive log parameters, at a minimum:

       ARCHIVE_LOG_START = TRUE
ARCHIVE_LOG_DEST = destination (operating system specific path to archive log destination)
       ARCHIVE_LOG_FORMAT = arch_%t_%s.arc
 

Usually the defaults for LOG_ARCHIVE_BUFFERS and LOG_ARCHIVE_BUFFER_SIZE are sufficient.

3. Using the appropriate interface (svrmgrl, svrmg23, sqlplus) startup the database in mounted mode:

       Svrmgrl> connect internal/password as sysdba
       Svrmgrl> startup mount pfile=<initialization file location>

4. Use the ALTER DATABASE command to reset the ARCHIVELOG mode:

       Svrmgrl> ALTER DATABASE ARCHIVELOG;

5. Use the ALTER DATABASE command to open the database.

       Svrmgrl> ALTER DATABASE OPEN;

6. Either shutdown and perform a cold backup or perform a hot backup. Since this is the first backup, I would suggest a cold backup be used. This is the baseline backup of your database.

7. Restart the database as you would normally.

Proper use of these backup/recovery tools allows the DBA to recover from any possible failure.

Get the Complete Oracle Utility Information  The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.