Skip to Content
0

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

Jul 28, 2017 at 09:20 PM

729

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

7 Answers

Best Answer
James Zhang
Jul 31, 2017 at 12:36 AM
1

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jul 31, 2017 at 08:22 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jul 31, 2017 at 05:08 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jul 31, 2017 at 05:19 PM
0

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>

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jul 31, 2017 at 05:49 PM
0

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 ?

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jul 31, 2017 at 06:13 PM
0

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 ?

Share
10 |10000 characters needed characters left characters exceeded
James Zhang
Aug 03, 2017 at 01:31 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded