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:
—————–

root@node1>su – oracle
oracle@node1>export ORACLE_SID=sample
oracle@node1>sqlplus / as sysdba

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

oracle@node1>export ORACLE_SID=sample
oracle@node1>which expdp
oracle@node1>which impdp
oracle@node1>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;

oracle@node1>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.

oracle@node1>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;

root@node1> mkdir -p /u03/datapump
root@node1> chown -R oracle:oinstall /u03/datapump
root@node1> chmod -R 775 /u03/datapump
root@node1> su – oracle

oracle@node1> export ORACLE_SID=sample

————————————————————
Taking Backups – EXPORTING
————————————————————
1.Full database backup
———————–

SQL>select count(*) from tab;

oracle@node1>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:
——————————————

oracle@node1>expdp attach=fullbackup

username:/ as sysdba

Export> continue_client

2.Taking User Level Backup:
—————————

oracle@node1>expdp dumpfile=user.dmp logfile=user.log schemas=scott
directory=dp

username:/ as sysdba

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

oracle@node1>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;

oracle@node1>expdp dumpfile=table.dmp logfile=table.log tables=emp,dept
directory=dp

username: scott/tiger

4.Taking row level backup:
————————–

oracle@node1>expdp dumpfile=row.dmp logfile=row.log tables=emp
directory=dp query=’where deptno=10′

username:scott/tiger

4.Taking Metadata backup:
————————-

oracle@node1>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;

oracle@node1>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’;

oracle@node1>impdp dumpfile=user.dmp remap_schema=scott:scott

directory=dp

username:/ as sysdba

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

oracle@node1>impdp dumpfile=user.dmp remap_schema=scott:u1

username:/ as sysdba

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

oracle@node1>impdp dumpfile=table.dmp remap_schema=scott:u6
directory=dp

username:/ as sysdba

U6> select * from tab;

3.Row Level Importing:
———————-

oracle@node1> impdp dumpfile=row.dmp remap_schema=scott:u6

directory=dp

username: / as sysdba

U6> select * from tab;

4.Importing Metadata:
———————

oracle@node1> impdp dumpfile=meta.dmp remap_schema=scott:u6
directory=dp

username:/ as sysdba

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s