An article on using Grid Control for ITIL compliance

Just found an article written by David Yahalom:

"Using Grid Control for ITIL compliance"
http://www.davidyahalom.com/index.php/proactive-monitoring-with-oracle-grid-control/

Great article, he ends it with:
"And remember…. Once you go Grid, you’ll never go back!"

Voted leader of the Oracle RAC SIG (Special Interest Group) in Singapore

Today I was voted leader of the Oracle RAC SIG (Special Interest Group) in Singapore.
My name was proposed by RaviShankar Buddha of Oracle, seconded by Ashish Agarwal of
DBCON and accepted by the others. Thanks guys.

This means I will co-ordinate and conduct a RAC Round Table every month
in Singapore where clients using RAC are invited to share their experiences and
issues with RAC, like a peer discussion. At times presentations on RAC and
related technology can be held by Oracle or by any of us.

To my surprise I found most of the RAC users who came to the round table today
were not using Grid Control, only one of them was. RAC is very complicated and to manage it efficiently, we need good management software like Grid Control. For eg, rolling patch upgrades can be applied using the deployment procedures in Grid Control.

Recently Published Article on OTN

Please read "Grid Control Architecture for Very Large Sites":

http://www.oracle.com/technology/pub/articles/havewala-gridcontrol.html

This is an article of mine that was published today on OTN.

POC: Customized Patch Reports for Grid Control

This is part of the Grid Control POC we recently arranged for a large client.


As per the earlier post on the issue of patch reports, the supplied out-of-box
reports show the patch, bugs fixed, installation time, Host, Home directory
and Platform. The reports do not show the databases, the reason being that
the patch is actually at the Oracle Home level and not at the database level.
If you wish to add in the database name, it is possible to modify the report by
creating a copy and then modifying the Sql statement. For example the
above report is from this statement that can be modified:


SELECT distinct
patch as PATCH,
bugs as BUGS,
installation_time as TIMESTAMP,
host as HOST,
home_location as HOME_DIRECTORY,
platform as PLATFORM
from mgmt$applied_patches patch,
mgmt$em_homes_platform home,
mgmt$target tgt
where home.HOME_ID = patch.CONTAINER_GUID
and patch.target_guid = tgt.target_guid
and patch.installation_time>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
and patch.installation_time<= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)

You can join this to MGMT$target_components which would have the same home as the mgmt$applied_patches home,
and then select the targets that are databases in this home.

SELECT distinct
patch as PATCH,
installation_time as TIMESTAMP,
host as HOST,
tgtcomp.target_name,
tgtcomp.target_type,
patch.home_location as HOME_DIRECTORY,
patch.home_name as HOME_NAME,
platform as PLATFORM
from mgmt$applied_patches patch,
mgmt$em_homes_platform home,
mgmt$target tgt,
mgmt$target_components tgtcomp
where home.HOME_ID = patch.CONTAINER_GUID
and patch.target_guid = tgt.target_guid
and tgtcomp.home_name = patch.home_name
and tgtcomp.target_type = 'oracle_database'
and patch.installation_time>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
and patch.installation_time<= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)


This is just an example of the customization that is possible.


How do we know what info is in the Grid Control Repository? We recommend using the new Sql Developer
Data Modelling utility from Oracle. This is downloadable from the Oracle Technical Network (OTN) and currently
installs as a stand-alone utility, but will be incorporated into a new release of Sql Developer in the not-too-distant
future. ( Sql Developer keeps getting better and better, just like Grid Control ! )


The Data Modelling utility allows reverese engineering of an existing Oracle Database, you can access
this via the menu. Select File>Import>Data Dictionary. In this way, you can import form Oracle 9,10
and 11, MS SQL Server 2000 and 2005, DB2/390 7 and 8, DB2/UDB 7 and 8.


Use this to reverse engineer the SYSMAN schema in the Grid Control repository, especially the MGMT$ views.
Then you can understand what info is available.


As a test of the customization:

After applying database patch 7210195 via Grid Control to our FINPRD1 and FINDW1 database home,
we created a Grid Control customized report "Applied Interim Patches (Customized - Database Level)".


The Sql used in this report is:


SELECT distinct
patch as "Applied Interim Patch",
installation_time as "Time Applied",
tgtcomp.target_name as "Database Target Name",
patch.home_location as "Oracle Home Directory",
patch.home_name as "Oracle Home Name",
host as Host
from mgmt$applied_patches patch,
mgmt$em_homes_platform home,
mgmt$target tgt,
mgmt$target_components tgtcomp
where home.HOME_ID = patch.CONTAINER_GUID
and patch.target_guid = tgt.target_guid
and tgtcomp.home_name = patch.home_name
and tgtcomp.target_type = 'oracle_database'
and patch.installation_time>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
and patch.installation_time<= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
order by installation_time desc


As you can see, we have changed the headings of the field columns in the report, and we also changed the styled text in the
Report definition from “INSTR_APPLIED_PATCHES_ALL_HOSTS” to the customized line:


“The report shows the interim patches applied on Oracle Databases across all the hosts
in the last 31 days. Use the time period selector to view the interim patches applied within a time-period. Thanks.”


We also created a Report: "Applied Interim Patches (Customized - Database Level and Select which Database)" that does the above and also allows you to select which database.


The Sql statement for this is:


SELECT distinct
patch as "Applied Interim Patch",
installation_time as "Time Applied",
tgtcomp.target_name as "Database Target Name",
patch.home_location as "Oracle Home Directory",
patch.home_name as "Oracle Home Name",
host as Host
from mgmt$applied_patches patch,
mgmt$em_homes_platform home,
mgmt$target tgt,
mgmt$target_components tgtcomp
where home.HOME_ID = patch.CONTAINER_GUID
and patch.target_guid = tgt.target_guid
and tgtcomp.home_name = patch.home_name
and tgtcomp.target_type = 'oracle_database'
and tgtcomp.target_guid = ??EMIP_BIND_TARGET_GUID??
and patch.installation_time>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
and patch.installation_time<= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
order by installation_time desc


The line which selects the database target selected is “and tgtcomp.target_guid = ??EMIP_BIND_TARGET_GUID??”


Regards,


Porus Homi Havewala
(Oracle ACE Director)
Principal Consultant,
S & I Systems Pte Ltd.

Mentioned in Oracle's Dev2DBA Newsletter (Feb 2009)

The Grid Control Blog has been mentioned in the Feb 2009 edition of 

Oracle's Dev2DBA Newsletter (previously called Oracle Techblast), 

in the ACE Watch section as follows:

Visit Oracle ACE Director Porus Hami Havewala's blog for some interesting findings from a Grid Control POC he recently conducted for a large client.

To subscribe to Oracle Newsletters such as this popular newsletter, 

visit the following link:

http://www.oracle.com/newsletters/index.html?msgid=7360639#tech

More attempts to release the oci.dll lock

As a follow-up to the oci.dll locking issue and the use of unlocker that I explained

in an earlier blog post, when I conducted a session on "Patching with Grid Control",

a DBA suggested there was a Windows fix avaiable for the oci.dll locking issue.

I found Metalink Note 232827.1 which suggested that the svchost process

has the oci.dll locked on the machine. The note asked to shutdown Windows

services such as the Com+ Event System, MSDTC, and IIS. After

those services are shutdown the machine has to be rebooted in order for the

svchost process to release the oci.dll. The note suggested that these services

lock the oci.dll even though the user may not be using them.

I followed these steps, but it didnt make a difference. The ocii.dll

stayed locked even after the listeners were stopped.

The DBA then suggested to set the following registry key in Windows:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer]

AlwaysUnloadDLL=1

I already had this registry key set. so this was not the solution. I had to

reply on the previous unlocker strategy (see the previous blog post).

A further bit of reading suggested that Windows keeps dlls in memory on

purpose for a certain period of time even after the dll is not is use. This is

for performance reasons.

I do wonder why Unix has no such issues and the performance is still ok

in unix without such "hold things in memory for some indefinite time"

tactics. We never have such locking issues on unix.

A side note on this issue: when I removed and reloaded the registry key

"AlwaysUnloadDLL=1" into the registry, from then on whenever I tried to

start the Grid Control Management service, it failed to load. I remembered

reading somewhere on the internet that this registry key may be causing

a Java load issue, so I deleted the "AlwaysUnloadDLL=1" key straight

away, and the Grid Control management service started loading fine.

Whew!

Windows - you can love it, you can hate it.