-------------------------------------------------------------------------------------------------
--  Script : awr_redo_nologging_size.sql
-------------------------------------------------------------------------------------------------
-- This script will calculate the daily redo size using AWR
-- Restrictions :
-- 	1. Of course, AWR must be running and collects statistics
--      2. If you have centralized AWR repository, then you might want to verify the data.
--         Tested only for non-centralized AWR repository
--
--  Author : Riyaj Shamsudeen
--  No implied or explicit warranty !
-------------------------------------------------------------------------------------------------
PROMPT
PROMPT
PROMPT  awr_redo_nologging_size.sql v1.20 by Riyaj Shamsudeen @orainternals.com
PROMPT
PROMPT   To generate Report about Redo rate from AWR tables
PROMPT    
PROMPT Calculation: Redo size if you turn on force logging at DB level = 
PROMPT                          current redo size + 
PROMPT                          db_block_size * (Physical Writes Direct - Physical Writes Direct Temporary tablespace)
PROMPT     Note: 1.This number includes Direct LOB Writes too.
PROMPT           2.If you have already turned on FORCE logging use script awr_redo_size.sql script.
PROMPT
set pages 40
set lines 160
set serveroutput on size 1000000
column "redo_size (MB)" format 999,999,999,999.99
column "phy_writes_dir (MB)" format 999,999,999,999.99
column "phy_writes_dir_temp(MB)" format 999,999,999,999.99
set verify off
accept db_block_size prompt 'Enter the block size(Null=8192):'
accept history_days prompt 'Enter past number of days to search for (Null=30):'
SELECT inst.db_name,
       redo_date,
       Trunc(SUM(redo_size + nvl('&&db_block_size', 8192) * ( phy_write_direct - phy_write_direct_temp )) / 1024 / 1024, 2) "redo_size (MB)",
       Trunc(SUM(phy_write_direct) * nvl('&&db_block_size',8192) / 1024 / 1024, 2) "phy_writes_dir (MB)",
       Trunc(SUM(phy_write_direct_temp) * nvl('&&db_block_size',8192) / 1024 / 1024, 2) "phy_writes_dir_temp(MB)"
FROM   (SELECT DISTINCT dbid,instance_number,
                        redo_date,
                        redo_size,
                        phy_write_direct,
                        phy_write_direct_temp,
                        startup_time
        FROM   (SELECT sysst.dbid,sysst.instance_number,
                       Trunc(begin_interval_time) redo_date,
                       startup_time,
                       VALUE,
                       CASE
                         WHEN stat_name = 'redo size' THEN
                         Last_value (VALUE) over ( PARTITION BY Trunc (begin_interval_time), startup_time, sysst.stat_id ,sysst.instance_number
                                                   ORDER BY begin_interval_time, startup_time, sysst.stat_id ROWS BETWEEN unbounded preceding AND unbounded following ) -
                         First_value (VALUE) over ( PARTITION BY Trunc(begin_interval_time), startup_time , sysst.stat_id ,sysst.instance_number
						   ORDER BY begin_interval_time, startup_time, sysst.stat_id ROWS BETWEEN unbounded preceding AND unbounded following )
                         ELSE 0
                        END                        redo_size,
                       CASE
                         WHEN stat_name = 'physical writes direct' THEN Nvl( 
			   Last_value (VALUE) over ( PARTITION BY Trunc( begin_interval_time), startup_time, sysst.stat_id,sysst.instance_number
						     ORDER BY begin_interval_time, startup_time, sysst.stat_id ROWS BETWEEN unbounded preceding AND unbounded following ), 0) - 
			   Nvl( First_value (VALUE) over ( PARTITION BY Trunc( begin_interval_time), startup_time , sysst.stat_id , sysst.instance_number
						     ORDER BY begin_interval_time, startup_time, sysst.stat_id ROWS BETWEEN unbounded preceding AND unbounded following ), 0) 
			  ELSE 0
                        END                        phy_write_direct,
                       CASE
                         WHEN stat_name = 'physical writes direct temporary tablespace' THEN Nvl(
                           Last_value (VALUE) over ( PARTITION BY Trunc( begin_interval_time), startup_time , sysst.stat_id ,sysst.instance_number
						     ORDER BY begin_interval_time, startup_time, sysst.stat_id ROWS BETWEEN unbounded preceding AND unbounded following ), 0) - 
			   Nvl( First_value (VALUE) over ( PARTITION BY Trunc( begin_interval_time), startup_time , sysst.stat_id ,sysst.instance_number
						     ORDER BY begin_interval_time, startup_time, sysst.stat_id ROWS BETWEEN unbounded preceding AND unbounded following ), 0) 
			  ELSE 0
                       END                        phy_write_direct_temp
                FROM   sys.wrh$_sysstat sysst,
                       dba_hist_snapshot snaps,
                       sys.wrh$_stat_name statname
                WHERE  snaps.snap_id = sysst.snap_id
                       AND snaps.dbid = sysst.dbid
                       AND snaps.instance_number = sysst.instance_number
                       AND sysst.stat_id = statname.stat_id
                       AND sysst.dbid = statname.dbid
                       AND statname.stat_name in 
			('redo size','physical writes direct' ,'physical writes direct temporary tablespace')
		       AND snaps.begin_interval_time >= to_date(trunc(sysdate-nvl('&&history_days',30)))
                ORDER  BY snaps.snap_id)) redo_data,
       sys.dba_hist_database_instance inst
WHERE  inst.dbid = redo_data.dbid
       AND inst.instance_number = redo_data.instance_number
GROUP  BY inst.db_name,
          redo_date
ORDER  BY inst.db_name,
          redo_date

/ 
set verify on