First of all, we need to check the current time zone version that we already have.<\/p>\n
\nSQL> SELECT version FROM v$timezone_file;\n\n VERSION\n----------\n 31\n<\/pre>\nThe version FROM v$timezone_file should match the DST_PRIMARY_TT_VERSION value found when selecting from DATABASE_PROPERTIES<\/p>\n
\nSQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value\n 2 FROM DATABASE_PROPERTIES\n 3 WHERE PROPERTY_NAME LIKE 'DST_%'\n 4 ORDER BY PROPERTY_NAME;\n\nPROPERTY_NAME VALUE\n-------------------------------------------- --------\nDST_PRIMARY_TT_VERSION 31\nDST_SECONDARY_TT_VERSION 0\nDST_UPGRADE_STATE NONE\n<\/pre>\nHowever, we want to go to version 33. So, lets see what we got in $ORACLE_HOME\/oracore\/zoneinfo\/ directory.<\/p>\n
\noracle@sun1:\/u01\/app\/oracle\/product\/18.5.0\/oracore\/zoneinfo$ ls\nbig timezlrg_14.dat timezlrg_22.dat timezlrg_30.dat timezone_10.dat timezone_19.dat timezone_27.dat timezone_7.dat\nlittle timezlrg_15.dat timezlrg_23.dat timezlrg_31.dat timezone_11.dat timezone_2.dat timezone_28.dat timezone_8.dat\nreadme.txt timezlrg_16.dat timezlrg_24.dat timezlrg_4.dat timezone_12.dat timezone_20.dat timezone_29.dat timezone_9.dat\ntimezdif.csv timezlrg_17.dat timezlrg_25.dat timezlrg_5.dat timezone_13.dat timezone_21.dat timezone_3.dat\ntimezlrg_1.dat timezlrg_18.dat timezlrg_26.dat timezlrg_6.dat timezone_14.dat timezone_22.dat timezone_30.dat\ntimezlrg_10.dat timezlrg_19.dat timezlrg_27.dat timezlrg_7.dat timezone_15.dat timezone_23.dat timezone_31.dat\ntimezlrg_11.dat timezlrg_2.dat timezlrg_28.dat timezlrg_8.dat timezone_16.dat timezone_24.dat timezone_4.dat\ntimezlrg_12.dat timezlrg_20.dat timezlrg_29.dat timezlrg_9.dat timezone_17.dat timezone_25.dat timezone_5.dat\ntimezlrg_13.dat timezlrg_21.dat timezlrg_3.dat timezone_1.dat timezone_18.dat timezone_26.dat timezone_6.dat\noracle@sun1:\/u01\/app\/oracle\/product\/18.5.0\/oracore\/zoneinfo$\n<\/pre>\nAt the moment, we got only the time zone version 31 shipped with the default installation.<\/p>\n
The general steps for upgrading to a new time zone version are:<\/p>\n
\n- Take a RMAN full backup<\/li>\n
- Create a guaranteed restore point<\/li>\n
- Shutdown the application<\/li>\n
- Apply the time zone patch<\/li>\n
- Run the check and upgrade scripts for the CDB<\/li>\n
- Run the check and upgrade scripts for the PDB’s<\/li>\n
- Start the application<\/li>\n<\/ol>\n
In this blog, I will not go into detail regarding RMAN or Flashback database. I will cover here only step 4,5 and 6.<\/p>\n
Ok. Lets start by downloading the following patch, and to apply it.<\/p>\n
\n- Patch 28852325: RDBMS – DSTV33 UPDATE – TZDATA2018G<\/li>\n<\/ul>\n
The patch just includes the following files which are copied to the $ORACLE_HOME\/oracore\/zoneinfo\/ directory.<\/p>\n
\noracle@sun1:\/u01\/stage\/18c\/TimeZone\/28852325\/files\/oracore\/zoneinfo$ ls -l\ntotal 2435\ndrwxr-x--- 2 oracle oinstall 4 Mar 15 13:12 big\ndrwxr-x--- 2 oracle oinstall 4 Mar 15 13:12 little\n-rw-r--r-- 1 oracle oinstall 58353 Mar 6 19:27 readme_33.txt\n-rw-r--r-- 1 oracle oinstall 785523 Mar 6 19:27 timezlrg_33.dat\n-rw-r--r-- 1 oracle oinstall 341123 Mar 6 19:27 timezone_33.dat\n\nHINT: This patch can be applied online without taking the database down.\n\noracle@sun1:\/u01\/stage\/18c\/TimeZone$ opatch apply 28852325\nOracle Interim Patch Installer version 12.2.0.1.16\nCopyright (c) 2019, Oracle Corporation. All rights reserved.\n\n[sourcecode language="plain"]\nOracle Home : \/u01\/app\/oracle\/product\/18.5.0\nCentral Inventory : \/u01\/app\/oraInventory\n from : \/u01\/app\/oracle\/product\/18.5.0\/oraInst.loc\nOPatch version : 12.2.0.1.16\nOUI version : 12.2.0.4.0\nLog file location : \/u01\/app\/oracle\/product\/18.5.0\/cfgtoollogs\/opatch\/opatch2019-03-15_13-13-08PM_1.log\n\nVerifying environment and performing prerequisite checks...\nOPatch continues with these patches: 28852325\n\nDo you want to proceed? [y|n]\ny\nUser Responded with: Y\nAll checks passed.\nBacking up files...\nApplying interim patch '28852325' to OH '\/u01\/app\/oracle\/product\/18.5.0'\n\nPatching component oracle.oracore.rsf, 18.0.0.0.0...\nPatch 28852325 successfully applied.\nLog file location: \/u01\/app\/oracle\/product\/18.5.0\/cfgtoollogs\/opatch\/opatch2019-03-15_13-13-08PM_1.log\n\nOPatch succeeded.\n\noracle@sun1:\/u01\/stage\/18c\/TimeZone$ opatch lsinv | grep 28852325\nPatch 28852325 : applied on Fri Mar 15 13:13:25 CET 2019\n 28852325\n<\/pre>\nAfter we successfully applied the patch, we can see a few more file in the $ORACLE_HOME\/oracore\/zoneinfo\/ directory. The important one for me is timezone_33.dat.<\/p>\n
\noracle@sun1:\/u01\/app\/oracle\/product\/18.5.0\/oracore\/zoneinfo$ ls\nbig timezlrg_13.dat timezlrg_21.dat timezlrg_3.dat timezlrg_9.dat timezone_17.dat timezone_25.dat timezone_4.dat\nlittle timezlrg_14.dat timezlrg_22.dat timezlrg_30.dat timezone_1.dat timezone_18.dat timezone_26.dat timezone_5.dat\nreadme.txt timezlrg_15.dat timezlrg_23.dat timezlrg_31.dat timezone_10.dat timezone_19.dat timezone_27.dat timezone_6.dat\nreadme_33.txt timezlrg_16.dat timezlrg_24.dat timezlrg_33.dat timezone_11.dat timezone_2.dat timezone_28.dat timezone_7.dat\ntimezdif.csv timezlrg_17.dat timezlrg_25.dat timezlrg_4.dat timezone_12.dat timezone_20.dat timezone_29.dat timezone_8.dat\ntimezlrg_1.dat timezlrg_18.dat timezlrg_26.dat timezlrg_5.dat timezone_13.dat timezone_21.dat timezone_3.dat timezone_9.dat\ntimezlrg_10.dat timezlrg_19.dat timezlrg_27.dat timezlrg_6.dat timezone_14.dat timezone_22.dat timezone_30.dat\ntimezlrg_11.dat timezlrg_2.dat timezlrg_28.dat timezlrg_7.dat timezone_15.dat timezone_23.dat timezone_31.dat\ntimezlrg_12.dat timezlrg_20.dat timezlrg_29.dat timezlrg_8.dat timezone_16.dat timezone_24.dat timezone_33.dat\noracle@sun1:\/u01\/app\/oracle\/product\/18.5.0\/oracore\/zoneinfo$\n<\/pre>\nStarting with 18c, you will find a few new scripts in the $ORACLE_HOME\/rdbms\/admin directory.<\/p>\n
\noracle@sun1:\/u01\/app\/oracle\/product\/18.5.0\/rdbms\/admin$ ls | grep utltz_\nutltz_countstar.sql\nutltz_countstats.sql\nutltz_upg_apply.sql\nutltz_upg_check.sql\n<\/pre>\nHowever, in case you are still on 12cR2 or 12cR1 you might want to download the scripts from the following MOS Note:<\/p>\n
\n- Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1)<\/li>\n<\/ul>\n
Which is, DBMS_DST_scriptsV1.9 at the time of writing.<\/p>\n
\noracle@sun1:\/u01\/stage\/18c\/TimeZone$ unzip DBMS_DST_scriptsV1.9.zip\nArchive: DBMS_DST_scriptsV1.9.zip\n creating: DBMS_DST_scriptsV1.9\/\n inflating: DBMS_DST_scriptsV1.9\/countstarTSTZ.sql\n inflating: DBMS_DST_scriptsV1.9\/countstatsTSTZ.sql\n inflating: DBMS_DST_scriptsV1.9\/upg_tzv_apply.sql\n inflating: DBMS_DST_scriptsV1.9\/upg_tzv_check.sql\n<\/pre>\nI am going to use the supplied scripts which are shipped with 18c, because they include the latest fixes, especially regarding PDB’s.<\/p>\n
\noracle@sun1:\/u01\/app\/oracle\/product\/18.5.0\/rdbms\/admin$ cat utltz_upg_check.sql\n...\n...\nRem MODIFIED (MM\/DD\/YY)\nRem huagli 08\/31\/17 - 26721930: DB version change\nRem huagli 06\/09\/17 - 25988996: CDB\/PDB RAC check and various cleanup\nRem huagli 04\/07\/17 - 25856520: handle PDB name correctly\nRem huagli 01\/31\/17 - renamed to utltz_upg_check.sql and added to shiphome\nRem gvermeir 08\/22\/14 - updated to handle CDB\/PDB (Multitenant) DST updates\nRem gvermeir 07\/10\/14 - changed 1882 in DST$ERROR_TABLE from error to warning\nRem gvermeir 05\/23\/14 - changed detection of Bug 14732853 to avoid using DBA_TSTZ_TAB_COLS\n...\n...\n<\/pre>\nThe scripts supplied with the DBMS_DST_scriptsV1.9.zip, do not contain the latest fixes from 2017.<\/p>\n
\noracle@sun1:\/u01\/stage\/18c\/TimeZone\/DBMS_DST_scriptsV1.9$ cat upg_tzv_check.sql\n...\n...\nRem MODIFIED (MM\/DD\/YY)\nRem gvermeir 08\/22\/14 - updated to handle CDB\/PDB (Multitenant) DST updates\nRem gvermeir 07\/10\/14 - changed 1882 in DST$ERROR_TABLE from error to warning\nRem gvermeir 05\/23\/14 - changed detection of Bug 14732853 to avoid using DBA_TSTZ_TAB_COLS\nRem gvermeir 03\/17\/14 - logging of time makes more sense in minutes\nRem gvermeir 03\/04\/14 - known bug detection is now faster on some dbs\nRem gvermeir 02\/20\/14 - added logging to alert.log\n...\n...\n<\/pre>\nNow … be careful. The next steps require a downtime. It is not possible to upgrade a TZ without a downtime. Only the TZ patch itself can be applied online.<\/p>\n
Before we run the TZ upgrade scripts, you might want to purge the Scheduler History and the DBMS_STATS history, to speed up the TZ Upgrade. These steps are optional.<\/p>\n
\nSQL> EXEC DBMS_SCHEDULER.PURGE_LOG;\n\nPL\/SQL procedure successfully completed.\n\nSQL> EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(0);\n\nPL\/SQL procedure successfully completed.\n\nSQL> EXEC DBMS_STATS.PURGE_STATS(SYSTIMESTAMP);\n\nPL\/SQL procedure successfully completed.\n\n--\n-- Do the TZ Upgrade.\n--\n \nSQL> EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(31);\n\nPL\/SQL procedure successfully completed.\n<\/pre>\nNow we do the Time Zone Upgrade on the CDB18 18.1.5 Database.<\/p>\n
\n\nSQL> select version from v$timezone_file;\n\n VERSION\n----------\n 31\n \n<\/pre>\nApply Timezone Patch for Version 33 and do the Upgrade Check.<\/p>\n
\noracle@sun1:\/u01\/app\/oracle\/product\/18.5.0\/rdbms\/admin$ ls | grep utltz_\nutltz_countstar.sql\nutltz_countstats.sql\nutltz_upg_apply.sql\nutltz_upg_check.sql\noracle@sun1:\/u01\/app\/oracle\/product\/18.5.0\/rdbms\/admin$ sq\n\nSQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 15 14:59:54 2019\nVersion 18.5.0.0.0\n\nCopyright (c) 1982, 2018, Oracle. All rights reserved.\n\nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.5.0.0.0\n\nSQL> @utltz_upg_check.sql\n\nSession altered.\n\nINFO: Starting with RDBMS DST update preparation.\nINFO: NO actual RDBMS DST update will be done by this script.\nINFO: If an ERROR occurs the script will EXIT sqlplus.\nINFO: Doing checks for known issues ...\nINFO: Database version is 18.0.0.0 .\nINFO: This database is a Multitenant database.\nINFO: Current container is CDB$ROOT .\nINFO: Updating the RDBMS DST version of the CDB \/ CDB$ROOT database\nINFO: will NOT update the RDBMS DST version of PDB databases in this CDB.\nWARNING: There are 1 open PDBs .\nWARNING: They will be closed when running utltz_upg_apply.sql .\nINFO: Database RDBMS DST version is DSTv31 .\nINFO: No known issues detected.\nINFO: Now detecting new RDBMS DST version.\nA prepare window has been successfully started.\nINFO: Newest RDBMS DST version detected is DSTv33 .\nINFO: Next step is checking all TSTZ data.\nINFO: It might take a while before any further output is seen ...\nA prepare window has been successfully ended.\nINFO: A newer RDBMS DST version than the one currently used is found.\nINFO: Note that NO DST update was yet done.\nINFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.\nINFO: Note that the utltz_upg_apply.sql script will\nINFO: restart the database 2 times WITHOUT any confirmation or prompt.\n\nSession altered.\n\nSQL>\n<\/pre>\nIn the alert log you will see the following entry<\/p>\n
\n...\n\n2019-03-15T15:00:04.719062+01:00\nutltz_upg_check sucessfully found newer RDBMS DSTv33 and took 0 minutes to run.\n...\n<\/pre>\nRun the upgrade … and be careful, this script will restart the database 2 times.<\/p>\n
\nSQL> @utltz_upg_apply.sql\n\nSession altered.\n\nINFO: If an ERROR occurs, the script will EXIT SQL*Plus.\nINFO: The database RDBMS DST version will be updated to DSTv33 .\nINFO: This database is a Multitenant database.\nINFO: Current container is CDB$ROOT .\nINFO: Updating the RDBMS DST version of the CDB \/ CDB$ROOT database\nINFO: will NOT update the RDBMS DST version of PDB databases in this CDB.\nWARNING: There are 1 open PDBs .\nWARNING: They will be closed when CDB$ROOT is restarted\nWARNING: This script will restart the database 2 times\nWARNING: WITHOUT asking ANY confirmation.\nWARNING: Hit control-c NOW if this is not intended.\nINFO: Restarting the database in UPGRADE mode to start the DST upgrade.\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\nORACLE instance started.\n\nTotal System Global Area 4294965728 bytes\nFixed Size 8894944 bytes\nVariable Size 989855744 bytes\nDatabase Buffers 3288334336 bytes\nRedo Buffers 7880704 bytes\nDatabase mounted.\nDatabase opened.\nINFO: Starting the RDBMS DST upgrade.\nINFO: Upgrading all SYS owned TSTZ data.\nINFO: It might take time before any further output is seen ...\nAn upgrade window has been successfully started.\nINFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\nORACLE instance started.\n\nTotal System Global Area 4294965728 bytes\nFixed Size 8894944 bytes\nVariable Size 989855744 bytes\nDatabase Buffers 3288334336 bytes\nRedo Buffers 7880704 bytes\nDatabase mounted.\nDatabase opened.\nINFO: Upgrading all non-SYS TSTZ data.\nINFO: It might take time before any further output is seen ...\nINFO: Do NOT start any application yet that uses TSTZ data!\nINFO: Next is a list of all upgraded tables:\nTable list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"\nNumber of failures: 0\nTable list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"\nNumber of failures: 0\nTable list: "APEX_050100"."WWV_FLOW_WORKSHEET_NOTIFY"\nNumber of failures: 0\nTable list: "APEX_050100"."WWV_FLOW_FEEDBACK"\nNumber of failures: 0\nTable list: "APEX_050100"."WWV_FLOW_FEEDBACK_FOLLOWUP"\nNumber of failures: 0\nTable list: "APEX_050100"."WWV_FLOW_DEBUG_MESSAGES"\nNumber of failures: 0\nTable list: "APEX_050100"."WWV_FLOW_DEBUG_MESSAGES2"\nNumber of failures: 0\nINFO: Total failures during update of TSTZ data: 0 .\nAn upgrade window has been successfully ended.\nINFO: Your new Server RDBMS DST version is DSTv33 .\nINFO: The RDBMS DST update is successfully finished.\nINFO: Make sure to exit this SQL*Plus session.\nINFO: Do not use it for timezone related selects.\n\nSession altered.\n\nSQL>\n<\/pre>\nIn the alert.log you will see<\/p>\n
\n...\n\n2019-03-15T15:02:27.513093+01:00\nutltz_upg_apply is ready to update to RDBMS DSTv33 and will now restart the database in UPGRADE mode.\nShutting down instance (immediate) (OS id: 6310)\nStopping background process SMCO\n2019-03-15T15:02:28.928167+01:00\n...\n...\nALTER pluggable DATABASE OPEN upgrade\nORA-65000 signalled during: ALTER pluggable DATABASE OPEN upgrade...\n...\n...\n2019-03-15T15:08:09.401025+01:00\nutltz_upg_apply sucessfully updated this database to RDBMS DSTv33 and took 6 minutes to run.\n\n...\n<\/pre>\nCheck the new version<\/p>\n
\nSQL> show con_name\n\nCON_NAME\n------------------------------\nCDB$ROOT\n\nSQL> select version from v$timezone_file;\n\n VERSION\n----------\n 33\n\nSQL> select count(*) from dba_invalid_objects;\n\n COUNT(*)\n----------\n 0\n<\/pre>\nKeep in mind. Updating the RDBMS DST version of the CDB will not change the RDBMS_DST version of the PDB’s in this CDB and updating the RDBMS DST version of a PDB will not change the RDBMS_DST version of the other PDB’s or the CDB.<\/p>\n
\nSQL> connect system@PDB1\nEnter password:\nConnected.\nSQL> show con_name\n\nCON_NAME\n------------------------------\nPDB1\n\nSQL> select version from v$timezone_file;\n\n VERSION\n----------\n 31\n\nSQL> @utltz_upg_check.sql\n\nSession altered.\n\nINFO: Starting with RDBMS DST update preparation.\nINFO: NO actual RDBMS DST update will be done by this script.\nINFO: If an ERROR occurs the script will EXIT sqlplus.\nINFO: Doing checks for known issues ...\nINFO: Database version is 18.0.0.0 .\nINFO: This database is a Multitenant database.\nINFO: This database is a PDB.\nINFO: Current PDB is PDB1 .\nINFO: Database RDBMS DST version is DSTv31 .\nINFO: No known issues detected.\nINFO: Now detecting new RDBMS DST version.\nA prepare window has been successfully started.\nINFO: Newest RDBMS DST version detected is DSTv33 .\nINFO: Next step is checking all TSTZ data.\nINFO: It might take a while before any further output is seen ...\nA prepare window has been successfully ended.\nINFO: A newer RDBMS DST version than the one currently used is found.\nINFO: Note that NO DST update was yet done.\nINFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.\nINFO: Note that the utltz_upg_apply.sql script will\nINFO: restart the database 2 times WITHOUT any confirmation or prompt.\n\nSession altered.\n\nSQL>\n\t\nSQL> @utltz_upg_apply.sql\n\nSession altered.\n\nINFO: If an ERROR occurs, the script will EXIT SQL*Plus.\nINFO: The database RDBMS DST version will be updated to DSTv33 .\nINFO: This database is a Multitenant database.\nINFO: This database is a PDB.\nINFO: Current PDB is PDB1 .\nWARNING: This script will restart the database 2 times\nWARNING: WITHOUT asking ANY confirmation.\nWARNING: Hit control-c NOW if this is not intended.\nINFO: Restarting the database in UPGRADE mode to start the DST upgrade.\nPluggable Database closed.\nPluggable Database opened.\nINFO: Starting the RDBMS DST upgrade.\nINFO: Upgrading all SYS owned TSTZ data.\nINFO: It might take time before any further output is seen ...\nAn upgrade window has been successfully started.\nINFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.\nPluggable Database closed.\nPluggable Database opened.\nINFO: Upgrading all non-SYS TSTZ data.\nINFO: It might take time before any further output is seen ...\nINFO: Do NOT start any application yet that uses TSTZ data!\nINFO: Next is a list of all upgraded tables:\nTable list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"\nNumber of failures: 0\nTable list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"\nNumber of failures: 0\nTable list: "APEX_050100"."WWV_FLOW_WORKSHEET_NOTIFY"\nNumber of failures: 0\nTable list: "APEX_050100"."WWV_FLOW_FEEDBACK"\nNumber of failures: 0\nTable list: "APEX_050100"."WWV_FLOW_FEEDBACK_FOLLOWUP"\nNumber of failures: 0\nTable list: "APEX_050100"."WWV_FLOW_DEBUG_MESSAGES"\nNumber of failures: 0\nTable list: "APEX_050100"."WWV_FLOW_DEBUG_MESSAGES2"\nNumber of failures: 0\nINFO: Total failures during update of TSTZ data: 0 .\nAn upgrade window has been successfully ended.\nINFO: Your new Server RDBMS DST version is DSTv33 .\nINFO: The RDBMS DST update is successfully finished.\nINFO: Make sure to exit this SQL*Plus session.\nINFO: Do not use it for timezone related selects.\n\nSession altered.\n\nSQL>\n\noracle@sun1:\/u01\/app\/oracle\/product\/18.5.0\/rdbms\/admin$ sqlplus sys@PDB1 as sysdba\n\nSQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 15 15:18:49 2019\nVersion 18.5.0.0.0\n\nCopyright (c) 1982, 2018, Oracle. All rights reserved.\n\nEnter password:\n\nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.5.0.0.0\n\nSQL> show con_name\n\nCON_NAME\n------------------------------\nPDB1\nSQL> select version from v$timezone_file;\n\n VERSION\n----------\n 33\n\nSQL> select count(*) from dba_invalid_objects;\n\n COUNT(*)\n----------\n 0\n\nSQL>\n<\/pre>\nIn case you want to monitor the progress of the time zone upgrade, you might want to execute the following scripts while the script utltz_upg_apply.sql is running.<\/p>\n
\nSELECT count(*) FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';\n\nset PAGES 1000\nselect TARGET, TO_CHAR(START_TIME,'HH24:MI:SS - DD-MM-YY'), TIME_REMAINING, SOFAR,\nTOTALWORK, SID, SERIAL#, OPNAME from V$SESSION_LONGOPS\nwhere sid in\n(select SID from V$SESSION where CLIENT_INFO = 'upg_tzv')\nand SOFAR < TOTALWORK\norder by START_TIME;\n\nselect S.SID, S.SERIAL#, S.SQL_ID, S.PREV_SQL_ID,\nS.EVENT#, S.EVENT, S.P1TEXT, S.P1, S.P2TEXT, S.P2, S.P3TEXT, S.P3, S.TIME_REMAINING_MICRO,\nS.SEQ#, S.BLOCKING_SESSION, BS.PROGRAM "Blocking Program",\nQ1.SQL_TEXT "Current SQL", Q2.SQL_TEXT "Previous SQL"\nfrom V$SESSION S, V$SQLAREA Q1, V$SQLAREA Q2, V$SESSION BS\nwhere S.SQL_ID = Q1.SQL_ID(+)\nand S.PREV_SQL_ID = Q2.SQL_ID(+)\nand S.BLOCKING_SESSION = BS.SID(+)\nand S.CLIENT_INFO = 'upg_tzv';\n<\/pre>\nYou might find the following MOS Notes helpful if you want to know more about time zone upgrades.<\/p>\n
\n- Download Time Zone Patch from MOS Note 412160.1<\/li>\n
- Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1)<\/li>\n
- Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)<\/li>\n<\/ul>\n
Conclusion<\/h3>\n
Time zone changes happens every now and then and it is good know how you can upgrade your Oracle database to the latest time zone release, in case you need too. However, do very good testing, before you run these scripts on your production database. And take care, that this is not an online procedure. The CDB is restarted several times, and the PDB’s are not upgraded automatically. You need to take care of the PDB’s yourself.<\/p>\n","protected":false},"excerpt":{"rendered":"
Why do I need time zone updates at all? Because nothing is more constant than the changes. And one of those are the time zone changes. Some countries remove the summer\/winter time changes, and some new time zones appear, which have never existed beforehand. In case you want to see all changes, since Oracle has […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ocean_post_layout":"","ocean_both_sidebars_style":"","ocean_both_sidebars_content_width":0,"ocean_both_sidebars_sidebars_width":0,"ocean_sidebar":"","ocean_second_sidebar":"","ocean_disable_margins":"enable","ocean_add_body_class":"","ocean_shortcode_before_top_bar":"","ocean_shortcode_after_top_bar":"","ocean_shortcode_before_header":"","ocean_shortcode_after_header":"","ocean_has_shortcode":"","ocean_shortcode_after_title":"","ocean_shortcode_before_footer_widgets":"","ocean_shortcode_after_footer_widgets":"","ocean_shortcode_before_footer_bottom":"","ocean_shortcode_after_footer_bottom":"","ocean_display_top_bar":"default","ocean_display_header":"default","ocean_header_style":"","ocean_center_header_left_menu":"","ocean_custom_header_template":"","ocean_custom_logo":0,"ocean_custom_retina_logo":0,"ocean_custom_logo_max_width":0,"ocean_custom_logo_tablet_max_width":0,"ocean_custom_logo_mobile_max_width":0,"ocean_custom_logo_max_height":0,"ocean_custom_logo_tablet_max_height":0,"ocean_custom_logo_mobile_max_height":0,"ocean_header_custom_menu":"","ocean_menu_typo_font_family":"","ocean_menu_typo_font_subset":"","ocean_menu_typo_font_size":0,"ocean_menu_typo_font_size_tablet":0,"ocean_menu_typo_font_size_mobile":0,"ocean_menu_typo_font_size_unit":"px","ocean_menu_typo_font_weight":"","ocean_menu_typo_font_weight_tablet":"","ocean_menu_typo_font_weight_mobile":"","ocean_menu_typo_transform":"","ocean_menu_typo_transform_tablet":"","ocean_menu_typo_transform_mobile":"","ocean_menu_typo_line_height":0,"ocean_menu_typo_line_height_tablet":0,"ocean_menu_typo_line_height_mobile":0,"ocean_menu_typo_line_height_unit":"","ocean_menu_typo_spacing":0,"ocean_menu_typo_spacing_tablet":0,"ocean_menu_typo_spacing_mobile":0,"ocean_menu_typo_spacing_unit":"","ocean_menu_link_color":"","ocean_menu_link_color_hover":"","ocean_menu_link_color_active":"","ocean_menu_link_background":"","ocean_menu_link_hover_background":"","ocean_menu_link_active_background":"","ocean_menu_social_links_bg":"","ocean_menu_social_hover_links_bg":"","ocean_menu_social_links_color":"","ocean_menu_social_hover_links_color":"","ocean_disable_title":"default","ocean_disable_heading":"default","ocean_post_title":"","ocean_post_subheading":"","ocean_post_title_style":"","ocean_post_title_background_color":"","ocean_post_title_background":0,"ocean_post_title_bg_image_position":"","ocean_post_title_bg_image_attachment":"","ocean_post_title_bg_image_repeat":"","ocean_post_title_bg_image_size":"","ocean_post_title_height":0,"ocean_post_title_bg_overlay":0.5,"ocean_post_title_bg_overlay_color":"","ocean_disable_breadcrumbs":"default","ocean_breadcrumbs_color":"","ocean_breadcrumbs_separator_color":"","ocean_breadcrumbs_links_color":"","ocean_breadcrumbs_links_hover_color":"","ocean_display_footer_widgets":"default","ocean_display_footer_bottom":"default","ocean_custom_footer_template":"","ocean_post_oembed":"","ocean_post_self_hosted_media":"","ocean_post_video_embed":"","ocean_link_format":"","ocean_link_format_target":"self","ocean_quote_format":"","ocean_quote_format_link":"post","ocean_gallery_link_images":"on","ocean_gallery_id":[],"footnotes":""},"categories":[4],"tags":[18,25],"class_list":["post-140","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle","tag-timezone","entry"],"yoast_head":"\n
How To Upgrade The TimeZone Version On Oracle 18.1.5 CDB With PDB's From Version 31 to 33 - ptdb - Platinum DB<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/ptdb.ch\/?p=140\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Upgrade The TimeZone Version On Oracle 18.1.5 CDB With PDB's From Version 31 to 33 - ptdb - Platinum DB\" \/>\n<meta property=\"og:description\" content=\"Why do I need time zone updates at all? Because nothing is more constant than the changes. And one of those are the time zone changes. Some countries remove the summer\/winter time changes, and some new time zones appear, which have never existed beforehand. In case you want to see all changes, since Oracle has […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/ptdb.ch\/?p=140\" \/>\n<meta property=\"og:site_name\" content=\"ptdb - Platinum DB\" \/>\n<meta property=\"article:published_time\" content=\"2020-11-17T07:25:37+00:00\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"17 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/ptdb.ch\/?p=140\",\"url\":\"https:\/\/ptdb.ch\/?p=140\",\"name\":\"How To Upgrade The TimeZone Version On Oracle 18.1.5 CDB With PDB's From Version 31 to 33 - ptdb - Platinum DB\",\"isPartOf\":{\"@id\":\"https:\/\/ptdb.ch\/#website\"},\"datePublished\":\"2020-11-17T07:25:37+00:00\",\"author\":{\"@id\":\"https:\/\/ptdb.ch\/#\/schema\/person\/0b7baf52d23e71d85e1c95442306090b\"},\"breadcrumb\":{\"@id\":\"https:\/\/ptdb.ch\/?p=140#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/ptdb.ch\/?p=140\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/ptdb.ch\/?p=140#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/ptdb.ch\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Upgrade The TimeZone Version On Oracle 18.1.5 CDB With PDB’s From Version 31 to 33\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/ptdb.ch\/#website\",\"url\":\"https:\/\/ptdb.ch\/\",\"name\":\"ptdb - Platinum DB\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/ptdb.ch\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/ptdb.ch\/#\/schema\/person\/0b7baf52d23e71d85e1c95442306090b\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/ptdb.ch\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1a3dffc48c5f6bae0b88a9f0b2a986d48d322673fbc2880c5abbfab96e45da8a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1a3dffc48c5f6bae0b88a9f0b2a986d48d322673fbc2880c5abbfab96e45da8a?s=96&d=mm&r=g\",\"caption\":\"admin\"},\"sameAs\":[\"https:\/\/ptdb.ch\"],\"url\":\"https:\/\/ptdb.ch\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How To Upgrade The TimeZone Version On Oracle 18.1.5 CDB With PDB's From Version 31 to 33 - ptdb - Platinum DB","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/ptdb.ch\/?p=140","og_locale":"en_US","og_type":"article","og_title":"How To Upgrade The TimeZone Version On Oracle 18.1.5 CDB With PDB's From Version 31 to 33 - ptdb - Platinum DB","og_description":"Why do I need time zone updates at all? Because nothing is more constant than the changes. And one of those are the time zone changes. Some countries remove the summer\/winter time changes, and some new time zones appear, which have never existed beforehand. In case you want to see all changes, since Oracle has […]","og_url":"https:\/\/ptdb.ch\/?p=140","og_site_name":"ptdb - Platinum DB","article_published_time":"2020-11-17T07:25:37+00:00","author":"admin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"admin","Est. reading time":"17 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/ptdb.ch\/?p=140","url":"https:\/\/ptdb.ch\/?p=140","name":"How To Upgrade The TimeZone Version On Oracle 18.1.5 CDB With PDB's From Version 31 to 33 - ptdb - Platinum DB","isPartOf":{"@id":"https:\/\/ptdb.ch\/#website"},"datePublished":"2020-11-17T07:25:37+00:00","author":{"@id":"https:\/\/ptdb.ch\/#\/schema\/person\/0b7baf52d23e71d85e1c95442306090b"},"breadcrumb":{"@id":"https:\/\/ptdb.ch\/?p=140#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/ptdb.ch\/?p=140"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/ptdb.ch\/?p=140#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/ptdb.ch\/"},{"@type":"ListItem","position":2,"name":"How To Upgrade The TimeZone Version On Oracle 18.1.5 CDB With PDB’s From Version 31 to 33"}]},{"@type":"WebSite","@id":"https:\/\/ptdb.ch\/#website","url":"https:\/\/ptdb.ch\/","name":"ptdb - Platinum DB","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/ptdb.ch\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/ptdb.ch\/#\/schema\/person\/0b7baf52d23e71d85e1c95442306090b","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/ptdb.ch\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/1a3dffc48c5f6bae0b88a9f0b2a986d48d322673fbc2880c5abbfab96e45da8a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1a3dffc48c5f6bae0b88a9f0b2a986d48d322673fbc2880c5abbfab96e45da8a?s=96&d=mm&r=g","caption":"admin"},"sameAs":["https:\/\/ptdb.ch"],"url":"https:\/\/ptdb.ch\/?author=1"}]}},"_links":{"self":[{"href":"https:\/\/ptdb.ch\/index.php?rest_route=\/wp\/v2\/posts\/140","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ptdb.ch\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ptdb.ch\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ptdb.ch\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ptdb.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=140"}],"version-history":[{"count":0,"href":"https:\/\/ptdb.ch\/index.php?rest_route=\/wp\/v2\/posts\/140\/revisions"}],"wp:attachment":[{"href":"https:\/\/ptdb.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=140"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ptdb.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=140"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ptdb.ch\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=140"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}