How to perform a full database export using original export/import utility?

Hello,

In my blog, there is a post already available on how to perform a full database export using the new and enhanced feature of Oracle Database 10g Export Utility, Data Pump – expdp/impdp. However, I’m still being requested, by my blog viewers and OTN forum users, to place a procedure on how to perform a full database export using traditional export utility – exp/imp. However, this is not the complete informative post for export/import utility; so, please refer the Oracle Documents links specified at the end of this post.

Before starting off with the steps, to perform the export at the database level, let’s have clear understanding on the following questions.

Can I perform or consider the export of my database as a backup approach?

Absolutely *NO*, because, the import can be done only up to the time the export was originally taken, i.e. the export dump cant be used to import until the point in time (up-to-date) and will not be imported the updates after the export done. This is not acceptable for Production Databases. So, never treat that, taking the export of the database is part of backup strategy of your Production database. In fact, a couple of other approaches are available to perform the backup of your database using the following ways – Cold Backup (Shutdown/Copy/Startup), Hot Backup (ALETER TABLESPACE ….BEGIN/END BACKUP), and RMAN backup methods.

Situations: When and why to perform a full database export.

* Export/import is only the possible way to construct a similar database on remote servers regardless of Operating System compatibility i.e. cross platform (Export on Linux and Import on Windows Systems).
* To migrate the database from one Operating System to another i.e. (Linux/Unix to Windows), and to upgrade the database from one version to another i.e. 8i-9i or 9i-10g, but not vice versa.
* If you are on Oracle 10g, above two points can be possible, using RMAN Convert Commands i.e. to construct or move a database from one OS to another OS i.e. Cross platform.
* When there is a requirement to clone a database on another test or development server, then take a full database mode export dump and create a database with the similar settings of target database, and import the dump in source database.

Simple Steps: how to perform a full database export using export utility.

* Use either system user or any other database user who has the EXP_FULL_DATABASE privilege.
* Set the NLS_LANG environment variable according the database character set and language details.
SQL> select * from nls_database_parameters
2 where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');

PARAMETER VALUE
------------------------------ ----------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8ISO8859P1

Windows (Dos Prompt):
C:\> set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Unix/Linux:
$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

* Start the export with following command and options.

exp system/password@mydb file=c:\exportdmp\exp_fulldb_MYDB_27Aug08.dmp
full=y log= c:\exportdmp\exp_fulldb_MYDB_27Aug08.log

Note: This is just a simple export command to perform the full database export. I would request and suggest you to refer Oracle Documentations on export/import and their options. Check the references.

Help on Export and Import:

Windows:
C:\> exp help=y
C:\> imp help=y

Linux/Unix.
$ exp help=y
$ imp help=y

References:

Oracle 10g :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm

Oracle 9i:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/part1.htm#435787

Oracle 8i:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch01.htm
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch02.htm

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/