Oracle: Timezone upgrade - the easy way

From time to time, countries change their time zone, or the daylight saving time changes (in Germany, we wait for that since years).

As some date columns include this information - like TIMESTAMP WITH TIME ZONE, they have to be updated. These updates are called Timezone upgrades.

The standard way

To upgrade timezone information, you can follow this instruction from Oracle-Base. It include the following steps:

• check current time zone version
• prepare for upgrade
• upgrade time zone
  • non$CDB or
  • Multitenant
• check

The easy way

I'm much too lazy for the steps above. And as they include many manual steps, each step can be faulty. So the probability to have an error is quite high.

The new Autoupgrade utility is a great tool to do timezone upgrade by executing a single command:

rlwrap $ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -mode deploy -config_values target_home="$ORACLE_HOME,sid=$ORACLE_SID,run_utlrp=yes,timezone_upg=yes"

Command parts:

• rlwrap: I use it on Linux command for not having to type every command manually if I repeat the same commands
• $ORACLE_HOME/jdk/bin/java is the Java binary from the current Oracle home JDK
• -mode deploy means that upgrading will be executed immediatelly
• -config_values says that parameters will be given within the command line instead of a configuration file
  • $ORACLE_HOME is the home path of the running Oracle database
  • sid is the ORACLE_SID of the database
  • run_utlrp determines of UTLRP should be run at the end
  • timezone_upg configures if timezone upgrade will be run - we set it to yes as this is the only part we really want to do now

Running Autoupgrade for time zone upgrades will need the same time as doing it manually, but it will do it from the beginning to the end.