(Please Note my other blog address: svgulhane wordpress IT updates)
Check points before IMPDP/EXPDP
- Check
the Oracle directory existed and have created the OS directory where
you want to store dump files. This Directory will pointed to OS
directory where Oracle will create the .dmp files, from where later you
can point your IMPDP job by using same Oracle Directory.
Oracle Directory
create or replace directory IN_WIP as '/oraint2/oradata/FCIS921/dpdump/';
grant execute, read, write on directory dir to UBS105SUP;
OR
grant all on directory dir to UBS105SUP;
- Check
the free space available in the server mount point. Space need to be
estimated to be consumed during expdp, else your job get stopped. In
such situaltion you can free the space by deleting unwanted files from
the storage space and then job get restarted automatically. You can
check the status by attaching the job with ATTACH command.
df –h
[user@server1~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 184G 32G 143G 18% /
tmpfs 7.5G 0 7.5G 0% /dev/shm
/dev/xvda1 976M 295M 631M 32% /boot
/dev/xvda5 4.8G 777M 3.8G 17% /oem
blrnas404.in.oracle.com:/export/1170
- Check the System and User Tablespace size, it should be sufficient. Please note System tablespace used by the Oracle for its scratch work during the IMPDP and also mostly consumed during compilation of the objects. This System tablespace cannot be recoverd or you can not get it returned even you drop the Schema. In case of Schema tablespace you can get the space returned by dropping that schema and space get used for storing the actual data of the tables and other objects. You can check space before firing IMPDP command by using following query.
SET LINESIZE 300
SET PAGESIZE 225
column File_path format a60
column AUTOEXTENSIBLE format a3
column Allocated format 999,999,9999
column Free format 999,999,9999
column Maxsize format 9999999,9999
column Yet_Not_alloc_MB format 9999999,9999
select
a.tablespace_name,
sum(a.BYTES/(1024*1024))Allocated,
(b.free_space) Free,
sum(a.maxbytes/(1024*1024))Maxsize ,
abs(sum((a.maxbytes/(1024*1024))-(a.BYTES/(1024*1024)))) Yet_Not_alloc_MB
from dba_data_files a,
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) b
where a.tablespace_name like('%&TS_Name%') and a.tablespace_name=b.tablespace_name
group by a.tablespace_name,free_space
order by a.tablespace_name ;
- Check the version of the source and target database, to avoid
following types of error during import of the dump file into different
version of the database schema.SELECT * FROM V$VERSIONIMP-00010: not a
valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully
ORA-39001: invalid argument value
ORA-39000: bad dump file specificationImport: Release 10.2.0.3.0 – 64bit Production on Tuesday, 07 June, 2017 09:43:10Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file
“/u04/oracledb/dpdump/test_version_failed.dmp”
To avoid this issue you need to care while exporting the dump you need to mention the target version of the DB. E.G
If you want to import your 11g expdp dump file into Oracle 10g in this case you need to add VERSION=10.2 parameter during export.
From 11g expdp command:
expdp xyz/xxxxx parfile=exp_version.par version=10.2
Now import the dump into the 10.2.0.1 database.
From 10g impdp command:
impdp xyz/xxxx parfile=imp_version.par - Check the size of the dump to be imported
ls –lrt
-rw-rw-rw- 1 db1123 oinstall 12538 Nov 20 00:00 expdp-20112014_000001.log
-rw-rw---- 1 db1123 oinstall 2210488320 Nov 20 00:00 expdp-20112014_000001.dmp
-rw-rw-rw- 1 db1123 oinstall 4497 Nov 20 00:00 expdp-tinfra-20112014_000001.log
-rw-rw---- 1 db1123 oinstall 16900096 Nov 20 00:00 expdp-tinfra-20112014_000001.dmp
- Check all the privileges related to expdp or impdp are granted.
set linesize 300
set pagesize 100
column Object format a30
column PRIVS format a30
column Type format a20
select P1 OBJECT, nvl(o.object_type, '(ROLE)')Type
, P2 PRIVS from (select Grantee G, Table_name p1, privilege p2
from dba_tab_privs
union
select Grantee G, null p1, granted_role p2
from dba_role_privs
union
select Grantee G, null p1, privilege p2 from dba_sys_privs),
dba_objects o
where G = '&USER'
and p1 = o.object_name(+);
- Grant all the basic privileges required.
grant create session to &&USR;
create public synonym dbms_shared_pool for sys.dbms_shared_pool;
grant select on dba_jobs_running to &USR;
grant select on v_$database to &USR;
grant select on v_$nls_parameters to &USR;
grant select on v_$parameter to &USR;
grant select on v_$process to &USR;
grant select on v_$session to &USR;
grant select on v_$timer to &USR;
grant select on v_$instance to &USR;
grant create database link to &USR;
grant create library to &USR;
grant create materialized view to &USR;
grant create procedure to &USR;
grant create sequence to &USR;
grant create session to &USR;
grant create synonym to &USR;
grant create table to &USR;
grant create trigger to &USR;
grant create type to &USR;
grant create view to &USR;
grant execute on dbms_alert to &USR;
grant execute on dbms_application_info to &USR;
grant execute on dbms_aq to &USR;
grant execute on dbms_aqadm to &USR;
grant execute on dbms_defer to &USR;
grant execute on dbms_defer_query to &USR;
grant execute on dbms_defer_sys to &USR;
grant execute on dbms_job to &USR;
grant execute on dbms_lock to &USR;
grant execute on dbms_pipe to &USR;
grant execute on dbms_refresh to &USR;
grant execute on dbms_rls to &USR;
grant execute on dbms_shared_pool to &USR;
grant execute on dbms_sql to &USR;
grant execute on utl_file to &USR;
--grant exp_full_database to &USR; (Check with DBA before granting this privilege)
--grant imp_full_database to &USR; (Check with DBA before granting this privilege)
grant execute on SYS.UTL_MAIL to &USR;
--grant execute on SYS.CTX_DDL to &USR;
grant execute on CTXSYS.CTX_DDL to &USR;
grant execute on SYS.DBMS_CRYPTO to &USR;
grant execute on SYS.UTL_RAW to &USR;
grant select on dba_data_files to &Usr;
grant select on dba_free_space to &Usr;
- Check the location of the dump and privileges granted on that path
ls –lrt----- 2gb
-rw-rw-rw- 1 db1123 oinstall 12538 Nov 20 00:00 expdp-20112014_000001.log
-rw-rw---- 1 db1123 oinstall 2210488320 Nov 20 00:00 expdp-20112014_000001.dmp
-rw-rw-rw- 1 db1123 oinstall 4497 Nov 20 00:00 expdp-tinfra-20112014_000001.log-rw-rw---- 1 db1123 oinstall 16900096 Nov 20 00:00 expdp-tinfra-20112014_000001.dmp-----16 mb
Parallel Import/Export needs to have multiple .dmp files
If there are 6 CPU then you can have 12 dump files can be optimal for parallel processing.
EXPDP Parallel Example:export.par
FILESIZE=10G
compression=all
parallel=32
logfile=expdp_t7.log
dumpfile=expd_t7_%u.dmp
schemas=fcubs1201
directory=expdir
COMPRESSION=ALL
JOB_NAME=fcubs1201_job
Execute
expdp parfile=export.par
Username: sys as sysdba
Password:welcome1
IMPDP Parallel Example:
impdp system/sysdec2012@TRAININGDB schemas=test parallel=2 DIRECTORY=DATA_DUMP DUMPFILE=test_%U.dmp LOGFILE=test.log JOB_NAME=testJob content=metadata_onlyDo not invoke Import as SYSDBA, except at the request of Oracle technical support. SYSDBA is used internally and has specialized functions; its behavior is not the same as for general users.
Increase undo retention
Increase redo log file size
If db is in archivelog mod then check archivelog it may get filled and get following error
ORA-19815: WARNING: db_recovery_file_dest_size of 3211788288000 bytes is 100.00% used, and has 0 remaining bytes available.Go to rman list archivelog all delete archivelog all
Checkpoints after IMPDP/EXPDP
- Check the count of the objects imported so far and find out how many was there in source schema.
- Check the log file generated during expdp or impdp for checking the progress of the dump.
- Generally import failed due to system or user tablespace not having free space. Also some time there is no free space in hard disk.
- Check if any user has locked the objects, if lock exists then clear that locksselect 'alter system kill session '''|| sess.sid||','||sess.serial#||''' immediate ;' from v$locked_object lo,dba_objects ao,v$session sess where ao.object_id=lo.object_id and lo.session_id=sess.sid ;
- If your session get closed you can attach to running job. Attach to that job_name and find the issues by status command.
impdp username_to_whom_job_executed/pwd attach= 'SYS_IMPORT_FULL_02'
expdp ddl/slrmclaren@TCTOOLS attach= 'DDLEXP'
impdp>statusStatus (Shows the running status of the job)
impdp> STOP_JOB=IMMEDIATE
impdp>kill_job
stop job immediate (We can start the job again)
Query to verify the expdp/impdp
select round(sofar/totalwork*100,2) percent_completed, v$session_longops.* from v$session_longops where sofar <> totalwork order by target, sid;
select substr(sql_text, instr(sql_text,'"')+1,
instr(sql_text,'"', 1, 2)-instr(sql_text,'"')-1)
table_name,
rows_processed,
round((sysdate
- to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))
*24*60, 1) minutes,
trunc(rows_processed /
((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))
*24*60)) rows_per_min
from
v$sqlarea
where
upper(sql_text) like 'INSERT % INTO "%'
and
command_type = 2
and
open_versions > 0;
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;
select * from USER_DATAPUMP_JOBS;
select * from DBA_DATAPUMP_SESSIONS;
select owner_name, job_name,state from dba_datapump_jobs where state not in 'NOT RUNNING';
SET LINESIZE 300
SET PAGESIZE 200
column OWNER_NAME format a10
column JOB_NAME format a30
column OPERATION format a10
column JOB_MODE format a6
column STATE format a20
column DATAPUMP_SESSIONS a10
column ATTACHED_SESSIONS a10
select OWNER_NAME,JOB_NAME,substr(operation,1,10) operation,state, ATTACHED_SESSIONS, DATAPUMP_SESSIONS from dba_datapump_jobs
where state not in 'NOT RUNNING';
