—-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:29

15.查詢每秒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

Oracle11gDATAGUARD相關檢視備忘

檢視列表

V$ARCHIVED_LOG

相關文章