What happen when someone will delete Oracle flashback logs ? You probably don't notice it until you will try to flashback database or bounce instance.
There is no hope for flashback database without flashback files but there is still way to start your database again without recovery or data loss.
Here is a scenario:
[oracle@dev-6 alert]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 1 09:34:18 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
SQL> select status from v$instance;
STATUS
------------------------------------------------
MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
OK open database doesn't work. So what happen when I disable a flashback logging
SQL> alter database flashback off;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
Well still doesn't work - but what is a flashback state ?
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------------------------------------------
RESTORE POINT ONLY
SQL> select * from v$restore_point;
select * from v$restore_point
*
ERROR at line 1:
ORA-38701: Flashback database log 33 seq 476 thread 1:
"/u01/app/oracle/fast_recovery_area/DEV/flashback/o1_mf_9nq1wbon_.flb"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
OK at least there is some information about root cause - it is looking for missing flashback files.
Information about flashback database is keep inside control file so let's try to recreate control file using trace file
SQL> alter database backup controlfile to trace as '/tmp/control.ctl';
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1336176640 bytes
Fixed Size 2253024 bytes
Variable Size 822087456 bytes
Database Buffers 503316480 bytes
Redo Buffers 8519680 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
Backup of control file has been created in trace file and edited as follow
[oracle@dev-6 tmp]$ vi control.ctl
CREATE CONTROLFILE REUSE DATABASE "DEV" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/dev/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/dev/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/dev/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/dev/system01.dbf',
'/u01/app/oracle/oradata/dev/sysaux01.dbf',
'/u01/app/oracle/oradata/dev/undotbs01.dbf',
'/u01/app/oracle/oradata/dev/users01.dbf',
'/u01/app/oracle/oradata/dev/USER.dbf',
'/u01/app/oracle/oradata/dev/DATA.dbf',
'/u01/app/oracle/oradata/dev/DATA_INDEX.dbf',
'/u01/app/oracle/oradata/dev/REFERENCE.dbf',
'/u01/app/oracle/oradata/dev/REFERENCE_INDEX.dbf',
'/u01/app/oracle/oradata/dev/ARCHIVE.dbf',
'/u01/app/oracle/oradata/dev/apex01.dbf'
CHARACTER SET AL32UTF8
;
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/nim_backup/backup/oracle/db/dev/%F''');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 7 DAYS');
RECOVER DATABASE;
-- Block change tracking was enabled, so re-enable it now.
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u01/app/oracle/oradata/dev/bct_01.log' REUSE;
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/dev/temp01.dbf' REUSE;
Let's try to create new control files and open database
[oracle@dev-6 tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 1 09:43:45 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
SQL> @control.ctl
Control file created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Media recovery complete.
Database altered.
System altered.
Database altered.
Tablespace altered.
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------------------------------------------
YES
SQL>
Database has been started and opened - all done.
I blogged about that cause I have to solve this problem several time when due to space restriction flashback logs
has been deleted by other DBA manually instead of disabling and enabling flashback on database.
Just keep in mind if you need to release space in FRA don't delete flashback logs manually but turn off flashback on the database
regards,
Marcin
