Thursday, 30 August 2018

WebSphere on Windows - Return Code 15

I've just spent a happy few hours trying to work out why various WebSphere Application Server (WAS) commands such as wsadmin.bat etc. refuse to properly run on a Windows Server 2012 box.

As per a previous blog post: -


I'm running IBM BPM 8.6 ( via WebSphere Application Server 8.5.5.13 ) against Microsoft SQL Server 2012.

For good security reasons, the WAS to SQL Server connection is via a trusted Service Account ( leveraging Integrated Security ), which means that any WAS to SQL Server interactions need to be run as that user.

Therefore, to perform the bootstrap process ( bootstrapProcessServerData.bat ), I need to run a command window ( CMD.EXE ) as the Service Account: -

runas.exe /user:<DOMAIN>\<USER> cmd.exe

which worked a treat.

However, when I ran the bootstrap process OR ANY OTHER SIMILAR WAS COMMAND …..

I got …..

NOTHING

NADA

ZIP

RIEN

By that, I mean that the command simply ran and then quit, without actually doing anything.

Similarly, the wsadmin.bat command failed in a similar way.

I knew it was permissions-related, as the wsadmin.bat command ran OK if I simply ran it from a CMD.EXE prompt ( which I started using Run As Administrator ( from the right-hand mouse button )

Obviously, I had no choice as I needed to run the bootstrap as the Service Account, rather than simply as Administrator.

I ( perhaps stupidly ) wanted to find out why ….

I spent ages tinkering with various .BAT files, including wsadmin.bat etc. but the closest I got was a RC=15 from the wsadmin command …..

In the end, I gave in and simply gave the Service Account access to the \Program Files\IBM\WebSphere\AppServer\profiles\DmgrProfile path ( Full Control ).

At a guess, I suspect that the wsadmin commands, and similar, were trying to write to, say, ..\temp and ..\workspace and ..\temp under the DM profile …

But that's a guess …….

IBM BPM and Microsoft SQL Server 2012 - What Fun

I've written about this MANY MANY times before, over the years and releases of IBM BPM.

This time around, I'm working with BPM 8.6 and Microsoft SQL Server 2012.

This is the second time in two weeks where I've worked with customers running their BPM workload on a Windows stack, including Windows Server 2012, Internet Information Server, Active Directory AND SQL Server.

The use of SQL Server 2012 brings two new wrinkles to the thus far smooth fabric of my BPM installations : -

  1. SQL Server supports so-called Integrated Authentication, where the bind from WebSphere Application Server to SQL Server via JDBC is made without sending credentials over the wire
  2. Secure JDBC connections are made using TLS 1.2, use of which is enforced

With regard to (1), this means that the JDBC connection is secured by use of a "trusted" user ID, known as a Service Account.

There is some analogy to the Single Sign-On (SSO) model that is offered by Kerberos / SPNEGO - in both cases, the target server trusts that the connecting user has been authenticated, by the Windows Domain Controller, rather than having credentials sent over the wire.

This is enforced by use of an additional JDBC connection string parameter: -

integratedSecurity=true

From a WAS perspective, this means that we need to run the JVMs, ALL of them, as the Service Account.

In the Windows world, this can be achieved using the runas command, similar to the way that the Unix sudo command works: -

runas /user:<DOMAIN>\<USER> cmd.exe

If successful, this brings up another CMD window, running as the Service Account.

From this, we can run commands such as startManager.bat and startServer.bat and bootstrapProcessServerData.bat.

To facilitate this, it's a good idea to run the JVMs, especially the Deployment Manager and the Node Agent(s), as Windows Services, using wasservice.exe and WASServiceHelper.bat : -


ensuring that one uses the Service Account to run the WAS binaries.

Speaking personally, I chose to set the Windows Services to auto-start on boot-up but NOT to restart after failure ( allowing one to perform PD when a problem occurs ).

Also, I chose NOT to include the actual JVMs ( AppClusterMember, SupClusterMember, MEClusterMember etc. ) as Services, allowing these to be manually started using WSAdmin or the Integrated Solutions Console.

With regard to (2), we need to ensure that all JDBC connections use TLS 1.2 …

This caused me some fun last week ……

I assumed (!) that this was a simple matter of configuring WAS to use TLS 1.2, via SSL Configurations, pulling the SQL Server signer certificate into the Cell-Default Trust Store etc. ….

Nope, it's not like that !

If the TLS 1.2 connection isn't enforced, we see this: -

Caused by: com.ibm.websphere.ce.cm.StaleConnectionException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "SQL Server did not return a response. The connection has been closed. ClientConnectionId:42f55367-538e-4f70-b007-023af79435f4". DSRA0010E: SQL State = 08S01, Error Code = 0

TLS 1.2 can be enforced, via use of a JVM property: -

com.ibm.jsse2.overrideDefaultTLS

This can either be set as  a Generic JVM Argument

-Dcom.ibm.jsse2.overrideDefaultTLS=true 

or as a JVM Custom Property 

Name - com.ibm.jsse2.overrideDefaultTLS
Value - true

This ALSO needs to be enabled for other Java processes, such as the Bootstrap …..

This requires one to edit the bootstrapProcessServerData.bat script and inserting: -

-javaoption "-Dcom.ibm.jsse2.overrideDefaultTLS=true"

into the script.

So….

Having said all of the above, I hit an issue yesterday where the Bootstrap failed, with: -

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select propvalue from lsw_system where propkey=?]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'lsw_system'.

Given that I had confirmed that I was running this using the Service Account ( using the runas command as mentioned above ), I wasn't too sure what was going wrong ( previously I'd seen the same issue when running the bootstrap as another user - the DBA was able to see this in a SQL Server trace ).

Given that I wasn't seeing any SSL exceptions, I knew that the TLS 1.2 configuration was A-OK.

I then asked the DBA to check the COLLATION sequence as per this: -


which was also A-OK.

At this point, I was hitting a blocker ….

Finally, the DBA had the inspiration to check the Default Schema on the BPMDB and PDWDB ( and CMNDB ) databases.

This was set to dbo which is a SQL Server default RATHER than to the Service Account.

Once he changed it …. ALL WORKED !!!

So, in short, SQL Server is fun, just get your security right !

References



Tuesday, 21 August 2018

WebSphere Application Server - Backing up profiles

I've probably written about this before ….

WebSphere Application Server (WAS) has a neat-o tool for backing WAS profiles, specifically backupConfig.sh, which can be executed against a profile, and which saves the entire profile configuration to a .ZIP file.

There are actually several copies of this script: -

find /opt/ibm/WebSphere/ -name backupConfig.sh

/opt/ibm/WebSphere/AppServer/bin/backupConfig.sh
/opt/ibm/WebSphere/AppServer/profiles/Dmgr02/bin/backupConfig.sh
/opt/ibm/WebSphere/AppServer/profiles/AppSrv02/bin/backupConfig.sh
/opt/ibm/WebSphere/AppServer/profiles/Dmgr01/bin/backupConfig.sh
/opt/ibm/WebSphere/AppServer/profiles/AppSrv01/bin/backupConfig.sh

*BUT* the versions that are are located within the profiles merely reference the original, which is located in the WAS bin directory: -

cat /opt/ibm/WebSphere/AppServer/profiles/Dmgr02/bin/backupConfig.sh

#!/bin/sh
binDir=`dirname ${0}`
. ${binDir}/setupCmdLine.sh
${WAS_HOME}/bin/backupConfig.sh "$@"

The script has several parameters: -

/opt/ibm/WebSphere/AppServer/profiles/Dmgr02/bin/backupConfig.sh -help

Usage: backupConfig [backup_file] [-nostop] [-quiet] [-logfile <filename>]
           [-replacelog] [-trace] [-username <username>] [-password <password>]
           [-profileName <profile>] [-help]


Cleverly, *IF* one chooses to run the script from the profile root: -

/opt/ibm/WebSphere/AppServer/profiles/Dmgr02/bin/backupConfig.sh

then the script ONLY backs up that profile.

Note that there is also a -nostop option - this is NOT the default.

Therefore, if one runs this: -

/opt/ibm/WebSphere/AppServer/bin/backupConfig.sh -profileName Dmgr02

ADMU0116I: Tool information is being logged in file
           /opt/ibm/WebSphere/AppServer/profiles/Dmgr02/logs/backupConfig.log
ADMU0128I: Starting tool with the Dmgr02 profile
ADMU5001I: Backing up config directory
           /opt/ibm/WebSphere/AppServer/profiles/Dmgr02/config to file
           /home/wasadmin/WebSphereConfig_2018-08-21_2.zip
ADMU0505I: Servers found in configuration:
ADMU0506I: Server name: dmgr
ADMU2010I: Stopping all server processes for node Dmgr
Realm/Cell Name: <default>
Username: 


any JVMs running within that profile will automatically be stopped …….

Thankfully, the prompt for credentials saves us BUT it's worth remembering that this is a risk.

The other nice thing is that one can override the backup file path/name to, for example, include a date stamp: -

echo "$(date +"%Y%m%d_%H%M%S")"

20180821_123458

as follows: -

/opt/ibm/WebSphere/AppServer/bin/backupConfig.sh "/home/wasadmin/Dmgr01_Backup_$(date +"%Y%m%d_%H%M%S").zip" -profileName Dmgr01 -nostop 

ADMU0116I: Tool information is being logged in file
           /opt/ibm/WebSphere/AppServer/profiles/Dmgr01/logs/backupConfig.log
ADMU0128I: Starting tool with the Dmgr01 profile
ADMU5001I: Backing up config directory
           /opt/ibm/WebSphere/AppServer/profiles/Dmgr01/config to file
           /home/wasadmin/Dmgr01_Backup_20180821_123603.zip
.......................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................
ADMU5002I: 1,879 files successfully backed up


ls -alh /home/wasadmin/Dmgr01_Backup_20180821_123603.zip

-rw-r--r-- 1 wasadmin wasadmins 551M Aug 21 12:36 /home/wasadmin/Dmgr01_Backup_20180821_123603.zip

This would be useful if, for example, one wished to create regular backups, perhaps via cron, without stopping the JVMs i.e. using the -nostop option.


IBM BPM 8.5.6 - CWMCB0046E - Bootstrap failing

Working with my client to build out an IBM BPM Standard 8.5.6.0 CF2 Deployment Environment, we saw an exception whilst running the bootstrap process: -

/opt/ibm/WebSphere/AppServer/profiles/Dmgr01/bin/bootstrapProcessServerData.sh -clusterName AppCluster

which failed with: -
...
Bootstraping data into cluster AppCluster and logging into /opt/ibm/WebSphere/AppServer/profiles/Dmgr01/logs/bootstrapProcesServerData.AppCluster.log

WASX7357I: By request, this scripting client is not connected to any server process. Certain configuration and application operations will be available in local mode.

...
com.ibm.bpm.config.util.ConfigException: CWMCB0046E: The 'BPMImportOffline' command failed: Index: 0, Size: 0
...
com.ibm.bpm.config.util.ConfigException: com.ibm.bpm.config.util.ConfigException: CWMCB0046E: The 'BPMImportOffline' command failed: Index: 0, Size: 0
...
 
Thankfully, it was a relatively simple solution ….

I'd given the client a set of SQL scripts to create the three BPM schema - psuser, cmnuser and pdwuser - from my own environment; I'm using the same version of Oracle 12cR2 as they are.

This, it transpires, was a mistake !

I had not realised, despite having worked with BPM for 6 years, that the SQL scripts include the WAS and BPM users that one uses when one creates the Deployment Environment.

Ordinarily, that would NOT be a problem - assuming that everyone uses the Dave Hay convention of wasadmin and deAdmin respectively.

Guess what ….

My client, sensibly, chose to use DIFFERENT user names.

These two IBM documents showed me the error of my ways : -



To validate this, I checked my own environment: -

select * from psuser.LSW_USR_XREF;

   USER_ID USER_NAME
---------- ----------------------------------------------------------------
FULL_NAME
--------------------------------------------------------------------------------
PROVIDER
--------------------------------------------------------------------------------
      1002 DSManager1
DSManager1
uid=DSManager1,o=defaultWIMFileBasedRealm

      1003 DSUser1
DSUser1
uid=DSUser1,o=defaultWIMFileBasedRealm

   USER_ID USER_NAME
---------- ----------------------------------------------------------------
FULL_NAME
--------------------------------------------------------------------------------
PROVIDER
--------------------------------------------------------------------------------

 1 wasadmin
wasadmin
uid=wasadmin,o=defaultWIMFileBasedRealm

 9 deAdmin
deAdmin

   USER_ID USER_NAME
---------- ----------------------------------------------------------------
FULL_NAME
--------------------------------------------------------------------------------
PROVIDER
--------------------------------------------------------------------------------
uid=deAdmin,o=defaultWIMFileBasedRealm


select * from pdwuser.LSW_USR_XREF;

   USER_ID USER_NAME
---------- ----------------------------------------------------------------
PROVIDER
--------------------------------------------------------------------------------
 3 deAdmin


When I checked the raw source of those tables: -

cd /opt/ibm/WebSphere/AppServer/profiles/Dmgr02/dbscripts
fgrep -Ri deadmin *

PCCell1.De1/Oracle/orcl/psuser/createSchema_Standard.sql: 'deAdmin',
PCCell1.De1/Oracle/orcl/psuser/createSchema_Standard.sql: 'deAdmin') ; 


fgrep -Ri wasadmin *

PCCell1.De1/Oracle/orcl/psuser/createSchema_Standard.sql: 'wasadmin',
PCCell1.De1/Oracle/orcl/psuser/createSchema_Standard.sql: 'wasadmin') ; 

DECLARE
v_table_count NUMBER;
  BEGIN
   SELECT COUNT(*) INTO v_table_count FROM
    psuser.LSW_USR_XREF WHERE USER_ID = 9 ;
     IF (v_table_count = 0) THEN
      INSERT INTO
    psuser.LSW_USR_XREF("USER_ID",
 "USER_NAME",
 "FULL_NAME")
VALUES (9,
 'deAdmin',
 'deAdmin') ;

END IF ;
END ;

DECLARE
v_table_count NUMBER;
  BEGIN
   SELECT COUNT(*) INTO v_table_count FROM
    psuser.LSW_USR_XREF WHERE USER_ID = 1 ;
     IF (v_table_count = 0) THEN
      INSERT INTO
    psuser.LSW_USR_XREF("USER_ID",
 "USER_NAME",
 "FULL_NAME")
VALUES (1,
 'wasadmin',
 'wasadmin') ;

END IF ;
END ;


So the moral of the story ….

Whenever one creates a Deployment Environment, use the generated SQL scripts UNLESS you're 100% certain that the WAS/BPM users are the same …..

Doh!!!!

Thursday, 16 August 2018

WebSphere Application Server 7 on Linux - It's been a while

I'm preparing to work with a client upgrading their infrastructure from WebSphere Application Server (WAS) Network Deployment v7, which runs on Java 6, to WAS ND 8.5.5, which runs on Java 8.

As a start, I wanted to install WAS on Linux ….

I have a VM running RHEL 7.4 ( which is a good start, as WAS 7 doesn't formally support that version of RHEL … but it works ).

However, the installation, which I'm obviously running as non-root, failed within seconds with: -

(16-Aug-2018 07:53:41), Process, com.installshield.extras.wizard.condition.AdminCondition, err, Unable to use Security Service
(16-Aug-2018 07:53:49), Process, com.ibm.ws.install.ni.ismp.actions.FeaturePanelControlAction, err, /tmp/normalFeaturePanelControl.xml (Permission denied)
(16-Aug-2018 07:53:49), Process, com.ibm.ws.install.ni.ismp.actions.FeaturePanelControlAction, err, /tmp/normalFeaturePanelControl.xml (Permission denied)
(16-Aug-2018 07:53:49), Process, com.ibm.ws.install.ni.ismp.actions.FeaturePanelControlAction, err, java.io.FileNotFoundException: /tmp/normalFeaturePanelControl.xml (Permission denied)
at java.io.FileOutputStream.<init>(FileOutputStream.java:179)
at com.ibm.ws.install.ni.framework.installtoolkitbridge.UnifiedFileIO.writeFile(UnifiedFileIO.java:83)
at com.ibm.ws.install.ni.framework.io.DiskFileSystem.writeEntry(DiskFileSystem.java:144)
at com.ibm.ws.install.ni.framework.io.DiskFileSystem.writeEntry(DiskFileSystem.java:101)
at com.ibm.ws.install.ni.framework.io.FileSystemEntry.getOutputStream(FileSystemEntry.java:242)
at com.ibm.ws.install.ni.framework.xml.XMLUtils.saveDocument(XMLUtils.java:67)
at com.ibm.ws.install.ni.framework.xml.XMLUtils.saveDocument(XMLUtils.java:49)
at com.ibm.ws.install.ni.ismp.actions.FeaturePanelControlAction.generateControlXML(FeaturePanelControlAction.java:775)
at com.ibm.ws.install.ni.ismp.actions.FeaturePanelControlAction.execute(FeaturePanelControlAction.java:620)
at com.installshield.wizard.StandardWizardListener.execute(StandardWizardListener.java:123)
at com.installshield.wizard.StandardWizardListener.currentBeanChanged(StandardWizardListener.java:106)
at com.installshield.wizard.Wizard$RunThread.run(Wizard.java:1569)


I've unpacked the WAS 7 ND bundle: -

C1G35ML.tar.gz


/tmp/WAS7/ND/WAS/install

as wasadmin.

I'd checked that the file in question - normalFeaturePanelControl.xml - did not exist in the download: -

find /tmp -name normalFeaturePanelControl.xml

and: -

ls -R /tmp | grep -i normalFeaturePanelControl.xml

so I tried to create it: -

touch /tmp/normalFeaturePanelControl.xml

which failed with: -

touch: cannot touch '/tmp/normalFeaturePanelControl.xml': Permission denied

This reminded me of the old days with WAS 7 ( circa 2010-2011 ), and reminded me to do this: -

chmod -R 777 /tmp/

as root.

At which point, the installation ran without problems ……

Ah, what fun !

Docker and 12-factor applications and IBM goodness

One of my team was looking for some inspiration in the context of modernising and refactoring applications using Docker.

I pinged him a few links: -


as a starter for 10 …….

Wednesday, 15 August 2018

Book Review - Kubernetes Management Design Patterns

Another in my infrequent series of reviews for the British Computer Society; this time, it's Kubernetes Management Design Patterns by Deepak Vohtra : -

This book, Kubernetes Management Design Patterns, by Deepak Vohra, is a useful and very in-depth guide into the world of the Kubernetes container cluster management solution.

In part, it's written in the context of the open-source CoreOS operating system, but the content is relevant across a wide range of Linux operating systems, including Ubuntu. The author also takes pains to compare and contrast various container platforms, including Amazon Web Services and Google Cloud Platform.

Whilst each chapter begins with a short problem statement, and then dives into the specific solution, I did find the book to be very focused upon the What and the How, rather than going deep on the Why. This isn't necessarily a bad thing, but it did mean that each chapter involves a lot of copy/paste typing of esoteric commands and scripts.

This is useful, but does mean that the content could become dated rather quickly, as new versions of Kubernetes, plus the dependencies and related platforms, evolve. Given the rapid pace of change in the container market, this is a relatively short-term reality.

Apart from that, my only other critique is that the book requires one to copy/paste and/or type a slew of commands, which has the potential to lead to mistakes and errors. Without a clear understanding of Why something is being done, there is the risk that the audience will fail to fully learn the valuable experiences that this book offers.

In addition, whilst the book makes reference to microservices, in the context of the author's other book on the subject, I feel that this is a serious omission, in terms of the Why of containers, management, orchestration, patterns, governance etc.

With the current focus upon microservices, 12-factor applications, serverless computing, Functions-as-a-Service etc., an opportunity to position Kubernetes at the heart of the debate has, in my view, been missed.

This book definitely adds to the lexicon of material in the arena of container management and orchestration, and should form part of an interested audience's collection. However, I'm not wholly convinced that it strictly adheres to it's title; that is to say, it focuses upon the detail of the subject, rather than the higher-level area of patterns, anti-patterns, good and bad practice etc.

To conclude, I do recommend this book to someone looking for a fairly detailed insight into Kubernetes etc. but would also advise potential readers to look for a more high-level, and perhaps business-oriented, perspective on the benefits and costs of a container management platform.

Given my reservations, I'd give this book 7 out of 10.

Tuesday, 14 August 2018

Adding IBM WebSphere Application Server Plugins to Microsoft Internet Information Server

This threw me for a while this PM.

I'm meddling about with a Windows Server 2012 R2 box, adding the WebSphere Application Server (WAS) Plugin, using Microsoft Internet Information Services (IIS), which is part of W2K12.

Part of the configuration requires one to configure the Internet Services Application Programming Interface (ISAPI) filter into the mix.

And yet I couldn't find it ....

Hey, guess what ... IT WAS NOT INSTALLED

Off to Server Manager, and there it is ....

Nice


Monday, 13 August 2018

Oracle - ORA-00959: tablespace 'LONGSPACE' does not exist

I was cleaning up an Oracle 12c R2 database prior to reinstalling IBM MDM AE 11.5.0.5, and hit a small glitch with two of the tablespaces: -

drop tablespace LongSpace;

drop tablespace LongSpace
*
ERROR at line 1:
ORA-00959: tablespace 'LONGSPACE' does not exist

even though it really does exist: -

select tablespace_name, con_id from cdb_tablespaces;

TABLESPACE_NAME     CON_ID
------------------------------ ----------
SYSTEM 0
SYSAUX 0
UNDOTBS1 0
TEMP 0
USERS 0
IndexSpace 0
LongSpace 0

7 rows selected.


The solution, as ever, was simple: -

drop tablespace "IndexSpace";

Tablespace dropped.

drop tablespace "LongSpace";

Tablespace dropped.

That'll teach me to use mixed-case tablespace names !

Thursday, 9 August 2018

Oracle and IBM MDM and the ORA-28040: No matching authentication protocol

Working through my IBM MDM AE 11.5.0.5 build, against Oracle 12c R2, when I hit this: -

cd /opt/ibm/MDM/AE/mds/scripts
./madconfig.sh Configure_MasterDataManagement


...
Running ODBC SQL statement [select 1 from dual;]...
Executing /opt/ibm/MDM/AE/mds/bin/madsql
/opt/ibm/MDM/AE/mds/bin/madsql: STATE=HY000, CODE=28040, MSG=[InitiateSystems][ODBC Oracle Wire Protocol driver][Oracle]ORA-28040: No matching authentication protocol
Result: 1
Return Code: 1, Time elapsed: 0.099 sec

BUILD FAILED
/opt/ibm/MDM/AE/mds/scripts/build-utils-separation.xml:4563: The following error occurred while executing this line:
/opt/ibm/MDM/AE/mds/scripts/build-utils-separation.xml:99: The following error occurred while executing this line:
/opt/ibm/MDM/AE/mds/scripts/build-utils-separation.xml:1221: The following error occurred while executing this line:
/opt/ibm/MDM/AE/mds/scripts/build-utils-config.xml:1214: ERROR: Unable to connect to the IBM InfoSphere MDM database via ODBC.

Total time: 1 minute 6 seconds

...

so I check the logs: -

ls -al /opt/ibm/MDM/AE/mds/log

total 16
drwxr-xr-x.  2 wasadmin wasadmins  104 Aug  9 13:36 .
drwxr-xr-x. 14 wasadmin wasadmins 4096 Aug  9 13:13 ..
-rw-r--r--.  1 wasadmin wasadmins  160 Aug  9 13:36 mad_ant_register_odbc.log
-rw-r--r--.  1 wasadmin wasadmins  213 Aug  9 13:32 madsql-20180809-133249.mlg
-rw-r--r--.  1 wasadmin wasadmins  213 Aug  9 13:36 madsql-20180809-133641.mlg


cat  /opt/ibm/MDM/AE/mds/log/mad_ant_register_odbc.log

Creating the ODBC.INI configuration file '/opt/ibm/MDM/AE/mds/conf/odbc.ini'..

cat  /opt/ibm/MDM/AE/mds/log/madsql-20180809-133641.mlg

13:36:41 /opt/ibm/MDM/AE/mds/bin/madsql INFO  Version 11.5.0.X, Built: Oct  9 2015 19:10:11
13:36:41 /opt/ibm/MDM/AE/mds/bin/madsql INFO  /opt/ibm/MDM/AE/mds/bin/madsql -nohead -quiet -sqlstmt select 1 from dual;


none of which is particularly revealing.

I even check the generated ODBC.INI file: -

cat /opt/ibm/MDM/AE/mds/conf/odbc.ini

[ODBC]
IANAAppCodePage=4
InstallDir=/opt/ibm/MDM/AE/mds
Trace=0
TraceDll=/opt/ibm/MDM/AE/mds/lib/o6trc27.so
TraceFile=odbctrace.out
UseCursorLib=0

[orcl_E001]
ApplicationUsingThreads=1
ArraySize=256000
CatalogIncludesSynonyms=0
CatalogOptions=0
DefaultLongDataBuffLen=1024
EnableScrollableCursors=0
LockTimeOut=-1
UseCurrentSchema=1
SupportSQLBigInt=1
ColumnsAsChar=1
ColumnSizeAsCharacter=1
Driver=/opt/ibm/MDM/AE/mds/lib/o6ora27.so
HostName=oracle.uk.ibm.com
PortNumber=1521
SID=orcl
CachedCursorLimit=50
CachedDescLimit=0
ServerType=2
WireProtocolMode=2


and then test using the MDM SQL tool ( madsql ): -

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/ibm/MDM/AE/linux/lib/
export MAD_ROOTDIR=/opt/ibm/MDM/AE/mds
export MAD_DBTYPE=oracle
export MAD_CONNSTR="DSN=orcl_E001;UID=mdm2oracle;PWD=passw0rd"
export MAD_CTXLIB=ODBC


/opt/ibm/MDM/AE/mds/bin/madsql

/opt/ibm/MDM/AE/mds/bin/madsql: STATE=HY000, CODE=28040, MSG=[InitiateSystems][ODBC Oracle Wire Protocol driver][Oracle]ORA-28040: No matching authentication protocol

Something I read online suggests that I need to add this: -

SQLNET.ALLOWED_LOGON_VERSION=8

to: -

/home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora

on the Oracle box.

This I do, which moves me onto this: -

Operation failed.
/opt/ibm/MDM/AE/mds/bin/madsql: STATE=28000, CODE=1017, MSG=[InitiateSystems][ODBC Oracle Wire Protocol driver][Oracle]ORA-01017: invalid username/password; logon denied

Just to be on the safe side, I install the unixODBC tool: -

/usr/bin/yum --noplugins install -y unixODBC

which provides the isql utility, and I set up the configuration: -

vi /etc/odbcinst.ini 

[Oracle]
Description = Oracle ODBC Connection
Driver = /home/wasadmin/app/wasadmin/product/12.2.0/client_1/libsqora.so.12.1
Setup =
FileUsage =


vi /etc/odbc.ini 

[Oracle]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = //oracle.uk.ibm.com:1521/orcl
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
UserID = mdm2oracle


but isql also fails to play ball: -

isql -v Oracle

[28000][unixODBC][Oracle][ODBC][Ora]ORA-01005: null password given; logon denied

etc.

I tinker further: -

 isql -v Oracle mdm2oracle passw0rd

and get this: -

[S1000][unixODBC][Oracle][ODBC][Ora]ORA-28000: the account is locked

[ISQL]ERROR: Could not SQLConnect


which is more useful.

I test using the Oracle sqlplus tool: -

sqlplus mdm2oracle/passw0rd@//oracle.uk.ibm.com:1521/orcl

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 9 15:44:20 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-28000: the account is locked


which is VERY revealing.

I checked Oracle: -

sqlplus / as sysdba

select username,account_status from dba_users where username = 'MDM2ORACLE';

As expected, this reported: -

...
USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------
MDM2ORACLE
LOCKED(TIMED)


Thus I unlocked the account AND ensured that it could be used for logon: -

alter user MDM2ORACLE account unlock;
grant connect, resource to MDM2ORACLE;


and tried again.

This time BOTH madsql and isql worked: -

/opt/ibm/MDM/AE/mds/bin/madsql

ODBC connection to Oracle via o6ora27.so Version 07.15.0229 (B0235, U0160) (ODBC 03.52.0000) successful.

Enter SQL Stmt: 


SELECT BANNER FROM v$version;
Stmt[1] = [SELECT BANNER FROM v$version]
BANNER                                                                           
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production     
PL/SQL Release 12.2.0.1.0 - Production                                           
CORE 12.2.0.1.0 Production                                                       
TNS for Linux: Version 12.2.0.1.0 - Production                                   
NLSRTL Version 12.2.0.1.0 - Production                                           
Operation committed. (elapsed = 1 seconds)
Enter SQL Stmt: 


and: -

isql ORACLE mdm2oracle passw0rd

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
SELECT BANNER FROM v$version;
+---------------------------------------------------------------------------------+
| BANNER                                                                          |
+---------------------------------------------------------------------------------+
| Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production    |
| PL/SQL Release 12.2.0.1.0 - Production                                          |
| CORE 12.2.0.1.0 Production                                                      |
| TNS for Linux: Version 12.2.0.1.0 - Production                                  |
| NLSRTL Version 12.2.0.1.0 - Production                                          |
+---------------------------------------------------------------------------------+
SQLRowCount returns -1
5 rows fetched
SQL> 

Right, now back to madconfig.sh …..

Deploying IBM MDM on WAS - Interesting quirk - CWWIM4537E for d9u6m12g7w

So I'm working through the implementation of another IBM Master Data Management (MDM) Advanced Edition (AE) stack, this time using MDM AE 11.5.0.5.

I'm installing it onto WebSphere Application Server (WAS) Network Deployment (ND) 8.5.5.13.

Unlike other products with which I've worked, the installation is a bit more interactive, in that the deployment using IBM Installation Manager (IIM) requires connectivity to both the database ( Oracle in my case ) and to WAS ND.

Therefore, it's necessary to have an existing ( but new ) WAS cell, with a Deployment Manager (DM) and managed node.

Having created my Deployment Manager, and started it, I was working through IIM using it's console mode, and one step: -


requires connectivity to the DM.

This wasn't working, so I checked the DM log: -

cat /opt/ibm/WebSphere/AppServer/profiles/Dmgr01/logs/dmgr/SystemOut.log

and was surprised to see: -

SystemOut.log:[09/08/18 09:45:42:971 BST] 0000010c LTPAServerObj E   SECJ0369E: Authentication failed when using LTPA. The exception is com.ibm.websphere.wim.exception.PasswordCheckFailedException: CWWIM4537E  No principal is found from the 'd9u6m12g7w' principal name..

Given that I'd "told" IIM that the User name was wasadmin, I couldn't see where d9u6m12g7w was coming from.

It seemed to be an internal SOAP user that IIM was sending, for no apparent reason.

In case I'd missed something, I even created a user called d9u6m12g7w in WAS, and gave it full access ( as for the wasadmin user itself ), but then saw this: -

SystemOut.log:[09/08/18 09:49:48:747 BST] 0000010a LTPAServerObj E   SECJ0369E: Authentication failed when using LTPA. The exception is com.ibm.websphere.wim.exception.PasswordCheckFailedException: CWWIM4513E  The password match failed for the 'd9u6m12g7w' principal name..

which kinda makes sense - if IIM is using a "random" user, it's probably also using a "random" password.

I dug around and found this: -


which, although it references an older version of MDM, made some sense.

The solution ( well, I'd call it a circumvention ) is this: -

Resolving the problem

Performing a new installation of InfoSphere MDM v11.4 on WebSphere Application Server v8.5.5.12 using the IBM Installation Manager GUI panels

• Open the WebSphere Application Server Integrated Solutions console (admin console).
• Go to System Administration > Deployment Manager > Java and Process Management > Process definition > Java Virtual Machine > Custom Properties.
• Add the property com.ibm.ws.management.connector.soap.disableSOAPAuthCheck with a value of true.
• Restart the Deployment Manager.

Optional: After the InfoSphere MDM installation is complete, delete the property com.ibm.ws.management.connector.soap.disableSOAPAuthCheck and restart the Deployment Manager.

Having restarted the DM, and retried the IIM step, I get further, although I still see this: -

[09/08/18 10:30:24:097 BST] 00000101 LTPAServerObj E   SECJ0369E: Authentication failed when using LTPA. The exception is com.ibm.websphere.wim.exception.PasswordCheckFailedException: CWWIM4513E  The password match failed for the 'd9u6m12g7w' principal name..
[09/08/18 10:30:24:100 BST] 00000101 RoleBasedAuth A   SECJ0305I: The role-based authorization check failed for admin-authz operation Server:getProcessType.  The user UNAUTHENTICATED (unique ID: unauthenticated) was not granted any of the following required roles: monitor, auditor, configurator, administrator, deployer, operator, adminsecuritymanager.


Weird

Tuesday, 7 August 2018

Oracle - Containers and Pluggables - ORA-65096: invalid common user or role name

Having successfully created my database earlier: -

dbca -silent -createDatabase \
     -templateName General_Purpose.dbc \
     -gdbname bpm856.uk.ibm.com -sid orcl -responseFile NO_VALUE \
     -characterSet AL32UTF8 \
     -sysPassword Qp455w0rd \
     -systemPassword Qp455w0rd \
     -createAsContainerDatabase true \
     -numberOfPDBs 1 \
     -pdbName pdb1 \
     -pdbAdminPassword Qp455w0rd \
     -databaseType MULTIPURPOSE \
     -automaticMemoryManagement false \
     -totalMemory 1536 \
     -storageType FS \
     -datafileDestination "/home/oracle/app/oracle/oradata" \
     -redoLogFileSize 50 \
     -emConfiguration NONE \
     -ignorePreReqs

I attempted to create a few schemas ( schemae ?? ) as part of an IBM BPM 8.5.6 deployment: -

sqlplus / as sysdba

CREATE USER cmnuser IDENTIFIED BY passw0rd;

CREATE USER cmnuser IDENTIFIED BY passw0rd
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

Hmmmmm :-(

This gave me a clue: -


specifically this: -

You must have created the database as a container database. While, you are trying to create user in the container, i.e. CDB$ROOT, however, you should create the user in the PLUGGABLE database.

You are not supposed to create objects in the container, the container holds the metadata for the pluggable databases. You should use the pluggable database for you general database operations. Else, do not create it as container, and not use multi-tenancy.

Looking at the dbca command above, can you see where I went wrong ??

     -createAsContainerDatabase true \

Yep :-)

I had to drop the database and then recreate it: -

dbca -silent -createDatabase \
     -templateName General_Purpose.dbc \
     -gdbname bpm856.uk.ibm.com -sid orcl -responseFile NO_VALUE \
     -characterSet AL32UTF8 \
     -sysPassword Qp455w0rd \
     -systemPassword Qp455w0rd \
     -createAsContainerDatabase false \
     -numberOfPDBs 1 \
     -pdbName pdb1 \
     -pdbAdminPassword Qp455w0rd \
     -databaseType MULTIPURPOSE \
     -automaticMemoryManagement false \
     -totalMemory 1536 \
     -storageType FS \
     -datafileDestination "/home/oracle/app/oracle/oradata" \
     -redoLogFileSize 50 \
     -emConfiguration NONE \
     -ignorePreReqs

and then re-test: -

 sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 7 18:56:11 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
CREATE USER cmnuser IDENTIFIED BY passw0rd;

User created.

SQL> drop user cmnuser;

User dropped.

SQL>
exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

and then continue with my BPM -> Oracle configuration: -

echo "passw0rd" | sqlplus / as sysdba @ /opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/cmnuser/createUser.sql
echo "passw0rd" | sqlplus / as sysdba @ /opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/psuser/createUser.sql
echo "passw0rd" | sqlplus / as sysdba @ /opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/pdwuser/createUser.sql


exit | sqlplus / as sysdba @/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/cmnuser/createSchema_Standard.sql
exit | sqlplus / as sysdba @/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/cmnuser/createSchema_Messaging.sql
exit | sqlplus / as sysdba @/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/psuser/createSchema_Standard.sql
exit | sqlplus / as sysdba @/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/psuser/createProcedure_Standard.sql
exit | sqlplus / as sysdba @/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/pdwuser/createSchema_Standard.sql

Again, like Manuel, I learn!!!!

Oracle and Linux - the gift that keeps on giving

Moving slowly forward, I was busy trying to create my database: -

dbca -silent -createDatabase \
>      -templateName General_Purpose.dbc \
>      -gdbname bpm856.uk.ibm.com -sid orcl -responseFile NO_VALUE \
>      -characterSet AL32UTF8 \
>      -sysPassword Qp455w0rd \
>      -systemPassword Qp455w0rd \
>      -createAsContainerDatabase true \
>      -numberOfPDBs 1 \
>      -pdbName pdb1 \
>      -pdbAdminPassword Qp455w0rd \
>      -databaseType MULTIPURPOSE \
>      -automaticMemoryManagement false \
>      -totalMemory 1536 \
>      -storageType FS \
>      -datafileDestination "/home/oracle/app/oracle/oradata" \
>      -redoLogFileSize 50 \
>      -emConfiguration NONE \
>      -ignorePreReqs


which got oh-so-far (!)

Copying database files
1% complete
2% complete
DBCA Operation failed.
Look at the log file "/home/oracle/app/oracle/cfgtoollogs/dbca/bpm856/bpm8560.log" for further details.


cat "/home/oracle/app/oracle/cfgtoollogs/dbca/bpm856/bpm8560.log" 

[ 2018-08-07 17:31:44.002 BST ] Copying database files
DBCA_PROGRESS : 1%
[ 2018-08-07 17:31:44.093 BST ] ORA-12547: TNS:lost contact

DBCA_PROGRESS : 2%
[ 2018-08-07 17:31:44.222 BST ] Error while cataloging RMAN Backups
[ 2018-08-07 17:31:44.313 BST ] DBCA_PROGRESS : DBCA Operation failed.


I re-ran the relink: -

relink all

and checked the log: -

cat /home/oracle/app/oracle/product/12.2.0/dbhome_1/install/relink.log

/home/oracle/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/config.o: file not recognized: File truncated

make: *** [/home/oracle/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/oracle] Error 1
Error in invoking target 'irman ioracle' of makefile '/home/oracle/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/ins_rdbms.mk'. See '/home/oracle/app/oracle/product/12.2.0/dbhome_1/install/relinkActions2018-08-07_05-34-29-PM.log' for details.

which isn't wrong: -

ls -al $ORACLE_HOME/rdbms/lib/config.o

-rw-r--r-- 1 oracle oinstall 0 Aug  7 15:14 config.o

Following some random online advice (!), I moved the zero byte file out of the way: -

mv $ORACLE_HOME/rdbms/lib/config.o $ORACLE_HOME/rdbms/lib/config.bad

and re-ran the relink: -

relink all

and checked the output: -

cat /home/oracle/app/oracle/product/12.2.0/dbhome_1/install/relink.log

which looked OK …

and then checked the offending file: -

ls -al $ORACLE_HOME/rdbms/lib/config.o

-rw-r--r-- 1 oracle oinstall 1344 Aug  7 17:39 /home/oracle/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/config.o

and now we're golden ……

One day I will understand all of this !

Oracle - "file too short" exception seen when attempting to invoke SQLPlus

So this is a fresh installation of Oracle 12c ( 12.2.0.1.0 ) on a Red Hat Enterprise Linux 7.5 box.

All has gone well …..

until now ….

When I log in as the DBA user: -

su - oracle

and attempt to access SQLPlus, I get this: -

sqlplus 

sqlplus: error while loading shared libraries: /home/oracle/app/oracle/product/12.2.0/dbhome_1/lib/libclntsh.so.12.1: file too short

I'd previously setup my Bash profile: -

vi ~/.bashrc

export ORACLE_HOME=/home/oracle/app/oracle/product/12.2.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin
if [ $?LD_LIBRARY_PATH ]
then
        LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
else
        LD_LIBRARY_PATH=$ORACLE_HOME/lib
fi
export LD_LIBRARY_PATH


and was able to validate that I could see the LD_LIBRARY_PATH variable: -

echo $LD_LIBRARY_PATH

/home/oracle/app/oracle/product/12.2.0/dbhome_1/lib:

and that the offending library was there: -

locate libclntsh.so

/home/oracle/app/oracle/product/12.2.0/dbhome_1/inventory/Scripts/ext/lib/libclntsh.so.12.1
/home/oracle/app/oracle/product/12.2.0/dbhome_1/inventory/backup/2018-08-07_03-13-33PM/Scripts/ext/lib/libclntsh.so.12.1
/home/oracle/app/oracle/product/12.2.0/dbhome_1/lib/libclntsh.so
/home/oracle/app/oracle/product/12.2.0/dbhome_1/lib/libclntsh.so.12.1


ls -al `locate libclntsh.so`

-rw-r----- 1 oracle oinstall 71638263 Aug  7 15:13 /home/oracle/app/oracle/product/12.2.0/dbhome_1/inventory/backup/2018-08-07_03-13-33PM/Scripts/ext/lib/libclntsh.so.12.1
-rwxr-x--- 1 oracle oinstall 71638263 Aug  7 15:13 /home/oracle/app/oracle/product/12.2.0/dbhome_1/inventory/Scripts/ext/lib/libclntsh.so.12.1
lrwxrwxrwx 1 oracle oinstall       17 Aug  7 15:14 /home/oracle/app/oracle/product/12.2.0/dbhome_1/lib/libclntsh.so -> libclntsh.so.12.1
-rwxr-x--- 1 oracle oinstall        0 Aug  7 15:14 /home/oracle/app/oracle/product/12.2.0/dbhome_1/lib/libclntsh.so.12.1


Note that the library - libclntsh.so.12.1 - is actually a symbol link, which is OK, BUT that the original file is zero bytes in length :-(

Following this: -


specifically: -

I relinked the binaries: -

$ORACLE_HOME/bin/relink all

writing relink log to: /home/oracle/app/oracle/product/12.2.0/dbhome_1/install/relink.log

and now have this: -

ls -al `locate libclntsh.so`

-rw-r----- 1 oracle oinstall 71638263 Aug  7 15:13 /home/oracle/app/oracle/product/12.2.0/dbhome_1/inventory/backup/2018-08-07_03-13-33PM/Scripts/ext/lib/libclntsh.so.12.1
-rwxr-x--- 1 oracle oinstall 71638263 Aug  7 15:13 /home/oracle/app/oracle/product/12.2.0/dbhome_1/inventory/Scripts/ext/lib/libclntsh.so.12.1
lrwxrwxrwx 1 oracle oinstall       17 Aug  7 16:54 /home/oracle/app/oracle/product/12.2.0/dbhome_1/lib/libclntsh.so -> libclntsh.so.12.1
-rwxr-xr-x 1 oracle oinstall 71613512 Aug  7 16:54 /home/oracle/app/oracle/product/12.2.0/dbhome_1/lib/libclntsh.so.12.1


which is better :-)

And, even more good news ….

sqlplus 

gives me: -

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 7 16:55:16 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name:


Yay!