关于ADG做failover失败，ORA-1577

zmyzmy726

报错如下：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

Database altered.

SQL>
SQL>
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-01577: cannot add log file '/u01/oradata/multistandby/group_1.257.872781509' - file already part of database


日志如下：
Tue Mar 03 14:18:39 2015
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
ALTER DATABASE SWITCHOVER TO PRIMARY (multistandby)
Maximum wait for role transition is 15 minutes.
Backup controlfile written to trace file /u01/oracle/diag/rdbms/multistandby/multistandby/trace/multistandby_ora_28385.trc
Standby terminal recovery start SCN: 1836431
RESETLOGS after incomplete recovery UNTIL CHANGE 1836433
Online log /u01/oradata/multistandby/group_1.257.872781509: Thread 1 Group 1 was previously cleared
Online log /u01/oradata/multistandby/group_1.257.872781509: Thread 1 Group 1 was previously cleared
Online log /u01/oradata/multistandby/group_2.258.872781509: Thread 1 Group 2 was previously cleared
Online log /u01/oradata/multistandby/group_2.258.872781509: Thread 1 Group 2 was previously cleared
Online log /u01/oradata/multistandby/group_3.265.872783647: Thread 2 Group 3 was previously cleared
Online log /u01/oradata/multistandby/group_3.259.872783647: Thread 2 Group 3 was previously cleared
Online log /u01/oradata/multistandby/group_4.266.872783649: Thread 2 Group 4 was previously cleared
Online log /u01/oradata/multistandby/group_4.260.872783649: Thread 2 Group 4 was previously cleared
ORA-1577 signalled during: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY...

Liu Maclean(刘相兵

odm finding:

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

Liu Maclean(刘相兵

1、
ORA-01577: cannot add log file '/u01/oradata/multistandby/group_1.257.872781509' - file already part of database

尝试drop掉以上/u01/oradata/multistandby/group_1.257.872781509 后重试是否报错？

若是产品环境 先备份控制文件 并backup controlfile to trace

zmyzmy726

根据您提供的资料，发现是控制文件中redolog文件名相同的问题，因为ADG端是本地文件系统，导致同文件同目录只有一份，但是很奇怪，之前也这种环境做过failover并不报这个错误，但是这次从升级11.2.0.3.0到11.2.0.3.5后，就会抛这个错误导致无法faiover，需将日志文件放到2个不同的目录，很奇怪。
另外，ASM中为何会出现相同名字的日志文件？？
SQL>  select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/multi/onlinelog/group_1.257.872781509
+FRA/multi/onlinelog/group_1.257.872781509
+DATA/multi/onlinelog/group_2.258.872781509
+FRA/multi/onlinelog/group_2.258.872781509
+DATA/multi/onlinelog/group_3.265.872783647
+FRA/multi/onlinelog/group_3.259.872783647
+DATA/multi/onlinelog/group_4.266.872783649
+FRA/multi/onlinelog/group_4.260.872783649

hollow_fdl

你设置了快速恢复区，就会这样