1 2 Previous Next 16 Replies Latest reply on Apr 15, 2014 12:24 PM by Tom321

    IMPDP fails with flashback_time=SYSTIMESTAMP

    Tom321

      Hi,

       

      i have a impdp job that did run for a long time without error. (OS= Solaris 11.1 DB=11.2.0.3 RAC)

      impdp user/pwd schemas=XXX directory=data_pump_dir logfile=XXX.log network_link=DBL_XXX parallel=4 cluster=n flashback_time=SYSTIMESTAMP

       

      Now i receive following error:

       

      ORA-39001: invalid argument value

      ORA-39150: bad flashback time

      ORA-08186: invalid timestamp specified

      ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1

       

      I did a run with datapump trace enabled and the logfile shows this:

       

      KUPM:08:30:27.204: FLASHBACK_TIME expression is:  SELECT TO_CHAR(SYSTIMESTAMP, 'YY-MON-DD HH:MI:SS AM') FROM SYS.DUAL

      KUPM:08:30:27.204: value_t after expression conversion: 14-APR-10 08:30:27 AM

      KUPM:08:30:27.205: Evaluated FLASHBACK_TIME is: 14-APR-10 08:30:27 AM

      KUPM:08:30:27.205: Timestamp to scn call is: SELECT TIMESTAMP_TO_SCN@DBL_ADICOMP(TO_TIMESTAMP(:1, :2)) FROM SYS.DUAL

      KUPM:08:30:27.225: ORA-39150: bad flashback time

      KUPM:08:30:27.226: ORA-08186: invalid timestamp specified

      ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1

       

      This started to happen after Daylight saving change in Germany on 30 March. Up until that point this job was running fine. I have read all related notes to nls, daylight saving, etc and checked everything i could think of, but without resolving this issue.

       

      OS is Solaris and DB environment is set to TZ=Europe/Berlin. Daylight saving was handled by solaris and this did work fine. Time is fine on all shells, db connections, listeners, ...

      NLS Settings are the same for all databases including the db accessed via the network link (runs on the same datacenter and timezone).

       

      Any ideas on what this could be and where to look next?

       

      Regards

      Thomas

        • 1. Re: IMPDP fails with flashback_time=SYSTIMESTAMP
          Meser

          could you try as like that :

           

          flashback_time=FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'Europe/Berlin')

          • 2. Re: IMPDP fails with flashback_time=SYSTIMESTAMP
            Tom321

            Hi meser,

             

            thanks for response, that comman dis working sometimes.

            at 11:08:46 -> Error

            11:09:12 -> Success

            Same ssh shell with same environment set and the same command. Any clue what could cause this random behavior?

            I have already restarted the listener this morning to rule some weird connection issue out.

             

            Regards

            Thomas

            • 3. Re: IMPDP fails with flashback_time=SYSTIMESTAMP
              Richard Harrison .

              Hmm strange - yo ugot the latest timezone definition files loaded?

               

              You could try consistent=y rather than flashbacktime=systimestamp

               

              they reintroduced it at 11.2 and i think it essentially uses flashback_scn directly rather than using the systimestamp - give that a try?

               

              Cheers,

              Rich

              1 person found this helpful
              • 4. Re: IMPDP fails with flashback_time=SYSTIMESTAMP
                Emad Al-Mousa

                execute the following query:

                 

                SQL> select * from V$NLS_PARAMETERS order by parameter;

                 

                check the format value of NLS_TIMESTAMP_FORMAT

                 

                for example,

                 

                If NLS_TIMESTAMP_FORMAT is DD-MON-RR HH.MI.SSXFF AM

                 

                 

                 

                Then use it in your IMPDP like this:

                 

                 

                FLASHBACK_TIME="TO_TIMESTAMP('11-JUL-09 4:40:00 PM', 'DD-MON-RR HH:MI:SSXFF AM')"

                 

                 

                hope this helps.

                Regards,

                1 person found this helpful
                • 5. Re: IMPDP fails with flashback_time=SYSTIMESTAMP
                  Tom321

                  Hi Rich,

                   

                  tried it and consistent=y  sadly is implemented only in expdp. The impdp command doesn't support it.

                  I have tried countless variants with formating the sysdate or timestamp and nothing works every time. I will revert back to a classic impdp from a expdp file instead of the db_link and work around this strange error.

                   

                  Thanks for Help Rich.

                   

                  Regards

                  Thomas

                  • 6. Re: IMPDP fails with flashback_time=SYSTIMESTAMP
                    Tom321

                    Hi,

                     

                    i have tried this beside many other things and it sadly doesn't work:

                     

                    "SQL> select * from V$NLS_PARAMETERS order by parameter;

                    ...

                    FLASHBACK_TIME="TO_TIMESTAMP('11-JUL-09 4:40:00 PM', 'DD-MON-RR HH:MI:SSXFF AM')"

                    "

                     

                    Regards

                    Thomas

                    • 7. Re: IMPDP fails with flashback_time=SYSTIMESTAMP
                      Richard Harrison .

                      Hi,

                      Had another idea....

                       

                      Are the machines exactly in sync with each other (in terms of time) - are you using ntp?

                       

                      If i try this on a local machine i get the same error

                       

                      expdp / flashback_time=systimestamp+200

                       

                      Export: Release 11.2.0.3.0 - Production on Fri Apr 11 08:45:02 2014

                       

                      Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

                       

                      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                      With the Partitioning option

                      ORA-39001: invalid argument value

                      ORA-39150: bad flashback time

                      ORA-08186: invalid timestamp specified

                       

                       

                      I wonder if the timestamp is evaluated in one db, but used on the other - where that time does not exist yet - it's in the future?

                       

                      Sound feasible?

                       

                      Cheers,

                      Rich

                      1 person found this helpful
                      • 8. Re: IMPDP fails with flashback_time=SYSTIMESTAMP
                        Tom321

                        Hi Rich,

                         

                        yes it is in fact the same machine. The 2 database run even from the same oracle home. Database link has been choose only because it was much faster and more resilent to faults (expdp, filesystem,etc) atleast up until April 2014...

                         

                        "I wonder if the timestamp is evaluated in one db, but used on the other - where that time does not exist yet - it's in the future?"

                        Thats a pretty good idea and it would explain why the impdp runs fine one time and fails a few minutes later with same command, same session, etc. If the systimestamp is evaluated to a non existent or future timestamp it would explain the error.

                         

                        Last errors from the trace are:

                        KUPM:14:51:09.036: Evaluated FLASHBACK_TIME is: 14-APR-10 02:51:09 PM

                        KUPM:14:51:09.037: Timestamp to scn call is: SELECT TIMESTAMP_TO_SCN@DBL_ADICOMP(TO_TIMESTAMP(:1, :2)) FROM SYS.DUAL

                        KUPM:14:51:09.048: ORA-39150: bad flashback time

                        KUPM:14:51:09.048: ORA-08186: invalid timestamp specified

                         

                        The systimestamp gets evaluated perfectly fine first and then it fails on converting this to a scn on the remote db.

                        I just don't understand why this has worked for over 1 year without any trouble and now starts to fail. Daylight saving has worked fine, i have checked everything i could find. And if something would be wrong it would affect both instances in the same matter...

                         

                        Regards

                        Thomas

                        • 9. Re: IMPDP fails with flashback_time=SYSTIMESTAMP
                          Richard Harrison .

                          Hi,

                          Did you explicitly check this in both databases?

                           

                          SELECT version FROM v$timezone_file;

                           

                          Has the TZ ever been messed around with at the OS level - the database will pick up the value when it starts and then stick with it.

                           

                          Other than that - you restarted both databases......

                           

                          Cheers,

                          Rich

                          • 10. Re: IMPDP fails with flashback_time=SYSTIMESTAMP
                            Tom321

                            Hi,

                             

                            the version 14 is the same on all instances. TZ =Europe/Berlin is in place since this system has been put in production and has never been changed since then. That applies to all other environment variables also, abolute identical for all instances.

                            The last restart of the databases was 14th March prior to the daylight saving change on 30th March in germany. With timezone Europe/Berlin a database restart is not making any changes, as the database is relying on the os to change it.

                             

                            Regards

                            Thomas

                            • 11. Re: IMPDP fails with flashback_time=SYSTIMESTAMP
                              Richard Harrison .

                              Hmm

                               

                              to 'fix' it can you change your script to explicitly fetch the current scn (using a dbms_flashback fucntion call) and then pass that to impdp - bit of a pain i know but should work.

                               

                              STill don;t know why you have the issue

                               

                              Is the clock on the machine out and ntp is drifting it back - it might just be moving it just as the impdp is initialising - sounds very unlikely but might explain the behaviour?

                               

                              Cheers,

                              Rich

                              • 12. Re: IMPDP fails with flashback_time=SYSTIMESTAMP
                                817202

                                Hi,

                                 

                                The exp utility used the CONSISTENT=Y parameter to indicate the export should be consistent to a point in time. By default the expdp utility exports are only consistent on a per table basis. If you want all tables in the export to be consistent to the same point in time, you need to use the FLASHBACK_SCN or FLASHBACK_TIME parameter.


                                Error:- ORA-08186: invalid timestamp specified during

                                 

                                Reason: Because NLS_DATE_FORMAT doesn't set.


                                Solution: set NLS_DATE_FORMAT

                                 

                                Syntax:- ALTER SESSION SET nls_date_format = 'dd-mm-yyyy hh24:mi:ss';

                                retest.

                                 

                                 

                                Correct me if i done some mistake to sort out the problem.

                                 

                                Thanks

                                • 13. Re: IMPDP fails with flashback_time=SYSTIMESTAMP
                                  Richard Harrison .

                                  Hi,

                                  consistent=y came back in 11.2 with expdp!

                                   

                                  check this output:

                                   

                                  expdp / consistent=y

                                   

                                  Export: Release 11.2.0.3.0 - Production on Fri Apr 11 19:27:11 2014

                                   

                                  Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

                                   

                                  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                                  With the Partitioning option

                                  Legacy Mode Active due to the following parameters:

                                  Legacy Mode Parameter: "consistent=TRUE" Location: Command Line, Replaced with: "flashback_time=TO_TIMESTAMP('2014-04-11 19:27:11', 'YYYY-MM-DD HH24:MI:SS')"

                                   

                                  Cheers,

                                  Rich

                                  • 14. Re: IMPDP fails with flashback_time=SYSTIMESTAMP
                                    Tom321

                                    Hi,

                                     

                                    the ntp demon is set to

                                    slewalways yes

                                    disable pll

                                     

                                    there should never be any jump in time on the system.

                                    Setting any NLS value in the environment doesn't change the issue. It is first evaluated correctly in the impdp run and after being evaluated the error occurs, regardless what nls_date_format or nls_timestamp_format is set.

                                     

                                    Regards

                                    Thomas

                                    1 2 Previous Next