MDM INSTALL: AWSJIS145E though TWS Oracle user does not exist

During installation of a Tivoli Workload Scheduler (TWS) Master Domain Manager (MDM) in which the RDBMS vendor is Oracle, a test is performed to confirm that the user specified to be the TWS "Oracle" user does not exist. If it does exist, a test is performed to confirm that the user has access to the tables in the Oracle tablespaces. It is possible to get a false positive indicating that the user exists even though it does not exist.

If it is known that the "Tivoli Workload Scheduler Oracle Database user" does not exist, then it is possible that the test for the existence of that user is a false positive.

If there are global settings made for Oracle sqlplus sessions that would return a custom prompt or if the following setting is in effect: "set FEEDBACK ON", then the return from "test_<user>_user.sql could be programmatically construed as a confirmation that the user exists in Oracle.

1. View the output from the test_<user>_user.sql in the /tmp/TWA/<version>/twsismp.log file.

For example:

(May 29, 2009 6:51:55 PM), TWSInstall, com.ibm.tws.ismp.wizard.panels.TWSOracleDBCfgInfo, dbg, createTestOraTwsUserSql: the sql file used to test the existence of the TWS Oracle User is /tmp/TWA/<version>/test_twsora_user.sql
....
(May 29, 2009 6:51:55 PM), TWSInstall, com.ibm.tws.ismp.wizard.panels.TWSOracleDBCfgInfo, dbg, validate_twsDBUserAuthInfo: result of the query for the existence of the TWS Oracle User is: Date: FRI MAY-29-2009Userid: SYSTEMDatabase: MST 01 row selected.
(May 29, 2009 6:51:55 PM), TWSInstall, com.ibm.tws.ismp.wizard.panels.TWSOracleDBCfgInfo, dbg, validate_twsDBUserAuthInfo: the TWS Oracle User already exists. The check of its credentials will be done.

In the above log example the return from tws_twsora_user.sql did return "0", but it also returned a custom prompt and "1 row selected".

It is possible that there are global settings in place to customize sqlplus sessions. Look for these customizations here:

$ORACLE_HOME/sqlplus/admin/glogin.sql
and/or
login.sql

2. To confirm that the current sqlplus settings will return simple enough information to bypass the above issue, perform the following test sqlplus commands that mimic what is executed in the test_<user>_user.sql script:

This test returns "1 row selected" and will cause the install test to fail:

SQL>select count(*) from dba_users where username = 'TWSORA';

COUNT(*)
----------
0
1 row selected.

For the above example, if the setting is made for the sqlplus sessions then the test will successfully return "0" and the user will be correctly determined to not exist:

SQL> set feedback off
SQL>select count(*) from dba_users where username = 'TWSORA';

COUNT(*)
----------
0

Since the sqlplus commands during installation are being called from a session in which settings cannot be affected, there must be a global change that would impact all sqlplus sessions or the session for the Oracle administrator user that is being used for the installation.

Modify global sqlplus settings so there is not a custom prompt and 'set FEEDBACK off' is in effect for sqlplus sessions. The $ORACLE_HOME/sqlplus/admin/glogin.sql or <User_HOME>/login.sql file(s) may need to be updated temporarily to bypass this issue.