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
'Database > ORACLE' 카테고리의 다른 글
| ora-00257 archiver error. connect internal only until freed (0) | 2018.01.08 |
|---|---|
| expdp failed with ORA-31641: unable to create dump file ORA-27054: NFS file system where the file is created or resides is not mounted with correct options (0) | 2018.01.08 |
| PCTFREE,PCTUSED,FREELIST (0) | 2018.01.05 |
| 오라클 undo / Pending Offline Segment (0) | 2018.01.04 |
| 오라클 11g 자동 통계 정보 수집 내용 변경 (0) | 2017.12.30 |