OPEN RESETLOGS FAILS WITH ORA-01577 (Doc ID 953293.1)

season0891發表於2016-01-28
AI

OPEN RESETLOGS FAILS WITH ORA-01577 (Doc ID 953293.1)


In this Document

Symptoms
Changes
Cause
Solution
  SOLUTION 1
  SOLUTION 2
References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Database - Enterprise Edition - Version 8.0.3.0 to 10.2.0.3 [Release 8.0.3 to 10.2]
Information in this document applies to any platform.

Symptoms

+ Production database has been upgraded multiple times since version 8 and current version is 10g.

+ An incomplete / TSPITR of this database is done on a new Aux instance.

+ Recovery finishes successfully, however, ALTER DATABASE OPEN RESETLOGS fails with ORA-01577.

SQL> ALTER DATABASE OPEN RESETLOGS ;

*
ERROR at line 1:
ORA-01577: cannot add log file 'H:\RECOVERDB\LOGFILES\GROUP_1.DBF' - file already part of database

Changes

Production database has been upgraded multiple times since version 8 and current version is 10g. User is performing incomplete / TSPITR of production database on a new Aux instance.

Cause

+ There are more than one entries for the same name of redolog member in one or more redolog groups. For example, V$LOG and V$LOGMEMBER will show entries like this:

 

SQL> select GROUP#,THREAD#,
            SEQUENCE#,MEMBERS,ARCHIVED,
            STATUS,FIRST_CHANGE#
     from v$log ;

 

GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS FIRST_CHANGE#
1 1 11797 2 YES INACTIVE 47168439375
2 1 11798 2 NO CURRENT 47168465932
3 1 11794 2 YES INACTIVE 47168382944

 

SQL> select GROUP#,substr(member,1,60) from v$logfile;

 

GROUP# SUBSTR(MEMBER,1,60)
1 H:\RECOVERDB\LOGFILES\GROUP_1.DBF
1 H:\RECOVERDB\LOGFILES\GROUP_1.DBF
2 H:\RECOVERDB\LOGFILES\GROUP_2.DBF
2 H:\RECOVERDB\LOGFILES\GROUP_2.DBF
3 H:\RECOVERDB\LOGFILES\GROUP_3.DBF
3 H:\RECOVERDB\LOGFILES\GROUP_3.DBF


+ This could be due to Bug 1421332 which is reported as fixed in 9.0.1.

There is an internal unpublished Note 1421332.9 which describes the code bug.

Solution

SOLUTION 1

Recreate the controlfile as mentioned in Bug 1421332 :

a) Backup existing controlfile to some other location.

b) Open database in MOUNT mode:

SQL> STARTUP MOUNT  -- specifiy pfile if required

 c) Generate controlfile creation script with RESETLOGS:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS ;

d) Modify controfile script which is generated in USER_DUMP_DEST location and remove duplicate entries for redo log members.

e) Start the database in NOMOUNT mode and run create controlfile script :

SQL> STARTUP NOMOUNT -- specify pfile if required

SQL> @create_controlfile.sql


f) The database will be mounted when controlfile is created. Now open database with resetlogs:

SQL> ALTER DATABASE OPEN RESETLOGS ;

 

SOLUTION 2

Open the DATABASE in MOUNT mode and do the following:

a) Drop the logfile members in INACTIVE group. Here assume that log group 2 is CURRENT.

SQL> ALTER DATABASE DROP LOGFILE MEMBER 'H:\RECOVERDB\LOGFILES\GROUP_1.DBF' ;

SQL> ALTER DATABASE DROP LOGFILE MEMBER 'H:\RECOVERDB\LOGFILES\GROUP_3.DBF' ;


b) Rename the current log member to a different name:

SQL> ALTER DATABASE RENAME FILE 'H:\RECOVERDB\LOGFILES\GROUP_2.DBF'  TO 'H:\RECOVERDB\LOGFILES\GROUP_2_NEW.DBF' ;

If you now select from V$LOGMEMBER, you will see two entries for CURRENT log group, one with old name and one with new name.

c) Drop the new log member which you just created in step b) above:

SQL> ALTER DATABASE DROP LOGFILE MEMBER ''H:\RECOVERDB\LOGFILES\GROUP_2_NEW.DBF' ; 


Now select from V$LOGMEMBER will show one member per log group. More members can now be created if required.

d) Open the database with RESETLOGS:

SQL> ALTER DATABASE OPEN RESETLOGS ; 

References

BUG:1421332 - ORA-1577 DURING OPEN RESETLOGS: LOG FILE NAMES ARE WRONG IN MIG'ED CONTROL FILE
 

Document Details

 
Rate this document Email link to this documentOpen document in new windowPrintable Page
Type:
Status:
Last Major Update:
Last Update:
PROBLEM
MODERATED
Feb 6, 2013
Feb 6, 2013
     
 

Related Products

 
Oracle Database - Enterprise Edition
     
 

Document References

 
     
 

Recently Viewed

 
     

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-1399743/，如需轉載，請註明出處，否則將追究法律責任。

相關文章