Home » RDBMS Server » Server Administration » Wrong dates in some records in Timstamp with local zone in 10g (10g)
Wrong dates in some records in Timstamp with local zone in 10g [message #661608] Fri, 24 March 2017 13:49 Go to next message
OraclePE
Messages: 10
Registered: March 2017
Junior Member
I have a very strange problem in oracle 10g where I have a table that contain column in time stamp with time zone which is giving wrong dates like 2048-xx-xxxx 00:00:34 or some records with year 0001 etc. but 90% of records are in proper date time stamp. what could be the reason for this? Please help.
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661610 is a reply to message #661608] Fri, 24 March 2017 14:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

> what could be the reason for this?
one of two alternatives below are possible cause for what you observe.
1) Oracle bug
2) Application bug

>is giving wrong dates like 2048-xx-xxxx 00:00:34
what is "xx-xxxx" above supposed to represent?

Does application code ever use format mask of "RR" for YEAR?

I would bet that root cause is most like #2 above.


Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661611 is a reply to message #661608] Fri, 24 March 2017 14:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Invalid dates have been entered.
Post the result of:
select * from v$version where rownum=1;
desc <mytable>
select to_char(<tmcol>,'DD/MM/YYYY HH24:MI:SS.FF6 TZR') dt, dump(<tmcol>) dmp 
from <mytable> 
where <something that determines the records with bad dates);
Before, Please read How to use [code] tags and make your code easier to read.

Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661617 is a reply to message #661611] Sat, 25 March 2017 10:11 Go to previous messageGo to next message
OraclePE
Messages: 10
Registered: March 2017
Junior Member
select * from v$version where rownum=1;

Oracle Database 10g Release 10.2.0.3.0 - 64bit Production


---------------------------------------
desc lbt_archive_170320_exp

Name               Null Type                              
------------------ ---- --------------------------------- 
EVENTTIME               TIMESTAMP(6) WITH LOCAL TIME ZONE 
BAGTAGID                VARCHAR2(12)                      
BAG_ID                  NUMBER(10)                        
STATUS                  VARCHAR2(3)                       
BAGSTATUS               VARCHAR2(20)                      
BAGPROFILE              VARCHAR2(3)                       
HBSSTATUS               VARCHAR2(3)                       
HBSCURLEVEL             VARCHAR2(3)                       
HBSRESULT               VARCHAR2(10)                      
CUSTSTATUS              VARCHAR2(10)                      
BSM_ID                  VARCHAR2(20)                      
TTR_ID                  NUMBER(10)                        
TDP_IDFINAL             NUMBER(6)                         
TDP_IDCURR              NUMBER(6)                         
TDP_IDOPER              NUMBER(6)                         
TNP_IDFIRSTPOS          NUMBER(6)                         
TNP_IDLASTPOS           NUMBER(6)                         
FIRSTPOSTIME            TIMESTAMP(0) WITH LOCAL TIME ZONE 
LASTPOSTIME             TIMESTAMP(0) WITH LOCAL TIME ZONE 
EXPIRATIONTIME          TIMESTAMP(0) WITH LOCAL TIME ZONE 
SECTODEST               NUMBER(6)                         
REASON                  VARCHAR2(20)                      
STORINGREASON           VARCHAR2(20)                      
ISNPBASEDHBSSTATUS      VARCHAR2(1)                       
ISPROCESSED             VARCHAR2(1)                       

                    

-------------------------------------------------------------------------

select to_char(eventtime,'DD/MM/YYYY HH24:MI:SS.FF6 TZR') dt, dump(eventtime) dmp 
from lbt_archive_170320_exp 
where eventtime > sysdate;

DT	DMP
00/00/2047 16:50:57.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,13,171,58,15,3,7,225
00/00/2047 16:50:57.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,13,171,58,15,3,7,225
00/00/2047 16:50:57.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,13,171,58,15,3,7,225
03/01/2047 08:51:52.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,77,172,53,15,3,7,225
03/01/2047 08:51:52.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,77,172,53,15,3,7,225
03/01/2047 08:51:52.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,77,172,53,15,3,7,225

please find the output from script.
*BlackSwan added {code} tags. PLEASE do so yourself in the future. You have been TWICE how to do so!

[Updated on: Sat, 25 March 2017 10:14] by Moderator

Report message to a moderator

Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661618 is a reply to message #661610] Sat, 25 March 2017 10:18 Go to previous messageGo to next message
OraclePE
Messages: 10
Registered: March 2017
Junior Member
Hi,

Even simple "INSERT INTO table1 ( column1 ) SELECT col1 FROM table2" generates this issue on TIMESTAMP column.

Regards
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661619 is a reply to message #661618] Sat, 25 March 2017 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
OraclePE wrote on Sat, 25 March 2017 08:18
Hi,

Even simple "INSERT INTO table1 ( column1 ) SELECT col1 FROM table2" generates this issue on TIMESTAMP column.

Regards
Do NOT tell us what you do & see.
SHOW US using COPY & PASTE!

how can reproduce what you report?
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661620 is a reply to message #661617] Sat, 25 March 2017 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OraclePE wrote on Sat, 25 March 2017 16:11
...
select to_char(eventtime,'DD/MM/YYYY HH24:MI:SS.FF6 TZR') dt, dump(eventtime) dmp 
from lbt_archive_170320_exp 
where eventtime > sysdate;

DT	DMP
00/00/2047 16:50:57.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,13,171,58,15,3,7,225
00/00/2047 16:50:57.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,13,171,58,15,3,7,225
00/00/2047 16:50:57.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,13,171,58,15,3,7,225
03/01/2047 08:51:52.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,77,172,53,15,3,7,225
03/01/2047 08:51:52.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,77,172,53,15,3,7,225
03/01/2047 08:51:52.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,77,172,53,15,3,7,225

These are indeed wrong values, and have been entered in the database by an application using binary values.
These can't be entered with usual SQL interface, ONLY with binary interface, for which the application is responsible of the correctness of the values.

Once again, read How to use [code] tags and make your code easier to read.

Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661622 is a reply to message #661620] Sat, 25 March 2017 11:12 Go to previous messageGo to next message
OraclePE
Messages: 10
Registered: March 2017
Junior Member
Dear Michel, the output which I copied from your script. We are not doing any binary input from our application. This wrong dates are generated in normal insert into from select * from source table. The source table has the same stable structure where we don't have any date corruption and which is again 10g database running on different server. we have created database link.
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661623 is a reply to message #661622] Sat, 25 March 2017 11:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Either Application is in error or Oracle is in error.
We here can't do anything about either alternative.
Either the application needs to be fixed or a Bug Report needs to be submitted.

> This wrong dates are generated in normal insert into from select * from source table.
How can we reproduce what you claim above (which is offered without ANY proof)?

What is OS name & version for each of the Oracle database servers?
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661625 is a reply to message #661623] Sat, 25 March 2017 11:43 Go to previous messageGo to next message
OraclePE
Messages: 10
Registered: March 2017
Junior Member
OS is windows 2003 server and source oracle 10g is in RAC configuration. I am sure it is not in source side. We tried increasing the SGA and PGA memory on destination oracle but did not helped. We thought of lack of memory. Now oracle will not support 10g. Totaly handicapped Smile.
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661626 is a reply to message #661625] Sat, 25 March 2017 11:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
How can we reproduce what you claim above (which is offered without ANY proof)?
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661628 is a reply to message #661625] Sat, 25 March 2017 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
This wrong dates are generated in normal insert into from select * from source table.
This is not possible if what you did is EXACTLY what you write that is both tables are in the same database and the same schema.
If not, COPY AND PASTE, formatting, the actual statement.

Quote:
OS is windows 2003 server
For both source and target?
How did you copy the source table to the target one?
If it is INSERT SELECT then you use a database link.
What are the NLS parameters for both databases, instances, servers?

As BlackSwan said and repeated, how can we reproduce what you say?


Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661635 is a reply to message #661628] Sat, 25 March 2017 13:02 Go to previous messageGo to next message
OraclePE
Messages: 10
Registered: March 2017
Junior Member
Both databases running on windows 2003. I will send the NLS settings tomorrow, I could reproduce the same issue via insert into. I execute the insert into from the target oracle database and do the select * from sourcetable@dblink which inserts data with 10% corrupted time stamp.

[Updated on: Sat, 25 March 2017 13:04]

Report message to a moderator

Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661637 is a reply to message #661635] Sat, 25 March 2017 13:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
OraclePE wrote on Sat, 25 March 2017 11:02
Both databases running on windows 2003. I will send the NLS settings tomorrow, I could reproduce the same issue via insert into. I execute the insert into from the target oracle database and do the select * from sourcetable@dblink which inserts data with 10% corrupted time stamp.
blah, blah, blah.

Either provide us reproducible test case or stop wasting everyone's time with your useless & baseless tales of woe.
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661656 is a reply to message #661637] Mon, 27 March 2017 02:26 Go to previous messageGo to next message
OraclePE
Messages: 10
Registered: March 2017
Junior Member
I am sorry ...

Here i am explaining my issue with all details,

My issue is when i am trying to copy old data to backup database from live database, some very old dates are getting inserted to the backup db along with the correct data.

The OS version is windows 2003
DB version for both live and backup db is 10.2.0.3.0

the wrong date format will look like

EVENTTIME
0002-12-02 07.11.35.453183457 PM
0002-12-03 10.52.04.453183457 PM
0002-12-03 10.52.04.453183457 PM
0002-12-03 10.52.04.453183457 PM

but none of these are there in the live database.

The data is copied to the backup db is through db link using "insert into backup_db_table select * from live_table@dblink".

And when we copy within 1 lakh rows, the wrong data is not getting entered. When the row count goes up more than 3 lakh, this issue is appearing in the column.

below i am copying the nls parameters for the backup db at session level.

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 YYYY-MM-DD HH.MI.SS.FF 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
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661657 is a reply to message #661656] Mon, 27 March 2017 02:27 Go to previous messageGo to next message
OraclePE
Messages: 10
Registered: March 2017
Junior Member
db version is
select * from v$version;

BANNER
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
"CORE 10.2.0.3.0 Production"
TNS for 64-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661658 is a reply to message #661657] Mon, 27 March 2017 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

BlackSwan wrote on Fri, 24 March 2017 20:27
...
How to use {code} tags and make your code easier to read
...

Michel Cadot wrote on Fri, 24 March 2017 20:31

...
Before, Please read How to use [code] tags and make your code easier to read.

Quote:
*BlackSwan added {code} tags. PLEASE do so yourself in the future. You have been TWICE how to do so!

Michel Cadot wrote on Sat, 25 March 2017 16:38
...
Once again, read How to use [code] tags and make your code easier to read.



Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661659 is a reply to message #661656] Mon, 27 March 2017 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

BlackSwan wrote on Sat, 25 March 2017 16:20
...
how can reproduce what you report?

BlackSwan wrote on Sat, 25 March 2017 17:24
...
How can we reproduce what you claim above (which is offered without ANY proof)?
...

Michel Cadot wrote on Sat, 25 March 2017 17:58

...
As BlackSwan said and repeated, how can we reproduce what you say?

BlackSwan wrote on Sat, 25 March 2017 19:09
...
Either provide us reproducible test case or stop wasting everyone's time with your useless & baseless tales of woe.
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661660 is a reply to message #661658] Mon, 27 March 2017 02:41 Go to previous messageGo to next message
OraclePE
Messages: 10
Registered: March 2017
Junior Member
Quote:
I am sorry ...

Here i am explaining my issue with all details,

My issue is when i am trying to copy old data to backup database from live database, some very old dates are getting inserted to the backup db along with the correct data.

The OS version is windows 2003
DB version for both live and backup db is 10.2.0.3.0

the wrong date format will look like

EVENTTIME
0002-12-02 07.11.35.453183457 PM
0002-12-03 10.52.04.453183457 PM
0002-12-03 10.52.04.453183457 PM
0002-12-03 10.52.04.453183457 PM

but none of these are there in the live database.

The data is copied to the backup db is through db link using
insert into backup_db_table
select * from live_table@dblink

Quote:
And when we copy within 100000 rows, the wrong data is not getting entered. When the row count goes up more than 300000 rows, this issue is appearing in the column.

below i am copying the nls parameters for the backup db at session level.
select * from nls_session_parameters;

Quote:
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 YYYY-MM-DD HH.MI.SS.FF 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
Quote:
db version is
select * from v$version;

Quote:
BANNER
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
"CORE 10.2.0.3.0 Production"
TNS for 64-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Quote:
below showing the nls session parameters at live db
select * from nls_session_parameters;

Quote:

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
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661673 is a reply to message #661660] Mon, 27 March 2017 08:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
what exactly do you expect/desire from here?

We can't change how Oracle behaves for you.
If you believe Oracle is in error, then you need to submit a Bug Report to MOS.
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661674 is a reply to message #661673] Mon, 27 March 2017 09:05 Go to previous messageGo to next message
OraclePE
Messages: 10
Registered: March 2017
Junior Member
Quote:
I tried to resolve using cursor, but it was returning with ora error as below;
Error report:
ORA-01804: failure to initialize timezone information
ORA-06512: at line 13
01804. 00000 - "failure to initialize timezone information"
*Cause: The timezone information file was not properly read.
*Action: Please contact Oracle Customer Support.


The pl/sql i used was
create or replace procedure test_old_date
as
p_array_size PLS_INTEGER DEFAULT 1000;
TYPE ARRAY IS TABLE OF lbt_test1%ROWTYPE;
l_data ARRAY;

CURSOR c IS SELECT * FROM lbt_bagtracking@totestdate where eventtime>systimestamp-10/1440;

BEGIN
execute immediate 'alter session set nls_timestamp_format = ''YYYY-MM-DD HH.MI.SS.FF AM'''; 
execute immediate 'alter session set time_zone = ''Asia/Muscat''';
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO lbt_test1 VALUES l_data(i);

    EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
END test_old_date;

but i was not getting any idea to solve the ORA:01804

Can anyone help me out in this error...

Thanks & Regards,
Thomas
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661675 is a reply to message #661674] Mon, 27 March 2017 09:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
[oracle@vbgeneric ~]$ oerr ora 1804
01804, 00000, "failure to initialize timezone information"
// *Cause: The timezone information file was not properly read.
// *Action: Please contact Oracle Customer Support.
[oracle@vbgeneric ~]$
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661677 is a reply to message #661660] Mon, 27 March 2017 11:20 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

I don't know if it part of your current problem, but looks like it could be.

The RR format was meant to be a temporary band-aid to buy time in dealing with Y2k. That was over 17 years ago. It's well past time to quit using it. It was never meant to be a permanent fixture. For those that don't understand its impact, and thought it was a permanent solution to y2k, to be set and then ignored forever, it wasn't so much a fix as a time-bomb.
Previous Topic: Oracle licenses Requirement
Next Topic: Auditing Oracle 11g
Goto Forum:
  


Current Time: Thu Mar 28 08:52:50 CDT 2024