Thursday, May 22, 2008

ORA-02095: specified initialization parameter cannot be modified

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may get the following error message when attempting to execute a statement like:

ALTER SYSTEM SET DB_DOMAIN=yocoya.com SCOPE=SPFILE;

ORA-02095: specified initialization parameter cannot be modified

Certain initialization parameters are not modifiable using ALTER SYSTEM, so the only way to change their value is to perform the following steps:
  1. CREATE PFILE='path to pfile' FROM SPFILE;
  2. save a backup copy of this pfile in case of troubles!
  3. edit the initialization parameter contained in the pfile;
  4. restart the db using the newly created pfile instead of the default spfile: STARTUP PFILE='...';
  5. CREATE SPFILE FROM PFILE='path to file';
  6. restart the db normally, using the default spfile.
How do you if a parameter is modifiable?
If you look at the dynamic view V$PARAMETER, there is a column called ISSYS_MODIFIABLE, if the value of this column is FALSE, then the parameter is not modifiable using ALTER SYSTEM.
You can find a list of modifiable parameters in the Reference Manual (11g, 10gR2, 10gR1).

See message translations for ORA-02095 and search additional resources.

updated June 4: see Graeme's comment.

10 comments:

sudsandsilver said...

SQL> alter system set db_domain='tom.com' scope=spfile;

System altered.

you need to 'quote' the value

regards
graeme

Byte64 said...

Hi Graeme, you're right, but funnily enough the documentation doesn't mention this quote requirement and the error message remains absolutely misleading...

Anyway it's good to know, in the end it avoids the hassle of restarting the instance twice.

Thanks for reporting this!

Flavio

Anonymous said...

Sir I'm trying to install RCU.bat..but after selecting matadata sevices and webCentre Suit [a small window appear that lists the prerequisites having 7component listed First is processed OK 2nd one is webCenter spaces that is showning X cross .. and other remaining are unprocessed ]


an error occurred i.e RCU-6083:Failed- check prerequisites requirements for selected component:WEBCENTER please refer to RCU log at rcuHome\rcu\log\logdir.2012-09-14_02-43\rcu.log for details — RCU-6107:DB Init Param Prerequisite failure for: PROCESSES Current Value is 100. It should be greater than or equal to 300.

after which I go to the SQLPLUS command line- login as SYSDBA/hr and run dis command
Login to dataabse as sysdba and run this

SQL> alter system set processes=300 scope=both;

then it prompts me: Specified initialization parameter cant be modified

So what should I do now..

Byte64 said...

Hi,
as far as I know PROCESSES can't be modified online, so you should try doing what I explained, create a PFILE from the current SPFILE, make a backup of the SPFILE just in case, modify the PFILE adjusting PROCESSES, restart the instance using PFILE, recreate the SPFILE from the PFILE if it worked.

Flavio

Jojo said...

Sir I’m trying to install RCU.bat..but after selecting matadata sevices and webCentre Suit [a small window appear that lists the prerequisites having 7component listed First is processed OK 2nd one is webCenter spaces that is showning X cross .. and other remaining are unprocessed ]

an error occurred i.e RCU-6083:Failed- check prerequisites requirements for selected component:WEBCENTER please refer to RCU log at rcuHome\rcu\log\logdir.2012-09-14_02-43\rcu.log for details — RCU-6107:DB Init Param Prerequisite failure for: PROCESSES Current Value is 100. It should be greater than or equal to 300.

after which I go to the SQLPLUS command line- login as SYSDBA/hr and run dis command
Login to dataabse as sysdba and run this

SQL> alter system set processes=300 scope=both;

then it prompts me: Specified initialization parameter cant be modified

SQLPLUS it only connects with hr/hr …when I want to connect with SYSDBA eith pass hr or SYSDBA with pass sysdba ..it wont work ..only HR with password hr is working and when I execute the command ..

SQL> alter system set processes=300 scope=both;
Error: Specified initialization parameter cant be modified

or
SQL> alter system setprocesses=300 scope=spfile;
ERROR: insufficient privileges is prompted

Byte64 said...

Hi,
may be reading my previous reply to a similar question can help.

At any rate, you can't specify "scope=both" for that parameter, you must change it in the initialization file (either PFILE or SPFILE).

Jojo said...

whats this SPFILE and what and where to do this... kindly explain

Byte64 said...

http://docs.oracle.com/cd/E11882_01/server.112/e25789/startup.htm#sthref1616

Jojo said...

The Content Server Listener Port is the port of your Oracle Server. I'm making connection through jdev. to webcenter content .. WHAT PORT should i use here ???

Byte64 said...

I don't know, but I think this question has little to do with the topic being exposed here, so if you don't mind you should post your questions in the OTN forum, not here.

Thank you
Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles