Datapump Activities
-Take refresh in the same database and from the same schema
-Take refresh in the same database but different schema
-Take refresh in the same database into different schema with different name
-Take backup activity and restoration back the same table.
-Take refresh to the remote database, to the same schema with different name.
-Multiple table backups from multiple schemas.
-Restore specific set of tables from the multiple tables backup
select segment_name, bytes/1024, owner from dba_segments where segment_name='DEPT' and owner='SCOTT';
select * from dba_directories;
mkdir -p /u01/oradata/dpbkps
create or replace directory DPBKPS as '/u01/oradata/dpbkps';
--Take the table dept backup from scott schema and refresh to the same schema when requested later.
expdp dumpfile=exp_scott.dept.dmp logfile=scott.dept.log directory=DPBKPS tables=scott.dept compression=all
Note: compression will include additional load on cpu, and it will take more time compare to normal.
You can drop the existing table or used table_exists_action command.
impdp directory=DPBKPS dumpfile=exp_scott.dept.dmp logfile=impdp_scott.dept.log table_exists_action=replace/truncate/append
--Table refresh in the same database but different schema
impdp directory=DPBKPS dumpfile=exp_scott.dept.dmp logfile=impdp_scott.dept.log remap_schema=SCOTT:SYSTEM
--Take refresh in the same database into different schema with different name
select table_name from dba_tables where table_name='DEPT_TMP';
impdp system/manager directory=DPBKPS dumpfile=exp_scott.dept.dmp logfile=impdp_scott.dept.log remap_schema=SCOTT:SYSTEM remap_table=DEPT.DEPT_TEMP
--Take refresh to the remote database, to the same schema with different name.
scp the export dumpfile to target location:
impdp system/manager directory=DPBKPS dumpfile=exp_scott.dept.dmp logfile=impdp_scott.dept.log table_exists_action=replace
--Multiple table backups from multiple schemas.
check the size of all tables.
select sum(bytes/1024) from dba_segments where segment_name in ('DEPT','DEPT_TEMP','EMP') and owner in ('SCOTT','SYSTEM');
expdp directory=DPBKPS dumpfile=expdp_multi_tables.dmp logfile=expdp_multi_tables.log tables=scott.dept,scott.emp,system.dept,system.dept_temp
impdp directory=DPBKPS dumpfile=
expdp_multi_tables.dmp logfile=expdp_multi_tables.log tables=system.dept table_exists_action=replace
-A schema backup when required
-Restore the same schema on the same database when requested
-A schema refresh from PROD to NON-PROD environment.
-A schema backup when requested and restore only set of tables from the backup dump.
-Taking multiple schemas backup backup and only restore one schema to the database or only one table of one schema to restore.
--A schema backup when required
Take the backup of SCOTT schema on MYDB database.
check the schema size:
select sum(byes/1024/1024) from dba_segments where owner='SCOTT';
check the directory location and its space:
select * from dba_directories;
expdp directory=dpbkps dumpfile=expdp_scott.dmp logfile=expdp_scott.log compression=all schema=SCOTT
Note: compression option will take longer time to export the backup.
expdp directory=dpbkps dumpfile=expdp_scott_%U.dmp logfile=expdp_scott.log compression=all schema=SCOTT parallel=2
--Restore the same schema on the same database when requested
drop the user if it is already available using cascade option.
impdp directory=dpbkp dumpfile=exp_scott_%U.dmp logfile=exp_scott.log parallel=2
--How to take DDL's backup only ?
expdp directory=dpbkps dumpfile=expdp_scott.dmp logfile=expdp_scott.log compression=all schema=SCOTT content=metadata_only
--A schema backup when requested and restore only set of tables from the backup dump.
impdp directory=dpbkp dumpfile=exp_scott_%U.dmp logfile=impdp_scott.dept.log parallel=2 tables=scott.dept table_exist_action=replace
--Taking multiple schemas backup backup and only restore one schema to the database or only one table of one schema to restore.
check the schema size:
select sum(byes/1024/1024) from dba_segments where owner in('SCOTT','OUTLN');
expdp directory=dpbkps dumpfile=expd_multi_%U.dmp logfile=exp_multi.log compression=all parallel=2 schemas=scott,outln
impdp directory=dpbkps dumpfile=exp_multi_%U.dmp logfile=impdp_multi.log parallel=2 tables=outln.ol$ table_exists_action=replace
No comments:
Post a Comment