Snow Inventory Oracle Scanner fails to collect Oracle database information, and the log shows an error similar to the following when the scanner attempts to log into the database to run scripts: 

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

Here's what's happening and how to fix it.

In the following example the agent is running under the root account and there are no Oracle db credentials in the agent config:

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

This is referred to as automatic inventory. From the documentation: 

Snow Inventory Oracle Scanner is designed to perform automatic Oracle inventory without the need to create a specific user account within the Oracle databases. The scanner automatically discovers all running Oracle instances on the current server, detects what user is running each database instance, and switches to that specific user when inventorying the databases. No specific permissions and no specific user is needed to inventory the databases.

In this scenario, before attempting the connection to the Oracle database we switch to the "Oracle owner" account to invoke sqlplus.

By "Oracle Owner" we mean the OS user account under which the database runs and owns all the files. Database connections are made using the context of the "sysdba" role. 

One of the first things SIOS does is to extract the scripts from the jar file to run. A temporary directory is created to extract the files into, and the permissions and ownership of the files and directories is updated. The log will show messages similar to the following when this occurs:

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

The command being run (chown -R root:dba /tmp/snow/sios} which recursively changes the ownership of the directory to the root user and the dba group. As such, in order to use these files now you must either be the root user, or else a member of the dba OS group. When the owner is being detected, logging similar to the following occurs: 

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

The scanner has the operating system process ID for the Oracle database (in our example here, it's ID 5113). The command (ps -p <PID> - o user) queries the user that owns that PID. In the example above the result is the oracle account. This is the default and will be the value most often returned. 

Next the scanner identifies the oracle user to find out what groups that account belongs to using the "id" command:

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)

From this the scanner determines that the oracle user is only a member of one group, and that's also called oracle. Critically, this reveals that the oracle user is not a member of the dba group. This is all the information needed to confirm and resolve the issue. 

Ultimately, the scanner attempts to log into the database and run scripts and fails:

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'"'"]

Notice the "su oracle " logged. This is the root user switching to the oracle user. Following on from here, all activity is run under the context of oracle rather than root. Because the oracle user is neither root nor a member of the dba group, it does not have the required access:

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 means that the file could not be read. The fix to this is to deploy the sios.config file (if not already there by default) and configure a suitable group to use other than dba for the group ownership of the extracted files. SIOS will then use the group defined in the sios.config for the chown command. 

In the example used here the oracle account launches sqlplus, therefore it’s the oracle user that needs to be able to read run_TEST.sql.

The oracle account is only a member of one group, also named oracle.

As such the sios.config must be configured as follows: 

<?xml version="1.0" encoding="utf-8"?>

<Configuration>

        <FileSystemSecurityModule>DEFAULT</FileSystemSecurityModule>

        <CommonOsGroup>oracle</CommonOsGroup>

</Configuration>

In many situations, you will probably find the oracle owner is a member of multiple groups. If so, confirm which group should be used. The output of the id command provides the list of options. 

Note

In some cases the root of the problem will be because the computer does not even have a group called dba. In such instances when the scanner tries to execute the chown command, the following error is logged:

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

(1) /bin/chown: invalid group: 'root:dba'

The solution is basically the same. Identify a suitable existing user that the oracle owner belongs to and set that in the sios.config.