GV$ARCHIVED_LOG AND GV$LOG_HISTORY Views in Oracle

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 )

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *