ERRORS ORA-39150 AND ORA-08186 DURING DATAPUMP IMPORT WHEN USING FLASHBACK_TIME OPTION

SYMPTOMS
When attempting to perform a datapump import with the FLASHBACK_TIME option, the following errors occur

ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-08186: invalid timestamp specified

For instance, the datapump command that was issued

oracle@s001dev ~: impdp system/pwd NETWORK_LINK=MERIDB2_TOMSKPROD FLASHBACK_TIME=\"TO_TIMESTAMP\(TO_CHAR\(SYSDATE,\'YYYY-MM-DD HH24:MI:SS\'\),\'YYYY-MM-DD HH24:MI:SS\'\)\" schemas=TOMSKPROD directory=DROPZONE logfile=TOMSKPROD.import.log 

Import: Release 19.0.0.0.0 - Production on Mon Feb 1 17:14:29 2021
Version 19.6.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-08186: invalid timestamp specified

or even if it was issued as recommended by the Database Documentation

Alternatively, you can enter the following parameter setting.
This setting initiate a consistent export that is based on current system time:

FLASHBACK_TIME=systimestamp

oracle@s001dev ~: impdp system/pwd NETWORK_LINK=MERIDB2_TOMSKPROD FLASHBACK_TIME=SYSTIMESTAMP schemas=TOMSKPROD directory=DROPZONE logfile=TOMSKPROD.import.log 

Import: Release 19.0.0.0.0 - Production on Mon Feb 1 17:15:06 2021
Version 19.6.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-08186: invalid timestamp specified

CAUSE
The timestamp format passed to the FLASHBACK_TIME option of the datapump session does not match that in the NLS_TIMESTAMP_FORMAT parameter.

## Issue against the target database as database link is used
COL PARAMETER FOR A25 
COL VALUE FOR A30
SELECT PARAMETER,VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_TIMESTAMP_FORMAT';

PARAMETER                 VALUE
------------------------- ------------------------------
NLS_TIMESTAMP_FORMAT      DD-MON-RR HH.MI.SSXFF AM

SOLUTION
Modify the FLASHBACK_TIME timestamp format according to NLS_TIMESTAMP_FORMAT parameter format

For instance, instead of YYYY-MM-DD HH24:MI:SS

FLASHBACK_TIME=\"TO_TIMESTAMP\(TO_CHAR\(SYSDATE,\'YYYY-MM-DD HH24:MI:SS\'\),\'YYYY-MM-DD HH24:MI:SS\'\)\"

I should use DD-MON-RR HH.MI.SS

FLASHBACK_TIME=\"TO_TIMESTAMP\(TO_CHAR\(SYSDATE,\'DD-MON-RR HH.MI.SS\'\),\'DD-MON-RR HH.MI.SS\'\)\"

With the corrected timestamp format for the FLASHBACK_TIME parameter everything works

oracle@s001dev ~: impdp system/pwd NETWORK_LINK=MERIDB2_TOMSKPROD FLASHBACK_TIME=\"TO_TIMESTAMP\(TO_CHAR\(SYSDATE,\'DD-MON-RR HH.MI.SS\'\),\'DD-MON-RR HH.MI.SS\'\)\" schemas=TOMSKPROD directory=DROPZONE logfile=TOMSKPROD.import.log 


Import: Release 19.0.0.0.0 - Production on Mon Feb 1 17:56:46 2021
Version 19.6.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** schemas=TOMSKPROD directory=DROPZONE logfile=TOMSKPROD.import.log NETWORK_LINK=MERIDB2_TOMSKPROD FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(SYSDATE,'DD-MON-RR HH.MI.SS'),'DD-MON-RR HH.MI.SS')"

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 42.97 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE

...

REFERENCES
Errors ORA-39150 ORA-8186 Invalid Timestamp Specified Received During DataPump Export (Doc ID 464132.1)