Friday, July 18, 2008

Timezone in OEM ?

I am facing a strange problem. Three days ago I tried to run a report in OEM, a database Availability History report. I have to run this report every month for a customer, but this time the output showed an error !

The report showed 'Error rendering element. Exception: ORA-00904: "MGMT_VIEW_UTIL"."ADJUST_TZ": invalid identifier'


Checking Metalink didn't help me, it seems the error has something to do with a timezone ? It's true that the OSM and the target agent have different timezones, both Europe but different counties. But this has always been the case, so why suddenly an error ?? Could it have something to so with the recent export/import of the sysman schema ?

I tried to change the timezone of the target agent by adjusting the emd.properties in the $AGENT_HOME. That did not solve the problem, even worse...the agent did not want to start anymore. I canhed the parameter back to the old timezone, hoping this would solve the problem. Reloaded the agent, bounced the oms and database. This time the agent started, but after a few seconds, when trying to upload data, the agent was killed.
Checking the emagent.trc file for errors showed me the following :

2008-07-15 16:59:48 Thread-2824600480
WARN upload: FxferSend: received fatal error in header from repository: http://amssys07.controllan.nl:4889/em/upload/FATAL_ERROR::500ORA-20603: Updating timezone region of a target is not supported. Use mgmt_target.set_agent_tzrgn() API to modify the timezone region.(target name = oebs20.control.publicsoft.nl:1830)(target type = oracle_emd)(new tzrgn = Europe/Amsterdam)(old tzrgn = Europe/Warsaw)ORA-06512: at "SYSMAN.CHECK_DUPLICATE_TARGETS", line 92ORA-04088: error during execution of trigger 'SYSMAN.CHECK_DUPLICATE_TARGETS'

It seems the timezone change was still in proces, even after changing the emd.properties again.
The following steps solved the agent problem...

oemagent@oebs20:(bin)> ./emctl resetTZ agentOracle Enterprise Manager 10g Release 10.1.0.5.0Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.Updating /oebs/oemagent/10.1.0/sysman/config/emd.properties...Time zone set to Europe/Warsaw.
To complete this process, you must connect to the Grid Control repository as user sysman, and execute: SQL>exec mgmt_target.set_agent_tzrgn('oebs20.control.publicsoft.nl:1830','Europe/Warsaw')


The agent did start after this, but the report still showed the same error !!!

6 comments:

Peter said...

Hi Bas,

as far as I know, the ORA-00904 error means that an unknown object is called.
ORA-00904 is (originally) raised when a nonexistent column is used in a statement. It also occurs when a method is called that cannot be found within the specific session. The way you described the problem does not have anything to do with timezone differences. the package "MGMT_VIEW_UTIL" simply cannot be found. Please check if this package is installed and accessable to the user that executes the call.

greetings,
Peter Boekelaar ;-)

Bas Klaassen said...

Hi Peter,

the package is installed and the user running the report does have execute privilege on the package.
I am still searching.... :)

jpiwowar said...

Hi Bas,

Any chance that the problem lies with an object referenced within the code for ADJUST_TZ? Perhaps a grant or synonym that was lost when you did the export/import? I don't have a grid control instance to investigate, so I don't know if it's possible to see the ADJUST_TZ code.

Regards,

John P.

Bas Klaassen said...

Must be something like that....I am looking into this code right now.

Marco Gralike said...

Bas, did you try to get more info via the emdiag kit (metalink note 421053.1)

Run "repvfy test -level 9 -detail" on OMS to get more log detail.

Bas Klaassen said...

Hi Marco,

I didn't yet...But I have the emdiag installed, so I will give it a try