• 案例:ADG环境遇到redo日志member路径有误以及RMAN-6571错误


    最近先后帮客户做了两套从虚拟化环境到物理机的数据库迁移,都是Linux系统,Oracle 11.2.0.4的RAC,最终选定ADG方案实现迁移,简单高效。
    在之前的文章Oracle 11g ADG 部署(duplicate)快速参考中,已经详细介绍了搭建步骤。但本次环境准备时还是遇到些小问题,本文记录下解决过程。

    问题1:备库Redo的一个member路径有误

    按流程做完发现备库在open后,Redo的一个member路径有误,都是+FRA磁盘组:
    SQL> select member from v$logfile;
    
    MEMBER
    -----------------------------------------------------------
    +DATA/jingyus/onlinelog/group_1.383.1050758359
    +FRA
    +DATA/jingyus/onlinelog/group_2.384.1050758359
    +FRA
    +DATA/jingyus/onlinelog/group_3.385.1050758359
    +FRA
    +DATA/jingyus/onlinelog/group_4.386.1050758359
    +FRA
    +DATA/jingyus/onlinelog/group_5.397.1050758359
    +FRA
    +DATA/jingyus/onlinelog/group_6.398.1050758361
    
    MEMBER
    -----------------------------------------------------------
    +FRA
    +DATA/jingyus/onlinelog/group_7.399.1050758361
    +FRA
    +DATA/jingyus/onlinelog/group_8.400.1050758361
    +FRA
    +DATA/jingyus/standbylog/standby_group_101.log
    +DATA/jingyus/standbylog/standby_group_102.log
    +DATA/jingyus/standbylog/standby_group_103.log
    +DATA/jingyus/standbylog/standby_group_104.log
    +DATA/jingyus/standbylog/standby_group_105.log
    +DATA/jingyus/standbylog/standby_group_201.log
    
    MEMBER
    ----------------------------------------------------------
    +DATA/jingyus/standbylog/standby_group_202.log
    +DATA/jingyus/standbylog/standby_group_203.log
    +DATA/jingyus/standbylog/standby_group_204.log
    +DATA/jingyus/standbylog/standby_group_205.log
    
    26 rows selected.
    

    这样的路径不全,也无法使用常规命令删除掉。
    首先想到的是查convert参数是否配置有误:

    --convert:
    SQL> show parameter convert
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_file_name_convert                 string      jingyu, jingyus
    log_file_name_convert                string      +fra/jingyu, +arch/jingyus, jingyu,
                                                     jingyus
    

    确认符合实际要求,没有问题。

    检查db_recovery相关参数:

    SQL> show parameter db_recovery
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_recovery_file_dest                string      +FRA
    db_recovery_file_dest_size           big integer 90G
    

    果然是这里有问题,新环境应该是+ARCH磁盘组,而且客户这里的新规范是不配置此参数,这里将参数去掉,重启实例生效:

    SQL> alter system reset db_recovery_file_dest_size;
    
    System altered.
    
    SQL> alter system reset db_recovery_file_dest;
    
    System altered.
    SQL> shutdown immediate 
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount;
    ORACLE instance started.
    
    Total System Global Area 7.4826E+10 bytes
    Fixed Size                  2261048 bytes
    Variable Size            1.3959E+10 bytes
    Database Buffers         6.0666E+10 bytes
    Redo Buffers              199049216 bytes
    Database mounted.
    SQL> show parameter db_recover
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_recovery_file_dest                string
    db_recovery_file_dest_size           big integer 0
    SQL> 
    

    问题2:switch database to copy报错RMAN-6571

    上面改完之后,已经有问题的member并不会自己修复,需要去主库生成适用于备库的控制文件,在备库进行恢复:
    --standby controlfile
    primary:
    RMAN> backup current controlfile for standby format '/tmp/std_ctl.bak';
    
    scp to standby.
    
    standby:
    shutdown immediate
    startup nomount
    RMAN> restore standby controlfile from '/tmp/std_ctl.bak';
    alter database mount;
    

    此时数据文件的名字因为OMF并不一样,convert转换的只有jingyu->jingyus,下面是示例:

    selct name from v$datafile;
    select member from v$Logfile;
    
    NAME
    --------------------------------------------------------------------------------
    +DATA/jingyu/datafile/dmb_ts.381.1046616217
    +DATA/jingyu/datafile/dmb_ts.383.1047808801
    +DATA/jingyu/datafile/dmo_ts.384.1048122001
    +DATA/jingyu/datafile/dmb_ts.385.1048755601
    +DATA/jingyu/datafile/dmb_ts.386.1049724001
    +DATA/jingyu/datafile/rpm.387.1049986803
    
    116 rows selected.
    
    --由于配置了db_file_name_convert 参数:
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_file_name_convert                 string      jingyu, jingyus
                                                                 
    --会按上面设置的规则转换:
    NAME
    --------------------------------------------------------------------------------
    +DATA/jingyus/datafile/dmb_ts.381.1046616217
    +DATA/jingyus/datafile/dmb_ts.383.1047808801
    +DATA/jingyus/datafile/dmo_ts.384.1048122001
    +DATA/jingyus/datafile/dmb_ts.385.1048755601
    +DATA/jingyus/datafile/dmb_ts.386.1049724001
    +DATA/jingyus/datafile/rpm.387.1049986803
    
    116 rows selected.
    

    但实际上我们同步过来的数据文件是这样:

    +DATA/jingyus/datafile/dmb_ts.342.1050704211
    +DATA/jingyus/datafile/dmb_ts.363.1050706625
    +DATA/jingyus/datafile/dmo_ts.364.1050706641
    +DATA/jingyus/datafile/dmb_ts.365.1050706649
    +DATA/jingyus/datafile/dmb_ts.366.1050706655
    +DATA/jingyus/datafile/rpm.367.1050706663
    
    116 rows selected.
    

    最直接的方式是通过数据库的rename file 命令进行一一更正,但是比较麻烦,有一个通用的技巧就是将这些真实的文件catalog到rman中,将以copy的方式识别,然后直接switch到copy,就实现了更名的目的,且不容易出错:

    catalog start with '+DATA/jingyus/datafile';
    switch database to copy;
    

    结果10号文件报错RMAN-6571,跳过10号文件,也是其他文件接连报错,看oerr的解释:

    $ oerr rman 6571
    6571, 1, "datafile %d does not have recoverable copy"
    // *Cause: The SWITCH command with the option TO COPY was specified but
    //         the datafile has no valid copy to switch to.
    // *Action: Verify whether the datafile has a valid datafile copy.
    

    顺手还去查了MOS文档

    • OERR: RMAN-6571 "datafile %d does not have recoverable copy" Reference Note (Doc ID 291493.1)

    也没找到有效的解决方案。后来走了些弯路,又尝试做了一次备库控制文件的创建,效果依旧。
    此时想到不如查下文件头,看看到底差异在哪,结果发现文件10-20都是别名的方式,根本不需要去switch:

    SQL> select file#, name, checkpoint_change# from v$datafile_header;
    
                             FILE# NAME                                                                           CHECKPOINT_CHANGE#
    ------------------------------ ------------------------------------------------------------------ ------------------------------
                                 1                                                                                                 0
                                 2                                                                                                 0
                                 3                                                                                                 0
                                 4                                                                                                 0
                                 5                                                                                                 0
                                 6                                                                                                 0
                                 7                                                                                                 0
                                 8                                                                                                 0
                                 9                                                                                                 0
                                10 +DATA/jingyus/datafile/dmb_ts01.dbf                                                     98520735063
                                11 +DATA/jingyus/datafile/dmb_ts02.dbf                                                     98520735063
    
                             FILE# NAME                                                                           CHECKPOINT_CHANGE#
    ------------------------------ ------------------------------------------------------------------ ------------------------------
                                12 +DATA/jingyus/datafile/dmb_ts03.dbf                                                     98520735063
                                13 +DATA/jingyus/datafile/dmb_ts04.dbf                                                     98520735063
                                14 +DATA/jingyus/datafile/dmo_ts01.dbf                                                     98520735063
                                15 +DATA/jingyus/datafile/dmo_ts02.dbf                                                     98520735063
                                16 +DATA/jingyus/datafile/dmo_ts03.dbf                                                     98520735063
                                17 +DATA/jingyus/datafile/etl_ts01.dbf                                                     98520735063
                                18 +DATA/jingyus/datafile/rpm01.dbf                                                        98520735063
                                19 +DATA/jingyus/datafile/use01.dbf                                                        98520735063
                                20 +DATA/jingyus/datafile/dms_ts01.dbf                                                     98520735063
                                21                                                                                                 0
                                22                                                                                                 0
    ...省略后面无问题的显示。
    

    再次验证下问题文件数,就是有这11个:

    SQL> select checkpoint_change#, count(*) from v$datafile_Header group by checkpoint_change#;
    
                CHECKPOINT_CHANGE#                       COUNT(*)
    ------------------------------ ------------------------------
                                 0                            105
                       98520757598                             11
    

    确认后,就只需要将需要switch的文件列出来:

    switch datafile 1,2,3,4,5,6,7,8,9,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101 to copy;
    

    再次查询:

    SQL> set num 30
    SQL> select checkpoint_change#, count(*) from v$datafile_Header group by checkpoint_change#;
    
                CHECKPOINT_CHANGE#                       COUNT(*)
    ------------------------------ ------------------------------
                       98520757598                            116
    

    此时开库,名字也都是OK:

    SQL> alter database open;
    
    Database altered.
    
    SQL>  select member from v$logfile;
    
    MEMBER
    ---------------------------------------------------------
    +DATA/jingyus/onlinelog/group_1.257.950284165
    +ARCH/jingyus/onlinelog/group_1.257.950284167
    +DATA/jingyus/onlinelog/group_2.258.950284167
    +ARCH/jingyus/onlinelog/group_2.258.950284169
    +DATA/jingyus/onlinelog/group_3.265.950286045
    +ARCH/jingyus/onlinelog/group_3.259.950286047
    +DATA/jingyus/onlinelog/group_4.266.950286047
    +ARCH/jingyus/onlinelog/group_4.260.950286049
    +DATA/jingyus/onlinelog/group_5.286.959014699
    +ARCH/jingyus/onlinelog/group_5.266.959014703
    +DATA/jingyus/onlinelog/group_6.287.959014717
    
    MEMBER
    ---------------------------------------------------------
    +ARCH/jingyus/onlinelog/group_6.273.959014719
    +DATA/jingyus/onlinelog/group_7.288.959014729
    +ARCH/jingyus/onlinelog/group_7.277.959014731
    +DATA/jingyus/onlinelog/group_8.289.959014753
    +ARCH/jingyus/onlinelog/group_8.269.959014755
    +DATA/jingyus/standbylog/standby_group_101.log
    +DATA/jingyus/standbylog/standby_group_102.log
    +DATA/jingyus/standbylog/standby_group_103.log
    +DATA/jingyus/standbylog/standby_group_104.log
    +DATA/jingyus/standbylog/standby_group_105.log
    +DATA/jingyus/standbylog/standby_group_201.log
    +DATA/jingyus/standbylog/standby_group_202.log
    +DATA/jingyus/standbylog/standby_group_203.log
    +DATA/jingyus/standbylog/standby_group_204.log
    +DATA/jingyus/standbylog/standby_group_205.log
    
    26 rows selected.
    

    然后启动备库的mrp时,会自动删除+ARCH 下的路径,这个应该就是因为我们前面去掉了db_recover的相关设置:

    --recover ,+ARCH auto deleted..
    
    SQL> select member from v$logfile;
    
    MEMBER
    --------------------------------------------------------
    +DATA/jingyus/onlinelog/group_1.397.1050759359
    +DATA/jingyus/onlinelog/group_2.398.1050759359
    +DATA/jingyus/onlinelog/group_3.399.1050759359
    +DATA/jingyus/onlinelog/group_4.400.1050759361
    +DATA/jingyus/onlinelog/group_5.401.1050759361
    +DATA/jingyus/onlinelog/group_6.402.1050759361
    +DATA/jingyus/onlinelog/group_7.403.1050759361
    +DATA/jingyus/onlinelog/group_8.404.1050759363
    +DATA/jingyus/standbylog/standby_group_101.log
    +DATA/jingyus/standbylog/standby_group_102.log
    +DATA/jingyus/standbylog/standby_group_103.log
    +DATA/jingyus/standbylog/standby_group_104.log
    +DATA/jingyus/standbylog/standby_group_105.log
    +DATA/jingyus/standbylog/standby_group_201.log
    +DATA/jingyus/standbylog/standby_group_202.log
    +DATA/jingyus/standbylog/standby_group_203.log
    +DATA/jingyus/standbylog/standby_group_204.log
    +DATA/jingyus/standbylog/standby_group_205.log
    
    18 rows selected.
    

    至此,遇到的问题就都解决了。松一口气,等待晚上配合切换即可。

  • 相关阅读:
    初识Redis
    一次kafka的offset回退事件及相关知识点
    接口透传
    看懂Oracle执行计划
    Oracle中merge into的使用
    动态规划算法:0/1背包问题 (0/1 Knapsack Problem)
    动态规划算法:硬币找零(Minimum Coin Change)
    链表:按照左右半区的方式重新组合单链表
    链表:删除链表中倒数第K个节点
    链表:合并两个有序链表
  • 原文地址:https://www.cnblogs.com/jyzhao/p/13651954.html
Copyright © 2020-2023  润新知