Datapump Export / Import Scenarios

Data pump was introduced from 10 version of oracle.

Utilities of datapump are:
1.expdp
2.impdp

Dump file generated by expdp cant be imported by imp.

The pre-requisite for data pump is we need to create a directory at the
server side at oracle level as well as os level.

In order to take backup of objects user should have read,write privilege on the directory.

Practical / Demo:
óóóóóñ——

[email protected]>su ñ oracle
[email protected]>export ORACLE_SID=sample
[email protected]>sqlplus / as sysdba

SQL>startup
SQL>select name,open_mode,log_mode from v$database;
SQL>select instance_name,status from v$instance;

[email protected]>export ORACLE_SID=sample
[email protected]>which expdp
[email protected]>which impdp
[email protected]>expdp dumpfile=sys.dmp logfile=sys.log
username:/ as sysdba

SQL> select name from v$tablespace;
SQL>create tablespace temp03
tempfile ë/u03/app/oracle/sample/temp03.dbfí size 10m;

[email protected]>expdp dumpfile=sys.dmp logfile=sys.log
username: / as sysdba

SQL>select count(*) from tab;

error: if dumpfile exist so we change name and enter again.

[email protected]>expdp dumpfile=sys.dmp logfile=sys1.log
username: / as sysdba

SQL> select count(*) from tab;
SQL> create directory dp as ë/u03/datapump’;
SQL> select * from dba_directories;

[email protected]> mkdir -p /u03/datapump
[email protected]> chown -R oracle:oinstall /u03/datapump
[email protected]> chmod -R 775 /u03/datapump
[email protected]> su ñ oracle

[email protected]> export ORACLE_SID=sample

óóóóóóóóóóóóóóóóóóóó
Taking Backups ñ EXPORTING
óóóóóóóóóóóóóóóóóóóó
1.Full database backup
óóóóóóóñ

SQL>select count(*) from tab;

[email protected]>expdp dumpfile=fulldb.dmp logfile=fulldb.log
full=y directory=dp job_name=fullbackup direct=y

username:/ as sysdba

SQL>select count(*) from tab;
SQL>select * from dba_datapump_jobs;

Stopping the job:

press ctrl+c

Export>stop_job=immediate
Are your sure stop the job:Yes

SQL>select * from dba_datapump_jobs;

To Restart particular job after some time:
óóóóóóóóóóóóóó

[email protected]>expdp attach=fullbackup

username:/ as sysdba

Export> continue_client

2.Taking User Level Backup:
óóóóóóóóó

[email protected]>expdp dumpfile=user.dmp logfile=user.log schemas=scott
directory=dp

username:/ as sysdba

3.Taking Table Level Backup:
óóóóóóóóó-

[email protected]>expdp dumpfile=table.dmp logfile=table.log tables=emp,dept
directory=dp

username:scott/tiger

Error: No such permisssion on directory dp, so we give permission to user scott
and try again.

SQL> grant read,write on directory dp to scott;

[email protected]>expdp dumpfile=table.dmp logfile=table.log tables=emp,dept
directory=dp

username: scott/tiger

4.Taking row level backup:
óóóóóóóóñ

[email protected]>expdp dumpfile=row.dmp logfile=row.log tables=emp
directory=dp query=íwhere deptno=10′

username:scott/tiger

4.Taking Metadata backup:
óóóóóóóó-

[email protected]>expdp dumpfile=meta.dmp logfile=meta.log tables=emp rows=n
directory=dp

username: scott/tiger

5.Taking Tablespace level backup:
óóóóóóóóóóó

SQL>select name from v$tablespace;

[email protected]>expdp dumpfile=ts.dmp logfile=ts.log tablespaces=users
directory=dp

username:/ as sysdba

óóóóóóóóóóóóóóóóóóóóóóóóñ
APPLYING BACKUPS ñ IMPORTING
óóóóóóóóóóóóóóóóóóóóóóóóñ
1.User Level Importing
óóóóóóóñ

SQL>drop user scott cascade;
SQL>select username from all_users where username=íSCOTT’;

[email protected]>impdp dumpfile=user.dmp remap_schema=scott:scott

directory=dp

username:/ as sysdba

SQL>select username from all_users where username=íSCOTT’;

[email protected]>impdp dumpfile=user.dmp remap_schema=scott:u1

username:/ as sysdba

2.Table Level Importing: (First Drop emp and dept tables)
óóóóóóóóóóóóóóóóóóó-

[email protected]>impdp dumpfile=table.dmp remap_schema=scott:u6
directory=dp

username:/ as sysdba

U6> select * from tab;

3.Row Level Importing:
óóóóóóó-

[email protected]> impdp dumpfile=row.dmp remap_schema=scott:u6

directory=dp

username: / as sysdba

U6> select * from tab;

4.Importing Metadata:
óóóóóóó

[email protected]> impdp dumpfile=meta.dmp remap_schema=scott:u6
directory=dp

username:/ as sysdba

u6> select * from tab;
u6> select * from emp;
u6> desc emp;

==============================================================================================
Oracle Datapump ( expdp ) / Import ( impdp ) 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!

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

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

1. Logical backup (Datapump expdp/impdpdp)
   We can take expdport(expdp) in four levels.

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

Note: Do the expdport in Source(ctsp) database and impdport in target(ctsd) database.

  Database must be in open and create the directory in both source and target database.

Sourec Database:

SQL> create directory expdp_dir1 as ‘/u01/app/oracle/’;
SQL> grant read,write on directory expdp_dir1 to system;

SQL> select *from dba_directories;

Target Database:

SQL> create directory expdp_dir1 as ‘/u01/app/oracle/’;

SQL> grant read,write on directory expdp_dir1 to system;

SQL> select *from dba_directories;

1A. Full database Level expdp/impdp

expdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=ctsfull.log full=y

impdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=ctsfull_impdp.log full=y

1B. Schema(User) Level expdp/impdp

expdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser.log schemas=raja

impdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser_impdp.log remap_schema=raja:raja

Note: RAJA is a user in the database

1C. Table Level expdp/impdp

expdp system/manager directory=expdp_dir1 dumpfile=ctstab.dmp logfile=ctstab.log tables=raja.emp

impdp system/manager directory=expdp_dir1 dumpfile=ctstab.dmp logfile=ctstab_impdp.log remap_schema=raja:raja tables=emp

DB Refresh steps:

1. take export(expdp) of source database(ctsp).

expdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=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 ctsfull.dmp [email protected]:/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 raja cascade;

4. Befor doing import(impdp) check the used space of source database and freespace in the target database. tablespaces names should

same between sourec 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 impdport in taget database.

impdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=ctsfull_impdp.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;

=============================== end ===========================

Schema Refersh steps:

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

expdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser.log schemas=raja

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 ctsuser.dmp [email protected]:/u02/app/oracle

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

select username from dba_users;

drop user raja cascade;

create user raja identified by raja;

4. Befor doing import(impdp) check the used space of a schema(user) in source database and freespace 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=’RAJA’ group by tablespace_name;

Target:

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

5. Start the import(impdp) in taget database.

impdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser_impdp.log remap_schema=raja:raja

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

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

Share this post: on Twitter on Facebook on Google+