Symbolic Links in DIRECTORY Objects not permitted with Oracle 18c/19c

With every new release behavior changes are introduced. This applies to 18c/19c as well. I want to blog about a behavior change related to the usage of symbolic links in directory paths.

DIRECTORY Objects

Since a couple of years, the direct usage of directory and file paths within the database is not permitted anymore. Nowadays a logical DIRECTORY object must be used to access files outside the database. The following features use DIRECTORY objects:

  • Data Pump
  • PL/SQL package UTL_FILE
  • BFILEs
  • External Tables

One advantage of this approach is the possibility to grant read/write permissions to database users.

SQL> CREATE OR REPLACE DIRECTORY data_dir AS '/data';
SQL> GRANT READ, WRITE ON DIRECTOR data_dir TO scott;

Behavior Change

Starting with Oracle 18c it is not possible anymore to use paths which include symbolic links. Lets see what is happening, when I still try to use symbolic links. I create a symbolic link for my /data directory.

$> mkdir /data
$> ln -s /data /u00/app/oracle/data

Then I create a DIRECTORY object in the database that points to the symbolic link.

SQL> CREATE DIRECTORY data_symlink_dir AS '/u00/app/oracle/data';
SQL> GRANT READ, WRITE ON DIRECTORY data_symlink_dir TO scott;

When SCOTT tries to use this directory for a Data Pump export, an ORA-29283 is raised.

$> expdp scott/tiger DIRECTORY=DATA_SYMLINK_DIR

Export: Release 19.0.0.0.0 - Production on Fri May 17 13:08:03 2019
Version 19.2.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]

If the parameter NOLOGFILE=YES is used, the export seems to start, but it fails after the start. But the dumpfile is created.

$> expdp scott/tiger DIRECTORY=DATA_SYMLINK_DIR NOLOGFILE=YES

Export: Release 19.0.0.0.0 - Production on Fri May 17 13:10:43 2019
Version 19.2.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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** DIRECTORY=DATA_SYMLINK_DIR NOLOGFILE=YES
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CREATE_OBJECT_ROWS [INDEX_STATISTICS]
INDEX_STATISTICS
ORA-39155: error expanding dump file name "/u00/app/oracle/data2/expdat.dmp"
ORA-48128: opening of a symbolic link is disallowed

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 12620
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPF$FILE", line 9730
ORA-06512: at "SYS.KUPF$FILE_INT", line 1211
ORA-06512: at "SYS.KUPF$FILE", line 9671
ORA-06512: at "SYS.KUPW$WORKER", line 11331

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x6bd0cd20     33472  package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION
0x6bd0cd20     12641  package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR
0x6bd0cd20     11602  package body SYS.KUPW$WORKER.CREATE_OBJECT_ROWS
0x6bd0cd20     15264  package body SYS.KUPW$WORKER.FETCH_XML_OBJECTS
0x6bd0cd20      3907  package body SYS.KUPW$WORKER.UNLOAD_METADATA
0x6bd0cd20     13732  package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS
0x6bd0cd20      2429  package body SYS.KUPW$WORKER.MAIN
0x6bc79e10         2  anonymous block

KUPW: Object row index into parse items is: 19
KUPW: Parse item count is: 190
KUPW: In function CHECK_FOR_REMAP_NETWORK
KUPW: Nothing to remap
KUPW: In procedure BUILD_OBJECT_STRINGS - non-base info
KUPW: In procedure BUILD_SUBNAME_LIST with INDEX_STATISTICS:.
KUPW: In function NEXT_PO_NUMBER
KUPW: PO number assigned: 37
KUPF$FILE.WRITE_LOB
KUPW: In procedure DETERMINE_FATAL_ERROR with ORA-39155: error expanding dump file name "/u00/app/oracle/data2/expdat.dmp"
ORA-06512: at "SYS.KUPF$FILE", line 9730
ORA-48128: opening of a symbolic link is disallowed
ORA-06512: at "SYS.KUPF$FILE_INT", line 1211
ORA-06512: at "SYS.KUPF$FILE", line 9671

Job "SCOTT"."SYS_EXPORT_SCHEMA_01" stopped due to fatal error at Fri May 17 13:10:51 2019 elapsed 0 00:00:07

Solution

Of course it is recommended to avoid the usage of symbolic links from now on. But if this is not possible, the old behavior can be restored as a workaround. To restore the old behavior, the following underscore parameters must be set.

SQL> ALTER SYSTEM SET "_disable_directory_link_check" = TRUE SCOPE=SPFILE;
SQL> ALTER SYSTEM SET "_kolfuseslf" = TRUE SCOPE=SPFILE;

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

NOTE: If _kolfuseslf is not adjusted, creation of the logfile is still not possible. Creation of the dumpfile works fine.

CAUTION: Setting underscore parameters can lead to an unstable database. So set them only in production after contacting Oracle Support.

After setting both parameters, the export is working like in the previous releases.

$> expdp scott/tiger DIRECTORY=DATA_SYMLINK_DIR

Export: Release 19.0.0.0.0 - Production on Fri May 17 13:51:29 2019
Version 19.2.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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** DIRECTORY=DATA_SYMLINK_DIR
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
. . exported "SCOTT"."OBJECTS_COPY2"                     8.995 MB   72524 rows
. . exported "SCOTT"."NEW_OBJECTS_COPY"                  4.741 MB   39981 rows
. . exported "SCOTT"."PLAN_TABLE"                        23.02 KB       5 rows
. . exported "SCOTT"."DEPT"                              7.343 KB       4 rows
. . exported "SCOTT"."SALGRADE"                          5.953 KB       5 rows
. . exported "SCOTT"."MV_EMP"                            5.187 KB      14 rows
. . exported "SCOTT"."DUMMY"                             5.054 KB       1 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."EMP"                               9.210 KB      14 rows
. . exported "SCOTT"."FBTEST"                            5.968 KB       1 rows
. . exported "SCOTT"."LDBLKCHN$TMP"                          0 KB       0 rows
. . exported "SCOTT"."OBJECTS_COPY"                      4.741 MB   39981 rows
. . exported "SCOTT"."T1"                                5.101 KB       3 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u00/app/oracle/data/expdat.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 17 13:53:50 2019 elapsed 0 00:02:14

If you want to get rid of all DIRECTORY objects with symbolic links, Oracle provides the script $ORACLE_HOME/rdbms/admin/utldirsymlink.sql to identify these objects.

SQL> @?/rdbms/admin/utldirsymlink.sql
The following DIRECTORY OBJECTS contain symlinks:
'DATA_SYMLINK_DIR'
1 DIRECTORY OBJECTS with symlinks found.

PL/SQL procedure successfully completed.

PL/SQL function DBMS_UTILITY.DIRECTORY_HAS_SYMLINK is used to check the paths of the DIRECTORY objects. It is possible to use the it for a specific object.

SQL> SELECT DBMS_UTILITY.DIRECTORY_HAS_SYMLINK(dirobj => 'DATA_SYMLINK_DIR') as "HAS_SYMLINK" FROM dual;

HAS_SYMLINK
-----------
          1

NOTE: The function is not listed in the official
PL/SQL Packages and Types Reference in Oracle 18c and 19c.

References