Export/Import Implementation (Oracle 9i/10G)

1        Logical Backup (Export/Import)

1.1     Create database directories

Execute the following commands to create a database directory. This directory must point to a valid directory on the same server as the database:  Execute following commands as sysdba.

SQL> CREATE DIRECTORY exp_backup AS '/u01/demo_exp_backup';
Directory created.
SQL> GRANT read, write ON DIRECTORY exp_backup TO scott;
Grant succeeded.

PS: Oracle introduced a default directory from 10g R2, called DATA_PUMP_DIR, that can be used:

SQL> SELECT directory_path FROM dba_directories WHERE directory_name = ‘EXP_BACKUP’;

DIRECTORY_PATH
--------------------------------------------------------------------------------

/u01/demo_exp_backup
For oracle 10G, we are using data pump utility for export and import of data. For
Oracle 9i, we are using exp/imp utility.
DB_NAME: DEMO
TNS Service name: DEMO
Data Pump Directories for backup: EXP_BACKUP
Location of backup: /u01/demo_exp_backup
Userid/Password to export/import: sys/******** as sysdba

1.2   Database Level:

Steps for EXPORT:

Login as oracle user then execute mentioned commands as sysdba privilege.
For Oracle 9i: Make sure of PATH, where to save this export .dmp file. It would take complete database export backup in one .dmp file.

$exp FILE=$ORACLE_HOME/DEMO_full_export.dmp LOG=$ORACLE_HOME/DEMO_full_export.log FULL=y
For Oracle 10G: Make sure to use right DIRECTORY. It would make 4 export .dmp extension files as we have given PARALLEL=4
$ expdp DIRECTORY=EXP_BACKUP dumpfile=ORCL_full_export_backup_%U.dmp logfile= ORCL_full_export_backup_export.log FULL=y PARALLEL=4
It would make 4 export files in EXP_DIRECTORY. Much faster than single file level export.
Steps to IMPORT:
Create new database with right tablespace mapping. It would dump complete database as it is including each and every object.
For Oracle 9i:
$ cd $ORACLE_HOME
$ imp FILE=DEMO_full_export.dmp LOG=DEMO_full_import.log FULL=y STATISTICS=NONE
For Oracle 10G:
$impdp DIRECTORY=EXP_BACKUP dumpfile=ORCL_full_export_backup_%U.dmp logfile= ORCL_full_export_backup_import.log FULL=y PARALLEL=4

1.1   Schema Level:

Steps for EXPORT: Taken example as schema_name “test”
For Oracle 9i:
$exp FILE=$ORACLE_HOME/ TEST_export_sysdate.dmp LOG= $ORACLE_HOME/TEST_export_sysdate.log OWNER=TEST
For Oracle 10G:
$expdp DIRECTORY=EXP_BACKUP dumpfile=username_export_date.dmp logfile= username_export_date.log SCHEMAS=TEST
Steps for IMPORT:
Drop earlier test schema after export then import into new test schema.
Create test schema first with default tablespace mapping
For Oracle 9i:
$imp FILE=$ORACLE_HOME/ TEST_export_sysdate.dmp LOG= $ORACLE_HOME/TEST_import_sysdate.log FROMUSER=TEST TOUSER=USERNAME
For Oracle 10G:
$impdp DIRECTORY=EXP_BACKUP dumpfile=username_export_date.dmp
Logfile= username_import.log SCHEMAS=TEST

1.1   Replication of schema’s using expdp/impdp:

Assuming example as test is schema, which we want to replicate to TEST1
Steps to EXPORT TEST schema:
$expdp DIRECTORY=EXP_BACKUP dumpfile=username_export_date.dmp logfile= username_export_date.log SCHEMAS=TEST
Steps to replicate TEST into TEST1
$ impdp DIRECTORY=EXP_BACKUP dumpfile=username_export_date.dmp logfile= username_import_date.log REMAP_SCHEMA=TEST:TEST1
It would go same tablespace which we have assigned to TEST schema. Supose test schema having USERS tablespace. Then this import would look for USERS tablespace. If you want to change the TABLESPACE mapping for new tablespace then follow mentioned command.
$ impdp DIRECTORY=EXP_BACKUP dumpfile=username_export_date.dmp logfile= username_import_date.log REMAP_SCHEMA:TEST:TEST1  REMAP_TABLESPACE=USERS:USERS_TEST
This command would insert TEST1 data into USERS_TEST tablespace.

1.2     Table Level Export:

Assuming example for emp table under scott schema. You can table more than one table backup using comma operator to separate tablenames.
For Oracle 9i:
$ cd $ORACLE_HOME
$ exp FILE=DBNAME_SCOTT_EMP_export_sysdate.dmp LOG= DBNAME_SCOTT_EMP_export_sysdate.log OWNER=SCOTT TABLES=EMP
$ exp FILE=DBNAME_SCOTT_EMP-DEPT_export_sysdate.dmp LOG= DBNAME_SCOTT_EMP-DEPT_export_sysdate.log OWNER=SCOTT TABLES=EMP,DEPT
For Oracle 10G:
$expdp DIRECTORY= EXP_BACKUP dumpfile=DBNAME_tablename_export.dmp
Logfile=DBNAME_tablename_export.log SCHEMAS=SCOTT TABLES=EMP,DEPT

NOTE: Before import, make sure that table does not exist in schema or truncate table, which is going to be imported. Otherwise it would insert duplicate rows in table. And use IGNORE=Y clause with import. Import the table using userid/password as owner of tablename. We are using userid as SCOTT for import.
Import for table:
For Oracle 9i:
$cd $ORACLE_HOME
$imp scott/tiger FILE=DBNAME_SCOTT_EMP_export_sysdate.dmp LOG= DBNAME_SCOTT_EMP_import_sysdate.log IGNORE=y

For Oracle 10G:
$impdp DIRECTORY= EXP_BACKUP dumpfile=DBNAME_tablename_export.dmp
Logfile=DBNAME_tablename_export.log SCHEMAS=SCOTT TABLES=EMP,DEPT

1.1  Other Export Examples

Expdp/impdp examples:
The exclude and include parameters availbale with expdp,impdp can be used as metadata filters so that one can specify any objects like tables,indexes,triggers, procedure to be excluded or included during export or import operation
syntax:
expdp <other_parameters> SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:”IN (’EMP’,’DEPT’)”;
impdp <other_parameters> SCHEMAS=scott INCLUDE=PACKAGE,FUNCTION, PROCEDURE,TABLE:”=’EMP’”
Examples of operator-usage:
EXCLUDE=SEQUENCE
or:EXCLUDE=TABLE:”IN (’EMP’,’DEPT’)”
or:EXCLUDE=INDEX:”= ‘MY_INDX’”
or:INCLUDE=PROCEDURE:”LIKE ‘MY_PROC_%’”
or:INCLUDE=TABLE:”> ‘E’”
The parameter can also be stored in a par (parameter file) as shown
Parameter file:exp.par
DIRECTORY = EXP_BACKUP
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
SCHEMAS = scott
INCLUDE = TABLE:”IN (’EMP’, ‘DEPT’)”
expdp system/manager parfile=exp.par
If parameter file is not used then in unix special care needs to be taken in syntax of expdp and impdp, in particular all the single quotes and double quotes needs to be preceded with the special character ‘\’ .The syntax for windows and unix
Windows:
D:\> expdp system/manager DIRECTORY=EXP_BACKUP DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\”IN (’EMP’, ‘DEP’)\”
Unix:
% expdp system/manager DIRECTORY=EXP_BAKUP DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\”IN \(\’EMP\’, \’DEP\’\)\”

For more details see help of exp/imp and expdp/impdp
$exp help=y
$expdp help=y
$imp help=y
$impdp help=y
D:\expdp help=y