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?
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.
ReplyDeletehttps://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_2085.htm#REFRN30176
Cheers
Patrick
Sorry - that should be 'instance' parameters not 'database' parameters
DeleteHi Patrick,
DeleteYou'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