Views Related to redo log files >
v$log Displays the redo log file information from the control file
v$logfile Identifies redo log groups and members and member status
v$log_history contains log history information
Header status of online redo log groups?
4 status #
current > LGWR is writing #
active > archiver is working on that group #
inactive > not LWGR or ARCHIVER
unused > BRAND NEW and have sequence number 0>>
set linesize 300
column REDOLOG_FILE_NAME format a50
SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ASC;
Archiver is slow?
LGWR has to wait for online redo log group"
To avoid LGWR waits( log file sync ) we add additional redolog groups for database
resizing the new online group>> or adding a new online redo log file group >
sql> alter database add logfile group < Group Number > ('location1','location2') size <M/G>;
CURRENT # WHERE LGWR is writing to online redo log group #
Log writer forcefully switches from one online redo log group to another online redo log group ##
sql> alter system switch logfile;
System altered.
GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB
---------- ---------- ---------- --- ---------------- --------------------------------------------
1 1 10 YES INACTIVE /u01/app/orcl/redo01.log 50
2 1 11 YES INACTIVE /u01/app/orcl/redo02.log 50
3 1 12 NO CURRENT /u01/app/orcl/redo03.log 50
Make sure header status is inactive then only we drop the online redolog group;
syntax# alter database drop logfile group <group number>
example
sql> alter database drop logfile group 1;
Drop a member to existing group >
sql> alter database drop logfile member '/u01/app/orcl/redo04.log' size 100m;
If the status if active or current, we cannot drop the online redolog group
Adding a member to the group >>
sql> alter database add logfile member 'location' to group <group Number>;
Query's to check number of archive logs generated per day
select
Start_Date,
Start_Time,
Num_Logs,
Round(Num_Logs * (Vl.Bytes / (1024 * 1024)), 2) AS Mbytes,
Vdb.NAME AS Dbname
FROM
(SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date, To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time,
COUNT(Vlh.Thread#) Num_Logs
FROM
V$log_History Vlh
GROUP BY
To_Char(Vlh.First_Time, 'YYYY-MM-DD'),
To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist,
V$log Vl,
V$database Vdb
WHERE
Vl.Group# = 1
ORDER BY
Log_Hist.Start_Date,
Log_Hist.Start_Time;
--Oracle In memory--
