3 Replies Latest reply on Dec 5, 2012 9:39 PM by Srini Chavali-Oracle

    going from timezone version 3 to 4

      Upgrading a db to

      What are the ramifications of just doing it without dumping the data out and loading it back in?

      Near as I can tell it's that the DST switch might result in a timestamp being off an hour?

      Which that's not good, but is there anything else? The upgrade notes indicate that if you are using timezone aware columns that you should dump
      the data out and load / update it back in afterwards.

      That's just not practical on a several TB database. That and 99% of my data is not effected by this. Rather then dump ALL the data
      (I have over a hundred effected columns) can I just dump out the data to another place for the ones close to the time flip and update those after upgrading?

      Just wondering if I missed other ramifications of this.
        • 1. Re: going from timezone version 3 to 4
          Srini Chavali-Oracle
          Can you pl post a link to where you are getting your information from ? Pl see 1A and 3B in this MOS Doc

          Actions for the DSTv4 update in the patchset [ID 1086400.1]

          • 2. Re: going from timezone version 3 to 4

            go to document 1086400.1 as specified in that.

            and in that document see the note
            "If a "select * from sys.sys_tzuv2_temptab;" give(s) affected columns then you need to back up the reported columns before upgrading to See point 5) on how to do this. For SYS.SCHEDULER$% rows you need to drop and re-submit the jobs (see previous point)."

            You get that by running the script supplied by oracle which really all that is doing is going off dba_tab_columns to see what tables have time zone aware columns.

            As further explained the purpose of this is they want you save off the timezone aware data so that after you upgrade your engine to and the resultant timezone files are upgraded you can update the data back (or re-import or whatever) so that oracle is using the version 4 timezone files as the data goes back in.

            My question still stands though. The only difference between 3 and 4 are some daylight savings changes I think.

            So for example a piece of data that has timezone info in it, but is in, say, July is completely uneffected by this.

            So I'm getting this info out of the same document # that you are listing... Granted oracle's documentation is rather byzantine to wind your way through but if you follow the breadcrumbs around that's basically it in a nutshell.
            • 3. Re: going from timezone version 3 to 4
              Srini Chavali-Oracle
              Nowhere in that document does it state you need to export and import data, so I am confused as to how you came to such a conclusion. Have you actually tried a test upgrade by following the directions in the document ?