The Snow Inventory Oracle Scanner (SIOS) is designed to handle a standard out of the box type installation and configuration of Oracle databases. Most of the time deviations from the default configuration are also handled correctly. However, some more unusual configurations prevent SIOS from connecting to the Oracle database properly. 

In very simple terms, all SIOS does is set standard environment variables, uses sqlplus to log in, and runs some queries.

If this standard method results in "ORA-01017: invalid username/password; logon denied" in the sios log, and you're absolutely sure that the password in use is correct (same creds can be used to login with sqlplus), most likely you have an environment variable set in your shell that the SIOS does not.

You may also find errors like:

ORA-28000: The account is locked.  (SIOS trying "bad" credentials over and  over)

ORA-12154: TNS:could not resolve the connect identifier specified (SIOS can't find this database)

Most typically the offending variable is the $TNS_ADMIN environment variable.

Here's an example command line run to set the variables and call sqlplus to run the connection test taken from the SIOS log:

cd /opt/snow/data/tmp/snow/sios/sios8122984862911632474/; TNS_ADMIN=/opt/snow/data/tmp/snow/sios/sios8122984862911632474; ORACLE_SID=ORCLCDB; ORACLE_HOME=/opt/oracle/product/19c/dbhome_1; LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH; NLS_LANG=AMERICAN_AMERICA.UTF8; PATH=/opt/oracle/product/19c/dbhome_1/bin:$PATH;export TNS_ADMIN ORACLE_SID ORACLE_HOME LD_LIBRARY_PATH NLS_LANG PATH;unset http_proxy;unset no_proxy;sqlplus "c##oscan" @run_TEST.sql ORCLCDB

Splitting up that concatenated command line, this is the sequence of events:

SIOS changes context to a temporary directory:

cd /opt/snow/data/tmp/snow/sios/sios8122984862911632474/

SIOS sets the environment variables:

TNS_ADMIN=/opt/snow/data/tmp/snow/sios/sios8122984862911632474
ORACLE_SID=ORCLCDB
ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
NLS_LANG=AMERICAN_AMERICA.UTF8
PATH=/opt/oracle/product/19c/dbhome_1/bin:$PATH

The variables are exported:

export TNS_ADMIN ORACLE_SID ORACLE_HOME LD_LIBRARY_PATH NLS_LANG PATH
unset http_proxy
unset no_proxy

And finally sqlplus is used to login and run the script:

sqlplus "c##oscan" @run_TEST.sql ORCLCDB

Notice that the first variable set is TNS_ADMIN and it's set within our temporary location. In that location a very simple version of an sqlnet.ora file is placed. On a Unix like OS that sqlnet.ora file  looks like this:

SQLNET.AUTHENTICATION_SERVICES= (ALL)

On Windows it's:

SQLNET.AUTHENTICATION_SERVICES=(NTS)

Take a look at the sqlnet.ora file that you're using in your environment to connect to the target db.

It will probably be in either :

• $ORACLE_HOME/network/admin
• $ORACLE_HOME/network/admin/<SID>

Chances are it looks quite different to the one above! Here's an example of a more "exotic" one where SIOS is setting the password protocol version to 8.

NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT)

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

You may have other settings in here as well that sqlplus will need in order to connect.

So the reason it works for you but not SIOS is that when you call sqlplus your shell is pointing TNS_ADMIN to your actual $TNS_ADMIN on the server, and sqlplus is able to use your sqlnet.ora and tnsnames.ora etc files in order to work out how to connect. When the sios tries it only has our cut-down version and so doesn't know how to connect to your database.

The solution is to export your actual TNS_ADMIN for SIOS to use. Here's how we do that in the snowagent.config file.

The following oracle section will export a TNS_ADMIN that will affect all the databases we find.

Notice the "Environment" element is set within the oracle element, but not attached to any particular instance.

<Oracle enabled="true">
    <Environment>
        <Item variable="TNS_ADMIN" value="/opt/oracle/product/19c/dbhome_1/network/admin/ROBCBD"/>
    </Environment>
    <DefaultInstanceCredentials>
        <UserName>c##oscan</UserName>
        <Password>XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX</Password>
    </DefaultInstanceCredentials>
    <Include>
        <AllInstances>true</AllInstances>
    </Include>
</Oracle>

In the real word though quite often it's discovered that it's just one out of any number of other instances that need a particular sqlnet.ora file, so you may need to be more granular about it.

In the example here the ORCLCDB is nice and standard and doesn't need any special sqlnet.ora settings to connect, but the ROBCBD does need a special sqlnet.ora that will only work with ROBCBD and not ORCLCDB.

Here's how to make that work, using the "InstancesWithConfiguration" element:

<Oracle enabled="true">
    <DefaultInstanceCredentials>
        <UserName>c##oscan</UserName>
        <Password>XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX</Password>
    </DefaultInstanceCredentials>
    <Include>
        <AllInstances>true</AllInstances>
    </Include>
    <InstancesWithConfiguration>
        <Instance>
            <SID>ROBCBD</SID>
            <Environment>
                <Item variable="TNS_ADMIN" value="/opt/oracle/product/19c/dbhome_1/network/admin/ROBCBD"/>
            </Environment>
        </Instance>
    </InstancesWithConfiguration>
 </Oracle>

In the above we include all instances, so every instance found will use the standard method of connecting as outlined above. This covers the ORCLCDB instance. But if we find an instance called ROBCBD, the SIOS also passes the customized $TNS_ADMIN for just that instance.

Here's how the command lines look in the sios log:

cd /opt/snow/data/tmp/snow/sios/sios14665351581772668785/; TNS_ADMIN=/opt/snow/data/tmp/snow/sios/sios14665351581772668785; ORACLE_SID=ROBCBD; ORACLE_HOME=/opt/oracle/product/19c/dbhome_1; LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH; NLS_LANG=AMERICAN_AMERICA.UTF8; PATH=/opt/oracle/product/19c/dbhome_1/bin:$PATH; TNS_ADMIN="/opt/oracle/product/19c/dbhome_1/network/admin/ROBCBD";export TNS_ADMIN ORACLE_SID ORACLE_HOME LD_LIBRARY_PATH NLS_LANG PATH TNS_ADMIN;unset http_proxy;unset no_proxy;sqlplus "c##oscan" @run_TEST.sql ROBCBD

Notice the TNS_ADMIN gets defined twice, the second one being that which was defined in the snowagent.config. As this one comes later it supersedes the standard one SIOS would usually use.

Next,  ORCLCDB is processed:

cd /opt/snow/data/tmp/snow/sios/sios4324485171963087190/; TNS_ADMIN=/opt/snow/data/tmp/snow/sios/sios4324485171963087190; ORACLE_SID=ORCLCDB; ORACLE_HOME=/opt/oracle/product/19c/dbhome_1; LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH; NLS_LANG=AMERICAN_AMERICA.UTF8; PATH=/opt/oracle/product/19c/dbhome_1/bin:$PATH;export TNS_ADMIN ORACLE_SID ORACLE_HOME LD_LIBRARY_PATH NLS_LANG PATH;unset http_proxy;unset no_proxy;sqlplus "c##oscan" @run_TEST.sql ORCLCDB

The only TNS_ADMIN in play here is the cut down one from the temporary location. This results in successful inventory of both instances.

In an environment where there are many databases spread across many servers and you'll always want to use the same snowagent.config file for them all, multiple instances can be defined within the InstancesWithConfiguration to affect only those instances that need any special environment variables.