v$log , v$logfile, v$log_history DD views

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

---

     1          1         38   52428800          1 NO  INACTIVE               1678937 07-APR-13
     2          1         39   52428800          1 NO  CURRENT                1708375 08-APR-13
     3          1         37   52428800          1 NO  INACTIVE               1641414 07-APR-13

GROUP# Log group
THREAD# Log thread
SEQUENCE# Log sequence
BYTES Size of the log (in bytes)
MEMBERS of members in the log group
ARCHIVED VARCHAR2(3) Archive status (YES or NO)
STATUS Log status:
UNUSED – Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
CURRENT – Current redo log. This implies that the redo log is active. The redo log could be open or closed.
ACTIVE – Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.

             CLEARING - Log is being re-created as an empty log after an

ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.

            CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there                 is some failure in the switch such as an I/O error writing the new log header.

          INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or                might not be archived.

         INVALIDATED - Archived the current redo log without a log switch.

          FIRST_CHANGE#              Lowest system change   (SCN) in the log
          FIRST_TIME            DATE  Time of the first SCN in the log

SQL> select * from v$logfile;

GROUP# STATUS  TYPE    MEMBER                                                                                                IS_

---

     3         ONLINE  /home/oracle/10.2.0/product/oradata/orcl/redo03.log                                                   NO
     2         ONLINE  /home/oracle/10.2.0/product/oradata/orcl/redo02.log                                                   NO
     1         ONLINE  /home/oracle/10.2.0/product/oradata/orcl/redo01.log                                                   NO

GROUP# Redo log group identifier number
STATUS Status of the log member:
INVALID – File is inaccessible

                   STALE - File's contents are incomplete

                 DELETED - File is no longer used

                null - File is in use

TYPE Type of the logfile:
ONLINE
STANDBY

MEMBER R edo log member name
IS_RECOVERY_DEST_FILE Indicates whether the file was created in the flash recovery area (YES) or not (NO)

SQL> select * from v$log_history;

 RECID      STAMP    THREAD#  SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS

---

     1  810903173          1          1        525876 24-MAR-13       556543            525876 24-MAR-13
     2  811006590          1          2        556543 24-MAR-13       615576            525876 24-MAR-13
     3  811020025          1          3        615576 25-MAR-13       636527            525876 24-MAR-13
     4  811029938          1          4        636527 25-MAR-13       656691            525876 24-MAR-13

RECID Control file record ID
STAMP Control file record stamp
THREAD# Thread of the archived log
SEQUENCE# Sequence of the archived log
FIRST_CHANGE# Lowest system change (SCN) in the log
FIRST_TIME Time of the first entry (lowest SCN) in the log
NEXT_CHANGE# Highest SCN in the log
RESETLOGS_CHANGE# Resetlogs change of the database when the log was written
RESETLOGS_TIME Resetlogs time of the database when the log was written

Share this post: on Twitter on Facebook on Google+