Skip to Content
avatar image
Former Member

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

Hi folks, I did a very simple brbackup and brrestore to a different machine , the restore part works fine, but the recovery is giving me trouble.

Recovery is going from CER to SME in vmware environment.

Here is what I did:

ran a brbackup from the original machine

./brbackup -u / -d util_file_online -t online_cons -p $ORACLE_HOME/dbs/initCER.sap -r $ORACLE_HOME/dbs/initCER.utl -m all -c force

The backup was successful.

immediately I switched over to the other box ( target host )

ran brrestore from the the target host

[orasme@centrock sapbackup]$ pwd

/mnt/oracle/SME/sapbackup

[orasme@centrock sapbackup]$ scp root@centhobby.sme.com:/mnt/oracle/CER/sapbackup/* /mnt/oracle/SME/sapbackup

scp root@centhobby.sme.com:/mnt/oracle/CER/saparch/* /mnt/oracle/SME/saparch

I even copy over the online redo logs:

[orasme@centrock origlogA]$ scp root@centhobby.sme.com:/mnt/oracle/CER/origlogA/* /mnt/oracle/SME/origlogA ;

root@centhobby.sme.com's password:

log1_m1.dbf 100% 10MB 10.0MB/s 00:00 log3_m1.dbf 100% 10MB 10.0MB/s 00:01 [orasme@centrock origlogA]$ scp root@centhobby.sme.com:/mnt/oracle/CER/origlogB/* /mnt/oracle/SME/origlogB ; root@centhobby.sme.com's password: log2_m1.dbf 100% 10MB 10.0MB/s 00:01 log4_m1.dbf 100% 10MB 10.0MB/s 00:00

[orasme@centrock origlogA]$ scp root@centhobby.sme.com:/mnt/oracle/CER/mirrlogA/* /mnt/oracle/SME/mirrlogA ; root@centhobby.sme.com's password: log1_m2.dbf 100% 10MB 10.0MB/s 00:00 log3_m2.dbf 100% 10MB 10.0MB/s 00:00

[orasme@centrock origlogA]$ scp root@centhobby.sme.com:/mnt/oracle/CER/mirrlogB/* /mnt/oracle/SME/mirrlogB ; root@centhobby.sme.com's password: log2_m2.dbf 100% 10MB 10.0MB/s 00:00 log4_m2.dbf 100% 10MB 10.0MB/s 00:00

[orasme@centrock origlogA]$

The brrestore was successful !!!

./brrestore -u / -d util_file_online -b bewfbrnx.anf -m full -p $ORACLE_HOME/dbs/initSME.sap -r $ORACLE_HOME/dbs/initSME.utl -c force

It brought back the control files and all datafiles

However the recover failed with the following error:

SQL> startup mount ; ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 150347776 bytes Fixed Size 2211128 bytes Variable Size 125829832 bytes Database Buffers 16777216 bytes Redo Buffers 5529600 bytes Database mounted.

SQL> archive log list ; Database log mode Archive Mode Automatic archival Enabled Archive destination /mnt/oracle/SME/saparch Oldest online log sequence 765 Next log sequence to archive 768 Current log sequence 768

SQL> recover database until cancel using backup controlfile ; ORA-00279: change 3735676 generated at 07/28/2017 14:14:56 needed for thread 1 ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_768_852647695.dbf ORA-00280: change 3735676 for thread 1 is in sequence #768

( note, I don't have suggestion : /mnt/oracle/SME/saparch/1_768_852647695.dbf)

( so I specify CANCEL )

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

CANCEL

ORA-10879: error signaled in parallel recovery slave 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: '/mnt/oracle/CER/sapdata1/system_1/system.data1'

SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE ;

ORA-00279: change 3735676 generated at 07/28/2017 14:14:56 needed for thread 1 ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_768_852647695.dbf

ORA-00280: change 3735676 for thread 1 is in sequence #768 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /mnt/oracle/SME/origlogA/log1_m1.dbf

ORA-00326: log begins at change 3735790, need earlier change 3735676

ORA-00334: archived log: '/mnt/oracle/SME/origlogA/log1_m1.dbf'

ORA-10879: error signaled in parallel recovery slave

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: '/mnt/oracle/CER/sapdata1/system_1/system.data1'

How to resolve this error ?

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Best Answer
    Jul 31, 2017 at 12:36 AM

    Hi Yung,

    You need to manually recover CURRENT online redo.
    Have you tried other redo logs? The last sequence is 768, so try to dump the online redo to find sequence 768:

    sql> alter system dump logfile 'redolog full patch' scn min 1 scn max 1 ;

    For the details, please check metalink Doc ID 1528788.1.

    Or easy way, try to specify all the online redo.


    Best regards,
    James

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 31, 2017 at 08:22 PM

    Thanks James, you are right !!!

    It was missing online redo log files.

    I have to copy over the online redo log files. After copy over, did another recovery

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

    /mnt/oracle/SME/origlogA/log1_m1.dbf Log applied.

    Media recovery complete.

    SQL>

    SQL> alter database open resetlogs ; alter database open resetlogs

    * ERROR at line 1:

    ORA-00603: ORACLE server session terminated by fatal error

    ORA-00600: internal error code, arguments: [2662], [0], [5672156], [0], [5692113], [12583040], [], [], [], [], [], []

    ORA-00600: internal error code, arguments: [2662], [0], [5672155], [0], [5692113], [12583040], [], [], [], [], [], []

    ORA-01092: ORACLE instance terminated. Disconnection forced

    ORA-00600: internal error code, arguments: [2662], [0], [5672153], [0], [5692113], [12583040], [], [], [], [], [], []

    Process ID: 20649 Session ID: 81 Serial number: 7

    I followed metalink doc ID 275902.1

    SQL> set linesize 120

    SQL> set pagesize 5000

    SQL> set wrap off

    SQL> select file#,name from v$tempfile ;

    FILE# NAME

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

    1 /mnt/oracle/CER/sapdata1/temp_1/temp.data1

    SQL> alter database tempfile '/mnt/oracle/CER/sapdata1/temp_1/temp.data1' drop ; Database altered.

    SQL> alter database open ;

    Database altered.

    SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/mnt/oracle/SME/sapdata1/temp_1/temp.data1' SIZE 100M; Tablespace created.

    Thanks James !!!

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 31, 2017 at 05:08 PM

    Hi James and folks,

    I have two questions:

    question#1) where do I ran the above command ? from the source machine or target machine ? ( I presume its from the source ?)

    question#2) today I realized that over the weekend, the source machine has generated additional archive redo logs including 768.

    1. I manually copied over tons of other archived redo logs generated over the weekend.

    2. ran database recovery again

    SQL> startup mount ; ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 150347776 bytes Fixed Size 2211128 bytes Variable Size 125829832 bytes Database Buffers 16777216 bytes Redo Buffers 5529600 bytes Database mounted.

    SQL> recover database until cancel using backup controlfile ;

    ORA-00279: change 3735790 generated at 07/28/2017 14:17:14 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_769_852647695.dbf

    ORA-00280: change 3735790 for thread 1 is in sequence #769 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3748986 generated at 07/28/2017 22:43:57 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_770_852647695.dbf

    ORA-00280: change 3748986 for thread 1 is in sequence #770

    ORA-00278: log file '/mnt/oracle/SME/saparch/1_769_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3757444 generated at 07/29/2017 04:00:20 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_771_852647695.dbf

    ORA-00280: change 3757444 for thread 1 is in sequence #771

    ORA-00278: log file '/mnt/oracle/SME/saparch/1_770_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3758171 generated at 07/29/2017 04:00:23 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_772_852647695.dbf

    ORA-00280: change 3758171 for thread 1 is in sequence #772

    ORA-00278: log file '/mnt/oracle/SME/saparch/1_771_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3763329 generated at 07/29/2017 07:00:26 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_773_852647695.dbf

    ORA-00280: change 3763329 for thread 1 is in sequence #773

    ORA-00278: log file '/mnt/oracle/SME/saparch/1_772_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3775490 generated at 07/29/2017 15:00:41 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_774_852647695.dbf

    ORA-00280: change 3775490 for thread 1 is in sequence #774 ORA-00278: log file '/mnt/oracle/SME/saparch/1_773_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3779066 generated at 07/29/2017 17:00:45 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_775_852647695.dbf

    ORA-00280: change 3779066 for thread 1 is in sequence #775

    ORA-00278: log file '/mnt/oracle/SME/saparch/1_774_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3779769 generated at 07/29/2017 17:00:47 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_776_852647695.dbf

    ORA-00280: change 3779769 for thread 1 is in sequence #776

    ORA-00278: log file '/mnt/oracle/SME/saparch/1_775_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3786510 generated at 07/29/2017 21:09:01 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_777_852647695.dbf

    ORA-00280: change 3786510 for thread 1 is in sequence #777

    ORA-00278: log file '/mnt/oracle/SME/saparch/1_776_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3788466 generated at 07/29/2017 22:00:54 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_778_852647695.dbf

    ORA-00280: change 3788466 for thread 1 is in sequence #778 ORA-00278: log file '/mnt/oracle/SME/saparch/1_777_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3795162 generated at 07/30/2017 02:00:01 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_779_852647695.dbf

    ORA-00280: change 3795162 for thread 1 is in sequence #779 ORA-00278: log file '/mnt/oracle/SME/saparch/1_778_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3801823 generated at 07/30/2017 06:00:09 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_780_852647695.dbf

    ORA-00280: change 3801823 for thread 1 is in sequence #780 ORA-00278: log file '/mnt/oracle/SME/saparch/1_779_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3802624 generated at 07/30/2017 06:00:11 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_781_852647695.dbf

    ORA-00280: change 3802624 for thread 1 is in sequence #781

    ORA-00278: log file '/mnt/oracle/SME/saparch/1_780_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3810483 generated at 07/30/2017 11:00:18 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_782_852647695.dbf

    ORA-00280: change 3810483 for thread 1 is in sequence #782

    ORA-00278: log file '/mnt/oracle/SME/saparch/1_781_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3815428 generated at 07/30/2017 14:00:23 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_783_852647695.dbf

    ORA-00280: change 3815428 for thread 1 is in sequence #783

    ORA-00278: log file '/mnt/oracle/SME/saparch/1_782_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3817688 generated at 07/30/2017 15:00:25 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_784_852647695.dbf

    ORA-00280: change 3817688 for thread 1 is in sequence #784

    ORA-00278: log file '/mnt/oracle/SME/saparch/1_783_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3826823 generated at 07/30/2017 21:00:35 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_785_852647695.dbf

    ORA-00280: change 3826823 for thread 1 is in sequence #785 ORA-00278: log file '/mnt/oracle/SME/saparch/1_784_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3830545 generated at 07/30/2017 23:00:39 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_786_852647695.dbf

    ORA-00280: change 3830545 for thread 1 is in sequence #786

    ORA-00278: log file '/mnt/oracle/SME/saparch/1_785_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3836962 generated at 07/31/2017 03:00:47 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_787_852647695.dbf

    ORA-00280: change 3836962 for thread 1 is in sequence #787

    ORA-00278: log file '/mnt/oracle/SME/saparch/1_786_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 3845964 generated at 07/31/2017 09:00:57 needed for thread 1

    ORA-00289: suggestion : /mnt/oracle/SME/saparch/1_788_852647695.dbf

    ORA-00280: change 3845964 for thread 1 is in sequence #788

    ORA-00278: log file '/mnt/oracle/SME/saparch/1_787_852647695.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00308: cannot open archived log '/mnt/oracle/SME/saparch/1_788_852647695.dbf'

    ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3

    ORA-10879: error signaled in parallel recovery slave

    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: '/mnt/oracle/CER/sapdata1/system_1/system.data1' SQL> recover database until cancel using backup controlfile ; ORA-00275: media recovery has already been started SQL> recover database until cancel using backup controlfile ;

    ORA-00275: media recovery has already been started SQL> shutdown immediate ;

    ORA-01109: database not open Database dismounted. ORACLE instance shut down.

    SQL> startup

    Total System Global Area 150347776 bytes Fixed Size 2211128 bytes

    Variable Size 125829832 bytes

    Database Buffers 16777216 bytes

    Redo Buffers 5529600 bytes Database mounted.

    ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

    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: '/mnt/oracle/CER/sapdata1/system_1/system.data1'

    SQL> alter database open noresetlogs ; alter database open noresetlogs * ERROR at line 1:

    ORA-01588: must use RESETLOGS option for database open

    SQL> alter database resetlogs force ; alter database resetlogs force * ERROR at line 1:

    ORA-00905: missing keyword

    SQL> set wrap off

    SQL> set linesize 120

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

    SQL> SQL> Session altered.

    SQL> truncating (as requested) before column COUNT(*) STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME RESETLOGS_CHANGE# RESETLOGS_TIMEFUZ

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

    ONLINE 5592529 11-APR-2017 12:35:08 1 11-JUL-2014 14:34:55 YES

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

    MIN(FHRBA_SEQ) MAX(FHRBA_SEQ) ------------------------------ ------------------------------

    1092 1092

    SQL>

    Why is it saying MIN SEQ 1092 and MAX SEQ 1092 ? I don't have those archive logs in my source machine. The highest is existing source archive log is 1_787_852647695.dbf

    I am lost with the above output trying to folllow metalink KB Doc ID 1528788.1

    Any help would be greatly appreciated .

    Yung Song

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 31, 2017 at 05:19 PM

    additional select output:

    SQL> select status, resetlogs_change#, resetlogs_time, checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time, count(*) from v$datafile_header group by status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time order by status, checkpoint_change#, checkpoint_time ; truncating (as requested) before column COUNT(*) STATUS

    RESETLOGS_CHANGE# RESETLOGS_TIMECHECKPOINT_CHANGE# CHECKPOINT_TIME

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

    ONLINE 1 11-JUL-2014 14:34:55 5592529 11-APR-2017 12:35:08

    SQL>

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 31, 2017 at 05:49 PM

    more additional output:

    SQL> select file#, error, fuzzy, count(*) from v$datafile_header group by file#, status, error, fuzzy ;

    FILE# ERROR FUZ COUNT(*)

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

    1 YES 1

    2 YES 1

    3 YES 1

    4 YES 1

    5 YES 1

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

    MIN(CHECKPOINT_CHANGE#) MAX(CHECKPOINT_CHANGE#)

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

    5592529 5592529

    can someone help me ?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 31, 2017 at 06:13 PM

    additional info:

    SQL> select substr(L.GROUP#,1,6) GROUP# , substr(L.THREAD#,1,7) THREAD# , substr(L.SEQUENCE#,1,10) SEQUENCE# , substr(L.MEMBERS,1,7) MEMBERS , substr(L.ARCHIVED,1,8) ARCHIVED , substr(L.STATUS,1,10) STATUS , substr(L.FIRST_CHANGE#,1,16) FIRST_CHANGE# , substr(LF.member,1,60) REDO_LOGFILE from GV$LOG L, GV$LOGFILE LF

    where L.GROUP# = LF.GROUP# ;

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

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

    1 1 765 2 YES INACTIVE 3732692 /mnt/oracle/CER/origlogA/log1_m1.dbf

    1 1 765 2 YES INACTIVE 3732692 /mnt/oracle/CER/mirrlogA/log1_m2.dbf

    2 1 766 2 YES INACTIVE 3734142 /mnt/oracle/CER/origlogB/log2_m1.dbf

    4 1 768 2 NO CURRENT 3735592 /mnt/oracle/CER/mirrlogB/log4_m2.dbf

    3 1 767 2 YES ACTIVE 3734160 /mnt/oracle/CER/origlogA/log3_m1.dbf

    3 1 767 2 YES ACTIVE 3734160 /mnt/oracle/CER/mirrlogA/log3_m2.dbf

    4 1 768 2 NO CURRENT 3735592 /mnt/oracle/CER/origlogB/log4_m1.dbf

    2 1 766 2 YES INACTIVE 3734142 /mnt/oracle/CER/mirrlogB/log2_m2.dbf 8 rows selected.

    SQL> select thread#, sequence#, substr(name,1,80) from v$Archived_log where 5592529 between first_change# and next_change#; THREAD# SEQUENCE# SUBSTR(NAME,1,80)

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

    1 1091 /mnt/oracle/CER/mirrlogA/log3_m2.dbf

    1 1091 /mnt/oracle/CER/origlogA/log3_m1.dbf

    1 1092 /mnt/oracle/CER/mirrlogB/log4_m2.dbf

    1 1092 /mnt/oracle/CER/origlogB/log4_m1.dbf

    What should I do next ?

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 03, 2017 at 01:31 AM

    Hi Yung Song,

    I am a little lost on your serial info, can you give one summary about your concern?

    To be honest metalink Doc ID 1528788.1 talks some oracle unpublished dictionary X$KCVFH, I am not sure as well.


    Best regards,
    James

    Add comment
    10|10000 characters needed characters exceeded