12c pdb expdp use DATA_PUMP_DIR meet ORA-39145
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIRis invalid
原因如下：
If none of the previous three conditions yields a directory object and you are a privileged user (i.e. uses who have the EXP_FULL_DATABASE role and IMP_FULL_DATABASE role), then Data Pump attempts to use the value of the default server-based directory object, DATA_PUMP_DIR.
It is important to understand that Data Pump does not create the DATA_PUMP_DIR directory object; it merely attempts to use its value when a privileged user has not provided a directory object using any of the mechanisms previously described. This default directory object must first be created by a DBA. Do not confuse this with the client-based environment variable of the same name.
Example to create all files in the directory DATA_PUMP_DI
DATA_PUMP_DIR 目录需要有EXP_FULL_DATABASE role and IMP_FULL_DATABASE role ,而12c pdb 下的system 用户没有该roll:
在cdb 下 system: PRIVILEGE 列为空，
SET lines 80
COL grantee FORMAT a20
COL privilege FORMAT a10
SELECT directory_name, grantee, privilege,DIRECTORY_PATH
FROM user_tab_privs t, all_directories d
WHERE t.table_name(+)=d.directory_name
ORDER BY 1,2,3;
DIRECTORY_NAME
--------------------------------------------------------------------------------
GRANTEE PRIVILEGE
-------------------- ----------
DIRECTORY_PATH
--------------------------------------------------------------------------------
DATAPUMP
/centrproddblog/dump
DATA_PUMP_DIR
/opt/oracle12c/admin/centrprod/dpdump/
OPATCH_INST_DIR
/opt/oracle12c/product/12.1/OPatch
OPATCH_LOG_DIR
/opt/oracle12c/product/12.1/QOpatch
OPATCH_SCRIPT_DIR
/opt/oracle12c/product/12.1/QOpatch
XSDDIR
/opt/oracle12c/product/12.1/rdbms/xml/schema
8 rows selected.
SQL> SQL>
在pdb 下 system: 即便给了读写权限给system, 仍然没有IMP_FULL_DATABASE rool;
SQL> /
DIRECTORY_NAME
--------------------------------------------------------------------------------
GRANTEE PRIVILEGE
-------------------- ----------
DIRECTORY_PATH
--------------------------------------------------------------------------------
DATA_PUMP_DIR
SYSTEM READ
/opt/oracle12c/admin/centrprod/dpdump/
DATA_PUMP_DIR
SYSTEM WRITE
/opt/oracle12c/admin/centrprod/dpdump/
OPATCH_INST_DIR
/opt/oracle12c/product/12.1/OPatch
OPATCH_LOG_DIR
/opt/oracle12c/product/12.1/QOpatch
OPATCH_SCRIPT_DIR
/opt/oracle12c/product/12.1/QOpatch
解决办法：
1.pdb 下手工创建directory datapump :
create or replace directory datapump as '/centruatdblog/dump';
grant read,write on directory datapump to public;
2 使用 datapump 替代 DATA_PUMP_DIR
Solution for user SCOTT: as described in 5.3. user SCOTT can set the environment variable DATA_PUMP_DIR to MY_DIR:
C:\> expdp scott/tiger@my_db_alias DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=scott
refer:
Export/Import DataPump Parameter DIRECTORY - How to Specify a Directory (文档 ID 266875.1)
