How to resolve ORA-01547 warning

The ORA-01547 before database open indicates that Oracle sees the datafiles as inconsistent. and this is resolved by applying more recovery, to make the datafiles consistent.

1. Check the current status of the datafiles:

   set numwidth 30;
   set pagesize 50000;
   alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
   select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;

     The goal is to have the above query return 1 row and fuzzy column value as NO. And if checkpoint_change# in query returns 0, this indicates that Oracle cannot read the file header. This is possibly because the location and name of the datafile within the controlfile is not the one on disk.

2. Check the archivelog sequence numbers needed to recover the files:

   select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;

     This query will show the smallest and largest sequence needed by the datafiles.
   To make the datafiles consistent, you must apply all archivelog files within the above range of sequence numbers. This can be one or many archivelog files.  And the above query will not be valid in a RAC environment. For RAC environment execute:

   select thread#, first_change#, next_change# from v$archived_log where <smallest/largest checkpoint_change#> between first_change# and next_change#;

3. Once further recovery is applied, confirm the datafiles are consistent with query in #1.
4. Once all datafiles are consistent and fuzzy=NO, the database can be opened and the ORA-01547 should no longer be returned.