Errors using expdp with `flashback_time=systimestamp`

Question

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.

Answer 1

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"

• user2847643: 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.

Answer 2

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.