Export and Import : Oracle Database Admin (DBA) Tutorial

We can take different types of Oracle database backups.

1. Logical Backup
2. Physical Backup

1.     Logical Backup

Logical backup can be done with export utility

Export (exp)  and Datapump (expdp):-

Export/expdp is an oracle utility used to store Oracle database in export format (.dmp) files for later retrieval.

Import (imp) and Datapump (impdp):-

Import is used to import the exported files into database.

2.     Physical Backup

Physical backup is backup of Oracle database blocks, it is again divided into COLD and HOT backups.

Export (exp)

Export backup can be taken at different levels

1. Table level
2. Schema level
3. Database level
4. Tablespace level
5. Transportable tablespace across platforms

Import (imp)

Export backup can be taken at different levels

1. Table level
2. Schema level
3. Database level
4. Tablespace level
5. Transportable tablespace across platforms

Examples

1. Full database backup

exp system/<password> file=fulldb.dmp log=fulldb.log full=y

2. Schema level backup (SCOTT)

exp system/<password> file=scottbkp.dmp log=scottbkp.log owner=scott

3. Table level backup

To take the backup of tables dept and salgrade under SCOTT schema

exp scott/<password> file=tablesbkp.dmp log=tablesbkp.log tables=dept,salgrade

4. Row level backup

If you want to take the backup of rows from EMP table whose deptno=10

Exp scott/<password> file==rowsbkp.dmp log=rowsbkp.log tables=emp query=\’ where deptno=10\’

5. Importing the backup of scott user to testuser

On the target database create testuser with the same privileges as SCOTT user

SQL> create user testuser identified by testpwd;

SQL> grant connect, resource to testuser;

Imp system/<password> file=fulldb.dmp log=impscott.log fromuser=scott touser=testuser commit=y ignore=y

6. Seeing the contents of a dumpfile

Imp scott/<password> file=scottbkp.dmp show=y

1.     Improving Export and Import Performance

EXPORT

•       Set the BUFFER parameter to a high value (e.g. 2M)

•       Set the RECORDLENGTH parameter to a high value (e.g. 64K)

•       Stop unnecessary applications to free-up resources for your job.

•       If you run multiple export sessions, ensure they write to different physical disks.

•       DO NOT export to an NFS mounted filesystem. It will take forever.

IMPORT

•       Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements. 

•       Place the file to be imported on a separate physical disk from the oracle data files

•       Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file

•       Set the LOG_BUFFER to a big value and restart oracle.

•       Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)

•       Use COMMIT=N in the import parameter file if you can afford it

•    Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements

•       Remember to run the indexfile previously created