签到成功

知道了

CNDBA社区CNDBA社区

Oracle 19.3 expdp/impdp ORA-65254 ORA-39087 错误解决方法

2021-09-17 16:14 4853 0 原创 Oracle 19c
作者: dave

1 现象1

在19.3 CDB 中执行expdp 导出,创建目录时报如下错误:

[dave@www.cndba.cn  ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 17 15:31:19 2021
Version 19.3.0.0.0

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


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

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DAVE                           READ WRITE NO
         5 USTC2                          MOUNTED
         6 USTC                           READ WRITE NO
         9 HUAINING                       MOUNTED
SQL> alter session set container=ustc;

Session altered.


SQL>  create directory backup as '/u01/backup/';
 create directory backup as '/u01/backup/'
*
ERROR at line 1:
ORA-65254: invalid path specified for the directory


SQL> !oerr ora 65254
65254, 00000, "invalid path specified for the directory"
// *Cause:   An absolute path was used in the CREATE DIRECTORY statement.
//           Since the PDB has set the PATH_PREFIX property, only relative
//           path is allowed for directories.
// *Action:  Specify a relative path and reissue the statement.
//

2 解决方法

这里报的错误,实际上,这里是因为PDB 使用种子创建时指定了PATH_PREFIX 前缀。 http://www.cndba.cn/cndba/dave/article/4729

ORA-65254: invalid path specified for the directory

在这种情况下,创建目录时只能使用相对路径:http://www.cndba.cn/cndba/dave/article/4729

SQL> create directory backup as 'backup';

Directory created.

SQL> set lines 150
SQL> select directory_name ,directory_path  from dba_directories where directory_name='BACKUP';

DIRECTORY_NAME  DIRECTORY_PATH
--------------- ----------------------------------------------------------------------
BACKUP          /u01/app/oracle/oradata/ORCL/ustc/backup

这里在PATH_PREFIX 前缀下会生成。 但是注意这里的目录还是需要手工创建,在OS级别mkdir创建backup目录。

然后赋权:

SQL> grant read, write on directory backup to system;

Grant succeeded.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         6 USTC                           READ WRITE NO
SQL>

3 现象2

但是这里还是会报如下错误:http://www.cndba.cn/cndba/dave/article/4729

[dave@www.cndba.cn  USTC]$ expdp system/oracle@dave full=yes directory=dumpdir dumpfile=pdbfull_%u.dmp filesize=2g parallel=2 logfile=pdbfull.log

Export: Release 19.0.0.0.0 - Production on Fri Sep 17 15:27:06 2021
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
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DUMPDIR is invalid


[dave@www.cndba.cn  USTC]$ oerr ora 39087
39087, 00000, "directory name %s is invalid"
// *Cause:  A corresponding directory object does not exist.
// *Action: Correct the directory object parameter, or create a corresponding
//          directory object with the CREATE DIRECTORY command.
[dave@www.cndba.cn  USTC]$

4 解决方法

关闭PDB并重启后,再次赋权:

http://www.cndba.cn/cndba/dave/article/4729
http://www.cndba.cn/cndba/dave/article/4729
http://www.cndba.cn/cndba/dave/article/4729
http://www.cndba.cn/cndba/dave/article/4729http://www.cndba.cn/cndba/dave/article/4729

SQL> grant read, write on directory backup to system;

Grant succeeded.

虽然之前已经赋权过,但还是要在赋权一次。http://www.cndba.cn/cndba/dave/article/4729

执行成功:

[dave@www.cndba.cn  ustc]$ expdp system/oracle@ustc full=yes directory=backup dumpfile=pdbfull1%u.dmp filesize=2g parallel=2 logfile=pdbexpfull.log

Export: Release 19.0.0.0.0 - Production on Fri Sep 17 16:00:48 2021
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
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/********@ustc full=yes directory=backup dumpfile=pdbfull1%u.dmp filesize=2g parallel=2 logfile=pdbexpfull.log
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
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
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SCHEMA/USER

如果还是出现错误,可以尝试在赋权一次。 http://www.cndba.cn/cndba/dave/article/4729

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2283
    原创
  • 3
    翻译
  • 579
    转载
  • 196
    评论
  • 访问:8172551次
  • 积分:4428
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ