Oracle 11.2.0.2 NLS_* parameters dynamically set

[Nik3]

Hello guys,

I just did a systemcopy for an ERP system (r3load) and imported the export into the newest Oracle database (11.2.0.2) on Windows. Everything went fine apart from one strange parameter "error".

After the installation when executing the automatic parameter script (note 1171650) there are obviously some parameters dynamically set (nls_*):

nls_calendar -> parameter was dynamically changed; no reliable recommendation can be given

nls_comp -> parameter was dynamically changed; no reliable recommendation can be given

nls_currency -> parameter was dynamically changed; no reliable recommendation can be given

nls_date_format -> parameter was dynamically changed; no reliable recommendation can be given

nls_date_language -> parameter was dynamically changed; no reliable recommendation can be given

nls_dual_currency -> parameter was dynamically changed; no reliable recommendation can be given

nls_iso_currency -> parameter was dynamically changed; no reliable recommendation can be given

nls_language -> parameter was dynamically changed; no reliable recommendation can be given

nls_numeric_characters -> parameter was dynamically changed; no reliable recommendation can be given

nls_sort -> parameter was dynamically changed; no reliable recommendation can be given

nls_territory -> parameter was dynamically changed; no reliable recommendation can be given

nls_time_format -> parameter was dynamically changed; no reliable recommendation can be given

nls_time_tz_format -> parameter was dynamically changed; no reliable recommendation can be given

nls_timestamp_format -> parameter was dynamically changed; no reliable recommendation can be given

nls_timestamp_tz_format -> parameter was dynamically changed; no reliable recommendation can be given

SQL> show parameter nls_calendar

NAME TYPE VALUE

-

---

-

---

-

---

nls_calendar string GREGORIAN

You cannot delete them as they are not configured in the spfile. I did not have those messages with previous Oracle 11.2.0.1 installations.

Does anybody know whats that about?

Best regards

Nik

[former_member184473 · Accepted solution]

Hello Nik,

Also, depending the scope you have used during the parameter change this message will disappear after the system restart.

Regards,

Eduardo Rezende

[Nik3 · Accepted solution]

Hi there,

Thanks for your replies. OS is Win. Serv. 2008 R2.

@Orkun

NLS_LANGUAGE                             AMERICAN
NLS_TERRITORY                            AMERICA
NLS_CURRENCY                             $
NLS_ISO_CURRENCY                         AMERICA
NLS_NUMERIC_CHARACTERS                   .,
NLS_CALENDAR                             GREGORIAN
NLS_DATE_FORMAT                          DD-MON-RR
NLS_DATE_LANGUAGE                        AMERICAN
NLS_CHARACTERSET                         UTF8
NLS_SORT                                 BINARY
NLS_TIME_FORMAT                          HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                     DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                       HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                  DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                        $
NLS_NCHAR_CHARACTERSET                   UTF8
NLS_COMP                                 BINARY
NLS_LENGTH_SEMANTICS                     BYTE
NLS_NCHAR_CONV_EXCP                      FALSE

It looks quite similiar apart from the NLS_CHARACTERSET but it is in fact an unicode system. OS settings (env+regional settings) are exactly the same (same OS template) as on other systems on 11.2.0.1 where I don't have this "problem". This problem persists directly after a fresh SAP installation with SAPINST (newest inst. master NW 7.0 SR3, 51040125_11) and even after adapting all parameters recommended by SAP as per note mentioned above.

@Eduardo: I did not change any of these parameters with the scope memory or spfile. I also restarted the db + os a couple of times. Still the same result.

Best regards

Nik

[former_member184473 · Accepted solution]

Hello Nik,

Have you used scope both, shutdown immediate and them restarted?

Regards,

Eduardo

[Nik3 · Accepted solution]

Hi Eduardo,

I used scope=spfile for all parameters which I had to add or adapt according to the script/note. I then stopped the db with 'shutdown immediate' and then I did a 'startup'.

I also ran the script before changing any parameters. Already at that time the error came up.

Wishes

Nik

[pavol_peregrin · Accepted solution]

Hi,

Any update on this topic? I am facing the same issue after upgrade from 10g to 11g even after aplying latest SBP 112023_201108. OS: AIX 6.1

Thanks

[Nik3 · Accepted solution]

Hi Pavol,

Well I have not investigated the problem any further yet. I left it as it is. In case you should find out sometting I would be pleased if you could post the solution.

Best regards

Nik

[former_member184473 · Accepted solution]

Hello Nik,

The parameter check script from note 1171650 has been updated today and the nls_* are now suppressed.

Regards,

Eduardo

[Nik3 · Accepted solution]

Hi Eduardo

Yes that's correct. Thanks for the info.

Best wishes

Nik

[Former Member]

Hi Nik,

Compare your result of "select * from v$nls_parameters;" statement with the output, below;

NLS_LANGUAGE=AMERICAN
NLS_TERRITORY=AMERICA
NLS_CURRENCY=$
NLS_ISO_CURRENCY=AMERICA
NLS_NUMERIC_CHARACTERS=.,
NLS_CALENDAR=GREGORIAN
NLS_DATE_FORMAT=DD-MON-RR
NLS_DATE_LANGUAGE=AMERICAN
NLS_CHARACTERSET=WE8DEC
NLS_SORT=BINARY
NLS_TIME_FORMAT=HH.MI.SSXFF=AM
NLS_TIMESTAMP_FORMAT=DD-MON-RR=HH.MI.SSXFF=AM
NLS_TIME_TZ_FORMAT=HH.MI.SSXFF=AMTZR
NLS_TIMESTAMP_TZ_FORMAT=DD-MON-RR=HH.MI.SSXFF=AM=TZR
NLS_DUAL_CURRENCY=$
NLS_NCHAR_CHARACTERSET=UTF8
NLS_COMP=BINARY
NLS_LENGTH_SEMANTICS=BYTE
NLS_NCHAR_CONV_EXCP=FALSE

Additionally, check your environment variables and regional settings, if you are running on Windows.

Best regards,

Orkun Gedik