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.

A New poll on patching

For patching your multiple Oracle databases, would you Use Grid Control Patch Deployment Procedures to automate, or do things the old-fashioned way? Or a bit of both? I have put a new poll on, go ahead and vote.

POC findings: Offline Patching of Previous CPUs and Modifying Reports

We were just going through a POC for Enterprise Manager Grid Control recently in a large client, and had some interesting results I like to share with everyone.

This particular client will not connect the Grid Control management service (OMS) to the internet for security reasons. This means the Refresh from Metalink job has to be run manually.

This has to be done after downloading certain xml files from Metalink (which contain the Patch Analysis metadata ie. which patches are available and what bugs they fix) and uploading these files, as per the following "Grid Automation Deployment Procedures" document on the Oracle OTN website:

http://www.oracle.com/technology/products/oem/pdf/grid-automation-deployment-procedures.pdf

After this the patch advisory on the Grid Control Home page rightly suggested that for a 9.2.0.1 home, the 9.2.0.8 patch as well as the October CPU had to be applied. Since the xml files had been downloaded manually from Metalink in December, the October CPU was the latest and therefore recommended to be applied.

The client wanted the earlier July CPU to be recommended instead. They said they rarely apply the latest patch but instead apply the earlier CPU patch. Now, the only way to do this would be to download the xml files from Metalink each month and store them on separate directories somwhere, but only upload the xml files to Grid Control as per the CPU you would like to be recommended by the Patch Advisory. For eg, if you want the Jul CPU to be recommended, then only upload the Jul or August or Sep xmls.

They also wanted a report to show all the patches applied on all their databases, showing each database one by one. The report as suggested was not fully available off-the-shelf in the reporting facility in Grid Control. The closest we come to this, is in this report:

Reports..Oracle Home Patch Advisories..Applied Interim Patches

This shows the patch, bugs fixed, installation time, Host, Home directory and Platform. It doesn’t show the databases, the reason being that the patch is actually at the Oracle Home level and not at the database level. This report may be appropriate for their needs, however if they wish to add in the database name, it is possible to modify the report by creating a copy andthen modifying the Sql statement. Oracle has mgmt$ views from which the info may be extracted.

Happy New Year and 11g Grid Control

This is the Blog's first post for the new year, so let me wish a Very Happy New Year 2009 to all the blog readers. All the Very Best for the New Year.

In 2009 I am looking forward to the release of 11g Grid Control, although I have no idea when it will be released. Hopefully, soon, this year.