Upgrade to 12c: ORA-38753: Cannot flashback if there is tablespace with FLASHBACK=NO !
Yesterday at customers site I had to upgrade one 11g database to 12c and stumbled upon a tricky issue.
As I already upgraded a many dozens of databases, I think (or I thought at least), that I am pretty familiar with the whole process: planning, preparation, check list, pre-upgrade tasks and so on… But, yesterday I failed – almost epic, if I hadn’t backup taken before the upgrade.
So, what happened?
During the upgrade, we choosed the restore point and flashback as our fallback option. After a 15-20 minutes some error popped up and customer decided to not take a risk, so we executed the “Restore Database” step at the end. Shortly, we got the message, that the database cannot be restored, or better to say “flashbacked”, throwing this error:
ORA-38753: Cannot flashback data file 54; no flashback log data. ORA-01110: data file 54: '+DATA/REPDWH/datafile/aimsl_i1_ts.302.837106381' ORA-38753: Cannot flashback data file 53; no flashback log data. ORA-01110: data file 53: '+DATA/REPDWH/datafile/aimsl_d1_ts.293.837106361'
Wtf!? I was really surprised, because the flashback was activated! How could that happen!?
First, here is the description of this error:
Error: ORA-38753 Cannot flashback data file %s; no flashback log data. --------------------------------------------------------------------------- Cause: An attempt to perform a FLASHBACK DATABASE failed because the file does not have enough flashback log data to cover the time to flash back. Either the file did not have flashback generation enabled for it, or had flashback generation turned off for it some time during the time span of the flashback. Action: The file cannot be flashed back. The file must be taken offline or the tablespace dropped before continuing with the FLASHBACK DATABASE command.
It seems, someone turned Flashback off for these tablespaces to avoid unnecessary flashback logs generation. However, FLASHBACK DATABASE can’t work, if flashback is off for any of the tablespaces. I verified it:
SQL> select a.file#, a.name file_name, b.ts#, b.name ts_name, b.flashback_on from v$datafile a, v$tablespace b where a.ts#=b.ts# and b.name like 'AIMSL%';
FILE# FILE_NAME TS# TS_NAME FLASHBACK_ON
---------- ------------------------------------------------- --- ----------- -------------
53 +DATA/REPDWH/datafile/aimsl_d1_ts.293.837106361 52 AIMSL_D1_TS NO
54 +DATA/REPDWH/datafile/aimsl_i1_ts.302.837106381 53 AIMSL_I1_TS NO
I found this MOS Note: “Workaround for Flashback Database fails with ORA-38753 ORA-01110 (Doc ID 982104.1)“, but unfortunately I was not able to recover the database. As suggested in the MOS Note, I took those 2 datafiles offline and did RECOVER DATABASE with the AUTO option, but when I tried to open the database I got following error messages:
Errors in file /opt/oracle/diag/rdbms/repdwh/REPDWH/trace/REPDWH_ora_28549.trc: ORA-00704: bootstrap process failure ORA-39700: database must be opened with UPGRADE option Error 704 happened during db open, shutting down database USER (ospid: 28549): terminating the instance due to error 704
Because of the time constraints, I had no time to try it over and over, or to search for another solution or workaround, so I restored the database and turned flashback on also for these 2 tablespaces.
So, I learned something new and from now on, I’ll check every time before the upgrade, if the flashback has been explicitly turned off for some tablespace. It would be also great, if the DBUA (Database Upgrade Assistant) or preupgrade script had that check built in.
Today I found another great MOS Note: “How to flashback to a Guaranteed Restore Point when some tablespaces have flashback off & RESETLOGS was done: Flashback Database fails with ORA-38753 ORA-01110 (Doc ID 1588027.1)“. I wish I had it yesterday, because it seems, I was only a couple of steps (commands) away to successfully recover the database…