In this Document
|
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
