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.
UTL_FILE_DIR Desupport
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
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
Hi Mike,
Just to keep you posted that the above behavior – datapump export over Symbolic link is unavailable since 19c only.
In 18c – Symbolic link restriction is added up only for External tables, UTL_FILE and BFILE
In 19c – it is extended to Datapump export.
Here is my Test case from AskTom that shows that Datapump export over symbolic link completed success.
https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:9540918400346407732
Hi Jeyabal (hope this is the first name),
well, this happens when you don’t patch š
I tried it with my 18.5.0 and 18.6.0 and it fails – with a slightly different error message (not precisely pointing to the issue but it fails):
$ expdp system/oracle full=Y directory=DPDIR dumpfile=expdp19c.dmp logfile=expdp19c.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
I will still update the blog post to mention that there may be differences whether you have a patched or unpatched 18c š
Cheers, and thanks for the hint!
Mike
Thanks Mike. BTW, you can call me as “Rajesh” š
Ok, thanks – I was unsure that the first and what the lastname is. Sorry š
And thanks again, Rajesh!
Cheers,
Mike
Using 19c (windows) I ran into this problem trying to do a DPImport from another server on our domain – not sure how to handle that one as we do not want the DPExports on the database server.
BUT, when I ran the $ORACLE_HOME/rdbms/admin/utldirsymlink.sql on the database I had created I found that several of the directories in the CDB contain symbolic links!
DATA_PUMP_DIR
SDO_DIR_ADMIN
XMLDIR
XSDDDIR
This seems strange to me
Hi Liz,
you may need to share a bit more details with me please.
Does this happen on Windows only?
What sym links got used?
Then I can compare with a default Linux install.
I assume it’s 19.3.0?
Cheers,
Mike
hi Mike,
While doing upgrade from 11.2.0.4 to 18.5, the dbua announced the warning:
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;
We continued the upgrade, everything went fine.
Afterwards, I executed $ORACLE_HOME/rdbms/admin/utldirsymlink.sql and surprise …
No DIRECTORY OBJECTS with symlinks found.
PL/SQL procedure successfully completed.
Is it possible that DBUA resolved this?
kind regards,
Els
I can’t speak about the DBUA.
And I doubt that it solved it as it would mean that it would need to change your directories.
But check if DBUA simply removed the directories.
Cheers,
Mike