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?