下面介绍两则11.2.0.4 physical standby两则故障处理

1. ORA-1033

1.1 报错

主库alert日志报错如下：

Error 1033 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'target'. Error is 1033.
Fri Mar 12 09:10:01 2021
复制

1.2 排查

使用sqlplus使用target tnsnames连接physical standby，
sqlplus sys/password@target as sysdba可以正常连接。

1.3 原因

ora 1033发生在physical standby环境中，如果物理备库状态正常，一般由于密码文件异常导致。

1.4 解决

重新将主库密码文件scp至备库，rename即可解决。

2. 未正确配置参数导致备库redo在开启应用之后变为源库磁盘组名称

2.1 问题描述

主库使用ASM，备库使用文件系统。
搭建Dataguard之后，在开启recover managed standby database disconnect from session时，
physical standby logfile位置均变成了+datadg源端主库磁盘组名称，未正确rename为备库路径。

备库日志报错如下：
Errors in file xxx/trace/sid_mrp0_45884.trc:
ORA-00313: open failed for members of log group xx of thread xx
ORA-00312: online log xx thread 1: '+DATADG/sid/onlinelog/group_xx.286.1062779691'
ORA-17503: ksfdopn:2 Failed to open file +DATADG/sid/onlinelog/group_xx.286.1062779691
ORA-15001: diskgroup "DATADG" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-00312: online log 6 thread 1: '+DATADG/sid/onlinelog/group_xx.266.1020201301'
ORA-17503: ksfdopn:2 Failed to open file +DATADG/sid/onlinelog/group_xx.266.1020201301
ORA-15001: diskgroup "DATADG" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
Clearing online redo logfile xx complete
Media Recovery Waiting for thread xx sequence xxxx
Completed: ALTER DATABASE RECOVER  managed standby database disconnect from session  

复制

备库日志位置此时如下：

SYS@dg > select * from v$logfile;

GROUP# STATUS     TYPE    MEMBER                                                       IS_
------ ---------- ------- ------------------------------------------------------------ ---
     5 INVALID    ONLINE  +datadg                                                      NO
     2 INVALID    ONLINE  +datadg                                                      NO
     1 INVALID    ONLINE  +datadg                                                      NO
     6 INVALID    ONLINE  +datadg                                                      NO
     6 INVALID    ONLINE  +datadg                                                      NO


复制

2.2 原因

由于构建备库时未正确设置log_file_name_convert，也未手动处理physical standby redo logfile物理位置，导致。

2.3 解决

1. 主库

-- 先暂时停止源端传输。
SYS@src > alter system set log_archive_dest_state_2=defer;

复制

2. 备库

--取消日志应用
SYS@dg > recover managed standby database cancel;

--正确设置log_file_name_convert
SYS@dg > alter system set log_file_name_convert='+DATADG/sid/onlinelog','/oradata' scope=spfile;

复制

3. 主库

--主库备份控制文件
RMAN> backup current controlfile for standby format '/rman/ctl_bak.bak';

-- 传输至备端
$ scp /rman/ctl_bak.bak targt:/rman/
复制

4. 备库

--重新恢复控制文件
RMAN> shutdown immediate;

RMAN> startup nomount;

RMAN> restore controlfile from '/rman/ctl_bak.bak';

--注册备库数据文件并切换
RMAN> catalog start with '/home/oracle/oradata/';

RMAN> switch database to copy;

--确认备库redo logfile是否正确转换
SYS@siddg > select * from v$logfile;

GROUP# STATUS     TYPE    MEMBER                                                       IS_
------ ---------- ------- ------------------------------------------------------------ ---
     5            ONLINE  /oradata/group_5.263.984508005                   NO
     2            ONLINE  /oradata/group_2.262.984508005                   NO
     1            ONLINE  /oradata/group_1.261.984508005                   NO
     6            ONLINE  /oradata/group_6.266.1020201301                  NO
     6            ONLINE  /oradata/group_6.286.1062779691                  NO
复制

5. 主库

--源端开启传输
SYS@src > alter system set log_archive_dest_state_2=enable;

复制

6. 备库

--开启应用
SYS@dg > alter database open read only;
SYS@dg > alter database recover managed standby database using current logfile disconnect from session;

--观察备库alert日志，会发现会自动最后clear掉logfile，重新在新位置生成logfile
Errors in file xxx.trc:
ORA-00313: open failed for members of log group xx of thread x
ORA-00312: online log xx thread 1: 'path'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log xx thread xx: 'path'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile xx complete

复制

END.