Oracle DataBase Import Export Steps

ORACLE DATABASE EXPORT- IMPORT STEPS

This document will describe the steps to export the oracle database from one server (machine) and import this database to different server (machine) or we can do this on same machine.

1)      Exporting the DataBase :- Please refer to  the following commands for doing so:

a)      First create a Directory for the actual path where original DataBase resides. Suppose the Path is d:/database. Then the command should be as:

àCreate or replace directory ‘Directory Name’ as ‘d:/database’;

    (Run these commands on CMD)

     

                  b) Now run this Command for exporting database

à Expdp UesrName/Password@SID    SCHEMAS=() directory=’Directory name’ dumpfile= ‘File name.dmp’ logfile=’file name.txt’;

 Note :- If want to export all schemas just mention full=Y in place of schemas.

c) Exract the table spaces in the database with following command in toad

select file_name from dba_data_files;

Match all the table space with standard tablespace script which needs to be run in the bank database before import data and create additional tablespaces if any.(Sometimes a table space is exteneded automatically to manage the more data so for this additional tablespace we need to create in the blank database as mention in some later steps of import data)

     2)  Importing the DataBase :- Please refer to  the following commands for doing so:

a)      Create a blank database in destination oracle. 

1. Run as :à dbca
2.  Click Next. 
3. Click Next.
4. Type the DATABASE & SID Name --Click Next.
5.  Click Next.
6.  Type password for DATABASE --Click Next.
7.  Click Next.
8.  Click Next.
9. Click Finish.
10.  It will create the Blank DataBase.

After creating the blank database

 Create tablespace Script with commands as:

 Create  tablespace extracted in step 2 (c) as below

CREATE TABLESPACE "ERES_SMALL"

LOGGING

    DATAFILE 'D:\oracle\product\10.2.0\tbs\ERES_SMALL.ora' SIZE 10M AUTOEXTEND ON NEXT 10M

    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

---D:\oracle\product\10.2.0\tbs  :- This should be the path of the tablespace folder created for  database

--Execute the script as SYSDBA user And Give all grats required to the users you created

 --This script should contains create command for all the table spaces in the previous database.

Create a directory from sqlPlus db_dir for folder where .dmp file and .log file exists or we copied it (Database backup files)as

SQL> create or replace directory db_dir as 'D:\CathpciDatabase';

CMD>impdp UsrName/Password@SID schemas=(Schema Names) Directory=db_dir DUMFILE=Where actual DB exists LOGFILE=Where actual logfile exists VERSION=10.2.0.3