+15146131498

Oracle DBA Support info@awator.net

Oracle Export ( exp ) / Import

Oracle Export ( exp ) / Import ( imp ) With Full DB and Schema Refresh Steps

ORACLE DATABASE BACKUPS:

Backup and recovery is one of the most important aspects of a DBA’s job. If you lose your company’s data, you could very well lose your job. Hardware and software can always be replaced, but your data may be irreplaceable!

Types of Backup:

1. Logical backup (exp/imp –> from Oracle 9i , expdp/impdp –>from Oracle 10g)
2. Physical backup

2.A) User Managed backup( Cold and Hot backup)
2.B) Server Manager backup( RMAN – Recovery Manager )

1. Logical backup (exp/imp)
====================

We can take export(exp) in four levels.

1A. Full database Level export/import
1B. Schema(User) Level export/import
1C. Table Level export/import
1D. Tablespace Level export/import

Please note that theĀ  export in done in Source(ctsp) database and import in target(ctsd) database and the only condition is that the Database must be in open.

1A. Full database Level export/import
=====================================

exp system/manager file=’/u01/app/oracle/ctsfull.dmp’ log=’/u01/app/oracle/ctsfull.log’ full=y

imp system/manager file=’/u01/app/oracle/ctsfull.dmp’ log=’/u01/app/oracle/ctsfull_imp.log’ full=y

1B. Schema(User) Level export/import

====================================
exp system/manager file=’/u01/app/oracle/ctsuser.dmp’ log=’/u01/app/oracle/ctsuser.log’ owner=leopold

imp system/manager file=’/u01/app/oracle/ctsuser.dmp’ log=’/u01/app/oracle/ctsuser_imp.log’ fromuser=leopold touser=leopold

Note: leopold is a user in the database

1C. Table Level export/import
=============================
exp system/manager file=’/u01/app/oracle/ctstab.dmp’ log=’/u01/app/oracle/ctstab.log’ tables=leopold.emp

imp system/manager file=’/u01/app/oracle/ctstab.dmp’ log=’/u01/app/oracle/ctstab_imp.log’ fromuser=leopold touser=leopold tables=emp

DB Refresh steps:
=================

1. take export of source database(ctsp).

exp system/manager file=’/u01/app/oracle/ctsfull.dmp’ log=’/u01/app/oracle/ctsfull.log’ full=y

2. move the dumpfile to target database(ctsd) using scp.. if both the database running in same server this steps is not required.

scp /u01/app/oracle/ctsfull.dmp oracle@<Target server IP>:/u02/app/oracle

3. Drop the application users and dont drop database default users like sys,system,dbsnmb,xdb.

If you give below command it will show username,created date. Mostly database default users will be created in same day.

select username,created from dba_users;

drop user leopold cascade;

4. Before doing import check the used space of source database and free space in the target database. tablespaces names should same between source and target database then Target database each tablespace size should be more than or equal to source database tablespace.

Source:

select tablespace_name,sum(bytes/1024/1024) from dba_segments group by tablespace_name;

Target:

select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;

5. Start the import in taget database.

imp system/manager file=’/u01/app/oracle/ctsfull.dmp’ log=’/u01/app/oracle/ctsfull_imp.log’ full=y

6. once competed compare the object counts between source and target databases.

select object_type,count(*) from dba_objects where status=’VALID’ group by object_type;

===================================================================================

Schema Refersh steps:
=====================

1. take export of a schema in source database(ctsp).

exp system/manager file=’/u01/app/oracle/ctsuser.dmp’ log=’/u01/app/oracle/ctsuser.log’ owner=leopold

Note: Dont give semicolon(;) at the end of the above command.

2. move the dumpfile to target database(ctsd) using scp.if both the database running in same server this steps is not required.

scp /u01/app/oracle/ctsuser.dmp oracle@<Target server IP>:/u02/app/oracle

3. create the new user in target database(if already existed drop and recreate)

select username from dba_users;

drop user leopold cascade;

create user leopold identified by leopold;

4. before doing import check the used space of a schema(user) in source database and free space in the target database then target

database tablespaces should be more than or equal to source database tablespaces.

Source:

select tablespace_name,sum(bytes/1024/1024) from dba_segments where owner=’leopold’ group by tablespace_name;

Target:

select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;

5. Start the import in taget database.

imp system/manager file=’/u01/app/oracle/ctsuser.dmp’ log=’/u01/app/oracle/ctsuser_imp.log’ fromuser=leopold touser=leopold

6. once competed compare the object counts between source and target databases.

select object_type,count(*) from dba_objects where owner=’leopold’ and status=’VALID’ group by object_type;

For More information

contact info@awator.net

SOCIAL LINK

Copyright

Copyright (c) 1998-2014