• 主库增加表空间导致DG同步失败


    主库增加表空间导致DG同步失败

    问题现象:

    由于主库表空间不足,同事给表空间增加数据文件,第二天收到反馈说备库未同步。

    1、主、备查看归档序列号,发现主、备归档正常同步。

    SQL>archive log list

    2、在主库端查询v$archived_log视图,确认日志是否被应用

    set lines 300 pages 300
    col name for a20
    select name,dest_id,thread#,sequence#,standby_dest,applied,registrar,completion_time from v$archived_log
    where standby_dest='YES'
    order by thread#,sequence#;

    3、在备库查看接收到的被应用的redo,发现日志未被应用

    在备库查看已经应用和正在应用的日志:
    SELECT SEQUENCE#,APPLIED,TO_CHAR(COMPLETION_TIME, 'yy-mm-dd hh24:mi:ss') FROM V$ARCHIVED_LOG where APPLIED='YES' OR APPLIED like '%IN-MEMRY%'  ORDER BY SEQUENCE#; SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

    4、处理方式:

    现在备库中建立了对应的文件夹并赋权
    将备库中STANDBY_FILE_MANAGEMENT选项设定为MANUAL
    查找出对应的UNNAMED 文件
    将UNNAMED文件重命名为指定需要新建的数据文件
    将备库STANDBY_FILE_MANAGEMENT 恢复设置为auto
    同步数据

    主备库查看:
    show parameter STANDBY_FILE_MANAGEMENT
    ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL; select name from v$datafile where name like '%UNNAMED%';

    主库
    主库:
    SQL>
    col name for a50
    select file#,name from v$datafile;
    
         FILE# NAME
    ---------- ----------------------------------------------------------------------------------------------------
             1 /opt/oradata/shdb/system01.dbf
             2 /opt/oradata/shdb/sysaux01.dbf
             3 /opt/oradata/shdb/undotbs01.dbf
             4 /opt/oradata/shdb/users01.dbf
             5 /opt/oradata/shdb/tbs_hank_dat01.dbf
             6 /opt/oradata/shdb/tbs_hank_idx01.dbf
             7 /opt/oradata/shdb/tbs_dock_dat01.dbf
             8 /opt/oradata/shdb/tbs_mok_dat01.dbf
             9 /opt/oradata/shdb/tbs_lod_dat01.dbf
            10 /opt/oradata/shdb/tbs_book_idx01.dbf
            11 /opt/oradata/shdb/tbs_book_part_dat01.dbf
            12 /opt/oradata/shdb/tbs_book_part_idx01.dbf
            13 /opt/oradata/shdb/system02.dbf
            14 /opt/oradata/shdb/tbs_book_idx02.dbf
            15 /opt/oradata/shdb/tbs_book_part_dat02.dbf
            16 /opt/oradata/shdb/tbs_dock_dat02.dbf
            17 /opt/oradata/shdb/tbs_book_idx03.dbf
            18 /opt/oradata/shdb/tbs_book_part_dat03.dbf
            19 /opt/oradata/shdb/tbs_book_idx04.dbf
            20 /opt/oradata/shdb/tbs_book_idx05.dbf
            21 /opt/oradata/shdb/tbs_book_part_dat04.dbf
            22 /opt/oradata/shdb/tbs_book_part_dat05.dbf
            23 /opt/oradata/shdb/tbs_dock_dat03.dbf
            24 /opt/oradata/shdb/user02.dbf
    
    24 rows selected.

    备库:

    备库:
    
    SQL> select name from v$datafile where name like '%UNNAMED%';
    
    NAME
    --------------------------------------------------
    /opt/oracle/app/product/11g/dbs/UNNAMED00019
    col name for a50
    select file#,name from v$datafile;
    
         FILE# NAME
    ---------- --------------------------------------------------
             1 /opt/oradata/shdbstd/system01.dbf
             2 /opt/oradata/shdbstd/sysaux01.dbf
             3 /opt/oradata/shdbstd/undotbs01.dbf
             4 /opt/oradata/shdbstd/users01.dbf
             5 /opt/oradata/shdbstd/tbs_hank_dat01.dbf
             6 /opt/oradata/shdbstd/tbs_hank_idx01.dbf
             7 /opt/oradata/shdbstd/tbs_dock_dat01.dbf
             8 /opt/oradata/shdbstd/tbs_mok_dat01.dbf
             9 /opt/oradata/shdbstd/tbs_lod_dat01.dbf
            10 /opt/oradata/shdbstd/tbs_book_idx01.dbf
            11 /opt/oradata/shdbstd/tbs_book_part_dat01.dbf
    
         FILE# NAME
    ---------- --------------------------------------------------
            12 /opt/oradata/shdbstd/tbs_book_part_idx01.dbf
            13 /opt/oradata/shdbstd/system02.dbf
            14 /opt/oradata/shdbstd/tbs_book_idx02.dbf
            15 /opt/oradata/shdbstd/tbs_book_part_dat02.dbf
            16 /opt/oradata/shdbstd/tbs_dock_dat02.dbf
            17 /opt/oradata/shdbstd/tbs_book_idx03.dbf
            18 /opt/oradata/shdbstd/tbs_book_part_dat03.dbf
            19 /opt/oracle/app/product/11g/dbs/UNNAMED00019
    
    19 rows selected.
    
    SQL> 

    和主库对比,进行修改

    alter database create datafile  '/opt/oracle/app/product/11g/dbs/UNNAMED00019' as '/opt/oradata/shdb/tbs_book_idx04.dbf';
    
    recover managed standby database disconnect;

    继续查询备库:

    SQL> select name from v$datafile;
    
    NAME
    --------------------------------------------------
    /opt/oradata/shdbstd/system01.dbf
    /opt/oradata/shdbstd/sysaux01.dbf
    /opt/oradata/shdbstd/undotbs01.dbf
    /opt/oradata/shdbstd/users01.dbf
    /opt/oradata/shdbstd/tbs_hank_dat01.dbf
    /opt/oradata/shdbstd/tbs_hank_idx01.dbf
    /opt/oradata/shdbstd/tbs_dock_dat01.dbf
    /opt/oradata/shdbstd/tbs_mok_dat01.dbf
    /opt/oradata/shdbstd/tbs_lod_dat01.dbf
    /opt/oradata/shdbstd/tbs_book_idx01.dbf
    /opt/oradata/shdbstd/tbs_book_part_dat01.dbf
    
    NAME
    --------------------------------------------------
    /opt/oradata/shdbstd/tbs_book_part_idx01.dbf
    /opt/oradata/shdbstd/system02.dbf
    /opt/oradata/shdbstd/tbs_book_idx02.dbf
    /opt/oradata/shdbstd/tbs_book_part_dat02.dbf
    /opt/oradata/shdbstd/tbs_dock_dat02.dbf
    /opt/oradata/shdbstd/tbs_book_idx03.dbf
    /opt/oradata/shdbstd/tbs_book_part_dat03.dbf
    /opt/oradata/shdb/tbs_book_idx04.dbf
    /opt/oracle/app/product/11g/dbs/UNNAMED00020
    
    20 rows selected.
    可以发现,又出现一个UNNAMED00020
    同样的方法处理
    alter database create datafile  '/opt/oracle/app/product/11g/dbs/UNNAMED00020' as '/opt/oradata/shdb/tbs_book_idx05.dbf';
    
    recover managed standby database disconnect;

    按照这种方法反复查询,知道主备数据文件数量一致,且备库不再出现UNNAMED***类似的数据文件

    ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=auto;

    5、备库启用apply,接收来自主库的redo并apply

    alter database recover managed standby database using current logfile disconnect from session;

    6、取消同步

    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

    7、备库再次启用apply,接收来自主库的redo并apply

    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

    8、根据步骤1、2、3进行验证。

    在备库查看已经应用和正在应用的日志:
    SELECT SEQUENCE#,APPLIED,TO_CHAR(COMPLETION_TIME, 'yy-mm-dd hh24:mi:ss') FROM V$ARCHIVED_LOG where APPLIED='YES' OR APPLIED like '%IN-MEMORY%' ORDER BY SEQUENCE#;

  • 相关阅读:
    第四章 解决面试题的思路
    第三章 高质量的代码
    第二章 面试需要的基础知识
    第九章 海量数据处理
    第八章 数据结构与算法
    用打王者荣耀的姿势,去做有意义的事【恶魔奶爸】
    linux入门级知识回顾
    Django回顾
    复习爬虫
    django预热
  • 原文地址:https://www.cnblogs.com/connected/p/10919562.html
Copyright © 2020-2023  润新知