내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
2024
10.12
14:00
10.12
14:00
OS환경 : Oracle Linux6.8(64bit)
DB 환경 : Oracle Database 11.2.0.4
에러 : datapump stop_job 후 dmp 파일 삭제 후 NOT RUNNING JOB 삭제
datapump 고아 프로세스 삭제 에러
1 2 3 4 5 6 | ORA-39002: invalid operation ORA-39000: bad dump file specification ORA-31640: unable to open dump file "/home/oracle/scott.dmp" for read ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 |
테스트 시나리오
datapump용 directory 생성 및 권한 부여
1 2 | SQL> create directory shuttest as '/home/oracle/'; SQL> grant read, write on directory shuttest to public; |
scott 유저 모든 오브젝트 expdp 실행 job_name=shut_job2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | $ expdp system/oracle dumpfile=scott.dmp directory=shuttest schemas=scott job_name=shut_job2 Export: Release 11.2.0.4.0 - Production on Tue Jan 8 11:03:51 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters and Data Mining options Starting "SYSTEM"."SHUT_JOB2": system/******** dumpfile=scott.dmp directory=shuttest schemas=scott job_name=shut_job2 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 256.9 MB 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/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE ^C (컨트롤 + C로 중지) |
expdp 상태 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | Export> status Job: SHUT_JOB2 Operation: EXPORT Mode: SCHEMA State: EXECUTING Bytes Processed: 0 Current Parallelism: 1 Job Error Count: 0 Dump File: /home/oracle/scott.dmp bytes written: 4,096 Worker 1 Status: Process Name: DW00 State: EXECUTING Object Schema: SCOTT Object Name: NEW_EMP Object Type: SCHEMA_EXPORT/TABLE/TABLE Completed Objects: 1 Worker Parallelism: 1 |
expdp job 중지
1 2 | Export> stop_job Are you sure you wish to stop this job ([yes]/no): yes |
dmp 파일 확인
1 2 3 4 5 6 7 | $ cd /home/oracle $ ls -al total 102760 drwxr-xr-x. 6 oracle dba 4096 Jan 8 11:04 . drwxr-xr-x. 4 root root 4096 Nov 9 16:10 .. -rw-r--r-- 1 oracle dba 1126 Jan 8 11:05 export.log -rw-r----- 1 oracle dba 28672 Jan 8 11:05 scott.dmp |
scott.dmp 파일 삭제
1 | $ rm -rf scott.dmp |
dba_datapump_jobs 확인
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> col owner_name for a10 col job_name for a10 col operation for a10 col job_mode for a10 col state for a20 select owner_name, job_name, operation, job_mode, state from dba_datapump_jobs; OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ---------- ---------- ---------- ---------- -------------------- SYSTEM SHUT_JOB2 EXPORT SCHEMA NOT RUNNING |
dmp 파일은 삭제하였지만 expdp job는 그대로 남아있는것을 확인가능
expdp job을 kill 시키기 위해 job 재실행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $ expdp system/oracle attach=shut_job2 Export: Release 11.2.0.4.0 - Production on Tue Jan 8 11:08:15 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters and Data Mining options ORA-39002: invalid operation ORA-39000: bad dump file specification ORA-31640: unable to open dump file "/home/oracle/scott.dmp" for read ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 |
파일이 없기 때문에 에러 발생
해결 방법 : job_name으로 만들어진 table을 삭제해 준다.
dba_datapump_jobs 확인
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> col owner_name for a10 col job_name for a10 col operation for a10 col job_mode for a10 col state for a20 select owner_name, job_name, operation, job_mode, state from dba_datapump_jobs; OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ---------- ---------- ---------- ---------- -------------------- SYSTEM SHUT_JOB2 EXPORT SCHEMA NOT RUNNING |
위 쿼리에서 나온 OWNER_NAME(expdp실행한 계정)로 접속
1 2 | SQL> conn system/oracle Connected. |
expdp job 이름으로 dba_objects 확인
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> col owner for a10 col object_name for a10 col object_type for a10 col status for a10 select owner, object_name, object_type, status from dba_objects where object_name = 'SHUT_JOB2'; OWNER OBJECT_NAM OBJECT_TYP STATUS ---------- ---------- ---------- ---------- SYSTEM SHUT_JOB2 TABLE VALID |
해당 expdp job이 테이블로 존재하는것을 확인 가능함
해당 테이블 삭제
1 2 3 | SQL> drop table SHUT_JOB2 purge; Table dropped. |
dba_datapump_jobs 확인
1 2 3 4 5 6 7 8 9 10 | SQL> col owner_name for a10 col job_name for a10 col operation for a10 col job_mode for a10 col state for a20 select owner_name, job_name, operation, job_mode, state from dba_datapump_jobs; no rows selected |
dba_datapump_jobs 뷰에도 더이상 datapump job이 나오지 않는것을 확인 할 수있다.
원인 : datapump expdp job이 정상적으로 종료되지 않아 발생한 오류
참조 : https://www.xtivia.com/removing-orphaned-oracle-datapump-jobs/