VMware Cloud Community
Candell
Contributor
Contributor
Jump to solution

Database upgrade 4.0 to 4.1 fails on Oracle 10.2.0.4

Hi all!

Since we need to go from 32-bit to 64-bit operating system for the new vCenter Server, I did a complete reinstall of the server today (keeping the database), and went from 2003 SP2 to Windows 2008 R2 x64. On this is installed the 64-bit Oracle client 10.2.0.4, with the 10.2.0.4 ODBC-client (verified as also being 64-bit, even though it's oddly named sqora32.dll).

The problem is that the database upgrade never completes when I try to install vCenter Server 4.1, and exits with a "Exception thrown while executing sql script". Checking the log file I see that it's process_temptable0_proc_oracle.sql that causes the error, and when I open the procedure in oracle enterprise manager, I see that the error is "ORA-12704: character set mismatch". Since the database resides on a separate cluster, the old character set is instact. The NLS_LANG on the vCenter server is set to the same as the old one (matching the database).

Anyone got any ideas how to proceed from here?

Thanks,

Anders

Reply
0 Kudos
1 Solution

Accepted Solutions
M0rph77
Enthusiast
Enthusiast
Jump to solution

vmware support came up with the following kb: http://kb.vmware.com/kb/1026331

make sure to backup the database

I'll post my results soon

View solution in original post

Reply
0 Kudos
9 Replies
Candell
Contributor
Contributor
Jump to solution

Thought I'd fill in with more findings. There seems to be some suspect lines in the process_temptableX_proc procedures for Oracle, where they try to update a NVARCHAR2(255) field with data from a VARCHAR2(255) without explicit cast.

It is the following two statements, that read data from VPX_TEMPTABLE0.DEVICE_NAME (nvarchar2(255)) and writes to VPX_DEVICE.DEVICE_NAME (varchar2(255)). Corresponding statements is present in process_temptable1_proc and process_temptable2_proc.

INSERT INTO VPX_DEVICE

(DEVICE_ID,

DEVICE_NAME

) SELECT VPX_DEVICE_SEQ.NEXTVAL, DEVICE_NAME

FROM (SELECT DISTINCT t1.DEVICE_NAME FROM

VPX_TEMPTABLE0 t1 MINUS SELECT nvl(t2.DEVICE_NAME, 'NULL') FROM VPX_DEVICE t2);

and

INSERT INTO VPX_STAT_COUNTER

(COUNTER_ID,

ENTITY_ID,

STAT_ID,

DEVICE_ID) SELECT VPX_STAT_COUNTER_SEQ.NEXTVAL,

c.ENTITY_ID, c.STAT_ID, d.DEVICE_ID from

(SELECT ENTITY_ID, DEVICE_NAME, STAT_ID FROM VPX_TEMPTABLE0 GROUP BY ENTITY_ID, DEVICE_NAME, STAT_ID

MINUS

SELECT ENTITY_ID, DEVICE_NAME, STAT_ID FROM VPXV_DEVICE_COUNTER)

c, VPX_DEVICE d WHERE c.DEVICE_NAME = nvl(d.DEVICE_NAME, 'NULL');

By adding some casts it's possible to make the procedures compile, and also, by changing the scripts in the installation media the entire installation processes without errors. My new statements read:

INSERT INTO VPX_DEVICE

(DEVICE_ID,

DEVICE_NAME

) SELECT VPX_DEVICE_SEQ.NEXTVAL, DEVICE_NAME

FROM (SELECT DISTINCT cast(t1.DEVICE_NAME as varchar2(255)) DEVICE_NAME FROM

VPX_TEMPTABLE0 t1 MINUS SELECT nvl(t2.DEVICE_NAME, 'NULL') FROM VPX_DEVICE t2);

and

INSERT INTO VPX_STAT_COUNTER

(COUNTER_ID,

ENTITY_ID,

STAT_ID,

DEVICE_ID) SELECT VPX_STAT_COUNTER_SEQ.NEXTVAL,

c.ENTITY_ID, c.STAT_ID, d.DEVICE_ID from

(SELECT ENTITY_ID, cast(DEVICE_NAME as varchar2(255)) DEVICE_NAME, STAT_ID FROM VPX_TEMPTABLE0 GROUP BY ENTITY_ID, DEVICE_NAME, STAT_ID

MINUS

SELECT ENTITY_ID, DEVICE_NAME, STAT_ID FROM VPXV_DEVICE_COUNTER)

c, VPX_DEVICE d WHERE c.DEVICE_NAME = nvl(d.DEVICE_NAME, 'NULL');

To me, this feels like a genuine bug in the scripts, and now all I need is a statement from VMware that this is a supported solution.

Reply
0 Kudos
Nixia
Contributor
Contributor
Jump to solution

Hi Anders,

Your conditions match mine perfectly (2003 SP2/2008R2x64/10.2.0.4). I got the exact same error "Exception thrown while executing sql script" but then on running the upgrade a second time it completed, with the only noticeable faults being no performance monitoring data or license tracking data going into the database. Unfortunately I did not check the logs so cannot confirm that the underlying fault is the same.

I have lived with it for a while as everything important (vmotion and VCB) works perfectly but I did log a call for this today and have referred them to your post as well.

I did not troubleshoot in depth as I will just rebuild VC from scratch if it comes to that. I know nothing about databases but at the moment I strongly suspect that I am merely missing the required tables (if thats the correct term) to store the performance and license usage data.

I hope vmware support can point us in the right direction.

Reply
0 Kudos
Candell
Contributor
Contributor
Jump to solution

Hi!

Glad I'm not alone, but of course also unfortunately that more people are affected.

The reason the installation works fine the second time, is that the database upgrade scripts actually starts with updating the version-table, so even if it fails the database is still tagged as being version 4.1. Next time the upgrade scripts finds the database being seemingly up to date, and exits happily. Too bad half of the 4.1-stuff isn't implemented.

I don't know the reason for tagging the database as 4.1 at the start. I'd do this last, when everything else is competed successfully.

/Anders

Reply
0 Kudos
M0rph77
Enthusiast
Enthusiast
Jump to solution

Hello,

I'm having the same issue @ the database upgrade step, with procedure "process_temptable0_proc"

We'd had to restore the database and roll back to vcenter 4.0U1.

We're using Oracle 11.1 with the 11.1.0.06 ODBC driver.

Characterset database is: AMERICAN_ AMERICA. UTF8

Select * from dba_errors returns the following result:

PROCESS_TEMPTABLE0_PROC PROCEDURE 1 1 506 PL/SQL: ORA-12704: character set mismatch. ERROR 0

PROCESS_TEMPTABLE0_PROC PROCEDURE 2 1 392 PL/SQL: SQL Statement ignored ERROR 0

PROCESS_TEMPTABLE0_PROC PROCEDURE 3 1 1141 PL/SQL: ORA-12704: character set mismatch. ERROR 0

PROCESS_TEMPTABLE0_PROC PROCEDURE 4 1 971 PL/SQL: SQL Statement ignored ERROR 0

PROCESS_TEMPTABLE1_PROC PROCEDURE 1 18 22 PL/SQL: ORA-12704: character set mismatch ERROR 0

PROCESS_TEMPTABLE1_PROC PROCEDURE 2 12 3 PL/SQL: SQL Statement ignored ERROR 0

PROCESS_TEMPTABLE1_PROC PROCEDURE 3 46 7 PL/SQL: ORA-12704: character set mismatch ERROR 0

PROCESS_TEMPTABLE1_PROC PROCEDURE 4 37 3 PL/SQL: SQL Statement ignored ERROR 0

I'm not a Oracle DBA, but our DBA is goint to look into the matter. I also will open a support call. But I suspect the resolution is finding the compatible character set.

Reply
0 Kudos
Candell
Contributor
Contributor
Jump to solution

I went on and installed with the hack to the sql-files I described above, and installation had no problems. We really wanted to have some kind of answer from VMware if this is supported or not, but could not get anything clear via our support (we have support via hp, and not VMware directly). Since we're in a hurry, we had no choice but to move on.

New issues are that the tomcat-part of the installation are having problems connecting to the database (or something). They apparantly use another method of connecting than the rest of the application. Symptoms are missing performance graphs in the client, and Storage View have a error.

VMware's compability testing of 64-bit oracle for vCenter doesn't seem overly thorough. Another example is the "Agent Pre-upgrade Check", which doesn't even run, because it looks for connection parameters in the 32-bit ODBC registry settings, which there aren't any if you're running 64-bit ODBC (which you're supposed to do for vCenter 4.1).

Reply
0 Kudos
M0rph77
Enthusiast
Enthusiast
Jump to solution

We're a little hesitant about hacking the sql files ourselves so we'll keep playing it safe for now. But I can imagine that if you know what you're doing and you can check if the database changes are constructed correctly, you have a quick fix for now.

You are right that VMware seems a bit lacking in documenting / testing different Oracle database settings.

What I am curious about, is if it IS a "character set" setting, and wich character set is compatible.

Regarding tomcat; have you allocated enough memory for the RAM hungry java process? (off topic.. we have a lot issues with the perfomance of java using vm's in combination with ballooning.. placing more physical RAM seemed to be the only good solution)

Regarding the agent pre update check; yes you're right. I've also encountered that nice little detail.. ok.. perhaps it doesn't matter for checking the host agents. but it does say in the documentation that a 64bit DSN is required..

Reply
0 Kudos
M0rph77
Enthusiast
Enthusiast
Jump to solution

vmware support came up with the following kb: http://kb.vmware.com/kb/1026331

make sure to backup the database

I'll post my results soon

Reply
0 Kudos
M0rph77
Enthusiast
Enthusiast
Jump to solution

yes, the kb article mentioned did resolve the db update issue!

asked support to update the release notes

:smileygrin:

Reply
0 Kudos
Candell
Contributor
Contributor
Jump to solution

Same result here. Installation went fine. Case closed! Smiley Happy

I still have problems with performance data and the storage view's though. I wonder if these parts try to use the 32-bit ODBC. I'll have to try to find a way to install the 32-bit ODBC on 64-bit Oracle anyway, since Update Manager seems to need it anyway.

Reply
0 Kudos