When upgrading an Oracle MDB, warning messages in the MDB installation log indicate that seed data could not be loaded for some tables. What steps should be taken to resolve the warning?

Products

• SUPPORT AUTOMATION- SERVER
• CA Service Desk Manager - Unified Self Service
• CA Service Desk Manager
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

Note: The MDB database may be hosted in a SQL Server or Oracle DBMS.  The examples in this document use an Oracle MDB. 

When upgrading an Oracle MDB, warning messages in the MDB installation log indicate that seed data could not be loaded for some tables. 

For example, the following errors may appear in the "install_ORCL.log" file:

06-08,12:00:06 WARN - Insertion of seed data failed
06-08,12:00:06 WARN - java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (MDBADMIN.XAK1CA_LANGUAGE) violated
INSERT INTO CA_LANGUAGE ( "LANG_CODE", "LANG_NAME", "CREATION_USER", "CREATION_DATE", "LAST_UPDATE_USER", "LAST_UPDATE_DATE", "LANGUAGE_UUID", "INACTIVE", "VERSION_NUMBER", "SOURCE_TYPE_ID", "TENANT" ) VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )

The name and location of the MDB installation log file depends on the installation method.

For example, for pre-14.1 versions of CA Service Desk Manager, the setupmdb command may have been run.  When running the setupmdb command manually, it is possible to specify the location of the corresponding MDB installation log file using the MDB_TARGET_DIR parameter.  For example, for CA SDM 12.6 with an Oracle MDB, a command similar to the following could be used:

setupmdb -DBVENDOR=oracle -DBDRIVER=Service_Desk -WORKSPACE=Service_Desk -DBHOST=localhost -DBNAME=ORCL -DBPORT=1521 -ORA_SERVICE_NAME=ORCL -DBUSER=SYS -DBPASSWORD=Password1 -MDB_ADMIN_PSWD=Password1 -ORA_TBLSPACE_PATH="C:\app\oracle\product\12.1.0\client_1" -ORA_DATA_TBLSPACE=MDB_DATA -ORA_INDEX_TBLSPACE=MDB_INDEX -JRE_DIR="C:\SDM12.6\UnzipDVD03162415E\casd.nt\JAVA\JRE" -MDB_TARGET_DIR="C:\temp_MDB_OutputLogDir" -MDB_COMMON_DIR="C:\SDM12.6\UnzipDVD03162415E\casd.nt\MDB\ORACLE"

Review the data that exists in the tables that correspond to the WARN messages.  Determine if some of the data may have been customized.

 

Environment

CA Service Desk Manager (SDM) upgrade.For example, upgrading from CA SDM 12.6 Cumulative patch #3 with Oracle MDB database to CA SDM 14.1.03 with Oracle MDB database using the Swing box method.

Cause

One possible root cause is that the load of the seed data did not complete successfully for an earlier upgrade of the MDB database. And now the latest upgrade is attempting to load the same seed data.

For example, CA_LANGUAGE should appear similar to the following screenshots, which show Data and Constraints tabs for that table:

<Please see attached file for image>

<Please see attached file for image>

 

If the data in the table is as-expected, then you may be able to ignore the WARNing message for the corresponding table.  However, unless it is resolved, the WARNing message is likely to re-occur whenever the MDB is upgraded again, either during an upgrade of the CA SDM product or during the application of a Cumulative patch, rollup, or test patch.  And so, it could be worth resolving the WARNing message as soon as possible.

Resolution

To resolve the WARNing message, one suggested work-around, using an Oracle MDB as an example, would be to follow these steps:

1. Backup the data in the tables that correspond to the "WARN - Insertion of seed data failed" messages. 

Tables with associated seed data can be identified in the MDB installation log file by searching for messages like this one:

06-05,04:09:32 INFO - MDBTools_0303I - Processing seed data package: CA_RESOURCE_STATUS.xml   timestamp: null
 

2. Run OracleDropConstraints.sql to drop the foreign key constraints.

3. For each of the affected tables in step#1, disable all constraints.

For example, using Oracle SQL Developer, open "Tables", scroll to the name of the table, right-click against the table, select "Constraint" and then select "Disable all...".

4. For each of the affected tables in step #1, drop all data from the table.

5. Run the MDB upgrade so that the seed data is loaded into the affected tables.

6. For each of the affected tables in step #1, re-enable all constraints.

For example, using Oracle SQL Developer, open "Tables", scroll to the name of the table, right-click against the table, select "Constraint" and then select "Enable all...".

7. Run OracleAddConstraints.sql.

8. If you need to, but this is unsupported, customize the affected tables as per the backup of the tables that were taken in step #1.

Additional Information

Upgrade CA SDM Using the Swing Box Method

Drop and Restore Constraints