If you cannot recover the data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Parnassusdata Software Database Recovery Team

Service Hotline:  +86 13764045638 E-mail: service@parnassusdata.com

 

 

 

Applies to:                                                                                                   

 

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later Information in this document applies to any platform.

 

Purpose                                                                                          

The goal of this article is to assist DBAs who encounter the following errors at Database open. ORA-1194 "file %s needs more recovery to be consistent"

ORA-1547 "warning: RECOVER succeeded but OPEN RESETLOGS would get error below"

ORA-1110 "data file %s: '%s'"

 

 

Scope                                   

 

This article is meant for database administrators and backup and recovery specialists tasked with the restore and recovery of a database

 

Details                                  

 

Assuming All Datafiles was either successfully restored, and/or recovery was done with existing database datafiles. Then open the database failed with errors like:

 

 

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: '/u01/app/oracle/oradata/V1123/system01.dbf'

 

 

 

Scenario 1: Current Controlfile  is  available

 

This shows the Information whats needed for recovery, usually you will not see the errors as above at open database, but nevertheless giving this example.

 

1) Ensure Instance is Mounted and ALL Datafiles ONLINE

 

If the CURRENT CONTROLFILE is used then you can run recover database, and it will

apply all archivelogs, and the online 'current' redolog if available, and finally you can open database Example:

SQL> select name, controlfile_type from v$database ;

 

NAME     CONTROL

--------- ------- V1123       CURRENT

 

SQL> recover automatic database ;

..

Media recovery complete SQL> alter database open

 

Scenario 2: Backup  Controlfile  is used for  recovery

 

Please see steps below showing you an example getting the errors like ORA-1194, ORA-1547, ORA-1110, and how to verify status of Controlfile, Datafiles, Logfiles, and finally recover and open database.

 

 

SQL> select name, controlfile_type from v$database ;

 

NAME     CONTROL

--------- -------

V1123    BACKUP      -- controlfile_type is "Backup"  Controlfile

 

SQL> select status,

2        resetlogs_change#,

3        resetlogs_time,

4        checkpoint_change#,

5        to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,

6        count(*)

7    from v$datafile_header

8   group by status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time

9   order by status, checkpoint_change#, checkpoint_time ;

 

STATUS  RESETLOGS_CHANGE# RESETLOGS_TIME    CHECKPOINT_CHANGE#  CHECKPOINT_TIME    COUNT(*)

-------  -----------------  --------------------  ------------------  --------------------  ----------

ONLINE           995548 15-FEB-2012:17:17:20         2446300 13-FEB-2013 15:09:44      1  -- Datafile(s) are at different checkpoint_change# (scn), so not consistent

ONLINE           995548 15-FEB-2012:17:17:20         2472049 13-FEB-2013 16:02:22          6

 

SQL> SQL>

SQL> -- Check for datafile status, and fuzziness

SQL> select STATUS, ERROR, FUZZY,  count(*)         from v$datafile_header group by STATUS, ERROR, FUZZY   ;

 

STATUS  ERROR                                                     FUZ  COUNT(*)

------- ----------------------------------------------------------------- --- ---------- ONLINE                                                          YES         7

 

SQL> SQL>

SQL> -- Check for MIN, and MAX SCN in Datafiles

SQL> select min(CHECKPOINT_CHANGE#), max(CHECKPOINT_CHANGE#) from v$datafile_header  ;

 

MIN(CHECKPOINT_CHANGE#)  MAX(CHECKPOINT_CHANGE#)

----------------------- ----------------------- 2446300               2472049

 

SQL>

SQL> select    substr(L.GROUP#,1,6)  GROUP#

2        ,substr(L.THREAD#,1,7)      THREAD#

3        ,substr(L.SEQUENCE#,1,10)    SEQUENCE#

4        ,substr(L.MEMBERS,1,7)       MEMBERS

5        ,substr(L.ARCHIVED,1,8)      ARCHIVED

6        ,substr(L.STATUS,1,10)        STATUS

7        ,substr(L.FIRST_CHANGE#,1,16)   FIRST_CHANGE#

8        ,substr(LF.member,1,60)      REDO_LOGFILE

9   from GV$LOG L, GV$LOGFILE LF

10  where L.GROUP# = LF.GROUP# ;

 

GROUP# THREAD# SEQUENCE#  MEMBERS ARC STATUS      FIRST_CHANGE#   REDO_LOGFILE

------  -------  ----------  -------  ---  ----------  ----------------  ------------------------------------------------------------

1     1      454       1      NO  CURRENT    2471963   /u01/app/oracle/oradata/V1123/redo01.log    <-- This is CURRENT log containing  most recent redo, and is available

3	1	453	1	YES INACTIVE	2471714	/u01/app/oracle/oradata/V1123/redo03.log
2	1	452	1	YES INACTIVE	2451698	/u01/app/oracle/oradata/V1123/redo02.log

SQL>

 

 

-- Use MIN(CHECKPOINT_CHANGE#) 2446300 as found before, then use it with this query to find the

-- first SEQ# 'number' and archivelog file needed for recover to start with.

-- All SEQ# up to the online Current Redolog SEQ# must be available without any gap for successful recovery

 

-- MIN(CHECKPOINT_CHANGE#)  2446300

 

SQL> select thread#, sequence#, substr(name,1,80) from v$Archived_log where 2446300 between first_change# and next_change#;

 

THREAD#  SEQUENCE# SUBSTR(NAME,1,80)

----------    ----------    --------------------------------------------------------------------------------

1       449 /u01/app/oracle/oradata/V1123/arch1/arch_1_449_775329440.arc

1       449  /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_449_8kq7oc6y_.arc

1       450 /u01/app/oracle/oradata/V1123/arch1/arch_1_450_775329440.arc

1       450  /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_450_8kqbn929_.arc

 

SQL>

 

SQL> select * from v$recover_file ;  -- Checking for Datafile(s) which needs recovery

 

FILE# ONLINE  ONLINE_ ERROR                                                     CHANGE# TIME

----------   -------   -------   -----------------------------------------------------------------   ---------- -------------------- 6 ONLINE  ONLINE                                                 2446300 13-FEB-2013:15:09:44

 

SQL>

 

 

 

If you use a "BACKUP CONTROLFILE", or previously used a CANCEL based recover command then we need to recover, and finally 'manual' apply the online current redolog.

 

Example:

 

SQL> select name, controlfile_type from v$database ;

 

NAME     CONTROL

--------- ------- V1123         BACKUP

 

SQL>

QL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;

 

ORA-00279: change 2446300 generated at 02/13/2013 15:09:44 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_450_8kqbn929_.arc ORA-00280: change 2446300 for thread 1 is in sequence #450

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto

ORA-00279: change 2451694 generated at 02/13/2013 16:00:25 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_451_8kqbnbmh_.arc ORA-00280: change 2451694 for thread 1 is in sequence #451

ORA-00278: log file '/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_450_8kqbn929_.arc' no longer needed for this recovery

...

< all required logs applied >

...

ORA-00279: change 2471963 generated at 02/13/2013 16:02:19 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_454_%u_.arc ORA-00280: change 2471963 for thread 1 is in sequence #454

ORA-00278: log file '/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_453_8kqbqvrk_.arc' no longer needed for this recovery <-- All Redo, up to and including SEQ# 453 is applied

 

 

ORA-00308: cannot open archived log '/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_454_%u_.arc' <<<-- "SEQ# 454" requested, which is in ONLINE REDOLOG as seen before

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory Additional information: 3

 

 

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:  '/u01/app/oracle/oradata/V1123/system01.dbf'

 

 

SQL>

SQL> select * from v$recover_file ;

 

FILE# ONLINE  ONLINE_ ERROR                                                     CHANGE# TIME

----------   -------   -------   -----------------------------------------------------------------   ---------- -------------------- 6 ONLINE  ONLINE                                                 2471963 13-FEB-2013:16:02:19

 

SQL>

 

SQL> alter database open resetlogs ; alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/V1123/system01.dbf' SQL>

 

The following query will show you the SCN to which we must at least recover to, to get all datafiles consistent.

 

SQL> select  min(FHSCN) "LOW FILEHDR SCN"

, max(FHSCN) "MAX FILEHDR SCN"

, max(FHAFS) "Min PITR ABSSCN"

from X$KCVFH ;

 

LOW FILEHDR SCN  MAX FILEHDR SCN  Min PITR ABSSCN

---------------- ---------------- ---------------- 2446300 2472049       0

 

 

-- Example output explained:

--

-- "LOW FILEHDR SCN"  - this is the SCN at which recovery process  starts

-- "MAX FILEHDR SCN"  - this is the SCN we must recover to to get all datafiles consistent

--

--   IF     "Min PITR ABSSCN"  != 0 AND  >  "MAX FILEHDR SCN"

--   THEN  "Min PITR ABSSCN"  is the SCN we must recover to to get all datafiles consistent

 

ABSSCN = Absolute SCN

Comment:

 

In the above output/sample we see, redo (archivelogs) was applied and datafile 6 was rolled forward but still needs more recovery and redo to be applied.

Database Recovery did not complete, because the online 'current' redolog is not applied automatically with a 'BACKUP' controlfile.

As we use a Backup Controlfile we must 'manually' apply the online 'current' redolog '/u01/app/oracle/oradata/V1123/redo01.log' having SEQ#  454 ( SCN 2472049) !

 

Additional Note:

 

As this is recover with a Backup Controlfile, or controlfile create from  Tracefile (sql> alter database backup to trace;) ,

query of v$log/v$logfile will possibly not give the correct information, about which logfile contains which sequence number (seq#).

 

# Options to find the Online log to be used

 

a: Check the Alert.log file for the last sequences used with 'Online Redolog files'

 

b: If Alert.log is lost you may simply try all online redolog files, if the wrong logfile is chosen nothing will be applied, but you will see in in the output message which sequence is in that online redolog file.

Then simply try the next online redolog file until you get 'media recovery complete' message.

 

c: You may also dump the file log file headers for Online redolog file(s) Example:

--------

sql> alter system dump logfile '/u01/app/oracle/oradata/V1123/redo01.log' scn min 1 scn max 1 ;

 

-- This will write a tracefile with the header dump to your 'trace' (11g) [ or udump (<=10g) ] directory

-- Check the tracefile for similar entry like...

~~~

..

descrip:"Thread 0001, Seq# 0000000454 ...

..

Low  scn: 0x.....

Next scn: 0x.....

..

~~~

 

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;

.

ORA-00279: change 2471963 generated at 02/13/2013 16:02:19 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_454_%u_.arc ORA-00280: change 2471963 for thread 1 is in sequence #454

 

Note:

 

If after applying all archive logs and online redo logs the database does not open please provide the following script output to Oracle support to assist with the recovery. ( Please upload spooled file: recovery_info.txt )

 

SQL> set pagesize 20000 set linesize 180

set pause off

set serveroutput on set feedback on

set echo on

set numformat 999999999999999

 

Spool recovery_info.txt

select substr(name, 1, 50), status from v$datafile;

select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header; select GROUP#,substr(member,1,60) from v$logfile;

select * from v$recover_file;

select distinct status from v$backup;

select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, FHAFS ABSSCN , fhrba_Seq SEQUENCE from x$kcvfh; select distinct (fuzzy) from v$datafile_header;

spool off exit;