Redo statistics

Job details

Name:	Redo statistics
Platform:	Oracle
Category:	Performance
Description:	Gets redolog files statistics. Based on Oracle dictionary and performance views.
Long description:	Task gets redolog files statistics. Based on Oracle dictionary and performance views.
Version:	3.5
Default schedule:	6,16,26,36,46,56 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & nine_and_above=‘1′]

Parameters

Name	Default value	Description
keep data for	14	The amount of days the statistics are kept in the history table.

Job Summary

Purpose: The primary objective of this task is to monitor and collect statistics related to the redo log files in Oracle databases.
Why: Redo log files are crucial for database recovery processes such as instance recovery and media recovery. Efficient management and monitoring of redo logs can prevent data loss, enhance data integrity, and optimize performance during recovery operations. Monitoring these statistics helps in managing the size, switches, and performance of redo logs which are significant for ensuring database resilience and efficiency.
Manual checking: You can manually check the statistics and information related to redo logs in an Oracle database by executing the following queries:

SELECT THREAD# as "Thread", GROUP# as "Group", MEMBER as "Member", BYTES as "Size", STATUS as "Status" FROM [schema_name].dbw_redo_stat_[instance_id] WHERE rep_type!='OLD' ORDER BY GROUP#

Details and Dependencies

Task Name: Redo statistics
Version: 3.5
Description: Gets redo log files statistics based on Oracle dictionary and performance views.
Default Schedule: Every 10 minutes (at minutes 6, 16, 26, 36, 46, 56 of each hour)

Dependencies:

Dependency Object	Object Type	Description
redo_stat_[instance_id]	2 (Self-reference)	Task has a dependency on itself, likely for iterative checks or updates.
dbw_redo_stat_[instance_id]	0 (Table)	Primary table holding redo log statistics.
dbw_trans_count_[instance_id]	0 (Table)	Stores transaction count data.
dbw_switch_count_hour_[instance_id]	0 (Table)	Holds data for redo log switches per hour.
dbw_switch_count_day_[instance_id]	0 (Table)	Contains redo log switches per day data.
dbw_switch_count_last_run_[instance_id]	0 (Table)	Used to track the last run details for day and hour redo statistics.
dbw_trans_count_histr_[instance_id]	0 (Table)	Historical transaction data table.

Implementation

Code: Various tables are created and procedures are defined to populate these tables with the logs’ statistics such as switch number and frequency, and transaction counts. These procedures help in generating reports on redo log file sizes, status, and activities.

Reporting Template

Report Title: Redo Statistics
Description: The report provides detailed analysis based on the collected data from redo log statistics, including redo log configurations and switch frequency.
Default Schedule: Hourly

Report Contents

Chapter: Redolog Analysis
Details:
Discusses the importance of redolog files in recovery processes.

Presentations: Includes tables and charts that display:
Redolog file statistics grouped by thread and size.

By efficiently monitoring and analyzing redo statistics using the dbWatch job, database administrators can ensure the stability and performance of their Oracle databases, especially in recovery scenarios.