sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle GG环境主备库日志同步失败解决办法汇总

时间:2016-11-07 09:19   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映查看OGG同步状态,没有同步,停在了凌晨12点过,查看日志,归档路径不存在。

查看OGG同步状态,没有同步,停在了凌晨12点过,查看日志,归档路径不存在,又遇这样的问题,这里重点看看数据库里面的原因,没有贴ogg的日志。这是遇到的第二个物理备库归档文件存放在错误的路径下面的案例,也就是没有存放在log_archive_dest_n参数指定的路径下面。

上一个案例是配置2个归档路径,但是每次只在两个中随机选择一个归档路径存放。这个案例是归档文件存放到FRA里面,没有存放在log_archive_dest_n指定的路径下面。

1,数据库alert日志

RFS[41]: No standby redo logfiles available for thread 1
这里报没有备库日志文件可用

RFS[41]: Opened log for thread 1 sequence 108610 dbid 653721835 branch 844866667
Thu May 19 00:04:50 2016
Archived Log entry 182030 added for thread 1 sequence 108607 ID 0x26f6aaeb dest 1:
Thu May 19 00:05:39 2016
Archived Log entry 182031 added for thread 1 sequence 108610 rlc 844866667 ID 0x26f6aaeb dest 2:
这里看到归档文件在路径2下面
通过ALERT日志大概知道是什么原因导致的。

确认一下归档文件的路径

SQL> select name from v$archived_log where sequence#=108610;

NAME
--------------------------------------------------------------------------------
/ora_arch/HTZSTANDBY/archivelog/2016_05_19/o1_mf_1_108610_cms4n0rx_.arc

2,确认归档参数

查看一个归档路径参数的配置信息,确认dest_2的配置。

QL> show parameter archive                                                                                                                 
                                                                                                          
NAME                                 TYPE                             VALUE                               
------------------------------------ -------------------------------- ------------------------------      
archive_lag_target                   integer                          0                                   
log_archive_config                   string                           DG_CONFIG=(htzadb,htzastdy)         
log_archive_dest                     string                                                               
log_archive_dest_1                   string                           location=/ora_arch/arch_for_og      
                                                                      g VALID_FOR=(ALL_LOGFILES,ALL_      
                                                                      ROLES) DB_UNIQUE_NAME=htzastdy      
log_archive_dest_2                   string                                    
log_archive_trace                    integer                          0                                                                        
standby_archive_dest                 string                            /dbs/arch                                    
SQL> show parameter recovery                                                                                        
                                                                                                                    
NAME                                 TYPE                             VALUE                                         
------------------------------------ -------------------------------- ------------------------------                
db_recovery_file_dest                string                           /ora_arch                                     
db_recovery_file_dest_size           big integer                      100G                                          
recovery_parallelism                 integer                          16
这里归档路径2是空,也就意味着如果配置FRA,那么在归档路径不可用的情况下,会自动把归档放到FRA里面。

3,查看日志文件

下面脚本只查询了status,type,其实还应该根据v$standby_log的archived,status两列来同时判断。

                                                                                                                                                           
                                bytes                                                                                                                      
  G  T STATUS     TYPE              M STATUS     FIRST_CHANGE# NEXT_CHANGE# MEMBER                                                       First Time        
--- -- ---------- ---------- -------- ---------- ------------- ------------ --------------------------------------------------------Oracle oracleplus.net---- -------------------
  1  1 CLEARING   ONLINE         2048               1.4930E+13   1.4930E+13 +HTZADG/htzastdy/onlinelog/group_1.264.846868585             20160519 10:02:41 
  2  1 CLEARING   ONLINE         2048               1.4930E+13   1.4930E+13 +HTZADG/htzastdy/onlinelog/group_2.263.846868591             20160519 10:10:11 
  5  1 CLEARING   ONLINE         2048               1.4930E+13   1.4930E+13 +HTZADG/htzastdy/onlinelog/group_5.260.846868611             20160519 10:17:18 
  6  1 CLEARING   ONLINE         2048               1.4930E+13   1.4930E+13 +HTZADG/htzastdy/onlinelog/group_6.259.846868617             20160519 10:26:06 
  7  1 CLEARING   ONLINE         2048               1.4930E+13   1.4930E+13 +HTZADG/htzastdy/onlinelog/group_7.258.846868623             20160519 10:30:39 
  8  1 CURRENT    ONLINE         2048               1.4930E+13   1.4930E+13 +HTZADG/htzastdy/onlinelog/group_8.257.846868631             20160519 10:38:25 
15  1 ACTIVE     STANDBY        2048               1.4930E+13   1.4930E+13 +HTZADG/htzastdy/onlinelog/group_15.281.846867873            20160519 10:38:25 
16  1 UNASSIGNED STANDBY        2048                                       +HTZADG/htzastdy/onlinelog/group_16.282.846867875                              
24  1 UNASSIGNED STANDBY        2048                                       +HTZADG/htzastdy/onlinelog/group_24.266.846867901                              
  3  2 CLEARING   ONLINE         2048               1.4930E+13   1.4930E+13 +HTZADG/htzastdy/onlinelog/group_3.262.846868597             20160519 10:17:20 
  4  2 CURRENT    ONLINE         2048               1.4930E+13   1.4930E+13 +HTZADG/htzastdy/onlinelog/group_4.261.846868605             20160519 10:30:41 
  9  2 CLEARING   ONLINE         2048               1.4930E+13   1.4930E+13 +HTZADG/htzastdy/onlinelog/group_9.566.846868637             20160519 08:30:59 
10  2 CLEARING   ONLINE         2048               1.4930E+13   1.4930E+13 +HTZADG/htzastdy/onlinelog/group_10.567.846868643            20160519 09:04:45 
11  2 CLEARING   ONLINE         2048               1.4930E+13   1.4930E+13 +HTZADG/htzastdy/onlinelog/group_11.568.846868649            20160519 09:30:48 
12  2 CLEARING   ONLINE         2048               1.4930E+13   1.4930E+13 +HTZADG/htzastdy/onlinelog/group_12.569.846868657            20160519 09:56:31 
13  2 ACTIVE     STANDBY        2048               1.4930E+13   1.4930E+13 +HTZADG/htzastdy/onlinelog/group_13.279.846867865            20160519 10:30:41 
14  2 UNASSIGNED STANDBY        2048                                       +HTZADG/htzastdy/onlinelog/group_14.280.846867869                              
17  2 UNASSIGNED STANDBY        2048                                       +HTZADG/htzastdy/onlinelog/group_17.283.846867879                              
18  2 UNASSIGNED STANDBY        2048                                       +HTZADG/htzastdy/onlinelog/group_18.270.846867881                              
19  2 UNASSIGNED STANDBY        2048                                       +HTZADG/htzastdy/onlinelog/group_19.269.846867885                              
20  2 UNASSIGNED STANDBY        2048                                       +HTZADG/htzastdy/onlinelog/group_20.272.846867887                              
21  2 UNASSIGNED STANDBY        2048                                       +HTZADG/htzastdy/onlinelog/group_21.271.846867891                              
22  2 UNASSIGNED STANDBY        2048                                       +HTZADG/htzastdy/onlinelog/group_22.268.846867895                              
23  2 UNASSIGNED STANDBY        2048                                       +HTZADG/htzastdy/onlinelog/group_23.267.846867897                              
25  2 UNASSIGNED STANDBY        2048                                       +HTZADG/htzastdy/onlinelog/group_25.265.846867903
这里看到线程1的STANDBY日志组只有3组,ONLINE日志组有6组(这个需要去主库确认),DG要求备库的日志组个数必须是主库个数加1。

4,解决方案

添加备库日志组就可以了,由于已经第二次遇到这样的问题了,于是写了一个备库添加的脚本

5,思考

环境中一共只有几套DG,就遇到2套DG有这个问题。DG搭建完全就是实施类的工作,不需要技术要求,但是需要标准文档。这个DG环境搭建时可能少执行几条语句导致了这个问题。在每种实施类的工作,我们都应该有自己的实施标准文档,不断的完善,不断的总结。也应该让很多实施类工作脚本化,减少人为粗心埋下的一些隐患。



本文固定链接: http://www.htz.pw/2016/05/20/%e4%b8%bb%e5%ba%93%e5%bd%92%e6%a1%a3%e5%88%87%e6%8d%a2%e9%a2%91%e7%b9%81%e5%af%bc%e8%87%b4%e5%a4%87%e5%ba%93%e5%bd%92%e6%a1%a3%e6%97%a5%e5%bf%97%e5%ad%98%e6%94%be%e5%9c%a8%e9%94%99%e8%af%af%e7%9a%84.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle GG环境主备库日志同步失败解决办法汇总

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1192.html

Oracle研究中心

关键词:

No standby redo logfiles available for thread 1解决笔记

OGG日志同步失败

OGG主库归档切换频繁导致备库归档日志存放在错误的路径