Tuesday, July 9, 2013

ASH and ORA-01843: not a valid month (11g)

When working with ASH, as many times before, I was using Toad ASH interface, which I find very handy. Once I got an error like:
Many would suspect in Toad, so I spool Toad session. Extracted statement was very easy to capture. Then I run the same code in pure sqlplus (11.2g client).
SQL> Select *
  2  from table(dbms_workload_repository.ASH_report_html(
  3           l_dbid => 782550019,
  4           l_inst_num => 1,
  5           l_btime => TO_DATE('07/02/2013 20:00:00', 'MM/DD/YYYY HH24:MI:SS'),
  6           l_etime => TO_DATE('07/02/2013 20:59:59', 'MM/DD/YYYY HH24:MI:SS'),
  7           l_options => 0,
  8           l_slot_width => 0,
  9           l_sid => null,
 10           l_sql_id => null,
 11           l_wait_class => null,
 12           l_service_hash => null,
 13           l_module => null,
 14           l_action => null,
 15           l_client_id => null,
 16           l_plsql_entry => null))
 17  ;
from table(dbms_workload_repository.ASH_report_html(
           *
ERROR at line 2:
ORA-01843: not a valid month
ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 11246
ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 10668
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 1554
ORA-06512: at line 1

SQL>
So error is here again, what relief Toad from any responsibility.
Running same db with some other date (very recent one)...I got proper and ok result:
SQL> Select *
  2  from table(dbms_workload_repository.ASH_report_html(
  3           l_dbid => 782550019,
  4           l_inst_num => 1,
  5           l_btime => TO_DATE('07/09/2013 10:00:00', 'MM/DD/YYYY HH24:MI:SS'),
  6           l_etime => TO_DATE('07/09/2013 10:59:59', 'MM/DD/YYYY HH24:MI:SS'),
  7           l_options => 0,
  8           l_slot_width => 0,
  9           l_sid => null,
 10           l_sql_id => null,
 11           l_wait_class => null,
 12           l_service_hash => null,
 13           l_module => null,
 14           l_action => null,
 15           l_client_id => null,
 16           l_plsql_entry => null))
 17  ;
 
OUTPUT                                                                          
--------------------------------------------------------------------------------
<html lang="en"><head><title>ASH Report - From 09-Jul-13 10:00:00 To 09-Jul-13 10:59:59</title>                                                                 
                                                                                
<style type="text/css">                                                         
...
 
...
<style type="text/css">                                                         
</table><p />
<br /><a class="awr" href="#top">Back to Top</a><p />
<p />
End of Report
</body></html>

464 rows selected.

SQL>
The same was running through Toad interface for the same period-as expected ...
Obviously date period was a problem what lead me to successfully ending.

The Solution

First let me show date when all this was happening.
SQL> select to_char (sysdate ,'dd.mm.yyyy hh24:mi:ss') D from dual;

D
-------------------
09.07.2013 13:48:31
Then I tried to see what ASH data are in SGA, V$ACTIVE_SESSION_HISTORY:
SQL> select min (sample_time), max(sample_time) from V$ACTIVE_SESSION_HISTORY;

MIN(SAMPLE_TIME)                MAX(SAMPLE_TIME)
------------------------------- ----------------------------
08.07.13 23:09:36,411           09.07.13 13:40:56,653
So I tried to call period out of V$ACTIVE_SESSION_HISTORY sample_time, which fall in DBA_HIST_ACTIVE_SESS_HISTORY view. Again, action result an error!
Trying to execute the problematic period through recommended Oracle scripts (ashrpt.sql/ashrpti.sql from rdbms/admin directory), all went fine, with no error.
Because all date based errors usually fall in some kind of NLS problem, I check what is in mine session:
SQL> col parameter for a30;
SQL> col VALUE for a30;
SQL>
SQL> select * from NLS_SESSION_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   CROATIAN
NLS_TERRITORY                  CROATIA
NLS_CURRENCY                   kn
NLS_ISO_CURRENCY               CROATIA
NLS_NUMERIC_CHARACTERS         ,.
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD.MM.RR
NLS_DATE_LANGUAGE              CROATIAN
NLS_SORT                       CROATIAN
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              kn
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 rows selected.

SQL>
From the time when RMAN was not working properly if NLS_LANG was not set to AMERICAN_AMERICA, I remember many problems. So, I changed session parameters to it:
SQL> alter session set NLS_LANGUAGE='AMERICAN';

Session altered.

SQL> alter session set NLS_TERRITORY='AMERICA';

Session altered.

SQL> select * from NLS_SESSION_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ------------------------------
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_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_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 rows selected.

SQL>
When I run again problematic period, all went without error:
SQL> Select *
  2  from table(dbms_workload_repository.ASH_report_html(
  3           l_dbid => 782550019,
  4           l_inst_num => 1,
  5           l_btime => TO_DATE('07/02/2013 20:00:00', 'MM/DD/YYYY HH24:MI:SS'),
  6           l_etime => TO_DATE('07/02/2013 20:59:59', 'MM/DD/YYYY HH24:MI:SS'),
  7           l_options => 0,
  8           l_slot_width => 0,
  9           l_sid => null,
 10           l_sql_id => null,
 11           l_wait_class => null,
 12           l_service_hash => null,
 13           l_module => null,
 14           l_action => null,
 15           l_client_id => null,
 16           l_plsql_entry => null))
 17  ;
 
OUTPUT                                                                          
--------------------------------------------------------------------------------
<html lang="en"><head><title>ASH Report - From 02-Jul-13 20:00:00 To 02-Jul-13 20:59:59</title>                                                                 
                                                                                
<style type="text/css">                                                         
...
 
...
<style type="text/css">                                                         
</table><p />
<br /><a class="awr" href="#top">Back to Top</a><p />
<p />
End of Report
</body></html>
464 rows selected.

SQL>
Works!

The End

As you see, if you run ASH_report_html call in period which fall in V$ACTIVE_SESSION_HISTORY, then your NLS settings has no influence at all.
If you run period outside of it, Oracle programmers has hard codded some where date picture in ASH_report_html ... to apply AMERICAN settings.
Be aware that some bad codding doesn't end soon...even if they come from Oracle.
Hope this helps someone.

Cheers!

No comments :

Post a Comment

Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign