RE: ALTER DATABASE OPEN RESETLOGS

From: Josh Collier <Josh.Collier_at_banfield.net>
Date: Thu, 10 Apr 2014 21:25:58 +0000
Message-ID: <D0534F8D31056242BE8E38FA9413FDA817F3E984_at_M1EXCHMB11.mmi.local>



Why are you specifying which control file to restore? It seems to be that you should just use the SET UNTIL TIME and let RMAN determine which controlfile to restore. If its not working with duplicate, doing it with "also just restore DB" probably isn't going to work either.

Are you setting db|log file name convert parameters?

Josh C.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark Sent: Friday, April 04, 2014 9:12 AM
To: Oracle L
Subject: RE: ALTER DATABASE OPEN RESETLOGS

Tiran, shouldn't the restore specify that it should be done using "backup control file"?

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/osadvsce.htm#CHDGJAEH

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Andrew Kerber Sent: Friday, April 04, 2014 10:01 AM
To: Tiran Harutyunyan
Cc: Oracle L
Subject: Re: ALTER DATABASE OPEN RESETLOGS

The issue is not with the missing logfiles. Thats normal, Oracle will re-create the redo logs. The error 1152 indicates that the file was not restored from a sufficiently old backup. Sometimes that means you restored the wrong controlfile and you need to restore an earlier one.

On Fri, Apr 4, 2014 at 4:37 AM, Outsider <mufc01666_at_gmail.com<mailto:mufc01666_at_gmail.com>> wrote: Dear,

I wonder if somebody can help to solve this problem. I opened SR in Oracle and after week I got answer like they cannot help me and again they ask logs.

I just wanted to restore primary DB to another host to make duplicate of that DB. I tried with DB duplicate with RMAN and also just restore DB. After ALTER DATABASE OPEN RESETLOGS In got a lot errors.

Oracle said that

The data dictionary is corrupt which is the reason we see the ORA-00942 errors.

here what I did....

run {
ALLOCATE CHANNEL 't11' type 'sbt_tape' parms="ENV=(TDPO_OPTFILE=/u01/app/oracle/admin/csrdev1/tsm207/tdpo.opt)"; restore controlfile from 'c-517364674-20140323-01';
}

run {
ALLOCATE CHANNEL 't11' type 'sbt_tape' parms="ENV=(TDPO_OPTFILE=/u01/app/oracle/admin/csrdev1/tsm207/tdpo.opt)"; SET UNTIL TIME "to_date('03/23/14 4:00:00','mm/dd/yy hh24:mi:ss')"; restore database;
recover database;
}

result



Finished recover at Mar 27 2014 18:14:59 released channel: t11

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/27/2014 18:17:06
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
Process ID: 30035
Session ID: 19 Serial number: 21
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-03114: not connected to ORACLE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/27/2014 18:17:06
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

Process ID: 30035
Session ID: 19 Serial number: 21

from alert log

Full restore complete of datafile 10 /u02/app/oracle/oradata/CSRDEV1/datafile/o1_mf_psai_9m8pdd9o_.dbf. Elapsed time: 0:03:57 checkpoint is 516215190
Thu Mar 27 18:13:50 2014

Signalling error 1152 for datafile 1!
Signalling error 1152 for datafile 2!
Signalling error 1152 for datafile 3!
Signalling error 1152 for datafile 4!
Signalling error 1152 for datafile 5!
Signalling error 1152 for datafile 6!
Signalling error 1152 for datafile 7!
Signalling error 1152 for datafile 8!
Signalling error 1152 for datafile 9!
Signalling error 1152 for datafile 10!
Signalling error 1152 for datafile 11!
Signalling error 1152 for datafile 12!
Signalling error 1152 for datafile 13!
Signalling error 1152 for datafile 14!
Signalling error 1152 for datafile 15!

Signalling error 1152 for datafile 16!
Signalling error 1152 for datafile 17!
Checker run found 1 new persistent data failures Thu Mar 27 18:13:51 2014
alter database recover datafile list clear Completed: alter database recover datafile list clear alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 Completed: alter database recover datafile list 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 alter database recover if needed
start until time 'MAR 23 2014 04:00:00' using backup controlfile Media Recovery Start
Serial Media Recovery started
ORA-279 signalled during: alter database recover if needed start until time 'MAR 23 2014 04:00:00' using backup controlfile ...
Thu Mar 27 18:14:23 2014
alter database recover logfile '/u01/app/oracle/fast_recovery_area/CSRDEV1/archivelog/2014_03_27/o1_mf_1_12348_9m8q395c_.arc' Media Recovery Log /u01/app/oracle/fast_recovery_area/CSRDEV1/archivelog/2014_03_27/o1_mf_1_12348_9m8q395c_.arc ORA-279 signalled during: alter database recover logfile '/u01/app/oracle/fast_recovery_area/CSRDEV1/archivelog/2014_03_27/o1_mf_1_12348_9m8q395c_.arc'... alter database recover logfile '/u01/app/oracle/fast_recovery_area/CSRDEV1/archivelog/2014_03_27/o1_mf_1_12349_9m8q39fz_.arc' Media Recovery Log /u01/app/oracle/fast_recovery_area/CSRDEV1/archivelog/2014_03_27/o1_mf_1_12349_9m8q39fz_.arc ORA-279 signalled during: alter database recover logfile '/u01/app/oracle/fast_recovery_area/CSRDEV1/archivelog/2014_03_27/o1_mf_1_12349_9m8q39fz_.arc'... Thu Mar 27 18:14:58 2014
alter database recover logfile '/u01/app/oracle/fast_recovery_area/CSRDEV1/archivelog/2014_03_27/o1_mf_1_12350_9m8q3vbd_.arc' Media Recovery Log /u01/app/oracle/fast_recovery_area/CSRDEV1/archivelog/2014_03_27/o1_mf_1_12350_9m8q3vbd_.arc Incomplete Recovery applied until change 516220911 time 03/23/2014 04:00:10 Media Recovery Complete (csrdev1)
Completed: alter database recover logfile '/u01/app/oracle/fast_recovery_area/CSRDEV1/archivelog/2014_03_27/o1_mf_1_12350_9m8q3vbd_.arc'

AND also what I found from trace files

AND FROM trace file

DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident) ORA-00312: online log 3 thread 1: '/u01/app/oracle/fast_recovery_area/CSRDEV1/onlinelog/o1_mf_3_84vs73jk_.log' ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory Additional information: 3
ORA-00312: online log 3 thread 1: '/u02/app/oracle/oradata/CSRDEV1/onlinelog/o1_mf_3_84vs738x_.log' ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory Additional information: 3
DDE: Problem Key 'ORA 313' was flood controlled (0x1) (no incident)

ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/fast_recovery_area/CSRDEV1/onlinelog/o1_mf_3_84vs73jk_.log'
ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory Additional information: 3
ORA-00312: online log 3 thread 1: '/u02/app/oracle/oradata/CSRDEV1/onlinelog/o1_mf_3_84vs738x_.log' ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory Additional information: 3
ORA-00313: open failed for members of log group 3 of thread 1 ORA-003 I don't understand why the names of online logs are wrong.... the path is correct but the names are wrong.....

Thank you for your time.



Tiran
Best Regards
Stay in touch...
--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 10 2014 - 23:25:58 CEST

Original text of this message