V$PARAMETER nls bug ?



Today we had a strange issue when doing a standby switchover related to NLS - we had built the standby with the exact same NLS_SETTINGS in the spfile as the primary - see list below

*.NLS_DATE_FORMAT='DD-MON-RRRR HH24:MI:SS'
*.NLS_LANGUAGE='ENGLISH'
*.NLS_TERRITORY='UNITED KINGDOM'


However we noticed this behaviour:

Primary

SQL> select sysdate from dual;

SYSDATE
--------------------
02-DEC-2016 09:28:26


Standby

SQL> select sysdate from dual;

SYSDATE
---------------
02-DEC-16


But the parameters at the database level in the spfile are exactly the same (and the database have been restarted before you ask) - whats going on....?

Lets do a show parameter on both of them

Primary

SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      DD-MON-RRRR HH24:MI:SS


Standby

SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      DD-MON-RR


hold on......

so show parameter is disagreeing with whats in the parameter file......

Lets look directly at the parameter views in the standby

  1* select value from v$parameter where name='nls_date_format'
SQL> /

VALUE
--------------------------------------------------------------------------------
DD-MON-RR

SQL>  select value from v$spparameter where name='nls_date_format'
  2  /

VALUE
--------------------------------------------------------------------------------
DD-MON-RRRR HH24:MI:SS

SQL>


right.........

so spfile parameter is disagreeing with parameter....

Lets look at the source for v$parameter to see if we can find out why

select  NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, DEFAULT_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISPDB_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED , ISDEPRECATED, ISBASIC, DESCRIPTION, UPDATE_COMMENT, HASH, CON_ID  from GV$PARAMETER where inst_id = USERENV('Instance')

ok gv$parameter then

select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdfl,  ksppstdf, decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),  decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',      3,'IMMEDIATE','FALSE'),  decode(bitand(ksppiflg/524288,1),1,'TRUE','FALSE'), decode(bitand(ksppiflg,4),4,'FALSE',                                      decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')),     decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),  decode(bitand(ksppstvf,2),2,'TRUE','FALSE'),  decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'),  decode(bitand(ksppilrmflg/268435456, 1), 1, 'TRUE', 'FALSE'),  ksppdesc, ksppstcmnt, ksppihash, x.con_id  from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and  bitand(ksppiflg,268435456) = 0 and  ((translate(ksppinm,'_','#') not like '##%') and    ((translate(ksppinm,'_','#') not like '#%')      or (ksppstdf = 'FALSE') or      (bitand(ksppstvf,5) > 0)))

Lets look directly at the x$ memory tables then

select * from x$ksppi a,x$ksppcv b where a.indx=b.indx and KSPPINM='nls_date_format';
00007F37D65945B8        280          1          0 nls_date_format                                                                           2 NLS Oracle date format                                  524544    0  677968994 00007F37D1A81B10        280          1          0
DD-MON-RR
DD-MON-RR
NONE                                                                                                                                                                                                FALSE              25

So even the underlying memory table is not agreeing with the database setting....

So this is strange - everything in 'parameter' is showing a different value to 'spparameter' - so whats going on here?

Well i can tell you whats caused it - on the standby server we have this unix env setting set

NLS_LANG=ENGLISH_UNITED KINGDOM.UTF8

A quick look on metalink reveals that if NLS_LANG is set (at all) then a default value of nls_date_format is set in the client overriding the database setting - this value being DD_MON-RR.

Thats all fair enough and i would expect my sqlplus session to honour this - however what i didn't expect is that the values shown from v$parameter would change because of this - this seems just wrong to me? Surely v$parameter shoud show the database setting not my user setting?

And just to check that the setting is not set on database startup because of the NLS_LANG setting - here is a quick demo of what happens if i reconnect with no NLS_LANG set

 unset NLS_LANG
sqlplus / as sysdba

SQL> sho parameter nls_date

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      DD-MON-RRRR HH24:MI:SS
nls_date_language                    string


And looking again at the x$ memory tables


  1* select * from x$ksppi a,x$ksppcv b where a.indx=b.indx and KSPPINM='nls_date_format'
SQL> /
00007F8299ED3470        280          1          0 nls_date_format                                                                           2 NLS Oracle date format                                  524544    0  677968994 00007F8299ED6518        280          1          0
DD-MON-RRRR HH24:MI:SS
DD-MON-RRRR HH24:MI:SS
NONE                                                                                                                                                                                                FALSE               0


So it seems v$parameter is lying................ and displaying my user setting rather than the db setting - anyone else noticed this - a bug surely?

3 comments:

  1. Hi Richard, this has caught me out before. v$parameter shows the parameter settings for the current session; it's v$system_parameter we need to look at to see the database parameters.
    https://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_2085.htm#REFRN30176

    Cheers

    Patrick

    ReplyDelete
    Replies
    1. Sorry - that should be 'instance' parameters not 'database' parameters

      Delete
    2. Hi Patrick,
      You're the second person to say RTFM :-)


      I guess this is one of those things where you just have to know about it, it's probably not how i would have designed the view names. I'd have had something like v$session_parameter showing the session parameter and v$parameter being the 'database' settings.

      Perhaps the most confusing thing for me is when you say

      show parameter x

      I had always kind of assumed this was directly what the database setting is - but clearly as it turns out it isn't.....

      Might see you tomorrow at UKOUG

      Cheers,
      Rich

      Delete