How Does Daylight Savings Time Affect The Database Kernel?

How Does Daylight Savings Time Affect The Database Kernel?

How Does Daylight Savings Time Affect The Database Kernel? (Doc ID 1013279.6)

SYMPTOMS

 When the system clock is changed (manually adjusted) or during DST the time goes forward or back l, does this have any potentially harmful effects for the database kernel itself?
 

CHANGES

 Adjusting the server clock or DST transition

CAUSE

  Adjusting the server clock or DST transition

SOLUTION

Because Oracle tracks the sequence of events in the database using the System Commit Number (SCN), changing the system clock for daylight savings time will have no effect on database operation like transactions.

Note that when using datatypes like Timezone With (Local) Timezone or timezone functions there is an potential problem when the Oracle RDBMS DST definitions are not updated, but this has no relation with the actual functioning of the database kernel itself.
See Note 412160.1 Updated Time Zones in Oracle Time Zone File patches /E) I'm on DSTv , do I NEED to apply newer DST patches?

The only point where the time change itself can have an potentially harmful effect is during time-based recovery.

Time based recovery requires checking of the actual time the transaction was recorded in the logfile.
Every log record has a time stamp associated with it.
If for some reason a dba has to go back to a backup which was taken prior to the system clock change and rollforward, recovery works just fine except for time based recovery (Note that time based recovery works fine if the system clock is moved forward in time).
When the system clock is changed backwards, its possible that there could be two redo records with the same time stamp.
If time based recovery is done in this scenario, since ORACLE applies only redo entries that were written prior to a specified time, recovery stops when it finds the first redo record which has that specified time.

The following example will illustrate the problem:

3pm           4pm     4.15    4.30  5pm-->4pm   4.16pm 4.30 5pm
|--------------|-------|-------|-------|---------|-------|------|
cold/hot       T1      T2      T3    clock       T4      T5     T6
backup                               change |<----------r1---------->|

A cold backup was taken at 3pm. A transaction T1 was done at 4pm.
So the redo record has a time stamp of 4pm. Transaction T2 was done at 4.15pm and transaction T3 at 4.30pm.

At 5pm the system clock was changed backward, one hour. 16 minutes later (i.e; at time 4.16pm) transaction T4 was done.
Later on, the disk crashed and we lost the databasefiles.

Any recovery done to recover data up to an certain time in the range R1 will not recover the data in the range R1 but recovers only until corresponding time before the system clock change.

Say if the DBA decides to recover until T5(4:30pm) which is in time range R1.
DBA restores the backup from 3pm and does a recovery until 4:30 thinking it would recover until T5.
Actually the recovery is done until T3 and not T5. So all the trasanctions entered after T3 will be lost.
Recovery beyond 5pm or say beyond range R1 should not cause any lose of data.

Note:
Although specifying a time in the interval 'clock change' to 'T6' will result in incomplete recovery to the first occurrence of the specified time it is still possible to recover to any point in time in this range using SCN based recovery (using the RECOVER UNTIL CHANGE clause).

Conclusion:
Following a time change where the clocks go back in time there is a window where INCOMPLETE recovery using TIME BASED recovery is affected.
Recovery to a point in time within this window can be achieved using SCN (or CHANGE) based recovery.
Recovery to a point in time after this window requires no special action.