标签：主库 备库 log app oradata 切换 oracle group u01
问题描述：物理备库在切换为主库时报错ORA-01577，如下所示：
数据库：oracle 11.2.0.4
系统架构：rac(2节点)+dg
1、异常重现
SYS@orcldg> alter database commit to switchover to primary with session shutdown;
alter database commit to switchover to primary with session shutdown
*
ERROR at line 1:
ORA-01577: cannot add log file '/u01/app/oracle/oradata/group_1.257.1132745547' - file already part of database
2、异常原因
主库端redo文件分别存放在+data/orcl/onlinelog和+fra/orcl/onlinelog目录下，存放在+data和+fra日志组1的两个成员文件名却相同，都是group_1.257.1132745547，日志组2也存在相同问题.
此种情况下搭建的ADG就会出现同个目录下有两个相同文件名的redo日志，在进行主备切换时便会出现ORA-01577告警.
3、redo文件相关查询
主库
ASMCMD> pwd
+data/orcl/onlinelog
ASMCMD> ls
group_1.257.1132745547
group_10.268.1132751451
group_11.269.1132751473
group_12.270.1132751475
group_13.271.1132751475
group_14.272.1132751475
group_15.273.1132751475
group_2.258.1132745547
group_3.265.1132747513
group_4.266.1132747513
standby10.log
standby11.log
standby12.log
standby13.log
standby14.log
standby15.log
ASMCMD> pwd
+fra/orcl/onlinelog
ASMCMD> ls
group_1.257.1132745547
group_10.267.1132751451
group_11.268.1132751473
group_12.269.1132751475
group_13.270.1132751475
group_14.271.1132751475
group_15.272.1132751475
group_2.258.1132745547
group_3.259.1132747513
group_4.260.1132747515
standby10B.log
standby11B.log
standby12B.log
standby13B.log
standby14B.log
standby15B.log
注意：日志组1和2的两个成员文件名相同，分别是group_1.257.1132745547和group_2.258.1132745547.
备库
SYS@orcldg> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/group_1.257.1132745547
/u01/app/oracle/oradata/group_1.257.1132745547
/u01/app/oracle/oradata/group_2.258.1132745547
/u01/app/oracle/oradata/group_2.258.1132745547
/u01/app/oracle/oradata/group_3.265.1132747513
/u01/app/oracle/oradata/group_3.259.1132747513
/u01/app/oracle/oradata/group_4.266.1132747513
/u01/app/oracle/oradata/group_4.260.1132747515
/u01/app/oracle/oradata/standby10.log
/u01/app/oracle/oradata/standby10b.log
/u01/app/oracle/oradata/standby11.log
/u01/app/oracle/oradata/standby11b.log
/u01/app/oracle/oradata/standby12.log
/u01/app/oracle/oradata/standby12b.log
/u01/app/oracle/oradata/standby13.log
/u01/app/oracle/oradata/standby13b.log
/u01/app/oracle/oradata/standby14.log
/u01/app/oracle/oradata/standby14b.log
/u01/app/oracle/oradata/standby15.log
/u01/app/oracle/oradata/standby15b.log
20 rows selected.
[oracle@hisdbdg oradata]$ ls -ltr
total 5390480
-rw-r----- 1 oracle oinstall 52429312 May 5 19:31 group_1.257.1132745547
-rw-r----- 1 oracle oinstall 52429312 May 5 19:31 group_2.258.1132745547
-rw-r----- 1 oracle oinstall 52429312 May 5 19:31 group_3.265.1132747513
-rw-r----- 1 oracle oinstall 52429312 May 5 19:31 group_3.259.1132747513
-rw-r----- 1 oracle oinstall 52429312 May 5 19:31 group_4.266.1132747513
-rw-r----- 1 oracle oinstall 52429312 May 5 19:31 group_4.260.1132747515
-rw-r----- 1 oracle oinstall 69214208 May 5 19:33 temp.262.1132745555
-rw-r----- 1 oracle oinstall 471867392 May 6 11:40 tbs_tst_idx_bak.286.1134752063
-rw-r----- 1 oracle oinstall 52429312 Jun 6 21:26 standby12.log
-rw-r----- 1 oracle oinstall 52429312 Jun 6 21:26 standby12b.log
-rw-r----- 1 oracle oinstall 52429312 Jun 7 18:42 standby15.log
-rw-r----- 1 oracle oinstall 52429312 Jun 7 18:42 standby15b.log
-rw-r----- 1 oracle oinstall 52429312 Jun 9 19:52 standby14.log
-rw-r----- 1 oracle oinstall 52429312 Jun 9 19:52 standby14b.log
-rw-r----- 1 oracle oinstall 52429312 Jun 9 21:23 standby10.log
-rw-r----- 1 oracle oinstall 52429312 Jun 9 21:23 standby10b.log
-rw-r----- 1 oracle oinstall 52429312 Jun 9 21:23 standby13.log
-rw-r----- 1 oracle oinstall 52429312 Jun 9 21:23 standby13b.log
-rw-r----- 1 oracle oinstall 52429312 Jun 9 21:48 standby11.log
-rw-r----- 1 oracle oinstall 52429312 Jun 9 21:48 standby11b.log
-rw-r----- 1 oracle oinstall 1111498752 Jun 9 21:48 system.259.1132745549
-rw-r----- 1 oracle oinstall 52436992 Jun 9 21:48 users.285.1133862437
-rw-r----- 1 oracle oinstall 52436992 Jun 9 21:48 users.284.1133862457
-rw-r----- 1 oracle oinstall 52436992 Jun 9 21:48 users.283.1133862473
-rw-r----- 1 oracle oinstall 5251072 Jun 9 21:48 users.264.1132745567
-rw-r----- 1 oracle oinstall 209723392 Jun 9 21:48 undotbs2.263.1132745567
-rw-r----- 1 oracle oinstall 880812032 Jun 9 21:48 undotbs1.261.1132745553
-rw-r----- 1 oracle oinstall 671096832 Jun 9 21:48 sysaux.260.1132745551
-rw-r----- 1 oracle oinstall 52436992 Jun 9 21:48 users.282.1133862489
-rw-r----- 1 oracle oinstall 52436992 Jun 9 21:48 users.281.1133862509
-rw-r----- 1 oracle oinstall 52436992 Jun 9 21:48 users.280.1133862525
-rw-r----- 1 oracle oinstall 104865792 Jun 9 21:48 users.279.1133862553
-rw-r----- 1 oracle oinstall 104865792 Jun 9 21:48 users.278.1133862571
-rw-r----- 1 oracle oinstall 52436992 Jun 9 21:48 users.277.1133862585
-rw-r----- 1 oracle oinstall 52436992 Jun 9 21:48 users.276.1133862603
-rw-r----- 1 oracle oinstall 52436992 Jun 9 21:48 users.275.1133862625
-rw-r----- 1 oracle oinstall 52436992 Jun 9 21:48 users.274.1133862645
-rw-r----- 1 oracle oinstall 471867392 Jun 9 21:48 tbs_tst_idx.286.1134752063
-rw-r----- 1 oracle oinstall 18792448 Jun 9 22:38 control01.ctl
说明：如上所示，搭建好的ADG视图v$logfile中记录的日志组1为两个相同的文件名group_1.257.1132745547，而系统层面却只有1个，日志组2也是相同的问题.
4、解决方案：
将日志组1和2的两个成员分别放到不同目录，同时修改ADG的log_file_name_convert参数.
[oracle@hisdbdg oradata]$ pwd
/u01/app/oracle/oradata
[oracle@hisdbdg oradata]$ mkdir redo01
SYS@orcldg> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SYS@orcldg> alter system set standby_file_management=manual;
System altered.
SYS@orcldg> alter database rename file '/u01/app/oracle/oradata/group_1.257.1132745547' to '/u01/app/oracle/oradata/redo01/group_1.257.1132745547';
Database altered.
SYS@orcldg> alter database rename file '/u01/app/oracle/oradata/group_2.258.1132745547' to '/u01/app/oracle/oradata/redo01/group_2.258.1132745547';
Database altered.
SYS@orcldg> alter database rename file '/u01/app/oracle/oradata/group_3.259.1132747513' to '/u01/app/oracle/oradata/redo01/group_3.259.1132747513';
Database altered.
SYS@orcldg> alter database rename file '/u01/app/oracle/oradata/group_4.260.1132747515' to '/u01/app/oracle/oradata/redo01/group_4.260.1132747515';
Database altered.
SYS@orcldg> alter database rename file '/u01/app/oracle/oradata/standby10b.log' to '/u01/app/oracle/oradata/redo01/standby10b.log';
Database altered.
SYS@orcldg> alter database rename file '/u01/app/oracle/oradata/standby11b.log' to '/u01/app/oracle/oradata/redo01/standby11b.log';
Database altered.
SYS@orcldg> alter database rename file '/u01/app/oracle/oradata/standby12b.log' to '/u01/app/oracle/oradata/redo01/standby12b.log';
Database altered.
SYS@orcldg> alter database rename file '/u01/app/oracle/oradata/standby13b.log' to '/u01/app/oracle/oradata/redo01/standby13b.log';
Database altered.
SYS@orcldg> alter database rename file '/u01/app/oracle/oradata/standby14b.log' to '/u01/app/oracle/oradata/redo01/standby14b.log';
Database altered.
SYS@orcldg> alter database rename file '/u01/app/oracle/oradata/standby15b.log' to '/u01/app/oracle/oradata/redo01/standby15b.log';
Database altered.
--修改备库log_file_name_convert，将主库的+FRA/orcl/onlinelog/路径指向备库的/u01/app/oracle/oradata/redo01/路径.
SYS@orcldg> alter system set log_file_name_convert='+DATA/orcl/onlinelog/','/u01/app/oracle/oradata/','+FRA/orcl/onlinelog/','/u01/app/oracle/oradata/redo01/','+DATA/orcl/controlfile/','/u01/app/oracle/oradata/','+FRA/orcl/controlfile/','/u01/app/oracle/oradata/' scope=spfile;
System altered.
--重启生效
SYS@orcldg> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SYS@orcldg> startup mount;
ORACLE instance started.
Total System Global Area 2455228416 bytes
Fixed Size 2255712 bytes
Variable Size 620758176 bytes
Database Buffers 1811939328 bytes
Redo Buffers 20275200 bytes
Database mounted.
SYS@orcldg> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 0 2 CLEARING_CURRENT YES
2 1 0 2 UNUSED YES
3 2 0 2 CURRENT YES
4 2 0 2 UNUSED YES
SYS@orcldg> select group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
---------- ---------------
1 50
2 50
3 50
4 50
SYS@orcldg> alter database add logfile thread 1 group 5 ('/u01/app/oracle/oradata/group_1.log','/u01/app/oracle/oradata/redo01/group_1.log') size 50M;
Database altered.
SYS@orcldg> alter database drop logfile group 2;
Database altered.
--系统层面删除日志组2的redo文件
[oracle@hisdbdg redo01]$ rm -f group_2.258.1132745547
[oracle@hisdbdg redo01]$ cd ..
[oracle@hisdbdg oradata]$ pwd
/u01/app/oracle/oradata
[oracle@hisdbdg oradata]$ rm -f group_2.258.1132745547
--添加日志组2文件
SYS@orcldg> alter database add logfile group 2 ('/u01/app/oracle/oradata/group_2.258.1132745547','/u01/app/oracle/oradata/redo01/group_2.258.1132745547') size 50M;
Database altered.
[oracle@hisdbdg oradata]$ mv standby10b.log redo01
[oracle@hisdbdg oradata]$ mv standby11b.log redo01
[oracle@hisdbdg oradata]$ mv standby12b.log redo01
[oracle@hisdbdg oradata]$ mv standby13b.log redo01
[oracle@hisdbdg oradata]$ mv standby14b.log redo01
[oracle@hisdbdg oradata]$ mv standby15b.log redo01
[oracle@hisdbdg oradata]$ ll redo01
total 563244
-rw-r----- 1 oracle oinstall 52429312 Jun 9 23:58 group_1.257.1132745547
-rw-r----- 1 oracle oinstall 52429312 Jun 9 23:51 group_1.log
-rw-r----- 1 oracle oinstall 52429312 Jun 9 23:55 group_2.258.1132745547
-rw-r----- 1 oracle oinstall 52429312 May 5 19:31 group_3.259.1132747513
-rw-r----- 1 oracle oinstall 52429312 Jun 9 23:32 group_4.260.1132747515
-rw-r----- 1 oracle oinstall 52429312 Jun 9 21:23 standby10b.log
-rw-r----- 1 oracle oinstall 52429312 Jun 9 21:48 standby11b.log
-rw-r----- 1 oracle oinstall 52429312 Jun 6 21:26 standby12b.log
-rw-r----- 1 oracle oinstall 52429312 Jun 9 21:23 standby13b.log
-rw-r----- 1 oracle oinstall 52429312 Jun 9 19:52 standby14b.log
-rw-r----- 1 oracle oinstall 52429312 Jun 7 18:42 standby15b.log
--此时在来处理日志组1，因为日志组1状态为current，所以留在最后来处理.
说明：当时主库已执行alter database commit to switchover to physical standby with session shutdown;主切备语句，后来有将其重新切回主库后操作.
[oracle@hisdbdg oradata]$ ll group_1.257.1132745547
-rw-r----- 1 oracle oinstall 52429312 May 5 19:31 group_1.257.1132745547
[oracle@hisdbdg oradata]$ ll group_2.258.1132745547
-rw-r----- 1 oracle oinstall 52429312 Jun 9 23:55 group_2.258.1132745547
--在主库多次进行日志切换，直到备库日志组1为CLEARING或为UNUSED状态.
SYS@orcldg> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 299 2 CLEARING YES
2 1 301 2 CURRENT YES
3 2 153 2 CURRENT YES
4 2 152 2 CLEARING YES
5 1 300 2 CLEARING YES
SYS@orcldg> alter database clear logfile group 1;
Database altered.
SYS@orcldg> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 0 2 UNUSED YES
2 1 301 2 CURRENT YES
3 2 153 2 CURRENT YES
4 2 152 2 CLEARING YES
5 1 300 2 CLEARING YES
SYS@orcldg> alter database drop logfile group 1;
Database altered.
[oracle@hisdbdg oradata]$ rm -f group_1.257.1132745547
[oracle@hisdbdg oradata]$ cd redo01
[oracle@hisdbdg redo01]$ rm -f group_1.257.1132745547
SYS@orcldg> alter database add logfile thread 1 group 1 ('/u01/app/oracle/oradata/group_1.257.1132745547','/u01/app/oracle/oradata/redo01/group_1.257.1132745547') size 50M;
Database altered.
--日志组1成功处理OK，此时同样在主库多次切换日志.
SYS@orcldg> select group#,thread#,sequence#,members,status,archived from v$log；
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 302 2 CURRENT YES
2 1 301 2 CLEARING YES
3 2 153 2 CLEARING YES
4 2 154 2 CURRENT YES
5 1 300 2 CLEARING YES
SYS@orcldg> alter database clear logfile group 5;
Database altered.
SYS@orcldg> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 302 2 CURRENT YES
2 1 301 2 CLEARING YES
3 2 153 2 CLEARING YES
4 2 154 2 CURRENT YES
5 1 0 2 UNUSED YES
SYS@orcldg> alter database drop logfile group 5;
Database altered.
SYS@orcldg> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/group_1.257.1132745547
/u01/app/oracle/oradata/redo01/group_1.257.1132745547
/u01/app/oracle/oradata/group_2.258.1132745547
/u01/app/oracle/oradata/redo01/group_2.258.1132745547
/u01/app/oracle/oradata/group_3.265.1132747513
/u01/app/oracle/oradata/redo01/group_3.259.1132747513
/u01/app/oracle/oradata/group_4.266.1132747513
/u01/app/oracle/oradata/redo01/group_4.260.1132747515
/u01/app/oracle/oradata/standby10.log
/u01/app/oracle/oradata/redo01/standby10b.log
/u01/app/oracle/oradata/standby11.log
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/redo01/standby11b.log
/u01/app/oracle/oradata/standby12.log
/u01/app/oracle/oradata/redo01/standby12b.log
/u01/app/oracle/oradata/standby13.log
/u01/app/oracle/oradata/redo01/standby13b.log
/u01/app/oracle/oradata/standby14.log
/u01/app/oracle/oradata/redo01/standby14b.log
/u01/app/oracle/oradata/standby15.log
/u01/app/oracle/oradata/redo01/standby15b.log
20 rows selected.
SYS@orcldg> alter system set standby_file_management=auto;
System altered.
说明：目前日志组两个成员文件名相同，但在不同目录下，此后备库成功切换为主库.
标签：主库,
备库,
log,
app,
oradata,
切换,
oracle,
group,
u01
From： https://blog.51cto.com/u_12991611/6456063