• [Oracle][DATAGUARD] 关于确认LOGICAL STANDBY的同期状况的方法


    Oracle的DATAGUARD环境,有PHYSICAL STANDBY和LOGICAL STANDBY两种。
    PHYSICAL STANDBY是传输REDO传到Standby端,然后由Standby端的MRP进程应用该Redo,以达到同期效果。
    LOGICAL STANDBY是传输REDO传到Standby端,然后由Standby端的LSP进程调用LogMiner来抽取SQL文,然后执行该SQL文以达到同期效果。
    这里介绍一个简单的确认同期状况的方法:

    ODM TEST CASE
    ===================
    Name = TC#1010_1

    ####Primary####

    SQL> set line 300
    SQL> select database_role,OPEN_MODE from v$database;
    select instance_name,status from v$instance;
    DATABASE_ROLE                                    OPEN_MODE
    ------------------------------------------------ ------------------------------------------------------------
    PRIMARY                                          READ WRITE

    SQL>

    INSTANCE_NAME                                    STATUS
    ------------------------------------------------ ------------------------------------
    orcl                                             OPEN

    SQL> create user USER_A identified by USER_A;

    ユーザーが作成されました。

    SQL> grant dba to USER_A;

    権限付与が成功しました。

    SQL> conn USER_A/USER_A
    接続されました。
    SQL> create table TBL_DGTEST(
    id char(8),
    USER_NAME varchar2(250));  2    3

    表が作成されました。

    SQL> declare
        vID     char(8);
        vText   varchar2(250);
     begin
        dbms_random.seed(uid);
       for i in 1..1000000
        loop
                vID := to_char(i, 'FM00000000');
                vText := dbms_random.string('x', 16);
               insert into TBL_DGTEST (id, USER_NAME) values (vID, vText);
               if (mod(i, 100) = 0) then
                       commit;
               end if;
       end loop;
       commit;
    end;
    /  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17

    PL/SQLプロシージャが正常に完了しました。

    SQL> select count(*) from TBL_DGTEST;

     COUNT(*)
    ----------
      1000000

    SQL>


    ####Standby####

    SQL> set line 300
    SQL> select database_role,OPEN_MODE from v$database;
    select instance_name,status from v$instance;
    DATABASE_ROLE                                    OPEN_MODE
    ------------------------------------------------ ------------------------------------------------------------
    LOGICAL STANDBY                                  READ WRITE ★LOGICAL STANDBY

    SQL>

    INSTANCE_NAME                                    STATUS
    ------------------------------------------------ ------------------------------------
    orcls                                            OPEN

    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; ★Begin applying sql

    データベースが変更されました。

    SQL> conn USER_A/USER_A
    接続されました。
    SQL> select count(*) from TBL_DGTEST;

     COUNT(*)
    ----------
      1000000 ★

    SQL>

    ODM TEST CASE
    ===================
    Name = TC#1010_2

    ####On Primary,insert 1000000 rows into TBL_DGTEST####

    SQL> SELECT THREAD#, SEQUENCE# FROM V$LOG WHERE STATUS='CURRENT'; ★You will check that SEQUENCE# was growing

      THREAD#  SEQUENCE#
    ---------- ----------
            1         96

    SQL> SELECT THREAD#, SEQUENCE# FROM V$LOG WHERE STATUS='CURRENT'; ★

      THREAD#  SEQUENCE#
    ---------- ----------
            1        101

    SQL> SELECT THREAD#, SEQUENCE# FROM V$LOG WHERE STATUS='CURRENT'; ★

      THREAD#  SEQUENCE#
    ---------- ----------
            1        102


    ####Use DBA_LOGSTDBY_LOG to moniter sql application on Standby####

    SQL> SELECT SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,TIMESTAMP,APPLIED FROM DBA_LOGSTDBY_LOG;

    SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP           APPLIED
    ---------- ------------- ------------ ------------------- ------------------------
           73        756171       757560 2017-03-01 20:29:55 YES
    <省略>
           92        827738       833358 2017-09-28 14:20:09 YES
           93        833358       834365 2017-09-28 14:57:39 CURRENT ★93
           94        834365       834411 2017-09-28 14:57:39 NO

    SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP           APPLIED
    ---------- ------------- ------------ ------------------- ------------------------
           95        834411       834994 2017-09-28 14:57:40 NO
           96        834994       837710 2017-09-28 15:01:28 NO
           97        837710       840227 2017-09-28 15:02:06 NO
           98        840227       842731 2017-09-28 15:02:39 NO
           99        842731       845235 2017-09-28 15:03:16 NO
          100        845235       847751 2017-09-28 15:03:49 NO
          101        847751       850377 2017-09-28 15:04:27 NO

    29行が選択されました。

    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; ★begin SQL application

    データベースが変更されました。

    SQL> SELECT SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,TIMESTAMP,APPLIED FROM DBA_LOGSTDBY_LOG;

    SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP           APPLIED
    ---------- ------------- ------------ ------------------- ------------------------
           73        756171       757560 2017-03-01 20:29:55 YES
    <省略>
           92        827738       833358 2017-09-28 14:20:09 YES
           93        833358       834365 2017-09-28 14:57:39 YES ★SQL application was over
           94        834365       834411 2017-09-28 14:57:39 YES ★SQL application was over

    SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP           APPLIED
    ---------- ------------- ------------ ------------------- ------------------------
           95        834411       834994 2017-09-28 14:57:40 YES ★SQL application was over
           96        834994       837710 2017-09-28 15:01:28 YES ★SQL application was over
           97        837710       840227 2017-09-28 15:02:06 CURRENT ★SQL application is on going
           98        840227       842731 2017-09-28 15:02:39 CURRENT ★SQL application is on going
           99        842731       845235 2017-09-28 15:03:16 NO ★SQL application is in line
          100        845235       847751 2017-09-28 15:03:49 NO ★
          101        847751       850377 2017-09-28 15:04:27 NO ★

    29行が選択されました。

    SQL> /

    SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP           APPLIED
    ---------- ------------- ------------ ------------------- ------------------------
           73        756171       757560 2017-03-01 20:29:55 YES
    <省略>
           92        827738       833358 2017-09-28 14:20:09 YES
           93        833358       834365 2017-09-28 14:57:39 YES
           94        834365       834411 2017-09-28 14:57:39 YES

    SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP           APPLIED
    ---------- ------------- ------------ ------------------- ------------------------
           95        834411       834994 2017-09-28 14:57:40 YES
           96        834994       837710 2017-09-28 15:01:28 YES
           97        837710       840227 2017-09-28 15:02:06 YES
           98        840227       842731 2017-09-28 15:02:39 YES
           99        842731       845235 2017-09-28 15:03:16 CURRENT ★SQL application is on going
          100        845235       847751 2017-09-28 15:03:49 CURRENT ★SQL application is on going
          101        847751       850377 2017-09-28 15:04:27 NO

    29行が選択されました。

    SQL> /

    SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP           APPLIED
    ---------- ------------- ------------ ------------------- ------------------------
           73        756171       757560 2017-03-01 20:29:55 YES
    <省略>
           92        827738       833358 2017-09-28 14:20:09 YES
           93        833358       834365 2017-09-28 14:57:39 YES
           94        834365       834411 2017-09-28 14:57:39 YES

    SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP           APPLIED
    ---------- ------------- ------------ ------------------- ------------------------
           95        834411       834994 2017-09-28 14:57:40 YES
           96        834994       837710 2017-09-28 15:01:28 YES
           97        837710       840227 2017-09-28 15:02:06 YES
           98        840227       842731 2017-09-28 15:02:39 YES
           99        842731       845235 2017-09-28 15:03:16 YES
          100        845235       847751 2017-09-28 15:03:49 CURRENT ★SQL application is on going
          101        847751       850377 2017-09-28 15:04:27 CURRENT ★SQL application is on going

    29行が選択されました。

    SQL> /

    SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP           APPLIED
    ---------- ------------- ------------ ------------------- ------------------------
           73        756171       757560 2017-03-01 20:29:55 YES
    <省略>
           92        827738       833358 2017-09-28 14:20:09 YES
           93        833358       834365 2017-09-28 14:57:39 YES
           94        834365       834411 2017-09-28 14:57:39 YES

    SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP           APPLIED
    ---------- ------------- ------------ ------------------- ------------------------
           95        834411       834994 2017-09-28 14:57:40 YES
           96        834994       837710 2017-09-28 15:01:28 YES
           97        837710       840227 2017-09-28 15:02:06 YES
           98        840227       842731 2017-09-28 15:02:39 YES
           99        842731       845235 2017-09-28 15:03:16 YES
          100        845235       847751 2017-09-28 15:03:49 YES ★SQL application was over
          101        847751       850377 2017-09-28 15:04:27 YES ★SQL application was over

    29行が選択されました。

    SQL>

  • 相关阅读:
    greenplum表的distributed key值查看
    oracle dump的使用心得
    Linux du与df命令的差异
    从语言只是工具说起
    DDD领域模型
    同一个对象在另一个对象中容易出现重复引用造成map覆盖,HiJson出现严重漏洞自动删除了$ref和空值
    乒乓球相关
    字符串转xml
    最新版java题
    list集合进行分页
  • 原文地址:https://www.cnblogs.com/Frank-20160505/p/7647262.html
Copyright © 2020-2023  润新知