Expdp / Impdp , FRA and FDA

FRA :

1. DB_RECOVERY_FILE_DEST_SIZE
2. DB_RECOVERY_FILE_DEST



SELECT * FROM V$RECOVERY_FILE_DEST;


SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

To backup FRA :  BACKUP RECOVERY AREA;


FDA :

Flashback data archives retain historical data for the time duration specified using the RETENTION parameter.FDA can also be used or auditing purpose. It is recommended to create archive for different retention periods and then group tables with same retention policy.Whenever undo data is generated for a FDA enabled table Oracle flags it so it will be processed by the FBDA background process.

CREATE FLASHBACK ARCHIVE flashback_archive
       TABLESPACE flashback_archive_tbs QUOTA 15G
RETENTION 5 YEARS;

ALTER FLASHBACK ARCHIVE flashback_archive1
      ADD TABLESPACE flashback_archive_tbs1 QUOTA 2G;

ALTER TABLE EMP FLASHBACK ARCHIVE;

ALTER TABLE EMP FLASHBACK ARCHIVE Flashback_Archive1;


SELECT flashback_archive_name,
       flashback_archive#,
       tablespace_name,
       quota_in_mb
FROM   dba_flashback_archive_ts

ORDER BY flashback_archive_name;




Expdp / Impdp


-- Export

expdp directory=dpump dumpfile=file.dmp logfile=file.log TABLES=schema.tablename  EXCLUDE=TABLE_STATISTICS,INDEX_STATISTICS

--Parallel export :

expdp tables=CASES directory=DPDATA1 parallel=4 dumpfile=\(expCASES1.dmp,epCASES2.dmp,expCASES3.dmp,expCASES4.dmp\)
or
expdp tables=CASES directory=DPDATA1 dumpfile=expCASES_%U.dmp parallel=4


--To create tablespaces in test

expdp directory=dpump_dir dumpfile=metadata.dmp content=metadata_only

impdp include=tablespace sqlfile=c.sql

impdp include=tablespace sqlfile=c.sql transform=pctspace:10

--Changing table’s Owner

impdp remap_schema=“OLDU:NEWU” network_link=maindb directory=… -- bypass dumpfile creation step.


--DF location change for tablespace

impdp remap_datafile=‘/u01/data1.dbf':'/u02/data1.dbf'


--Remove Storage options

impdp arup/arup   directory=tmp_dir dumpfile=metadata_full.dmp     sqlfile=metadata_full.sql     include=table:"='TRANS'"   transform=segment_attributes:n:table

expdp transform=pctspace:30 tables=accounts


-- Monitoring export :

select sid, serial#
from v$session s, dba_datapump_sessions d
where s.saddr = d.saddr;


select sid, serial#, sofar, totalwork
from v$session_longops
where opname = '<DP Job Name>'
and sofar != totalwork;


-- Determine the time and space expdp will take before actually running

expdp  / directory=dpump  schemas=scott,hr estimate_only=y



To disable archive log generation in 12c

transform = disable_archive_logging


Export Mutiple tables from list stored in a table

expdp / directory=DIR dumpfile=expdp_tables.dmp logfile=expdp_dmtables.log
schemas=schema1,schema2
include=TABLE:"IN\(SELECT object_name FROM <schema>.expdp_table\)"


Export with where condition

expdp / directory=DIR dumpfile=expdp_tables.dmp logfile=expdp_dmtables.log
tables=(schema1.tab1,schema1.tab2)
query=schema1.tab1:"where end_date=to_date('4195-01-01','yyyy-mm-dd')",schema1.tab2:"where end_date= to_date ('4195-01-01','yyyy-mm-dd')"