Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
Highlighted
MarkoVlaovic Acclaimed Contributor.
Acclaimed Contributor.
479 views

Upgrade from 2019.02 to 2019.05 fails - ORA-00904: "A_DATA_TAGS": invalid identifier

Jump to solution

HI

while performing an upgrade from windows UCMDB 2019.02 (Oracle DB) to 2019.05 we got a lot (1000+) of errors:

java.sql.SQLSyntaxErrorException: [mercury][Oracle JDBC Driver][Oracle]ORA-00904: "UNIX_1"."A_SD_TYPE": invalid identifier

or

...ORA-00904: "A_DATA_TAGS": invalid identifier

...ORA-00904: "UNIX_1"."A_UNIX_SERVICEPACK": invalid identifier

One full error:

Can't execute prepared statement [SELECT UNIX_1.CMDB_ID, UNIX_1.A_DATA_NOTE, UNIX_1.A_DEFAULT_GATEWAY_IP_ADDRESS, UNIX_1.A_HOST_OSINSTALLTYPE, UNIX_1.A_HOST_OSRELEASE, UNIX_1.A_OS_VENDOR, UNIX_1.A_OS_FAMILY, UNIX_1.A_DISCOVERED_OS_NAME, UNIX_1.A_DISCOVERED_OS_VERSION, UNIX_1.A_UNIX_SERVICEPACK, UNIX_1.A_SWAP_MEMORY_SIZE, UNIX_1.A_MEMORY_SIZE, UNIX_1.A_DISCOVERED_OS_VENDOR, UNIX_1.A_DISCOVERED_MODEL, UNIX_1.A_DOMAIN_NAME, UNIX_1.A_VENDOR, UNIX_1.A_DISCOVERED_VENDOR, UNIX_1.A_NODE_FAMILY, UNIX_1.A_PAE_ENABLED, UNIX_1.A_NODE_MODEL, UNIX_1.A_OS_ARCHITECTURE FROM UNIX_1 WHERE UNIX_1.CMDB_ID IN (hextoraw(?)); Values: <4a4cd0fc47aabf97b24ba48a942eb4cf>], due to exception: java.sql.SQLSyntaxErrorException: [mercury][Oracle JDBC Driver][Oracle]ORA-00904: "UNIX_1"."A_UNIX_SERVICEPACK": invalid identifier

 

 

Somebody else had this? what is the cause?

tnx, Marko

Tags (2)
0 Likes
1 Solution

Accepted Solutions
Micro Focus Contributor
Micro Focus Contributor

Re: Upgrade from 2019.02 to 2019.05 fails - ORA-00904: "A_DATA_TAGS": invalid identifier

Jump to solution

Hi,

It is documented into the deployment guide, online version:

https://docs.microfocus.com/UCMDB/2019.05/ucmdb-docs/docs/eng/pdfs/Deployment_Guide/Guide/UCMDB_Deployment.htm

 

  1. (Oracle version 12.1.0.1 and lower only) If you upgraded UCMDB server from an earlier version with Oracle version 12.1.0.1 (or lower), make sure you open a support case to request and apply the hotfix related to issue in QCCR1H126420 (After upgrade to 2019.05, startup fails at data acquisition step: java.sql.SQLSyntaxErrorException: [mercury][Oracle JDBC Driver][Oracle]ORA-00904: "VMWARE_RESOURCE_POOL_1"."A_DATA_TAGS": invalid identifier).

 

The hotfix needs to be applied before the first startup of the server, after the upgrade. Otherwise it will not work.

Regards,

 

Diana

 

View solution in original post

10 Replies
Micro Focus Expert
Micro Focus Expert

Re: Upgrade from 2019.02 to 2019.05 fails - ORA-00904: "A_DATA_TAGS": invalid identifier

Jump to solution

In UCMDb 2019.05 we added the tags attribute for almost all of the CITs.

Most likely during the upgrade and deployment of the newer Content Pack, something failed and this tag wasn't properly transmitted in the dataModel.
From JMX you can run rebuildBDmodelAndViews on preview to see on what CITs is this attribute missing.

Kind regards,
Bogdan Mureșan
EMEA CMS Technical Success
0 Likes
MarkoVlaovic Acclaimed Contributor.
Acclaimed Contributor.

Re: Upgrade from 2019.02 to 2019.05 fails - ORA-00904: "A_DATA_TAGS": invalid identifier

Jump to solution

Hi John, thanks

Meanwhile, digging deeper i find in the release notes that some new JSON stuff attributes are to be added to the CI, meaning the tables need to be expanded. Right.

Also, it says this will go fine on Oracle 12+ versions.

We have Oracle 11.2 version.

Could this be the reason?

https://docs.microfocus.com/itom/Universal_CMDB:2019.05/AWSTagsSupport

...mentions Oracle versions that support JSON, but it is not clear what happens if Oracle is 11.2

P.S UCMDB support matrix lists 11.2 as SUPPORTED...so i'm confused...

Micro Focus Expert
Micro Focus Expert

Re: Upgrade from 2019.02 to 2019.05 fails - ORA-00904: "A_DATA_TAGS": invalid identifier

Jump to solution

It could be that it failed due to the unsupported Oracle version.

Did you check the 2019.05 Support matrix for DBs?

Kind regards,
Bogdan Mureșan
EMEA CMS Technical Success
0 Likes
MarkoVlaovic Acclaimed Contributor.
Acclaimed Contributor.

Re: Upgrade from 2019.02 to 2019.05 fails - ORA-00904: "A_DATA_TAGS": invalid identifier

Jump to solution

Yes, i did check, it says it's suported.

one more thing, since errors were storming, error.log was rolling over so in some other error.logs from the same upgrade process, this one also occured a lot:

 

Caused by: com.mercury.topaz.cmdb.server.manage.dal.CmdbDalException: Can't execute statement [ALTER TABLE CDM_SOLARIS_ZONE_CONFIG_1 add A_DATA_TAGS VARCHAR2(4000) NULL CONSTRAINT JSON_CDM_SOLARIS_279305306 CHECK (A_DATA_TAGS IS JSON WITH UNIQUE KEYS)], due to exception: java.sql.SQLException: [mercury][Oracle JDBC Driver][Oracle]ORA-00908: missing NULL keyword

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Upgrade from 2019.02 to 2019.05 fails - ORA-00904: "A_DATA_TAGS": invalid identifier

Jump to solution

OK and the JMX output for the rebuild method what did it say?

Kind regards,
Bogdan Mureșan
EMEA CMS Technical Success
0 Likes
MarkoVlaovic Acclaimed Contributor.
Acclaimed Contributor.

Re: Upgrade from 2019.02 to 2019.05 fails - ORA-00904: "A_DATA_TAGS": invalid identifier

Jump to solution

hi

sorry we have brought back previous version from the backup, and we plan to migrate the DB to Oracle 12.x first and then try the upgrade again.

We believe this issue is due to JSON expansions on the DB tables, if the DB is not 12.x

we'll give it another try and then update here

marko

 

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Upgrade from 2019.02 to 2019.05 fails - ORA-00904: "A_DATA_TAGS": invalid identifier

Jump to solution

It could be due to the JSON changes. 
Either way, you had to do an Oracle upgrade so this could be a good opportunity for this.

Kind regards,
Bogdan Mureșan
EMEA CMS Technical Success
0 Likes
bmceachin Trusted Contributor.
Trusted Contributor.

Re: Upgrade from 2019.02 to 2019.05 fails - ORA-00904: "A_DATA_TAGS": invalid identifier

Jump to solution

Was there a resolution for this?  I'm receiving the same error after 10.33 to 2019.05 (but only in the CustID 100001, not CustID 1).  This field doesn't exist in the table.  I don't think it's DB version related because I just upgraded another environment from .02 to 2019.05, and they use the same Oracle version 11.2.0.3.0

 

0 Likes
Micro Focus Contributor
Micro Focus Contributor

Re: Upgrade from 2019.02 to 2019.05 fails - ORA-00904: "A_DATA_TAGS": invalid identifier

Jump to solution

Hi,

It is documented into the deployment guide, online version:

https://docs.microfocus.com/UCMDB/2019.05/ucmdb-docs/docs/eng/pdfs/Deployment_Guide/Guide/UCMDB_Deployment.htm

 

  1. (Oracle version 12.1.0.1 and lower only) If you upgraded UCMDB server from an earlier version with Oracle version 12.1.0.1 (or lower), make sure you open a support case to request and apply the hotfix related to issue in QCCR1H126420 (After upgrade to 2019.05, startup fails at data acquisition step: java.sql.SQLSyntaxErrorException: [mercury][Oracle JDBC Driver][Oracle]ORA-00904: "VMWARE_RESOURCE_POOL_1"."A_DATA_TAGS": invalid identifier).

 

The hotfix needs to be applied before the first startup of the server, after the upgrade. Otherwise it will not work.

Regards,

 

Diana

 

View solution in original post

Micro Focus Contributor
Micro Focus Contributor

Re: Upgrade from 2019.02 to 2019.05 fails - ORA-00904: "A_DATA_TAGS": invalid identifier

Jump to solution

Just to add an addendum to this solution. If you are on an Oracle version that you would not expect this issue to occur e.g. 12.2 then please check you have not got the "compatible" parameter set to a lower value. If you do have this set lower then Oracle may also not allow the JSON updates to occur. You can either use the same hotfix or set the "compatible" parameter to match that of Oracle.

You can check this by using the following SQL:

SELECT name, value FROM v$parameter
WHERE name = 'compatible';

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.