Recovery Question.
Date: Tue, 25 Jul 2023 08:37:22 +0530
Message-ID: <CAKi_91mxjnCvbLDkYTeF3MVoQh5wHZe+cs13DrjWwW-WEYDyUw_at_mail.gmail.com>
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 -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 25 2023 - 05:07:22 CEST