• Oracle 11.2.0.4.0 Dataguard部署和日常维护(7)


    1. 设置备库的闪回目录

    show parameter db_recovery_file;
    
    NAME                             TYPE                         VALUE
    ------------------------------------ --------------------------------- -------------------------------------------
    db_recovery_file_dest                string                        /u01/app/oracle/fast_recovery_area
    db_recovery_file_dest_size           big integer                      4G

    2. 开启备库的闪回功能

    select flashback_on from v$database;
    
    FLASHBACK_ON
    ------------------------------------------------------
    NO
    
    alter database flashback on;
    
    alter database flashback on
    *
    ERROR at line 1:
    ORA-01153: an incompatible media recovery is active
    
    alter database recover managed standby database cancel;
    alter database flashback on;
    select flashback_on from v$database;
    
    FLASHBACK_ON
    ------------------------------------------------------
    YES
    alter database recover managed standby database using current logfile disconnect from session;

    3. 检查主备库同步状态

    • on primary
    select ads.dest_id,max(sequence#) "Current Sequence",
               max(log_sequence) "Last Archived"
           from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
           where ad.dest_id=al.dest_id
           and al.dest_id=ads.dest_id
           and al.resetlogs_change#=(select max(resetlogs_change#) from v$archived_log )
           group by ads.dest_id;
    
       DEST_ID Current Sequence Last Archived
    ---------- ---------------- -------------
         1         79           79
         2         79           80
    • on standby
    select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
          from (select thread# thrd, max(sequence#) almax
              from v$archived_log
              where resetlogs_change#=(select resetlogs_change# from v$database)
              group by thread#) al,
             (select thread# thrd, max(sequence#) lhmax
              from v$log_history
              where resetlogs_change#=(select resetlogs_change# from v$database)
              group by thread#) lh
         where al.thrd = lh.thrd;
    
        Thread Last Seq Received Last Seq Applied
    ---------- ----------------- ----------------
         1          79           79

    4. 取消备库的恢复进程

    select process,status from v$managed_standby;
    
    PROCESS             STATUS
    ------------------- ------------------------------------
    ARCH                CLOSING
    ARCH                CLOSING
    ARCH                CONNECTED
    ARCH                CLOSING
    RFS                 IDLE
    RFS                 IDLE
    RFS                 IDLE
    MRP0                APPLYING_LOG
    
    alter database recover managed standby database cancel;
    
    select process,status from v$managed_standby;
    
    PROCESS             STATUS
    ------------------- ------------------------------------
    ARCH                CLOSING
    ARCH                CLOSING
    ARCH                CONNECTED
    ARCH                CLOSING
    RFS                 IDLE
    RFS                 IDLE
    RFS                 IDLE

    5. 创建备库的还原点

    create restore point before_open_standby guarantee flashback database;
    select name from v$restore_point;
    
    NAME
    --------------------------------------------------
    BEFORE_OPEN_STANDBY

    6. 在主库归档日志

    alter system archive log current;

    7. 确认备库已经归档了最新的日志

    select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
          from (select thread# thrd, max(sequence#) almax
              from v$archived_log
              where resetlogs_change#=(select resetlogs_change# from v$database)
              group by thread#) al,
             (select thread# thrd, max(sequence#) lhmax
              from v$log_history
              where resetlogs_change#=(select resetlogs_change# from v$database)
              group by thread#) lh
         where al.thrd = lh.thrd;
    
        Thread Last Seq Received Last Seq Applied
    ---------- ----------------- ----------------
         1          80           79

    8. 延迟指向被激活的备库的日志归档目的地

    show parameter log_archive_dest_state_2;
    
    NAME                             TYPE                              VALUE
    ------------------------------------ --------------------------------- ------------------------------
    log_archive_dest_state_2            string                             ENABLE
    
    alter system set log_archive_dest_state_2='DEFER';
    
    show parameter log_archive_dest_state_2;
    
    NAME                                  TYPE                              VALUE
    ------------------------------------ --------------------------------- ------------------------------
    log_archive_dest_state_2              string                            DEFER

    9. 激活备库

    alter database activate standby database;
    alter database open;
    select open_mode,database_role from v$database;
    
    OPEN_MODE                                           DATABASE_ROLE
    ------------------------------------------------------------ ------------------------------------------------
    READ WRITE                                           PRIMARY

    10. 向备库写入测试数据

    begin
    for i in 1..10000 loop
    insert into test10 values (i,'shall');
    end loop;
    commit;
    end;
     /
    
    PL/SQL procedure successfully completed.
    
    select count(*) from test10;
    
      COUNT(*)
    ----------
         10000

    11. 将备库闪回至还原点

    shutdown immediate;
    startup mount;
    flashback database to restore point before_open_standby;
    alter database convert to physical standby;
    shutdown immediate;
    startup mount;
    alter database recover managed standby database using current logfile disconnect from session;

    12. 重新启用到备库的日志归档目的地

    alter system set log_archive_dest_state_2='ENABLE';
    show parameter log_archive_dest_state_2;
    
    NAME                             TYPE                         VALUE
    ------------------------------------ --------------------------------- ------------------------------
    log_archive_dest_state_2            string                         ENABLE

    13. 测试数据同步是否正常

    •  on primary
    alter system archive log current;
    alter system archive log current;
    
    select ads.dest_id,max(sequence#) "Current Sequence",
               max(log_sequence) "Last Archived"
           from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
           where ad.dest_id=al.dest_id
           and al.dest_id=ads.dest_id
           and al.resetlogs_change#=(select max(resetlogs_change#) from v$archived_log )
           group by ads.dest_id;
    
      DEST_ID Current Sequence Last Archived
    ---------- ---------------- -------------
         1         83           83
         2         83           84
    • on standby
    select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
          from (select thread# thrd, max(sequence#) almax
              from v$archived_log
              where resetlogs_change#=(select resetlogs_change# from v$database)
              group by thread#) al,
             (select thread# thrd, max(sequence#) lhmax
              from v$log_history
              where resetlogs_change#=(select resetlogs_change# from v$database)
              group by thread#) lh
         where al.thrd = lh.thrd;
    
        Thread Last Seq Received Last Seq Applied
    ---------- ----------------- ----------------
         1          83           83

    14. 清理闪回点

    select name from v$restore_point;
    
    NAME
    --------------------------------------------------
    BEFORE_OPEN_STANDBY
    
    drop restore point BEFORE_OPEN_STANDBY;
  • 相关阅读:
    LoadRunner创建脚本和场景流程
    Monitorix系统和网络监控工具
    查询日志logcat使用总结
    SqlServer存储过程示例
    编写sql查询语句思路
    dstat工具使用介绍
    dstat参数选项
    SqlServer50条常用查询语句
    MySQL查询示例
    CMake 常用方法
  • 原文地址:https://www.cnblogs.com/ilifeilong/p/7124145.html
Copyright © 2020-2023  润新知