[DataGuard] 管理用视图

1. 逻辑备库

1-1. 查看不被应用的对象
# sys 用户创建的表不会被应用
LDG> select statement_opt,owner,name from dba_logstdby_skip;

1-2. 逻辑备库操作日志, 只保留近 100 条
LDG> select event_time, status, event from dba_logstdby_events order by event_timestamp; 

1-3. 当前重做日志的情况
LDG> select sequence#, first_change#, next_change#, timestamp, applied from dba_logstdby_log;

1-4. LogMiner 状态信息
LDG> select * from v$logstdby_stats;

1-5. 当前日志服务相关信息
LDG> select sid, serial#, spid, type, status, high_scn from v$logstdby_process;

1-6. 当前 LOG 应用服务进展情况
LDG> select applied_scn, latest_scn, mining_scn, restart_scn from v$logstdby_progress;

1-7. SQL 应用大概状态
LDG> select session_id, state from v$logstdby_state;

-----------------------------------------------------------------------------------

2. 物理备库

2-1. 物理备库相关进程, redo 应用, redo 传输服务的活动状态
PDG> select process, client_process, sequence#, status, block#, blocks from v$managed_standby;

2-2. REDO 应用进度
PDG> select dest_name, archived_thread#, archived_seq#, applied_thread#, applied_seq#, db_unique_name from v$archive_dest_status where status = 'VALID';

2-3. 归档信息
PDG> select name, creator, sequence#, applied, completion_time from v$archived_log;

2-4. 归档历史
PDG> select first_time, first_change#, next_change#, sequence# from v$log_history;

2-5. 物理备库未接收的日志
# N -- 主库本地归档路径
# M -- 备库远程归档路径
PRIM> select local.thread#, local.sequence# from (select thread#, sequence# from v$archived_log where dest_id=N) local where local.sequence# not in (select sequence# from v$archived_log where dest_id=M and thread# = local.thread#);

2-6. 当前数据库基本信息
PDG> select database_role, db_unique_name, open_mode, protection_mode, protection_level, switchover_status from v$database;

2-7. failover 后快速启动信息
PDG> select fs_failover_status, fs_failover_current_target, fs_failover_threshold, fs_failover_observer_present from v$database;

2-8. 查看应用模式
# IDLE, MANAGED, MANAGED REAL TIME APPLY
PDG> select recovery_mode from v$archive_dest_status;

2-9. DataGuard 事件 (alert.log)
PDG> select message from v$dataguard_status;