Damir Vadas, Oracle as I learned

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!

Tuesday, July 9, 2013

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

The Solution

The End