내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : 오라클 11g R2 datapump 이용 db full 백업
데이터 펌프용 디렉토리 생성
1 2 3 4 5 6 7 | SQL> create directory expdpdir as '/home/oracle/pump/'; Directory created. SQL> grant read, write on directory expdpdir to public; Grant succeeded. |
데이터펌프 expdp 실행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 | $ expdp system/oracle directory=expdpdir full=y Export: Release 11.2.0.4.0 - Production on Fri Jan 29 01:00:32 2021 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, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/***** directory=expdpdir full=y Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 320 KB Processing object type DATABASE_EXPORT/TABLESPACE Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type DATABASE_EXPORT/SCHEMA/USER Processing object type DATABASE_EXPORT/ROLE Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE Processing object type DATABASE_EXPORT/RESOURCE_COST Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/CONTEXT Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/AUDIT . . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE" 6.335 KB 2 rows . . exported "SYSTEM"."REPCAT$_OBJECT_TYPES" 6.890 KB 28 rows . . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD" 5.843 KB 19 rows . . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS" 5.492 KB 3 rows . . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES" 6.296 KB 2 rows . . exported "OUTLN"."OL$" 0 KB 0 rows . . exported "OUTLN"."OL$HINTS" 0 KB 0 rows . . exported "OUTLN"."OL$NODES" 0 KB 0 rows . . exported "SYSTEM"."DEF$_AQCALL" 0 KB 0 rows . . exported "SYSTEM"."DEF$_AQERROR" 0 KB 0 rows . . exported "SYSTEM"."DEF$_CALLDEST" 0 KB 0 rows . . exported "SYSTEM"."DEF$_DEFAULTDEST" 0 KB 0 rows . . exported "SYSTEM"."DEF$_DESTINATION" 0 KB 0 rows . . exported "SYSTEM"."DEF$_ERROR" 0 KB 0 rows . . exported "SYSTEM"."DEF$_LOB" 0 KB 0 rows . . exported "SYSTEM"."DEF$_ORIGIN" 0 KB 0 rows . . exported "SYSTEM"."DEF$_PROPAGATOR" 0 KB 0 rows . . exported "SYSTEM"."DEF$_PUSHED_TRANSACTIONS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_AUDIT_COLUMN" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_COLUMN_GROUP" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_CONFLICT" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_DDL" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_EXCEPTIONS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_EXTENSION" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_FLAVORS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_FLAVOR_OBJECTS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_GENERATED" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_GROUPED_COLUMN" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_INSTANTIATION_DDL" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_KEY_COLUMNS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_OBJECT_PARMS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_PARAMETER_COLUMN" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_PRIORITY" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_PRIORITY_GROUP" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REFRESH_TEMPLATES" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REPCAT" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REPCATLOG" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REPCOLUMN" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REPGROUP_PRIVS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REPOBJECT" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REPPROP" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REPSCHEMA" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_RESOLUTION" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_SITES_NEW" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_SITE_OBJECTS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_SNAPGROUP" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is: /home/oracle/pump/expdat.dmp Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Fri Jan 29 01:00:49 2021 elapsed 0 00:00:15 |
파일 확인
1 2 3 4 5 6 7 | $ cd /home/oracle/pump/ $ ls -al total 2568 drwxr-xr-x 2 oracle dba 4096 Jan 29 01:00 . drwx------. 12 oracle dba 4096 Jan 29 00:57 .. -rw-r----- 1 oracle dba 2609152 Jan 29 01:00 expdat.dmp -rw-r--r-- 1 oracle dba 8293 Jan 29 01:00 export.log |
덤프파일 및 로그 파일, job 이름 설정
1 | $ expdp system/oracle directory=expdpdir full=y dumpfile=ordlfull.dmp logfile=orclfull.log job_name=job_orcl_full |
expdp 작업 중 컨트롤(Crtl)+c 입력시 job이 끊기지 않고 상태를 볼수있는 상태가 됨
상태확인 : status
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | $ expdp system/oracle directory=expdpdir full=y dumpfile=ordlfull.dmp logfile=orclfull.log job_name=job_orcl_full Export: Release 11.2.0.4.0 - Production on Fri Jan 29 01:07:10 2021 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, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."JOB_ORCL_FULL": system/ directory=expdpdir full=y dumpfile=ordlfull.dmp logfile=orclfull.log job_name=job_orcl_full Estimate in progress using BLOCKS method... ^C <-- [컨트롤+c 입력] Export> status <-- [status 입력] Job: JOB_ORCL_FULL Operation: EXPORT Mode: FULL State: EXECUTING Bytes Processed: 0 Current Parallelism: 1 Job Error Count: 0 Dump File: /home/oracle/pump/ordlfull.dmp bytes written: 4,096 Worker 1 Status: Process Name: DW00 State: EXECUTING Object Type: DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM Completed Objects: 4 Total Objects: 4 Worker Parallelism: 1 |
작업 중지 : stop_job
1 2 3 4 5 6 7 8 9 10 11 12 13 | $ expdp system/oracle directory=expdpdir full=y dumpfile=ordlfull.dmp logfile=orclfull.log job_name=job_orcl_full Export: Release 11.2.0.4.0 - Production on Fri Jan 29 01:07:10 2021 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, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."JOB_ORCL_FULL": system/***** directory=expdpdir full=y dumpfile=ordlfull.dmp logfile=orclfull.log job_name=job_orcl_full Estimate in progress using BLOCKS method... ^C <-- [컨트롤+c 입력] Export> stop_job <-- [stop_job 입력] Are you sure you wish to stop this job ([yes]/no): yes |
중지 시 job 상태 확인
1 2 3 4 5 6 7 8 9 10 11 | SQL> col OWNER_NAME for a10 col JOB_NAME for a16 col OPERATION for a10 col JOB_MODE for a10 col STATE for a20 select * from dba_datapump_jobs; OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS ---------- ---------------- ---------- ---------- -------------------- ---------- ----------------- ----------------- SYSTEM JOB_ORCL_FULL EXPORT FULL NOT RUNNING 0 0 0 |
state가 NOT RUNNING 상태로 표시됨
해당 job 에 다시 접속(attach) : attach=잡 이름
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | $ expdp system/oracle attach=job_orcl_full Export: Release 11.2.0.4.0 - Production on Fri Jan 29 01:11:30 2021 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, OLAP, Data Mining and Real Application Testing options Job: JOB_ORCL_FULL Owner: SYSTEM Operation: EXPORT Creator Privs: TRUE GUID: B9F9147F4362056EE0531E00A8C02E40 Start Time: Friday, 29 January, 2021 1:11:30 Mode: FULL Instance: ORCL11 Max Parallelism: 1 EXPORT Job Parameters: Parameter Name Parameter Value: CLIENT_COMMAND system/***** directory=expdpdir full=y dumpfile=ordlfull.dmp logfile=orclfull.log job_name=job_orcl_full State: IDLING Bytes Processed: 31,600 Percent Done: 99 Current Parallelism: 1 Job Error Count: 0 Dump File: /home/oracle/pump/ordlfull.dmp bytes written: 589,824 Worker 1 Status: Process Name: DW00 State: UNDEFINED |
job 상태 재확인
1 2 3 4 5 6 7 8 9 10 11 | SQL> col OWNER_NAME for a10 col JOB_NAME for a16 col OPERATION for a10 col JOB_MODE for a10 col STATE for a20 select * from dba_datapump_jobs; OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS ---------- ---------------- ---------- ---------- -------------------- ---------- ----------------- ----------------- SYSTEM JOB_ORCL_FULL EXPORT FULL IDLING 1 0 1 |
state가 IDLING로 표시됨
job 재실행 후 상태 확인 : start_job, status
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | $ expdp system/oracle attach=job_orcl_full . . Export> start_job <-- [start_job 입력] Export> satus <-- [status 입력] Job: JOB_ORCL_FULL Operation: EXPORT Mode: FULL State: EXECUTING Bytes Processed: 31,600 Percent Done: 9 Current Parallelism: 1 Job Error Count: 0 Dump File: /home/oracle/pump/ordlfull.dmp bytes written: 593,920 Worker 1 Status: Process Name: DW00 State: EXECUTING |
datapump job 로그파일 확인
1 2 3 4 5 6 7 8 9 10 11 12 | $ cd /home/oracle/pump/ $ vi orclfull.log Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Job "SYSTEM"."JOB_ORCL_FULL" stopped due to fatal error at Fri Jan 29 01:14:00 2021 elapsed 0 00:02:30 Job JOB_ORCL_FULL has been reopened at Fri Jan 29 01:14:19 2021 Restarting "SYSTEM"."JOB_ORCL_FULL": system/***** directory=expdpdir full=y dumpfile=ordlfull.dmp logfile=orclfull.log job_name=job_orcl_full Master table "SYSTEM"."JOB_ORCL_FULL" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.JOB_ORCL_FULL is: /home/oracle/pump/ordlfull.dmp Job "SYSTEM"."JOB_ORCL_FULL" successfully completed at Fri Jan 29 01:14:24 2021 elapsed 0 00:00:05 |
로그 파일 확인시 stop_job 기록과 start_job에 대한 기록이 남아있음
참조 :
https://blog.goodusdata.com/61
'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 11g R2 엔진 백업 복구 테스트(windows) (0) | 2021.02.06 |
|---|---|
| 오라클 DDL(drop 등)도 로그마이너로 복구가 될까? (0) | 2021.01.31 |
| 오라클 19c rman incarnation 설명 및 복구 시나리오 (0) | 2021.01.21 |
| 오라클 19c rman catalog db 이용 백업 복구 (0) | 2021.01.21 |
| 오라클 11g R2 ADG Gap 발생 시나리오1 (0) | 2021.01.18 |