使用expdp/impdp的最小要求

翻译

sugar黑黑 2022-04-08 16:43:37 博主文章分类：EXPDP/IMPDP

文章标签 oracle ORA-31631 ORA-39109 数据导入导出数据泵 文章分类 Oracle 数据库

最近因为业务需要，在不同版本的库之间导出导入数据，又用起了许久不用的impdp和expdp

1、最小需求

系统权限：create session、create table

对象权限：读写DIRECTORY 权限

足够的表空间限额

对于10g和11.1：

EXP_FULL_DATABASE

- to run a full database Export DataPump job or

- to run a transport_tablespace Export DataPump job or

- to run an Export DataPump job with the TRACE parameter or

- to run an Export DataPump job that exports a different schema.

IMP_FULL_DATABASE

- to run a full database Import DataPump job or

- to run a transport_datafiles Import DataPump job or

- to run an Import DataPump job with the TRACE parameter or

- to run an Import DataPump job that imports a different schema.

对于11.2及更高的版本：

DATAPUMP_EXP_FULL_DATABASE

- to run a full database Export DataPump job or

- to run a transport_tablespace Export DataPump job or

- to run an Export DataPump job with the TRACE parameter or

- to run an Export DataPump job that exports a different schema.

DATAPUMP_IMP_FULL_DATABASE

- to run a full database Import DataPump job or

- to run a transport_datafiles Import DataPump job or

- to run an Import DataPump job with the TRACE parameter or

- to run an Import DataPump job that imports a different schema.

需要注意的是，这些需求是针对用于运行expdp及impdp连接到数据库的用户。

2、如何授权

权限可以显式授予，也可以通过角色授予。

CREATE DIRECTORY <DIRECTORY_NAME> AS '<full_pre_existing_directory_path_here>';
GRANT create session, create table TO <USERNAME> IDENTIFIED BY <PASSWORD>;
GRANT read, write ON DIRECTORY <DIRECTORY_NAME> TO <USERNAME>;
ALTER USER <USERNAME> DEFAULT TABLESPACE <TABLESPACE_NAME>;
ALTER USER <USERNAME> QUOTA unlimited ON <TABLESPACE_NAME>;

-- or:

CONNECT system/manager
CREATE DIRECTORY <DIRECTORY_NAME> AS '<full_pre_existing_directory_path_here>';
CREATE ROLE <ROLE_NAME>;
GRANT create session, create table TO <ROLE_NAME>;
GRANT read, write ON DIRECTORY <DIRECTORY_NAME> TO <ROLE_NAME>;
GRANT <ROLE_NAME> TO <USERNAME>;
ALTER USER <USERNAME> DEFAULT ROLE all;
ALTER USER <USERNAME> DEFAULT TABLESPACE <TABLESPACE_NAME>;
ALTER USER <USERNAME> QUOTA unlimited ON <TABLESPACE_NAME>;

3、特权用户

是拥有EXP_FULL_DATABASE/IMP_FULL_DATABASE 角色的用户或者有dba角色的用户。

SET lines 80 
COL grantee for a30
COL granted_role FOR a40
COL default_role for a5
SELECT grantee, granted_role, default_role 
FROM dba_role_privs 
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE') 
ORDER BY 1,2;

使用expdp/impdp的最小要求_ORA-39109

4、可能出现的错误

4.1 会话相关

UDE-00008: operation generated ORACLE error 1045
ORA-01045: user <USERNAME> lacks CREATE SESSION privilege; logon denied

解决方法：

GRANT create session TO <USERNAME>;
-- or:
GRANT create session TO <ROLE_NAME>;

4.2、Master table related.

ORA-31626: job does not exist
ORA-31633: unable to create master table "<SCHEMA_NAME>.SYS_EXPORT_SCHEMA_01"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01031: insufficient privileges

解决方法：

GRANT create table TO <USERNAME>;
-- or:
GRANT create table TO <ROLE_NAME>;

4.3、目录相关

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name <DIRECTORY_NAME> is invalid

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39087: directory name <DIRECTORY_NAME> is invalid

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-31631: privileges are required

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31631: privileges are required

解决方法：

GRANT read, write ON DIRECTORY <DIRECTORY_NAME> TO <USERNAME>;
-- or:
GRANT read, write ON DIRECTORY <DIRECTORY_NAME> TO <ROLE_NAME>;

4.4、表空间配额相关

ORA-31626: job does not exist
ORA-31633: unable to create master table "<SCHEMA_NAME>.SYS_EXPORT_SCHEMA_01"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01536: space quota exceeded for tablespace '<TABLESPACE_NAME>'

ORA-31626: job does not exist
ORA-31633: unable to create master table "<SCHEMA_NAME>.SYS_EXPORT_SCHEMA_01"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01950: no privileges on tablespace '<TABLESPACE_NAME>'

解决方法：

ALTER USER <USERNAME> QUOTA unlimited ON <TABLESPACE_NAME>; 
-- or: 
ALTER USER <USERNAME> DEFAULT TABLESPACE <TABLESPACE_NAME>;

4.5、用户相关

> expdp <USERNAME>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> DUMPFILE=<DUMP_NAME>.dmp \
LOGFILE=<LOG_NAME>.log TRANSPORT_TABLESPACES=<TABLESPACE_NAME>

Export: Release 10.2.0.3.0 - Production on Monday, 30 July, 2007 10:03:59
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-31631: privileges are required
ORA-39162: Transportable tablespace job require privileges

> expdp<USERNAME>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> DUMPFILE=<DUMP_NAME>.dmp \
LOGFILE=<LOG_NAME>.log TABLES=<SCHEMA_NAM>.<TABLE_NAME>

Export: Release 10.2.0.3.0 - Production on Monday, 30 July, 2007 11:51:25
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-31631: privileges are required
ORA-39109: Unprivileged users may not operate upon other users' schemas

解决方法：

GRANT exp_full_database TO <USERNAME>;
-- or:
GRANT dba TO <ROLE_NAME>;

4.6、dblink相关

impdp <USERNAME>/<PASSWORD> NOLOGFILE=y NETWORK_LINK=<DB_LINK_NAME> \
SCHEMAS=<SCHEMA_NAME> REMAP_SCHEMA=<SOURCE_SCHEMA>:<TARGET_SCHEMA>

Import: Release 10.2.0.3.0 - Production on Thursday, 23 August, 2007 11:49:30
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user

解决方法：

-- in remote database:
GRANT exp_full_database TO <USERNAME>;
-- or in remote database:
GRANT dba TO <ROLE_NAME>;

-- in local database:
CONNECT <USERNAME>/<PASSWORD>
CREATE DATABASE LINK <DB_LINK_NAME> 
   CONNECT TO system IDENTIFIED BY <PASSWORD>
   USING '<HOST_NAME>';

> impdp <USERNAME>/<PASSWORD> NOLOGFILE=y NETWORK_LINK=<DB_LINK_NAME> SCHEMAS=<SCHEMA_NAME> REMAP_SCHEMA=<SOURCE_SCHEMA>:<TARGET_SCHEMA>

4.7、trace文件相关

> expdp <USERNAME>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> DUMPFILE=<DUMP_NAME>.dmp \
LOGFILE=<LOG_NAME>.log TABLES=<TABLE_NAME> TRACE=480300

Export: Release 10.2.0.3.0 - Production on Monday, 30 July, 2007 12:44:30
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-31631: privileges are required

解决方法：

GRANT exp_full_database TO <USERNAME>; 
-- or: 
GRANT dba TO <ROLE_NAME>;

4.8、闪回查询相关

File: <PARFILE_NAME>.par
------------------------------
DIRECTORY = <DIRECTORY_NAME>
DUMPFILE = <DUMP_NAME>.dmp
LOGFILE = <LOG_NAME>.log
FLASHBACK_TIME = "to_timestamp(to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')"

> expdp <USERNAME>/<PASSWORD> SCHEMAS=<SCHEMA_NAME> parfile=<PARFILE_NAME>.par
...
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-31693: Table data object "<SCHEMA_NAME>"."<TABLE_NAME>" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
...

解决方法：

GRANT flashback ON <SCHEMA_NAME>.<TABLE_NAME> TO <USERNAME>; 
-- or: 
GRANT flashback any table TO <ROLE_NAME>;

4.9、Change Data Capture (CDC) related.

...
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_CDC_EXPDP".SCHEMA_CALLOUT(:1,0,1,'10.02.00.03.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 907
ORA-01031: insufficient privileges

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6249
...

...
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_CDC_EXPDP".SCHEMA_CALLOUT(:1,0,1,'10.02.00.03.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 907
ORA-00942: table or view does not exist

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6249
...

解决方法：

...
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_CDC_EXPDP".SCHEMA_CALLOUT(:1,0,1,'10.02.00.03.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 907
ORA-00942: table or view does not exist

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6249
...

4.10、导出用户相关

ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_SCHED_EXPORT_CALLOUTS".SCHEMA_CALLOUT(:1,0,1,'10.02.00.03.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 907
ORA-01950: no privileges on tablespace '<TABLESPACE_NAME>'

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6249

----- PL/SQL Call Stack -----
object line object
handle number name
2DFB4638 14938 package body SYS.KUPW$WORKER
2DFB4638 6314 package body SYS.KUPW$WORKER
2DFB4638 9129 package body SYS.KUPW$WORKER
2DFB4638 1882 package body SYS.KUPW$WORKER
2DFB4638 6875 package body SYS.KUPW$WORKER
2DFB4638 1260 package body SYS.KUPW$WORKER
2DC76BE4 2 anonymous block

Job "<SCHEMA>"."SYS_EXPORT_SCHEMA_02" stopped due to fatal error at 14:38:18

解决方法：

ALTER USER <USERNAME> QUOTA unlimited ON <TABLESPACE_NAME>;
-- or:
ALTER USER <USERNAME> DEFAULT TABLESPACE <TABLESPACE_NAME>;

如果在Oracle 11201数据库上直接使用impdp不落地导入，需要imp_full_database权限

========================分割线=============================

本次是在Oracle 11201和Oracle 11204之间的操作，

需要从Oracle 11204导入表t的数据到Oracle 11201

在Oracle 11201上创建dblink，在此之前需要配置在tnsnames.ora中添加到Oracle 11204的配置

create public database link "dblink_to_11204" connect to user_11204 identified by "passwd_11204" using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = server_11204)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = xxx)))';

expdp user/passwd network_link=dblink_to_11204 directory=expdpdatadir dumpfile=t.dmp tables=store.t log=t.log

$ impdp xxxx directory=expdpdatadir logfile=impdp.log network_link=dblink_store tables=store.t remap_tablespace=tbs_1:tbs_2 remap_schema=user_1:user_2

以上参考MOS文档Doc ID 351598.1