Hi All,
I have an Oracle recovery related question..
Environment: Oracle 12.1 running on RHEL 7.
We performed incomplete recovery for a set of datafiles (646 datafiles) as
we lost the mount point that had those datafiles. After the recovery, below
is the status of checkpoint for both datafile header and checkpoint
information in controlfile. If we notice the checkpoint_change# for all the
datafiles are in consistent state and checkpoint_change_change# of datafile
header matches with the controlfile too. But Oracle was not allowing to
open the database [1] using resetlogs as the SYSTEM datafile requires more
recovery.
Q1) Why would Oracle still report FUZZINESS for the rest of the 2635 odd
datafiles?
Q2) What other conditions should be met before we can make sure that the
database can be opened?
[1]
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error
below
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/xxxx/xxxxx/xxxxx_system01.dbf'
Details:
============
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
Session altered.
SQL> select checkpoint_change#,checkpoint_time,fuzzy,count(1) from
v$datafile_header group by checkpoint_change#,fuzzy,checkpoint_time;
CHECKPOINT_CHANGE# CHECKPOINT_TIME FUZZY COUNT(1)
-------------------- --------------------------- ---------
--------------------
48336396202 28-JAN-23 11:02:52 NO 2
78233391650 23-JUL-23 08:57:11 NO 646
78233391650 23-JUL-23 08:57:11 YES 2635
SQL> select checkpoint_change#,checkpoint_time,count(1) from v$datafile
group by checkpoint_change#,checkpoint_time;
CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(1)
-------------------- --------------------------- --------------------
48336396202 28-JAN-23 11:02:52 2
78233391650 23-JUL-23 08:57:11 3281
--
Thanks,
Satalabaha
