Choosing active plan with OEM puts instance name in spfile

I want to use this post to document a confusing issue we have had with the init parameter resource_manager_plan.  We had a situation where we had used an alter system command to set a particular plan to be active, but a different plan was active.  Also, this was on a two node RAC system and the nodes had different active plans.  The spfile had the parameter set one way for a given instance and also set for all instances another way.  It looks like OEM caused this issue by inserting the parameter into the spfile with the instance name on it.  So, if you use a combination of OEM and alter system commands to set the active resource manager plan take a good look at your spfile to make sure you don’t have conflicting settings.

I put together a quick example on a stand alone test database to demonstrate how this could happen.  I start out with no active plan.  I use this command to dump out the spfile in a text file on my C: drive:

create pfile='C:\todoitems\oemrmparameter\pfile1.txt' from spfile;

The parameter resource_manager_plan was not in the spfile initially.  So, then I set the plan DSS_PLAN active in OEM:

I chose DSS_PLAN and clicked on the “Go” button.

Now DSS_PLAN is active.  When I look at the spfile it has the following parameters:

*.resource_manager_plan='DSS_PLAN'
orcl.resource_manager_plan='DSS_PLAN'

Note how both the *. and orcl. parameters are inserted.  Next I manually change the resource_manager_plan parameter using this alter system statement:

alter system set resource_manager_plan='' scope=both;

Then I bounce the database and which plan is active?  Still the DSS_PLAN.  Here is how the spfile looks now:

orcl.resource_manager_plan='DSS_PLAN'
*.resource_manager_plan=''

The orcl. parameter overrides the *. one so that is why it is running DSS_PLAN.  Then to cleanup I run these two commands to delete both entries from the spfile:

alter system reset resource_manager_plan scope=spfile sid='orcl';
alter system reset resource_manager_plan scope=spfile sid='*';

After bouncing the database you are back to the default INTERNAL_PLAN:

This may seem easy to avoid now that I’ve described the issue, but it was very confusing until I dumped out the spfile.  The key is that OEM will put the instance name on the parameter in the spfile.

– Bobby