본문 바로가기
ORACLE/ORACLE_Admin

impdp/expdp

minies 2023. 5. 1. 22:24
반응형

## datapump

  • 오라클에서 백업을 하거나 데이터를 이동할 때 사용하는 유틸리티
  • DDL과 DML 문장으로 백업된다
  • import와 export를 보완해서 나온 기능 (속도, 중간에 작업이 끊기면 다시 시작)

 

※ RMAN 과의 차이는 RMAN은 물리적 백업, Datapump는 논리적 백업

 

 

 

### expdp 옵션

expdp username/password(생략)
[schemas, tablespaces, tables, full 중 택 1]
job_name = 작업 이름 -> 중간에 작업이 중단됐을 경우 사용 할 이름
directory = 파일을 저장할 위치
dumpfile = 저장할 이름
logfile = 로그를 따로 추출하고 싶을 경우 사용
parallel = 병렬 작업을 하고 싶을 경우 사용
content = [all, data_only, metadata_only]
encryption = 암호화시 필요
encryption_password = 암호화시 필요

 

### impdp 옵션

impdp username/password(생략)
table_exist_action = [skip, append, drp, truncate] 
remap_schema = damin:damin2 -- damin에서 damin2로 변경
remap_datafile = '/home/oracle/datapump':'/home/oracle/datapump2' -- d이런식으로 경로변경
remap_tablespace = users:users2 -- users에서 users2로변경

 

 

 

 

## expdp 실습

 

1. export 할 디렉토리 생성

mkdir -p /home/oracle/datapump
create directory prod_dir as '/home/oracle/datapump';

 

 

2. 권한부여 

grant write,read on directory prod_dir to scott, damin;

 

3. 추출

[/home/oracle]$ expdp damin/ schemas=damin directory=prod_dir job_name=example1 dumpfile=scml.dmp

Export: Release 19.0.0.0.0 - Production on Wed Apr 19 20:19:22 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "DAMIN"."EXAMPLE1":  damin/******** schemas=damin directory=prod_dir job_name=example1 dumpfile=scml.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "DAMIN"."DEPARTMENT"                        6.851 KB      12 rows
. . exported "DAMIN"."PROFESSOR"                         10.23 KB      16 rows
. . exported "DAMIN"."STUDENT"                           11.50 KB      20 rows
Master table "DAMIN"."EXAMPLE1" successfully loaded/unloaded
******************************************************************************
Dump file set for DAMIN.EXAMPLE1 is:
  /home/oracle/datapump/scml.dmp
Job "DAMIN"."EXAMPLE1" successfully completed at Wed Apr 19 20:20:11 2023 elapsed 0 00:00:46

 

 

4. 확인

[/home/oracle/datapump]$ ls
damin.dmp  export.log  impdpDAMIN_PROFESSOR.log  import.log  scml.dmp

 

 

## full expdp 중단 후 다시 실행

1. full expdp

[/home/oracle/datapump]$ expdp system/oracle full=y directory=prod_dir job_name=example2 parallel=3 dumpfile=full.dmp

 

 

2. 중단 (ctrl+c)

Export: Release 19.0.0.0.0 - Production on Wed Apr 19 20:32:25 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Produc                                                                                                                                   tion
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."EXAMPLE2":  system/******** full=y directory=prod_dir job_nam                                                                                                                                   e=example2 parallel=3 dumpfile=full.dmp
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATI                                                                                                                                   STICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
^C

 

 

3. 상태확인 후 stop

Export> status

Job: EXAMPLE2
  Operation: EXPORT
  Mode: FULL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 3
  Job Error Count: 0
  Job heartbeat: 1
  Dump File: /home/oracle/datapump/full.dmp
    bytes written: 69,632

Worker 1 Status:
  Instance ID: 1
  Instance name: ORA19C
  Host name: HORA19C
  Object start time: Wednesday, 19 April, 2023 20:32:35
  Object status at: Wednesday, 19 April, 2023 20:32:36
  Process Name: DW00
  State: EXECUTING

Worker 2 Status:
  Instance ID: 1
  Instance name: ORA19C
  Host name: HORA19C
  Object start time: Wednesday, 19 April, 2023 20:32:38
  Object status at: Wednesday, 19 April, 2023 20:32:38
  Process Name: DW01
  State: EXECUTING

Worker 3 Status:
  Instance ID: 1
  Instance name: ORA19C
  Host name: HORA19C
  Object start time: Wednesday, 19 April, 2023 20:32:38
  Object status at: Wednesday, 19 April, 2023 20:32:38
  Process Name: DW02
  State: EXECUTING

Export> stop
Are you sure you wish to stop this job ([yes]/no): yes

 

 

4. 재시작 (start_job)

- 백그라운드 프로세스로 진행되기 때문에 콘솔에 따로 뜨지 않음

[/home/oracle/datapump]$ expdp system/oracle attach=system.example2

Export: Release 19.0.0.0.0 - Production on Wed Apr 19 20:33:29 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Produc                                                                                                                                   tion

Job: EXAMPLE2
  Owner: SYSTEM
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: F9AFC7441DCD1243E0530F00A8C0BC08
  Start Time: Wednesday, 19 April, 2023 20:33:31
  Mode: FULL
  Instance: ORA19C
  Max Parallelism: 3
  Timezone: +00:00
  Timezone version: 32
  Endianness: LITTLE
  NLS character set: AL32UTF8
  NLS NCHAR character set: AL16UTF16
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** full=y directory=prod_dir job_name=ex                                                                                                                                   ample2 parallel=3 dumpfile=full.dmp
     TRACE                 0
  State: IDLING
  Bytes Processed: 0
  Current Parallelism: 3
  Job Error Count: 0
  Job heartbeat: 4
  Dump File: /home/oracle/datapump/full.dmp
    bytes written: 90,112

Worker 1 Status:
  Instance ID: 1
  Instance name: ORA19C
  Host name: HORA19C
  Object start time: Wednesday, 19 April, 2023 20:32:36
  Object status at: Wednesday, 19 April, 2023 20:32:48
  Process Name: DW00
  State: UNDEFINED
  Object Schema: SYS
  Object Type: DATABASE_EXPORT/STATISTICS/MARKER
  Worker Parallelism: 1

Worker 2 Status:
  Instance ID: 1
  Instance name: ORA19C
  Host name: HORA19C
  Object start time: Wednesday, 19 April, 2023 20:32:38
  Object status at: Wednesday, 19 April, 2023 20:33:00
  Process Name: DW01
  State: UNDEFINED

Worker 3 Status:
  Instance ID: 1
  Instance name: ORA19C
  Host name: HORA19C
  Object start time: Wednesday, 19 April, 2023 20:32:44
  Object status at: Wednesday, 19 April, 2023 20:32:59
  Process Name: DW02
  State: UNDEFINED
Export> start_job

Export> status

Job: EXAMPLE2
  Operation: EXPORT
  Mode: FULL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 3
  Job Error Count: 0
  Job heartbeat: 1
  Dump File: /home/oracle/datapump/full.dmp
    bytes written: 118,784

Worker 1 Status:
  Instance ID: 1
  Instance name: ORA19C
  Host name: HORA19C
  Access method: direct_path
  Object start time: Wednesday, 19 April, 2023 20:34:02
  Object status at: Wednesday, 19 April, 2023 20:34:03
  Process Name: DW00
  State: EXECUTING

Worker 2 Status:
  Instance ID: 1
  Instance name: ORA19C
  Host name: HORA19C
  Object start time: Wednesday, 19 April, 2023 20:33:45
  Object status at: Wednesday, 19 April, 2023 20:33:52
  Process Name: DW01
  State: EXECUTING

Worker 3 Status:
  Instance ID: 1
  Instance name: ORA19C
  Host name: HORA19C
  Object start time: Wednesday, 19 April, 2023 20:34:02
  Object status at: Wednesday, 19 April, 2023 20:34:08
  Process Name: DW02
  State: EXECUTING
  Object Schema: SYS
  Object Name: DBA_SENSITIVE_DATA
  Object Type: DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
  Completed Objects: 1
  Worker Parallelism: 1

Export> status

Job: EXAMPLE2
  Operation: EXPORT
  Mode: FULL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 3
  Job Error Count: 0
  Job heartbeat: 1
  Dump File: /home/oracle/datapump/full.dmp
    bytes written: 131,072

Worker 1 Status:
  Instance ID: 1
  Instance name: ORA19C
  Host name: HORA19C
  Object start time: Wednesday, 19 April, 2023 20:34:03
  Object status at: Wednesday, 19 April, 2023 20:34:59
  Process Name: DW00
  State: EXECUTING

Worker 2 Status:
  Instance ID: 1
  Instance name: ORA19C
  Host name: HORA19C
  Object start time: Wednesday, 19 April, 2023 20:33:52
  Object status at: Wednesday, 19 April, 2023 20:35:00
  Process Name: DW01
  State: EXECUTING
  Object Schema: SYS
  Object Name: AUD$
  Object Type: DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
  Completed Objects: 60
  Worker Parallelism: 1

Worker 3 Status:
  Instance ID: 1
  Instance name: ORA19C
  Host name: HORA19C
  Object start time: Wednesday, 19 April, 2023 20:34:56
  Object status at: Wednesday, 19 April, 2023 20:35:00
  Process Name: DW02
  State: EXECUTING

Export>

 

 

5. 확인

[/home/oracle/datapump]$ ls
damin.dmp  export.log  full.dmp  impdpDAMIN_PROFESSOR.log  import.log  scml.dmp
[/home/oracle/datapump]$

 

 

 

## impdp 실습

1. 새로운 유저(damin2) 생성

 

 

 

 

2. impdp (메타데이터만)

→ 여기서 메타데이터뿐만 아니라 모든 데이터를 채워넣으려면 content=all 로 변경

[/home/oracle/datapump]$ impdp system/ dumpfile=scml.dmp content=metadata_only directory=prod_dir remap_schema=damin:damin2

Import: Release 19.0.0.0.0 - Production on Wed Apr 19 20:48:45 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=scml.dmp content=metadata_only directory=prod_dir remap_schema=damin:damin2
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Apr 19 20:49:09 2023 elapsed 0 00:00:22

 

 

3. impdp 로 존재하는 테이블에 데이터 채워넣기

[/home/oracle/datapump]$ impdp system/ dumpfile=scml.dmp directory=prod_dir remap_schema=damin:damin2 table_exists_action=append

Import: Release 19.0.0.0.0 - Production on Wed Apr 19 20:49:59 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=scml.dmp directory=prod_dir remap_schema=damin:damin2 table_exists_action=append
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "DAMIN2"."DEPARTMENT" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "DAMIN2"."PROFESSOR" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "DAMIN2"."STUDENT" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DAMIN2"."DEPARTMENT"                       6.851 KB      12 rows
. . imported "DAMIN2"."PROFESSOR"                        10.23 KB      16 rows
. . imported "DAMIN2"."STUDENT"                          11.50 KB      20 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Apr 19 20:50:09 2023 elapsed 0 00:00:08

 

 

4. 확인

 

[/home/oracle/datapump]$ sqlplus damin2/

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 19 20:50:32 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Wed Apr 19 2023 20:45:07 +09:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
DEPARTMENT
PROFESSOR
STUDENT
반응형
저작자표시

'ORACLE > ORACLE_Admin' 카테고리의 다른 글

Oracle 패스워드 관리  (0) 2023.08.06
export / import  (1) 2023.05.14
Shared Server 설정  (0) 2023.04.24
alert log 확인 (단계별 기동/중지, SMON kill)  (0) 2023.04.18
ORACLE Controlfile  (0) 2023.04.17

'ORACLE/ORACLE_Admin' Related Articles

티스토리툴바