Monday, 4 June 2012

EPM Shared Services Registry cleaner

No doubt you aware of the EPM Shared Services registry but if you are a little unsure on what it is all about then here is the official Oracle description.

The Shared Services Registry is part of the database that you configure for Foundation Services. Created the first time that you configure EPM System products, the Shared Services Registry simplifies configuration by storing and reusing the following information for most EPM System products that you install:

•    Initial configuration values such as database settings and deployment settings
•    The computer names, ports, servers, and URLs you use to implement multiple, integrated, EPM System products and components


Configuration changes that you make for one product are automatically applied to other products used in the deployment.

The Registry was introduced in version 11 and it was Oracle’s answer to centralise all the property files that were scattered everywhere in previous releases, I did write a blog about the registry and property files a few years ago so I am not going to go over old ground


The registry basically comprises of a set of six relational tables which are held together by component ids , it is not advisable to start changing the information directly in the tables as there are a number of supported methods to control it, this can be done by the command line epmsys_registry utility or for the majority of registry properties by using the LCM import/export features in Shared Services.

The registry works quite well most of the time but like most of products it is one that has improved over time, though saying that with a combination of maintenance releases, reconfigurations and bugs the registry can also get into a bit of a mess, sometimes it is not noticeable but other times it can cause nothing but a headache.

Luckily Oracle were aware of the issues that have arisen with the registry and have provided a utility to clean it up.

At the time of writing this there is not much documentation available on the utility so I apologise if there are any inaccuracies as it is just my own interpretation.

The utility is based on six rules defined in an XML file for cleaning up the registry and these are
  • Remove components without parent HOST node
  • Remove APP_SERVER components with just HOST components in parents
  • Remove components without any children or parent nodes
  • Remove HOST to HOST links
  • Remove webapps with invalid ‘serverName’ property
  • Merge duplicate webapps
From 11.1.2.2 the utility is installed by default and is available in <MIDDLEWARE_HOME>/user_projects/<instancename>/bin and can be run by executing registry-cleanup.bat/sh


The utility will go through each of the six rules and search for problems in the registry, it will skip certain component configurations which are also defined in the rules xml file.

This was run on a fresh install of 11.1.2.2 so as expected the utility didn’t encounter any issues.

If you are running 11.1.2.1 then the utility is also available as a patch and is definitely worth having a look, I would probably consider it a prerequisite if going through a maintenance release to 11.1.2.2

The nice feature of the utility is that you don’t have to take any action if problems are found and can just use it to see how the registry is looking.

I will now go through the process of getting up and running with the utility on 11.1.2.1


The patch 13807599 :Providing a cleanup script for Shared Services Registry issues is available from Oracle Support


Once downloaded extract the patch to <MIDDLEWARE_HOME>\EPMsytem11R1\Opatch


To apply the patch run the following from the Opatch directory.

Windows

opatch.bat apply <EPM_ORACLE_HOME>\OPatch\13807599 -oh <EPM_ORACLE_HOME> -jre <MIDDLEWARE_HOME>\jdk160_21

On my instance that was

opatch.bat apply E:\Oracle\Middleware\EPMSystem11R1\OPatch\13807599 -oh E:\Oracle\Middleware\EPMSystem11R1 -jre E:\Oracle\Middleware\jdk160_21

*nix

./opatch apply <EPM_ORACLE_HOME>/OPatch/13807599 -oh <EPM_ORACLE_HOME> -jre <MIDDLEWARE_HOME>/jdk160_21 -invPtrLoc <EPM_ORACLE_HOME>/oraInst.loc


Once the patch has been applied you will find the utility in <MIDDLEWARE_HOME>\EPMSystem11R1\common\config\11.1.2.0 which is a different location than the one preinstalled on 11.1.2.2

Please note before running the utility make sure you perform a backup of the database/schema holding the Shared Services Registry as even though this utility is there to clean up the registry there is also a chance it could also do more damage.

The utility can be run from by executing registry-clean.bat/sh

So let’s give the utility a try on an EPM 11.1.2.1 instance though I am not sure how many issues it will actually find as I have not really had many problems with it.


Once the utility starts up it asks for the EPM instance location which is different than the 11.1.2.2 version, I think it just requires the epm instance so it can locate the reg.properties file which contains the connection information to the Shared Services Registry database.


The utility now steps through the six rules with the first being “Remove components without parent HOST node

The utility finds no issues with that rule and skips four components according to the definition in the rules xml file.


The next rule is to “Remove APP_SERVER components with just HOST components in parents

This time the utility finds one component matching the rule and provides five different options to take action upon.


Selecting I provides full details of the entry in the registry.


If you run a registry report using E:\Oracle\Middleware\user_projects\epmsystem1\bin\epmsys_registry.bat/sh then you can view the same entry in the registry.


It is also possible to run a SQL query against the registry tables to view the same information though please note these are not the only records in the registry tables relating to this entry.


Selecting Y deletes the component out of the registry.


A quick query confirms the records have been removed.


The utility runs through the remaining rules which with the instance I chose didn’t find any more issues.

So give the utility a trial as you don’t have to delete any entries and you have the ability to see what shape the registry is in.

7 comments:

Regis said...

John, you should really consider adding a "Donate" link to your pages! Your posts are always so helpful!

Ragav said...

John,

Is registry clean up applicable for 11.1.2.0?

Regards,
Ragav.

John Goodwin said...

As far as I am aware it works against 11.1.2.1 and 11.1.2.2
Raise an SR with Oracle and see if there is a version available for 11.1.2.0

Anonymous said...

Thanks John, this really helped me today.

Best Regards,
Tej

Warren Pereira said...

Just what was required after 4 re-configurations.

Thanks a lot John. This is a really helpful blog.

Regards,
Warren

TJ P said...

John, thanks for your help. Even 3 years later, you're still helping people.

I have an EPM 11.1.2.1, and after contacting Oracle for the script, they said this function is available in 11.1.2.2. That's great, but I wanted the patch for 11.1.2.1 and after finding it myself, they confirmed I can use it.

Oracle Support has not even helped 1% of how much you've helped me in my Hyperion career. Thanks again!
-TJ

Tracy said...

Thanks John. I was almost afraid to run this utility but you mentioned it prompts for action which is great!