when flashback goes bad.

If you are like me you may well have some clone apps databases as a development or training environment. If you haven't I suggest you read Steven Chan on the subject here. As well as cloning our development databases we make a habit of turning on flashback database so that in the event that a developer messes up development or needs to revert to an older state then we can do quick point in time recovery.

When I came in this morning I noticed that our recently cloned training environment was down. having ascertained that in fact the clone had been completed 'successfully' last thursday I started to investigate. Starting the database manually gave me this interesting error.

comp-dev-db-1:ora.TRAIN.TRAIN1.inst:Done removing ipc resources for sid "TRAIN1"
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:SQL*Plus: Release 10.1.0.4.0 - Production on Mon Jan 14 09:46:57 2008
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:Copyright (c) 1982, 2005, Oracle.  All rights reserved.
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:Enter user-name: Connected to an idle instance.
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:SQL> ORACLE instance started.
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:Total System Global Area 1073741824 bytes
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:Fixed Size               782648 bytes
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:Variable Size                 265555656 bytes
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:Database Buffers      805306368 bytes
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:Redo Buffers                    2097152 bytes
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:Database mounted.
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:ORA-38760: This database instance failed to turn on flashback database
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:
comp-dev-db-1:ora.TRAIN.TRAIN1.inst:

That is a new error to me so I started digging around in the alert.log. There I found some interesting ORA-600 errors as follows.

Errors in file $ORACLE_HOME/admin/$CONTEXT_NAME/bdump/train1_smon_32219.trc:
ORA-00600: internal error code, arguments: [15709], [29], [1], [], [], [], [], []
ORA-38701: Flashback database log 96 seq 12 thread 2: "/<flashback location>/flashback/o1_mf_3rfh3tkd_.flb"
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory

It seems what had happened was that after the cleanup the DBA who had carried out the cloning recalled that he hadn't deleted the old flashback logs and so he deleted all the files in our flashback location. Oracle crashed at this point and would not restart. There are similar notes on metalink regarding this same behaviour if the flashback log becomes corrupt. Lessons learned

1. Make sure that you complete all your cloning activities in the correct order before handing a clone over to users.
2. Corrupt flashback logs are a threat to database availability - something I wouldn't have expected.