Hoping someone can enlighten me...
I arrived in the office this morning to find out that an automated (via OS CRON) SQL*Plus script had started failing at 0100BST and continued to do so until 0230BST during the switchover from BST to GMT with the following Oracle Error
"*ERROR at line 20:
ORA-08186: invalid timestamp specified"
The SQL below has had some identifying names redacted but is otherwise syntactically equal to that which failed.
SELECT TO_CHAR( MIN(ioh.mdstamp), 'DD-MON-YY hh24:mi:ss') First_Update
, TO_CHAR( MAX(ioh.mdstamp), 'DD-MON-YY hh24:mi:ss') Last_Update
, ioh.consignment
, ioh.order
, CASE ioh.ord_type
WHEN 'T' THEN 'STORE MOVE'
WHEN 'S' THEN 'CUST DEL'
END Movement_Type
, ioh.status Order_Status
, ioh.site_id Site
, COUNT(\*) Attempts
FROM table1
VERSIONS
BETWEEN TIMESTAMP
from_tz(cast((sysdate) as timestamp),'Europe/London') - INTERVAL '20' MINUTE
AND MAXVALUE ioh
WHERE versions_operation = 'I'
And ioh.client_id = 'client1'
AND ioh.status = 'Cancelled'
AND ioh.MSTATUS = 'Pending'
GROUP BY ioh.consignment
, ioh.order
, ioh.ord_type
, ioh.status
, ioh.site_id
ORDER BY ioh.consignment
, ioh.order_id
, 2
Environment is:
- OS: RHEL7.2
- DB: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
- SQL*Plus: Release 12.1.0.2.0
The script executes every 15mins within a BASH wrapper script called via the OS Cron.
sqlplus -s $CONNECT_DCS << ! >> ${f_SQLResults}
@${SQL_SCRIPTS}/${__SCRIPTNAME_NOEXT}.sql;
exit;
!
Another script captured the OS's BST to DST Change:
---------------------------------------
Checking for rejections due to invalid address
Sun 27 Oct **01:47:11 BST** 2019
got count 0
0 - no rejections
---------------------------------------
---------------------------------------
Checking for rejections due to invalid address
Sun 27 Oct **01:02:10 GMT** 2019
got count 0
0 - no rejections
---------------------------------------
The failures started occurring at 0100h BST and continued to occur until what would have been 0230h BST if the change BST to DST change at 0200BST not occurred. As I'm ensuring the Timezone is 'Europe/London' i thought the DB would handle this DST Change automatically but I'm failing to understand why it didn't. One thought that did occur to me is that it may have something to do with using the MAX SCN value or the NLS_TERRITORY bwing set as 'AMERICA' although agian I'd have thought explicitly setting the timezone in the sql qwould have resolved this.
The following DB Config is set:
SQL> select dbtimezone from dual;
DBTIME
------
+00:00
1 row selected.
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
Europe/London
1 row selected.
SQL> select SYSTIMESTAMP from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
28-OCT-19 09.30.36.653265 AM +00:00
1 row selected.
SQL> !date
Mon 28 Oct 09:30:42 GMT 2019
SQL> set lines 200
SQL> l
1* select * from v$nls_parameters
SQL> /
PARAMETER VALUE CON_ID
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
NLS_LANGUAGE AMERICAN 0
NLS_TERRITORY AMERICA 0
NLS_CURRENCY $ 0
NLS_ISO_CURRENCY AMERICA 0
NLS_NUMERIC_CHARACTERS ., 0
NLS_CALENDAR GREGORIAN 0
NLS_DATE_FORMAT DD-MON-RR 0
NLS_DATE_LANGUAGE AMERICAN 0
NLS_CHARACTERSET AL32UTF8 0
NLS_SORT BINARY 0
NLS_TIME_FORMAT HH.MI.SSXFF AM 0
PARAMETER VALUE CON_ID
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 0
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 0
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 0
NLS_DUAL_CURRENCY $ 0
NLS_NCHAR_CHARACTERSET AL16UTF16 0
NLS_COMP BINARY 0
NLS_LENGTH_SEMANTICS CHAR 0
NLS_NCHAR_CONV_EXCP FALSE 0
19 rows selected.
SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'tzr') FROM dual;
TO_CHAR(SYSTIMESTAMP,'TZR')
--------------------------------
+00:00
1 row selected.
SQL> !date +"%Z %z"
GMT +0000
SQL> !timedatectl
Local time: Mon 2019-10-28 11:57:36 GMT
Universal time: Mon 2019-10-28 11:57:36 UTC
RTC time: Mon 2019-10-28 11:57:36
Time zone: Europe/London (GMT, +0000)
NTP enabled: yes
NTP synchronized: yes
RTC in local TZ: no
DST active: no
Last DST change: DST ended at
Sun 2019-10-27 01:59:59 BST
Sun 2019-10-27 01:00:00 GMT
Next DST change: DST begins (the clock jumps one hour forward) at
Sun 2020-03-29 00:59:59 GMT
Sun 2020-03-29 02:00:00 BST
SQL>
If anyone can help I'd be really appreciative.
Cheers
PS: Just noticed that our glogin.sql has the following line: "alter session set time_zone='Europe/London';"