Modification of Oracle param

[Former Member]

Hi,

I want to modify few parameters on my Oracle SAP instance, but everytime I delete them, after restart they appear again. I think because they are default, but not sure.

For example I am using:

alter system reset "remote_login_passwordfile" scope=spfile sid="DVS";

System altered.

after the restart of the instance, I still see:

SQL> show parameter remote_login_passwordfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

Any idea how can I get rid of these parameters ? To delete them forever from the system ?

I saw options like:

backup spfile and pfile

change param in spfile

stop the DB

start the DB with the pfile

create spfile from pfile

stop the DB again

start the DB as normally ( using the spfile as default )

Is it possible to use the method above ?

Any other simpler ideas ?

Thanks,

Jordy

[Former Member · Accepted solution]

Hi Jordan,

This is the default kernel value. You will see the parameter even you delete it on the pfile or spfile.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi Orkun,

Same si for parameter:

sort_area_retained_size

Any idea how can I get a list of these parameters that cannot be deleted .... or if there is any doc about this ?

It would be helpfull.

Thanks

[Former Member · Accepted solution]

You can't delete the parameters completely. If the parameter has not been configured in the database profile, its default value will be set, automatically.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hello Orkun, guys,

Indeed, I deleted the parameters from the spfile, restarted the instance, and they are really gone from spfile:

SQL> alter system reset "sort_area_retained_size" scope=spfile sid='*';

System altered.

But in Db02, I still see them under active parameters:

Is this still normal ?

I

[Former Member · Accepted solution]

>> Is this still normal ?

Yes, it is normal. It is the default value.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Thanks Orkun,

Helpful as always

Cheers!

[Former Member]

Hello,

The SHOW PARAMETER command shows all parameters, regardless of whether you set them explicitly in the SPFILE or whether the built-in default is used.

If you want to know which parameters currently have a value different from the default then the following SQL script might be helpful:

column NAME format A45

column DISPLAY_VALUE format A80

set linesize 132

select NAME, DISPLAY_VALUE from V$PARAMETER

where ISDEFAULT = 'FALSE'

order by NAME;

Regards,

Mark

[Former Member]

Yes you will see defaults showing using show parameter command.

To check have a look at your spfile (the actual file).

Are the entries DVS.REMOTE.......

or are they *.REMOTE......

If they are * you need to use:

alter system reset remote_login_passwordfile scope=spfile sid='*';

Then restart your instance.

You can look at your spfile to confirm the entry has been removed.

[former_member192421]

Hi Jordan,

Instead of deleting, I think you can comment it out by putting "#" before the parameter.

Can you try this?

Regards,

Rajkumar