IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
I will explain GV$ARCHIVED_LOG AND GV$LOG_HISTORY Views in Oracle in this post.
gv$archived_log view in Oracle
V$Archived_log and gv$archived_log views are one of the most important views for DBAs that includes archive log names and archived log information from the control file. An archive log record is inserted after the online redo log is successfully archived or cleared.
v$archived_log and gv$archived_log views columns are as follows.
SQL> desc gv$archived_log Name Null? Type ----------------------------------------- -------- ---------------------------- INST_ID NUMBER 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) CON_ID NUMBER SQL>
If your database is single instance, you can use the v$archived_log, but if your database is cluster and Oracle RAC, then you should use the gv$archived_log view.
If you have standby database, you can use the applied column to filter applied archived logs as follows.
select * from gv$archived_log where applied='YES';
Or You can use the deleted column to see not deleted archived logs.
select * from gv$archived_log where deleted='YES'; select sequence#,first_time,next_time,applied from v$archived_log order by sequence#; select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='YES';
GV$LOG_HISTORY in Oracle
V$LOG_HISTORY and GV$LOG_HISTORY contain log history information from the control file.
GV$LOG_HISTORY columns are as follows.
SQL> desc gv$log_history Name Null? Type ----------------------------------------- -------- ---------------------------- INST_ID NUMBER RECID NUMBER STAMP NUMBER THREAD# NUMBER SEQUENCE# NUMBER FIRST_CHANGE# NUMBER FIRST_TIME DATE NEXT_CHANGE# NUMBER RESETLOGS_CHANGE# NUMBER RESETLOGS_TIME DATE CON_ID NUMBER SQL>
You can list the gv$log_history as follows.
LOG FREQUENCY MAP in Oracle
You can use the following script to find LOG FREQUENCY MAP.
SELECT TO_CHAR (first_time, 'YYYY-MON-DD') DAY, TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '00', 1, 0)), '9999') "00", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '01', 1, 0)), '9999') "01", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '02', 1, 0)), '9999') "02", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '03', 1, 0)), '9999') "03", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '04', 1, 0)), '9999') "04", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '05', 1, 0)), '9999') "05", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '06', 1, 0)), '9999') "06", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '07', 1, 0)), '9999') "07", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '08', 1, 0)), '9999') "08", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '09', 1, 0)), '9999') "09", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '10', 1, 0)), '9999') "10", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '11', 1, 0)), '9999') "11", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '12', 1, 0)), '9999') "12", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '13', 1, 0)), '9999') "13", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '14', 1, 0)), '9999') "14", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '15', 1, 0)), '9999') "15", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '16', 1, 0)), '9999') "16", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '17', 1, 0)), '9999') "17", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '18', 1, 0)), '9999') "18", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '19', 1, 0)), '9999') "19", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '20', 1, 0)), '9999') "20", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '22', 1, 0)), '9999') "22", TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '23', 1, 0)), '9999') "23" FROM gv$log_history WHERE first_time > SYSDATE - 7 GROUP BY TO_CHAR (first_time, 'YYYY-MON-DD') ORDER BY TO_CHAR (first_time, 'YYYY-MON-DD') DESC;
Difference ( Lag ) Script for Standby
You can use the gv$archived_log and gv$log_history views to find the difference between Production and Standby using the following scripts.
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last in Sequence", APPL.SEQUENCE# "Last Applied Sequence", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last in Sequence", APPL.SEQUENCE# "Last Applied Sequence", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" 2 FROM 3 (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, 4 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL 5 WHERE 6 ARCH.THREAD# = APPL.THREAD# 7 ORDER BY 1; Thread Last in Sequence Last Applied Sequence Difference ---------- ---------------- --------------------- ---------- 1 38444 38444 0 2 35036 35036 0 3 33734 33734 0 4 34719 34719 0 5 34148 34148 0 6 34289 34289 0 7 34488 34488 0 8 35190 35190 0 8 rows selected. SQL>
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
