ORACLE 数据迁移之EXPDP整库导出导入

windjack · 发表于 2020-11-5 18:01:33

ORACLE 数据迁移之EXPDP整库导出导入

--------------------------------------------------------------------------------------------------------------

环境：

OS：centos 7.8 64bit

DB: 12.1.0.2 64bit

---------------------------------------------------------------------------------------------------------------

1、确认源库表空间

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

TBS_TTS

7 rows selected.

2.确认源库用户(注意非系统用户)

SQL> select username,DEFAULT_TABLESPACE from dba_users;

USERNAME DEFAULT_TABLESPACE

------------------------------ ------------------------------

LG TBS_TTS

OE EXAMPLE

SCOTT USERS

U1 USERS

HR1 USERS

BI USERS

U2 USERS

.....................

SQL>conn lg/oracle

SQL> create table t1 tablespace tbs_tts as select * from session_privs;

3.整库导出

如果在保证在导出期间业务数据不发生变化，尽量关闭监听，重启数据库后再执行导出。

[oracle@dbserver ~]$ expdp \' / as sysdba\' directory=exp_dir dumpfile=full.dmp full=y log=full.log

Export: Release 12.1.0.2.0 - Production on Mon Nov 2 02:08:00 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Legacy Mode Active due to the following parameters:

Legacy Mode Parameter: "log=full.log" Location: Command Line, Replaced with: "logfile=full.log"

Legacy Mode has set reuse_dumpfiles=true parameter.

FLASHBACK automatically enabled to preserve database integrity.

Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" directory=exp_dir dumpfile=full.dmp full=y logfile=full.log reuse_dumpfiles=true

Estimate in progress using BLOCKS method...

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 307.1 MB

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/PROFILE

Processing object type DATABASE_EXPORT/SYS_USER/USER

Processing object type DATABASE_EXPORT/SCHEMA/USER

Processing object type DATABASE_EXPORT/RADM_FPTM

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/SCHEMA/ON_USER_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA

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

。。。。。。。。。。。

. . exported "LBACSYS"."OLS$PROFILE" 0 KB 0 rows

. . exported "LBACSYS"."OLS$PROFILES" 0 KB 0 rows

. . exported "LBACSYS"."OLS$PROG" 0 KB 0 rows

. . exported "LBACSYS"."OLS$SESSINFO" 0 KB 0 rows

. . exported "LBACSYS"."OLS$USER" 0 KB 0 rows

. . exported "LBACSYS"."OLS$USER_COMPARTMENTS" 0 KB 0 rows

. . exported "LBACSYS"."OLS$USER_GROUPS" 0 KB 0 rows

. . exported "LBACSYS"."OLS$USER_LEVELS" 0 KB 0 rows

. . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows

. . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 8.671 KB 4 rows

. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 10.21 KB 22 rows

. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.953 KB 2 rows

. . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows

. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows

. . exported "SYS"."NACL$_ACE_EXP" 9.929 KB 1 rows

. . exported "SYS"."NACL$_HOST_EXP" 6.914 KB 1 rows

. . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows

. . exported "WMSYS"."WM$EXP_MAP" 7.718 KB 3 rows

. . exported "PM"."ONLINE_MEDIA" 7.855 MB 9 rows

. . exported "SH"."CUSTOMERS" 10.27 MB 55500 rows

. . exported "SH"."COSTS":"COSTS_Q1_1998" 139.6 KB 4411 rows

. . exported "SH"."COSTS":"COSTS_Q1_1999" 183.7 KB 5884 rows

. . exported "SH"."COSTS":"COSTS_Q1_2000" 120.7 KB 3772 rows

. . exported "SH"."COSTS":"COSTS_Q1_2001" 228.0 KB 7328 rows

. . exported "SH"."COSTS":"COSTS_Q2_1998" 79.68 KB 2397 rows

. . exported "SH"."COSTS":"COSTS_Q2_1999" 132.7 KB 4179 rows

. . exported "SH"."COSTS":"COSTS_Q2_2000" 119.1 KB 3715 rows

. . exported "SH"."COSTS":"COSTS_Q2_2001" 184.7 KB 5882 rows

. . exported "SH"."COSTS":"COSTS_Q3_1998" 131.3 KB 4129 rows

. . exported "SH"."COSTS":"COSTS_Q3_1999" 137.5 KB 4336 rows

. . exported "SH"."COSTS":"COSTS_Q3_2000" 151.6 KB 4798 rows

. . exported "SH"."COSTS":"COSTS_Q3_2001" 234.6 KB 7545 rows

. . exported "SH"."COSTS":"COSTS_Q4_1998" 144.8 KB 4577 rows

. . exported "SH"."COSTS":"COSTS_Q4_1999" 159.2 KB 5060 rows

. . exported "SH"."COSTS":"COSTS_Q4_2000" 160.4 KB 5088 rows

. . exported "SH"."COSTS":"COSTS_Q4_2001" 278.5 KB 9011 rows

. . exported "SH"."SALES":"SALES_Q1_1998" 1.413 MB 43687 rows

. . exported "SH"."SALES":"SALES_Q1_1999" 2.071 MB 64186 rows

. . exported "SH"."SALES":"SALES_Q1_2000" 2.012 MB 62197 rows

. . exported "SH"."SALES":"SALES_Q1_2001" 1.965 MB 60608 rows

. . exported "SH"."SALES":"SALES_Q2_1998" 1.160 MB 35758 rows

. . exported "SH"."SALES":"SALES_Q2_1999" 1.754 MB 54233 rows

. . exported "SH"."SALES":"SALES_Q2_2000" 1.802 MB 55515 rows

. . exported "SH"."SALES":"SALES_Q2_2001" 2.051 MB 63292 rows

. . exported "SH"."SALES":"SALES_Q3_1998" 1.634 MB 50515 rows

. . exported "SH"."SALES":"SALES_Q3_1999" 2.166 MB 67138 rows

. . exported "SH"."SALES":"SALES_Q3_2000" 1.910 MB 58950 rows

. . exported "SH"."SALES":"SALES_Q3_2001" 2.130 MB 65769 rows

. . exported "SH"."SALES":"SALES_Q4_1998" 1.581 MB 48874 rows

. . exported "SH"."SALES":"SALES_Q4_1999" 2.014 MB 62388 rows

. . exported "SH"."SALES":"SALES_Q4_2000" 1.814 MB 55984 rows

. . exported "SH"."SALES":"SALES_Q4_2001" 2.257 MB 69749 rows

. . exported "PM"."PRINT_MEDIA" 190.6 KB 4 rows

. . exported "SH"."SUPPLEMENTARY_DEMOGRAPHICS" 697.6 KB 4500 rows

. . exported "OE"."PRODUCT_DESCRIPTIONS" 2.379 MB 8640 rows

. . exported "PM"."TEXTDOCS_NESTEDTAB" 87.85 KB 12 rows

. . exported "OE"."PURCHASEORDER" 247.7 KB 132 rows

. . exported "SH"."FWEEK_PSCAT_SALES_MV" 419.9 KB 11266 rows

. . exported "SH"."PROMOTIONS" 59.17 KB 503 rows

. . exported "SH"."TIMES" 381.7 KB 1826 rows

. . exported "OE"."WAREHOUSES" 12.75 KB 9 rows

. . exported "OE"."CUSTOMERS" 81.17 KB 319 rows

. . exported "IX"."STREAMS_QUEUE_TABLE" 0 KB 0 rows

. . exported "IX"."ORDERS_QUEUETABLE" 0 KB 0 rows

. . exported "OE"."PRODUCT_INFORMATION" 73.05 KB 288 rows

. . exported "HR"."COUNTRIES" 6.460 KB 25 rows

. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows

. . exported "HR"."EMP" 9.593 KB 1 rows

. . exported "HR"."EMP1" 17.08 KB 107 rows

. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows

. . exported "HR"."JOBS" 7.109 KB 19 rows

. . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows

. . exported "HR"."LOCATIONS" 8.437 KB 23 rows

. . exported "HR"."REGIONS" 5.546 KB 4 rows

. . exported "HR"."T1" 5.070 KB 3 rows

. . exported "HR"."TEST" 5.476 KB 1 rows

. . exported "IX"."AQ$_ORDERS_QUEUETABLE_G" 0 KB 0 rows

. . exported "IX"."AQ$_ORDERS_QUEUETABLE_H" 0 KB 0 rows

. . exported "IX"."AQ$_ORDERS_QUEUETABLE_I" 0 KB 0 rows

. . exported "IX"."AQ$_ORDERS_QUEUETABLE_L" 0 KB 0 rows

. . exported "IX"."AQ$_ORDERS_QUEUETABLE_S" 11.30 KB 4 rows

. . exported "IX"."AQ$_ORDERS_QUEUETABLE_T" 0 KB 0 rows

. . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_C" 0 KB 0 rows

. . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_G" 0 KB 0 rows

. . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_H" 0 KB 0 rows

. . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_I" 0 KB 0 rows

. . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_L" 0 KB 0 rows

. . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_S" 11.57 KB 1 rows

. . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_T" 0 KB 0 rows

. . exported "LG"."T1" 5.257 KB 11 rows

. . exported "OE"."PRODUCT_REF_LIST_NESTEDTAB" 12.57 KB 288 rows

. . exported "OE"."CATEGORIES_TAB" 14.43 KB 22 rows

. . exported "OE"."SUBCATEGORY_REF_LIST_NESTEDTAB" 6.656 KB 21 rows

. . exported "OE"."INVENTORIES" 21.76 KB 1112 rows

. . exported "OE"."ORDERS" 12.59 KB 105 rows

. . exported "OE"."ORDER_ITEMS" 21.01 KB 665 rows

. . exported "OE"."PROMOTIONS" 5.570 KB 2 rows

. . exported "SCOTT"."DEPT" 6.023 KB 4 rows

. . exported "SCOTT"."EMP" 8.773 KB 14 rows

. . exported "SCOTT"."SALGRADE" 5.953 KB 5 rows

. . exported "SH"."CAL_MONTH_SALES_MV" 6.382 KB 48 rows

. . exported "SH"."CHANNELS" 7.414 KB 5 rows

. . exported "SH"."COUNTRIES" 10.46 KB 23 rows

. . exported "SH"."DIMENSION_EXCEPTIONS" 0 KB 0 rows

. . exported "SH"."PRODUCTS" 26.71 KB 72 rows

. . exported "SYSTEM"."TEMP_JAVA_PRIVS" 18.50 KB 126 rows

. . exported "U2"."EMP" 5.046 KB 1 rows

. . exported "U2"."EMP1" 5.046 KB 1 rows

. . exported "HR"."TEST1" 0 KB 0 rows

. . exported "HR1"."A" 0 KB 0 rows

. . exported "SCOTT"."BONUS" 0 KB 0 rows

. . exported "SH"."COSTS":"COSTS_1995" 0 KB 0 rows

. . exported "SH"."COSTS":"COSTS_1996" 0 KB 0 rows

. . exported "SH"."COSTS":"COSTS_H1_1997" 0 KB 0 rows

. . exported "SH"."COSTS":"COSTS_H2_1997" 0 KB 0 rows

. . exported "SH"."COSTS":"COSTS_Q1_2002" 0 KB 0 rows

. . exported "SH"."COSTS":"COSTS_Q1_2003" 0 KB 0 rows

. . exported "SH"."COSTS":"COSTS_Q2_2002" 0 KB 0 rows

. . exported "SH"."COSTS":"COSTS_Q2_2003" 0 KB 0 rows

. . exported "SH"."COSTS":"COSTS_Q3_2002" 0 KB 0 rows

. . exported "SH"."COSTS":"COSTS_Q3_2003" 0 KB 0 rows

. . exported "SH"."COSTS":"COSTS_Q4_2002" 0 KB 0 rows

. . exported "SH"."COSTS":"COSTS_Q4_2003" 0 KB 0 rows

. . exported "SH"."SALES":"SALES_1995" 0 KB 0 rows

. . exported "SH"."SALES":"SALES_1996" 0 KB 0 rows

. . exported "SH"."SALES":"SALES_H1_1997" 0 KB 0 rows

. . exported "SH"."SALES":"SALES_H2_1997" 0 KB 0 rows

. . exported "SH"."SALES":"SALES_Q1_2002" 0 KB 0 rows

. . exported "SH"."SALES":"SALES_Q1_2003" 0 KB 0 rows

. . exported "SH"."SALES":"SALES_Q2_2002" 0 KB 0 rows

. . exported "SH"."SALES":"SALES_Q2_2003" 0 KB 0 rows

. . exported "SH"."SALES":"SALES_Q3_2002" 0 KB 0 rows

. . exported "SH"."SALES":"SALES_Q3_2003" 0 KB 0 rows

. . exported "SH"."SALES":"SALES_Q4_2002" 0 KB 0 rows

. . exported "SH"."SALES":"SALES_Q4_2003" 0 KB 0 rows

Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_FULL_01 is:

/home/oracle/full.dmp

Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Mon Nov 2 01:57:28 2020 elapsed 0 00:03:38

4.目标库导入前确认

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

6 rows selected.

SQL> select username from dba_users;

USERNAME

--------------------------------------------------------------------------------

OE

SCOTT

ORACLE_OCM

OJVMSYS

SYSKM

XS$NULL

BI

PM

GSMCATUSER

MDDATA

SYSBACKUP

IX

SH

DIP

SYSDG

APEX_PUBLIC_USER

HR

SPATIAL_CSW_ADMIN_USR

SPATIAL_WFS_ADMIN_USR

GSMUSER

AUDSYS

FLOWS_FILES

DVF

MDSYS

ORDSYS

DBSNMP

WMSYS

APEX_040200

APPQOSSYS

GSMADMIN_INTERNAL

ORDDATA

CTXSYS

ANONYMOUS

XDB

ORDPLUGINS

DVSYS

SI_INFORMTN_SCHEMA

OLAPSYS

LBACSYS

OUTLN

SYSTEM

SYS

42 rows selected.

5.目标库导入

[oracle@dbserver ~]$ impdp \' / as sysdba \' directory=imp_dir dumpfile=full.dmp full=y log=full.log

Import: Release 12.1.0.2.0 - Production on Mon Nov 2 02:05:42 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Legacy Mode Active due to the following parameters:

Legacy Mode Parameter: "log=full.log" Location: Command Line, Replaced with: "logfile=full.log"

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=imp_dir dumpfile=full.dmp full=y logfile=full.log

Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/TABLESPACE

ORA-31684: Object type TABLESPACE:"EXAMPLE" already exists

ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists

ORA-31684: Object type TABLESPACE:"TEMP" already exists

ORA-31684: Object type TABLESPACE:"USERS" already exists

ORA-31684: Object type TABLESPACE:"TBS_TTS" already exists

Processing object type DATABASE_EXPORT/PROFILE

Processing object type DATABASE_EXPORT/SYS_USER/USER

Processing object type DATABASE_EXPORT/SCHEMA/USER

ORA-31684: Object type USER:"HR" already exists

ORA-31684: Object type USER:"IX" already exists

ORA-31684: Object type USER:"SH" already exists

ORA-31684: Object type USER:"PM" already exists

ORA-31684: Object type USER:"BI" already exists

ORA-31684: Object type USER:"OE" already exists

ORA-31684: Object type USER:"SCOTT" already exists

ORA-31684: Object type USER:"HR1" already exists

ORA-31684: Object type USER:"U2" already exists

ORA-31684: Object type USER:"U1" already exists

Processing object type DATABASE_EXPORT/RADM_FPTM

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT

ORA-39083: Object type PROC_SYSTEM_GRANT failed to create with error:

ORA-00955: name is already used by an existing object

。。。。。。。。。。。

Failing sql is:

BEGIN

dbms_scheduler.create_job('"ORACLE_APEX_WS_NOTIFICATIONS"',

job_type=>'STORED_PROCEDURE', job_action=>

'WWV_FLOW_WORKSHEET_API.DO_NOTIFY'

, number_of_arguments=>0,

start_date=>TO_TIMESTAMP_TZ('07-JUL-2014 06.29.55.281735000 AM -07:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval

ORA-39083: Object type PROCOBJ:"APEX_040200"."ORACLE_APEX_DAILY_MAINTENANCE" failed to create with error:

ORA-27477: "APEX_040200"."ORACLE_APEX_DAILY_MAINTENANCE" already exists

Failing sql is:

BEGIN

dbms_scheduler.create_job('"ORACLE_APEX_DAILY_MAINTENANCE"',

job_type=>'STORED_PROCEDURE', job_action=>

'WWV_FLOW_MAINT.DAILY_MAINTENANCE'

, number_of_arguments=>0,

start_date=>TO_TIMESTAMP_TZ('07-JUL-2014 06.29.55.284760000 AM -07:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_inter

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE

Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER

Job "SYS"."SYS_IMPORT_FULL_01" completed with 208 error(s) at Mon Nov 2 02:08:20 2020 elapsed 0 00:02:36

6.验证数据

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

TBS_TTS

7 rows selected.

SQL> select * from lg.t1;

PRIVILEGE

----------------------------------------

SET CONTAINER

CREATE INDEXTYPE

CREATE OPERATOR

CREATE TYPE

CREATE TRIGGER

CREATE PROCEDURE

CREATE SEQUENCE

CREATE CLUSTER

CREATE TABLE

UNLIMITED TABLESPACE

CREATE SESSION

11 rows selected.

相关的新表空间和新用户自动创建了，是因为目标库和源库OS和文件路径都一样。如果不一样，需要先在目标库上创建相关的表空间。

------------------------------------------------------------------------------------------------------------------------

相关错误：

1.READ ONLY 只读状态下导出失败

[oracle@dbserver ~]$ expdp \' / as sysdba\' directory=exp_dir dumpfile=full.dmp full=y

Export: Release 12.1.0.2.0 - Production on Mon Nov 2 01:50:40 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

ORA-31626: job does not exist

ORA-31633: unable to create master table "SYS.SYS_EXPORT_FULL_05"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT", line 1048

ORA-16000: database or pluggable database open for read-only access

解决办法：以read write 方式打开数据库

2.导出报错ORA-39070: Unable to open the log file.

[oracle@dbserver ~]$ expdp \' / as sysdba\' directory=exp_dir dumpfile=full.dmp full=y log=full.log

Export: Release 12.1.0.2.0 - Production on Mon Nov 2 01:52:04 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation

ORA-06512: at "SYS.UTL_FILE", line 536

ORA-29283: invalid file operation

解决办法：

sqlplus> create or replace directory exp_dir as '/home/oracle';

-----------------------------------------------------------------------------------------------------------------------

注意：

如果是基于用户（schema模式）导出导入时，在目标库必须先创建该用户在源库一样的表空间，否则导入失败。

当然选择使用REMAP进行相关对象的转换也是可以的。

ORACLE的REMAP包含：remap_tablespace,remap_schema,remap_table,remap_data等，使用方法参照官方文档。

--THE END.

帐号		自动登录	找回密码
密码			注册

[Oracle] ORACLE 数据迁移之EXPDP整库导出导入