Since Oracle 12c, you can recover a crash consistent snapshot. Oracle support note 604683.1 says how to do this.
We had an issue where the recovery wanted to effectively run to the end of time, and wouldn’t ever finish.
No matter how many logs were applied, it said:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/CS_SR/system/system01.dbf'
Solution
The solution is the snapshot time of the recovery clause. To demonstrate
I have a database
being recovered from a snapshot, and here are some archived redo logs from the
source database.
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 11 14:43:39 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
SQL> recover databaseuntil cancel using backup controlfile snapshot time '08-DEC-2017 14:00:00';
ORA-00279: change 15280596683192generatedat12/08/201713:42:02 needed forthread 1ORA-00289: suggestion : /CS_SR/archive/CS_SR_68_1_962144139.arc
ORA-00280: change 15280596683192for thread 1isin sequence #68Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/CS_SR/system/system01.dbf'ORA-01112: media recovery not started
We can see that since we have recovered to a time before we told the
database that the snapshot was taken, it still wanted more recovery. Lets give
it some more recovery to beyond the snapshot time.
It seems that if you don’t tell the database the snapshot time, it wants to recover for ever which was the problem we encountered.
Confusion
Some colleagues managed to create a database from a crash consistent snapshot.
After considering, we realised that these are from snapshots of a
physical standby, i.e. a database that isn’t open, therefore hasn’t
marked it’s datafiles as fuzzy.