>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:
—————————
oracle@node1>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:
————————–
o[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;
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’;
[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;
