Most of you may have recognized the desupport of
UTL_FILE_DIR with Oracle Database 18c. Reason is mostly that
UTL_FILE_DIR opens a lot of possibilities to do insecure things. But this has another effect which may not be obvious to you. There’s a behavior change in Oracle 18c/19c: No symbolic links for Data Pump directories. Read further to learn more about it.
In Oracle Database 18c we announced the desupport of UTL_FILE_DIR initialization parameter. But as you can read as well in the Database Upgrade Guide, there’s this remark:
UTL_FILE Package Symbolic Link in Directory Paths Not Supported
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
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 220.127.116.11?
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 18.104.22.168.0 - Production on Wed Jul 10 22:04:25 2019 Version 22.214.171.124.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 126.96.36.199.0 - Production ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation: path traverses a symlink 
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 188.8.131.52 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:
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 184.108.40.206.0 Build: 1 on 2019-07-11T10:01:39 Upgrade-To version: 220.127.116.11.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
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 18.104.22.168: 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 22.214.171.124.0 - Production on Mon Jul 15 17:42:50 2019 Version 126.96.36.199.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 188.8.131.52.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).