0

Oracle 11.2.0.1.0 on Windows 2012 Std:

expdp '/ as sysdba' full=y directory=exp dumpfile=... logfile=... flashback_time=systimestamp

Fails with:

ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-01841: (full) year must be between -4713 and +9999, and not be 0 
ORA-01877: string is too long for internal buffer

Replacing flashback_time=systimestamp with consistent=y which AFAIK is a backwards compatible way to say the same exact thing also fails with the same errors. Removing those options completely, with no other changes, makes it work.

Still, I would like to take a consistent backup in addition to RMAN, which yes I do have configured and working. There are various workarounds suggested on the web. For example using to_timestamp but none of them seem to be working.

My questions are:

  1. Is this a known bug?
  2. Are there any settings that could cause this behavior like system locale?
  3. I am using a slightly modified version of this script and I swear just yesterday it worked fine. I don't recall changing anything, today it doesn't work. Any ideas?
  4. Is there a reliable workaround? I am thinking of using flashback_scn instead but that will require a separate query beforehand.

Edit:

Doubling down on the weirdness here. Each day:

  • 00:00-11:59 - errors as above
  • 12:00-23:59 - everything works fine

I ran the script repeatedly yesterday evening using flashback_time=systimestamp - no errors. Today 7:45am getting errors again from the same script, without modifications.

0

Just had a look at the documentation.

It says:

You can specify the time in any format that the DBMS_FLASHBACK.ENABLE_AT_TIME procedure accepts

So SYSTIMESTAMP should actually work... Though the docs suggest you need to enclose the parameter in quotation marks. Try:

FLASHBACK_TIME="SYSTIMESTAMP"
1
  • Thanks for the suggestion. I have no idea what's going on but now it works again with or without quotes. I have a suspicion that it's somehow connected to time of day. It stopped working around midnight, didn't work in the morning, now it's past noon and and it's working again. Quite frustrating :/ I'm going to test this some more. – user2847643 Sep 5 '19 at 10:47
0

Mystery solved. I've had a chance to ask an Oracle DBA who was doing some consulting work for us about this. Turns out it is most likely a bug in how Oracle handles spaces in localized timestamps. A simple workaround is to set NLS_LANG=american_america.ee8iso8859p2 before running expdp.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.