Oracle 10g Parameters change.

[Former Member]

Dear all,

How to change the oracle 10g parameters, in which file we need to make the changes , please help me on this.

Regards

Ratnakar G.

[former_member213250]

Hello Ratnakar

To make life easy regards to DB parameter settings, firstly I would recommend to run the automated parameter check

report on your system. Please check the mentioned corrections in note 1171650 that need to be made on the script if your system is OLAP system if OLTP no changes necessary and run the attachments scripts on your system.

Compare the output of parameter check to that of standard parameter recommendation note : 830576.

If any of the values in column SHOULD BE VALUE differs from output to standard parameter recommendation note

please make them changes.

Hope it helps

BR

Venkat

[Former Member]

Hello Ratnakar,

There're several options to set oracle parameters.

First option:

Use brtools ==> 1 (Instance man.) ==> 4 (alter database parameters)

After that Stop and start SAP system

Second option:

Make a copy of the initSID.ora and spfile on operating system.

Edit the parameters you want to set in the initSID.ora

Connect with the database > sqlplus "/as sysdba" > create spfile from pfile;

Restart the SAP application with the database.

Third option: (My favorite)

Start on operation system > sqlplus "/as sysdba"

Edit with the alter command the parameters you want to set. See examples.

After that restart system.

Examples:

alter system set "_fix_control" = '4728348:off' scope = both

alter system set "_index_join_enabled" = FALSE scope = both

alter system set "_in_memory_undo" = FALSE scope = both

alter system set "_optimizer_mjc_enabled" = FALSE scope = both

alter system set "_sort_elimination_cost_ratio" = 10 scope = both

alter system set filesystemio_options = 'setall' scope = both

alter system set log_checkpoints_to_alert = TRUE scope = both

alter system set max_dump_file_size = '20000' scope = both

alter system set filesystemio_options = 'setall' scope = both

alter system set replication_dependency_tracking = FALSE scope = spfile

alter system set star_transformation_enabled = 'TRUE' scope = spfile

alter system set "_eliminate_common_subexpr" = FALSE scope = spfile

alter system set "_optimizer_join_sel_sanity_check" = TRUE scope = spfile

alter system set processes = 1760 scope = spfile;

alter system set sessions = 3520 scope = spfile;

alter system set "_fix_control" = '5099019:ON','5705630:ON','5765456:3' scope = both;

alter system set event = '10027 trace name context forever, level 1','10028 trace name context forever, level 1' scope = spfile;

etc.....

Kind regards,

Marcel

[Former Member]

Hi. Ratnakar

Your Examples are Perfect, and, they help me.

thanks.

[Former Member]

hi,

You can change the oracle parameters using BRtools. Use 830576 Snote for recommended parameters for oracle.

Regards,

Raju

[volker_borowski2]

Hi,

Better use the script from note 1171650 to check the parameters.

It is a whole lot less of work

Volker

[former_member204746]

Read SAP note Note 830576 - Parameter recommendations for Oracle 10g

[former_member227600]

Hi,

You can change oracle parameter in init<SID>.ora file. After changes restart the database.

NOTE:- Be very care when you change any oracle parameter because it can down your database.

[Former Member]

Altering the init<SID>.ora is obsolete and not recommended. You should work with spfiles. You can create an spfile from init<SID>.ora using sqlplus command CREATE SPFILE from PFILE; (PFILE is the old init<SID>.ora).

Once you have the SPFILE in place, you can alter the params from sqlplus as well. An example syntax is,

alter system set param=value scope=spfile|memory|both.

Just google to see the full syntax. Some params can be done memory or both in which case you do not need to restart the database.

Regards,

Shan

[fjhernanz]

Hi,

Please see note 601157. I'm sure it will be of help

Br,

Javier