oracle and UTC

I was trouble shooting an repeatly appreared ORA-1555 issue. The ORA-1555 was caused by a SQL with a small query duration while the auto turned undo retention time is far larger then that.

Till now we are already close to the root of the issue. But that's not the topic i want to say here, maybe in later's post. Here i want to share a trick i found during my trouble shooting. It proved oracle use UTC as its standard.

When checking the undo segment header dump, i found below:
index state cflags wrap# uel  scn             dba      cmt
------------------------------------------------------------------------------
0x00 9 0x00 0x02de 0x0007 0x0b50.2b608af5 0x02401bb0 1333068666
0x01 9 0x00 0x02de 0x0002 0x0b50.2b66f219 0x02401bb3 1333070396
0x02 9 0x00 0x02de 0x0042 0x0b50.2b66f2a3 0x02401bb3 1333070396
0x03 9 0x00 0x02de 0x000d 0x0b50.2b5ccf81 0x02c05c70 1333067236
0x04 9 0x00 0x02dd 0x0037 0x0b50.2b55621f 0x02c05c4a 1333065389
0x05 9 0x00 0x02de 0x001b 0x0b50.2b5cf63e 0x02c05c71 1333067756
0x06 9 0x00 0x02de 0x0000 0x0b50.2b606d49 0x00000000 1333068599
0x07 9 0x00 0x02de 0x0017 0x0b50.2b61aa83 0x02401bb2 1333068893
0x08 9 0x00 0x02de 0x0027 0x0b50.2b5d14ba 0x02c05cbf 1333067788
0x09 9 0x03 0x02de 0x0015 0x0b50.2b6b3d38 0x02401c81 1333071273
0x0a 9 0x00 0x02de 0x0012 0x0b50.2b6b495d 0x02401c81 1333071316
0x0b 9 0x00 0x02de 0x0001 0x0b50.2b66ef7e 0x02401bb2 1333070395
0x0c 9 0x00 0x02dd 0x0025 0x0b50.2b5ce170 0x02c05c71 1333067657
........
........

9 in state column indicates all transaction here are not activity now, and fclags indiates all transcations are committed normal(no dead transaction).

All seems good till i notice the cmt column. cmt means commit time. And it is not record as an SCN format which we usually see in oracle. It is stored as an unix time format.

Let‘s take red part 1333071317 as example. I tried to conver it into oracle time:
SQL> select sysdate,TO_DATE('19700101000000','YYYYMMDDHH24MISS')+NUMTODSINTERVAL('1333071316', 'SECOND') cmt from dual;
SYSDATE CMT
------------------- -------------------
2012-03-29-22.23.32 2012-03-30-01.35.16


What? The transcation commit time was beyond current time? I saw the future?

It did confuse me for a while till i suddenly figure out that the commit time should use the UAT, while our lcoal machine is using EDT.

Check below:
cihcispdb774.corporate.ge.com[oracle]_gldr12> date
Thu Mar 29 22:24:21 EDT 2012
cihcispdb774.corporate.ge.com[oracle]_gldr12> date -u
Fri Mar 30 02:24:27 UTC 2012


And:
cihcispdb774.corporate.ge.com[oracle]_gldr12> date +%s
1333074371

Now when compared to UTC they are all matched.


What is UTC?
According to the US Navel Observatory, UTC time is "Coordinated Universal Time," a coordinated civil time scale "kept by time laboratories around the world ... determined using highly precise atomic clocks." This is approximately the same as Greenwich Mean Time (GMT), but is more precise. A number of nations, such as Morocco, Iceland, and Mali; use UTC directly as their civil time. In the winter, UTC is the civil time for the UK. US Civil Time is determined by a set number of hours offset from UTC. For instance, Arizona (which doesn't keep Daylight Savings Time) is always UTC+7. Beijing, China's timezone is UTC-8