DataPump를 이용한 간단한 마이그레이션 절차입니다.
1. schema level export
# expdp \'/ as sysdba\' directory=mig dumpfile=scott.dmp log=scott_export.log schemas=scott
Export: Release 11.2.0.3.0 - Production on Wed Jan 21 18:24:16 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=scott_export.log" Location: Command Line, Replaced with: "logfile=scott_export.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=mig dumpfile=scott.dmp logfile=scott_export.log schemas=scott reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
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
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."TEST_NLS" 5.320 KB 1 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP64" 6.710 KB 1 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP67" 6.734 KB 2 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP72" 6.710 KB 1 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP76" 6.710 KB 1 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP61" 0 KB 0 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP62" 0 KB 0 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP63" 0 KB 0 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP65" 0 KB 0 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP66" 0 KB 0 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP68" 0 KB 0 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP69" 0 KB 0 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP70" 0 KB 0 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP71" 0 KB 0 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP73" 0 KB 0 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP74" 0 KB 0 rows
. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP75" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/pump/scott.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:24:32
2. ddl 추출
# impdp \'/ as sysdba\' directory=mig dumpfile=scott.dmp sqlfile=cr_scott.sql
Import: Release 11.2.0.3.0 - Production on Wed Jan 21 19:27:32 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01": "/******** AS SYSDBA" directory=mig dumpfile=scott.dmp sqlfile=cr_scott.sql
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
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 19:27:36
3. DDL 수행
SQL> @cr_scott
4. import
# impdp \'/ as sysdba\' directory=mig dumpfile=scott.dmp logfile=scott_imp.log ignore=y
Import: Release 11.2.0.3.0 - Production on Wed Jan 21 19:29:22 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "ignore=TRUE" Location: Command Line, Replaced with: "table_exists_action=append"
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=mig dumpfile=scott.dmp logfile=scott_imp.log table_exists_action=append
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
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
Table "SCOTT"."SALES_RANGE_HASH" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "SCOTT"."TEST_NLS" 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 "SCOTT"."TEST_NLS" 5.320 KB 1 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP76" 6.710 KB 1 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP61" 0 KB 0 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP62" 0 KB 0 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP63" 0 KB 0 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP65" 0 KB 0 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP66" 0 KB 0 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP68" 0 KB 0 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP69" 0 KB 0 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP70" 0 KB 0 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP71" 0 KB 0 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP73" 0 KB 0 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP74" 0 KB 0 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP75" 0 KB 0 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP64" 6.710 KB 1 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP67" 6.734 KB 2 rows
. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP72" 6.710 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 19:29:28
'Oracle Database > Oracle Tool & Util' 카테고리의 다른 글
| SQL Developer 4.1을 이용해 Excel data를 Oracle database로 넣기 (0) | 2015.01.15 |
|---|---|
| SQL Developer 4.1의 instance monitoring 기능 (0) | 2015.01.15 |
| oracle - logminer (0) | 2010.09.17 |