通过本命令检测系统同步和延迟状态。

本次测试数据库版本：SQL*Plus: Release 12.2.0.1.0 Production on Fri Mar 11 12:11:23 2022

1，编写信息

#10.88.0.70 备数据库

#10.88.0.21 主数据库

#编写巡检脚本，查询备和主的数据库dataguard状态[oracle@db01 ~]$ cat chkdg.sh

echo "10.88.0.70 info"
sqlplus -S / as sysdba <<EOF
@/home/oracle/dg/dg.sql
EOF

echo "10.88.0.21 info"
sqlplus -S sys/oracle@10.88.0.21/hisdb as sysdba<<EOF
select unique thread# as thread,max(sequence#) over(partition by thread#) as last from gv\$archived_log;
EOF

#sql信息[oracle@db01 ~]$ cat /home/oracle/dg/dg.sql--GAP查询
select thread# ,low_sequence#,high_sequence# from v$archive_gap;--max日志查询
select unique thread# as thread,max(sequence#) over(partition by thread#) as last from gv$archived_log;
select unique thread# as thread,max(sequence#) over(partition by thread#) as last from gv$archived_log where applied='YES';
SET LINESIZE 500;
col value format a20;
select * from v$dataguard_stats;
select sequence#,to_char(FIRST_TIME,'yyyy-MM-dd HH24:mi:ss'),to_char(NEXT_TIME,'yyyy-MM-dd HH24:mi:ss'),applied from v$archived_log
where sequence# in (select max(sequence#) from gv$archived_log);
 select process,status,sequence#,THREAD#,BLOCK# from v$managed_standby;
[oracle@db01 ~]$

复制

2，执行效果

[oracle@db01 ~]$ ./chkdg.sh
10.88.0.70 info

no rows selected


    THREAD       LAST
---------- ----------
         1      31032
         2      32411


    THREAD       LAST
---------- ----------
         1      31031
         2      32411


SOURCE_DBID SOURCE_DB_UNIQUE_NAME            NAME                             VALUE                UNIT                           TIME_COMPUTED                  DATUM_TIME                         CON_ID
----------- -------------------------------- -------------------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ----------
 1967978881                                  transport lag                    +00 00:01:26         day(2) to second(0) interval   03/11/2022 12:10:35            03/11/2022 12:10:28                     0
 1967978881                                  apply lag                        +00 00:01:26         day(2) to second(0) interval   03/11/2022 12:10:35            03/11/2022 12:10:28                     0
 1967978881                                  apply finish time                                     day(2) to second(3) interval   03/11/2022 12:10:35                                                    0
          0                                  estimated startup time           21                   second                         03/11/2022 12:10:35                                                    0


 SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' APPLIED
---------- ------------------- ------------------- ---------
     32411 2022-03-11 12:08:05 2022-03-11 12:08:11 YES


PROCESS   STATUS        SEQUENCE#    THREAD#     BLOCK#
--------- ------------ ---------- ---------- ----------
ARCH      CONNECTED             0          0          0
DGRD      ALLOCATED             0          0          0
DGRD      ALLOCATED             0          0          0
ARCH      CONNECTED             0          0          0
ARCH      CONNECTED             0          0          0
ARCH      CONNECTED             0          0          0
RFS       IDLE              32412          2        285
RFS       IDLE              31033          1       6862
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0

PROCESS   STATUS        SEQUENCE#    THREAD#     BLOCK#
--------- ------------ ---------- ---------- ----------
RFS       IDLE                  0          0          0
MRP0      WAIT_FOR_LOG      32412          2          0

13 rows selected.

10.88.0.21 info

    THREAD       LAST
---------- ----------
         1      31032
         2      32411

[oracle@db01 ~]$
复制