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!

Tuesday, July 2, 2013

Dell launches Toad for Oracle 12.0 for database professionals

On 21st June 2013, Dell has launched Toad for Oracle 12.0, the latest version of its database software for professional users, making database administration easier than ever. This version fully support Oracle 12c database.

Toad for Oracle v12 is packed with new features and enhancements, including:
  • The community window – Connect to Toad World from inside Toad and interact via Q&A and forum posts.
  • Jump search – Use high-speed, automated search functionality to get immediate contextual help on any Toad or Oracle topic inside or outside Toad.
  • Multi-schema compare – Compare schemas across two databases simultaneously for enormous time savings.
  • MyToad – Get remote task and script execution via any mobile device.
  • Private and public repositories – Give teams the ability to collaborate through script and file sharing.
  • Upgraded team coding – Seamlessly integrate with multiple version-control systems to help ensure code integrity for development projects with added support for Microsoft Team Foundation Server 2012.
  • Code analysis – Take advantage of new rules that contribute to high-quality code across the team.
  • Best of all, Toad World is now easier to navigate, encourages greater engagement, and puts the power of knowledge at your fingertips. No wonder this community attracts three million visitors each year.
  • ... and many minor fixes and improvements
The three million strong Toad World has also received a revamp, letting database professionals connect with experts on forums, groups and blogs, providing a wealth of tools and resources, and letting users access product betas, freeware downloads and more.

"At Dell, we think a connected employee is a more efficient one. With the new Toad and Toad World releases, Dell connects its users to knowledge, and to other users on their team and around the world. Individuals and teams working with data and databases can be smarter and faster," said Darin Bartik, executive director of product management at the Information Management division of Dell Software.

"In turn, business applications and processes that depend on this work can be released quickly, perform better, and cost less to operate. It’s a simple concept that we think will have a tremendous impact on our customers."

Hope that this version will have many fixes that 64 bit version was suffering a lot. Looking forward to test it.

Cheers!

Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign