Yet another developer journey ...

Yet another developer journey ...

ORA-39150 & ORA-08186 using expdp in 19.12.0.0.0

Photo by David Pupaza on Unsplash

ORA-39150 & ORA-08186 using expdp in 19.12.0.0.0

Dominik Schischma's photo
Dominik Schischma
·Jun 2, 2022·

2 min read

Today I got an email from one of our colleagues that the daily datapump which we are executing with the parameter 'flashback_time=systimestamp' is suddenly failing.

The log files only show the following and alert log is empty:

Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
ORA-39150: bad flashback time
ORA-08186: invalid timestamp specified

Metalink from Oracle just telling us that the timestamp format is wrong, and we do not need to worry about timestamp format if we use systimestamp.

After digging around a little bit, I noticed that the times in log files suddenly 'jumped' without any notice about a time drift, neither from windows nor oracle (and oracle is very verbose in logging this).

Restarting the whole database got rid of the error. The reason for all of this seems to be that the customer changed the region format of the server without restarting the database. However, I have not tried reproducing this in a lab, but it seems to be the only change which was done on the server in the last days.

As a side note, if you are questioning yourself why we are running a daily datapump export: Naturally we are running an additional recovery manager backup and we always advise the customers to use some solid tested robust fancy backup software with a dedicated database agent for handling the database backup correctly with monitoring and so on and on... BUT if you have got a big application database and someone started a wrongly configured cleanup operation, naturally without any verification in the dev environment, or 'accidently dropping' the wrong table / deleting single rows, you do not want to role backup a whole database to a specific point in time, sometimes not even a specific table. Depending on the type of data (low change rate, e.g. master data) you can recover them sometimes much faster from dump instead of recovering from backup. Another point... we also experienced people who 'lost' their rman backup or some files and you end up with an old backup or need to contact oracle support for an inconsistent recovery because of the missing files. Then a datapump files is also coming in handy...

 
Share this