Re: Recovery Question.

Hi
Usually this means more recovery is needed. Check v$archived_log to see which log files cover the checkpint_change# number range according to first_change# and next_change#.

Regards
Pete

On Tue, 25 Jul 2023 at 04:09, Satalabaha Oracle <satalabaha.oracle_at_gmail.com> wrote:

> 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
>

-- 
Regards

Pete

--
http://www.freelists.org/webpage/oracle-l