Snow Inventory Oracle Scanner (SIOS) fails to collect Oracle database information, and the log contains the following error when the scanner attempts to run its SQL scripts:

SP2-0310: unable to open file "run_TEST.sql"

This error can occur during automatic Oracle inventory when the Oracle owner account does not have permission to read the SQL scripts extracted by the scanner.

During automatic inventory, Snow Inventory Oracle Scanner discovers the running Oracle instances, determines which operating system user owns each database process, and switches to that user when inventorying the database.

For example, the scanner might initially run as root without Oracle database credentials configured in the agent:

Trace; Initializing; se.snow.oracleaudit.SnowOracleScanner; main; OS user: root

This is referred to as automatic inventory. As described in the Snow Inventory Oracle Scanner documentation, SIOS can automatically discover running Oracle instances, identify the operating system user running each instance, and switch to that user to inventory the database without requiring a dedicated Oracle database account.

The operating system account under which the database runs and owns the Oracle files is referred to here as the Oracle owner. Database connections are made using the sysdba role.

How the error occurs

One of the first actions SIOS performs is extracting the SQL scripts it needs from the JAR file into a temporary directory. It then updates the ownership and permissions for the extracted files and directories.

The log shows a command similar to the following:

Trace; Initializing; se.snow.common.SystemHelper; executeCommandReturnValuesAsString; Executing system command: [/bin/sh, -c, /bin/chown -R root:dba /tmp/snow/sios/]

In this example, the following command recursively changes ownership of the extracted files and directories to the root user and the dba group:

chown -R root:dba /tmp/snow/sios/

To read the extracted scripts, the Oracle owner must have the required permissions. By default, SIOS uses the dba group unless another group is configured using CommonOSGroup.

How to confirm the cause

SIOS determines which operating system user owns the Oracle database process. The log shows output similar to the following:

Verbose; Scanning for Oracle data; se.snow.common.SystemCommandExecutor; executeCommand; Starting command [/bin/sh, -c, /bin/ps -p 5113 -o user | /bin/tail -1]

Verbose; Scanning for Oracle data; se.snow.common.SystemHelper; executeCommandReturnValuesAsString; Output: oracle

In this example, 5113 is the operating system process ID for the Oracle database. The following command queries the user that owns that process:

ps -p 5113 -o user

The result is the oracle account. This is the default Oracle owner name and is commonly returned in this situation.

SIOS then uses the id command to determine which operating system groups the Oracle owner belongs to:

Verbose; Scanning for Oracle data; se.snow.common.SystemCommandExecutor; executeCommand; Starting command [/bin/sh, -c, id oracle]

Verbose; Scanning for Oracle data; se.snow.common.SystemHelper; executeCommandReturnValuesAsString; Output: uid=500(oracle) gid=501(oracle) groups=501(oracle)

In this example, the oracle user belongs only to the oracle group. It is not a member of the dba group.

This group mismatch causes the failure. The extracted SQL scripts are assigned to root:dba, but SIOS switches from root to the Oracle owner before starting SQL*Plus.

The log shows the switch to the oracle account:

Verbose; Scanning for Oracle data; se.snow.common.SystemCommandExecutor; executeCommand; Starting command [/bin/sh, -c, cd /tmp/snow/sios/sios8678147548054945305/;su oracle -c '/bin/sh -c '"'"' TNS_ADMIN=/tmp/snow/sios/sios8678147548054945305; ORACLE_SID=XXXXXX; ORACLE_HOME=/app/oracle/product/19.0.0/dbhome_1; LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH; PATH=/app/oracle/product/19.0.0/dbhome_1/bin:$PATH;export TNS_ADMIN ORACLE_SID ORACLE_HOME LD_LIBRARY_PATH PATH;unset http_proxy;unset no_proxy;sqlplus "/ as sysdba" @run_TEST.sql XXXXXX'"'"]

The important part of this command is:

su oracle

After this switch, the remaining activity runs under the oracle account instead of root.

Because the oracle account is neither root nor a member of the dba group, it cannot read the extracted run_TEST.sql file. SQL*Plus connects successfully to the database but fails when it attempts to open the script:

Verbose; Scanning for Oracle data; se.snow.common.SystemHelper; executeCommandReturnValuesAsString; Output: SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 21 11:52:20 2024 Version 19.18.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.18.0.0.0

SP2-0310: unable to open file "run_TEST.sql"

SQL> SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.18.0.0.0

The SP2-0310 message indicates that SQL*Plus could not read the specified file.