Mir Sayeed Hassan – Oracle Blog

One of test database is set with flashback enabled as on, But there are some flashback files are deleted so we are not able to open the database in open mode & got the below ORA-38760 error.

ORA-38760: This database instance failed to turn on flashback database

Check the status of the database

SQL> select status from V$instance;

STATUS
-------
MOUNTED

Disable the flashback & try to enable again

SQL> alter database flashback off;
Database altered.

Startup database in open mode

SQL> alter database open;

alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

Enable the flashback

SQL> alter database flashback on;

alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38714: Instance recovery required.

Check the alert log file 

Errors in file /u01/app/oracle/diag/rdbms/mirtdb/mirtdb/trace/mirtdb_rvwr_7982.trc:

ORA-38701: Flashback database log 149 seq 149 thread 1: "/u01/app/oracle/fast_recovery_area/MIRTDB/flashback/o1_mf_h9dtotrk_.flb"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount
Fri Apr 17 05:48:00 2020
alter database datafile '/u01/app/oracle/TESTDB/MIRTDB/datafile/o1_mf_fda_tbs1_h6v4jxnw_.dbf' resize 1g
ORA-1109 signalled during: alter database datafile '/u01/app/oracle/TESTDB/MIRTDB/datafile/o1_mf_fda_tbs1_h6v4jxnw_.dbf' resize 1g...

alter database open

Errors in file /u01/app/oracle/diag/rdbms/mirtdb/mirtdb/trace/mirtdb_ora_7992.trc:
ORA-38760: This database instance failed to turn on flashback database
ORA-38760 signalled during: alter database open...
Fri Apr 17 05:48:20 2020

alter database flashback on

Errors in file /u01/app/oracle/diag/rdbms/mirtdb/mirtdb/trace/mirtdb_ora_7992.trc:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38714: Instance recovery required.

Therefore  checked any existing restore point is created, if yes then drop & open the database

SQL> select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point; 

NAME           SCN        GUA    DATABASE_INCARNATION# 
———————————————————————————————--------------------------
BEFORE_UPGRADE 6877750    YES                2

Drop the above restore point

SQL> drop restore point BEFORE_UPGRADE;
restore point dropped.

Now open the database

SQL> alter database open; 
Database altered.

Check the status of the database

SQL> select status from v$instance;

STATUS
------
OPEN

=====Hence tested & Verified in our test env=====