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.
Table of Contents
DIRECTORY Objects
For 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 that include symbolic links. Let’s 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 dump file 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, the creation of the log file is still not possible. The creation of the dump file 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 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.
One comment
Comments are closed.