In 2019 they decided in Chile that the winter time adjustment would be 5 months, instead of the usual 3 months. Morocco also decided to move its time with respect to GMT from +00 to +01.
These changes that you may think do not affect you in the least, unless you live in Chile or Morocco, should be applied to the database’s time systems. Keep in mind that these changes are NOT installed in conventional Release Upgrade patches, and if you try to do expdp/impdp with different versions, it will fail.
DST stands for Daylight Savings Time, and it represents complexity in computer systems because:
- In a time adjustment to autumn, the same time happens twice.
- In the spring time setting, one hour disappears.
- In each region, these situations may occur at different times (or not occur).
- Changes in these time systems respond to governmental, political or religious decisions.
Every year there is something new in the DST. In the European Community they voted to leave the DST, but in the second vote in the European Council, the motion failed. If it had gone ahead, it would have meant an important change in the time management of all European applications. It must be considered that currently 60% of the countries in the world are under DST adjustments.
How is all this managed in an Oracle database? What happens when the server is in one time zone, but the clients are in another? How is it possible to manage different time management for the same moment of time?
Well, in the event that the applications use DATE type columns and the SYSDATE function, the value they will handle will always be that of the server, and all time adjustment cases must be done in the application. In fact, before Oracle9i the SQL language was not sensitive to time zones.
Currently, we have specific data types that handle not only the time with the time zone, but also allow us to manage the local time: These are TIMESTAMP WITH TIMEZONE (stored internally as UTC) and TIMESTAMP WITH LOCAL TIMEZONE.
Additionally, it is also possible to manage:
- The user’s time zone: SESSIONTIMEZONE
- Server time zone: DBTIMEZONE
- Convert a date from one time zone to another with the FROM_TZ function, or extract the date in UTC (Universal format) with SYS_EXTRACT_UTC, or use conversions with CAST(value AS TIME ZONE … AS TIMESTAMP).
Therefore, if your databases are used by several countries, the recommendation is to stop using SYSDATE and use SYSTIMESTAMP, LOCALTIMESTAMP or CURRENT_TIMESTAMP.
Should I migrate my database DST?
Well, the decision is yours, but you have to keep in mind that updating the DST implies upgrading the database and requires a shutdown. You should also keep in mind that data from a recent version DST cannot be exported/imported to a database that has an older DST.
To help you decide, it is also important that you can take a look at those tables that are going to be affected.
To do this, when the BEGIN_PREPARE(version) of the DBMS_DST package is invoked, it provides information in the SYS.DST$AFFECTED_TABLES table and this will allow us to evaluate both the number of affected tables for each user and the number of affected rows.
SQL> desc dst$affected_tables Nombre ┐Nulo? Tipo ----------------------------------------- -------- ---------------------------- TABLE_OWNER NOT NULL VARCHAR2(128) TABLE_NAME NOT NULL VARCHAR2(128) COLUMN_NAME NOT NULL VARCHAR2(4000) ROW_COUNT NUMBER ERROR_COUNT NUMBER
In parallel, we can obtain more information about these cases in the DST$ERROR_TABLE table.
SQL> desc DST$ERROR_TABLE
Nombre ┐Nulo? Tipo
----------------------------------------- -------- ----------------------------
TABLE_OWNER NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
COLUMN_NAME NOT NULL VARCHAR2(4000)
RID ROWID
ERROR_NUMBER NUMBER
If we see everything correct, the procedure to migrate the DST is as follows:
- Stop the database, including all instances if it is a RAC.
- Shootdown and startup in UPGRADE mode.
- Launch DBMS_DST.BEGIN_UPGRADE(version);
- Restart the database in NORMAL mode.
- Launch the DBMS_DST.UPGRADE_DATABASE and END_UPGRADE.
For more information, in this support note you have all the details.
Primary Note DST FAQ : Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)