DG学习笔记(12)_相关视图

1 如何查询当前DataGuard使用的模式 v$database
SQL> select NAME,RESETLOGS_TIME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,REMOTE_ARCHIVE,DATABASE_ROLE,SWITCHOVER_STATUS,FORCE_LOGGING from v$database;

2 查看当前LOG_ARCHIVE_DEST_N 参数的设置 V$ARCHIVE_DEST
SQL> select dest_id, dest_name, STATUS,ARCHIVER,DESTINATION,REOPEN_SECS,DELAY_MINS,NET_TIMEOUT,PROCESS,MAX_FAILURE,TRANSMIT_MODE,ASYNC_BLOCKS,AFFIRM,TYPE from v$archive_dest where dest_id = 1;

3 查询是否创建了Standby Redo Log.(Standby端)
SQL> SELECT * FROM V$LOGFILE WHERE TYPE = 'STANDBY';

4 查询当前在线日志的状态
SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;

5 查询Primary端最新生成的归档文件Sequence#
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

6 比较两个归档路径是不是同步．
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE#
FROM
 (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
  WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);

如没数据返回，则表示同步的．

7 查看DG相关进程的状态.(Primary ,Standby　端分别执行)
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

8 查询从Primary接收到的所有归档(Standby 端)
SQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG;

10 查询已应用的归档(Standby 端).V$LOG_HISTORY
SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#  FROM V$LOG_HISTORY;

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

11 查询没有传输过去的归档(Primary端)
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
  WHERE
 LOCAL.SEQUENCE# NOT IN
 (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);

12 查询主,备是否存在GAP
SQL> select * from v$archive_gap;