----by acdante
视图列表
| V$ARCHIVE_DEST |
描述DataGuard所有配置目标和目标位置 |
| V$ARCHIIVE_DEST_STATUS |
显示redo传输目标的配置状态信息 |
| V$ARCHIVE_GAP |
物理备库归档重做日志文件间隔 |
| V$DATAGUARD_CONFIG |
显示DataGuard配置的DB_UNIQUE_NAME |
| V$DATAGUARD_STATUS |
显示有消息触发的事件并记录到alert日志中 |
| V$LOG |
REDO LOG 信息 |
| V$LOGFILE |
REDO LOG和STANDBY LOG信息 |
| V$LOG_HISTORY |
包含控制文件中的归档日志历史信息 |
| V$ARCHIVED_LOG |
更详细的包含控制文件中的归档日志历史信息 |
| V$MANAGED_STANDBY |
显示与DG相关的Oracle进程的当前状态信息 |
| V$STANDBY_LOG |
备用日志文件信息 |
|
|
|
| 逻辑备库 |
|
| DBA_LOGSTANDBY_EVENTS |
逻辑备库上最后100个事件(默认) |
| DBA_LOGSTANDBY_PROGRESS |
检查SQL APPPLY是否正在运行 |
| DBA_LOGSTANDBY_LOG |
检查归档日志是否正常传递 |
| DBA_LOGSTANDBY_UNSUPPORTED |
确定SQL Apply不支持的数据类型 |
| V$LOGSTANDBY_PROCESS |
显示逻辑备库相关进程 |
|
|
|
V$ARCHIVED_LOG
SQL> desc v$archived_log;
Name Null? Type
----------------------------------------- -------- ----------------------------
RECID NUMBER
STAMP NUMBER
NAME VARCHAR2(513)
DEST_ID NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
RESETLOGS_ID NUMBER
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
BLOCKS NUMBER
BLOCK_SIZE NUMBER
CREATOR VARCHAR2(7)
REGISTRAR VARCHAR2(7)
STANDBY_DEST VARCHAR2(3)
ARCHIVED VARCHAR2(3)
APPLIED VARCHAR2(9)
DELETED VARCHAR2(3)
STATUS VARCHAR2(1)
COMPLETION_TIME DATE
DICTIONARY_BEGIN VARCHAR2(3)
DICTIONARY_END VARCHAR2(3)
END_OF_REDO VARCHAR2(3)
BACKUP_COUNT NUMBER
ARCHIVAL_THREAD# NUMBER
ACTIVATION# NUMBER
IS_RECOVERY_DEST_FILE VARCHAR2(3)
COMPRESSED VARCHAR2(3)
FAL VARCHAR2(3)
END_OF_REDO_TYPE VARCHAR2(10)
BACKED_BY_VSS VARCHAR2(3)
！============================================================================！
1. (where rownum <= 10,查看前十条记录） /查看归档日志
select sequence#, first_time, next_time from v$archived_log where rownum <= 10 order by sequence#;
2.查看备库日志应用情况
SELECT SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;3.查询DG备库未应用的日志列表
select sequence#, first_time, next_time,applied from v$archived_log where applied='NO' order by sequence#;
4.查看DG保护模式
SELECT PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE; 5.查看归档裂缝（未同步的日志）
SELECT * FROM V$ARCHIVE_GAP;6.查看归档应用进程状态 MRPO APPLYING_LOG
select process,status,thread#,sequence#,client_pid from v$managed_standby;7.查看DG切换状态
select database_role,switchover_status from v$database; 8.查看DataGuard状态信息
SELECT MESSAGE FROM V$DATAGUARD_STATUS;9.开启备库日志应用
SQL>alter database recover managed standby database using current logfile disconnect;10.取消备库日志应用
SQL>alter database recover managed standby database cancel;11.查询主库在该SCN之后有无新增数据文件
select FILE#,name from v$datafile where CREATION_CHANGE#> =6401802928;12.暂停主库的归档日志传送
alter system set log_archive_dest_state_2 = 'defer';13.启用主库日志传送
alter system set log_archive_dest_state_2 = 'enable';14.查询DataGuard信息
set linesize 120
col NAME for a25
col VALUE for a18
col UNIT for a30
col TIME_COMPUTED for a20
col DATUM_TIME for a20
--select v$dataguard_stats
select * from v$dataguard_stats;
NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
------------------------- ------------------ ------------------------------ -------------------- --------------------
transport lag +00 00:00:00 day(2) to second(0) interval 06/30/2017 09:06:29 06/30/2017 09:06:29
apply lag +00 00:00:00 day(2) to second(0) interval 06/30/2017 09:06:29 06/30/2017 09:06:29
apply finish time day(2) to second(3) interval 06/30/2017 09:06:29
estimated startup time 11 second 06/30/2017 09:06:2915.查询每秒redo使用速率
select * from v$sysmetric_history where metric_name = 'Redo Generated Per Sec';
BEGIN_TIM END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT
--------- --------- ------------ ---------- ---------- ------------------------ ---------- -----------------
31-MAY-17 31-MAY-17 6004 2 2017 Redo Generated Per Sec 0 Bytes Per Second
31-MAY-17 31-MAY-17 6002 2 2017 Redo Generated Per Sec 172.60913 Bytes Per Second
31-MAY-17 31-MAY-17 6003 2 2017 Redo Generated Per Sec 11.7274696 Bytes Per Second
31-MAY-17 31-MAY-17 6002 2 2017 Redo Generated Per Sec 0 Bytes Per Second
31-MAY-17 31-MAY-17 6003 2 2017 Redo Generated Per Sec 12.3271697 Bytes Per Second
31-MAY-17 31-MAY-17 6902 2 2017 Redo Generated Per Sec 0 Bytes Per Second
31-MAY-17 31-MAY-17 9003 2 2017 Redo Generated Per Sec 262.445851 Bytes Per Second
31-MAY-17 31-MAY-17 9003 2 2017 Redo Generated Per Sec 8.70820837 Bytes Per Second
31-MAY-17 31-MAY-17 9003 2 2017 Redo Generated Per Sec 95.434855 Bytes Per Second
