BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Monitoring Redo Log Status

Oracle Tips by Burleson Consulting

Remote DBAs should monitor redo log status to determine which logs are in use and if there are any odd status codes such as stale log indications or indications of corrupt redo logs. The log files can have the following status values:

USED. Indicates status of a log that has just been added (never used) or that a RESETLOGS command has been issued.

CURRENT. Indicates a valid log that is in use.

ACTIVE. Indicates a valid log file that is not currently in use.

CLEARING. Indicates log is being re-created as an empty log due to Remote DBA action.

CLEARING CURRENT. Means that current log is being cleared of a closed thread. If a log stays in this status, it could indicate there is some failure in the log switch.

INACTIVE. Means that log is no longer needed for instance recovery but may be needed for media recovery.

The v$logfile table has a status indicator that gives these additional codes:

INVALID. File is inaccessible.

STALE. File contents are incomplete (such as when an instance is shut down with SHUTDOWN ABORT or due to a system crash).

DELETED. File is no longer used.

The script in Source 11.27 provides some basic information on log status. Listing 11.22 shows an example of output from LOG_STAT.SQL script.

SOURCE 11.27 Example LOG_STAT.SQL script.

rem
rem Name:     log_stat.sql
rem
rem FUNCTION: Provide a current status for redo logs
rem
rem
COLUMN first_change# FORMAT 99999999  HEADING Change#
COLUMN group#        FORMAT 9,999     HEADING Grp#
COLUMN thread#       FORMAT 999       HEADING Th#
COLUMN sequence#     FORMAT 999,999   HEADING Seq#
COLUMN members       FORMAT 999       HEADING Mem
COLUMN archived      FORMAT a4        HEADING Arc?
COLUMN first_time    FORMAT a21       HEADING 'Switch|Time'
BREAK ON thread#
SET PAGES 60 LINES 131 feedback OFF
START title132 'Current Redo Log Status'
SPOOL rep_out\&db\log_stat
SELECT thread#,group#,sequence#,bytes,
       members,archived,
       status,first_change#,
       TO_CHAR(first_time, 'DD-MM-YYYY HH24:MI:SS') first_time
  FROM
       sys.v_$log
See Code Depot for Full Scripts
SPOOL OFF
PAUSE Press Enter to continue
SET PAGES 22 LINES 80 feedback ON
CLEAR BREAKS
CLEAR COLUMNS
TTILE OFF

LISTING 11.22 Example output of script to monitor redo log status.

Date: 06/15/97                                               Page:   1
Time: 01:39 PM           Current Redo Log Status                SYSTEM
                             ORTEST1 database
                                               
                                                   Switch
Th# Grp#  Seq#     BYTES Mem Arc? STATUS   Change# Time
--- ---- -----   ------- -------- -------- ------- ------------------
  1    1 4,489   1048576   2 NO   INACTIVE  719114 15-JUN-97 16:54:23
       2 4,490   1048576   2 NO   INACTIVE  719117 15-JUN-97 16:56:10
       3 4,491   1048576   2 NO   CURRENT   719120 15-JUN-97 17:02:22

Monitoring Redo Log Switches

In addition to the alert logs, the frequency of log switches can also be monitored via the v$log_history and v$archived_log DPTs. A script that uses these DPTs for this purpose is shown in Source 11.28. Listing 11.23 shows an example of output from an archive log switch script.

SOURCE 11.28 Script to monitor archive log switches.

REM
REM NAME         :log_hist.sql
REM PURPOSE      :Provide info on logs for last 24 hours since last
REM PURPOSE      :log switch
REM USE          : From SQLPLUS
REM Limitations  : None
REM MRA 10/14/01 Updated for Oracle9i
REM
COLUMN thread#             FORMAT 999      HEADING 'Thrd#'
COLUMN sequence#           FORMAT 99999    HEADING 'Seq#'
COLUMN first_change#                       HEADING 'SCN Low#'
COLUMN next_change#                        HEADING 'SCN High#'
COLUMN archive_name        FORMAT a50      HEADING 'Log File'
COLUMN first_time          FORMAT a20      HEADING 'Switch Time'
COLUMN name                FORMAT a30      HEADING 'Archive Log'
SET LINES 132 feedback OFF VERIFY OFF
START title132 "Log History Report"
SPOOL rep_out\&db\log_hist
REM
SELECT
     X.recid,a.thread#,
     a.sequence#,a.first_change#,
     a.switch_change#,
     TO_CHAR(a.first_time,'DD-MON-YYYY HH24:MI:SS') first_time,
     x.name
FROM
 v$loghist a, v$archived_log x
WHERE
  a.first_time>
   (SELECT b.first_time-1
   FROM v$loghist b WHERE b.switch_change# =
    (SELECT MAX(c.switch_change#) FROM v$loghist c)) AND
See Code Depot for Full Scripts
SPOOL OFF
SET LINES 80 VERIFY ON feedback ON
CLEAR COLUMNS
TTITLE OFF
PAUSE Press Enter to continue

LISTING 11.23     Example of output from archive log switch script.

Date: 10/14/01                                                                          Page:   1
Time: 04:10 PM                             Log History Report                  Remote DBAUTIL
                                           galinux1 database

    RECID Thrd#   Seq#  SCN Low# SWITCH_CHANGE# Switch Time          Archive Log
--------- ----- ------ --------- -------------- -------------------- -------------       1      8    375520         409741 05-SEP-2001 08:18:06

Press Enter to continue

Monitoring Redo Statistics

There are no views in Oracle that allow the user to look directly at a log file’s statistical data. Instead, we must look at statistics based on redo log and log writer process statistics. These statistics are in the views V$STATNAME, V$SESSION, V$PROCESS, V$SESSTAT, V$LATCH, and V$LATCHNAME. An example of a report that uses these views is shown in Source 11.29; an example of the script’s output is shown in Listing 11.24.

SOURCE 11.29 Script to generate reports on redo statistics.

REM
REM NAME          : rdo_stat.sql
REM PURPOSE       : Show REDO latch statistics
REM USE           : from SQLPlus
REM Limitations   : Must have access to v$_ views
REM
SET PAGES 56 LINES 78 VERIFY OFF feedback OFF
START title80 "Redo Latch Statistics"
SPOOL rep_out/&&db/rdo_stat
rem
COLUMN name      FORMAT a30          HEADING Name
COLUMN percent   FORMAT 999.999      HEADING Percent
COLUMN total                         HEADING Total
rem
SELECT
     l2.name,
     immediate_gets+gets Total,
     immediate_gets "Immediates",
     misses+immediate_misses "Total Misses",
     DECODE (100.*(GREATEST(misses+immediate_misses,1)/
     GREATEST(immediate_gets+gets,1)),100,0) Percent
FROM
     v$latch l1,
     v$latchname l2
WHERE
     l2.name like '%redo%'
     and l1.latch#=l2.latch# ;
rem
PAUSE Press Enter to continue
rem
rem Name: Redo_stat.sql
rem
rem Function: Select redo statistics from v$sysstat

COLUMN name    FORMAT a30         HEADING 'Redo|Statistic|Name'
COLUMN value   FORMAT 999,999,999 HEADING 'Redo|Statistic|Value'
SET PAGES 80 LINES 60 feedback OFF VERIFY OFF
START title80 'Redo Log Statistics'
SPOOL rep_out/&&db/redo_stat
SELECT
     name,
     value
FROM
     v$sysstat
WHERE
     name LIKE '%redo%'
ORDER BY statistic#;
SPOOL OFF
SET LINES 24 feedback ON VERIFY ON
TTITLE OFF
CLEAR COLUMNS
CLEAR BREAKS

LISTING 11.24 Example of output from redo report scripts.

Date: 10/14/01                                             Page:   1
Time: 04:14 PM             Redo Latch Statistics            Remote DBAUTIL
                              galinux1 databa

Name                            Total Immediates Total Misses  Percent
--------------------------- --------- ---------- ------------ --------
redo allocation                172438          0            0
redo copy                        6259       6231            0
redo writing                   672470          0            0
Press Enter to continue

Date: 10/14/01                                              Page:   1
Time: 04:14 PM              Redo Log Statistics             Remote DBAUTIL
                              galinux1 databa

Redo                                   Redo
Statistic                         Statistic
Name                                  Value
------------------------------ ------------
redo synch writes                       250
redo synch time                          72
redo entries                          6,231
redo size                         1,569,816
redo buffer allocation retries            0
redo wastage                      1,200,696
redo writer latching time                 0
redo writes                           3,635
redo blocks written                   5,586
redo write time                         151
redo log space requests                   0
redo log space wait time                  0
redo log switch interrupts                0
redo ordering marks                       0

Of course, right about now you are probably asking, what good all these numbers will do you. Let’s look at what they mean and how you can use them. The first section of the report in Source 11.29 should be self-explanatory. The redo logs use two latches: REDO ALLOCATION and REDO COPY.

In general, if the PERCENT statistic (actually, the ratio of total misses to total gets) is greater than 10 percent, contention is occurring, and the Remote DBA needs to examine the way he or she is doing redo logs (more about this in a second). The initial latch granted for redo is the REDO_ALLOCATION latch. The REDO_COPY latch is granted to a user when the size of his or her entry is greater than the _LOG_

SMALL_ENTRY_MAX_SIZE parameter in the initialization file. If you see REDO_
ALLOCATION latch contention, decrease the value of _LOG_SMALL_ENTRY_MAX_

SIZE. If there is more than one user that requires the REDO_COPY latch, you get contention on single-CPU systems. The number of REDO_COPY latches is limited to twice the number of CPUs on the system. If you have a single CPU, only one is allowed. It is normal to see high contention for this latch on single-CPU systems, and there is nothing the Remote DBA can do to increase the number of REDO_COPY latches. However, even on single-CPU systems, you can force Oracle to prebuild redo entries, thereby reducing the number of latches required. This is accomplished by setting the _LOG_ENTRY_ PREBUILD_THRESHOLD entry in the initialization file higher. On multiple-CPU systems, increase the number of REDO_COPY latches to twice the number of CPUs.

In the second half of the report, statistics from the caches that affect redo operations are shown. Let’s look at what these numbers tell us. The most important of the listed statistics are redo blocks written, redo entries linearized, redo small copies, and redo writes.

* redo blocks written is useful when two entries are compared for a specified time period. This will indicate how much redo is generated for the period between the two checks.

* redo small copies  tells how many times the entry was effectively written on a redo allocation latch. This indicates that a redo copy latch was not required for this entry. This statistic should be compared with the redo entries parameter. If there is close to a one-to-one relationship, then your system is making effective use of the redo allocation latch. If there is a large difference, then the LOG_SMALL_ENTRY_MAX_SIZE INIT.ORA parameter should be increased. If the LOG_SIMULTANEOUS_COPIES parameter is 0, this value is ignored.

* redo writes is the total number of redo writes to the redo buffer. If this value is too large compared to the redo entries parameter value, then the Remote DBA should tune the INIT.ORA parameters mentioned in the previous sections to force prebuilding of the entries. If the entries are not prebuilt, the entry may require several writes to the buffer before it is fully entered; if it is prebuilt, it requires only one.

* redo log space wait is the statistic that tells you if users are having to wait for space in the redo buffer. If this value is nonzero, increase the size of the LOG_

BUFFER in the initialization file.

* redo buffer allocation retries is the statistic that tells the Remote DBA the number of repeated attempts needed to allocate space in the redo buffer. If this value is high in comparison to redo entries, it indicates that the redo logs may be too small and should be increased in size. Normally, this value should be much less than the redo entries statistic. In the example, it has a value of 5 compared to the entry’s value of 1044; this is satisfactory.

* redo size tells the total number of redo bytes generated since the database was started. Comparison of two readings will give the amount generated over time. This value can then be used to determine if the log switch interval is proper. Too many log switches over a small amount of time can impair performance.

Use the following formula to look at log switches over time:

(X / (dN / dt)) / interval of concern

where:

X is the value of LOG_CHECKPOINT_INTERVAL or size of the redo log in system blocks.

dN is the change in the redo size over the time interval.

dt is the time differential for the period (usually minutes).

Once the number of log switches is known, the Remote DBA can use this value to determine the size of redo logs based on system I/O requirements. If you need to reduce the number of log switches, increase the redo log size; of course, this may impact system availability since it takes longer to write out a large redo log buffer than a small one to disk. A balance must be struck between undersizing the redo logs and taking a database performance hit and making the logs too large and taking an I/O hit.


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter