impdp - ORA-31640 ORA-31693 ORA-19505 ORA-27037

impdp ORA-31640 ORA-31693 ORA-19505 ORA-27037

금일 IMPDP 명령어로

상콤하게 import 작업을 하다가 위와 같은 ora 에러르 만났다...

처음엔 깜놀 했다가

parallel 옵션 을 사용할 때 dump 파일을 모든 rac 노드가  읽지 못하기 때문이라고 알게되었다

cluster=N 옵션을 통해 해결 가능하다

아래는 원문이다.

When I am trying to import schema, The impdp is failed to open dump file. see below script I am using.

$ cat parfile.par
USERID='/ as sysdba'
DIRECTORY=IMP_DP
DUMPFILE=SMARTSUPPY_SCHEMA.dmp
logfile=SMARTSUPPY_SCHEMA_09nov2014.log
parallel=10
SCHEMAS='SMARTSUPPY_SCHEMA'

I got below error while import..

ORA-31693: Table data object "SMARTSUPPY_SCHEMA"."BPM_TRANS_90000":"P297" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/export/APSDBSR-1351/SMARTSUPPY_SCHEMA.dmp" for read
ORA-19505: failed to identify file "/export/APSDBSR-1351/SMARTSUPPY_SCHEMA.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
. . imported "SMARTSUPPY_SCHEMA"."BPM_TRANS_90000":"P315"      265.3 MB 1484775 rows
. . imported "SMARTSUPPY_SCHEMA"."TRC_HOP_T_90000":"P139"      219.7 MB 1402774 rows

The issue is due to I am using PARALLEL=10, so the dump file directory not being accessible from all nodes in the RAC. When we use PARALLEL > 1 option the child thread will be started in other node to complete the job faster, when job started in other node the dump file can't access the file. Due to this we receive this error. To fix the issue either you place the dump file to be accessed from all the nodes in that RAC environment or use cluster=N option.

Added cluster=N parameter in parfile and started the import again. 

$ cat parfile.par
USERID='/ as sysdba'
DIRECTORY=IMP_DP
DUMPFILE=SMARTSUPPY_SCHEMA.dmp
logfile=SMARTSUPPY_SCHEMA_09nov2014.log
parallel=10
SCHEMAS='SMARTSUPPY_SCHEMA'
CLUSTER=N
$

$ nohup impdp parfile=parfile.par &
[1] 27620
> nohup: ignoring input and appending output to `nohup.out'
$
$ jobs -l
[1]+ 27620 Running                 nohup impdp parfile=parfile.par &
$

$ tail -f SMARTSUPPY_SCHEMA_09nov2014.log
Master table "SYS"."SYS_IMPORT_SCHEMA_03" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_03":  /******** AS SYSDBA parfile=parfile.par
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P475"  1.956 GB 6711857 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P480"  1.965 GB 6794127 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P474"  1.958 GB 6727517 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P479"  1.921 GB 6616816 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P478"  1.903 GB 6512491 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P481"  1.909 GB 6618578 rows

Hope this will help you.. :)

Best Regards,
Chowdari

출처 : http://mbc-dba.blogspot.kr/2014/11/impdp-ora-31640-ora-31693-ora-19505-ora.html