Behavior Change in Oracle 18c/19c: No symbolic links for Data Pump directories

UTL_FILE Package Symbolic Link in Directory Paths Not Supported

Using the UTL_FILE package to access a symbolic link fails in the new Oracle Database release. To avoid the issue, you must change the directory object and the file name, so that neither contains a symbolic link.

Of course, it is not obvious that this may have an effect on Data Pump as well. But it has.

Quick Experiment with Data Pump

I’m creating a directory object – but on the OS level, it is a symbolic link.

mkdir /home/oracle/dp
ln -s /home/oracle/dp /home/oracle/symlnk_dp

This is a symbolic directory link to /home/oracle/dp .
I will use this symbolic link for a directory object in the database. And I plan to use it for Data Pump afterwards.

sqlplus / as sysdba

create directory DPDIR as '/home/oracle/symlnk_dp';
grant read, write on directory DPDIR to system;

Afterwards, I will try to use this directory for Data Pump during a simple export.

expdp system/oracle full=Y directory=DPDIR dumpfile=expdp19c.dmp logfile=expdp19c.log

What happens in Oracle 12.2.0.1?

A full database export will be done. Data Pump does its job as expected.

What happens in Oracle 19c?

Well, that’s the reason for this blog post – and the same will happen in Oracle Database 18c:

$ expdp system/oracle full=Y directory=DPDIR dumpfile=expdp19c.dmp logfile=expdp19c.log

Export: Release 19.0.0.0.0 - Production on Wed Jul 10 22:04:25 2019
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-29283: invalid file operation: path traverses a symlink [29433]

Ouch!

The temporary solution

Others seem to have found out about this already. You can read a clear description on Christian Grohmann’s blog.

There’s a workaround if you hit this but can’t replace the sym links straight away. You can set these two underscore parameters to revert to the Oracle 12.2.0.1 and earlier behavior. But PLEASE be aware that there is a reason (security!) why this has been desupported.

ALTER SYSTEM SET "_disable_directory_link_check"=TRUE
             COMMENT='  - Directory SymLink Desupport- Jul 11, 2019 by Mike'
             SCOPE=SPFILE;
ALTER SYSTEM SET "_kolfuseslf"=TRUE
             COMMENT='  - Directory SymLink Desupport- Jul 11, 2019 by Mike'
             SCOPE=SPFILE;

How to detect symbolic link directories?

Since Oracle Database 18c we offer a script to detect symbolic links used for directories:

$ORACLE_HOME/rdbms/admin/utldirsymlink.sql

The output looks like this:

SQL> start $ORACLE_HOME/rdbms/admin/utldirsymlink.sql
The following DIRECTORY OBJECTS contain symlinks:
'DPDIR'
1 DIRECTORY OBJECTS with symlinks found.

PL/SQL procedure successfully completed.

No errors.

This gets checked as well by default when you execute the preupgrade.jar to upgrade the database from a release before Oracle 18c to either Oracle 18c or 19c:

$ java -jar $OH19/rdbms/admin/preupgrade.jar TEXT TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2019-07-11T10:01:39

Upgrade-To version: 19.0.0.0.0

...

  8.  To identify directory objects with symbolic links in the path name, run
      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
      Recreate any directory objects listed, using path names that contain no
      symbolic links.
      
      Some directory object path names may currently contain symbolic links.
      
      Starting in Release 18c, symbolic links are not allowed in directory
      object path names used with BFILE data types, the UTL_FILE package, or
      external tables.

...

You’ll find the check documented in MOS Note: 2380601.1 (Database Preupgrade tool check list):

It does not affect Data Pump only – but also happens with BFILEs and External Tables.

The correct solution

The preupgrade.jar tells you already the correct solution (see the picture above):
Recreate any directory object listed using path names – not containing any symbolic links.

Further Information and Links

• Desupport of UTL_FILE_DIR in Oracle Database 18c
• Christian Gohmann; Symbolic Links in Directory Objects not permitted in Oracle 18c/19c
• Connor McDonald: The Death of UTL_FILE
• Oracle 12.2.0.1: UTL_FILE – Deprecated Subprograms
• MOS Note: 2380601.1 (Database Preupgrade tool check list)

And an annotation as you can see from the comments section that Jayabal pointed out that it does not fail in 18c. I tested it with 18.6.0, and it fails with a similar error message:

$ expdp system/oracle full=Y directory=DPDIR dumpfile=expdp18c.dmp logfile=expdp18c.log

Export: Release 18.0.0.0.0 - Production on Mon Jul 15 17:42:50 2019
Version 18.6.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-29283: invalid file operation

But as you can see from AskTom’s Connor McDonald testing it with an unpatched 18.3.0, it does not fail. I think the change came in with either 18.5.0 or 18.6.0 as a security change (just my guess, I don’t have the exact bug number for this behavior change).

–Mike