Parameters not effected after chaning in init<SID>.ora

[eshwar_rao]

Hi guru's

I am working on SAP with oracle 10.2.0.2.0 .

I have changed two parameters in /oracle/<SID>/102_64/dbs/init<SID>.ora file

OPTIMIZER_INDEX_COST_ADJ=20 (OLD VALUE IS 50) and

OPTIMIZERMJC_ENABLED=FALSE (new parameter added).

After this I have restarted the SAP and Oracle. but when I login to SAP and check in ST04->Detail Analysis Menu->Parameter Changes->Active parameters, these changes were not effected. but the same I am able to see in Display init<SID>.ora.

Can I anyone tell me how to make these changed parameters active

Thanks

Eshwar

[Former Member · Accepted solution]

do you have a spfile? If you have spfile, the database is started with spfile.

<b><i>SQL> show parameter spfile;</i></b>

Oracle searches for the parameter file in the following order:

1. spfile<SID>.ora

2. spfile.ora

3. init<SID>.ora

see also please the SAP note.

601157 - Oracle9i: Server Parameter File

If you want that the database will be started with init<SID>.ora, please rename the spfile<SID>.ora and then create it again, if you want it:

<b><i>create spfile from pfile;</i></b>

or you can also start your database with init.ora like:

<b><i>startup pfile='<PATH_TO_init<sid>.ora/init<SID>.ora'</i></b>

e.g.

<b><i>startup pfile='oracle/C11/102_64/dbs/initC11.ora'</i></b>

best regards

Baran

[eshwar_rao · Accepted solution]

Hi Baran,

Thanks for your reply.

I want to know if I execute the command

startup pfile='oracle/C11/102_64/dbs/initC11.ora, will the changes also effect in spfile<sid>.ora.

Bcoz what I understand is that both spfile,sid>.ora and init<sid.ora> should have same parameter values.

Thanks

Eshawr

[Former Member · Accepted solution]

after changing your init<SID>.ora, you start your the database at first with pfile (init<SID>.ora) and then create spfile from pfile.

You have to startup the database again, so as to the database is started with spfile.

only with startup: then the database will be started with spfile;

e.g. like that

<i>SQL> startup pfile='oracle/C11/102_64/dbs/initC11.ora'

SQL>create spfile from pfile;

SQL>shutdown immediate;

SQL>startup</i>;

Best regards

Baran

[Former Member · Accepted solution]

before you create a new spfile, you should rename the old one or make a copy of it.

[fidel_vales · Accepted solution]

Hi,

NO, if you startup with the pfile, the spfile is not used, not updated.

If you startup with a "good" pfile AND you have an spfile, you should recreate the spfile. ( create spfile from pfile )

BEST, is to have clear ideas

- do I want to use pfile or spfile?

if you want to use pfile drop the spfile and use the pfile like the old times.

If you want to use spfile, FORGET about modifying manually the parameters on the init<sid>.ora. Modify the parameters directly on the spfile. HOW?

1) Read the note I mentioned (601157) to learn more about spfile

2) change the parameter with "ALTER SYSTEM" ( read the note )

3) use brspace to change the parameters

Just saw the comment by Baran, good timing.

Another advantage of changing directly on the spfile and NOT use pfile for intermediate changes.

You do not need two "reboots"

You avoid mistakes like this ( changiing pfile and not spfile )

having a backup of the pfile is good, in case spfile gets corrupted. BR*tools do this aoutmatically

Message was edited by:

Fidel Vales

Message was edited by:

Fidel Vales

[Former Member · Accepted solution]

you should differ static parameters from dynamic parameters. For the changes in dynamic parameters you don't need to start the database.

For the activating changes of static parameters you should start the database, if you change the parameter with alter system e.g like that

ALTER SYSTEM SET <PARAM_name>=<value> SCOPE = SPFILE;

see please also the oracle note Doc ID: <b><i>Note:249664.1 Pfile vs SPfile</i></b>

and the SAP note: <b><i>601157 - Oracle9i: Server Parameter File</i></b>

Best regards

[Former Member · Accepted solution]

Here you can see which Oracle Database parameters are dynamic or static or if they are modifiable with ALTER SYSTEM or not.

for 10g databases:

http://www.oracle.com/pls/db102/initora

for 9.2 databases:

http://www.oracle.com/pls/db92/db92.initora?remark=homepage

best regards

[eshwar_rao · Accepted solution]

Hi Baran,

Thanks for the reply..It was helpful and the problem is solved. Now the parameters got effected in the server.

Regards

Eshwar

[fidel_vales]

if you are using an spfile, you should change the parameters on it.

the command would be

alter system set OPTIMIZER_INDEX_COST_ADJ=20 scope=spfile sid=`*`

On th next restart it will be set.

SCOPE can be "MEMORY" and "BOTH" for parameters that can be change without restart

check note 601157 for more info

[Former Member]

sp file might be in use ..

Thanks

Prince Jose